核心

不在數(shù)據(jù)庫(kù)做運(yùn)算

單表數(shù)據(jù)量：一年內(nèi)單表純INT不超過1000W，含CHAR不超500W。單庫(kù)不超過300~400表

表字段盡量少，上限控制在20~50個(gè)

適當(dāng)可以冗余(平衡范式和冗余)

拒絕大sql，大事務(wù)，大批量

字段

數(shù)值類型的字節(jié)和運(yùn)用范圍

如果可以，將字符串轉(zhuǎn)化為數(shù)字存儲(chǔ)?？梢约涌觳樵兯俣群凸?jié)省空間，舉例用INT代替CHAR(15)來存儲(chǔ)IP

優(yōu)先使用SET和ENUM...(可能有問題!)

避免使用NULL

少用TEXT/BLOB，如果必須使用(超過varchar最大限制64k)則必須拆分到單獨(dú)的表

不在數(shù)據(jù)庫(kù)存圖片

索引

能不加的索引盡量不加，最好不超過字段數(shù)的20%(如：性別不加)，結(jié)合核心SQL優(yōu)先考慮覆蓋索引(https://my.oschina.net/BearCatYN/blog/476748)

字符字段必須建前綴索引。由于字符串很長(zhǎng)，通?？梢运饕_始的幾個(gè)字符，而不是全部值，以節(jié)約空間并得到好的性能。(http://www.educity.cn/wenda/402373.html)

不在索引列進(jìn)行數(shù)學(xué)運(yùn)算和函數(shù)運(yùn)算(會(huì)導(dǎo)致無(wú)法使用索引 => 全表掃描)，如where id+1 = 100和id = 100 - 1，效率差很遠(yuǎn)

自增列或全局ID做INNODB的主鍵

盡量不用外鍵(由程序保證約束)，高并發(fā)的時(shí)候容易死鎖

SQL

SQL語(yǔ)句盡可能簡(jiǎn)單，因?yàn)橐粭lSQL只能在一個(gè)CPU運(yùn)算，在高并發(fā)的情況下，可能一條大SQL就把整個(gè)數(shù)據(jù)庫(kù)堵死。而簡(jiǎn)單的SQL緩存命中率更高，減少鎖表的時(shí)間(特別是MyISAM)，用上多CPU

保持事務(wù)、DB連接足夠短，即開即用、用完就關(guān)。與事務(wù)無(wú)關(guān)操作放到事務(wù)外面，減少鎖資源的占用；在不破壞一致性前提下，使用多個(gè)短事務(wù)代替長(zhǎng)事務(wù)(如：發(fā)帖時(shí)的圖片上傳等待)

盡可能少用存儲(chǔ)過程，少用觸發(fā)器，減用MySQL函數(shù)對(duì)結(jié)果進(jìn)行處理(交由客戶端程序負(fù)責(zé))

盡量少用select *，只取需要數(shù)據(jù)列，為使用覆蓋索引提供可能性，減少臨時(shí)表生成，更安全

用in()代替or，因?yàn)閛r的效率是O(n)，而in()的效率是O(Log n)。如：where a = 1 OR a = 100與where a IN (1, 100)

merge index往往很弱智，所以用union代替對(duì)多字段的or查詢。如：select * from t where a = 1 OR b = 2與select * from t where a = 1 UNION select * from t where b = 2

盡量避免負(fù)向查找，如NOT、!=等

盡量避免%前綴模糊查詢，由于使用的是B+ Tree，前綴模糊使用不了索引，導(dǎo)致全表掃描(后綴模糊速度相對(duì)快很多)

減少COUNT(*)，使用COUNT(col)，前者資源開銷大，盡量少用。MyISAM不帶WHERE COUNT()而INNODB帶WHERE COUNT()。 計(jì)數(shù)的統(tǒng)計(jì)可以采用的方法：實(shí)時(shí)統(tǒng)計(jì)可以使用memcache，雙向更新，凌晨跑基準(zhǔn)；非實(shí)時(shí)統(tǒng)計(jì)盡量用單獨(dú)統(tǒng)計(jì)表，定期重算

LIMIT高效分頁(yè)：傳統(tǒng)的方法是select * from t limit 10000, 10，推薦的方法是select * from t where id > 23423 limit 10。LIMIT的偏移量越大則越慢。還有一些高效的方法有：先取id來LIMIT偏移，減少整體的數(shù)據(jù)偏移；取到需要的id，與原表JOIN；程序取ID，然后用IN來填寫。select * from t where id >= (select id from t limit 10000, 1) limit 10,select * from t INNER JOIN (select id from t limit 10000, 10) USING (id),select id from t limit 10000, 10; select * from t where id in (123, 456...)

若無(wú)需對(duì)結(jié)果進(jìn)行去重，則用UNION ALL而非UNION(UNION有去重開銷)

分解JOIN聯(lián)接來保證高并發(fā)。高并發(fā)DB不建議進(jìn)行兩個(gè)表以上的JOIN

group by會(huì)默認(rèn)自動(dòng)升序排序，如果需要去掉排序，需要指定order by NULL

比較原則：數(shù)字對(duì)數(shù)字、字符對(duì)字符。如果數(shù)值列與字符類型作比較，同時(shí)轉(zhuǎn)換成雙精度；如果字符列與數(shù)值類型作比較，字符列整列轉(zhuǎn)數(shù)值，且不會(huì)使用索引查詢

load data導(dǎo)入數(shù)據(jù)比insert快約20倍(不需要刷新緩存)

盡量不使用insert...select(延遲、同步出錯(cuò))

大批量更新凌晨操作，避開高峰

SQL的一些命令：explain, show profile, mysqlsla, mysqldumpslow, show slow log, show processlist, show QUERY_RESPONSE_TIME(Percona)

約定

數(shù)據(jù)庫(kù)在不同時(shí)期使用不同的：實(shí)時(shí)數(shù)據(jù)用real庫(kù)，模擬環(huán)境用sim庫(kù)，測(cè)試用qa庫(kù)，開發(fā)用dev庫(kù)

禁止未經(jīng)DBA確認(rèn)的子查詢(大部分情況優(yōu)化較差，特別是WHERE中使用IN id的子查詢，一般可以用JOIN改寫)

不要在程序上加鎖數(shù)據(jù)庫(kù)，因?yàn)橥獠挎i對(duì)數(shù)據(jù)庫(kù)不可控，高并發(fā)時(shí)是災(zāi)難，并且極難調(diào)試排查(可以采用事務(wù)來解決)

統(tǒng)一字符集：UTF-8，校對(duì)規(guī)則：utf8_general_ci

庫(kù)和表的名稱統(tǒng)一用小寫(大小寫敏感、且不同操作系統(tǒng)都有不同的限制)；字段名大小寫不敏感；索引名默認(rèn)為idx_字段名；庫(kù)名用縮寫，盡量在2~7個(gè)字母；避免用保留字命名

作者：TyrusChin

鏈接：http://www.itdecent.cn/p/cf620ad5e8f4

來源：簡(jiǎn)書

著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處。