一、背景介紹

從系統(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í)行效率的主要因素：

1）數(shù)據(jù)量

SQL 執(zhí)行后返回給客戶端的數(shù)據(jù)量的大小；

數(shù)據(jù)量越大需要掃描的 I/O 次數(shù)越多，數(shù)據(jù)庫(kù)服務(wù)器的 IO 更容易成為瓶頸。

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

數(shù)據(jù)在緩存中還是在磁盤(pán)上；

是否能夠通過(guò)全局索引快速尋址；

是否結(jié)合謂詞條件命中全局索引加速掃描。

3）數(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 方式。

1、優(yōu)化思路

?1）減少數(shù)據(jù)掃描（減少磁盤(pán)訪問(wèn)）

盡量在查詢中加入一些可以提前過(guò)濾數(shù)據(jù)的謂詞條件，比如按照時(shí)間過(guò)濾數(shù)據(jù)等，可以減少數(shù)據(jù)的掃描量，對(duì)查詢更友好；

在掃描大表數(shù)據(jù)時(shí)是否可以命中索引，減少回表代價(jià)，避免全表掃描。

2）返回更少數(shù)據(jù)（減少網(wǎng)絡(luò)傳輸或磁盤(pán)訪問(wèn)）

3）減少交互次數(shù)（減少網(wǎng)絡(luò)傳輸）

將數(shù)據(jù)存放在更快的地方；

某條查詢涉及到大表，無(wú)法進(jìn)一步優(yōu)化，如果返回的數(shù)據(jù)量不大且變化頻率不高但訪問(wèn)頻率很高，此時(shí)應(yīng)該考慮將返回的數(shù)據(jù)放在應(yīng)用端的緩存當(dāng)中或者 Redis 這樣的緩存當(dāng)中，以提高存取速度。

4）減少服務(wù)器 CPU 開(kāi)銷(xiāo)（減少 CPU 及內(nèi)存開(kāi)銷(xiāo)）

5）避免大事務(wù)操作

6）利用更多資源（增加資源）

2、優(yōu)化案例

1）數(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è)分段查詢互不依賴(lài)；較方式一的缺點(diǎn)在于較依賴(lài)數(shù)據(jù)的連續(xù)性，若數(shù)據(jù)過(guò)于分散，代價(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;
}

2）優(yōu)化 GROUP BY

提高 GROUP BY 語(yǔ)句的效率, 可以通過(guò)將不需要的記錄在 GROUP BY 之前過(guò)濾掉.下面兩個(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

3）范圍查詢

聯(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 的情況下

a. 從索引里找到所有 trade_date_time 在'2019-05-01' 到'2020-05-01' 區(qū)間的主鍵 id，假設(shè)有 100 萬(wàn)個(gè)；

b. 對(duì)這些 id 進(jìn)行排序（為的是在下面一步回表操作中優(yōu)化 I/O 操作，因?yàn)楹芏喟さ媒闹麈I可能一次磁盤(pán) I/O 就都取到了）；

c. 回表，查出 100 萬(wàn)行記錄，然后逐個(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'為例：

a. 在范圍查找的時(shí)候,直接找到最大,最小的值,然后進(jìn)行鏈表遍歷，故僅能用到 trade_date_time 的索引，無(wú)法使用到 org_code 索引；

b. 基于 MySQL5.6+的索引下推特性，雖然 org_code 字段無(wú)法使用到索引樹(shù)，但是可以用于過(guò)濾回表的主鍵 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ù)需求。

4）優(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使不使用索引與所查列無(wú)關(guān)，只與索引本身，where條件，order by 字段，group by 字段有關(guān)。索引的作用一個(gè)是查找，一個(gè)是排序。

5）業(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í)該條件無(wú)法走索引。我們可以結(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
}

?3、數(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ù)在物理上分隔開(kāi)，不同分區(qū)的數(shù)據(jù)可以制定保存在處于不同磁盤(pán)上的數(shù)據(jù)文件里。這樣，當(dāng)對(duì)這個(gè)表進(jìn)行查詢時(shí)，只需要在表分區(qū)中進(jìn)行掃描，而不必進(jìn)行全表掃描，明顯縮短了查詢時(shí)間，另外處于不同磁盤(pán)的分區(qū)也將對(duì)這個(gè)表的數(shù)據(jù)傳輸分散在不同的磁盤(pán) I/O，一個(gè)精心設(shè)置的分區(qū)可以將數(shù)據(jù)傳輸對(duì)磁盤(pán) I/O 競(jìng)爭(zhēng)均勻地分散開(kāi)。對(duì)數(shù)據(jù)量大的表可采取此方法，可按月建表分區(qū)。

?4、SQL 語(yǔ)句優(yōu)化

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

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

?5、大表優(yōu)化

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

三、原理剖析

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

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

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

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

2）缺點(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ù)的更新效率

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

1）主鍵索引

2）普通索引

3）組合索引

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

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

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

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

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

Dynamic 行存儲(chǔ)格式下，對(duì)于處理行溢出（當(dāng)一個(gè)字段存儲(chǔ)長(zhǎng)度過(guò)大時(shí)，會(huì)發(fā)生行溢出）時(shí)，僅存放溢出頁(yè)內(nèi)存地址。

?4、索引的設(shè)計(jì)原則

1）哪些情況適合建索引

• 數(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)：

a. 連接表數(shù)量最好不要超過(guò) 3 張，每增加一張表就相當(dāng)于增加了一次嵌套循環(huán)，數(shù)量級(jí)增長(zhǎng)會(huì)非?？?/p>

b. 對(duì)多表查詢時(shí)的 where 條件創(chuàng)建索引

c. 對(duì)連接字段創(chuàng)建索引，并且數(shù)據(jù)類(lèi)型保持一致

• 在確定數(shù)據(jù)范圍的情況下盡量使用數(shù)據(jù)類(lèi)型較小的，因?yàn)樗饕龝?huì)也會(huì)占用空間

• 對(duì)字符串創(chuàng)建索引時(shí)建議使用字符串的前綴作為索引

• 這樣做的好處是：

a. 能節(jié)省索引的空間；

b. 雖然不能精確定位，但是能夠定位到相同的前綴，然后通過(guò)主鍵查詢完整的字符串，這樣既能節(jié)省空間，又減少了字符串的比較時(shí)間，還能解決排序問(wèn)題。

• 區(qū)分度高（散列性高）的字段適合作為索引。

• 在多個(gè)字段需要?jiǎng)?chuàng)建索引的情況下，聯(lián)合索引優(yōu)先于單值索引。使用最頻繁的列作為索引的最左側(cè)。

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

• 在 where 條件中用不到的字段不需要。

• 數(shù)據(jù)量小的不需要建索引，比如數(shù)據(jù)少于 1000 條。

• 由大量重復(fù)數(shù)據(jù)的列上不要建索引，比如性別字段中只有男和女時(shí)。

• 避免在經(jīng)常更新的表或字段中創(chuàng)建過(guò)多的索引。

• 不建議主鍵使用無(wú)序的值作為索引，比如 uuid。

• 不要定義冗余或重復(fù)的索引。

• 例如：已經(jīng)創(chuàng)建了聯(lián)合索引 key(id,name)后就不需要再單獨(dú)建一個(gè) key(id)的索引。

?5、索引優(yōu)化之 MRR

例如有一張表 user，主鍵 id，普通字段 age，為 age 創(chuàng)建非聚集索引，有一條查詢語(yǔ)句 select* user from table where age > 18;(注意查詢語(yǔ)句中的結(jié)果是*)

在 MySQL5.5 以及之前的版本中如何查詢呢？先通過(guò)非聚集索引查詢到 age>18 的第一條數(shù)據(jù)，獲取到了主鍵 id；然后根據(jù)非聚集索引中的葉子節(jié)點(diǎn)存儲(chǔ)的主鍵 id 去聚集索引中查詢行數(shù)據(jù)；根據(jù) age>18 的數(shù)據(jù)條數(shù)每次查詢聚集索引，這個(gè)過(guò)程叫做回表。

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

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

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

?6、索引下推

假設(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過(guò)濾。按照過(guò)濾后的數(shù)據(jù)再一一進(jìn)行回表掃描。

索引下推使用條件

• 只能用于range、 ref、 eq_ref、ref_or_null訪問(wèn)方法；

• 只能用于InnoDB和 MyISAM存儲(chǔ)引擎及其分區(qū)表；

• 對(duì)存儲(chǔ)引擎來(lái)說(shuō)，索引下推只適用于二級(jí)索引（也叫輔助索引）；

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

• 引用了子查詢的條件不能下推；

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

?7、思考

1）MySQL 一張表到底能存多少數(shù)據(jù)？

2）為什么要控制單行數(shù)據(jù)大小?

3）優(yōu)化案例 4 中優(yōu)化前的 SQL 為什么走不到索引？

四、總結(jié)

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