上篇文章介紹了如何創建合適的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的性能，
所以，索引好用，適度即可，
知識點總結：
文章持續更新，可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術干貨，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/500863.html
標籤：MySQL
下一篇：lamp