1. SQL 沒(méi)加索引

很多時(shí)候，我們的慢查詢，都是因?yàn)闆](méi)有加索引。如果沒(méi)有加索引的話，會(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 隱式的類(lèi)型轉(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 字段為字串類(lèi)型，是 B + 樹(shù)的普通索引，如果查詢條件傳了一個(gè)數(shù)字過(guò)去，會(huì)導(dǎo)致索引失效。

如下：

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

為什么第一條語(yǔ)句未加單引號(hào)就不走索引了呢？這是因?yàn)椴患訂我?hào)時(shí)，是字符串跟數(shù)字的比較，它們類(lèi)型不匹配，MySQL 會(huì)做隱式的類(lèi)型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點(diǎn)數(shù)再做比較。隱式的類(lèi)型轉(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 沒(méi)有加索引的。我們使用了 or，以下 SQL 是不走索引的，如下：

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

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

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

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

like 查詢以 % 開(kāi)頭，索引失效

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

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

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

既然 like 查詢以 % 開(kāi)頭，會(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 TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,userIdvarchar(32) NOT NULL,login_timedatetime NOT NULL, PRIMARY KEY (id), KEYidx_userId(userId) USING BTREE, KEYidx_login_time(login_Time) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

雖然 login_time 加了索引，但是因?yàn)槭褂昧?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 TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,userIdvarchar(32) NOT NULL,ageint(11) DEFAULT NULL, PRIMARY KEY (id), KEYidx_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 TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,userIdint(11) NOT NULL,ageint(11) DEFAULT NULL,namevarchar(255) NOT NULL, PRIMARY KEY (id), KEYidx_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)化器覺(jué)得即使走了索引，還是需要掃描很多很多行的哈，它覺(jué)得不劃算，不如直接不走索引。平時(shí)我們用！= 或者 < >，not in 的時(shí)候，留點(diǎn)心眼哈。

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

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

CREATE TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,cardvarchar(255) DEFAULT NULL,namevarchar(255) DEFAULT NULL, PRIMARY KEY (id), KEYidx_name(name) USING BTREE, KEYidx_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 TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,namevarchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,ageint(11) NOT NULL, PRIMARY KEY (id), KEYidx_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è)索引的。你寫(xiě) SQL 語(yǔ)句的時(shí)候，沒(méi)有主動(dòng)指定使用哪個(gè)索引的話，用哪個(gè)索引是由 MySQL 來(lái)確定的。

我們?nèi)粘ｉ_(kā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)該大家都司空見(jiàn)慣了吧。

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í)行流程：

通過(guò)普通二級(jí)索引樹(shù) idx_create_time，過(guò)濾 create_time 條件，找到滿足條件的主鍵 id。通過(guò)主鍵id，回到 id主鍵索引樹(shù)，找到滿足記錄的行，然后取出需要展示的列（回表過(guò)程）掃描滿足條件的 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í)候，從該條開(kāi)始往下掃描。就好像看書(shū)一樣，上次看到哪里了，你就折疊一下或者夾個(gè)書(shū)簽，下次來(lái)看的時(shí)候，直接就翻到啦。

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

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

這樣的話，后面無(wú)論翻多少頁(yè)，性能都會(huì)不錯(cuò)的，因?yàn)槊辛?id索引。但是這種方式有局限性：需要一種類(lèi)似連續(xù)自增的字段。

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

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

如下：

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í)索引樹(shù)查詢到滿足條件的主鍵ID，再與原表通過(guò)主鍵ID 內(nèi)連接，這樣后面直接走了主鍵索引了，同時(shí)也減少了回表。

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

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

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

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

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

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

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

假設(shè) B + 樹(shù)的高度為 2 的話，即有一個(gè)根結(jié)點(diǎn)和若干個(gè)葉子結(jié)點(diǎn)。這棵 B + 樹(shù)的存放總記錄數(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 類(lèi)型，長(zhǎng)度為 8 字節(jié) (面試官問(wèn)你 int 類(lèi)型，一個(gè) int 就是 32 位，4 字節(jié))，而指針大小在 InnoDB 源碼中設(shè)置為 6 字節(jié)，所以就是 8+6=14 字節(jié)，16k/14B =16*1024B/14B = 1170

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

如果 B + 樹(shù)想存儲(chǔ)更多的數(shù)據(jù)，那樹(shù)結(jié)構(gòu)層級(jí)就會(huì)更高，查詢一條數(shù)據(jù)時(shí)，需要經(jīng)歷的磁盤(pán) 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 取模混合等等。

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 算法，跟我們寫(xiě)程序時(shí)的嵌套查詢類(lèi)似，并且可以用上被驅(qū)動(dòng)表的索引。
• Block Nested-Loop Join：這種 join 算法，被驅(qū)動(dòng)表上沒(méi)有可用的索引 , 它會(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)表沒(méi)有可用的索引，join 是在 join_buffer 內(nèi)存做的，如果匹配的數(shù)據(jù)量比較小或者 join_buffer 設(shè)置的比較大，速度也不會(huì)太慢。但是，如果 join 的數(shù)據(jù)量比較大時(shí)，mysql 會(huì)采用在硬盤(pán)上創(chuàng)建臨時(shí)表的方式進(jìn)行多張表的關(guān)聯(lián)匹配，這種顯然效率就極低，本來(lái)磁盤(pán)的 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è)跟磁盤(pán)數(shù)據(jù)頁(yè)內(nèi)容不一致的時(shí)候，我們稱(chēng)這個(gè)內(nèi)存頁(yè)為 “臟頁(yè)”。內(nèi)存數(shù)據(jù)寫(xiě)入到磁盤(pán)后，內(nèi)存和磁盤(pán)上的數(shù)據(jù)頁(yè)的內(nèi)容就一致了，稱(chēng)為 “干凈頁(yè)”。一般有更新 SQL 才可能會(huì)導(dǎo)致臟頁(yè)，我們回憶一下：一條更新語(yǔ)句是如何執(zhí)行的

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

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

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

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

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

有些小伙伴可能有疑惑，redo log 日志不是在磁盤(pán)嘛？那為什么不慢？其實(shí)是因?yàn)閷?xiě) redo log 的過(guò)程是順序?qū)懘疟P(pán)的。磁盤(pán)順序?qū)憰?huì)減少尋道等待時(shí)間，速度比隨機(jī)寫(xiě)要快很多的。

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

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

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

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

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

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

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

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

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

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

8. order by 文件排序

order by 就一定會(huì)導(dǎo)致慢查詢嗎？不是這樣的哈，因?yàn)?order by 平時(shí)用得多，并且數(shù)據(jù)量一上來(lái)，還是走文件排序的話，很容易有慢 SQL 的。聽(tīng)我娓娓道來(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;
• MySQL 為對(duì)應(yīng)的線程初始化 sort_buffer，放入需要排序的 age 字段，以及主鍵id；
• 從索引樹(shù) idx_city， 找到第一個(gè)滿足 city='深圳’條件的主鍵id，也就是圖中的 id=9；
• 到主鍵id索引樹(shù)拿到 id=9 的這一行數(shù)據(jù)， 取 age和主鍵id 的值，存到 sort_buffer；
• 從索引樹(shù) idx_city 拿到下一個(gè)記錄的主鍵id，即圖中的 id=13；
• 重復(fù)步驟 3、4 直到 city 的值不等于深圳為止；
• 前面 5 步已經(jīng)查找到了所有 city 為深圳的數(shù)據(jù)，在 sort_buffer 中，將所有數(shù)據(jù)根據(jù) age 進(jìn)行排序；
• 遍歷排序結(jié)果，取前 10 行，并按照 id 的值回到原表中，取出 city、name 和 age 三個(gè)字段返回給客戶端。

全字段排序

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

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

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

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

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

借助磁盤(pán)文件排序的話，效率就更慢一點(diǎn)。因?yàn)橄劝褦?shù)據(jù)放入 sort_buffer，當(dāng)快要滿時(shí)。會(huì)排一下序，然后把 sort_buffer 中的數(shù)據(jù)，放到臨時(shí)磁盤(pán)文件，等到所有滿足條件數(shù)據(jù)都查完排完，再用歸并算法把磁盤(pán)的臨時(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)化；

9. 拿不到鎖

有時(shí)候，我們查詢一條很簡(jiǎn)單的 SQL，但是卻等待很長(zhǎng)的時(shí)間，不見(jiàn)結(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 子查詢不走索引！

之前見(jiàn)到過(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 TABLEold_account(idint(11) NOT NULL AUTO_INCREMENT COMMENT ‘主鍵Id',namevarchar(255) DEFAULT NULL COMMENT ‘賬戶名',balanceint(11) DEFAULT NULL COMMENT ‘余額',create_timedatetime NOT NULL COMMENT ‘創(chuàng)建時(shí)間',update_timedatetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新時(shí)間', PRIMARY KEY (id), KEYidx_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é)果如下：

selecttest2.account.idASid,test2.account.nameASname,test2.account.balanceASbalance,test2.account.create_timeAScreate_time,test2.account.update_timeASupdate_timefromtest2.accountsemi 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 卻沒(méi)有對(duì)它做這個(gè)優(yōu)化。

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

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

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

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

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

CREATE TABLEstaff(idbigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘主鍵id',id_cardvarchar(20) NOT NULL COMMENT ‘身份證號(hào)碼',namevarchar(64) NOT NULL COMMENT ‘姓名',ageint(4) NOT NULL COMMENT ‘年齡',cityvarchar(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;

• 創(chuàng)建內(nèi)存臨時(shí)表，表里有兩個(gè)字段 city和num；
• 全表掃描 staff 的記錄，依次取出 city = ‘X’ 的記錄。
  • 判斷臨時(shí)表中是否有為 city=‘X’ 的行，沒(méi)有就插入一個(gè)記錄 (X,1);
  • 如果臨時(shí)表中有 city=‘X’ 的行，就將 X 這一行的 num 值加 1；

遍歷完成后，再根據(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í)候還可能用到磁盤(pán)臨時(shí)表。

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

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è)這些值一開(kāi)始就是有序的，我們是不是直接往下掃描統(tǒng)計(jì)就好了，就不用臨時(shí)表來(lái)記錄并統(tǒng)計(jì)結(jié)果啦？

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

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

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 哈。之前見(jiàn)過(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)閉了。

到此這篇關(guān)于總結(jié)12個(gè)MySQL慢查詢的原因分析的文章就介紹到這了,更多相關(guān) MySQL慢查詢 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論