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

創(chuàng)新互聯(lián)專注于企業(yè)成都全網(wǎng)營銷推廣、網(wǎng)站重做改版、鄄城網(wǎng)站定制設計、自適應品牌網(wǎng)站建設、H5高端網(wǎng)站建設、商城系統(tǒng)網(wǎng)站開發(fā)、集團公司官網(wǎng)建設、外貿(mào)網(wǎng)站建設、高端網(wǎng)站制作、響應式網(wǎng)頁設計等建站業(yè)務，價格優(yōu)惠性價比高，為鄄城等各大城市提供網(wǎng)站開發(fā)制作服務。

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

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)

執(zhí)行計劃如下:

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)

到了這個時候，不上表結(jié)構(gòu)有些草率了，結(jié)構(gòu)有所刪減。

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)

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

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

所以到了這里，我們?nèi)绾蝸矶ㄐ赃@個問題：

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

2)是因為時間字段的排序?qū)е碌膯?

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

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

對于這些疑問，我們可以很快通過幾條對比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)

而經(jīng)過這些對比，主要加入了limit 1，索引選擇情況就會發(fā)生變化。我們抓取一條limit  2的執(zhí)行計劃來看看。可以明顯看到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排序操作，導致了代價會略微高一些，而在優(yōu)化器中在評估中，顯然這部分是缺失了一些信息導致判斷失誤。

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

1)補充完整的復合索引，userid和CreateTime能夠做到互補，該方案已經(jīng)在同構(gòu)環(huán)境中做了完整的模擬測試，能夠達到預期

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

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

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

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

“如何理解MySQL limit導致的執(zhí)行計劃差異”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識可以關注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！

文章名稱：如何理解MySQLlimit導致的執(zhí)行計劃差異
網(wǎng)站路徑：http://jinyejixie.com/article22/pspijc.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供企業(yè)建站、、ChatGPT、軟件開發(fā)、網(wǎng)站維護、定制網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)