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

WHERE條件字段使用函數(shù)

假設(shè)我們有如下創(chuàng)建表的語句

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; 

上面是一種時間維度的業(yè)務(wù)表，此時如果我們要僅僅查詢所有數(shù)據(jù)中 7月份的交易筆數(shù)。此時我們可能會想到如下SQL

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

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

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

通常情況下，我們需要人工的去優(yōu)化SQL 。當(dāng)然這往往需要結(jié)合具體的業(yè)務(wù)數(shù)據(jù)去處理了，如上面的查詢可能會優(yōu)化為如下的情況：

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 也不會主動的去做 “移項”的優(yōu)化，此時也會造成全表掃描。

字段隱式轉(zhuǎn)換

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

mysql> select '10' > 9; 

當(dāng)我們執(zhí)行上面的SQL 時，會得到如下結(jié)果

從執(zhí)行結(jié)果可以看出，字符類型默認(rèn)會轉(zhuǎn)換為數(shù)字類型。需要注意的點是：'10' ->10、'10A' -> 10、但是 'A10' -> 0 ，轉(zhuǎn)換會過濾掉無效字符，但是需要數(shù)字開頭，否則就轉(zhuǎn)化為 0 。

現(xiàn)在我們看一下如下語句：

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

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

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

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

隱式字符編碼轉(zhuǎn)換

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

當(dāng)我們需要查詢一條交易記錄(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 的這一條記錄執(zhí)行的查詢，使用了主鍵索引，掃描行數(shù) 1 ；但是第二條沒有使用 trade_detail 上的 tradeid索引，是不是感到有些奇怪。

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

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

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

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

隱式轉(zhuǎn)換后的執(zhí)行SQL 如下：

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

由此看來，執(zhí)行的過程中對 trade_detail 的查詢字段 tradeid 使用了函數(shù)，因此不走索引。但是當(dāng)我們反過來查詢時，也就是從一條 trade_detail 去關(guān)聯(lián)對應(yīng)的 trade_log 時，會是什么情況呢？

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

 

由上圖可以看出，第二次查詢使用到了 tradelog的 tradeid 索引了。當(dāng)執(zhí)行計劃找到 trade_detail 中 id=4 的記錄后（R4），再去tradelog 中關(guān)聯(lián)對應(yīng)的記錄時，執(zhí)行的SQL 如下：

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

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

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; 

• 手工字符編碼轉(zhuǎn)換。