如何理解MySQL limit導(dǎo)致的執(zhí)行計(jì)劃差異

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

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

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í)行計(jì)劃如下:

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í)行效率來看，無疑于走了一個全表掃描。

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

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

1)是因?yàn)閛rder by導(dǎo)致的嗎?

2)是因?yàn)闀r間字段的排序?qū)е碌膯?

3)是因?yàn)閘imit操作導(dǎo)致的嗎?

4)是因?yàn)閡serid本身的數(shù)據(jù)過濾效果差導(dǎo)致的嗎?

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

通過如下的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í)行計(jì)劃來看看?？梢悦黠@看到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)

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

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

查看

reconsidering_access_paths_for_index_ordering部分的信息會是關(guān)鍵所在。

"index_provides_order": true,

"order_direction": "desc",

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

有如下幾種方式可以修復(fù)：

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

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

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

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

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

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