慢 SQL 分析與優(yōu)化

作者：字節(jié)跳動(dòng)技術(shù)團(tuán)隊(duì) 2022-07-14 14:46:51

拋開數(shù)據(jù)庫硬件層面，數(shù)據(jù)庫表設(shè)計(jì)、索引設(shè)計(jì)、業(yè)務(wù)代碼邏輯、分庫分表策略、數(shù)據(jù)歸檔策略都對(duì) SQL 執(zhí)行效率有影響，我們只有在整個(gè)設(shè)計(jì)、開發(fā)、運(yùn)維階段保持高度敏感、追求極致，才能讓我們系統(tǒng)的可用性、伸縮性不會(huì)隨著業(yè)務(wù)增長而劣化。

作者｜伍樓華

背景介紹

從系統(tǒng)設(shè)計(jì)角度看，一個(gè)系統(tǒng)從設(shè)計(jì)搭建到數(shù)據(jù)逐步增長，SQL 執(zhí)行效率可能會(huì)出現(xiàn)劣化，為繼續(xù)支撐業(yè)務(wù)發(fā)展，我們需要對(duì)慢 SQL 進(jìn)行分析和優(yōu)化，嚴(yán)峻的情況下甚至需要對(duì)整個(gè)系統(tǒng)進(jìn)行重構(gòu)。所以我們往往需要在系統(tǒng)設(shè)計(jì)前對(duì)業(yè)務(wù)進(jìn)行充分調(diào)研、遵守系統(tǒng)設(shè)計(jì)規(guī)范，在系統(tǒng)運(yùn)行時(shí)定期結(jié)合當(dāng)前業(yè)務(wù)發(fā)展情況進(jìn)行系統(tǒng)瓶頸的分析。

從數(shù)據(jù)庫角度看，每個(gè) SQL 執(zhí)行都需要消耗一定 I/O 資源，SQL 執(zhí)行的快慢，決定了資源被占用時(shí)間的長短。假如有一條慢 SQL 占用了 30%的資源共計(jì) 1 分鐘。那么在這 1 分鐘時(shí)間內(nèi)，其他 SQL 能夠分配的資源總量就是 70%，如此循環(huán)，當(dāng)資源分配完的時(shí)候，所有新的 SQL 執(zhí)行將會(huì)排隊(duì)等待。所以往往一條慢 SQL 會(huì)影響到整個(gè)業(yè)務(wù)。

本文僅討論 MySQL-InnoDB 的情況。

優(yōu)化方式

SQL 語句執(zhí)行效率的主要因素

數(shù)據(jù)量

SQL 執(zhí)行后返回給客戶端的數(shù)據(jù)量的大小；
數(shù)據(jù)量越大需要掃描的 I/O 次數(shù)越多，數(shù)據(jù)庫服務(wù)器的 IO 更容易成為瓶頸。

取數(shù)據(jù)的方式

數(shù)據(jù)在緩存中還是在磁盤上；
是否能夠通過全局索引快速尋址；
是否結(jié)合謂詞條件命中全局索引加速掃描。

數(shù)據(jù)加工的方式

排序、子查詢、聚合、關(guān)聯(lián)等，一般需要先把數(shù)據(jù)取到臨時(shí)表中，再對(duì)數(shù)據(jù)進(jìn)行加工；
對(duì)于數(shù)據(jù)量比較多的計(jì)算，會(huì)消耗大量計(jì)算節(jié)點(diǎn)的 CPU 資源，讓數(shù)據(jù)加工變得更加緩慢；
是否選擇了合適的 join 方式

優(yōu)化思路

減少數(shù)據(jù)掃描（減少磁盤訪問）

盡量在查詢中加入一些可以提前過濾數(shù)據(jù)的謂詞條件，比如按照時(shí)間過濾數(shù)據(jù)等，可以減少數(shù)據(jù)的掃描量，對(duì)查詢更友好；
在掃描大表數(shù)據(jù)時(shí)是否可以命中索引，減少回表代價(jià)，避免全表掃描。

返回更少數(shù)據(jù)（減少網(wǎng)絡(luò)傳輸或磁盤訪問）
減少交互次數(shù)（減少網(wǎng)絡(luò)傳輸）
將數(shù)據(jù)存放在更快的地方
某條查詢涉及到大表，無法進(jìn)一步優(yōu)化，如果返回的數(shù)據(jù)量不大且變化頻率不高但訪問頻率很高，此時(shí)應(yīng)該考慮將返回的數(shù)據(jù)放在應(yīng)用端的緩存當(dāng)中或者 Redis 這樣的緩存當(dāng)中，以提高存取速度。
減少服務(wù)器 CPU 開銷（減少 CPU 及內(nèi)存開銷）
避免大事務(wù)操作
利用更多資源（增加資源）

優(yōu)化案例

數(shù)據(jù)分頁優(yōu)化

select * from table_demo where type = ?  limit ?,?;

優(yōu)化方式一：偏移 id

lastId = 0 or min(id)
do {
select * from table_demo where type = ? and id >{#lastId}  limit ?;
lastId = max(id)
} while (isNotEmpty)

優(yōu)化方式二：分段查詢

該方式較方式一的優(yōu)點(diǎn)在于可并行查詢，每個(gè)分段查詢互不依賴；較方式一的缺點(diǎn)在于較依賴數(shù)據(jù)的連續(xù)性，若數(shù)據(jù)過于分散，代價(jià)較高。

minId = min(id) maxId = max(id)
for(int i = minId; i<= maxId; i+=pageSize){
select * from table_demo where type = ? and id between i and i+ pageSize;
}

優(yōu)化 GROUP BY

提高 GROUP BY 語句的效率, 可以通過將不需要的記錄在 GROUP BY 之前過濾掉.下面兩個(gè)查詢返回相同結(jié)果但第二個(gè)明顯就快了許多。

低效:

select job , avg(sal) from table_demo group by job having  job = ‘manager'

高效:

select job , avg(sal) from table_demo where  job = ‘manager' group by job

范圍查詢

聯(lián)合索引中如果有某個(gè)列存在范圍（大于小于）查詢，其右邊的列是否還有意義？

explain select count(1) from statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00'
explain select * from statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00'  limit 0, 100
explain select * from statement where org_code='1012' and trade_date_time >= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00'

使用單鍵索引 trade_date_time 的情況下

從索引里找到所有 trade_date_time 在'2019-05-01' 到'2020-05-01' 區(qū)間的主鍵 id。假設(shè)有 100 萬個(gè)。
對(duì)這些 id 進(jìn)行排序（為的是在下面一步回表操作中優(yōu)化 I/O 操作，因?yàn)楹芏喟さ媒闹麈I可能一次磁盤 I/O 就都取到了）
回表，查出 100 萬行記錄，然后逐個(gè)掃描，篩選出 org_code='1020'的行記錄

使用聯(lián)合索引 trade_date_time, org_code -聯(lián)合索引 trade_date_time, org_code 底層結(jié)構(gòu)推導(dǎo)如下：

以查找 trade_date_time >='2019-05-01' and trade_date_time <='2020-05-01' and org_code='1020'為例：

在范圍查找的時(shí)候,直接找到最大,最小的值,然后進(jìn)行鏈表遍歷，故僅能用到 trade_date_time 的索引，無法使用到 org_code 索引
基于 MySQL5.6+的索引下推特性，雖然 org_code 字段無法使用到索引樹，但是可以用于過濾回表的主鍵 id 數(shù)。

小結(jié)：對(duì)于該 case, 索引效果[org_code,trade_date_time] > [trade_date_time, org_code]>[trade_date_time]。實(shí)際業(yè)務(wù)場景中，檢索條件中 trade_date_time 基本上肯定會(huì)出現(xiàn)，但 org_code 卻不一定，故索引的設(shè)計(jì)還需要結(jié)合實(shí)際業(yè)務(wù)需求。

優(yōu)化 Order by

索引：

KEY `idx_account_trade_date_time` (`account_number`,`trade_date_time`),
  KEY `idx_trade_date_times` (`trade_date_time`)
  KEY `idx_createtime` (`create_time`),

慢 SQL:

SELECT  id,....,creator,modifier,create_time,update_time  FROM statement
WHERE (account_number = 'XXX' AND create_time >= '2022-04-24 06:03:44' AND create_time <= '2022-04-24 08:03:44' AND dc_flag = 'C') ORDER BY trade_date_time DESC,id DESC LIMIT 0,1000;

優(yōu)化前：SQL 執(zhí)行超時(shí)被 kill 了

SELECT  id,....,creator,modifier,create_time,update_time  FROM statement
WHERE (account_number = 'XXX' AND create_time >= '2022-04-24 06:03:44' AND create_time <= '2022-04-24 08:03:44' AND dc_flag = 'C') ORDER BY create_time DESC,id DESC LIMIT 0,1000;

優(yōu)化后：執(zhí)行總行數(shù)為:6 行，耗時(shí) 34ms。

MySQL使不使用索引與所查列無關(guān)，只與索引本身，where條件，order by 字段，group by 字段有關(guān)。索引的作用一個(gè)是查找，一個(gè)是排序。

業(yè)務(wù)拆分

select * from order where status='S' and update_time < now-5min  limit 500

拆分優(yōu)化：

隨著業(yè)務(wù)數(shù)據(jù)的增長 status='S'的數(shù)據(jù)基本占據(jù)數(shù)據(jù)的 90%以上，此時(shí)該條件無法走索引。我們可以結(jié)合業(yè)務(wù)特征，對(duì)數(shù)據(jù)獲取按日期進(jìn)行拆分。

date = now; minDate = now - 10 days
while(date > minDate) {
select * from order where order_date={#date} and status='S' and update_time < now-5min  limit 500
date = data + 1
}

數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化

范式優(yōu)化：表的設(shè)計(jì)合理化（符合 3NF），比如消除冗余（節(jié)省空間）；
反范式優(yōu)化：比如適當(dāng)加冗余等（減少 join）
拆分表：分區(qū)將數(shù)據(jù)在物理上分隔開，不同分區(qū)的數(shù)據(jù)可以制定保存在處于不同磁盤上的數(shù)據(jù)文件里。這樣，當(dāng)對(duì)這個(gè)表進(jìn)行查詢時(shí)，只需要在表分區(qū)中進(jìn)行掃描，而不必進(jìn)行全表掃描，明顯縮短了查詢時(shí)間，另外處于不同磁盤的分區(qū)也將對(duì)這個(gè)表的數(shù)據(jù)傳輸分散在不同的磁盤 I/O，一個(gè)精心設(shè)置的分區(qū)可以將數(shù)據(jù)傳輸對(duì)磁盤 I/O 競爭均勻地分散開。對(duì)數(shù)據(jù)量大的表可采取此方法，可按月建表分區(qū)。

SQL 語句優(yōu)化

SQL 檢查狀態(tài)及分?jǐn)?shù)計(jì)算邏輯

盡量避免使用子查詢
用 IN 來替換 OR
讀取適當(dāng)?shù)挠涗?LIMIT M,N，而不要讀多余的記錄
禁止不必要的 Order By 排序
總和查詢可以禁止排重用 union all
避免隨機(jī)取記錄
將多次插入換成批量 Insert 插入
只返回必要的列，用具體的字段列表代替 select * 語句
區(qū)分 in 和 exists
優(yōu)化 Group By 語句
盡量使用數(shù)字型字段
優(yōu)化 Join 語句

大表優(yōu)化

分庫分表（水平、垂直）
讀寫分離
數(shù)據(jù)定期歸檔

原理剖析

MySQL 邏輯架構(gòu)圖：

索引的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)

提高查詢語句的執(zhí)行效率，減少 IO 操作的次數(shù)
創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性
加了索引的列會(huì)進(jìn)行排序，在使用分組和排序子句進(jìn)行查詢時(shí)，可以顯著減少查詢中分組和排序的時(shí)間

缺點(diǎn)

索引需要占物理空間
創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加
當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增刪改查時(shí)，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的更新效率

索引的數(shù)據(jù)結(jié)構(gòu)

主鍵索引

普通索引

組合索引

索引頁結(jié)構(gòu)

索引頁由七部分組成，其中 Infimum 和 Supremum 也屬于記錄，只不過是虛擬記錄，這里為了與用戶記錄區(qū)分開，還是決定將兩者拆開。

數(shù)據(jù)行格式：

MySQL 有 4 種存儲(chǔ)格式：

Compact
Redundant （5.0 版本以前用，已廢棄）
Dynamic （MySQL5.7 默認(rèn)格式）
Compressed

Dynamic 行存儲(chǔ)格式下，對(duì)于處理行溢出（當(dāng)一個(gè)字段存儲(chǔ)長度過大時(shí)，會(huì)發(fā)生行溢出）時(shí)，僅存放溢出頁內(nèi)存地址。

索引的設(shè)計(jì)原則

哪些情況適合建索引

數(shù)據(jù)又?jǐn)?shù)值有唯一性的限制
頻繁作為 where 條件的字段
經(jīng)常使用 group by 和 order by 的字段，既有 group by 又有 order by 的字段時(shí)，建議建聯(lián)合索引
經(jīng)常作為 update 或 delete 條件的字段
經(jīng)常需要 distinct 的字段
多表連接時(shí)的字段建議創(chuàng)建索引，也有注意事項(xiàng)

連接表數(shù)量最好不要超過 3 張，每增加一張表就相當(dāng)于增加了一次嵌套循環(huán)，數(shù)量級(jí)增長會(huì)非常快
對(duì)多表查詢時(shí)的 where 條件創(chuàng)建索引
對(duì)連接字段創(chuàng)建索引，并且數(shù)據(jù)類型保持一致

在確定數(shù)據(jù)范圍的情況下盡量使用數(shù)據(jù)類型較小的，因?yàn)樗饕龝?huì)也會(huì)占用空間
對(duì)字符串創(chuàng)建索引時(shí)建議使用字符串的前綴作為索引
這樣做的好處是：
能節(jié)省索引的空間，
雖然不能精確定位，但是能夠定位到相同的前綴，然后通過主鍵查詢完整的字符串，這樣既能節(jié)省空間，又減少了字符串的比較時(shí)間，還能解決排序問題。
區(qū)分度高（散列性高）的字段適合作為索引。
在多個(gè)字段需要?jiǎng)?chuàng)建索引的情況下，聯(lián)合索引優(yōu)先于單值索引。使用最頻繁的列作為索引的最左側(cè) 。

哪些情況下不需要使用索引

在 where 條件中用不到的字段不需要。
數(shù)據(jù)量小的不需要建索引，比如數(shù)據(jù)少于 1000 條。
由大量重復(fù)數(shù)據(jù)的列上不要建索引，比如性別字段中只有男和女時(shí)。
避免在經(jīng)常更新的表或字段中創(chuàng)建過多的索引。
不建議主鍵使用無序的值作為索引，比如 uuid。
不要定義冗余或重復(fù)的索引
例如：已經(jīng)創(chuàng)建了聯(lián)合索引 key(id,name)后就不需要再單獨(dú)建一個(gè) key(id)的索引

索引優(yōu)化之 MRR

例如有一張表 user，主鍵 id，普通字段 age，為 age 創(chuàng)建非聚集索引，有一條查詢語句 select* user from table where age > 18;(注意查詢語句中的結(jié)果是*)

在 MySQL5.5 以及之前的版本中如何查詢呢？先通過非聚集索引查詢到 age>18 的第一條數(shù)據(jù)，獲取到了主鍵 id；然后根據(jù)非聚集索引中的葉子節(jié)點(diǎn)存儲(chǔ)的主鍵 id 去聚集索引中查詢行數(shù)據(jù)；根據(jù) age>18 的數(shù)據(jù)條數(shù)每次查詢聚集索引，這個(gè)過程叫做回表。

上述的步驟有什么缺點(diǎn)呢？如何 age>18 的數(shù)據(jù)非常多，那么每次回表都需要經(jīng)過 3 次 IO(假設(shè) B+樹的高度是 3)，那么會(huì)導(dǎo)致查詢效率過低。

在 MySQL5.6 時(shí)針對(duì)上述問題進(jìn)行了優(yōu)化，優(yōu)化器先查詢到 age>3 的所有數(shù)據(jù)的主鍵 id，對(duì)所有主鍵的 id 進(jìn)行排序，排序的結(jié)果緩存到 read_rnd_buffer，然后通過排好序的主鍵在聚簇索引中進(jìn)行查詢。
如果兩個(gè)主鍵的范圍相近，在同一個(gè)數(shù)據(jù)頁中就可以之間按照順序獲取，那么磁盤 io 的過程將會(huì)大大降低。這個(gè)優(yōu)化的過程就叫做 Multi Range Read(MRR) 多返回查詢。