[[342082]]

今天收到一個(gè)業(yè)務(wù)的報(bào)警，提示慢日志比較頻繁，登上環(huán)境查看，發(fā)現(xiàn)SQL是一條看起來(lái)很簡(jiǎn)單的語(yǔ)句，環(huán)境在MySQL 5.7.16版本下，慢日志里面執(zhí)行時(shí)間顯示是近1分鐘，我在從庫(kù)上面執(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) 

到了這個(gè)時(shí)候，不上表結(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申請(qǐng)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萬(wàn)左右，從目前的執(zhí)行效率來(lái)看，無(wú)疑于走了一個(gè)全表掃描。

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

所以到了這里，我們?nèi)绾蝸?lái)定性這個(gè)問(wèn)題：

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

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

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

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

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

通過(guò)如下的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)過(guò)這些對(duì)比，主要加入了limit 1，索引選擇情況就會(huì)發(fā)生變化。我們抓取一條limit 2的執(zhí)行計(jì)劃來(lái)看看?？梢悦黠@看到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部分的信息會(huì)是關(guān)鍵所在。

"index_provides_order": true,

"order_direction": "desc",

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

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

1)補(bǔ)充完整的復(fù)合索引，userid和CreateTime能夠做到互補(bǔ)，該方案已經(jīng)在同構(gòu)環(huán)境中做了完整的模擬測(cè)試，能夠達(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方式來(lái)強(qiáng)制索引，當(dāng)然對(duì)于業(yè)務(wù)具有一定的侵入性

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

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

本文轉(zhuǎn)載自微信公眾號(hào)「 楊建榮的學(xué)習(xí)筆記」，可以通過(guò)以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系 楊建榮的學(xué)習(xí)筆記公眾號(hào)。