文章轉(zhuǎn)載自segmentfault 踏雪無(wú)痕S 的回答 ，加上個(gè)人的一些觀點(diǎn) 侵刪

設(shè)計(jì)原則

1房轿、不在數(shù)據(jù)庫(kù)做運(yùn)算：cpu計(jì)算務(wù)必移至業(yè)務(wù)層
2、控制單表數(shù)據(jù)量：?jiǎn)伪碛涗浛刂圃?000w
3所森、控制列數(shù)量：字段數(shù)控制在20以內(nèi)
4囱持、平衡范式與冗余：為提高效率犧牲范式設(shè)計(jì)，冗余數(shù)據(jù)
5焕济、拒絕3B：拒絕大sql纷妆，大事務(wù)，大批量
6晴弃、表字符集使用UTF8

個(gè)人更推薦utf8mb4

7掩幢、使用INNODB存儲(chǔ)引擎

關(guān)于 使用 INNODB 存儲(chǔ)引擎逊拍，在《高性能Mysql》這本書中就有提到，不建議使用MyIsam粒蜈，具體請(qǐng)查閱此書

數(shù)據(jù)表設(shè)計(jì)

1顺献、盡可能地使用最有效(最小)的數(shù)據(jù)類型

tinyint(1Byte)
smallint(2Byte)
mediumint(3Byte)
int(4Byte)
bigint(8Byte)
bad case：int(1)/int(11)
2、不要將數(shù)字存儲(chǔ)為字符串枯怖，字符轉(zhuǎn)化為數(shù)字注整，用int存儲(chǔ)ip而非char(15)

對(duì)于PHP 可以用 long2ip() 和ip2long() 函數(shù)對(duì)IP進(jìn)行轉(zhuǎn)換

3、優(yōu)先使用enum或set度硝，sex enum (‘F’, ‘M’)

本人不推薦使用enum 肿轨，除非真的確定情況絕對(duì)不會(huì)改變，如：性別

4蕊程，避免使用NULL字段

NULL字段很難查詢優(yōu)化
NULL字段的索引需要額外空間
NULL字段的復(fù)合索引無(wú)效
bad case：name char(32) default nullage int not null
good case：age int not null default 0
5椒袍，少用text/blob，varchar的性能會(huì)比text高很多藻茂；實(shí)在避免不了blob驹暑，請(qǐng)拆表

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

7辨赐、對(duì)于MyISAM表优俘，如果沒(méi)有任何變長(zhǎng)列(VARCHAR、TEXT或BLOB列)掀序，使用固定尺寸的記錄格式帆焕。這比較快但是不幸地可能會(huì)浪費(fèi)一些空間。即使你已經(jīng)用CREATE選項(xiàng)讓VARCHAR列ROW_FORMAT=fixed不恭，也可以提示想使用固定長(zhǎng)度的行

不推薦使用myisam引擎

8叶雹、使用sample character set，例如latin1换吧。盡量少使用utf-8折晦，因?yàn)閡tf-8占用的空間是latin1的3倍≌赐撸可以在不需要使用utf-8的字段上面使用latin1筋遭，例如mail，url等

以前沒(méi)有注意過(guò)這一點(diǎn)暴拄。漓滔。。

9乖篷、精確度與空間的轉(zhuǎn)換响驴。在存儲(chǔ)相同數(shù)值范圍的數(shù)據(jù)時(shí)，浮點(diǎn)數(shù)類型通常都會(huì)比DECIMAL類型使用更少的空間撕蔼。FLOAT字段使用4 字節(jié)存儲(chǔ) 數(shù)據(jù)豁鲤。DOUBLE類型需要8 個(gè)字節(jié)并擁有更高的精確度和更大的數(shù)值范圍秽誊，DECIMAL類型的數(shù)據(jù)將會(huì)轉(zhuǎn)換成DOUBLE類型

10、庫(kù)名表名字段名必須有固定的命名長(zhǎng)度琳骡，12個(gè)字符以內(nèi)锅论；庫(kù)名、表名楣号、字段名禁止超過(guò)32個(gè)字符最易。須見(jiàn)名之意；庫(kù)名炫狱、表名藻懒、字段名禁止使用MySQL保留字；臨時(shí)庫(kù)视译、表名必須以tmp為前綴嬉荆，并以日期為后綴； 備份庫(kù)酷含、表必須以bak為前綴鄙早，并以日期為后綴

11、InnoDB表行記錄物理長(zhǎng)度不超過(guò)8KB椅亚，InnoDB的data page默認(rèn)是16KB限番，基于B+Tree的特點(diǎn)，一個(gè)data page中需要至少存儲(chǔ)2條記錄什往。因此，當(dāng)實(shí)際存儲(chǔ)長(zhǎng)度超過(guò)8KB（尤其是TEXT/BLOB列）的大列（large column）時(shí)會(huì)引起“page-overflow存儲(chǔ)”慌闭，類似ORACLE中的“行遷移”别威，因此，如果必須使用大列（尤其是TEXT/BLOB類型）且讀寫頻繁的話驴剔，則最好把這些列拆分到子表中省古，不要和主表放在一起存儲(chǔ)，如果不太頻繁丧失，可以考慮繼續(xù)保留在主表中豺妓，如果將 innodbpagesize 選項(xiàng)修改成 8KB，那么行記錄物理長(zhǎng)度建議不超過(guò)4KB

索引類

1布讹、謹(jǐn)慎合理使用索引

改善查詢琳拭、減慢更新
索引一定不是越多越好（能不加就不加，要加的一定得加）
覆蓋記錄條數(shù)過(guò)多不適合建索引描验，例如“性別”
2白嘁、字符字段必須建前綴索引

3、不在索引做列運(yùn)算膘流，bad case：select id where age +1 = 10;

沒(méi)記錯(cuò)的話再索引列計(jì)算就不會(huì)使用索引絮缅， 大小比較也算計(jì)算

4鲁沥、innodb主鍵推薦使用自增列

主鍵建立聚簇索引
主鍵不應(yīng)該被修改
字符串不應(yīng)該做主鍵
如果不指定主鍵，innodb會(huì)使用唯一且非空值索引代替
5耕魄、不用外鍵画恰，請(qǐng)由程序保證約束

6、避免在已有索引的前綴上建立索引吸奴。例如：如果存在index（a允扇，b）則去掉index（a）

7、控制單個(gè)索引的長(zhǎng)度奄抽。使用key（name（8））在數(shù)據(jù)的前面幾個(gè)字符建立索引

8蔼两、要選擇性的使用索引。在變化很少的列上使用索引并不是很好逞度，例如性別列

9额划、Optimize table可以壓縮和排序index，注意不要頻繁運(yùn)行

10档泽、Analyze table可以更新數(shù)據(jù)

11俊戳、索引選擇性是不重復(fù)的索引值也叫基數(shù)（cardinality）表中數(shù)據(jù)行數(shù)的比值，索引選擇性=基數(shù)/數(shù)據(jù)行馆匿，count(distinct(username))/count(*) 就是索引選擇性抑胎，高索引選擇性的好處就是mysql查找匹配的時(shí)候可以過(guò)濾更多的行，唯一索引的選擇性最佳渐北，值為1

12阿逃、不要用重復(fù)或多余索引，對(duì)于INNODB引擎的索引來(lái)說(shuō)赃蛛，每次修改數(shù)據(jù)都要把主鍵索引恃锉，輔助索引中相應(yīng)索引值修改，這可能會(huì)出現(xiàn)大量數(shù) 據(jù)遷移呕臂，分頁(yè)破托，以及碎片的出現(xiàn)

13、超過(guò)20個(gè)長(zhǎng)度的字符串列歧蒋，最好創(chuàng)建前綴索引而非整列索引（例如：ALTER TABLE t1 ADD INDEX(user(20))）土砂，可以有效提高索引利用率，不過(guò)它的缺點(diǎn)是對(duì)這個(gè)列排序時(shí)用不到前綴索引谜洽。前綴索引的長(zhǎng)度可以基于對(duì)該字段的統(tǒng)計(jì)得出萝映， 一般略大于平均長(zhǎng)度一點(diǎn)就可以了

14、定期用 pt-duplicate-key-checker 工具檢查并刪除重復(fù)的索引阐虚。比如 index idx1(a, b) 索引已經(jīng)涵蓋了 index idx2(a)锌俱，就可以刪除 idx2 索引了

sql語(yǔ)句設(shè)計(jì)類

1、sql語(yǔ)句盡可能簡(jiǎn)單,一條sql只能在一個(gè)cpu運(yùn)算敌呈，大語(yǔ)句拆小語(yǔ)句贸宏，減少鎖時(shí)間造寝，一條大sql可以堵死整個(gè)庫(kù)(充分利用QUERY CACHE和充分利用多核CPU)

2、簡(jiǎn)單的事務(wù),事務(wù)時(shí)間盡可能短,bad case：上傳圖片事務(wù)

3吭练、避免使用trig/func,觸發(fā)器诫龙、函數(shù)不用,客戶端程序取而代之

4、不用select *,消耗cpu鲫咽，io签赃，內(nèi)存，帶寬,這種程序不具有擴(kuò)展性

5分尸、OR改寫為IN()

or的效率是n級(jí)別
in的消息時(shí)log(n)級(jí)別
in的個(gè)數(shù)建議控制在200以內(nèi)
select id from t where phone=’159′ or phone=’136′ =>select id from t where phone in (’159′, ’136′);
6锦聊、OR改寫為UNION

mysql的索引合并很弱智
select id from t where phone = '159' or name = 'john';
=>
select id from t where phone='159' union select id from t where name='jonh';
7、避免負(fù)向%箩绍，如not in/like

8孔庭、慎用count(*)

這句不是很贊同，因?yàn)楹蚦ount(1)相比起來(lái)并沒(méi)有什么明顯的差距材蛛，但是好像都比count(主鍵)好圆到。這部分內(nèi)容《高性能Mysql》這本書中好像也有提到

9、limit高效分頁(yè)

limit越大卑吭，效率越低
select id from t limit 10000, 10;
=>
select id from t where id > 10000 limit 10;

可以使用 延遲關(guān)聯(lián) 優(yōu)化 limit

10芽淡、使用union all替代union，union有去重開(kāi)銷

11豆赏、少用連接join

通過(guò)反范式挣菲，增加重復(fù)數(shù)據(jù)列來(lái)避免使用join

12、使用group by掷邦，分組白胀、自動(dòng)排序

13、請(qǐng)使用同類型比較

14耙饰、使用load data導(dǎo)數(shù)據(jù)纹笼，load data比insert快約20倍

15纹份、對(duì)數(shù)據(jù)的更新要打散后批量更新苟跪，不要一次更新太多數(shù)據(jù)

16、使用性能分析工具

Sql explain / showprofile / mysqlsla

說(shuō)來(lái)慘愧 我只用過(guò) explain

17蔓涧、使用--log-slow-queries –long-query-time=2查看查詢比較慢的語(yǔ)句件已。然后使用explain分析查詢，做出優(yōu)化

show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log;
show processlist;
show query_response_time(percona)
optimize 數(shù)據(jù)在插入元暴，更新篷扩，刪除的時(shí)候難免一些數(shù)據(jù)遷移，分頁(yè)茉盏，之后就出現(xiàn)一些碎片鉴未，久而久之碎片積累起來(lái)影響性能枢冤， 這就需要DBA定期的優(yōu)化數(shù)據(jù)庫(kù)減少碎片，這就通過(guò)optimize命令铜秆。如對(duì)MyISAM表操作：optimize table 表名

18淹真、禁止在數(shù)據(jù)庫(kù)中跑大查詢

19、使預(yù)編譯語(yǔ)句连茧，只傳參數(shù)核蘸，比傳遞SQL語(yǔ)句更高效；一次解析啸驯，多次使用客扎；降低SQL注入概率

20、禁止使用order by rand()

21罚斗、禁止單條SQL語(yǔ)句同時(shí)更新多個(gè)表

我猜問(wèn)題在于 行鎖和某一個(gè)表更新失敗數(shù)據(jù)不可逆問(wèn)題

22徙鱼、避免在數(shù)據(jù)庫(kù)中進(jìn)行數(shù)學(xué)運(yùn)算(MySQL不擅長(zhǎng)數(shù)學(xué)運(yùn)算和邏輯判斷)

23、SQL語(yǔ)句要求所有研發(fā)惰聂，SQL關(guān)鍵字全部是大寫疆偿，每個(gè)詞只允許有一個(gè)空格

24、能不用NOT IN就不用NOTIN搓幌，坑太多了杆故。。會(huì)把空和NULL給查出來(lái)

注意
1溉愁、哪怕是基于索引的條件過(guò)濾处铛，如果優(yōu)化器意識(shí)到總共需要掃描的數(shù)據(jù)量超過(guò)30%時(shí)（ORACLE里貌似是20%，MySQL目前是30%拐揭，沒(méi)準(zhǔn)以后會(huì)調(diào)整）撤蟆，就會(huì)直接改變執(zhí)行計(jì)劃為全表掃描，不再使用索引

2堂污、多表JOIN時(shí)家肯，要把過(guò)濾性最大（不一定是數(shù)據(jù)量最小哦，而是只加了WHERE條件后過(guò)濾性最大的那個(gè)）的表選為驅(qū)動(dòng)表盟猖。此外讨衣，如果JOIN之后有排序，排序字段一定要屬于驅(qū)動(dòng)表式镐，才能利用驅(qū)動(dòng)表上的索引完成排序

3反镇、絕大多數(shù)情況下，排序的代價(jià)通常要來(lái)的更高娘汞，因此如果看到執(zhí)行計(jì)劃中有 Using filesort歹茶，優(yōu)先創(chuàng)建排序索引吧

4、利用 pt-query-digest 定期分析slow query log，并結(jié)合 Box Anemometer 構(gòu)建slow query log分析及優(yōu)化系統(tǒng)

最后 《高性能Mysql》這本書真的非常推薦惊豺，雖然小弟好多看不懂的內(nèi)容