在互聯網應用中，通常情況下我們查詢DB 只會使用簡單的、查詢效率較高的SQL，大部分的邏輯都需要在代碼中去實現。今天介紹一下，一些看起來簡單的SQL，也有可能導致查詢性能的低下。

WHERE條件字段使用函數

假設我們有如下創建表的語句

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

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

從上面的建表語句我們可以看出，索引是建在 t_modified 上面的。此時如果我們要查詢上面的SQL 查詢，執行過程將會是如下：

從上圖可以看出，當對索引字段做函數操作后，可能會造成索引結構順序的錯亂。因此，MySQL 會放棄走搜索樹的查詢結構，取而代之的是全索引掃描。（優化器選擇走 t_modified 索引全表遍歷，而不選擇 主鍵索引的原因是 t_modified 索引相對小一點）

通常情況下，我們需要人工的去優化SQL 。當然這往往需要結合具體的業務數據去處理了，如上面的查詢可能會優化為如下的情況：

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

對于MySQL 的簡單查詢來說，還有一個坑就是：

SELECT * FROM tradelog WHERE id + 1 = 999;  

這個時候，MySQL 也不會主動的去做 “移項”的優化，此時也會造成全表掃描。

字段隱式轉換

MySQL 中的字段隱式轉換可能會引起索引不可用，下面我們先看一個字符與數字比較的例子。如下所示：

mysql> select '10' > 9; 

當我們執行上面的SQL 時，會得到如下結果

從執行結果可以看出，字符類型默認會轉換為數字類型。需要注意的點是：'10' ->10、'10A' -> 10、但是 'A10' -> 0 ，轉換會過濾掉無效字符，但是需要數字開頭，否則就轉化為 0 。

現在我們看一下如下語句：

mysql> explain select * from tradelog where tradeid = 222; 

因為 tradeid 是 VARCHAR 類型，MySQL 會將其轉化為 數字然后比較，最終導致索引不可用，全表掃描。當我們對 int 類型字段查詢時，對應的value 值可以隨意使用 10 或者 '10' ，此時都會轉化為 數字 10 ，使用索引。上面的語句執行就相當于如下：

mysql> explain select * from tradelog where CAST(tradeid AS signed int) = 222; 

也就是隱藏的在查詢字段上面使用了函數操作，從而導致了全表掃描。

隱式字符編碼轉換

上面的案例介紹了，不同類型字段之間的類型轉換。對于相同類型(VARCHAR) 的不同字符集編碼也可能會出現隱式轉換。下面再創建一張日志詳情表(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'); 

當我們需要查詢一條交易記錄(trade_log) 中的全部交易詳情(trade_detail) 時，可能會使用如下SQL

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

上面是對 trade_log 的 id = 2 的這一條記錄執行的查詢，使用了主鍵索引，掃描行數 1 ；但是第二條沒有使用 trade_detail 上的 tradeid索引，是不是感到有些奇怪。

在上面的執行計劃里面，先是從 trade_log 里面去查詢 id=2 的記錄，然后再去匹配 trade_detail 。這里面 trade_log 稱為 驅動表，trade_detail 稱為 被驅動表，其執行流程如下所示：

那么上面第二條執行計劃為什么沒有走索引呢，仔細看你會發現上面 2 張表創建時所使用的字符集編碼不同，一個是 utf8 一個是 utf8mb4 。utfutf8mb4 是 utf8 字符集的超集，當我們將 兩張表的字段進行比較時，utf8 會轉換為utf8mb4 （避免精度丟失）。

上圖中的第 3步可以認為是執行如下操作（$L2.tradeid.value 是 utf8mb4 的字符值）：

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

隱式轉換后的執行SQL 如下：

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

由此看來，執行的過程中對 trade_detail 的查詢字段 tradeid 使用了函數，因此不走索引。但是當我們反過來查詢時，也就是從一條 trade_detail 去關聯對應的 trade_log 時，會是什么情況呢？

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

 

由上圖可以看出，第二次查詢使用到了 tradelog的 tradeid 索引了。當執行計劃找到 trade_detail 中 id=4 的記錄后（R4），再去tradelog 中關聯對應的記錄時，執行的SQL 如下：

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

此時 等號右邊的 value 值需要做隱式轉換，并沒有在索引字段上做函數操作，如下所示：

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

解決方案

對于字符集不同造成的索引不可用，可以使用如下 2 中方式去解決。

• 修改表的字符集編碼。

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

• 手工字符編碼轉換。