盤點 MySQL 慢查詢的 12 個原因

前言

大家好，我是撿田螺的小男孩。

日常開發中，我們經常會遇到數據庫慢查詢。那麼導致數據慢查詢都有哪些常見的原因呢？今天田螺哥就跟大家聊聊導致 MySQL 慢查詢的 12 個常見原因，以及對應的解決方法。

SQL 沒加索引

很多時候，我們的慢查詢，都是因爲沒有加索引。如果沒有加索引的話，會導致全表掃描的。因此，應考慮在where的條件列，建立索引，儘量避免全表掃描。

反例：

select * from user_info where name ='撿田螺的小男孩公衆號' ;

正例：

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

SQL 索引不生效

有時候我們明明加了索引了，但是索引卻不生效。在哪些場景，索引會不生效呢？主要有以下十大經典場景：

2.1 隱式的類型轉換，索引失效

我們創建一個用戶 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 + 樹的普通索引，如果查詢條件傳了一個數字過去，會導致索引失效。如下：

如果給數字加上'', 也就是說，傳的是一個字符串呢，當然是走索引，如下圖：

爲什麼第一條語句未加單引號就不走索引了呢？這是因爲不加單引號時，是字符串跟數字的比較，它們類型不匹配，MySQL 會做隱式的類型轉換，把它們轉換爲浮點數再做比較。隱式的類型轉換，索引會失效。

2.2 查詢條件包含 or，可能導致索引失效

我們還是用這個表結構：

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 是不走索引的，如下：

對於or+ 沒有索引的age這種情況，假設它走了userId的索引，但是走到age查詢條件時，它還得全表掃描，也就是需要三步過程：全表掃描 + 索引掃描 + 合併。如果它一開始就走全表掃描，直接一遍掃描就完事。Mysql 優化器出於效率與成本考慮，遇到or條件，讓索引失效，看起來也合情合理嘛。

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

2.3. like 通配符可能導致索引失效。

並不是用了like通配符，索引一定會失效，而是 like 查詢是以%開頭，纔會導致索引失效。

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

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

把%放後面，發現索引還是正常走的，如下：

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

既然like查詢以%開頭，會導致索引失效。我們如何優化呢？

使用覆蓋索引
把%放後面

2.4 查詢條件不滿足聯合索引的最左匹配原則

MySQl 建立聯合索引時，會遵循最左前綴匹配的原則，即最左優先。如果你建立一個（a,b,c）的聯合索引，相當於建立了(a)、(a,b)、(a,b,c)三個索引。

假設有以下表結構：

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;

有一個聯合索引idx_userid_name，我們執行這個 SQL，查詢條件是name，索引是無效：

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

因爲查詢條件列name不是聯合索引idx_userid_name中的第一個列，索引不生效

在聯合索引中，查詢條件滿足最左匹配原則時，索引才正常生效。

2.5 在索引列上使用 mysql 的內置函數

表結構：

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加了索引，但是因爲使用了mysql的內置函數Date_ADD()，索引直接 GG，如圖：

一般這種情況怎麼優化呢？可以把內置函數的邏輯轉移到右邊，如下：

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

2.6 對索引進行列運算（如，+、-、*、/）, 索引不生效

表結構：

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加了索引，但是因爲它進行運算，索引直接迷路了。。。如圖：

所以不可以對索引列進行運算，可以在代碼處理好，再傳參進去。

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

表結構：

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這些時，索引如同虛設。如下：

其實這個也是跟mySQL優化器有關，如果優化器覺得即使走了索引，還是需要掃描很多很多行的哈，它覺得不划算，不如直接不走索引。平時我們用！= 或者< >，not in的時候，留點心眼哈。

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

表結構:

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;

單個name字段加上索引，並查詢name爲非空的語句，其實會走索引的，如下:

單個card字段加上索引，並查詢 name 爲非空的語句，其實會走索引的，如下:

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

很多時候，也是因爲數據量問題，導致了 MySQL 優化器放棄走索引。同時，平時我們用 explain 分析 SQL 的時候，如果type=range, 要注意一下哈，因爲這個可能因爲數據量問題，導致索引無效。

2.9 左右連接，關聯的字段編碼格式不一樣

新建兩個表，一個user，一個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。

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

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

所以大家在做表關聯時，注意一下關聯字段的編碼問題哈。

2.10 優化器選錯了索引

MySQL 中一張表是可以支持多個索引的。你寫SQL語句的時候，沒有主動指定使用哪個索引的話，用哪個索引是由MySQL來確定的。

我們日常開發中，不斷地刪除歷史數據和新增數據的場景，有可能會導致 MySQL 選錯索引。那麼有哪些解決方案呢？

使用force index 強行選擇某個索引
修改你的 SQl，引導它使用我們期望的索引
優化你的業務邏輯
優化你的索引，新建一個更合適的索引，或者刪除誤用的索引。

limit 深分頁問題

limit 深分頁問題，會導致慢查詢，應該大家都司空見慣了吧。

3.1 limit 深分頁爲什麼會變慢

limit 深分頁爲什麼會導致 SQL 變慢呢？假設我們有表結構如下：

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 '創建時間',
  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  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，執行過程是怎樣的嘛？

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

這個 SQL 的執行流程：

通過普通二級索引樹idx_create_time，過濾create_time條件，找到滿足條件的主鍵id。
通過主鍵id，回到id主鍵索引樹，找到滿足記錄的行，然後取出需要展示的列（回表過程）
掃描滿足條件的100010行，然後扔掉前100000行，返回。

limit深分頁，導致SQL變慢原因有兩個：

limit語句會先掃描offset+n行，然後再丟棄掉前offset行，返回後n行數據。也就是說limit 100000,10，就會掃描100010行，而 limit 0,10，只掃描10行。
limit 100000,10 掃描更多的行數，也意味着回表更多的次數。

3.2 如何優化深分頁問題

我們可以通過減少回表次數來優化。一般有標籤記錄法和延遲關聯法。

標籤記錄法

就是標記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。就好像看書一樣，上次看到哪裏了，你就摺疊一下或者夾個書籤，下次來看的時候，直接就翻到啦。

假設上一次記錄到100000，則 SQL 可以修改爲：

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

這樣的話，後面無論翻多少頁，性能都會不錯的，因爲命中了id索引。但是這種方式有侷限性：需要一種類似連續自增的字段。

延遲關聯法

延遲關聯法，就是把條件轉移到主鍵索引樹，然後減少回表。如下：

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;

優化思路就是，先通過idx_create_time二級索引樹查詢到滿足條件的主鍵ID，再與原表通過主鍵ID內連接，這樣後面直接走了主鍵索引了，同時也減少了回表。

單表數據量太大

4.1 單表數據量太大爲什麼會變慢？

一個表的數據量達到好幾千萬或者上億時，加索引的效果沒那麼明顯啦。性能之所以會變差，是因爲維護索引的B+樹結構層級變得更高了，查詢一條數據時，需要經歷的磁盤 IO 變多，因此查詢性能變慢。

4.2 一棵 B + 樹可以存多少數據量

大家是否還記得，一個 B + 樹大概可以存放多少數據量呢？

InnoDB 存儲引擎最小儲存單元是頁，一頁大小就是16k。

B + 樹葉子存的是數據，內部節點存的是鍵值 + 指針。索引組織表通過非葉子節點的二分查找法以及指針確定數據在哪個頁中，進而再去數據頁中找到需要的數據；

假設 B + 樹的高度爲2的話，即有一個根結點和若干個葉子結點。這棵 B + 樹的存放總記錄數爲 = 根結點指針數 * 單個葉子節點記錄行數。

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

因此，一棵高度爲 2 的 B + 樹，能存放1170 * 16=18720條這樣的數據記錄。同理一棵高度爲 3 的 B + 樹，能存放1170 *1170 *16 =21902400，也就是說，可以存放兩千萬左右的記錄。B + 樹高度一般爲 1-3 層，已經滿足千萬級別的數據存儲。

如果 B + 樹想存儲更多的數據，那樹結構層級就會更高，查詢一條數據時，需要經歷的磁盤 IO 變多，因此查詢性能變慢。

4.3 如何解決單表數據量太大，查詢變慢的問題

一般超過千萬級別，我們可以考慮分庫分表了。

分庫分表可能導致的問題：

事務問題
跨庫問題
排序問題
分頁問題
分佈式 ID

因此，大家在評估是否分庫分表前，先考慮下，是否可以把部分歷史數據歸檔先，如果可以的話，先不要急着分庫分表。如果真的要分庫分表，綜合考慮和評估方案。比如可以考慮垂直、水平分庫分表。水平分庫分表策略的話，range 範圍、hash 取模、range+hash 取模混合等等。

join 或者子查詢過多

一般來說，不建議使用子查詢，可以把子查詢改成join來優化。而數據庫有個規範約定就是：儘量不要有超過 3 個以上的表連接。爲什麼要這麼建議呢? 我們來聊聊，join哪些方面可能導致慢查詢吧。

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

Index Nested-Loop Join：這個 join 算法，跟我們寫程序時的嵌套查詢類似，並且可以用上被驅動表的索引。
Block Nested-Loop Join：這種 join 算法，被驅動表上沒有可用的索引, 它會先把驅動表的數據讀入線程內存join_buffer中，再掃描被驅動表，把被驅動表的每一行取出來，跟join_buffer中的數據做對比，滿足 join 條件的，作爲結果集的一部分返回。

join過多的問題：

一方面，過多的表連接，會大大增加 SQL 複雜度。另外一方面，如果可以使用被驅動表的索引那還好，並且使用小表來做驅動表，查詢效率更佳。如果被驅動表沒有可用的索引，join 是在join_buffer內存做的，如果匹配的數據量比較小或者join_buffer設置的比較大，速度也不會太慢。但是，如果join的數據量比較大時，mysql 會採用在硬盤上創建臨時表的方式進行多張表的關聯匹配，這種顯然效率就極低，本來磁盤的 IO 就不快，還要關聯。

一般情況下，如果業務需要的話，關聯2~3個表是可以接受的，但是關聯的字段需要加索引哈。如果需要關聯更多的表，建議從代碼層面進行拆分，在業務層先查詢一張表的數據，然後以關聯字段作爲條件查詢關聯表形成map，然後在業務層進行數據的拼裝。

in 元素過多

如果使用了in，即使後面的條件加了索引，還是要注意in後面的元素不要過多哈。in元素一般建議不要超過500個，如果超過了，建議分組，每次500一組進行哈。

反例：

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

如果我們對in的條件不做任何限制的話，該查詢語句一次性可能會查詢出非常多的數據，很容易導致接口超時。尤其有時候，我們是用的子查詢，in 後面的子查詢，你都不知道數量有多少那種，更容易採坑（所以我把 in 元素過多抽出來作爲一個小節）。如下這種子查詢：

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

正例是，分批進行，每批 500 個：

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

如果傳參的 ids 太多，還可以做個參數校驗什麼的

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

數據庫在刷髒頁

7.1 什麼是髒頁

當內存數據頁跟磁盤數據頁內容不一致的時候，我們稱這個內存頁爲 “髒頁”。內存數據寫入到磁盤後，內存和磁盤上的數據頁的內容就一致了，稱爲 “乾淨頁”。一般有更新 SQL 纔可能會導致髒頁，我們回憶一下：一條更新語句是如何執行的

7.2 一條更新語句是如何執行的？

以下的這個更新 SQL，如何執行的呢？

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

對於這條更新 SQL，執行器會先找引擎取id=666這一行。如果這行所在的數據頁本來就在內存中的話，就直接返回給執行器。如果不在內存，就去磁盤讀入內存，再返回。
執行器拿到引擎給的行數據後，給這一行C的值加一，得到新的一行數據，再調用引擎接口寫入這行新數據。
引擎將這行新數據更新到內存中，同時將這個更新操作記錄到redo log裏面，但是此時redo log 是處於prepare狀態的哈。
執行器生成這個操作的binlog，並把binlog寫入磁盤。
執行器調用引擎的提交事務接口，引擎把剛剛寫入的redo log改成提交（commit）狀態，更新完成。

InnoDB 在處理更新語句的時候，只做了寫日誌這一個磁盤操作。這個日誌叫作redo log（重做日誌）。平時更新 SQL 執行得很快，其實是因爲它只是在寫內存和redo log日誌，等到空閒的時候，才把redo log日誌裏的數據同步到磁盤中。

有些小夥伴可能有疑惑，redo log日誌不是在磁盤嘛？那爲什麼不慢？其實是因爲寫redo log的過程是順序寫磁盤的。磁盤順序寫會減少尋道等待時間，速度比隨機寫要快很多的。

7.3 爲什麼會出現髒頁呢？

更新 SQL 只是在寫內存和redo log日誌，等到空閒的時候，才把redo log日誌裏的數據同步到磁盤中。這時內存數據頁跟磁盤數據頁內容不一致, 就出現髒頁。

7.4 什麼時候會刷髒頁（flush）？

InnoDB 存儲引擎的redo log大小是固定，且是環型寫入的，如下圖（圖片來源於 MySQL 實戰 45 講）：

那什麼時候會刷髒頁？有幾種場景：

redo log寫滿了，要刷髒頁。這種情況要儘量避免的。因爲出現這種情況時，整個系統就不能再接受更新啦，即所有的更新都必須堵住。
內存不夠了，需要新的內存頁，就要淘汰一些數據頁，這時候會刷髒頁

InnoDB 用緩衝池（buffer pool）管理內存, 而當要讀入的數據頁沒有在內存的時候，就必須到緩衝池中申請一個數據頁。這時候只能把最久不使用的數據頁從內存中淘汰掉：如果要淘汰的是一個乾淨頁，就直接釋放出來複用；但如果是髒頁呢，就必須將髒頁先刷到磁盤，變成乾淨頁後才能複用。

MySQL 認爲系統空閒的時候，也會刷一些髒頁
MySQL 正常關閉時，會把內存的髒頁都 flush 到磁盤上

7.5 爲什麼刷髒頁會導致 SQL 變慢呢？

redo log寫滿了，要刷髒頁，這時候會導致系統所有的更新堵住，寫性能都跌爲 0 了，肯定慢呀。一般要杜絕出現這個情況。
一個查詢要淘汰的髒頁個數太多，一樣會導致查詢的響應時間明顯變長。
order by 文件排序

order by就一定會導致慢查詢嗎？不是這樣的哈，因爲order by平時用得多，並且數據量一上來，還是走文件排序的話，很容易有慢 SQL 的。聽我娓娓道來，order by哪些時候可能會導致慢 SQL 哈。

8.1 order by 的 Using filesort 文件排序

我們平時經常需要用到order by ，主要就是用來給某些字段排序的。比如以下 SQL:

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

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

查看explain執行計劃的時候，可以看到Extra這一列，有一個Using filesort，它表示用到文件排序。

8.2 order by 文件排序效率爲什麼較低

order by用到文件排序時，爲什麼查詢效率會相對低呢？

order by排序，分爲全字段排序和 rowid 排序。它是拿max_length_for_sort_data和結果行數據長度對比，如果結果行數據長度超過max_length_for_sort_data這個值，就會走 rowid 排序，相反，則走全字段排序。

rowid 排序

rowid 排序，一般需要回表去找滿足條件的數據，所以效率會慢一點。以下這個 SQL，使用rowid排序，執行過程是這樣：

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

MySQL 爲對應的線程初始化sort_buffer，放入需要排序的age字段，以及主鍵id；
從索引樹idx_city，找到第一個滿足 city='深圳’條件的主鍵id，也就是圖中的id=9；
到主鍵id索引樹拿到id=9的這一行數據，取age和主鍵id的值，存到sort_buffer；
從索引樹idx_city拿到下一個記錄的主鍵id，即圖中的id=13；
重複步驟 3、4 直到city的值不等於深圳爲止；
前面 5 步已經查找到了所有city爲深圳的數據，在sort_buffer中，將所有數據根據 age 進行排序；
遍歷排序結果，取前 10 行，並按照id的值回到原表中，取出city、name 和 age三個字段返回給客戶端。

全字段排序

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

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

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

sort_buffer的大小是由一個參數控制的：sort_buffer_size。

如果要排序的數據小於sort_buffer_size，排序在sort_buffer內存中完成
如果要排序的數據大於sort_buffer_size，則藉助磁盤文件來進行排序。

藉助磁盤文件排序的話，效率就更慢一點。因爲先把數據放入sort_buffer，當快要滿時。會排一下序，然後把sort_buffer中的數據，放到臨時磁盤文件，等到所有滿足條件數據都查完排完，再用歸併算法把磁盤的臨時排好序的小文件，合併成一個有序的大文件。

8.3 如何優化 order by 的文件排序

order by使用文件排序，效率會低一點。我們怎麼優化呢？

因爲數據是無序的，所以就需要排序。如果數據本身是有序的，那就不會再用到文件排序啦。而索引數據本身是有序的，我們通過建立索引來優化order by語句。
我們還可以通過調整max_length_for_sort_data、sort_buffer_size等參數優化；

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

拿不到鎖

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

舉一個生活的例子哈，你和別人合租了一間房子，這個房子只有一個衛生間的話。假設某一時刻，你們都想去衛生間，但是對方比你早了一點點。那麼此時你只能等對方出來後才能進去。

這時候，我們可以用show processlist命令，看看當前語句處於什麼狀態哈。

delete + in 子查詢不走索引！

之前見到過一個生產慢 SQL 問題，當delete遇到in子查詢時，即使有索引，也是不走索引的。而對應的select + in子查詢，卻可以走索引。

MySQL 版本是 5.7，假設當前有兩張表 account 和 old_account, 表結構如下：

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 '創建時間',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  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 '創建時間',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='賬戶表';

執行的 SQL 如下：

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

查看執行計劃，發現不走索引：

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

爲什麼select + in子查詢會走索引，delete + in子查詢卻不會走索引呢？

我們執行以下 SQL 看看：

explain select * from account where name in (select name from old_account);
show WARNINGS; //可以查看優化後,最終執行的sql

結果如下：

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

可以發現，實際執行的時候，MySQL 對select in子查詢做了優化，把子查詢改成join的方式，所以可以走索引。但是很遺憾，對於delete in子查詢，MySQL 卻沒有對它做這個優化。

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

11、group by 使用臨時表

group by一般用於分組統計，它表達的邏輯就是根據一定的規則，進行分組。日常開發中，我們使用得比較頻繁。如果不注意，很容易產生慢 SQL。

11.1 group by 的執行流程

假設有表結構：

CREATE TABLE `staff` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
  `id_card` varchar(20) NOT NULL COMMENT '身份證號碼',
  `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='員工表';

我們查看一下這個 SQL 的執行計劃：

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

Extra 這個字段的Using temporary表示在執行分組的時候使用了臨時表
Extra 這個字段的Using filesort表示使用了文件排序

group by是怎麼使用到臨時表和排序了呢？我們來看下這個 SQL 的執行流程

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

創建內存臨時表，表裏有兩個字段city和num；
全表掃描staff的記錄，依次取出city = 'X'的記錄。

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

遍歷完成後，再根據字段city做排序，得到結果集返回給客戶端。這個流程的執行圖如下：

臨時表的排序是怎樣的呢？

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

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

11.2 group by 可能會慢在哪裏？

group by使用不當，很容易就會產生慢 SQL 問題。因爲它既用到臨時表，又默認用到排序。有時候還可能用到磁盤臨時表。

如果執行過程中，會發現內存臨時表大小到達了上限（控制這個上限的參數就是 tmp_table_size），會把內存臨時錶轉成磁盤臨時表。
如果數據量很大，很可能這個查詢需要的磁盤臨時表，就會佔用大量的磁盤空間。

11.3 如何優化 group by 呢？

從哪些方向去優化呢？

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

我們一起來想下，執行group by語句爲什麼需要臨時表呢？group by的語義邏輯，就是統計不同的值出現的個數。如果這個這些值一開始就是有序的，我們是不是直接往下掃描統計就好了，就不用臨時表來記錄並統計結果啦?

可以有這些優化方案：

group by 後面的字段加索引
order by null 不用排序
儘量只使用內存臨時表
使用 SQL_BIG_RESULT

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

系統硬件或網絡資源

如果數據庫服務器內存、硬件資源，或者網絡資源配置不是很好，就會慢一些哈。這時候可以升級配置。這就好比你的計算機有時候很卡，你可以加個內存條什麼的一個道理。
如果數據庫壓力本身很大，比如高併發場景下，大量請求到數據庫來，數據庫服務器CPU佔用很高或者IO利用率很高，這種情況下所有語句的執行都有可能變慢的哈。

最後

如果測試環境數據庫的一些參數配置，和生產環境參數配置不一致的話，也容易產生慢 SQL 哈。之前見過一個慢 SQL 的生產案例，就是測試環境用了index merge，所以查看 explain 執行計劃時，是可以走索引的，但是到了生產，卻全表掃描，最後排查發現是生產環境配置把index merge關閉了。

參考與感謝

MySQL 實戰 45 講（https://time.geekbang.org/column/article/72775?cid=100020801）

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/JWR5xG2LzNNouF9Tl9JS0A

前言

2.1 隱式的類型轉換，索引失效

2.2 查詢條件包含 or，可能導致索引失效

2.3. like 通配符可能導致索引失效。

2.4 查詢條件不滿足聯合索引的最左匹配原則

2.5 在索引列上使用 mysql 的內置函數

2.6 對索引進行列運算（如，+、-、*、/）, 索引不生效

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

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

2.9 左右連接，關聯的字段編碼格式不一樣

2.10 優化器選錯了索引

3.1 limit 深分頁爲什麼會變慢

3.2 如何優化深分頁問題

4.1 單表數據量太大爲什麼會變慢？

4.2 一棵 B + 樹可以存多少數據量

4.3 如何解決單表數據量太大，查詢變慢的問題

7.1 什麼是髒頁

7.2 一條更新語句是如何執行的？

7.3 爲什麼會出現髒頁呢？

7.4 什麼時候會刷髒頁（flush）？

7.5 爲什麼刷髒頁會導致 SQL 變慢呢？

8.1 order by 的 Using filesort 文件排序

8.2 order by 文件排序效率爲什麼較低

rowid 排序

全字段排序

8.3 如何優化 order by 的文件排序

11、group by 使用臨時表

11.1 group by 的執行流程

11.2 group by 可能會慢在哪裏？

11.3 如何優化 group by 呢？

最後

參考與感謝

猜你喜歡