MySQL中怎么優化查詢性能

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中怎么優化查詢性能的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！