盤點(diǎn)MySQL慢查詢的12個(gè)原因

來(lái)源丨撿田螺的小男孩

作者丨撿田螺的小男孩

前言

日常開發(fā)中，我們經(jīng)常會(huì)遇到數(shù)據(jù)庫(kù)慢查詢。那么導(dǎo)致數(shù)據(jù)慢查詢都有哪些常見的原因呢？今天田螺哥就跟大家聊聊導(dǎo)致MySQL慢查詢的12個(gè)常見原因，以及對(duì)應(yīng)的解決方法。

1. SQL沒加索引

很多時(shí)候，我們的慢查詢，都是因?yàn)?strong>沒有加索引。如果沒有加索引的話，會(huì)導(dǎo)致全表掃描的。因此，應(yīng)考慮在where的條件列，建立索引，盡量避免全表掃描。

反例：

select?*?from?user_info?where?name?='撿田螺的小男孩公眾號(hào)'?;

正例：

//添加索引
alter?table?user_info?add?index?idx_name?(name);

2. SQL 索引不生效

有時(shí)候我們明明加了索引了，但是索引卻不生效。在哪些場(chǎng)景，索引會(huì)不生效呢？主要有以下十大經(jīng)典場(chǎng)景：

2.1 隱式的類型轉(zhuǎn)換，索引失效

我們創(chuàng)建一個(gè)用戶user表

CREATE?TABLE?user?(
??id?int(11)?NOT?NULL?AUTO_INCREMENT,
??userId?varchar(32)?NOT?NULL,
??age??varchar(16)?NOT?NULL,
??name?varchar(255)?NOT?NULL,
??PRIMARY?KEY?(id),
??KEY?idx_userid?(userId)?USING?BTREE
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

userId字段為字串類型，是B+樹的普通索引，如果查詢條件傳了一個(gè)數(shù)字過(guò)去，會(huì)導(dǎo)致索引失效。如下：

如果給數(shù)字加上'',也就是說(shuō)，傳的是一個(gè)字符串呢，當(dāng)然是走索引，如下圖：

為什么第一條語(yǔ)句未加單引號(hào)就不走索引了呢？這是因?yàn)椴患訂我?hào)時(shí)，是字符串跟數(shù)字的比較，它們類型不匹配，MySQL會(huì)做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點(diǎn)數(shù)再做比較。隱式的類型轉(zhuǎn)換，索引會(huì)失效。

2.2 查詢條件包含or，可能導(dǎo)致索引失效

我們還是用這個(gè)表結(jié)構(gòu)：

CREATE?TABLE?user?(
??id?int(11)?NOT?NULL?AUTO_INCREMENT,
??userId?varchar(32)?NOT?NULL,
??age??varchar(16)?NOT?NULL,
??name?varchar(255)?NOT?NULL,
??PRIMARY?KEY?(id),
??KEY?idx_userid?(userId)?USING?BTREE
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

其中userId加了索引，但是age沒有加索引的。我們使用了or，以下SQL是不走索引的，如下：

對(duì)于or+沒有索引的age這種情況，假設(shè)它走了userId的索引，但是走到age查詢條件時(shí)，它還得全表掃描，也就是需要三步過(guò)程：全表掃描+索引掃描+合并。如果它一開始就走全表掃描，直接一遍掃描就完事。Mysql優(yōu)化器出于效率與成本考慮，遇到or條件，讓索引失效，看起來(lái)也合情合理嘛。

注意：如果or條件的列都加了索引，索引可能會(huì)走也可能不走，大家可以自己試一試哈。但是平時(shí)大家使用的時(shí)候，還是要注意一下這個(gè)or，學(xué)會(huì)用explain分析。遇到不走索引的時(shí)候，考慮拆開兩條SQL。

2.3. like通配符可能導(dǎo)致索引失效。

并不是用了like通配符，索引一定會(huì)失效，而是like查詢是以%開頭，才會(huì)導(dǎo)致索引失效。

like查詢以%開頭，索引失效

explain?select?*?from?user?where?userId?like?'%123';

把%放后面，發(fā)現(xiàn)索引還是正常走的，如下：

explain?select?*?from?user?where?userId?like?'123%';

既然like查詢以%開頭，會(huì)導(dǎo)致索引失效。我們?nèi)绾蝺?yōu)化呢？

• 使用覆蓋索引
• 把%放后面

2.4 查詢條件不滿足聯(lián)合索引的最左匹配原則

MySQl建立聯(lián)合索引時(shí)，會(huì)遵循最左前綴匹配的原則，即最左優(yōu)先。如果你建立一個(gè)（a,b,c）的聯(lián)合索引，相當(dāng)于建立了(a)、(a,b)、(a,b,c)三個(gè)索引。

假設(shè)有以下表結(jié)構(gòu)：

CREATE?TABLE?user?(
??id?int(11)?NOT?NULL?AUTO_INCREMENT,
??user_id?varchar(32)?NOT?NULL,
??age??varchar(16)?NOT?NULL,
??name?varchar(255)?NOT?NULL,
??PRIMARY?KEY?(id),
??KEY?idx_userid_name?(user_id,name)?USING?BTREE
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

有一個(gè)聯(lián)合索引idx_userid_name，我們執(zhí)行這個(gè)SQL，查詢條件是name，索引是無(wú)效：

explain?select?*?from?user?where?name?='撿田螺的小男孩';

因?yàn)椴樵儣l件列name不是聯(lián)合索引idx_userid_name中的第一個(gè)列，索引不生效

在聯(lián)合索引中，查詢條件滿足最左匹配原則時(shí)，索引才正常生效。

2.5 在索引列上使用mysql的內(nèi)置函數(shù)

表結(jié)構(gòu)：

CREATE?TABLE?`user`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`userId`?varchar(32)?NOT?NULL,
??`login_time`?datetime?NOT?NULL,
??PRIMARY?KEY?(`id`),
??KEY?`idx_userId`?(`userId`)?USING?BTREE,
??KEY?`idx_login_time`?(`login_Time`)?USING?BTREE
)?ENGINE=InnoDB?AUTO_INCREMENT=2?DEFAULT?CHARSET=utf8;

雖然login_time加了索引，但是因?yàn)槭褂昧?code style="margin-right: 2px;margin-left: 2px;padding: 2px 4px;font-size: 14px;border-radius: 4px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);">mysql的內(nèi)置函數(shù)Date_ADD()，索引直接GG，如圖：

一般這種情況怎么優(yōu)化呢？可以把內(nèi)置函數(shù)的邏輯轉(zhuǎn)移到右邊，如下：

explain??select?*?from?user?where?login_time?=?DATE_ADD('2022-05-22?00:00:00',INTERVAL?-1?DAY);

2.6 對(duì)索引進(jìn)行列運(yùn)算（如，+、-、*、/）,索引不生效

表結(jié)構(gòu)：

CREATE?TABLE?`user`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`userId`?varchar(32)?NOT?NULL,
??`age`?int(11)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`),
??KEY?`idx_age`?(`age`)?USING?BTREE
)?ENGINE=InnoDB?AUTO_INCREMENT=2?DEFAULT?CHARSET=utf8;

雖然age加了索引，但是因?yàn)樗M(jìn)行運(yùn)算，索引直接迷路了。。。如圖：

所以不可以對(duì)索引列進(jìn)行運(yùn)算，可以在代碼處理好，再傳參進(jìn)去。

2.7 索引字段上使用（！= 或者 < >），索引可能失效

表結(jié)構(gòu)：

CREATE?TABLE?`user`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`userId`?int(11)?NOT?NULL,
??`age`?int(11)?DEFAULT?NULL,
??`name`?varchar(255)?NOT?NULL,
??PRIMARY?KEY?(`id`),
??KEY?`idx_age`?(`age`)?USING?BTREE
)?ENGINE=InnoDB?AUTO_INCREMENT=2?DEFAULT?CHARSET=utf8;

雖然age加了索引，但是使用了！=?或者< >，not in這些時(shí)，索引如同虛設(shè)。如下：

其實(shí)這個(gè)也是跟mySQL優(yōu)化器有關(guān)，如果優(yōu)化器覺得即使走了索引，還是需要掃描很多很多行的哈，它覺得不劃算，不如直接不走索引。平時(shí)我們用！=?或者< >，not in的時(shí)候，留點(diǎn)心眼哈。

2.8 索引字段上使用is null， is not null，索引可能失效

表結(jié)構(gòu):

CREATE?TABLE?`user`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`card`?varchar(255)?DEFAULT?NULL,
??`name`?varchar(255)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`),
??KEY?`idx_name`?(`name`)?USING?BTREE,
??KEY?`idx_card`?(`card`)?USING?BTREE
)?ENGINE=InnoDB?AUTO_INCREMENT=2?DEFAULT?CHARSET=utf8;

單個(gè)name字段加上索引，并查詢name為非空的語(yǔ)句，其實(shí)會(huì)走索引的，如下:

單個(gè)card字段加上索引，并查詢name為非空的語(yǔ)句，其實(shí)會(huì)走索引的，如下:

但是它兩用or連接起來(lái)，索引就失效了，如下：

很多時(shí)候，也是因?yàn)閿?shù)據(jù)量問(wèn)題，導(dǎo)致了MySQL優(yōu)化器放棄走索引。同時(shí)，平時(shí)我們用explain分析SQL的時(shí)候，如果type=range,要注意一下哈，因?yàn)檫@個(gè)可能因?yàn)閿?shù)據(jù)量問(wèn)題，導(dǎo)致索引無(wú)效。

2.9 左右連接，關(guān)聯(lián)的字段編碼格式不一樣

新建兩個(gè)表，一個(gè)user，一個(gè)user_job

CREATE?TABLE?`user`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`name`?varchar(255)?CHARACTER?SET?utf8mb4?DEFAULT?NULL,
??`age`?int(11)?NOT?NULL,
??PRIMARY?KEY?(`id`),
??KEY?`idx_name`?(`name`)?USING?BTREE
)?ENGINE=InnoDB?AUTO_INCREMENT=2?DEFAULT?CHARSET=utf8;

CREATE?TABLE?`user_job`?(
??`id`?int(11)?NOT?NULL,
??`userId`?int(11)?NOT?NULL,
??`job`?varchar(255)?DEFAULT?NULL,
??`name`?varchar(255)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`),
??KEY?`idx_name`?(`name`)?USING?BTREE
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

user表的name字段編碼是utf8mb4，而user_job表的name字段編碼為utf8。

執(zhí)行左外連接查詢,user_job表還是走全表掃描，如下：

如果把它們的name字段改為編碼一致，相同的SQL，還是會(huì)走索引。

所以大家在做表關(guān)聯(lián)時(shí)，注意一下關(guān)聯(lián)字段的編碼問(wèn)題哈。

2.10 優(yōu)化器選錯(cuò)了索引

MySQL 中一張表是可以支持多個(gè)索引的。你寫SQL語(yǔ)句的時(shí)候，沒有主動(dòng)指定使用哪個(gè)索引的話，用哪個(gè)索引是由MySQL來(lái)確定的。

我們?nèi)粘ｉ_發(fā)中，不斷地刪除歷史數(shù)據(jù)和新增數(shù)據(jù)的場(chǎng)景，有可能會(huì)導(dǎo)致MySQL選錯(cuò)索引。那么有哪些解決方案呢？

• 使用force index?強(qiáng)行選擇某個(gè)索引
• 修改你的SQl，引導(dǎo)它使用我們期望的索引
• 優(yōu)化你的業(yè)務(wù)邏輯
• 優(yōu)化你的索引，新建一個(gè)更合適的索引，或者刪除誤用的索引。

3. limit深分頁(yè)問(wèn)題

limit深分頁(yè)問(wèn)題，會(huì)導(dǎo)致慢查詢，應(yīng)該大家都司空見慣了吧。

3.1 limit深分頁(yè)為什么會(huì)變慢

limit深分頁(yè)為什么會(huì)導(dǎo)致SQL變慢呢？假設(shè)我們有表結(jié)構(gòu)如下：

CREATE?TABLE?account?(
??id?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵Id',
??name?varchar(255)?DEFAULT?NULL?COMMENT?'賬戶名',
??balance?int(11)?DEFAULT?NULL?COMMENT?'余額',
??create_time?datetime?NOT?NULL?COMMENT?'創(chuàng)建時(shí)間',
??update_time?datetime?NOT?NULL?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'更新時(shí)間',
??PRIMARY?KEY?(id),
??KEY?idx_name?(name),
??KEY?idx_create_time?(create_time)?//索引
)?ENGINE=InnoDB?AUTO_INCREMENT=1570068?DEFAULT?CHARSET=utf8?ROW_FORMAT=REDUNDANT?COMMENT='賬戶表';

你知道以下SQL，執(zhí)行過(guò)程是怎樣的嘛？

select?id,name,balance?from?account?where?create_time>?'2020-09-19'?limit?100000,10;

這個(gè)SQL的執(zhí)行流程：

1. 通過(guò)普通二級(jí)索引樹idx_create_time，過(guò)濾create_time條件，找到滿足條件的主鍵id。
2. 通過(guò)主鍵id，回到id主鍵索引樹，找到滿足記錄的行，然后取出需要展示的列（回表過(guò)程）
3. 掃描滿足條件的100010行，然后扔掉前100000行，返回。

limit深分頁(yè)，導(dǎo)致SQL變慢原因有兩個(gè)：

• limit語(yǔ)句會(huì)先掃描offset+n行，然后再丟棄掉前offset行，返回后n行數(shù)據(jù)。也就是說(shuō)limit 100000,10，就會(huì)掃描100010行，而limit?0,10，只掃描10行。
• limit 100000,10?掃描更多的行數(shù)，也意味著回表更多的次數(shù)。

3.2 如何優(yōu)化深分頁(yè)問(wèn)題

我們可以通過(guò)減少回表次數(shù)來(lái)優(yōu)化。一般有標(biāo)簽記錄法和延遲關(guān)聯(lián)法。

標(biāo)簽記錄法

就是標(biāo)記一下上次查詢到哪一條了，下次再來(lái)查的時(shí)候，從該條開始往下掃描。就好像看書一樣，上次看到哪里了，你就折疊一下或者夾個(gè)書簽，下次來(lái)看的時(shí)候，直接就翻到啦。

假設(shè)上一次記錄到100000，則SQL可以修改為：

select??id,name,balance?FROM?account?where?id?>?100000?limit?10;

這樣的話，后面無(wú)論翻多少頁(yè)，性能都會(huì)不錯(cuò)的，因?yàn)槊辛?code style="margin-right: 2px;margin-left: 2px;padding: 2px 4px;font-size: 14px;border-radius: 4px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);">id索引。但是這種方式有局限性：需要一種類似連續(xù)自增的字段。

延遲關(guān)聯(lián)法

延遲關(guān)聯(lián)法，就是把條件轉(zhuǎn)移到主鍵索引樹，然后減少回表。如下：

select??acct1.id,acct1.name,acct1.balance?FROM?account?acct1?INNER?JOIN?(SELECT?a.id?FROM?account?a?WHERE?a.create_time?>?'2020-09-19'?limit?100000,?10)?AS?acct2?on?acct1.id=?acct2.id;

優(yōu)化思路就是，先通過(guò)idx_create_time二級(jí)索引樹查詢到滿足條件的主鍵ID，再與原表通過(guò)主鍵ID內(nèi)連接，這樣后面直接走了主鍵索引了，同時(shí)也減少了回表。

4. 單表數(shù)據(jù)量太大

4.1 單表數(shù)據(jù)量太大為什么會(huì)變慢？

一個(gè)表的數(shù)據(jù)量達(dá)到好幾千萬(wàn)或者上億時(shí)，加索引的效果沒那么明顯啦。性能之所以會(huì)變差，是因?yàn)榫S護(hù)索引的B+樹結(jié)構(gòu)層級(jí)變得更高了，查詢一條數(shù)據(jù)時(shí)，需要經(jīng)歷的磁盤IO變多，因此查詢性能變慢。

4.2 一棵B+樹可以存多少數(shù)據(jù)量

大家是否還記得，一個(gè)B+樹大概可以存放多少數(shù)據(jù)量呢？

InnoDB存儲(chǔ)引擎最小儲(chǔ)存單元是頁(yè)，一頁(yè)大小就是16k。

B+樹葉子存的是數(shù)據(jù)，內(nèi)部節(jié)點(diǎn)存的是鍵值+指針。索引組織表通過(guò)非葉子節(jié)點(diǎn)的二分查找法以及指針確定數(shù)據(jù)在哪個(gè)頁(yè)中，進(jìn)而再去數(shù)據(jù)頁(yè)中找到需要的數(shù)據(jù)；

假設(shè)B+樹的高度為2的話，即有一個(gè)根結(jié)點(diǎn)和若干個(gè)葉子結(jié)點(diǎn)。這棵B+樹的存放總記錄數(shù)為=根結(jié)點(diǎn)指針數(shù)*單個(gè)葉子節(jié)點(diǎn)記錄行數(shù)。

• 如果一行記錄的數(shù)據(jù)大小為1k，那么單個(gè)葉子節(jié)點(diǎn)可以存的記錄數(shù) =16k/1k =16.
• 非葉子節(jié)點(diǎn)內(nèi)存放多少指針呢？我們假設(shè)主鍵ID為bigint類型，長(zhǎng)度為8字節(jié)(面試官問(wèn)你int類型，一個(gè)int就是32位，4字節(jié))，而指針大小在InnoDB源碼中設(shè)置為6字節(jié)，所以就是8+6=14字節(jié)，16k/14B =16*1024B/14B = 1170

因此，一棵高度為2的B+樹，能存放1170 * 16=18720條這樣的數(shù)據(jù)記錄。同理一棵高度為3的B+樹，能存放1170 *1170 *16 =21902400，也就是說(shuō)，可以存放兩千萬(wàn)左右的記錄。B+樹高度一般為1-3層，已經(jīng)滿足千萬(wàn)級(jí)別的數(shù)據(jù)存儲(chǔ)。

如果B+樹想存儲(chǔ)更多的數(shù)據(jù)，那樹結(jié)構(gòu)層級(jí)就會(huì)更高，查詢一條數(shù)據(jù)時(shí)，需要經(jīng)歷的磁盤IO變多，因此查詢性能變慢。

4.3 如何解決單表數(shù)據(jù)量太大，查詢變慢的問(wèn)題

一般超過(guò)千萬(wàn)級(jí)別，我們可以考慮分庫(kù)分表了。

分庫(kù)分表可能導(dǎo)致的問(wèn)題：

• 事務(wù)問(wèn)題
• 跨庫(kù)問(wèn)題
• 排序問(wèn)題
• 分頁(yè)問(wèn)題
• 分布式ID

因此，大家在評(píng)估是否分庫(kù)分表前，先考慮下，是否可以把部分歷史數(shù)據(jù)歸檔先，如果可以的話，先不要急著分庫(kù)分表。如果真的要分庫(kù)分表，綜合考慮和評(píng)估方案。比如可以考慮垂直、水平分庫(kù)分表。水平分庫(kù)分表策略的話，range范圍、hash取模、range+hash取?；旌?/strong>等等。

5. join 或者子查詢過(guò)多

一般來(lái)說(shuō)，不建議使用子查詢，可以把子查詢改成join來(lái)優(yōu)化。而數(shù)據(jù)庫(kù)有個(gè)規(guī)范約定就是：盡量不要有超過(guò)3個(gè)以上的表連接。為什么要這么建議呢? 我們來(lái)聊聊，join哪些方面可能導(dǎo)致慢查詢吧。

MySQL中，join的執(zhí)行算法，分別是：Index Nested-Loop Join和Block Nested-Loop Join。

• Index Nested-Loop Join：這個(gè)join算法，跟我們寫程序時(shí)的嵌套查詢類似，并且可以用上被驅(qū)動(dòng)表的索引。
• Block Nested-Loop Join：這種join算法，被驅(qū)動(dòng)表上沒有可用的索引,它會(huì)先把驅(qū)動(dòng)表的數(shù)據(jù)讀入線程內(nèi)存join_buffer中，再掃描被驅(qū)動(dòng)表，把被驅(qū)動(dòng)表的每一行取出來(lái)，跟join_buffer中的數(shù)據(jù)做對(duì)比，滿足join條件的，作為結(jié)果集的一部分返回。

join過(guò)多的問(wèn)題：

一方面，過(guò)多的表連接，會(huì)大大增加SQL復(fù)雜度。另外一方面，如果可以使用被驅(qū)動(dòng)表的索引那還好，并且使用小表來(lái)做驅(qū)動(dòng)表，查詢效率更佳。如果被驅(qū)動(dòng)表沒有可用的索引，join是在join_buffer內(nèi)存做的，如果匹配的數(shù)據(jù)量比較小或者join_buffer設(shè)置的比較大，速度也不會(huì)太慢。但是，如果join的數(shù)據(jù)量比較大時(shí)，mysql會(huì)采用在硬盤上創(chuàng)建臨時(shí)表的方式進(jìn)行多張表的關(guān)聯(lián)匹配，這種顯然效率就極低，本來(lái)磁盤的 IO 就不快，還要關(guān)聯(lián)。

一般情況下，如果業(yè)務(wù)需要的話，關(guān)聯(lián)2~3個(gè)表是可以接受的，但是關(guān)聯(lián)的字段需要加索引哈。如果需要關(guān)聯(lián)更多的表，建議從代碼層面進(jìn)行拆分，在業(yè)務(wù)層先查詢一張表的數(shù)據(jù)，然后以關(guān)聯(lián)字段作為條件查詢關(guān)聯(lián)表形成map，然后在業(yè)務(wù)層進(jìn)行數(shù)據(jù)的拼裝。

6. in元素過(guò)多

如果使用了in，即使后面的條件加了索引，還是要注意in后面的元素不要過(guò)多哈。in元素一般建議不要超過(guò)500個(gè)，如果超過(guò)了，建議分組，每次500一組進(jìn)行哈。

反例：

select?user_id,name?from?user?where?user_id?in?(1,2,3...1000000);?

如果我們對(duì)in的條件不做任何限制的話，該查詢語(yǔ)句一次性可能會(huì)查詢出非常多的數(shù)據(jù)，很容易導(dǎo)致接口超時(shí)。尤其有時(shí)候，我們是用的子查詢，in后面的子查詢，你都不知道數(shù)量有多少那種，更容易采坑（所以我把in元素過(guò)多抽出來(lái)作為一個(gè)小節(jié)）。如下這種子查詢：

select?*?from?user?where?user_id?in?(select?author_id?from?artilce?where?type?=?1);

正例是，分批進(jìn)行，每批500個(gè)：

select?user_id,name?from?user?where?user_id?in?(1,2,3...500);

如果傳參的ids太多，還可以做個(gè)參數(shù)校驗(yàn)什么的

if?(userIds.size()?>?500)?{
????throw?new?Exception("單次查詢的用戶Id不能超過(guò)200");
}

7. 數(shù)據(jù)庫(kù)在刷臟頁(yè)

7.1 什么是臟頁(yè)

當(dāng)內(nèi)存數(shù)據(jù)頁(yè)跟磁盤數(shù)據(jù)頁(yè)內(nèi)容不一致的時(shí)候，我們稱這個(gè)內(nèi)存頁(yè)為“臟頁(yè)”。內(nèi)存數(shù)據(jù)寫入到磁盤后，內(nèi)存和磁盤上的數(shù)據(jù)頁(yè)的內(nèi)容就一致了，稱為“干凈頁(yè)”。一般有更新SQL才可能會(huì)導(dǎo)致臟頁(yè)，我們回憶一下：一條更新語(yǔ)句是如何執(zhí)行的

7.2 一條更新語(yǔ)句是如何執(zhí)行的？

以下的這個(gè)更新SQL，如何執(zhí)行的呢？

update?t?set?c=c+1?where?id=666;

1. 對(duì)于這條更新SQL，執(zhí)行器會(huì)先找引擎取id=666這一行。如果這行所在的數(shù)據(jù)頁(yè)本來(lái)就在內(nèi)存中的話，就直接返回給執(zhí)行器。如果不在內(nèi)存，就去磁盤讀入內(nèi)存，再返回。
2. 執(zhí)行器拿到引擎給的行數(shù)據(jù)后，給這一行C的值加一，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。
3. 引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到redo log里面，但是此時(shí)redo log?是處于prepare狀態(tài)的哈。
4. 執(zhí)行器生成這個(gè)操作的binlog，并把binlog寫入磁盤。
5. 執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的redo log改成提交（commit）狀態(tài)，更新完成。

InnoDB 在處理更新語(yǔ)句的時(shí)候，只做了寫日志這一個(gè)磁盤操作。這個(gè)日志叫作redo log（重做日志）。平時(shí)更新SQL執(zhí)行得很快，其實(shí)是因?yàn)樗皇窃趯憙?nèi)存和redo log日志，等到空閑的時(shí)候，才把redo log日志里的數(shù)據(jù)同步到磁盤中。

有些小伙伴可能有疑惑，redo log日志不是在磁盤嘛？那為什么不慢？其實(shí)是因?yàn)閷?code style="margin-right: 2px;margin-left: 2px;padding: 2px 4px;font-size: 14px;border-radius: 4px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);">redo log的過(guò)程是順序?qū)懘疟P的。磁盤順序?qū)?/strong>會(huì)減少尋道等待時(shí)間，速度比隨機(jī)寫要快很多的。

7.3 為什么會(huì)出現(xiàn)臟頁(yè)呢？

更新SQL只是在寫內(nèi)存和redo log日志，等到空閑的時(shí)候，才把redo log日志里的數(shù)據(jù)同步到磁盤中。這時(shí)內(nèi)存數(shù)據(jù)頁(yè)跟磁盤數(shù)據(jù)頁(yè)內(nèi)容不一致,就出現(xiàn)臟頁(yè)。

7.4 什么時(shí)候會(huì)刷臟頁(yè)（flush）？

InnoDB存儲(chǔ)引擎的redo log大小是固定，且是環(huán)型寫入的，如下圖（圖片來(lái)源于MySQL 實(shí)戰(zhàn) 45 講）：

那什么時(shí)候會(huì)刷臟頁(yè)？有幾種場(chǎng)景：

1. redo log寫滿了，要刷臟頁(yè)。這種情況要盡量避免的。因?yàn)槌霈F(xiàn)這種情況時(shí)，整個(gè)系統(tǒng)就不能再接受更新啦，即所有的更新都必須堵住。
2. 內(nèi)存不夠了，需要新的內(nèi)存頁(yè)，就要淘汰一些數(shù)據(jù)頁(yè)，這時(shí)候會(huì)刷臟頁(yè)

InnoDB 用緩沖池（buffer pool）管理內(nèi)存,而當(dāng)要讀入的數(shù)據(jù)頁(yè)沒有在內(nèi)存的時(shí)候，就必須到緩沖池中申請(qǐng)一個(gè)數(shù)據(jù)頁(yè)。這時(shí)候只能把最久不使用的數(shù)據(jù)頁(yè)從內(nèi)存中淘汰掉：如果要淘汰的是一個(gè)干凈頁(yè)，就直接釋放出來(lái)復(fù)用；但如果是臟頁(yè)呢，就必須將臟頁(yè)先刷到磁盤，變成干凈頁(yè)后才能復(fù)用。

3. MySQL 認(rèn)為系統(tǒng)空閑的時(shí)候，也會(huì)刷一些臟頁(yè)
4. MySQL 正常關(guān)閉時(shí)，會(huì)把內(nèi)存的臟頁(yè)都 flush 到磁盤上

7.5 為什么刷臟頁(yè)會(huì)導(dǎo)致SQL變慢呢？

1. redo log寫滿了，要刷臟頁(yè)，這時(shí)候會(huì)導(dǎo)致系統(tǒng)所有的更新堵住，寫性能都跌為0了，肯定慢呀。一般要杜絕出現(xiàn)這個(gè)情況。
2. 一個(gè)查詢要淘汰的臟頁(yè)個(gè)數(shù)太多，一樣會(huì)導(dǎo)致查詢的響應(yīng)時(shí)間明顯變長(zhǎng)。

8. order by 文件排序

order by就一定會(huì)導(dǎo)致慢查詢嗎？不是這樣的哈，因?yàn)?code style="margin-right: 2px;margin-left: 2px;padding: 2px 4px;font-size: 14px;border-radius: 4px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);">order by平時(shí)用得多，并且數(shù)據(jù)量一上來(lái)，還是走文件排序的話，很容易有慢SQL的。聽我娓娓道來(lái)，order by哪些時(shí)候可能會(huì)導(dǎo)致慢SQL哈。

8.1 order by 的 Using filesort文件排序

我們平時(shí)經(jīng)常需要用到order by?，主要就是用來(lái)給某些字段排序的。比如以下SQL:

select?name,age,city?from?staff?where?city?=?'深圳'?order?by?age?limit?10;

它表示的意思就是：查詢前10個(gè)，來(lái)自深圳員工的姓名、年齡、城市，并且按照年齡小到大排序。

查看explain執(zhí)行計(jì)劃的時(shí)候，可以看到Extra這一列，有一個(gè)Using filesort，它表示用到文件排序。

8.2 order by文件排序效率為什么較低

order by用到文件排序時(shí)，為什么查詢效率會(huì)相對(duì)低呢？

order by排序，分為全字段排序和rowid排序。它是拿max_length_for_sort_data和結(jié)果行數(shù)據(jù)長(zhǎng)度對(duì)比，如果結(jié)果行數(shù)據(jù)長(zhǎng)度超過(guò)max_length_for_sort_data這個(gè)值，就會(huì)走rowid排序，相反，則走全字段排序。

rowid排序

rowid排序，一般需要回表去找滿足條件的數(shù)據(jù)，所以效率會(huì)慢一點(diǎn)。以下這個(gè)SQL，使用rowid排序，執(zhí)行過(guò)程是這樣：

select?name,age,city?from?staff?where?city?=?'深圳'?order?by?age?limit?10;

1. MySQL 為對(duì)應(yīng)的線程初始化sort_buffer，放入需要排序的age字段，以及主鍵id；
2. 從索引樹idx_city， 找到第一個(gè)滿足?city='深圳’條件的主鍵id，也就是圖中的id=9；
3. 到主鍵id索引樹拿到id=9的這一行數(shù)據(jù)， 取age和主鍵id的值，存到sort_buffer；
4. 從索引樹idx_city拿到下一個(gè)記錄的主鍵id，即圖中的id=13；
5. 重復(fù)步驟 3、4 直到city的值不等于深圳為止；
6. 前面5步已經(jīng)查找到了所有city為深圳的數(shù)據(jù)，在sort_buffer中，將所有數(shù)據(jù)根據(jù)age進(jìn)行排序；
7. 遍歷排序結(jié)果，取前10行，并按照id的值回到原表中，取出city、name 和 age三個(gè)字段返回給客戶端。

全字段排序

同樣的SQL，如果是走全字段排序是這樣的：

select?name,age,city?from?staff?where?city?=?'深圳'?order?by?age?limit?10;

1. MySQL 為對(duì)應(yīng)的線程初始化sort_buffer，放入需要查詢的name、age、city字段；
2. 從索引樹idx_city， 找到第一個(gè)滿足?city='深圳’條件的主鍵 id，也就是圖中的id=9；
3. 到主鍵id索引樹拿到id=9的這一行數(shù)據(jù)， 取name、age、city三個(gè)字段的值，存到sort_buffer；
4. 從索引樹idx_city?拿到下一個(gè)記錄的主鍵id，即圖中的id=13；
5. 重復(fù)步驟 3、4 直到city的值不等于深圳為止；
6. 前面5步已經(jīng)查找到了所有city為深圳的數(shù)據(jù)，在sort_buffer中，將所有數(shù)據(jù)根據(jù)age進(jìn)行排序；
7. 按照排序結(jié)果取前10行返回給客戶端。

sort_buffer的大小是由一個(gè)參數(shù)控制的：sort_buffer_size。

• 如果要排序的數(shù)據(jù)小于sort_buffer_size，排序在sort_buffer內(nèi)存中完成
• 如果要排序的數(shù)據(jù)大于sort_buffer_size，則借助磁盤文件來(lái)進(jìn)行排序。

借助磁盤文件排序的話，效率就更慢一點(diǎn)。因?yàn)橄劝褦?shù)據(jù)放入sort_buffer，當(dāng)快要滿時(shí)。會(huì)排一下序，然后把sort_buffer中的數(shù)據(jù)，放到臨時(shí)磁盤文件，等到所有滿足條件數(shù)據(jù)都查完排完，再用歸并算法把磁盤的臨時(shí)排好序的小文件，合并成一個(gè)有序的大文件。

8.3 如何優(yōu)化order by的文件排序

order by使用文件排序，效率會(huì)低一點(diǎn)。我們?cè)趺磧?yōu)化呢？

• 因?yàn)閿?shù)據(jù)是無(wú)序的，所以就需要排序。如果數(shù)據(jù)本身是有序的，那就不會(huì)再用到文件排序啦。而索引數(shù)據(jù)本身是有序的，我們通過(guò)建立索引來(lái)優(yōu)化order by語(yǔ)句。
• 我們還可以通過(guò)調(diào)整max_length_for_sort_data、sort_buffer_size等參數(shù)優(yōu)化；

大家有興趣可以看下我之前這篇文章哈：看一遍就理解：order by詳解

9. 拿不到鎖

有時(shí)候，我們查詢一條很簡(jiǎn)單的SQL，但是卻等待很長(zhǎng)的時(shí)間，不見結(jié)果返回。一般這種時(shí)候就是表被鎖住了，或者要查詢的某一行或者幾行被鎖住了。我們只能慢慢等待鎖被釋放。

舉一個(gè)生活的例子哈，你和別人合租了一間房子，這個(gè)房子只有一個(gè)衛(wèi)生間的話。假設(shè)某一時(shí)刻，你們都想去衛(wèi)生間，但是對(duì)方比你早了一點(diǎn)點(diǎn)。那么此時(shí)你只能等對(duì)方出來(lái)后才能進(jìn)去。

這時(shí)候，我們可以用show processlist命令，看看當(dāng)前語(yǔ)句處于什么狀態(tài)哈。

10. delete + in子查詢不走索引！

之前見到過(guò)一個(gè)生產(chǎn)慢SQL問(wèn)題，當(dāng)delete遇到in子查詢時(shí)，即使有索引，也是不走索引的。而對(duì)應(yīng)的select + in子查詢，卻可以走索引。

MySQL版本是5.7，假設(shè)當(dāng)前有兩張表account和old_account,表結(jié)構(gòu)如下：

CREATE?TABLE?`old_account`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵Id',
??`name`?varchar(255)?DEFAULT?NULL?COMMENT?'賬戶名',
??`balance`?int(11)?DEFAULT?NULL?COMMENT?'余額',
??`create_time`?datetime?NOT?NULL?COMMENT?'創(chuàng)建時(shí)間',
??`update_time`?datetime?NOT?NULL?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'更新時(shí)間',
??PRIMARY?KEY?(`id`),
??KEY?`idx_name`?(`name`)?USING?BTREE
)?ENGINE=InnoDB?AUTO_INCREMENT=1570068?DEFAULT?CHARSET=utf8?ROW_FORMAT=REDUNDANT?COMMENT='老的賬戶表';

CREATE?TABLE?`account`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵Id',
??`name`?varchar(255)?DEFAULT?NULL?COMMENT?'賬戶名',
??`balance`?int(11)?DEFAULT?NULL?COMMENT?'余額',
??`create_time`?datetime?NOT?NULL?COMMENT?'創(chuàng)建時(shí)間',
??`update_time`?datetime?NOT?NULL?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'更新時(shí)間',
??PRIMARY?KEY?(`id`),
??KEY?`idx_name`?(`name`)?USING?BTREE
)?ENGINE=InnoDB?AUTO_INCREMENT=1570068?DEFAULT?CHARSET=utf8?ROW_FORMAT=REDUNDANT?COMMENT='賬戶表';

執(zhí)行的SQL如下：

delete?from?account?where?name?in?(select?name?from?old_account);

查看執(zhí)行計(jì)劃，發(fā)現(xiàn)不走索引：

但是如果把delete換成select，就會(huì)走索引。如下：

為什么select + in子查詢會(huì)走索引，delete + in子查詢卻不會(huì)走索引呢？

我們執(zhí)行以下SQL看看：

explain?select?*?from?account?where?name?in?(select?name?from?old_account);
show?WARNINGS;?//可以查看優(yōu)化后,最終執(zhí)行的sql

結(jié)果如下：

select?`test2`.`account`.`id`?AS?`id`,`test2`.`account`.`name`?AS?`name`,`test2`.`account`.`balance`?AS?`balance`,`test2`.`account`.`create_time`?AS?`create_time`,`test2`.`account`.`update_time`?AS?`update_time`?from?`test2`.`account`?
semi?join?(`test2`.`old_account`)
where?(`test2`.`account`.`name`?=?`test2`.`old_account`.`name`)

可以發(fā)現(xiàn)，實(shí)際執(zhí)行的時(shí)候，MySQL對(duì)select in子查詢做了優(yōu)化，把子查詢改成join的方式，所以可以走索引。但是很遺憾，對(duì)于delete in子查詢，MySQL卻沒有對(duì)它做這個(gè)優(yōu)化。

日常開發(fā)中，大家注意一下這個(gè)場(chǎng)景哈，大家有興趣可以看下這篇文章哈：生產(chǎn)問(wèn)題分析！delete in子查詢不走索引？！

11、group by使用臨時(shí)表

group by一般用于分組統(tǒng)計(jì)，它表達(dá)的邏輯就是根據(jù)一定的規(guī)則，進(jìn)行分組。日常開發(fā)中，我們使用得比較頻繁。如果不注意，很容易產(chǎn)生慢SQL。

11.1 group by的執(zhí)行流程

假設(shè)有表結(jié)構(gòu)：

CREATE?TABLE?`staff`?(
??`id`?bigint(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵id',
??`id_card`?varchar(20)?NOT?NULL?COMMENT?'身份證號(hào)碼',
??`name`?varchar(64)?NOT?NULL?COMMENT?'姓名',
??`age`?int(4)?NOT?NULL?COMMENT?'年齡',
??`city`?varchar(64)?NOT?NULL?COMMENT?'城市',
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?AUTO_INCREMENT=15?DEFAULT?CHARSET=utf8?COMMENT='員工表';

我們查看一下這個(gè)SQL的執(zhí)行計(jì)劃：

explain?select?city?,count(*)?as?num?from?staff?group?by?city;

• Extra 這個(gè)字段的Using temporary表示在執(zhí)行分組的時(shí)候使用了臨時(shí)表
• Extra 這個(gè)字段的Using filesort表示使用了文件排序

group by是怎么使用到臨時(shí)表和排序了呢？我們來(lái)看下這個(gè)SQL的執(zhí)行流程

select?city?,count(*)?as?num?from?staff?group?by?city;

1. 創(chuàng)建內(nèi)存臨時(shí)表，表里有兩個(gè)字段city和num；
2. 全表掃描staff的記錄，依次取出city = 'X'的記錄。

• 判斷臨時(shí)表中是否有為?city='X'的行，沒有就插入一個(gè)記錄?(X,1);
• 如果臨時(shí)表中有city='X'的行，就將X這一行的num值加 1；

3. 遍歷完成后，再根據(jù)字段city做排序，得到結(jié)果集返回給客戶端。這個(gè)流程的執(zhí)行圖如下：

臨時(shí)表的排序是怎樣的呢？

就是把需要排序的字段，放到sort buffer，排完就返回。在這里注意一點(diǎn)哈，排序分全字段排序和rowid排序

• 如果是全字段排序，需要查詢返回的字段，都放入sort buffer，根據(jù)排序字段排完，直接返回
• 如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。

11.2 ?group by可能會(huì)慢在哪里？

group by使用不當(dāng)，很容易就會(huì)產(chǎn)生慢SQL 問(wèn)題。因?yàn)樗扔玫脚R時(shí)表，又默認(rèn)用到排序。有時(shí)候還可能用到磁盤臨時(shí)表。

• 如果執(zhí)行過(guò)程中，會(huì)發(fā)現(xiàn)內(nèi)存臨時(shí)表大小到達(dá)了上限（控制這個(gè)上限的參數(shù)就是tmp_table_size），會(huì)把內(nèi)存臨時(shí)表轉(zhuǎn)成磁盤臨時(shí)表。
• 如果數(shù)據(jù)量很大，很可能這個(gè)查詢需要的磁盤臨時(shí)表，就會(huì)占用大量的磁盤空間。

11.3 如何優(yōu)化group by呢？

從哪些方向去優(yōu)化呢？

• 方向1：既然它默認(rèn)會(huì)排序，我們不給它排是不是就行啦。
• 方向2：既然臨時(shí)表是影響group by性能的X因素，我們是不是可以不用臨時(shí)表？

我們一起來(lái)想下，執(zhí)行group by語(yǔ)句為什么需要臨時(shí)表呢？group by的語(yǔ)義邏輯，就是統(tǒng)計(jì)不同的值出現(xiàn)的個(gè)數(shù)。如果這個(gè)這些值一開始就是有序的，我們是不是直接往下掃描統(tǒng)計(jì)就好了，就不用臨時(shí)表來(lái)記錄并統(tǒng)計(jì)結(jié)果啦?

可以有這些優(yōu)化方案：

• group by 后面的字段加索引
• order by null 不用排序
• 盡量只使用內(nèi)存臨時(shí)表
• 使用SQL_BIG_RESULT

大家可以看下我這篇文章哈：看一遍就理解：group by詳解

12. 系統(tǒng)硬件或網(wǎng)絡(luò)資源

• 如果數(shù)據(jù)庫(kù)服務(wù)器內(nèi)存、硬件資源，或者網(wǎng)絡(luò)資源配置不是很好，就會(huì)慢一些哈。這時(shí)候可以升級(jí)配置。這就好比你的計(jì)算機(jī)有時(shí)候很卡，你可以加個(gè)內(nèi)存條什么的一個(gè)道理。
• 如果數(shù)據(jù)庫(kù)壓力本身很大，比如高并發(fā)場(chǎng)景下，大量請(qǐng)求到數(shù)據(jù)庫(kù)來(lái)，數(shù)據(jù)庫(kù)服務(wù)器CPU占用很高或者IO利用率很高，這種情況下所有語(yǔ)句的執(zhí)行都有可能變慢的哈。

最后

如果測(cè)試環(huán)境數(shù)據(jù)庫(kù)的一些參數(shù)配置，和生產(chǎn)環(huán)境參數(shù)配置不一致的話，也容易產(chǎn)生慢SQL哈。之前見過(guò)一個(gè)慢SQL的生產(chǎn)案例，就是測(cè)試環(huán)境用了index merge，所以查看explain執(zhí)行計(jì)劃時(shí)，是可以走索引的，但是到了生產(chǎn)，卻全表掃描，最后排查發(fā)現(xiàn)是生產(chǎn)環(huán)境配置把index merge關(guān)閉了。大家是否還遇到其他場(chǎng)景的慢SQL呢？如果有的話，歡迎評(píng)論區(qū)留言交流哈

如果這篇文章對(duì)您有所幫助，或者有所啟發(fā)的話，求一鍵三連：點(diǎn)贊、轉(zhuǎn)發(fā)、在看，您的支持是我堅(jiān)持寫作最大的動(dòng)力。

參考與感謝

• MySQL實(shí)戰(zhàn)45講（https://time.geekbang.org/column/article/72775?cid=100020801）

最后給大家分享我寫的SQL兩件套：《SQL基礎(chǔ)知識(shí)第二版》和《SQL高級(jí)知識(shí)第二版》的PDF電子版。里面有各個(gè)語(yǔ)法的解釋、大量的實(shí)例講解和批注等等，非常通俗易懂，方便大家跟著一起來(lái)實(shí)操。

有需要的讀者可以下載學(xué)習(xí)，在下面的公眾號(hào)「數(shù)據(jù)前線」(非本號(hào))后臺(tái)回復(fù)關(guān)鍵字：SQL，就行
數(shù)據(jù)前線
——End——
后臺(tái)回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺(tái)回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。

推薦閱讀
SQL中 LEFT JOIN 左表合并去重實(shí)用技巧
學(xué) SQL 必須了解的10個(gè)高級(jí)概念
如何寫出一手好 SQL ？很有必要！
SQL中去重的三種方法，還有誰(shuí)不會(huì)？