SQL規(guī)范落地實踐

本文由707同學供稿。

1. 概述

數據庫在各類生產系統(tǒng)中是不可或缺的中間件，SQL代碼作為操作數據庫的標準語法，在日常開發(fā)中使用比例非常高，幾乎每個批次都會有產品涉及，但各開發(fā)人員對SQL開發(fā)技能的掌握程度參差不齊。

為了規(guī)避開發(fā)技能不足，而引發(fā)SQL質量問題的風險，在最大程度上規(guī)范開發(fā)方法，由數據庫專家團隊從歷史經驗和業(yè)界優(yōu)秀實踐中總結出一套SQL代碼開發(fā)規(guī)范。

然而，無法落地的規(guī)范，只能是空中樓閣，為了能夠讓規(guī)范順利落地，我們通過將規(guī)范內化在工具中，將一條條規(guī)范條文具象化、可驗證化，以檢查開發(fā)人員提交的SQL代碼質量。

2. 規(guī)范

2.1 整體介紹

為了更好地指導產品SQL設計及開發(fā)，避免不恰當的設計、開發(fā)帶來問題和隱患，同時為了提升開發(fā)人員對SQL相關知識的掌握程度，制定了若干SQL規(guī)范。

本規(guī)范分為SQL設計規(guī)范和SQL開發(fā)規(guī)范兩個部分。SQL設計規(guī)范重點關注在設計階段需要考慮的庫、表、字段、索引設計，通過充分設計降低后續(xù)工程階段正向及反向實施成本。SQL開發(fā)規(guī)范重點關注編碼、DDL、DML、查詢優(yōu)化，通過明確的規(guī)則指導編寫合理、高效的SQL語句。

本實踐落地的SQL規(guī)范為開發(fā)規(guī)范，具體規(guī)范如下，包含DML、DQL和DDL，并且規(guī)范分為三個級別：強制、推薦和參考，強制表示必須按照規(guī)范實現(xiàn)，推薦表示建議按照規(guī)范實現(xiàn)，參考表示僅提供參考。

2.2 DML與DQL規(guī)范示例

【強制】SQL關鍵字大寫

【強制】INSERT語句必須要插入的字段名稱

【強制】數據行刪除/更新使用delete/update時，必須帶上WHERE子句

【強制】禁止在UPDATE語句中，將“,”寫成AND

【推薦】如果需要清除全表數據，建議使用TRUNCATE TABLE刪除所有的行

【推薦】避免使用REPLACE。先采用SELECT判斷是否存在記錄，然后再考慮INSERT或UPDATE

【參考】如無必要鎖定數據，則應避免使用FOR UPDATE

【強制】禁止使用SELECT * 查詢

【強制】WHERE 條件中的過濾條件字段上嚴禁使用任何函數,包括數據類型轉換函數

【強制】多表關聯(lián)查詢時，避免使用非索引字段作為關聯(lián)條件

【強制】禁止使用ORDER BY RAND()

【強制】進行模糊查詢時，避免使用左模糊或者全模糊匹配。根據最左前綴原則合理安排查詢條件

【推薦】避免使用COUNT(*)作為查詢字段

【推薦】相同字段的OR條件大于3個，建議使用IN代替

【推薦】不同字段的OR條件大于3個，建議使用使用UNION ALL代替

【推薦】盡量避免在SELECT子句中使用子查詢，替換為連接查詢

【推薦】考慮使用IN替代EXISTS做嵌套查詢

【推薦】必須進行表關聯(lián)查詢時，控制關聯(lián)表的個數不超過兩個

【推薦】外連接的 SQL 語句,建議一律寫成LEFT JOIN（左側為主表），而不要使用 RIGHT JOIN

【推薦】對MIN(), MAX()等聚合函數，建議利用數據的有序性配合LIMIT 1將SQL等價轉化

【推薦】使用WHERE子句代替HAVING子句

【強制】分頁查詢語句全部都需要帶有排序條件，除非業(yè)務方明確要求不要使用任何排序來隨機展示數據

【強制】多表 JOIN 的分頁語句，如果過濾條件在單個表上，先利用索引在子查詢中通過分頁限定數據范圍，再 JOIN

【推薦】大數據量分頁查詢時，避免直接使用數據庫提供的分頁命令LIMIT m,n

【強制】SQL語法錯誤導致的異常

2.3 DDL規(guī)范示例

【強制】避免使用存儲過程、觸發(fā)器、函數等，容易將業(yè)務邏輯和數據庫耦合在一起；

【強制】所有的數據庫對象命名，只使用小寫字母、數字和下劃線的組合，并以字母開頭。

【強制】禁止使用SQL關鍵字進行數據庫對象命名。

【強制】所有的數據庫對象命名，長度不要超過32個字符。

【推薦】采用如下規(guī)則進行索引命名：

非唯一索引按照“idx_字段名稱_字段名稱[_字段名]”進行命名；

唯一索引按照“uk_字段名稱_字段名稱[_字段名]”進行命名；

主鍵按照：pk_表名稱。

【強制】明確指定數據庫默認的字符集和校驗規(guī)則；

【推薦】所有表統(tǒng)一使用utf8字符集，排序規(guī)則采用utf8_general_ci。特殊情況如：需要存Emoji表情，則可選utf8mb4，校對規(guī)則采用對應的utf8mb4_general_ci。

【參考】控制單表字段個數不要超過50個。

【強制】存儲TEXT類型的字段時，獨立出來一張表，用主鍵來對應，避免影響其它字段索引效率。

【推薦】建表必備三個字段：id, create_time, update_time.

【推薦】如果可能，字段盡量使用NOT NULL屬性，并且設置默認值。

【推薦】如果變長字符型長度超過2000，采用TEXT類型。

【強制】InnoDB引擎表必須設置主鍵。

【強制】禁止使用外鍵。

【強制】在varchar字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據實際文本區(qū)分度決定索引長度。

【推薦】采用自增整型字段作為InnoDB引擎表的主鍵。

【推薦】避免冗余索引：避免在主鍵列上重復建立索引；根據最左前綴原則避免重復索引。

【強制】對表的多次ALTER操作合并為一次操作

3. 檢查規(guī)范落地

3.1 落地方式

在設計過程中，考慮到以工具來實現(xiàn)，既能讓開發(fā)環(huán)境本地自測，也可以通過DevOps平臺自動回歸檢查，并且盡量對工程減少入侵。故采用Maven插件的形式來提供支持，對原工程業(yè)務代碼無任何入侵，且插件只在編譯構建階段生效，不會對服務的執(zhí)行產生任何影響。該方式無論在本地配置還是在DevOps平臺配置均可方便使用，避免對開發(fā)人員造成額外的工作負擔。該工具的核心思想與編碼設計上次已經分享過，詳見文章動手擼一個SQL規(guī)范檢查工具。

3.2 架構設計

本SQL檢查工具針對使用Mybatis框架的工程，架構由兩部分組成，分別是核心模塊和插件模塊，將上層插件與核心拆分開，而非形成單體結構，可最大化增加可擴展性。

核心部分負責SQL的解析，最重要的是DDL、DML和DQL三種類型SQL規(guī)則，根據前文中的規(guī)范編寫落地為對應的語法規(guī)則，一條規(guī)則對應一個類文件，若規(guī)則有擴充可便捷地向核心模塊追加。

插件部分目前為Maven形式，以核心作為支撐，插件在編譯階段運行時會調起核心模塊，依次檢查所有的規(guī)則，未來可根據需求擴展為其他形式的插件。

3.3 執(zhí)行邏輯

收集SQL語句：掃描代碼中Mybatis相關的mapper配置文件，比如位于資源文件夾中的配置文件resources/mapper/*.xml，識別出所有SQL語句，供后續(xù)進行分析。

語法分析：根據SQL語法規(guī)則，對SQL語句進行語法分析，提取出SQL語句各關鍵字元素，并進行中間結果分類保存，再做進一步分析。

規(guī)范檢查：

1）靜態(tài)檢查

根據預先設計好的語法檢查規(guī)則，對SQL語句進行靜態(tài)代碼檢查，逐條進行分析掃描，得到每條規(guī)則的評判結果，進行記錄。

2）動態(tài)檢查

有一些規(guī)則依賴于真實的數據庫，僅憑SQL靜態(tài)檢查無法完全覆蓋，故在仿真生產環(huán)境的鏡像庫，對SQL語句進行重放，識別對數據庫表記錄增刪改查操作耗時時長，識別慢SQL。收集SQL執(zhí)行計劃，分析是否為最優(yōu)執(zhí)行計劃。

3.4 報告展示

規(guī)范檢測很重要，但是結果的展示也同樣重要，具有一種設計優(yōu)良的可視化展示形式是非常重要的。本工具提供了多種展示形式，包括終端展示、Json報文結構展示、Html頁面展示三種，并且提供了方便的可擴展點，通過開發(fā)新的Appender即可添加新的展示形式。報告結果中會有所有檢測出的規(guī)范問題，以及解決方案，用戶可以根據提示對SQL進行整改。

此外，還提供了相應的儀表盤網站，頁面中可展示所有產品的檢測結果匯總和詳情。可通過該站查看所有批次缺陷趨勢，某個批次各產品的缺陷分布，某個產品的各批次缺陷數量趨勢，以及某批次某產品各種缺陷類型的分布情況。用戶通過該網站可查看各產品缺陷增長和缺陷修復情況，并可以按照各批次和各產品篩選缺陷情況，從多個維度監(jiān)測各產品SQL規(guī)范情況。從各產品的排名可以起到正向的督促監(jiān)督作用，有對比競爭能夠極大激發(fā)大家修改不規(guī)范項的欲望，促進SQL質量的穩(wěn)步提升。

3.5 DevOps自動化

SQL檢查是一個持續(xù)的過程，需要在開發(fā)過程中不斷地進行，我們可以通過CI流水線進行集成，在執(zhí)行Maven構建的命令中添加SQL檢查插件的執(zhí)行命令，按照一定的構建規(guī)則，可以持續(xù)向儀表盤上推送數據。這樣就形成一個持續(xù)不斷的流式SQL檢查結果，可實時統(tǒng)計出缺陷情況。

4. 總結

通過規(guī)范的制定、規(guī)范的開發(fā)、規(guī)范的結果展示和規(guī)范的自動化檢查，一系列的實踐成功將SQL規(guī)范落地，本規(guī)范的落地標志著這種方式的探索初見成效，是一種可行的方案。SQL規(guī)范僅僅是一個開始，未來更多的規(guī)范同樣可以以這種方式落地，并最終開花結果。