20條sql書寫規(guī)范好習慣讓你受益一生

《20條sql書寫規(guī)范好習慣讓你受益一生》由會員分享，可在線閱讀，更多相關(guān)《20條sql書寫規(guī)范好習慣讓你受益一生（9頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、MySQL 中，21 個寫 SQL 的好習慣每一個好習慣都是一筆財富，本文分 SQL 后悔藥、SQL 性能優(yōu)化、SQL 規(guī)范優(yōu)雅三個方向，分享寫 SQL 的 21 個好習慣。1. 寫完 SQL 先 explain 查看執(zhí)行計劃?！維QL 性能優(yōu)化】日常開發(fā)寫 SQL 的時候，盡量養(yǎng)成這個好習慣呀：寫完 SQL 后，用 explain 分析一下，尤其注意走不走索引。explainselectuserid,name,agefromuserwhereuserid=10086orage=18;2. 操作 delete 或者 update 語句，加個 limit。【SQL 后悔藥】在執(zhí)行刪除或者更新語句

2、時，盡量加上 limit，以下面的這條 SQL 為例吧：deletefromeuserwhereage30limit200;因為加了 limit 主要有這些好處： 降低寫錯 SQL 的代價, 你在命令行執(zhí)行這個 SQL 的時候，如果不加 limit，執(zhí)行的時候一個不小心手抖，可能數(shù)據(jù)全刪掉了，如果刪錯了呢？加了 limit 200，就不一樣了。刪錯也只是丟失 200 條數(shù)據(jù)，可以通過 binlog 日志快速恢復(fù)的。 SQL 效率很可能更高，你在 SQL 行中，加了 limit 1，如果第一條就命中目標 return， 沒有 limit 的話，還會繼續(xù)執(zhí)行掃描表。 避免了長事務(wù)，delete 執(zhí)

3、行時，如果 age 加了索引，MySQL 會將所有相關(guān)的行加寫鎖和間隙鎖，所有執(zhí)行相關(guān)行會被鎖住，如果刪除數(shù)量大，會直接影響相關(guān)業(yè)務(wù)無法使用。 數(shù)據(jù)量大的話，容易把 CPU 打滿,如果你刪除數(shù)據(jù)量很大時，不加 limit 限制一下記錄數(shù)，容易把 CPU 打滿，導(dǎo)致越刪越慢的。3. 設(shè)計表的時候，所有表和字段都添加相應(yīng)的注釋。【SQL 規(guī)范優(yōu)雅】這個好習慣一定要養(yǎng)成啦，設(shè)計數(shù)據(jù)庫表的時候，所有表和字段都添加相應(yīng)的注釋，后面更容易維護。正例：CREATETABLEaccount(idint(11)NOTNULLAUTO_INCREMENTCOMMENT主鍵Id,namevarchar(255)DE

4、FAULTNULLCOMMENT賬戶名,balanceint(11)DEFAULTNULLCOMMENT余額,create_timedatetimeNOTNULLCOMMENT創(chuàng)建時間,update_timedatetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT更新時間,PRIMARYKEY(id),KEYidx_name(name)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT=賬戶表;反例：CREATETABLEa

5、ccount(idint(11)NOTNULLAUTO_INCREMENT,namevarchar(255)DEFAULTNULL,balanceint(11)DEFAULTNULL,create_timedatetimeNOTNULL,update_timedatetimeNOTNULLONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(id),KEYidx_name(name)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8;4. SQL 書寫格式，關(guān)鍵字大小保持一致，使用縮進?！維QL

6、 規(guī)范優(yōu)雅】正例：SELECTstu.name,sum(stu.score)FROMStudentstuWHEREstu.classNo=1班GROUPBYstu.name反例：SELECTstu.name,sum(stu.score)fromStudentstuWHEREstu.classNo=1班groupbystu.name.顯然，統(tǒng)一關(guān)鍵字大小寫一致，使用縮進對齊，會使你的 SQL 看起來更優(yōu)雅5. INSERT 語句標明對應(yīng)的字段名稱?！維QL 規(guī)范優(yōu)雅】反例：insertintoStudentvalues(666,撿田螺的小男孩,100);正例：insertintoStudent(

7、student_id,name,score)values(666,撿田螺的小男孩,100);6. 變更 SQL 操作先在測試環(huán)境執(zhí)行，寫明詳細的操作步驟以及回滾方案，并在上生產(chǎn)前 review。【SQL 后悔藥】 變更 SQL 操作先在測試環(huán)境測試，避免有語法錯誤就放到生產(chǎn)上了。 變更SQL操作需要寫明詳細操作步驟，尤其有依賴關(guān)系的時候，如：先修改表結(jié)構(gòu)再補充對應(yīng)的數(shù)據(jù)。 變更SQL操作要有回滾方案，并在上生產(chǎn)前，review 對應(yīng)變更 SQL。7. 設(shè)計數(shù)據(jù)庫表的時候，加上三個字段：主鍵、create_time、update_time。【SQL 規(guī)范優(yōu)雅】反例：CREATETABLEacco

8、unt(namevarchar(255)DEFAULTNULLCOMMENT賬戶名,balanceint(11)DEFAULTNULLCOMMENT余額,)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT=賬戶表;正例：CREATETABLEaccount(idint(11)NOTNULLAUTO_INCREMENTCOMMENT主鍵Id,namevarchar(255)DEFAULTNULLCOMMENT賬戶名,balanceint(11)DEFAULTNULLCOMMENT余額

9、,create_timedatetimeNOTNULLCOMMENT創(chuàng)建時間,update_timedatetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT更新時間,PRIMARYKEY(id),KEYidx_name(name)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT=賬戶表;理由： 主鍵一般都要加上的，沒有主鍵的表是沒有靈魂的。 創(chuàng)建時間和更新時間的話，還是建議加上吧，詳細審計、跟蹤記錄，都是有用的。阿里開發(fā)手

10、冊也提到這個點，如圖：8. 寫完 SQL 語句，檢查 where、order by、group by 后面的列，多表關(guān)聯(lián)的列是否已加索引，優(yōu)先考慮組合索引?！維QL 性能優(yōu)化】反例：select*fromuserwhereaddress=深圳orderbyage;正例：添加索引altertableuseraddindexidx_address_age(address,age)9. 修改或刪除重要數(shù)據(jù)前，要先備份，先備份，先備份。【SQL 后悔藥】如果要修改或刪除數(shù)據(jù)，在執(zhí)行 SQL 前一定要先備份要修改的數(shù)據(jù)，萬一誤操作，還能吃口后悔藥10. where 后面的字段，留意其數(shù)據(jù)類型的隱式轉(zhuǎn)換。

11、【SQL 性能優(yōu)化】反例：/userid 是varchar字符串類型select*fromuserwhereuserid=123;正例：select*fromuserwhereuserid=123;理由： 因為不加單引號時，是字符串跟數(shù)字的比較，它們類型不匹配，MySQL 會做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點數(shù)再做比較，最后導(dǎo)致索引失效。11. 盡量把所有列定義為 NOT NULL?！維QL 規(guī)范優(yōu)雅】 NOT NULL 列更節(jié)省空間，NULL 列需要一個額外字節(jié)作為判斷是否為 NULL 的標志位。 NULL 列需要注意空指針問題，NULL 列在計算和比較的時候，需要注意空指針問題。12. 修

12、改或者刪除 SQL，先寫 WHERE 查一下，確認后再補充 delete 或 update?！維QL 后悔藥】尤其在操作生產(chǎn)的數(shù)據(jù)時，遇到修改或者刪除的 SQL，先加個 where 查詢一下，確認 OK 之后，再執(zhí)行 update 或者 delete 操作。13. 減少不必要的字段返回，如使用 select 代替 select *?！維QL 性能優(yōu)化】反例：select*fromemployee;正例：selectid，namefromemployee;理由： 節(jié)省資源，減少網(wǎng)絡(luò)開銷。 可能用到覆蓋索引，減少回表，提高查詢效率。14. 所有表必須使用 Innodb 存儲引擎。【SQL 規(guī)范優(yōu)雅

13、】Innodb支持事務(wù)，支持行級鎖，更好的恢復(fù)性，高并發(fā)下性能更好，所以呢，沒有特殊要求（即 Innodb 無法滿足的功能如：列存儲，存儲空間數(shù)據(jù)等）的情況下，所有表必須使用 Innodb 存儲引擎。15. 數(shù)據(jù)庫和表的字符集盡量統(tǒng)一使用 UTF8?！維QL 規(guī)范優(yōu)雅】盡量統(tǒng)一使用 UTF8 編碼。 可以避免亂碼問題。 可以避免不同字符集比較轉(zhuǎn)換所導(dǎo)致的索引失效問題。如果需要存儲表情，那么選擇 utf8mb4 來進行存儲，注意它與 utf-8 編碼的區(qū)別。16. 盡量使用 varchar 代替 char?！維QL 性能優(yōu)化】反例：deptNamechar(100)DEFAULTNULLCOMM

14、ENT部門名稱正例：deptNamevarchar(100)DEFAULTNULLCOMMENT部門名稱理由： 因為首先變長字段存儲空間小，可以節(jié)省存儲空間。17. 如果修改字段含義或?qū)ψ侄伪硎镜臓顟B(tài)追加時，需要及時更新字段注釋?！維QL 規(guī)范優(yōu)雅】這個點，是阿里開發(fā)手冊中 MySQL 的規(guī)約。你的字段，尤其是表示枚舉狀態(tài)時，如果含義被修改了，或者狀態(tài)追加時，為了后面更好維護，需要及時更新字段的注釋。18. SQL 命令行修改數(shù)據(jù)，養(yǎng)成 begin + commit 事務(wù)的習慣?！維QL 后悔藥】正例：begin;updateaccountsetbalance=1000000wherename

15、=撿田螺的小男孩;commit;反例：updateaccountsetbalance=1000000wherename=撿田螺的小男孩;19. 索引命名要規(guī)范，主鍵索引名為 pk_ 字段名；唯一索引名為 uk _字段名 ；普通索引名則為 idx _字段名?！維QL 規(guī)范優(yōu)雅】說明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的簡稱。20. WHERE 從句中不對列進行函數(shù)轉(zhuǎn)換和表達式計算?！維QL 性能優(yōu)化】假設(shè) loginTime 加了索引。反例：selectuserId,loginTimefromloginuserwhereDate_AD

16、D(loginTime,Interval7DAY)=now();正例：explainselectuserId,loginTimefromloginuserwhereloginTime=Date_ADD(NOW(),INTERVAL-7DAY);理由： 索引列上使用 MySQL 的內(nèi)置函數(shù)，索引失效。21. 如果修改/更新數(shù)據(jù)過多，考慮批量進行。【SQL 性能優(yōu)化】反例：deletefromaccountlimit100000;正例：foreach(200次)deletefromaccountlimit500;理由： 大批量操作會造成主從延遲。 大批量操作會產(chǎn)生大事務(wù)，阻塞。 大批量操作，數(shù)據(jù)量過大，會把 CPU 打滿。參考與感謝