最近生產(chǎn)數(shù)據(jù)庫(kù)頻繁報(bào)警哎媚，主要由于數(shù)據(jù)量越來(lái)越大喇伯，業(yè)務(wù)越來(lái)越復(fù)雜，不斷又有新人的添加拨与，不熟悉業(yè)務(wù)稻据，在加上書(shū)寫(xiě)的sql不規(guī)范，嚴(yán)重影響業(yè)務(wù)的穩(wěn)定性买喧。一個(gè)有問(wèn)題的sql 攀甚，會(huì)導(dǎo)致整個(gè)業(yè)務(wù)異常，下圖非常形象岗喉。綜合大家常見(jiàn)的問(wèn)題秋度，結(jié)合一些書(shū)籍和mysql 官網(wǎng)等整理了一下sql 規(guī)范。
基本規(guī)范
1钱床、同一個(gè)業(yè)務(wù)放入同一個(gè)庫(kù)荚斯，禁止跨庫(kù)連表
2、更新刪除操作，單次操作最多1萬(wàn)條
3事期、不建議使用存儲(chǔ)過(guò)程滥壕、觸發(fā)器、視圖等高級(jí)功能兽泣，數(shù)據(jù)庫(kù)功能單一绎橘，只用來(lái)存儲(chǔ)數(shù)據(jù)，業(yè)務(wù)通過(guò)上層業(yè)務(wù)實(shí)現(xiàn)
4唠倦、對(duì)于統(tǒng)計(jì)功能數(shù)據(jù)讀操作在只讀庫(kù)中進(jìn)行
5称鳞、存儲(chǔ)引擎默認(rèn)使用innodb
6、庫(kù)名稠鼻、表名冈止、字段名稱(chēng)統(tǒng)一小寫(xiě)字母，下劃線(xiàn)’’_’’分割
7候齿、庫(kù)名熙暴、表名、字段名稱(chēng)盡量不超過(guò)12個(gè)字符
8慌盯、表一定要有主鍵
9周霉、建議使用UNSIGNED 存儲(chǔ)非負(fù)數(shù)值
10、使用短數(shù)據(jù)類(lèi)型亚皂，使用TINYINT UNSIGNED
11俱箱、盡可能不使用TEXT、BLOG類(lèi)型孕讳，如果一定要使用建議將此類(lèi)型拆分到擴(kuò)展表
12、varchar(N),N 表示的是字符數(shù)不是字節(jié)數(shù)巍膘；
13厂财、存儲(chǔ)年使用YEAR 類(lèi)型
14、存儲(chǔ)日期使用DATE 類(lèi)型
15峡懈、ipv4 地址使用無(wú)符號(hào)整數(shù)存儲(chǔ)璃饱，可通過(guò)函數(shù)INET_ATON() 和 INET_NTOA（）轉(zhuǎn)化。
16肪康、在存儲(chǔ)時(shí)間滿(mǎn)足業(yè)務(wù)需求的情況下荚恶，建議使用TIMESTAMP
- TIMESTAMP 4字節(jié) DATETIME 8字節(jié)
- 自動(dòng)初始化和更新
- 時(shí)間范圍不一樣
16、建議字段定義為NOT NULL ,并設(shè)置默認(rèn)值
17磷支、禁止存儲(chǔ)圖片谒撼、文件、視頻等文件
18雾狈、禁止使用外鍵
- 大數(shù)據(jù)量下性能差廓潜，每次更新都要去檢查維護(hù)
- 涉及到分表業(yè)務(wù)無(wú)法實(shí)現(xiàn)
- 外鍵會(huì)導(dǎo)致表結(jié)構(gòu)非常混亂，幾乎是動(dòng)都不能去動(dòng)辩蛋，一層套一層的外鍵約束呻畸，在表很多的情況下很可能會(huì)導(dǎo)致循環(huán)約束
- 關(guān)聯(lián)數(shù)據(jù)一致可通過(guò)程序保證
19、禁止使用臨時(shí)表
20悼院、可以添加適當(dāng)?shù)娜哂嘧侄翁岣咝阅?br>
21伤为、不建議一個(gè)字段通過(guò)字符串拼接存入多個(gè)值
22、不建議使用bit 類(lèi)型
23据途、不建議使用enum 類(lèi)型
24绞愚、一般是犧牲寫(xiě)操作提升讀操作性能
25、大量數(shù)據(jù)獲取時(shí)需要分頁(yè)昨凡，pageSize 最大1000
26 爽醋、一個(gè)復(fù)雜的查詢(xún)（尤其復(fù)雜的連表）考慮拆分多次查詢(xún)
27、 連接表的順序不是按照語(yǔ)句順序便脊，優(yōu)化器會(huì)重排
28蚂四、標(biāo)志列類(lèi)型和關(guān)聯(lián)表類(lèi)型一致
29、字段唯一的必須添加唯一索引保證哪痰，唯一索引查詢(xún)效率更高
30遂赠、非唯一索引命名規(guī)則：index_字段名稱(chēng)
31、唯一索引字段名稱(chēng)規(guī)則：unique_index_字段名稱(chēng)
32晌杰、索引中的字段數(shù)不超過(guò)5個(gè)
33跷睦、單張表的索引數(shù)不超過(guò)5個(gè)
34、ORDER BY 肋演、GROUP BY 抑诸、DISTINCT 的字段考慮加入索引
35、連表查詢(xún)時(shí)進(jìn)可能保證ORDER BY和GROUP BY 在一個(gè)表中
36爹殊、建議通過(guò)連接取代子查詢(xún)蜕乡，特殊場(chǎng)景具體選擇
37、合理建立聯(lián)合索引梗夸，避免冗余索引
（a层玲、b、c）相當(dāng)于 (a) (a反症、b) (a辛块、b、c)
38铅碍、使用EXPLAIN 判斷sql語(yǔ)句是否合理使用索引润绵，盡量避免extra列出現(xiàn) Using File Sort,Using Temporary
39、合理利用覆蓋索引
40胞谈、盡量擴(kuò)展索引而不是新建索引
41授药、表及字段命名避免使用關(guān)鍵字,如 order士嚎、type 等
42、id自增長(zhǎng)不要求每個(gè)表都需要悔叽，如果有其他字段作為主鍵更適合需求莱衩，則不要id，主鍵設(shè)為指定為改字段
43娇澎、limit 使用時(shí)笨蚁，offset避免過(guò)大，比如limit 100000,10 等趟庄，會(huì)導(dǎo)致掃描過(guò)多行拉高數(shù)據(jù)庫(kù)cpu,尤其涉及到統(tǒng)計(jì)等功能括细。可通過(guò)主鍵id 限制范圍
需要考慮建立索引的字段
1戚啥、where 條件過(guò)濾的字段
2奋单、group by 、order by 猫十、distinct
3览濒、連接的字段
不需要添加索引的情況
1、where 條件中不含有的字段
2拖云、頻繁更改的字段
3贷笛、數(shù)據(jù)重復(fù)多，只含有幾類(lèi)的值宙项，比如性別乏苦，狀態(tài)字段
索引在以下條件是無(wú)效的
1、優(yōu)化器認(rèn)為全表掃描比使用索引快
2尤筐、復(fù)合索引沒(méi)有按照最左原則
3汇荐、使用like 且以通配符開(kāi)始
4、使用not in 盆繁、not like掀淘、<>、!= 等反向查詢(xún)
5改基、使用is null等判斷為空的條件
6繁疤、hash索引使用了范圍查找
7咖为、索引列含有表達(dá)式或者函數(shù)
Select * from order_info where amout+1>100;
SELECT * FROM order_info WHERE length( user_mobile )>10
8秕狰、由Or 連接的，Or前有索引躁染，Or后沒(méi)有索引
SELECT * FROM order_info WHERE user_mobile ='13700000000' OR amount =1000;
詳細(xì)優(yōu)化方案官網(wǎng)介紹：https://dev.mysql.com/doc/refman/8.0/en/optimization.html
查看詳細(xì)計(jì)劃 explain
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
英語(yǔ)閱讀好的鸣哀，建議大家從官網(wǎng)學(xué)習(xí)掌握，對(duì)于常用優(yōu)化的方案都介紹的都比較詳細(xì)吞彤、權(quán)威我衬，而且隨著新版本的發(fā)布新功能優(yōu)化點(diǎn)也會(huì)更新叹放。
