[[342082]]

今天收到一個業務的報警，提示慢日志比較頻繁，登上環境查看，發現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中進行一些模擬測試，稍后奉上測試結果。

本文轉載自微信公眾號「 楊建榮的學習筆記」，可以通過以下二維碼關注。轉載本文請聯系 楊建榮的學習筆記公眾號。