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

本篇內容介紹了“如何理解 MySQL limit 導致的執行計劃差異”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

今天收到一個業務的報警，提示慢日志比較頻繁，登上環境查看，發現 SQL 是一條看起來很簡單的語句，環境在 MySQL  5.7.16 版本下，慢日志里面執行時間顯示是近 1 分鐘，我在從庫上面執行了一下，發現優化空間確實很大：

select OrgId from `testcomm`.apply_join_org where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 1; Empty set (48.71 sec)

執行計劃如下:

explain select OrgId -  from `testcomm`.apply_join_org -  where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: apply_join_org partitions: NULL type: index possible_keys: IndexRTUser key: IndexCreateTime key_len: 5 ref: NULL rows: 4332 filtered: 0.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)

到了這個時候，不上表結構有些草率了，結構有所刪減。

CREATE TABLE `apply_join_org` ( `ApplyJoinId` int(11) NOT NULL AUTO_INCREMENT, `RTId` int(11) DEFAULT NULL, `UserId` int(11) NOT NULL, `OrgId` int(11) NOT NULL, `ApplyMsg` varchar(100) DEFAULT NULL, `CreateTime` datetime NOT NULL, `ReplyMemId` int(11) DEFAULT  0 , `ReplyTime` datetime NOT NULL, `ApplyStatus` tinyint(4) DEFAULT  1  COMMENT  0 拒絕 1 申請 2 同意 , `IfDel` tinyint(4) DEFAULT  1 , `UpdateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `RP` int(11) DEFAULT  0  COMMENT  RP 值 , `sex` tinyint(1) DEFAULT NULL, `IfLeaguer` tinyint(1) NOT NULL DEFAULT  0 , PRIMARY KEY (`ApplyJoinId`), KEY `IndexOrgIdStatus` (`OrgId`,`ApplyStatus`,`IfDel`), KEY `IndexRTUser` (`UserId`), KEY `IndexCreateTime` (`CreateTime`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=22495957 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

此外涉及的這張表的數據量有 2000 萬左右，從目前的執行效率來看，無疑于走了一個全表掃描。

其實這個問題到了這個還是比較好理解的。從語句的表現，結合表結構，我們可以感覺到：  整個 SQL 的執行過程中，原本是基于字段 UserId，沒想到卻因為 order by 中的 CreateTime，導致索引選擇錯誤，執行代價差異很大。

所以到了這里，我們如何來定性這個問題：

1) 是因為 order by 導致的嗎?

2) 是因為時間字段的排序導致的嗎?

3) 是因為 limit 操作導致的嗎?

4) 是因為 userid 本身的數據過濾效果差導致的嗎?

對于這些疑問，我們可以很快通過幾條對比 SQL 就能夠快速驗證。

通過如下的 SQL 可以看到 order by 不是最主要的原因

select OrgId -  from `testcomm`.apply_join_org -  where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime ; Empty set (0.01 sec

order by 排序也不是最主要的原因

select OrgId -  from `testcomm`.apply_join_org -  where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc ; Empty set (0.01 sec)

order by 排序 +limit 10 也不是最主要的原因

select OrgId from `testcomm`.apply_join_org where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 10; Empty set (0.01 sec)

order by 排序 +limit 2 也不是最主要的原因

select OrgId -  from `testcomm`.apply_join_org -  where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 2; Empty set (0.01 sec)

而經過這些對比，主要加入了 limit 1，索引選擇情況就會發生變化。我們抓取一條 limit  2 的執行計劃來看看?？梢悦黠@看到 type 為 ref, 此外 ref 部分差異很大 (const)。

explain select OrgId from `testcomm`.apply_join_org where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: apply_join_org partitions: NULL type: ref possible_keys: IndexRTUser key: IndexRTUser key_len: 4 ref: const rows: 4854 filtered: 1.00 Extra: Using index condition; Using where; Using filesort 1 row in set, 1 warning (0.00 sec)

如果想得到更進一步的信息，可以使用如下的方式：

SET optimizer_trace= enabled=on  SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

查看

reconsidering_access_paths_for_index_ordering 部分的信息會是關鍵所在。

index_provides_order : true,

order_direction : desc ,

而對于這個問題的分析，主要還是在于對于 cost 的評估方式，顯然在目前的測試中，增加了額外的 order  by 排序操作，導致了代價會略微高一些，而在優化器中在評估中，顯然這部分是缺失了一些信息導致判斷失誤。

有如下幾種方式可以修復：

1) 補充完整的復合索引，userid 和 CreateTime 能夠做到互補，該方案已經在同構環境中做了完整的模擬測試，能夠達到預期

alter table `testcomm`.apply_join_org drop key IndexRTUser; alter table `testcomm`.apply_join_org add key `IndexRTUser2`(UserId,CreateTime);

2) 使用 force index 的 hint 方式來強制索引，當然對于業務具有一定的侵入性

3) 調整 SQL 邏輯模式，確實是否可以使用其他的方式來代替這種 limit 1 的使用模式。

而從長計議，其實整個評估中的優化器還是比較薄弱的，對于索引選擇中的判斷依據，如果有了直方圖等輔助信息，整個過程會更加如虎添翼，這塊的內容，準備在 8.0 中進行一些模擬測試，稍后奉上測試結果。

“如何理解 MySQL limit 導致的執行計劃差異”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！