背景介紹

從系統(tǒng)設(shè)計(jì)角度看，一個(gè)系統(tǒng)從設(shè)計(jì)搭建到數(shù)據(jù)逐步增長(zhǎng)，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ù)庫(kù)角度看，每個(gè) SQL 執(zhí)行都需要消耗一定 I/O 資源，SQL 執(zhí)行的快慢，決定了資源被占用時(shí)間的長(zhǎng)短。假如有一條慢 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 語(yǔ)句執(zhí)行效率的主要因素

• 數(shù)據(jù)量
  • SQL 執(zhí)行后返回給客戶端的數(shù)據(jù)量的大??；
  • 數(shù)據(jù)量越大需要掃描的 I/O 次數(shù)越多，數(shù)據(jù)庫(kù)服務(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è)優(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 語(yǔ)句的效率, 可以通過將不需要的記錄在 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'為例：

1. 在范圍查找的時(shí)候,直接找到最大,最小的值,然后進(jìn)行鏈表遍歷，故僅能用到 trade_date_time 的索引，無法使用到 org_code 索引
2. 基于 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ù)場(chǎng)景中，檢索條件中 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ù)的增長(zhǎng) 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ù)庫(kù)結(jié)構(gòu)優(yōu)化

1. 范式優(yōu)化：表的設(shè)計(jì)合理化（符合 3NF），比如消除冗余（節(jié)省空間）；
2. 反范式優(yōu)化：比如適當(dāng)加冗余等（減少 join）
3. 拆分表：分區(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 競(jìng)爭(zhēng)均勻地分散開。對(duì)數(shù)據(jù)量大的表可采取此方法，可按月建表分區(qū)。

SQL 語(yǔ)句優(yōu)化

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

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

大表優(yōu)化

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

原理剖析

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

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

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

提高查詢語(yǔ)句的執(zhí)行效率，減少 IO 操作的次數(shù)

創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫(kù)表中每一行數(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)

主鍵索引

普通索引

組合索引

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

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

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

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

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

Dynamic 行存儲(chǔ)格式下，對(duì)于處理行溢出（當(dāng)一個(gè)字段存儲(chǔ)長(zhǎng)度過大時(shí)，會(huì)發(fā)生行溢出）時(shí)，僅存放溢出頁(yè)內(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í)增長(zhǎng)會(huì)非?？?/li>
  • 對(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)建非聚集索引，有一條查詢語(yǔ)句 select* user from table where age > 18;(注意查詢語(yǔ)句中的結(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ù)頁(yè)中就可以之間按照順序獲取，那么磁盤 io 的過程將會(huì)大大降低。這個(gè)優(yōu)化的過程就叫做 Multi Range Read(MRR) 多返回查詢。

索引下推

假設(shè)有索引(name, age), 執(zhí)行 SQL: select * from tuser where name like '張%' and age=10;

MySQL 5.6 以后， 存儲(chǔ)引擎根據(jù)（name，age）聯(lián)合索引，找到，由于聯(lián)合索引中包含列，所以存儲(chǔ)引擎直接在聯(lián)合索引里按照age=10過濾。按照過濾后的數(shù)據(jù)再一一進(jìn)行回表掃描。

索引下推使用條件

• 只能用于range、 ref、 eq_ref、ref_or_null訪問方法；
• 只能用于InnoDB和 MyISAM存儲(chǔ)引擎及其分區(qū)表；
• 對(duì)存儲(chǔ)引擎來說，索引下推只適用于二級(jí)索引（也叫輔助索引）;

索引下推的目的是為了減少回表次數(shù)，也就是要減少 IO 操作。對(duì)于的聚簇索引來說，數(shù)據(jù)和索引是在一起的，不存在回表這一說。

• 引用了子查詢的條件不能下推；
• 引用了存儲(chǔ)函數(shù)的條件不能下推，因?yàn)榇鎯?chǔ)引擎無法調(diào)用存儲(chǔ)函數(shù)。

思考：

1. MySQL 一張表到底能存多少數(shù)據(jù)？
2. 為什么要控制單行數(shù)據(jù)大小?
3. 優(yōu)化案例 4 中優(yōu)化前的 SQL 為什么走不到索引？

總結(jié)

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

到此這篇關(guān)于慢SQL分析與優(yōu)化的文章就介紹到這了,更多相關(guān)慢SQL分析優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

參考資料

https://help.aliyun.com/document_detail/311122.html

https://blog.csdn.net/qq_32099833/article/details/123150701

https://www.cnblogs.com/tufujie/p/9413852.html