在MySQL中，有很多看上去邏輯相同，但性能差異巨大的SQL語句。對這些語句使用不當的話，就會不經意導致整個數據庫壓力變大。本文選擇了三個這樣的案例。

案例一：條件字段函數操作

假設現在維護了一個交易系統，其中交易記錄表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;

目前的需求是，統計發生在所有年份中7月份的交易記錄總數?？赡軙@樣寫SQL語句：

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

但這可能需要執行很久，因為如果對字段做了函數計算，就用不上索引了。為什么呢？

上面是t_modified索引示意圖。如果SQL語句的條件是where t_modified='2018-7-1'，引擎會按照綠色箭頭的路線快速定位到結果。但如果要計算month()函數，傳入7這個數字，并不知道如何在樹中查找。也就是說，對索引字段做函數操作，可能會破壞索引值的有序性，因此優化器就決定放棄走樹搜索功能。

但優化器并不是要放棄使用這個索引。在該案例中，優化器可以選擇遍歷主鍵索引，也可以選擇遍歷t_modified索引，對比索引大小后會選擇遍歷t_modified索引。

用explain命令查看執行結果：

• key=t_modified，表示用上了索引；

• rows=100335，已知測試數據共10萬行，表示這條語句掃描了整個索引的所有值。

為了讓優化器能按照預期用上t_modified索引的快速定位能力，需要把SQL語句改為基于字段本身的范圍查詢：

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');

還有其他的情況，索引可能也會失效，比如：

• select * from tradelog where id+1=10000，失效；

• select * from tradelog where id=10000-1，可以。

案例二：隱式類型轉換

有一條SQL語句：

select * from tradelog where tradeid=110717;

explain結果顯示這條語句需要走全表掃描，這是因為tradeid字段類型是varchar(32)，而輸入的參數是整型，所以需要做類型轉換。

那么，數據類型轉換規則是什么？為什么有數據類型轉換，就需要走全索引掃描？

來看一個簡單的比較select "10">9：

• 如果規則是將字符串轉成數字，那么結果應該是1；

• 如果規則是將數字轉成字符串，那么結果應該是0。

驗證結果：

所以在MySQL中，字符串和數字做比較的話，規則是將字符串轉成數字。

這時再看案例給出的語句，能發現對于優化器來說，語句等同于：

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

而根據案例一，這條語句會索引失效。

案例三：隱式字符編碼轉換

假設系統里還有另外一個表trade_detail用于記錄交易的操作細節。為了便于分析，在表中插入一些數據：

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');

這時，有一條SQL語句：

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

其explain結果：

第一行說明優化器會在tradelog找到id=2的行，這個步驟用上了主鍵索引；第二行key=null，說明沒有用上tradeid索引，進行了全表掃描。

在該執行計劃中，是從tradelog表取tradeid字段，再去trade_detail表里查詢匹配字段，因此把tradelog稱為驅動表，把trade_detail稱為被驅動表，把tradeid稱為關聯字段。

該語句的執行流程：

• 首先根據id在tradelog表找到L2這一行；

• 從L2取出tradeid字段的值；

• 根據tradeid到trade_detail表查找條件匹配的行，這個過程是通過遍歷主鍵索引的方式。

我們本來是希望在trade_detail表里用索引，但最終并沒有。

究其原因，通常是因為兩個表的字符集不同，一個是utf8，一個是utf8mb4，所以做表連接查詢時用不上關聯字段的索引。那么為什么字符集不同就用不上索引呢？

如果把流程的最后一步單獨改成SQL語句，那就是：

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

其中，$L2.tradeid.value的字符集是utf8mb4。由于utf8mb4是utf8的超集，因此這兩個類型的字符串作比較時，MySQL內部會先把utf8字符串轉換成utf8mb4，再做比較，等同于：

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

CONVERT在這里表示字符集轉換。而這條語句就相當于案例一的堆索引字段做函數操作，優化器會放棄走樹搜索。

此時可以看出，字符集不同只是條件，連接過程要求在被驅動表的索引字段上加函數操作，是直接導致對被驅動表做全表掃描的原因。

作為對比驗證，換一下驅動表和被驅動表：

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

其explain結果：

從結果可以看出，當trade_detail變成驅動表，此時用上了被驅動表tradelog里的索引。

假設驅動表trade_detail里id=4的行記為R4，如果我們類似分析該語句的最后一步，其類似：

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

又由于有字符串轉換，進一步的，該語句可以寫為：

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

此時可以發現，函數是加在輸入參數上的，這樣是可以用上被驅動表的tradeid索引的。

理解了原理，如果想要優化語句：

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

有兩種做法：

• 把trade_detail表上的tradeid字段的字符集也改成utf8mb4；

• 如果不支持修改字符集，可以修改SQL語句為

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

最后總結：三個案例其實都想說明同一件事，就是對索引字段做函數操作，可能會破壞索引值的有序性，因此優化器會放棄走樹搜索。