MySQL 避坑寶典 -- 來自小米的開源工具

rovast發表於2019-03-12
MySql開源工具

前言

最近偶然翻翻一些部落格，發現依然有一些介紹 mysql 常見最佳化場景的東西，甚是有趣。想起了之前在公司做的 SQL 規範相關工作。獨樂了不如眾樂樂，獨學習不如眾分享，跟大家分享下自己在這個環節的一些心得。

之前無非是根據一些經驗和書籍，列出常見的場景。直到有一次看到了小米的開源工具，SOAR，簡直是被震驚的感覺。這個工具透過是 SQL 語法樹的分析，結合小米 DBA 多年經驗的總結，進行了一系列啟發規則的校驗。最後給出 SQL 的最佳化建議，甚是好用。

當然，本篇文章不會介紹 SOAR 的具體使用，我們來聊聊那些 DBA 總結出來的啟發規則。根據啟發規則，大家也能解決平時遇到的相關 SQL 問題。

關於 SOAR 的使用和二次開發，如果大家有興趣，歡迎留言。如果有需要，可以和博主交流~~

-------- 美麗的分割線 -------

摘自： https://github.com/XiaoMi/soar/blob/master...

這是小米 soar 的預設啟發規則彙總，也是 DBA 多年精華總結。熟讀各個案例，對於一般的 MySQL 最佳化有很高的幫助。
如果你不喜歡太理論的東西，或者沒時間去深入，舉一反三學習也未嘗不可。

建議使用 AS 關鍵字顯示宣告一個別名

  • Item: ALI.001
  • Severity: L0
  • Content: 在列或表別名(如"tbl AS alias")中, 明確使用 AS 關鍵字比隱含別名(如"tbl alias")更易懂。
  • Case:
select name from tbl t1 where id < 1000

不建議給列萬用字元'*'設定別名

  • Item: ALI.002
  • Severity: L8
  • Content: 例: "SELECT tbl.* col1, col2"上面這條 SQL 給列萬用字元設定了別名，這樣的SQL可能存在邏輯錯誤。您可能意在查詢 col1, 但是代替它的是重新命名的是 tbl 的最後一列。
  • Case:
select tbl.* as c1,c2,c3 from tbl where id < 1000

別名不要與表或列的名字相同

  • Item: ALI.003
  • Severity: L1
  • Content: 表或列的別名與其真實名稱相同, 這樣的別名會使得查詢更難去分辨。
  • Case:
select name from tbl as tbl where id < 1000

修改表的預設字符集不會改表各個欄位的字符集

  • Item: ALT.001
  • Severity: L4
  • Content: 很多初學者會將 ALTER TABLE tbl_name [DEFAULT] CHARACTER SET 'UTF8' 誤認為會修改所有欄位的字符集，但實際上它只會影響後續新增的欄位不會改表已有欄位的字符集。如果想修改整張表所有欄位的字符集建議使用 ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
  • Case:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

同一張表的多條 ALTER 請求建議合為一條

  • Item: ALT.002
  • Severity: L2
  • Content: 每次表結構變更對線上服務都會產生影響，即使是能夠透過線上工具進行調整也請儘量透過合併 ALTER 請求的試減少操作次數。
  • Case:
ALTER TABLE tbl ADD COLUMN col int, ADD INDEX idx_col (`col`);

刪除列為高危操作，操作前請注意檢查業務邏輯是否還有依賴

  • Item: ALT.003
  • Severity: L0
  • Content: 如業務邏輯依賴未完全消除，列被刪除後可能導致資料無法寫入或無法查詢到已刪除列資料導致程式異常的情況。這種情況下即使透過備份資料回滾也會丟失使用者請求寫入的資料。
  • Case:
ALTER TABLE tbl DROP COLUMN col;

刪除主鍵和外來鍵為高危操作，操作前請與 DBA 確認影響

  • Item: ALT.004
  • Severity: L0
  • Content: 主鍵和外來鍵為關係型資料庫中兩種重要約束，刪除已有約束會打破已有業務邏輯，操作前請業務開發與 DBA 確認影響，三思而行。
  • Case:
ALTER TABLE tbl DROP PRIMARY KEY;

不建議使用前項萬用字元查詢

  • Item: ARG.001
  • Severity: L4
  • Content: 例如 "％foo"，查詢引數有一個前項萬用字元的情況無法使用已有索引。
  • Case:
select c1,c2,c3 from tbl where name like '%foo'

沒有萬用字元的 LIKE 查詢

  • Item: ARG.002
  • Severity: L1
  • Content: 不包含萬用字元的 LIKE 查詢可能存在邏輯錯誤，因為邏輯上它與等值查詢相同。
  • Case:
select c1,c2,c3 from tbl where name like 'foo'

引數比較包含隱式轉換，無法使用索引

  • Item: ARG.003
  • Severity: L4
  • Content: 隱式型別轉換有無法命中索引的風險，在高併發、大資料量的情況下，命不中索引帶來的後果非常嚴重。
  • Case:
SELECT * FROM sakila.film WHERE length >= '60';

IN (NULL)/NOT IN (NULL) 永遠非真

  • Item: ARG.004
  • Severity: L4
  • Content: 正確的作法是 col IN ('val1', 'val2', 'val3') OR col IS NULL
  • Case:
SELECT * FROM tb WHERE col IN (NULL);

IN 要慎用，元素過多會導致全表掃描

  • Item: ARG.005
  • Severity: L1
  • Content: 如：select id from t where num in(1,2,3)對於連續的數值，能用 BETWEEN 就不要用 IN 了：select id from t where num between 1 and 3。而當 IN 值過多時 MySQL 也可能會進入全表掃描導致效能急劇下降。
  • Case:
select id from t where num in(1,2,3)

應儘量避免在 WHERE 子句中對欄位進行 NULL 值判斷

  • Item: ARG.006
  • Severity: L1
  • Content: 使用 IS NULL 或 IS NOT NULL 將可能導致引擎放棄使用索引而進行全表掃描，如：select id from t where num is null;可以在num上設定預設值0，確保表中 num 列沒有 NULL 值，然後這樣查詢： select id from t where num=0;
  • Case:
select id from t where num is null

避免使用模式匹配

  • Item: ARG.007
  • Severity: L3
  • Content: 效能問題是使用模式匹配運算子的最大缺點。使用 LIKE 或正規表示式進行模式匹配進行查詢的另一個問題，是可能會返回意料之外的結果。最好的方案就是使用特殊的搜尋引擎技術來替代 SQL，比如 Apache Lucene。另一個可選方案是將結果儲存起來從而減少重複的搜尋開銷。如果一定要使用SQL，請考慮在 MySQL 中使用像 FULLTEXT 索引這樣的第三方擴充套件。但更廣泛地說，您不一定要使用SQL來解決所有問題。
  • Case:
select c_id,c2,c3 from tbl where c2 like 'test%'

OR 查詢索引列時請儘量使用 IN 謂詞

  • Item: ARG.008
  • Severity: L1
  • Content: IN-list 謂詞可以用於索引檢索，並且最佳化器可以對 IN-list 進行排序，以匹配索引的排序序列，從而獲得更有效的檢索。請注意，IN-list 必須只包含常量，或在查詢塊執行期間保持常量的值，例如外引用。
  • Case:
SELECT c1,c2,c3 FROM tbl WHERE c1 = 14 OR c1 = 17

引號中的字串開頭或結尾包含空格

  • Item: ARG.009
  • Severity: L1
  • Content: 如果 VARCHAR 列的前後存在空格將可能引起邏輯問題，如在 MySQL 5.5中 'a' 和 'a ' 可能會在查詢中被認為是相同的值。
  • Case:
SELECT 'abc '

不要使用 hint，如：sql_no_cache, force index, ignore key, straight join等

  • Item: ARG.010
  • Severity: L1
  • Content: hint 是用來強制 SQL 按照某個執行計劃來執行，但隨著資料量變化我們無法保證自己當初的預判是正確的。
  • Case:
SELECT * FROM t1 USE INDEX (i1) ORDER BY a;

不要使用負向查詢，如：NOT IN/NOT LIKE

  • Item: ARG.011
  • Severity: L3
  • Content: 請儘量不要使用負向查詢，這將導致全表掃描，對查詢效能影響較大。
  • Case:
select id from t where num not in(1,2,3);

一次性 INSERT/REPLACE 的資料過多

  • Item: ARG.012
  • Severity: L2
  • Content: 單條 INSERT/REPLACE 語句批次插入大量資料效能較差，甚至可能導致從庫同步延遲。為了提升效能，減少批次寫入資料對從庫同步延時的影響，建議採用分批次插入的方法。
  • Case:
INSERT INTO tb (a) VALUES (1), (2)

最外層 SELECT 未指定 WHERE 條件

  • Item: CLA.001
  • Severity: L4
  • Content: SELECT 語句沒有 WHERE 子句，可能檢查比預期更多的行(全表掃描)。對於 SELECT COUNT(*) 型別的請求如果不要求精度，建議使用 SHOW TABLE STATUS 或 EXPLAIN 替代。
  • Case:
select id from tbl

不建議使用 ORDER BY RAND()

  • Item: CLA.002
  • Severity: L3
  • Content: ORDER BY RAND() 是從結果集中檢索隨機行的一種非常低效的方法，因為它會對整個結果進行排序並丟棄其大部分資料。
  • Case:
select name from tbl where id < 1000 order by rand(number)

不建議使用帶 OFFSET 的LIMIT 查詢

  • Item: CLA.003
  • Severity: L2
  • Content: 使用 LIMIT 和 OFFSET 對結果集分頁的複雜度是 O(n^2)，並且會隨著資料增大而導致效能問題。採用“書籤”掃描的方法實現分頁效率更高。
  • Case:
select c1,c2 from tbl where name=xx order by number limit 1 offset 20

不建議對常量進行 GROUP BY

  • Item: CLA.004
  • Severity: L2
  • Content: GROUP BY 1 表示按第一列進行 GROUP BY。如果在 GROUP BY 子句中使用數字，而不是表示式或列名稱，當查詢列順序改變時，可能會導致問題。
  • Case:
select col1,col2 from tbl group by 1

ORDER BY 常數列沒有任何意義

  • Item: CLA.005
  • Severity: L2
  • Content: SQL 邏輯上可能存在錯誤; 最多隻是一個無用的操作，不會更改查詢結果。
  • Case:
select id from test where id=1 order by id

在不同的表中 GROUP BY 或 ORDER BY

  • Item: CLA.006
  • Severity: L4
  • Content: 這將強制使用臨時表和 filesort，可能產生巨大效能隱患，並且可能消耗大量記憶體和磁碟上的臨時空間。
  • Case:
select tb1.col, tb2.col from tb1, tb2 where id=1 group by tb1.col, tb2.col

ORDER BY 語句對多個不同條件使用不同方向的排序無法使用索引

  • Item: CLA.007
  • Severity: L2
  • Content: ORDER BY 子句中的所有表示式必須按統一的 ASC 或 DESC 方向排序，以便利用索引。
  • Case:
select c1,c2,c3 from t1 where c1='foo' order by c2 desc, c3 asc

請為 GROUP BY 顯示新增 ORDER BY 條件

  • Item: CLA.008
  • Severity: L2
  • Content: 預設 MySQL 會對 'GROUP BY col1, col2, ...' 請求按如下順序排序 'ORDER BY col1, col2, ...'。如果 GROUP BY 語句不指定 ORDER BY 條件會導致無謂的排序產生，如果不需要排序建議新增 'ORDER BY NULL'。
  • Case:
select c1,c2,c3 from t1 where c1='foo' group by c2

ORDER BY 的條件為表示式

  • Item: CLA.009
  • Severity: L2
  • Content: 當 ORDER BY 條件為表示式或函式時會使用到臨時表，如果在未指定 WHERE 或 WHERE 條件返回的結果集較大時效能會很差。
  • Case:
select description from film where title ='ACADEMY DINOSAUR' order by length-language_id;

GROUP BY 的條件為表示式

  • Item: CLA.010
  • Severity: L2
  • Content: 當 GROUP BY 條件為表示式或函式時會使用到臨時表，如果在未指定 WHERE 或 WHERE 條件返回的結果集較大時效能會很差。
  • Case:
select description from film where title ='ACADEMY DINOSAUR' GROUP BY length-language_id;

建議為表新增註釋

  • Item: CLA.011
  • Severity: L1
  • Content: 為表新增註釋能夠使得表的意義更明確，從而為日後的維護帶來極大的便利。
  • Case:
CREATE TABLE `test1` (`ID` bigint(20) NOT NULL AUTO_INCREMENT,`c1` varchar(128) DEFAULT NULL,PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

將複雜的裹腳布式查詢分解成幾個簡單的查詢

  • Item: CLA.012
  • Severity: L2
  • Content: SQL是一門極具表現力的語言，您可以在單個SQL查詢或者單條語句中完成很多事情。但這並不意味著必須強制只使用一行程式碼，或者認為使用一行程式碼就搞定每個任務是個好主意。透過一個查詢來獲得所有結果的常見後果是得到了一個笛卡兒積。當查詢中的兩張表之間沒有條件限制它們的關係時，就會發生這種情況。沒有對應的限制而直接使用兩張表進行聯結查詢，就會得到第一張表中的每一行和第二張表中的每一行的一個組合。每一個這樣的組合就會成為結果集中的一行，最終您就會得到一個行數很多的結果集。重要的是要考慮這些查詢很難編寫、難以修改和難以除錯。資料庫查詢請求的日益增加應該是預料之中的事。經理們想要更復雜的報告以及在使用者介面上新增更多的欄位。如果您的設計很複雜，並且是一個單一查詢，要擴充套件它們就會很費時費力。不論對您還是專案來說，時間花在這些事情上面不值得。將複雜的義大利麵條式查詢分解成幾個簡單的查詢。當您拆分一個複雜的SQL查詢時，得到的結果可能是很多類似的查詢，可能僅僅在資料型別上有所不同。編寫所有的這些查詢是很乏味的，因此，最好能夠有個程式自動生成這些程式碼。SQL程式碼生成是一個很好的應用。儘管SQL支援用一行程式碼解決複雜的問題，但也別做不切實際的事情。
  • Case:
這是一條很長很長的 SQL，案例略。

不建議使用 HAVING 子句

  • Item: CLA.013
  • Severity: L3
  • Content: 將查詢的 HAVING 子句改寫為 WHERE 中的查詢條件，可以在查詢處理期間使用索引。
  • Case:
SELECT s.c_id,count(s.c_id) FROM s where c = test GROUP BY s.c_id HAVING s.c_id <> '1660' AND s.c_id <> '2' order by s.c_id

刪除全表時建議使用 TRUNCATE 替代 DELETE

  • Item: CLA.014
  • Severity: L2
  • Content: 刪除全表時建議使用 TRUNCATE 替代 DELETE
  • Case:
delete from tbl

UPDATE 未指定 WHERE 條件

  • Item: CLA.015
  • Severity: L4
  • Content: UPDATE 不指定 WHERE 條件一般是致命的，請您三思後行
  • Case:
update tbl set col=1

不要 UPDATE 主鍵

  • Item: CLA.016
  • Severity: L2
  • Content: 主鍵是資料表中記錄的唯一識別符號，不建議頻繁更新主鍵列，這將影響後設資料統計資訊進而影響正常的查詢。
  • Case:
update tbl set col=1

不建議使用 SELECT * 型別查詢

  • Item: COL.001
  • Severity: L1
  • Content: 當表結構變更時，使用 * 萬用字元選擇所有列將導致查詢的含義和行為會發生更改，可能導致查詢返回更多的資料。
  • Case:
select * from tbl where id=1

INSERT/REPLACE 未指定列名

  • Item: COL.002
  • Severity: L2
  • Content: 當表結構發生變更，如果 INSERT 或 REPLACE 請求不明確指定列名，請求的結果將會與預想的不同; 建議使用 “INSERT INTO tbl(col1，col2)VALUES ...” 代替。
  • Case:
insert into tbl values(1,'name')

建議修改自增 ID 為無符號型別

  • Item: COL.003
  • Severity: L2
  • Content: 建議修改自增 ID 為無符號型別
  • Case:
create table test(`id` int(11) NOT NULL AUTO_INCREMENT)

請為列新增預設值

  • Item: COL.004
  • Severity: L1
  • Content: 請為列新增預設值，如果是 ALTER 操作，請不要忘記將原欄位的預設值寫上。欄位無預設值，當表較大時無法線上變更表結構。
  • Case:
CREATE TABLE tbl (col int) ENGINE=InnoDB;

列未新增註釋

  • Item: COL.005
  • Severity: L1
  • Content: 建議對錶中每個列新增註釋，來明確每個列在表中的含義及作用。
  • Case:
CREATE TABLE tbl (col int) ENGINE=InnoDB;

表中包含有太多的列

  • Item: COL.006
  • Severity: L3
  • Content: 表中包含有太多的列
  • Case:
CREATE TABLE tbl ( cols ....);

可使用 VARCHAR 代替 CHAR， VARBINARY 代替 BINARY

  • Item: COL.008
  • Severity: L1
  • Content: 為首先變長欄位儲存空間小，可以節省儲存空間。其次對於查詢來說，在一個相對較小的欄位內搜尋效率顯然要高些。
  • Case:
create table t1(id int,name char(20),last_time date)

建議使用精確的資料型別

  • Item: COL.009
  • Severity: L2
  • Content: 實際上，任何使用 FLOAT, REAL 或 DOUBLE PRECISION 資料型別的設計都有可能是反模式。大多數應用程式使用的浮點數的取值範圍並不需要達到IEEE 754標準所定義的最大/最小區間。在計算總量時，非精確浮點數所積累的影響是嚴重的。使用 SQL 中的 NUMERIC 或 DECIMAL 型別來代替 FLOAT 及其類似的資料型別進行固定精度的小數儲存。這些資料型別精確地根據您定義這一列時指定的精度來儲存資料。儘可能不要使用浮點數。
  • Case:
CREATE TABLE tab2 (p_id  BIGINT UNSIGNED NOT NULL,a_id  BIGINT UNSIGNED NOT NULL,hours float not null,PRIMARY KEY (p_id, a_id))

不建議使用 ENUM 資料型別

  • Item: COL.010
  • Severity: L2
  • Content: ENUM 定義了列中值的型別，使用字串表示 ENUM 裡的值時，實際儲存在列中的資料是這些值在定義時的序數。因此，這列的資料是位元組對齊的，當您進行一次排序查詢時，結果是按照實際儲存的序數值排序的，而不是按字串值的字母順序排序的。這可能不是您所希望的。沒有什麼語法支援從 ENUM 或者 check 約束中新增或刪除一個值；您只能使用一個新的集合重新定義這一列。如果您打算廢棄一個選項，您可能會為歷史資料而煩惱。作為一種策略，改變後設資料——也就是說，改變表和列的定義——應該是不常見的，並且要注意測試和質量保證。有一個更好的解決方案來約束一列中的可選值:建立一張檢查表，每一行包含一個允許在列中出現的候選值；然後在引用新表的舊錶上宣告一個外來鍵約束。
  • Case:
create table tab1(status ENUM('new','in progress','fixed'))

當需要唯一約束時才使用 NULL，僅當列不能有缺失值時才使用 NOT NULL

  • Item: COL.011
  • Severity: L0
  • Content: NULL 和0是不同的，10乘以 NULL 還是 NULL。NULL 和空字串是不一樣的。將一個字串和標準 SQL 中的 NULL 聯合起來的結果還是 NULL。NULL 和 FALSE 也是不同的。AND、OR 和 NOT 這三個布林操作如果涉及 NULL，其結果也讓很多人感到困惑。當您將一列宣告為 NOT NULL 時，也就是說這列中的每一個值都必須存在且是有意義的。使用 NULL 來表示任意型別不存在的空值。 當您將一列宣告為 NOT NULL 時，也就是說這列中的每一個值都必須存在且是有意義的。
  • Case:
select c1,c2,c3 from tbl where c4 is null or c4 <> 1

BLOB 和 TEXT 型別的欄位不可設定為 NULL

  • Item: COL.012
  • Severity: L5
  • Content: BLOB 和 TEXT 型別的欄位不可設定為 NULL
  • Case:
CREATE TABLE `tbl` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `c` longblob, PRIMARY KEY (`id`));

TIMESTAMP 型別未設定預設值

  • Item: COL.013
  • Severity: L4
  • Content: TIMESTAMP 型別未設定預設值
  • Case:
CREATE TABLE tbl( `id` bigint not null, `create_time` timestamp);

為列指定了字符集

  • Item: COL.014
  • Severity: L5
  • Content: 建議列與表使用同一個字符集，不要單獨指定列的字符集。
  • Case:
CREATE TABLE `tb2` ( `id` int(11) DEFAULT NULL, `col` char(10) CHARACTER SET utf8 DEFAULT NULL)

BLOB 型別的欄位不可指定預設值

  • Item: COL.015
  • Severity: L4
  • Content: BLOB 型別的欄位不可指定預設值
  • Case:
CREATE TABLE `tbl` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `c` blob NOT NULL DEFAULT '', PRIMARY KEY (`id`));

整型定義建議採用 INT(10) 或 BIGINT(20)

  • Item: COL.016
  • Severity: L1
  • Content: INT(M) 在 integer 資料型別中，M 表示最大顯示寬度。 在 INT(M) 中，M 的值跟 INT(M) 所佔多少儲存空間並無任何關係。 INT(3)、INT(4)、INT(8) 在磁碟上都是佔用 4 bytes 的儲存空間。
  • Case:
CREATE TABLE tab (a INT(1));

VARCHAR 定義長度過長

  • Item: COL.017
  • Severity: L2
  • Content: varchar 是可變長字串，不預先分配儲存空間，長度不要超過255，如果儲存長度過長 MySQL 將定義欄位型別為 text，獨立出來一張表，用主鍵來對應，避免影響其它欄位索引效率。
  • Case:
CREATE TABLE tab (a varchar(3500));

消除不必要的 DISTINCT 條件

  • Item: DIS.001
  • Severity: L1
  • Content: 太多DISTINCT條件是複雜的裹腳布式查詢的症狀。考慮將複雜查詢分解成許多簡單的查詢，並減少DISTINCT條件的數量。如果主鍵列是列的結果集的一部分，則DISTINCT條件可能沒有影響。
  • Case:
SELECT DISTINCT c.c_id,count(DISTINCT c.c_name),count(DISTINCT c.c_e),count(DISTINCT c.c_n),count(DISTINCT c.c_me),c.c_d FROM (select distinct id, name from B) as e WHERE e.country_id = c.country_id

COUNT(DISTINCT) 多列時結果可能和你預想的不同

  • Item: DIS.002
  • Severity: L3
  • Content: COUNT(DISTINCT col) 計算該列除NULL之外的不重複行數，注意 COUNT(DISTINCT col, col2) 如果其中一列全為 NULL 那麼即使另一列有不同的值，也返回0。
  • Case:
SELECT COUNT(DISTINCT col, col2) FROM tbl;

DISTINCT * 對有主鍵的表沒有意義

  • Item: DIS.003
  • Severity: L3
  • Content: 當表已經有主鍵時，對所有列進行 DISTINCT 的輸出結果與不進行 DISTINCT 操作的結果相同，請不要畫蛇添足。
  • Case:
SELECT DISTINCT * FROM film;

避免在 WHERE 條件中使用函式或其他運算子

  • Item: FUN.001
  • Severity: L2
  • Content: 雖然在 SQL 中使用函式可以簡化很多複雜的查詢，但使用了函式的查詢無法利用表中已經建立的索引，該查詢將會是全表掃描，效能較差。通常建議將列名寫在比較運算子左側，將查詢過濾條件放在比較運算子右側。也不建議在查詢比較條件兩側書寫多餘的括號，這會對閱讀產生比較大的困擾。
  • Case:
select id from t where substring(name,1,3)='abc'

指定了 WHERE 條件或非 MyISAM 引擎時使用 COUNT(*) 操作效能不佳

  • Item: FUN.002
  • Severity: L1
  • Content: COUNT(*) 的作用是統計表行數，COUNT(COL) 的作用是統計指定列非 NULL 的行數。MyISAM 表對於 COUNT(*) 統計全錶行數進行了特殊的最佳化，通常情況下非常快。但對於非 MyISAM 表或指定了某些 WHERE 條件，COUNT(*) 操作需要掃描大量的行才能獲取精確的結果，效能也因此不佳。有時候某些業務場景並不需要完全精確的 COUNT 值，此時可以用近似值來代替。EXPLAIN 出來的最佳化器估算的行數就是一個不錯的近似值，執行 EXPLAIN 並不需要真正去執行查詢，所以成本很低。
  • Case:
SELECT c3, COUNT(*) AS accounts FROM tab where c2 < 10000 GROUP BY c3 ORDER BY num

使用了合併為可空列的字串連線

  • Item: FUN.003
  • Severity: L3
  • Content: 在一些查詢請求中，您需要強制讓某一列或者某個表示式返回非 NULL 的值，從而讓查詢邏輯變得更簡單，擔憂不想將這個值存下來。使用 COALESCE() 函式來構造連線的表示式，這樣即使是空值列也不會使整表示式變為 NULL。
  • Case:
select c1 || coalesce(' ' || c2 || ' ', ' ') || c3 as c from tbl

不建議使用 SYSDATE() 函式

  • Item: FUN.004
  • Severity: L4
  • Content: SYSDATE() 函式可能導致主從資料不一致，請使用 NOW() 函式替代 SYSDATE()。
  • Case:
SELECT SYSDATE();

不建議使用 COUNT(col) 或 COUNT(常量)

  • Item: FUN.005
  • Severity: L1
  • Content: 不要使用 COUNT(col) 或 COUNT(常量) 來替代 COUNT(*), COUNT(*) 是 SQL92 定義的標準統計行數的方法，跟資料無關，跟 NULL 和非 NULL 也無關。
  • Case:
SELECT COUNT(1) FROM tbl;

使用 SUM(COL) 時需注意 NPE 問題

  • Item: FUN.006
  • Severity: L1
  • Content: 當某一列的值全是 NULL 時，COUNT(COL) 的返回結果為0,但 SUM(COL) 的返回結果為 NULL，因此使用 SUM() 時需注意 NPE 問題。可以使用如下方式來避免 SUM 的 NPE 問題: SELECT IF(ISNULL(SUM(COL)), 0, SUM(COL)) FROM tbl
  • Case:
SELECT SUM(COL) FROM tbl;

不建議使用觸發器

  • Item: FUN.007
  • Severity: L1
  • Content: 觸發器的執行沒有反饋和日誌，隱藏了實際的執行步驟，當資料庫出現問題是，不能透過慢日誌分析觸發器的具體執行情況，不易發現問題。在MySQL中，觸發器不能臨時關閉或開啟，在資料遷移或資料恢復等場景下，需要臨時drop觸發器，可能影響到生產環境。
  • Case:
CREATE TRIGGER t1 AFTER INSERT ON work FOR EACH ROW INSERT INTO time VALUES(NOW());

不建議使用儲存過程

  • Item: FUN.008
  • Severity: L1
  • Content: 儲存過程無版本控制，配合業務的儲存過程升級很難做到業務無感知。儲存過程在擴充和移植上也存在問題。
  • Case:
CREATE PROCEDURE simpleproc (OUT param1 INT);

不建議使用自定義函式

  • Item: FUN.009
  • Severity: L1
  • Content: 不建議使用自定義函式
  • Case:
CREATE FUNCTION hello (s CHAR(20));

不建議對等值查詢列使用 GROUP BY

  • Item: GRP.001
  • Severity: L2
  • Content: GROUP BY 中的列在前面的 WHERE 條件中使用了等值查詢，對這樣的列進行 GROUP BY 意義不大。
  • Case:
select film_id, title from film where release_year='2006' group by release_year

JOIN 語句混用逗號和 ANSI 模式

  • Item: JOI.001
  • Severity: L2
  • Content: 表連線的時候混用逗號和 ANSI JOIN 不便於人類理解，並且MySQL不同版本的表連線行為和優先順序均有所不同，當 MySQL 版本變化後可能會引入錯誤。
  • Case:
select c1,c2,c3 from t1,t2 join t3 on t1.c1=t2.c1,t1.c3=t3,c1 where id>1000

同一張表被連線兩次

  • Item: JOI.002
  • Severity: L4
  • Content: 相同的表在 FROM 子句中至少出現兩次，可以簡化為對該表的單次訪問。
  • Case:
select tb1.col from (tb1, tb2) join tb2 on tb1.id=tb.id where tb1.id=1

OUTER JOIN 失效

  • Item: JOI.003
  • Severity: L4
  • Content: 由於 WHERE 條件錯誤使得 OUTER JOIN 的外部表無資料返回，這會將查詢隱式轉換為 INNER JOIN 。如：select c from L left join R using(c) where L.a=5 and R.b=10。這種 SQL 邏輯上可能存在錯誤或程式設計師對 OUTER JOIN 如何工作存在誤解，因為 LEFT/RIGHT JOIN 是 LEFT/RIGHT OUTER JOIN 的縮寫。
  • Case:
select c1,c2,c3 from t1 left outer join t2 using(c1) where t1.c2=2 and t2.c3=4

不建議使用排它 JOIN

  • Item: JOI.004
  • Severity: L4
  • Content: 只在右側表為 NULL 的帶 WHERE 子句的 LEFT OUTER JOIN 語句，有可能是在WHERE子句中使用錯誤的列，如：“... FROM l LEFT OUTER JOIN r ON l.l = r.r WHERE r.z IS NULL”，這個查詢正確的邏輯可能是 WHERE r.r IS NULL。
  • Case:
select c1,c2,c3 from t1 left outer join t2 on t1.c1=t2.c1 where t2.c2 is null

減少 JOIN 的數量

  • Item: JOI.005
  • Severity: L2
  • Content: 太多的 JOIN 是複雜的裹腳布式查詢的症狀。考慮將複雜查詢分解成許多簡單的查詢，並減少 JOIN 的數量。
  • Case:
select bp1.p_id, b1.d_d as l, b1.b_id from b1 join bp1 on (b1.b_id = bp1.b_id) left outer join (b1 as b2 join bp2 on (b2.b_id = bp2.b_id)) on (bp1.p_id = bp2.p_id ) join bp21 on (b1.b_id = bp1.b_id) join bp31 on (b1.b_id = bp1.b_id) join bp41 on (b1.b_id = bp1.b_id) where b2.b_id = 0

將巢狀查詢重寫為 JOIN 通常會導致更高效的執行和更有效的最佳化

  • Item: JOI.006
  • Severity: L4
  • Content: 一般來說，非巢狀子查詢總是用於關聯子查詢，最多是來自FROM子句中的一個表，這些子查詢用於 ANY, ALL 和 EXISTS 的謂詞。如果可以根據查詢語義決定子查詢最多返回一個行，那麼一個不相關的子查詢或來自FROM子句中的多個表的子查詢就被壓平了。
  • Case:
SELECT s,p,d FROM tbl WHERE p.p_id = (SELECT s.p_id FROM tbl WHERE s.c_id = 100996 AND s.q = 1 )

不建議使用聯表刪除或更新

  • Item: JOI.007
  • Severity: L4
  • Content: 當需要同時刪除或更新多張表時建議使用簡單語句，一條 SQL 只刪除或更新一張表，儘量不要將多張表的操作在同一條語句。
  • Case:
UPDATE users u LEFT JOIN hobby h ON u.id = h.uid SET u.name = 'pianoboy' WHERE h.hobby = 'piano';

不要使用跨資料庫的 JOIN 查詢

  • Item: JOI.008
  • Severity: L4
  • Content: 一般來說，跨資料庫的 JOIN 查詢意味著查詢語句跨越了兩個不同的子系統，這可能意味著系統耦合度過高或庫表結構設計不合理。
  • Case:
SELECT s,p,d FROM tbl WHERE p.p_id = (SELECT s.p_id FROM tbl WHERE s.c_id = 100996 AND s.q = 1 )

建議使用自增列作為主鍵，如使用聯合自增主鍵時請將自增鍵作為第一列

  • Item: KEY.001
  • Severity: L2
  • Content: 建議使用自增列作為主鍵，如使用聯合自增主鍵時請將自增鍵作為第一列
  • Case:
create table test(`id` int(11) NOT NULL PRIMARY KEY (`id`))

無主鍵或唯一鍵，無法線上變更表結構

  • Item: KEY.002
  • Severity: L4
  • Content: 無主鍵或唯一鍵，無法線上變更表結構
  • Case:
create table test(col varchar(5000))

避免外來鍵等遞迴關係

  • Item: KEY.003
  • Severity: L4
  • Content: 存在遞迴關係的資料很常見，資料常會像樹或者以層級方式組織。然而，建立一個外來鍵約束來強制執行同一表中兩列之間的關係，會導致笨拙的查詢。樹的每一層對應著另一個連線。您將需要發出遞迴查詢，以獲得節點的所有後代或所有祖先。解決方案是構造一個附加的閉包表。它記錄了樹中所有節點間的關係，而不僅僅是那些具有直接的父子關係。您也可以比較不同層次的資料設計：閉包表，路徑列舉，巢狀集。然後根據應用程式的需要選擇一個。
  • Case:
CREATE TABLE tab2 (p_id  BIGINT UNSIGNED NOT NULL,a_id  BIGINT UNSIGNED NOT NULL,PRIMARY KEY (p_id, a_id),FOREIGN KEY (p_id) REFERENCES tab1(p_id),FOREIGN KEY (a_id) REFERENCES tab3(a_id))

提醒：請將索引屬性順序與查詢對齊

  • Item: KEY.004
  • Severity: L0
  • Content: 如果為列建立複合索引，請確保查詢屬性與索引屬性的順序相同，以便DBMS在處理查詢時使用索引。如果查詢和索引屬性訂單沒有對齊，那麼DBMS可能無法在查詢處理期間使用索引。
  • Case:
create index idx1 on tbl (last_name,first_name)

表建的索引過多

  • Item: KEY.005
  • Severity: L2
  • Content: 表建的索引過多
  • Case:
CREATE TABLE tbl ( a int, b int, c int, KEY idx_a (`a`),KEY idx_b(`b`),KEY idx_c(`c`));

主鍵中的列過多

  • Item: KEY.006
  • Severity: L4
  • Content: 主鍵中的列過多
  • Case:
CREATE TABLE tbl ( a int, b int, c int, PRIMARY KEY(`a`,`b`,`c`));

未指定主鍵或主鍵非 bigint

  • Item: KEY.007
  • Severity: L4
  • Content: 未指定主鍵或主鍵非 bigint，建議將主鍵設定為 bigint unsigned。
  • Case:
CREATE TABLE tbl (a bigint);

ORDER BY 多個列但排序方向不同時可能無法使用索引

  • Item: KEY.008
  • Severity: L4
  • Content: 在 MySQL 8.0之前當 ORDER BY 多個列指定的排序方向不同時將無法使用已經建立的索引。
  • Case:
SELECT * FROM tbl ORDER BY a DESC, b ASC;

新增唯一索引前請注意檢查資料唯一性

  • Item: KEY.009
  • Severity: L0
  • Content: 請提前檢查新增唯一索引列的資料唯一性，如果資料不唯一線上表結構調整時將有可能自動將重複列刪除，這有可能導致資料丟失。
  • Case:
CREATE UNIQUE INDEX part_of_name ON customer (name(10));

全文索引不是銀彈

  • Item: KEY.010
  • Severity: L0
  • Content: 全文索引主要用於解決模糊查詢的效能問題，但需要控制好查詢的頻率和併發度。同時注意調整 ft_min_word_len, ft_max_word_len, ngram_token_size 等引數。
  • Case:
CREATE TABLE `tb` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ip` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`), FULLTEXT KEY `ip` (`ip`) ) ENGINE=InnoDB;

SQL_CALC_FOUND_ROWS 效率低下

  • Item: KWR.001
  • Severity: L2
  • Content: 因為 SQL_CALC_FOUND_ROWS 不能很好地擴充套件，所以可能導致效能問題; 建議業務使用其他策略來替代 SQL_CALC_FOUND_ROWS 提供的計數功能，比如：分頁結果展示等。
  • Case:
select SQL_CALC_FOUND_ROWS col from tbl where id>1000

不建議使用 MySQL 關鍵字做列名或表名

  • Item: KWR.002
  • Severity: L2
  • Content: 當使用關鍵字做為列名或表名時程式需要對列名和表名進行轉義，如果疏忽被將導致請求無法執行。
  • Case:
CREATE TABLE tbl ( `select` int )

不建議使用複數做列名或表名

  • Item: KWR.003
  • Severity: L1
  • Content: 表名應該僅僅表示表裡面的實體內容，不應該表示實體數量，對應於 DO 類名也是單數形式，符合表達習慣。
  • Case:
CREATE TABLE tbl ( `books` int )

不建議使用使用多位元組編碼字元(中文)命名

  • Item: KWR.004
  • Severity: L1
  • Content: 為庫、表、列、別名命名時建議使用英文，數字，下劃線等字元，不建議使用中文或其他多位元組編碼字元。
  • Case:
select col as 列 from tb

INSERT INTO xx SELECT 加鎖粒度較大請謹慎

  • Item: LCK.001
  • Severity: L3
  • Content: INSERT INTO xx SELECT 加鎖粒度較大請謹慎
  • Case:
INSERT INTO tbl SELECT * FROM tbl2;

請慎用 INSERT ON DUPLICATE KEY UPDATE

  • Item: LCK.002
  • Severity: L3
  • Content: 當主鍵為自增鍵時使用 INSERT ON DUPLICATE KEY UPDATE 可能會導致主鍵出現大量不連續快速增長，導致主鍵快速溢位無法繼續寫入。極端情況下還有可能導致主從資料不一致。
  • Case:
INSERT INTO t1(a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

用字元型別儲存IP地址

  • Item: LIT.001
  • Severity: L2
  • Content: 字串字面上看起來像IP地址，但不是 INET_ATON() 的引數，表示資料被儲存為字元而不是整數。將IP地址儲存為整數更為有效。
  • Case:
insert into tbl (IP,name) values('10.20.306.122','test')

日期/時間未使用引號括起

  • Item: LIT.002
  • Severity: L4
  • Content: 諸如“WHERE col <2010-02-12”之類的查詢是有效的SQL，但可能是一個錯誤，因為它將被解釋為“WHERE col <1996”; 日期/時間文字應該加引號。
  • Case:
select col1,col2 from tbl where time < 2018-01-10

一列中儲存一系列相關資料的集合

  • Item: LIT.003
  • Severity: L3
  • Content: 將 ID 儲存為一個列表，作為 VARCHAR/TEXT 列，這樣能導致效能和資料完整性問題。查詢這樣的列需要使用模式匹配的表示式。使用逗號分隔的列表來做多表聯結查詢定位一行資料是極不優雅和耗時的。這將使驗證 ID 更加困難。考慮一下，列表最多支援存放多少資料呢？將 ID 儲存在一張單獨的表中，代替使用多值屬性，從而每個單獨的屬性值都可以佔據一行。這樣交叉表實現了兩張表之間的多對多關係。這將更好地簡化查詢，也更有效地驗證ID。
  • Case:
select c1,c2,c3,c4 from tab1 where col_id REGEXP '[[:<:]]12[[:>:]]'

請使用分號或已設定的 DELIMITER 結尾

  • Item: LIT.004
  • Severity: L1
  • Content: USE database, SHOW DATABASES 等命令也需要使用使用分號或已設定的 DELIMITER 結尾。
  • Case:
USE db

非確定性的 GROUP BY

  • Item: RES.001
  • Severity: L4
  • Content: SQL返回的列既不在聚合函式中也不是 GROUP BY 表示式的列中，因此這些值的結果將是非確定性的。如：select a, b, c from tbl where foo="bar" group by a，該 SQL 返回的結果就是不確定的。
  • Case:
select c1,c2,c3 from t1 where c2='foo' group by c2

未使用 ORDER BY 的 LIMIT 查詢

  • Item: RES.002
  • Severity: L4
  • Content: 沒有 ORDER BY 的 LIMIT 會導致非確定性的結果，這取決於查詢執行計劃。
  • Case:
select col1,col2 from tbl where name=xx limit 10

UPDATE/DELETE 操作使用了 LIMIT 條件

  • Item: RES.003
  • Severity: L4
  • Content: UPDATE/DELETE 操作使用 LIMIT 條件和不新增 WHERE 條件一樣危險，它可將會導致主從資料不一致或從庫同步中斷。
  • Case:
UPDATE film SET length = 120 WHERE title = 'abc' LIMIT 1;

UPDATE/DELETE 操作指定了 ORDER BY 條件

  • Item: RES.004
  • Severity: L4
  • Content: UPDATE/DELETE 操作不要指定 ORDER BY 條件。
  • Case:
UPDATE film SET length = 120 WHERE title = 'abc' ORDER BY title

UPDATE 語句可能存在邏輯錯誤，導致資料損壞

  • Item: RES.005
  • Severity: L4
  • Content: 在一條 UPDATE 語句中，如果要更新多個欄位，欄位間不能使用 AND ，而應該用逗號分隔。
  • Case:
update tbl set col = 1 and cl = 2 where col=3;

永遠不真的比較條件

  • Item: RES.006
  • Severity: L4
  • Content: 查詢條件永遠非真，如果該條件出現在 where 中可能導致查詢無匹配到的結果。
  • Case:
select * from tbl where 1 != 1;

永遠為真的比較條件

  • Item: RES.007
  • Severity: L4
  • Content: 查詢條件永遠為真，可能導致 WHERE 條件失效進行全表查詢。
  • Case:
select * from tbl where 1 = 1;

不建議使用LOAD DATA/SELECT ... INTO OUTFILE

  • Item: RES.008
  • Severity: L2
  • Content: SELECT INTO OUTFILE 需要授予 FILE 許可權，這透過會引入安全問題。LOAD DATA 雖然可以提高資料匯入速度，但同時也可能導致從庫同步延遲過大。
  • Case:
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

請謹慎使用TRUNCATE操作

  • Item: SEC.001
  • Severity: L0
  • Content: 一般來說想清空一張表最快速的做法就是使用TRUNCATE TABLE tbl_name;語句。但TRUNCATE操作也並非是毫無代價的，TRUNCATE TABLE無法返回被刪除的準確行數，如果需要返回被刪除的行數建議使用DELETE語法。TRUNCATE 操作還會重置 AUTO_INCREMENT，如果不想重置該值建議使用 DELETE FROM tbl_name WHERE 1;替代。TRUNCATE 操作會對資料字典新增源資料鎖(MDL)，當一次需要 TRUNCATE 很多表時會影響整個例項的所有請求，因此如果要 TRUNCATE 多個表建議用 DROP+CREATE 的方式以減少鎖時長。
  • Case:
TRUNCATE TABLE tbl_name

不使用明文儲存密碼

  • Item: SEC.002
  • Severity: L0
  • Content: 使用明文儲存密碼或者使用明文在網路上傳遞密碼都是不安全的。如果攻擊者能夠截獲您用來插入密碼的SQL語句，他們就能直接讀到密碼。另外，將使用者輸入的字串以明文的形式插入到純SQL語句中，也會讓攻擊者發現它。如果您能夠讀取密碼，駭客也可以。解決方案是使用單向雜湊函式對原始密碼進行加密編碼。雜湊是指將輸入字串轉化成另一個新的、不可識別的字串的函式。對密碼加密表示式加點隨機串來防禦“字典攻擊”。不要將明文密碼輸入到SQL查詢語句中。在應用程式程式碼中計算雜湊串，只在SQL查詢中使用雜湊串。
  • Case:
create table test(id int,name varchar(20) not null,password varchar(200)not null)

使用DELETE/DROP/TRUNCATE等操作時注意備份

  • Item: SEC.003
  • Severity: L0
  • Content: 在執行高危操作之前對資料進行備份是十分有必要的。
  • Case:
delete from table where col = 'condition'

建議使用 datetime 替換 timestamp 型別

  • Item: SKEY.005
  • Severity: L4
  • Content: 建議使用 datetime 替換 timestamp 型別，且預設值設定為 1970-01-01 00:00:00。 datetime 型別能儲存大範圍的值，從1001年到9999年，且與時區無關。使用8個位元組的儲存空間（比 timestamp 多出4位元組）
  • Case:
CREATE TABLE tbl (a datetime);

缺少資料庫必須欄位 last_update_time 和 is_del

  • Item: SKEY.006
  • Severity: L4
  • Content: 資料庫必須欄位 （`last_update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最後更新時間'; `is_del` TINYINT (1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否刪除 0：未刪除 1：已刪除'）
  • Case:
CREATE TABLE tbl （`last_update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最後更新時間'; `is_del` TINYINT (1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否刪除 0：未刪除 1：已刪除'）;

last_update_time 和 is_del 型別不對

  • Item: SKEY.006a
  • Severity: L4
  • Content: 資料庫必須欄位 （`last_update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最後更新時間'; `is_del` TINYINT (1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否刪除 0：未刪除 1：已刪除'）
  • Case:
CREATE TABLE tbl （`last_update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最後更新時間'; `is_del` TINYINT (1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否刪除 0：未刪除 1：已刪除'）;

不建議使用大欄位 TEXT BLOB

  • Item: SKEY.010
  • Severity: L1
  • Content: BLOB 和 TEXT 都是為儲存很大的資料而設計的字串資料型別，且效能開銷較大，請檢查是否有必要使用
  • Case:
CREATE TABLE tbl （a TEXT）;

整形建議使用 unsigned

  • Item: SKEY.011
  • Severity: L1
  • Content: 請檢查整形是否有負數場景，如無特殊場景，建議使用 unsigned
  • Case:
CREATE TABLE tbl （a int unsigned）;

'!=' 運算子是非標準的

  • Item: STA.001
  • Severity: L0
  • Content: "<>"才是標準SQL中的不等於運算子。
  • Case:
select col1,col2 from tbl where type!=0

庫名或表名點後建議不要加空格

  • Item: STA.002
  • Severity: L1
  • Content: 當使用 db.table 或 table.column 格式訪問表或欄位時，請不要在點號後面新增空格，雖然這樣語法正確。
  • Case:
select col from sakila. film

索引起名不規範

  • Item: STA.003
  • Severity: L1
  • Content: 建議普通二級索引以idx_為字首，唯一索引以uniq_為字首。
  • Case:
select col from now where type!=0

起名時請不要使用字母、數字和下劃線之外的字元

  • Item: STA.004
  • Severity: L1
  • Content: 以字母或下劃線開頭，名字只允許使用字母、數字和下劃線。請統一大小寫，不要使用駝峰命名法。不要在名字中出現連續下劃線'__'，這樣很難辨認。
  • Case:
CREATE TABLE ` abc` (a int);

MySQL 對子查詢的最佳化效果不佳

  • Item: SUB.001
  • Severity: L4
  • Content: MySQL 將外部查詢中的每一行作為依賴子查詢執行子查詢。 這是導致嚴重效能問題的常見原因。這可能會在 MySQL 5.6 版本中得到改善, 但對於5.1及更早版本, 建議將該類查詢分別重寫為 JOIN 或 LEFT OUTER JOIN。
  • Case:
select col1,col2,col3 from table1 where col2 in(select col from table2)

如果您不在乎重複的話，建議使用 UNION ALL 替代 UNION

  • Item: SUB.002
  • Severity: L2
  • Content: 與去除重複的UNION不同，UNION ALL允許重複元組。如果您不關心重複元組，那麼使用UNION ALL將是一個更快的選項。
  • Case:
select teacher_id as id,people_name as name from t1,t2 where t1.teacher_id=t2.people_id union select student_id as id,people_name as name from t1,t2 where t1.student_id=t2.people_id

考慮使用 EXISTS 而不是 DISTINCT 子查詢

  • Item: SUB.003
  • Severity: L3
  • Content: DISTINCT 關鍵字在對元組排序後刪除重複。相反，考慮使用一個帶有 EXISTS 關鍵字的子查詢，您可以避免返回整個表。
  • Case:
SELECT DISTINCT c.c_id, c.c_name FROM c,e WHERE e.c_id = c.c_id

執行計劃中巢狀連線深度過深

  • Item: SUB.004
  • Severity: L3
  • Content: MySQL對子查詢的最佳化效果不佳,MySQL將外部查詢中的每一行作為依賴子查詢執行子查詢。 這是導致嚴重效能問題的常見原因。
  • Case:
SELECT * from tb where id in (select id from (select id from tb))

子查詢不支援LIMIT

  • Item: SUB.005
  • Severity: L8
  • Content: 當前 MySQL 版本不支援在子查詢中進行 'LIMIT & IN/ALL/ANY/SOME'。
  • Case:
SELECT * FROM staff WHERE name IN (SELECT NAME FROM customer ORDER BY name LIMIT 1)

不建議在子查詢中使用函式

  • Item: SUB.006
  • Severity: L2
  • Content: MySQL將外部查詢中的每一行作為依賴子查詢執行子查詢，如果在子查詢中使用函式，即使是semi-join也很難進行高效的查詢。可以將子查詢重寫為OUTER JOIN語句並用連線條件對資料進行過濾。
  • Case:
SELECT * FROM staff WHERE name IN (SELECT max(NAME) FROM customer)

不建議使用分割槽表

  • Item: TBL.001
  • Severity: L4
  • Content: 不建議使用分割槽表
  • Case:
CREATE TABLE trb3(id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE(YEAR(purchased)) (PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (2005) );

請為表選擇合適的儲存引擎

  • Item: TBL.002
  • Severity: L4
  • Content: 建表或修改表的儲存引擎時建議使用推薦的儲存引擎，如：innodb
  • Case:
create table test(`id` int(11) NOT NULL AUTO_INCREMENT)

以DUAL命名的表在資料庫中有特殊含義

  • Item: TBL.003
  • Severity: L8
  • Content: DUAL表為虛擬表，不需要建立即可使用，也不建議服務以DUAL命名錶。
  • Case:
create table dual(id int, primary key (id));

表的初始AUTO_INCREMENT值不為0

  • Item: TBL.004
  • Severity: L2
  • Content: AUTO_INCREMENT不為0會導致資料空洞。
  • Case:
CREATE TABLE tbl (a int) AUTO_INCREMENT = 10;

請使用推薦的字符集

  • Item: TBL.005
  • Severity: L4
  • Content: 表字符集只允許設定為utf8mb4
  • Case:
CREATE TABLE tbl (a int) DEFAULT CHARSET = latin1;

不建議使用檢視

  • Item: TBL.006
  • Severity: L1
  • Content: 不建議使用檢視
  • Case:
create view v_today (today) AS SELECT CURRENT_DATE;

不建議使用臨時表

  • Item: TBL.007
  • Severity: L1
  • Content: 不建議使用臨時表
  • Case:
CREATE TEMPORARY TABLE `work` (`time` time DEFAULT NULL) ENGINE=InnoDB;
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章