一文帶你理解慢SQL分析與優化

背景介紹

從系統設計角度看，一個系統從設計搭建到資料逐步增長，SQL 執行效率可能會出現劣化，為繼續支撐業務發展，我們需要對慢 SQL 進行分析和優化，嚴峻的情況下甚至需要對整個系統進行重構。所以我們往往需要在系統設計前對業務進行充分調研、遵守系統設計規範，在系統執行時定期結合當前業務發展情況進行系統瓶頸的分析。

從資料庫角度看，每個 SQL 執行都需要消耗一定 I/O 資源，SQL 執行的快慢，決定了資源被佔用時間的長短。假如有一條慢 SQL 佔用了 30%的資源共計 1 分鐘。那麼在這 1 分鐘時間內，其他 SQL 能夠分配的資源總量就是 70%，如此迴圈，當資源分配完的時候，所有新的 SQL 執行將會排隊等待。所以往往一條慢 SQL 會影響到整個業務。

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

優化方式

SQL 語句執行效率的主要因素

• 資料量
  • SQL 執行後返回給使用者端的資料量的大小；
  • 資料量越大需要掃描的 I/O 次數越多，資料庫伺服器的 IO 更容易成為瓶頸。
• 取資料的方式
  • 資料在快取中還是在磁碟上；
  • 是否能夠通過全域性索引快速定址；
  • 是否結合謂詞條件命中全域性索引加速掃描。
• 資料加工的方式
  • 排序、子查詢、聚合、關聯等，一般需要先把資料取到臨時表中，再對資料進行加工；
  • 對於資料量比較多的計算，會消耗大量計算節點的 CPU 資源，讓資料加工變得更加緩慢；
  • 是否選擇了合適的 join 方式

優化思路

• 減少資料掃描（減少磁碟存取）
  • 儘量在查詢中加入一些可以提前過濾資料的謂詞條件，比如按照時間過濾資料等，可以減少資料的掃描量，對查詢更友好；
  • 在掃描大表資料時是否可以命中索引，減少回表代價，避免全表掃描。
• 返回更少資料（減少網路傳輸或磁碟存取）
• 減少互動次數（減少網路傳輸）
  • 將資料存放在更快的地方
  • 某條查詢涉及到大表，無法進一步優化，如果返回的資料量不大且變化頻率不高但存取頻率很高，此時應該考慮將返回的資料放在應用端的快取當中或者 Redis 這樣的快取當中，以提高存取速度。
• 減少伺服器 CPU 開銷（減少 CPU 及記憶體開銷）
• 避免大事務操作
• 利用更多資源（增加資源）

優化案例

資料分頁優化

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

優化方式一：偏移 id

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

優化方式二：分段查詢

該方式較方式一的優點在於可並行查詢，每個分段查詢互不依賴；較方式一的缺點在於較依賴資料的連續性，若資料過於分散，代價較高。

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;
}

優化 GROUP BY

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

低效:

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

範圍查詢

聯合索引中如果有某個列存在範圍（大於小於）查詢，其右邊的列是否還有意義？

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' 區間的主鍵 id。假設有 100 萬個。
• 對這些 id 進行排序（為的是在下面一步回表操作中優化 I/O 操作，因為很多捱得近的主鍵可能一次磁碟 I/O 就都取到了）
• 回表，查出 100 萬行記錄，然後逐個掃描，篩選出 org_code='1020'的行記錄

使用聯合索引 trade_date_time, org_code -聯合索引 trade_date_time, org_code 底層結構推導如下：

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

1. 在範圍查詢的時候,直接找到最大,最小的值,然後進行連結串列遍歷，故僅能用到 trade_date_time 的索引，無法使用到 org_code 索引
2. 基於 MySQL5.6+的索引下推特性，雖然 org_code 欄位無法使用到索引樹，但是可以用於過濾回表的主鍵 id 數。

小結：對於該 case, 索引效果[org_code,trade_date_time] > [trade_date_time, org_code]>[trade_date_time]。實際業務場景中，檢索條件中 trade_date_time 基本上肯定會出現，但 org_code 卻不一定，故索引的設計還需要結合實際業務需求。

優化 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;

優化前：SQL 執行超時被 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;

優化後：執行總行數為:6 行，耗時 34ms。

MySQL使不使用索引與所查列無關，只與索引本身，where條件，order by 欄位，group by 欄位有關。索引的作用一個是查詢，一個是排序。

業務拆分

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

拆分優化：

隨著業務資料的增長 status='S'的資料基本佔據資料的 90%以上，此時該條件無法走索引。我們可以結合業務特徵，對資料獲取按日期進行拆分。

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
}

資料庫結構優化

1. 正規化優化：表的設計合理化（符合 3NF），比如消除冗餘（節省空間）；
2. 反正規化優化：比如適當加冗餘等（減少 join）
3. 拆分表：分割區將資料在物理上分隔開，不同分割區的資料可以制定儲存在處於不同磁碟上的資料檔案裡。這樣，當對這個表進行查詢時，只需要在表分割區中進行掃描，而不必進行全表掃描，明顯縮短了查詢時間，另外處於不同磁碟的分割區也將對這個表的資料傳輸分散在不同的磁碟 I/O，一個精心設定的分割區可以將資料傳輸對磁碟 I/O 競爭均勻地分散開。對資料量大的表可採取此方法，可按月建表分割區。

SQL 語句優化

SQL 檢查狀態及分數計算邏輯

1. 儘量避免使用子查詢
2. 用 IN 來替換 OR
3. 讀取適當的記錄 LIMIT M,N，而不要讀多餘的記錄
4. 禁止不必要的 Order By 排序
5. 總和查詢可以禁止排重用 union all
6. 避免隨機取記錄
7. 將多次插入換成批次 Insert 插入
8. 只返回必要的列，用具體的欄位列表代替 select * 語句
9. 區分 in 和 exists
10. 優化 Group By 語句
11. 儘量使用數位型欄位
12. 優化 Join 語句

大表優化

• 分庫分表（水平、垂直）
• 讀寫分離
• 資料定期歸檔

原理剖析

MySQL 邏輯架構圖：

索引的優缺點

優點

提高查詢語句的執行效率，減少 IO 操作的次數

建立唯一性索引，可以保證資料庫表中每一行資料的唯一性

加了索引的列會進行排序，在使用分組和排序子句進行查詢時，可以顯著減少查詢中分組和排序的時間

缺點

索引需要佔物理空間

建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加

當對錶中的資料進行增刪改查時，索引也要動態的維護，這樣就降低了資料的更新效率

索引的資料結構

主鍵索引

普通索引

組合索引

索引頁結構

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

資料行格式：

MySQL 有 4 種儲存格式：

1. Compact
2. Redundant （5.0 版本以前用，已廢棄）
3. Dynamic （MySQL5.7 預設格式）
4. Compressed

Dynamic 行儲存格式下，對於處理行溢位（當一個欄位儲存長度過大時，會發生行溢位）時，僅存放溢位頁記憶體地址。

索引的設計原則

哪些情況適合建索引

• 資料又數值有唯一性的限制
• 頻繁作為 where 條件的欄位
• 經常使用 group by 和 order by 的欄位，既有 group by 又有 order by 的欄位時，建議建聯合索引
• 經常作為 update 或 delete 條件的欄位
• 經常需要 distinct 的欄位
• 多表連線時的欄位建議建立索引，也有注意事項
  • 連線表數量最好不要超過 3 張，每增加一張表就相當於增加了一次巢狀迴圈，數量級增長會非常快
  • 對多表查詢時的 where 條件建立索引
  • 對連線欄位建立索引，並且資料型別保持一致
• 在確定資料範圍的情況下儘量使用資料型別較小的，因為索引會也會佔用空間
• 對字串建立索引時建議使用字串的字首作為索引
• 這樣做的好處是：
  • 能節省索引的空間，
  • 雖然不能精確定位，但是能夠定位到相同的字首，然後通過主鍵查詢完整的字串，這樣既能節省空間，又減少了字串的比較時間，還能解決排序問題。
• 區分度高（雜湊性高）的欄位適合作為索引。
• 在多個欄位需要建立索引的情況下，聯合索引優先於單值索引。使用最頻繁的列作為索引的最左側 。

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

• 在 where 條件中用不到的欄位不需要。
• 資料量小的不需要建索引，比如資料少於 1000 條。
• 由大量重複資料的列上不要建索引，比如性別欄位中只有男和女時。
• 避免在經常更新的表或欄位中建立過多的索引。
• 不建議主鍵使用無序的值作為索引，比如 uuid。
• 不要定義冗餘或重複的索引
• 例如：已經建立了聯合索引 key(id,name)後就不需要再單獨建一個 key(id)的索引

索引優化之 MRR

例如有一張表 user，主鍵 id，普通欄位 age，為 age 建立非聚集索引，有一條查詢語句 select* user from table where age > 18;(注意查詢語句中的結果是*)

在 MySQL5.5 以及之前的版本中如何查詢呢？先通過非聚集索引查詢到 age>18 的第一條資料，獲取到了主鍵 id；然後根據非聚集索引中的葉子節點儲存的主鍵 id 去聚集索引中查詢行資料；根據 age>18 的資料條數每次查詢聚集索引，這個過程叫做回表。

上述的步驟有什麼缺點呢？如何 age>18 的資料非常多，那麼每次回表都需要經過 3 次 IO(假設 B+樹的高度是 3)，那麼會導致查詢效率過低。

在 MySQL5.6 時針對上述問題進行了優化，優化器先查詢到 age>3 的所有資料的主鍵 id，對所有主鍵的 id 進行排序，排序的結果快取到 read_rnd_buffer，然後通過排好序的主鍵在聚簇索引中進行查詢。

如果兩個主鍵的範圍相近，在同一個資料頁中就可以之間按照順序獲取，那麼磁碟 io 的過程將會大大降低。這個優化的過程就叫做 Multi Range Read(MRR) 多返回查詢。

索引下推

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

MySQL 5.6 以後， 儲存引擎根據（name，age）聯合索引，找到，由於聯合索引中包含列，所以儲存引擎直接在聯合索引裡按照age=10過濾。按照過濾後的資料再一一進行回表掃描。

索引下推使用條件

• 只能用於range、 ref、 eq_ref、ref_or_null存取方法；
• 只能用於InnoDB和 MyISAM儲存引擎及其分割區表；
• 對儲存引擎來說，索引下推只適用於二級索引（也叫輔助索引）;

索引下推的目的是為了減少回表次數，也就是要減少 IO 操作。對於的聚簇索引來說，資料和索引是在一起的，不存在回表這一說。

• 參照了子查詢的條件不能下推；
• 參照了儲存函數的條件不能下推，因為儲存引擎無法呼叫儲存函數。

思考：

1. MySQL 一張表到底能存多少資料？
2. 為什麼要控制單行資料大小?
3. 優化案例 4 中優化前的 SQL 為什麼走不到索引？

總結

拋開資料庫硬體層面，資料庫表設計、索引設計、業務程式碼邏輯、分庫分表策略、資料歸檔策略都對 SQL 執行效率有影響，我們只有在整個設計、開發、運維階段保持高度敏感、追求極致，才能讓我們系統的可用性、伸縮性不會隨著業務增長而劣化。

到此這篇關於慢SQL分析與優化的文章就介紹到這了,更多相關慢SQL分析優化內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

參考資料

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