慢 SQL 分析與優化

背景介紹

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

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

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

優化方式

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

優化思路

優化案例

數據分頁優化

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 >='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 邏輯架構圖：

索引的優缺點

優點

缺點

索引的數據結構

主鍵索引

普通索引

組合索引

索引頁結構

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

數據行格式：

MySQL 有 4 種存儲格式：

  1. Compact

  2. Redundant （5.0 版本以前用，已廢棄）

  3. Dynamic （MySQL5.7 默認格式）

  4. Compressed

Dynamic 行存儲格式下，對於處理行溢出（當一個字段存儲長度過大時，會發生行溢出）時，僅存放溢出頁內存地址。

索引的設計原則

哪些情況適合建索引

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

索引優化之 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過濾。按照過濾後的數據再一一進行回表掃描。

索引下推使用條件

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

思考：

  1. MySQL 一張表到底能存多少數據？

  2. 爲什麼要控制單行數據大小?

  3. 優化案例 4 中優化前的 SQL 爲什麼走不到索引？

總結

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

參考資料

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

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

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

字節跳動技術團隊 字節跳動的技術實踐分享

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/CaSVhAJgycjjbCxAkII2ZA

猜你喜歡