在 MySQL 中添忘，有很多看上去邏輯相同察皇，但性能卻差異巨大的 SQL 語句不傅。對這些語句使用不當?shù)脑捚劬桑蜁唤?jīng)意間導(dǎo)致整個數(shù)據(jù)庫的壓力變大。

本文選了三個這樣的案例蛤签。希望再遇到相似的問題時，你可以做到舉一反三栅哀、快速解決問題震肮。

案例一：條件字段函數(shù)操作

• 假設(shè)你現(xiàn)在維護了一個交易系統(tǒng)，其中交易記錄表 tradelog 包含交易流水號（tradeid）留拾、交易員 id（operator）戳晌、交易時間（t_modified）等字段。為了便于描述痴柔，我們先忽略其他字段沦偎。這個表的建表語句如下：

mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• 假設(shè)，現(xiàn)在已經(jīng)記錄了從 2016 年初到 2018 年底的所有數(shù)據(jù)咳蔚，運營部門有一個需求是豪嚎，要統(tǒng)計發(fā)生在所有年份中 7 月份的交易記錄總數(shù)。這個邏輯看上去并不復(fù)雜谈火，你的 SQL 語句可能會這么寫：

mysql> select count(*) from tradelog where month(t_modified)=7;

• 由于 t_modified 字段上有索引侈询，于是你就很放心地在生產(chǎn)庫中執(zhí)行了這條語句，但卻發(fā)現(xiàn)執(zhí)行了特別久糯耍，才返回了結(jié)果扔字。
• 如果你問 DBA 同事為什么會出現(xiàn)這樣的情況，他大概會告訴你：如果對字段做了函數(shù)計算温技，就用不上索引了革为，這是 MySQL 的規(guī)定。
• 現(xiàn)在你已經(jīng)學過了 InnoDB 的索引結(jié)構(gòu)了舵鳞，可以再追問一句為什么震檩？為什么條件是 where t_modified='2018-7-1’的時候可以用上索引，而改成 where month(t_modified)=7 的時候就不行了系任？
• 下面是這個 t_modified 索引的示意圖恳蹲。方框上面的數(shù)字就是 month() 函數(shù)對應(yīng)的值虐块。

t_modified索引示意圖

• 如果你的 SQL 語句條件用的是 where t_modified='2018-7-1’的話，引擎就會按照上面綠色箭頭的路線嘉蕾，快速定位到 t_modified='2018-7-1’需要的結(jié)果贺奠。實際上，B+ 樹提供的這個快速定位能力错忱，來源于同一層兄弟節(jié)點的有序性儡率。但是，如果計算 month() 函數(shù)的話以清，你會看到傳入 7 的時候儿普，在樹的第一層就不知道該怎么辦了。
• 也就是說掷倔，對索引字段做函數(shù)操作眉孩，可能會破壞索引值的有序性，因此優(yōu)化器就決定放棄走樹搜索功能勒葱。
• 需要注意的是浪汪，優(yōu)化器并不是要放棄使用這個索引。在這個例子里凛虽，放棄了樹搜索功能死遭，優(yōu)化器可以選擇遍歷主鍵索引，也可以選擇遍歷索引 t_modified凯旋，優(yōu)化器對比索引大小后發(fā)現(xiàn)呀潭，索引 t_modified 更小，遍歷這個索引比遍歷主鍵索引來得更快至非。因此最終還是會選擇索引 t_modified钠署。

explain示意圖

• key="t_modified"表示的是，使用了 t_modified 這個索引荒椭；我在測試表數(shù)據(jù)中插入了 10 萬行數(shù)據(jù)踏幻，rows=100335，說明這條語句掃描了整個索引的所有值戳杀；Extra 字段的 Using index该面，表示的是使用了覆蓋索引。
• 也就是說信卡，由于在 t_modified 字段加了 month() 函數(shù)操作隔缀，導(dǎo)致了全索引掃描。為了能夠用上索引的快速定位能力傍菇，我們就要把 SQL 語句改成基于字段本身的范圍查詢猾瘸。按照下面這個寫法，優(yōu)化器就能按照我們預(yù)期的，用上 t_modified 索引的快速定位能力了牵触。

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

• 當然淮悼，如果你的系統(tǒng)上線時間更早，或者后面又插入了之后年份的數(shù)據(jù)的話揽思，你就需要再把其他年份補齊袜腥。到這里我給你說明了，由于加了 month() 函數(shù)操作钉汗，MySQL 無法再使用索引快速定位功能羹令，而只能使用全索引掃描。
• 不過優(yōu)化器在個問題上確實有“偷懶”行為损痰，即使是對于不改變有序性的函數(shù)福侈，也不會考慮使用索引。比如卢未，對于 select * from tradelog where id + 1 = 10000 這個 SQL 語句肪凛，這個加 1 操作并不會改變有序性，但是 MySQL 優(yōu)化器還是不能用 id 索引快速定位到 9999 這一行辽社。所以显拜，需要你在寫 SQL 語句的時候，手動改寫成 where id = 10000 -1 才可以爹袁。

案例二：隱式類型轉(zhuǎn)換

mysql> select * from tradelog where tradeid=110717;

• 交易編號 tradeid 這個字段上，本來就有索引矮固，但是 explain 的結(jié)果卻顯示失息，這條語句需要走全表掃描。你可能也發(fā)現(xiàn)了档址，tradeid 的字段類型是 varchar(32)盹兢，而輸入的參數(shù)卻是整型，所以需要做類型轉(zhuǎn)換守伸。
  那么绎秒，現(xiàn)在這里就有兩個問題：
  • 數(shù)據(jù)類型轉(zhuǎn)換的規(guī)則是什么？
  • 為什么有數(shù)據(jù)類型轉(zhuǎn)換尼摹，就需要走全索引掃描见芹？
• 你可能會說，數(shù)據(jù)庫里面類型這么多蠢涝，這種數(shù)據(jù)類型轉(zhuǎn)換規(guī)則更多玄呛，我記不住，應(yīng)該怎么辦呢和二？這里有一個簡單的方法徘铝，看 select “10” > 9 的結(jié)果：
  • 如果規(guī)則是“將字符串轉(zhuǎn)成數(shù)字”，那么就是做數(shù)字比較，結(jié)果應(yīng)該是 1惕它；
  • 如果規(guī)則是“將數(shù)字轉(zhuǎn)成字符串”怕午，那么就是做字符串比較，結(jié)果應(yīng)該是 0淹魄。

驗證圖

• 從圖中可知郁惜，select “10” > 9 返回的是 1，所以你就能確認 MySQL 里的轉(zhuǎn)換規(guī)則了：在 MySQL 中揭北，字符串和數(shù)字做比較的話扳炬，是將字符串轉(zhuǎn)換成數(shù)字。
  這時搔体，你再看這個全表掃描的語句：

mysql> select * from tradelog where tradeid=110717;

• 就知道對于優(yōu)化器來說恨樟，這個語句相當于：

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

• 也就是說，這條語句觸發(fā)了我們上面說到的規(guī)則：對索引字段做函數(shù)操作疚俱，優(yōu)化器會放棄走樹搜索功能劝术。

案例三：隱式字符編碼轉(zhuǎn)換

• 假設(shè)系統(tǒng)里還有另外一個表 trade_detail，用于記錄交易的操作細節(jié)呆奕。為了便于量化分析和復(fù)現(xiàn)养晋，我往交易日志表 tradelog 和交易詳情表 trade_detail 這兩個表里插入一些數(shù)據(jù)。

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步驟*/
  `step_info` varchar(32) DEFAULT NULL, /*步驟信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());
insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

• 這時候梁钾，如果要查詢 id=2 的交易的所有操作步驟信息绳泉，SQL 語句可以這么寫：

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*語句Q1*/

explain結(jié)果示意圖

• 第一行顯示優(yōu)化器會先在交易記錄表 tradelog 上查到 id=2 的行，這個步驟用上了主鍵索引姆泻，rows=1 表示只掃描一行零酪；
• 第二行 key=NULL，表示沒有用上交易詳情表 trade_detail 上的 tradeid 索引拇勃，進行了全表掃描四苇。
• 在這個執(zhí)行計劃里，是從 tradelog 表中取 tradeid 字段方咆，再去 trade_detail 表里查詢匹配字段月腋。因此，我們把 tradelog 稱為驅(qū)動表瓣赂，把 trade_detail 稱為被驅(qū)動表榆骚，把 tradeid 稱為關(guān)聯(lián)字段。

語句執(zhí)行過程示意圖

• 第 1 步煌集，是根據(jù) id 在 tradelog 表里找到 L2 這一行寨躁；
• 第 2 步，是從 L2 中取出 tradeid 字段的值牙勘；
• 第 3 步职恳，是根據(jù) tradeid 值到 trade_detail 表中查找條件匹配的行所禀。explain 的結(jié)果里面第二行的 key=NULL 表示的就是，這個過程是通過遍歷主鍵索引的方式放钦，一個一個地判斷 tradeid 的值是否匹配色徘。
• 進行到這里，你會發(fā)現(xiàn)第 3 步不符合我們的預(yù)期操禀。因為表 trade_detail 里 tradeid 字段上是有索引的兽掰，我們本來是希望通過使用 tradeid 索引能夠快速定位到等值的行第晰。但棍好，這里并沒有识颊。
• 如果你去問 DBA 同學，他們可能會告訴你揪惦，因為這兩個表的字符集不同遍搞，一個是 utf8，一個是 utf8mb4器腋，所以做表連接查詢的時候用不上關(guān)聯(lián)字段的索引溪猿。這個回答，也是通常你搜索這個問題時會得到的答案纫塌。但是你應(yīng)該再追問一下诊县，為什么字符集不同就用不上索引呢？如果單獨把這一步改成 SQL 語句的話措左，那就是：

mysql> select * from trade_detail where tradeid=$L2.tradeid.value; 

• 其中依痊，$L2.tradeid.value 的字符集是 utf8mb4。
  參照前面的兩個例子怎披，你肯定就想到了胸嘁，字符集 utf8mb4 是 utf8 的超集，所以當這兩個類型的字符串在做比較的時候钳枕，MySQL 內(nèi)部的操作是，先把 utf8 字符串轉(zhuǎn)成 utf8mb4 字符集赏壹，再做比較鱼炒。

這個設(shè)定很好理解，utf8mb4 是 utf8 的超集蝌借。類似地昔瞧，在程序設(shè)計語言里面，做自動類型轉(zhuǎn)換的時候菩佑，為了避免數(shù)據(jù)在轉(zhuǎn)換過程中由于截斷導(dǎo)致數(shù)據(jù)錯誤自晰，也都是“按數(shù)據(jù)長度增加的方向”進行轉(zhuǎn)換的

• 因此， 在執(zhí)行上面這個語句的時候稍坯，需要將被驅(qū)動數(shù)據(jù)表里的字段一個個地轉(zhuǎn)換成 utf8mb4酬荞，再跟 L2 做比較搓劫。
  也就是說，實際上這個語句等同于下面這個寫法：

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 

• CONVERT() 函數(shù)混巧，在這里的意思是把輸入的字符串轉(zhuǎn)成 utf8mb4 字符集枪向。
  這就再次觸發(fā)了我們上面說到的原則：對索引字段做函數(shù)操作，優(yōu)化器會放棄走樹搜索功能咧党。到這里秘蛔，你終于明確了，字符集不同只是條件之一傍衡，連接過程中要求在被驅(qū)動表的索引字段上加函數(shù)操作深员，是直接導(dǎo)致對被驅(qū)動表做全表掃描的原因。
• 作為對比驗證蛙埂，提另外一個需求倦畅，“查找 trade_detail 表里 id=4 的操作，對應(yīng)的操作者是誰”箱残，再來看下這個語句和它的執(zhí)行計劃滔迈。

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

explain結(jié)果

• 這個語句里 trade_detail 表成了驅(qū)動表，但是 explain 結(jié)果的第二行顯示被辑，這次的查詢操作用上了被驅(qū)動表 tradelog 里的索引 (tradeid)燎悍，掃描行數(shù)是 1。
  這也是兩個 tradeid 字段的 join 操作盼理，為什么這次能用上被驅(qū)動表的 tradeid 索引呢谈山？我們來分析一下。
• 假設(shè)驅(qū)動表 trade_detail 里 id=4 的行記為 R4宏怔，那么在連接的時候（圖的第 3 步）奏路，被驅(qū)動表 tradelog 上執(zhí)行的就是類似這樣的 SQL 語句：

select operator from tradelog  where traideid =$R4.tradeid.value; 

• 這時候 $R4.tradeid.value 的字符集是 utf8, 按照字符集轉(zhuǎn)換規(guī)則，要轉(zhuǎn)成 utf8mb4臊诊，所以這個過程就被改寫成：

select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4); 

• 你看鸽粉，這里的 CONVERT 函數(shù)是加在輸入?yún)?shù)上的，這樣就可以用上被驅(qū)動表的 traideid 索引抓艳。
  理解了原理以后触机，就可以用來指導(dǎo)操作了。如果要優(yōu)化語句

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

的執(zhí)行過程玷或，有兩種做法：

• 比較常見的優(yōu)化方法是儡首，把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4，這樣就沒有字符集轉(zhuǎn)換的問題了偏友。

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

• 如果能夠修改字段的字符集的話蔬胯，是最好不過了。但如果數(shù)據(jù)量比較大位他， 或者業(yè)務(wù)上暫時不能做這個 DDL 的話氛濒，那就只能采用修改 SQL 語句的方法了产场。

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

優(yōu)化后explain結(jié)果示意圖

小結(jié)

• 對索引字段做函數(shù)操作，可能會破壞索引值的有序性泼橘，因此優(yōu)化器就決定放棄走樹搜索功能涝动。
• 第二個例子是隱式類型轉(zhuǎn)換，第三個例子是隱式字符編碼轉(zhuǎn)換炬灭，它們都跟第一個例子一樣醋粟，因為要求在索引字段上做函數(shù)操作而導(dǎo)致了全索引掃描。
• MySQL 的優(yōu)化器確實有“偷懶”的嫌疑重归，即使簡單地把 where id+1=1000 改寫成 where id=1000-1 就能夠用上索引快速查找米愿，也不會主動做這個語句重寫。
• 因此鼻吮，每次你的業(yè)務(wù)代碼升級時育苟，把可能出現(xiàn)的、新的 SQL 語句 explain 一下椎木，是一個很好的習慣违柏。