mysql 走索引 很慢_MySQL优化:为什么SQL走索引还那么慢？
背景
2019-01-11 9:00-10:00 一個 MySQL 數據庫把 CPU 打滿了。
硬件配置：256G 內存，48 core
分析過程
接手這個問題時現場已經不在了，信息有限，所以我們先從監控系統中查看一下當時的狀態。從 PMM 監控來看，這個 MySQL 實例每天上午九點 CPU 都會升高到 10%-20%，只有 1月 2號 和 1月 11號 CPU 達到 100%，也就是今天的故障。懷疑是業務在九點會有壓力下發，排查方向是慢查詢。
1. 按執行次數統計 slow log 發現次數最多的一條 sql：
mysqldumpslow -s c slow.log > /tmp/slow_report.txt
Count: 3276 Time=21.75s (71261s) Lock=0.00s (1s) Rows=0.9 (2785), xxx
SELECT T.TASK_ID,
T.xx,
T.xx,
...
FROM T_xx_TASK T
WHERE N=N
AND T.STATUS IN (N,N,N)
AND IFNULL(T.MAX_OPEN_TIMES,N) > IFNULL(T.OPEN_TIMES,N)
AND (T.CLOSE_DATE IS NULL OR T.CLOSE_DATE >= SUBDATE(NOW(),INTERVAL 'S' MINUTE))
AND T.REL_DEVTYPE = N
AND T.REL_DEVID = N
AND T.TASK_DATE >= 'S'
AND T.TASK_DATE <= 'S'
ORDER BY TASK_ID DESC
LIMIT N,N
2. 在 slow log 中找到這條查詢記錄掃描行數：“Rows_examined: 1161559”，看起來是全表掃描，CPU 升高通常原因就是同時執行大量慢 sql，所以接下來分析這個 sql
3. 因為 T_xxx_TASK 表在現場應急時清理過數據(從110萬刪至4萬行)，所以需要用備份恢復該表到故障前。恢復備份后，查看執行計劃與執行時間：
explain SELECT T.TASK_ID,
T.xx,
...
FROM T_xxx_TASK T
WHERE 1=1
AND T.STATUS IN (1,2,3)
AND IFNULL(T.MAX_OPEN_TIMES,0) > IFNULL(T.OPEN_TIMES,0)
AND (T.CLOSE_DATE IS NULL OR T.CLOSE_DATE >= SUBDATE(NOW(),INTERVAL '10' MINUTE))
AND T.REL_DEVTYPE = 1
AND T.REL_DEVID = 000000025xxx
AND T.TASK_DATE >= '2019-01-11'
AND T.TASK_DATE <= '2019-01-11'
ORDER BY TASK_ID DESC
LIMIT 0,20;
執行時間 10s+：
1 row in set (10.37 sec)
表索引信息:
show index from T_xxx_TASK;
看到這里其實已經可以基本確定是這個 SQL 引起的了，因為執行一次就要10s+，而且那個時間點會并發下發大量的這個 SQL。但是有一點陷阱藏在這里：
1. 執行計劃中明明有使用到索引，為什么執行還是這么慢？
2. 執行計劃中顯示掃描行數為 644，為什么 slow log 中顯示 100多萬行？
a. 我們先看執行計劃，選擇的索引“INDX_BIOM_ELOCK_TASK3(TASK_ID)”。結合 sql 來看，因為有 "ORDER BY TASK_ID DESC" 子句，排序通常很慢，如果使用了文件排序性能會更差，優化器選擇這個索引避免了排序。
那為什么不選 possible_keys:INDX_BIOM_ELOCK_TASK 呢？原因也很簡單，TASK_DATE 字段選擇度太低了，優化器覺得去這個索引樹上找到對應的記錄太費勁了(代價太大)，所以就不選這個索引了。不過如果我們強制選擇這個索引(用 forece index 語法)，會看到 SQL 執行速度更快少于10s，那是因為優化器基于代價的原則并不等價于執行速度的快慢；
b. 再看執行計劃中的 type:index，"index" 代表 “全索引掃描”，其實和全表掃描差不多，只是掃描的時候是按照索引次序進行而不是行，主要優點就是避免了排序，但是開銷仍然非常大。Extra:Using where 也意味著掃描完索引后還需要回表進行篩選。一般來說，得保證 type 至少達到 range 級別，最好能達到 ref。
在第2點中提到的“慢日志記錄Rows_examined: 1161559，看起來是全表掃描”，這里更正為“全索引掃描”，掃描行數確實等于表的行數；
c. 關于執行計劃中：“rows：644”，其實這個只是估算值，并不準確，我們分析慢 SQL 時判斷準確的掃描行數應該以 slow log 中的 Rows_examined 為準。
4. 優化建議：添加組合索引 IDX_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)
優化過程：
TASK_DATE 字段存在索引，但是選擇度很低，優化器不會走這個索引，建議后續可以刪除這個索引：
select count(*),count(distinct TASK_DATE) from T_BIOMA_ELOCK_TASK;
+------------+---------------------------+
| count(*) | count(distinct TASK_DATE) |
+------------+---------------------------+
| 1161559 | 223 |
+------------+---------------------------+
在這個 sql 中 REL_DEVID 字段從命名上看選擇度較高，通過下面 sql 來檢驗確實如此：
select count(*),count(distinct REL_DEVID) from T_BIOMA_ELOCK_TASK;
+----------+---------------------------+
| count(*) | count(distinct REL_DEVID) |
+----------+---------------------------+
| 1161559 | 62235 |
+----------+---------------------------+
由于有排序，所以得把 task_id 也加入到新建的索引中，REL_DEVID,task_id 組合選擇度 100%：
select count(*),count(distinct REL_DEVID,task_id) from T_BIOMA_ELOCK_TASK;
+----------+-----------------------------------+
| count(*) | count(distinct REL_DEVID,task_id) |
+----------+-----------------------------------+
| 1161559 | 1161559 |
+----------+-----------------------------------+
在測試環境添加 REL_DEVID，TASK_ID 組合索引，測試 sql 性能：
alter table T_BIOMA_ELOCK_TASK add index idx_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID);
添加索引后執行計劃：
這里還要注意一點“隱式轉換”：REL_DEVID 字段數據類型為 varchar，需要在 sql 中加引號：
AND T.REL_DEVID = 000000025xxx >> AND T.REL_DEVID = '000000025xxx'
執行時間從 10s+ 降到 毫秒級別：
1 row in set (0.00 sec)
結論
一個典型的 order by 查詢的優化，添加更合適的索引可以避免性能問題：
執行計劃使用索引并不意味著就能執行快，要看 type 類型，如果對執行計劃不是很了解，可以移步另一篇文章：https://www.jianshu.com/p/27494df83a92
總結
以上是生活随笔為你收集整理的mysql 走索引 很慢_MySQL优化:为什么SQL走索引还那么慢？的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql 聚簇索引和非聚簇索引_MyS
- 下一篇： input 没显示 html,html