共計 4179 個字符，預計需要花費 11 分鐘才能閱讀完成。

MySQL 中怎么優化查詢性能，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

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;

手工字符編碼轉換。

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

看完上述內容，你們掌握 MySQL 中怎么優化查詢性能的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！