精心整理16條MySQL使用規範，減少80%問題，推薦分享給團隊
上篇文章介紹了如何創建合適的MySQL索引，今天再一塊學一下如何更規範、更合理的使用MySQL？
合理規範的使用MySQL，可以大大減少開發工作量和線上問題，並提升SQL查詢性能。
我精心總結了這16條MySQL規約，分享給大家，歡迎評論指正。
1. 禁止使用select *
阿里開發規範中，有這麼一句話：
**select *** 會查詢表中所有字段，如果表中的字段有更改，必須修改SQL語句，不然就會執行錯誤。
查詢出非必要的字段，徒增磁盤IO和網絡延遲。
2. 用小表驅動大表
關聯查詢的時候，先用小表查到結果，再用結果去大表查詢，可以大大減少連接次數。
比如我們要查詢某個部門下的員工，由於部門數量遠遠小於員工數量。我們可以把部門表當作驅動表，員工表當作被驅動表。
查詢SQL類似這樣：
select * from department
inner join employee
on department.id=employee.department_id
where department_name='部門1';
3. join關聯表不宜過多
join關聯表禁止超過3張，join關聯過多，不但會增加查詢時間，降低查詢性能，還會產生臨時表緩存結果數據，推薦拆成多條小SQL執行。
另外關聯字段的類型一定要保持一致，並且在每張表都要建立關聯字段的索引。
4. 禁止使用左模糊或者全模糊查詢
當我們在SQL查詢使用左模糊或者全模糊匹配的時候，類似下面這樣：
# 左模糊查詢
select * from user where name='%一燈';
# 全模糊查詢
select * from user where name='%一燈%';
根據B+樹的特性，即使我們在name字段上建立了索引，查詢的時候也是無法用到索引的。
5. 索引訪問類型至少達到range級別
索引訪問類型常見的有這幾個級別，從上到下，性能由好到差。
要求SQL索引訪問類型至少要達到range級別，最好到const級別。
6. 更優雅的使用聯合索引
由於聯合索引有最左匹配原則，所以需要優先把區分度高的字段放在最左邊第一列。
比如要統計用戶表中生日字段和性別字段區分度，可以這樣統計：
select
count(distinct birthday)/count(*),
count(distinct gender)/count(*)
from user;
值越大，區分度越高。
出道面試題，下面這條SQL該怎麼創建聯合索引：
select a from table_name where b=1 order by c;
SQL中用到abc三個字段，創建聯合索引的順序是（b,c,a）。
這道題還涉及到另一個知識點，SQL執行的順序：
from > on > join > where > group by > having > select > distinct > order by > limit
7. 注意避免深分頁
MySQL深分頁的時候，查詢性能較差。
select * from user where name='一燈' limit 10000,10;
我們可以採用子查詢的方式進行優化：
select * from user
where id in (
select id from user
where name='一燈'
limit 10000,10
);
這樣可以減少非聚簇索引回表查詢的次數。
8. 單表字段不要超過30個
當單表字段數量過多的時候，加載大量數據也會拖慢查詢性能。
如果字段超過30個，不用看，肯定是表設計的不合理。
這時候，可以拆成多張表，用垂直分表的方式，進行冷熱字段分離。
9. 枚舉字段不要使用字符類型
字符類型會佔用更多的存儲空間，當我們想要存儲枚舉值或者表示是否的時候，可以採用tinyint數值類型，最好採用無符號整數unsigned tinyint。
10. 小數類型禁止使用float和double
在存儲和計算的時候，float 和 double 都存在精度損失的問題，無法得到正確的結果。
所以在涉及到存儲小數的時候，必須使用decimal類型。
11. 所有字段必須設置默認值且不允許為null
字段允許為null，會佔用額外的存儲空間。
索引並不會索引null值，所以查詢null值的時候無法用到索引。
當數值類型允許為null，返回給映射實體類的時候還可能會報空指針異常。
12. 必須創建主鍵，最好是有序數值類型
如果我們自己沒有給表設置主鍵，InnoDB會自動增加一列隱藏的主鍵，我們無法使用到，並且也佔用的更多的存儲空間，所以建表的時候，必須設置主鍵。
有序數值更適合做主鍵，插入數據的時候，由於是有序的，不會頻繁調整B+樹結構，性能更好。
13. 快速判斷是否存在某條記錄
一般我們判斷表中是否存在某條記錄的時候，會使用count函數，然後判斷返回值是否大於1。
select count(*) from user where name='一燈';
InnoDB存儲引擎並沒有像MyIsAm那樣緩存表的總行數，每次查詢都是實時計算的，耗時較長。
我們可以採用limit加快查詢效率：
select id from user where name='一燈' limit 1;
limit 1表示匹配到一條就返回，查詢效率更好，結果集只返回id，還可以用到覆蓋索引。
14. in條件中數量不宜過多
in條件中數量不要超過1000個，不然耗時會非常長，可以拆成多批次查詢。
15. 禁止創建預留字段
無法通過預留字段的名稱判斷這個字段是幹嘛用的。
預留字段的類型不一定合適。
無法為預留字段創建合適的索引。
16. 單表索引數不要超過5個
創建適當的索引可以提高查詢效率，但是過多的索引，不但佔用更多存儲空間，還會拖慢更新SQL的性能。
所以，索引好用，適度即可。
知識點總結：
文章持續更新，可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。
