超全的數據庫建表/SQL/索引規範[SQL 效率/速度 提升/加快]

資料來源: https://mp.weixin.qq.com/s?__biz=MzIwNTc4NTEwOQ==&mid=2247489835&idx=2&sn=52c1d5c1f7e22826fcba5c15948e0c17&chksm=972ac851a05d414724e534b2746bad14cf95e82f00712771a89ecadbea8e8c400350fb636d82&scene=126&sessionid=1605055183&key=cb189423faa3ef6078b1836ef8ed1d6bc98271d3bb72c257797c07e7959904074e37d7ae2f9b73a5cd95e38a5556e95def741333b7532eecee19cd7d9ef1a9c67742dc730d86708ffbdad5e5b8d66264ebc338c27c27047e9c9e94c93b57065a8648fa0f3a541460f814aa36fdf7be5941b2baf8830c6573b2f016c657ded0e4&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10+x64&version=6300002f&lang=zh_TW&exportkey=AqbKdJHDF9MYvFl2U%2B7jRY0%3D&pass_ticket=IeRo11YVVNJ2XZB31JPqM9vyjn5XLBVmL1hQgcLg%2BE8UVXJiNcqTTcOC3WNj3%2FEQ&wx_header=0

一、建表規約
    【強制】（1）存儲引擎必須使用InnoDB
        解讀：InnoDB支持事物、行級鎖、並發性能更好，CPU及內存緩存頁優化使得資源利用率更高。

    【強制】（2）每張表必須設置一個主鍵ID，且這個主鍵ID使用自增主鍵（在滿足需要的情況下盡量短），除非在分庫分錶環境下。
        解讀：由於InnoDB組織數據的方式決定了需要有一個主鍵，而且若是這個主鍵ID是單調遞增的可以有效提高插入的性能，避免過多的頁分裂、減少表碎片提高空間的使用率。而在分庫分錶環境下，則需要統一來分配各個表中的主鍵值，從而避免整個邏輯表中主鍵重複。

    【強制】（3）必須使用utf8mb4字符集
        解讀：在Mysql中的UTF-8並非“真正的UTF-8”，而utf8mb4”才是真正的“UTF-8”。

    【強制】（4） 數據庫表、表字段必須加入中文註釋
        解讀：大家都別懶

    【強制】（5） 庫名、表名、字段名均小寫，下劃線風格，不超過32個字符，必須見名知意，禁止拼音英文混用。
        解讀：約定

    【強制】（6）單表列數目必須小於30，若超過則應該考慮將表拆分
        解讀：單表列數太多使得Mysql服務器處理InnoDB返回數據之間的映射成本太高

    【強制】（7）禁止使用外鍵，如果有外鍵完整性約束，需要應用程序控制
        解讀：外鍵會導致表與表之間耦合，UPDATE與DELETE操作都會涉及相關聯的表，十分影響SQL的性能，甚至會造成死鎖。

    【強制】（8）必須把字段定義為NOT NULL並且提供默認值
        解讀：a、NULL的列使索引/索引統計/值比較都更加複雜，對MySQL來說更難優化b、NULL這種類型Msql內部需要進行特殊處理，增加數據庫處理記錄的複雜性；同等條件下，表中有較多空字段的時候，數據庫的處理性能會降低很多c、NULL值需要更多的存儲空，無論是表還是索引中每行中的NULL的列都需要額外的空間來標識

    【強制】（9）禁用保留字，如DESC、RANGE、MARCH等，請參考Mysql官方保留字。
    【強制】（10）如果存儲的字符串長度幾乎相等，使用CHAR定長字符串類型。
        解讀：能夠減少空間碎片，節省存儲空間。

    【建議】（11）在一些場景下，考慮使用TIMESTAMP代替DATETIME。
        解讀：a、這兩種類型的都能表達”yyyy-MM-dd HH:mm:ss”格式的時間，TIMESTAMP只需要佔用4個字節的長度，可以存儲的範圍為(1970-2038)年，在各個時區，所展示的時間是不一樣的；b、而DATETIME類型占用8個字節，對時區不敏感，可以存儲的範圍為(1001-9999)年。

    【建議】（12）當心自動生成的Schema，建議所有的Schema手動編寫。 **
        解讀：對於一些數據庫客戶端不要太過信任。
        

二、SQL規約

    【建議】 (1)為了充分利用緩存，不允許使用自定義函數、存儲函數、用戶變量。
        解讀：如果查詢中包含任何用戶自定義函數、存儲函數、用戶變量、臨時表、Mysql庫中的系統表，其查詢結果都不會被緩存。比如函數NOW()或者CURRENT_DATE()會因為不同的查詢時間，返回不同的查詢結果。

    【強制】（2）在查詢中指定所需的列，而不是直接使用“ *”返回所有的列
        解讀：a）讀取不需要的列會增加CPU、IO、NET消耗b）不能有效的利用覆蓋索引

    【強制】（3）不允許使用屬性隱式轉換
        解讀：假設我們在手機號列上添加了索引，然後執行下面的SQL會發生什麼？explain SELECT user_name FROM parent WHERE phone=13812345678;很明顯就是索引不生效，會全表掃描。

    【建議】（4）在WHERE條件的屬性上使用函數或者表達式
        解讀：Mysql無法自動解析這種表達式，無法使用到索引。

    【強制】（5）禁止使用外鍵與級聯，一切外鍵概念必須在應用層解決。
        解讀：外鍵與級聯更新適用於單機低並發，不適合分佈式、高並發集群;級聯更新是強阻塞，存在數據庫更新風暴的風險；外鍵影響數據庫的插入速度。

    【建議】（6）應盡量避免在WHERE子句中使用or作為連接條件
        解讀：根據情況可以選擇使用UNION ALL來代替OR

    【強制】（7）不允許使用%開頭的模糊查詢
        解讀：根據索引的最左前綴原理，%開頭的模糊查詢無法使用索引，可以使用ES來做檢索。
        

三、索引規約

    【建議】（1）避免在更新比較頻繁、區分度不高的列上單獨建立索引
        解讀：區分度不高的列單獨創建索引的優化效果很小，但是較為頻繁的更新則會讓索引的維護成本更高

    【強制】（2） JOIN的表不允許超過五個。需要JOIN的字段，數據類型必須絕對一致; 多表關聯查詢時，保證被關聯的字段需要有索引。
        解讀：太多表的JOIN會讓Mysql的優化器更難權衡出一個“最佳”的執行計劃（可能性為表數量的階乘），同時要注意關聯字段的類型、長度、字符編碼等等是否一致。

    【強制】（3）在一個聯合索引中，若第一列索引區分度等於1，那麼則不需要建立聯合索引。
        解讀：索引通過第一列就能夠完全定位的數據，所以聯合索引的後邊部分是不需要的。

    【強制】（4）建立聯合索引時，必須將區分度更高的字段放在左邊
        解讀：區分度更高的列放在左邊，能夠在一開始就有效的過濾掉無用數據。提高索引的效率，相應我們在Mapper中編寫SQL的WHERE條件中有多個條件時，需要先看看當前表是否有現成的聯合索引直接使用，注意各個條件的順序盡量和索引的順序一致。

    【建議】（5）利用覆蓋索引來進行查詢操作，避免回表
        解讀：覆蓋查詢即是查詢只需要通過索引即可拿到所需DATA，而不再需要再次回表查詢，所以效率相對很高。我們在使用EXPLAIN的結果，extra列會出現：”using index”。這裡也要強調一下不要使用“SELECT * ”，否則幾乎不可能使用到覆蓋索引。

    【建議】（6）在較長VARCHAR字段,例如VARCHAR(100)上建立索引時，應指定索引長度，沒必要對全字段建立索引，根據實際文本區分度決定索引長度即可。
        解讀：索引的長度與區分度是一對矛盾體，一般對字符串類型數據，若長度為20的索引，區分度會高達90%以上，則可以考慮創建長度例為20的索引，而非全字段索引。例如可以使用SELECT COUNT(DISTINCT LEFT(lesson_code, 20)) / COUNT(*) FROM lesson;來確定lesson_code字段字符長度為20時文本區分度。

    【建議】（7）如果有ORDER BY的場景，請注意利用索引的有序性。ORDER BY最後的字段是聯合索引的一部分，並且放在索引組合順序的最後，避免出現file_sort的情況，影響查詢性能。
        解讀：1、假設有查詢條件為WHERE a=? and b=? ORDER BY c；存在索引：a_b_c，則此時可以利用索引排序。2、反例：在查詢條件中包含了範圍查詢，那麼索引有序性無法利用，如:WHERE a>10 ORDER BY b;索引a_b無法排序。

    【建議】（8）在where中索引的列不能某個表達式的一部分，也不能是函數的參數。
        解讀：即是某列上已經添加了索引，但是若此列成為表達式的一部分、或者是函數的參數，Mysql無法將此列單獨解析出來，索引也不會生效。

    【建議】 （9）我們在where條件中使用範圍查詢時，索引最多用於一個範圍條件，超過一個則後邊的不走索引。
        解讀：Mysql能夠使用多個範圍條件裡邊的最左邊的第一個範圍查詢，但是後邊的範圍查詢則無法使用。

    【建議】 （10）在多個表進行外連接時，表之間的關聯字段類型必須完全一致
        解讀：當兩個表進行Join時，字段類型若沒有完全一致，則加索引也不會生效，這裡的完全一致包括但不限於字段類型、字段長度、字符集、collection等等