作者 ｜ 伍樓華

背景介紹

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

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

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

優(yōu)化方式

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

數(shù)據(jù)量

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

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

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

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

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

優(yōu)化思路

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

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

• 返回更少數(shù)據(jù)（減少網(wǎng)絡(luò)傳輸或磁盤訪問）
• 減少交互次數(shù)（減少網(wǎng)絡(luò)傳輸）
• 將數(shù)據(jù)存放在更快的地方
• 某條查詢涉及到大表，無法進一步優(yōu)化，如果返回的數(shù)據(jù)量不大且變化頻率不高但訪問頻率很高，此時應(yīng)該考慮將返回的數(shù)據(jù)放在應(yīng)用端的緩存當中或者 Redis 這樣的緩存當中，以提高存取速度。
• 減少服務(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)點在于可并行查詢，每個分段查詢互不依賴；較方式一的缺點在于較依賴數(shù)據(jù)的連續(xù)性，若數(shù)據(jù)過于分散，代價較高。

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 之前過濾掉.下面兩個查詢返回相同結(jié)果但第二個明顯就快了許多。

低效:

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)合索引中如果有某個列存在范圍（大于小于）查詢，其右邊的列是否還有意義？

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

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

以查找 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 數(shù)。

小結(jié)：對于該 case, 索引效果[org_code,trade_date_time] > [trade_date_time, org_code]>[trade_date_time]。實際業(yè)務(wù)場景中，檢索條件中 trade_date_time 基本上肯定會出現(xiàn)，但 org_code 卻不一定，故索引的設(shè)計還需要結(jié)合實際業(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í)行超時被 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 行，耗時 34ms。

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

業(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%以上，此時該條件無法走索引。我們可以結(jié)合業(yè)務(wù)特征，對數(shù)據(jù)獲取按日期進行拆分。

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)化

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

SQL 語句優(yōu)化

SQL 檢查狀態(tài)及分數(shù)計算邏輯

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

大表優(yōu)化

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

原理剖析

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

索引的優(yōu)缺點

優(yōu)點

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

缺點

• 索引需要占物理空間
• 創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加
• 當對表中的數(shù)據(jù)進行增刪改查時，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的更新效率

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

主鍵索引

普通索引

組合索引

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

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

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

MySQL 有 4 種存儲格式：

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

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

索引的設(shè)計原則

哪些情況適合建索引

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

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

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

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

• 在 where 條件中用不到的字段不需要。
• 數(shù)據(jù)量小的不需要建索引，比如數(shù)據(jù)少于 1000 條。
• 由大量重復數(shù)據(jù)的列上不要建索引，比如性別字段中只有男和女時。
• 避免在經(jīng)常更新的表或字段中創(chuàng)建過多的索引。
• 不建議主鍵使用無序的值作為索引，比如 uuid。
• 不要定義冗余或重復的索引
• 例如：已經(jīng)創(chuàng)建了聯(lián)合索引 key(id,name)后就不需要再單獨建一個 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é)點存儲的主鍵 id 去聚集索引中查詢行數(shù)據(jù)；根據(jù) age>18 的數(shù)據(jù)條數(shù)每次查詢聚集索引，這個過程叫做回表。

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

在 MySQL5.6 時針對上述問題進行了優(yōu)化，優(yōu)化器先查詢到 age>3 的所有數(shù)據(jù)的主鍵 id，對所有主鍵的 id 進行排序，排序的結(jié)果緩存到 read_rnd_buffer，然后通過排好序的主鍵在聚簇索引中進行查詢。

如果兩個主鍵的范圍相近，在同一個數(shù)據(jù)頁中就可以之間按照順序獲取，那么磁盤 io 的過程將會大大降低。這個優(yōu)化的過程就叫做 Multi Range Read(MRR) 多返回查詢。

索引下推

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

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

索引下推使用條件

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

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

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

思考：

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

總結(jié)

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