作者 ｜ 伍樓華
背景介紹
從系統設計角度看，一個系統從設計搭建到數據逐步增長，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'為例：
- 在范圍查找的時候,直接找到最大,最小的值,然后進行鏈表遍歷，故僅能用到 trade_date_time 的索引，無法使用到 org_code 索引
- 基于 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
}
數據庫結構優化
- 范式優化：表的設計合理化（符合 3NF），比如消除冗余（節省空間）；
- 反范式優化：比如適當加冗余等（減少 join）
- 拆分表：分區將數據在物理上分隔開，不同分區的數據可以制定保存在處于不同磁盤上的數據文件里。這樣，當對這個表進行查詢時，只需要在表分區中進行掃描，而不必進行全表掃描，明顯縮短了查詢時間，另外處于不同磁盤的分區也將對這個表的數據傳輸分散在不同的磁盤 I/O，一個精心設置的分區可以將數據傳輸對磁盤 I/O 競爭均勻地分散開。對數據量大的表可采取此方法，可按月建表分區。
SQL 語句優化
SQL 檢查狀態及分數計算邏輯
- 盡量避免使用子查詢
- 用 IN 來替換 OR
- 讀取適當的記錄 LIMIT M,N，而不要讀多余的記錄
- 禁止不必要的 Order By 排序
- 總和查詢可以禁止排重用 union all
- 避免隨機取記錄
- 將多次插入換成批量 Insert 插入
- 只返回必要的列，用具體的字段列表代替 select * 語句
- 區分 in 和 exists
- 優化 Group By 語句
- 盡量使用數字型字段
- 優化 Join 語句
大表優化
- 分庫分表（水平、垂直）
- 讀寫分離
- 數據定期歸檔
原理剖析
MySQL 邏輯架構圖：
索引的優缺點
優點
- 提高查詢語句的執行效率，減少 IO 操作的次數
- 創建唯一性索引，可以保證數據庫表中每一行數據的唯一性
- 加了索引的列會進行排序，在使用分組和排序子句進行查詢時，可以顯著減少查詢中分組和排序的時間
缺點
- 索引需要占物理空間
- 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加
- 當對表中的數據進行增刪改查時，索引也要動態的維護，這樣就降低了數據的更新效率
索引的數據結構
主鍵索引
普通索引
組合索引
索引頁結構
索引頁由七部分組成，其中 Infimum 和 Supremum 也屬于記錄，只不過是虛擬記錄，這里為了與用戶記錄區分開，還是決定將兩者拆開。
數據行格式：
MySQL 有 4 種存儲格式：
- Compact
- Redundant （5.0 版本以前用，已廢棄）
- Dynamic （MySQL5.7 默認格式）
- 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 操作。對于的聚簇索引來說，數據和索引是在一起的，不存在回表這一說。
- 引用了子查詢的條件不能下推；
- 引用了存儲函數的條件不能下推，因為存儲引擎無法調用存儲函數。
思考：
- MySQL 一張表到底能存多少數據？
- 為什么要控制單行數據大小?
- 優化案例 4 中優化前的 SQL 為什么走不到索引？
總結
拋開數據庫硬件層面，數據庫表設計、索引設計、業務代碼邏輯、分庫分表策略、數據歸檔策略都對 SQL 執行效率有影響，我們只有在整個設計、開發、運維階段保持高度敏感、追求極致，才能讓我們系統的可用性、伸縮性不會隨著業務增長而劣化。
