背景

2019-01-11 9:00-10:00 一個(gè) MySQL 數(shù)據(jù)庫(kù)把 CPU 打滿了。

硬件配置：256G 內(nèi)存，48 core

分析過(guò)程

接手這個(gè)問(wèn)題時(shí)現(xiàn)場(chǎng)已經(jīng)不在了，信息有限，所以我們先從監(jiān)控系統(tǒng)中查看一下當(dāng)時(shí)的狀態(tài)。從 PMM 監(jiān)控來(lái)看，這個(gè) MySQL 實(shí)例每天上午九點(diǎn) CPU 都會(huì)升高到 10%-20%，只有 1 月 2 號(hào) 和 1 月 11 號(hào) CPU 達(dá)到 100%，也就是今天的故障。懷疑是業(yè)務(wù)在九點(diǎn)會(huì)有壓力下發(fā)，排查方向是慢查詢。

1. 按執(zhí)行次數(shù)統(tǒng)計(jì) slow log 發(fā)現(xiàn)次數(shù)最多的一條 sql：

mysqldumpslow -s c slow.log>/tmp/slow_report.txt

Count: 3276 Time=21.75s (71261s) Lock=0.00s (1s) Rows=0.9 (2785), xxxSELECT T.TASK_ID,T.xx,T.xx,...FROM T_xx_TASK TWHERE N=NAND 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 = NAND T.REL_DEVID = NAND T.TASK_DATE >= 'S'AND T.TASK_DATE <= 'S'ORDER BY TASK_ID DESCLIMIT N,N

2. 在 slow log 中找到這條查詢記錄掃描行數(shù)：“Rows_examined: 1161559”，看起來(lái)是全表掃描，CPU 升高通常原因就是同時(shí)執(zhí)行大量慢 sql，所以接下來(lái)分析這個(gè) sql

3. 因?yàn)?T_xxx_TASK 表在現(xiàn)場(chǎng)應(yīng)急時(shí)清理過(guò)數(shù)據(jù)（從 110 萬(wàn)刪至 4 萬(wàn)行），所以需要用備份恢復(fù)該表到故障前。恢復(fù)備份后，查看執(zhí)行計(jì)劃與執(zhí)行時(shí)間：

explain SELECT T.TASK_ID,T.xx,...FROM T_xxx_TASK TWHERE 1=1AND 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 = 1AND T.REL_DEVID = 000000025xxxAND T.TASK_DATE >= '2019-01-11'AND T.TASK_DATE <= '2019-01-11'ORDER BY TASK_ID DESCLIMIT 0,20;

執(zhí)行時(shí)間 10s+：

1 row in set (10.37 sec)

表索引信息:

show index from T_xxx_TASK;

看到這里其實(shí)已經(jīng)可以基本確定是這個(gè) SQL 引起的了，因?yàn)閳?zhí)行一次就要 10s+，而且那個(gè)時(shí)間點(diǎn)會(huì)并發(fā)下發(fā)大量的這個(gè) SQL。但是有一點(diǎn)陷阱藏在這里：

1. 執(zhí)行計(jì)劃中明明有使用到索引，為什么執(zhí)行還是這么慢？

2. 執(zhí)行計(jì)劃中顯示掃描行數(shù)為 644，為什么 slow log 中顯示 100 多萬(wàn)行？

a. 我們先看執(zhí)行計(jì)劃，選擇的索引 “INDX_BIOM_ELOCK_TASK3(TASK_ID)”。結(jié)合 sql 來(lái)看，因?yàn)橛?"ORDER BY TASK_ID DESC" 子句，排序通常很慢，如果使用了文件排序性能會(huì)更差，優(yōu)化器選擇這個(gè)索引避免了排序。

那為什么不選 possible_keys:INDX_BIOM_ELOCK_TASK 呢？原因也很簡(jiǎn)單，TASK_DATE 字段區(qū)分度太低了，走這個(gè)索引需要掃描的行數(shù)很大，而且還要進(jìn)行額外的排序，優(yōu)化器綜合判斷代價(jià)更大，所以就不選這個(gè)索引了。不過(guò)如果我們強(qiáng)制選擇這個(gè)索引（用 force index 語(yǔ)法），會(huì)看到 SQL 執(zhí)行速度更快少于 10s，那是因?yàn)閮?yōu)化器基于代價(jià)的原則并不等價(jià)于執(zhí)行速度的快慢；

b. 再看執(zhí)行計(jì)劃中的 type:index，"index" 代表 “全索引掃描”，其實(shí)和全表掃描差不多，只是掃描的時(shí)候是按照索引次序進(jìn)行而不是行，主要優(yōu)點(diǎn)就是避免了排序，但是開(kāi)銷(xiāo)仍然非常大。

Extra:Using where 也意味著掃描完索引后還需要回表進(jìn)行篩選。一般來(lái)說(shuō)，得保證 type 至少達(dá)到 range 級(jí)別，最好能達(dá)到 ref。

在第 2 點(diǎn)中提到的“慢日志記錄Rows_examined: 1161559，看起來(lái)是全表掃描”，這里更正為“全索引掃描”，掃描行數(shù)確實(shí)等于表的行數(shù)；

c. 關(guān)于執(zhí)行計(jì)劃中：“rows：644”，其實(shí)這個(gè)只是估算值，并不準(zhǔn)確，我們分析慢 SQL 時(shí)判斷準(zhǔn)確的掃描行數(shù)應(yīng)該以 slow log 中的 Rows_examined 為準(zhǔn)。

4. 優(yōu)化建議：添加組合索引 IDX_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)

優(yōu)化過(guò)程：

TASK_DATE 字段存在索引，但是選擇度很低，優(yōu)化器不會(huì)走這個(gè)索引，建議后續(xù)可以刪除這個(gè)索引：

select count(*),count(distinct TASK_DATE) from T_BIOMA_ELOCK_TASK;+------------+---------------------------+| count(*) | count(distinct TASK_DATE) |+------------+---------------------------+| 1161559 | 223 |+------------+---------------------------+

在這個(gè) sql 中 REL_DEVID 字段從命名上看選擇度較高，通過(guò)下面 sql 來(lái)檢驗(yàn)確實(shí)如此：

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 |+----------+-----------------------------------+

在測(cè)試環(huán)境添加 REL_DEVID，TASK_ID 組合索引，測(cè)試 sql 性能：alter table T_BIOMA_ELOCK_TASK add index idx_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID);

添加索引后執(zhí)行計(jì)劃：

這里還要注意一點(diǎn)“隱式轉(zhuǎn)換”：REL_DEVID 字段數(shù)據(jù)類(lèi)型為 varchar，需要在 sql 中加引號(hào)：AND T.REL_DEVID = 000000025xxx >> AND T.REL_DEVID = '000000025xxx'

執(zhí)行時(shí)間從 10s+ 降到 毫秒級(jí)別：

1 row in set (0.00 sec)

結(jié)論

一個(gè)典型的 order by 查詢的優(yōu)化，添加更合適的索引可以避免性能問(wèn)題：執(zhí)行計(jì)劃使用索引并不意味著就能執(zhí)行快。