總結12個MySQL慢查詢的原因分析

1. SQL 沒加索引

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

反例：

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

正例：

//新增索引 alter table user_info add index idx_name (name);

2. 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 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 加了索引，但是因為使用了 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 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 加了索引，但是因為它進行運算，索引直接迷路了

如圖：

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

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

表結構：

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

如下：

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

2.8 索引欄位上使用 is null， is not null，索引可能失效

表結構:

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;

單個 name 欄位加上索引，並查詢 name 為非空的語句，其實會走索引的，如下:

單個 card 欄位加上索引，並查詢 name 為非空的語句，其實會走索引的，如下:圖片

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

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

2.9 左右連線，關聯的欄位編碼格式不一樣

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

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

如果把它們的 name 欄位改為編碼一致，相同的 SQL，還是會走索引。

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

2.10 優化器選錯了索引

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

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

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

3. 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. 單表資料量太大

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 取模混合等等。

5. 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，然後在業務層進行資料的拼裝。

6. 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. 資料庫在刷髒頁

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 了，肯定慢呀。一般要杜絕出現這個情況。
• 一個查詢要淘汰的髒頁個數太多，一樣會導致查詢的響應時間明顯變長。

8. 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 等引數優化；

9. 拿不到鎖

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

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

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

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

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

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

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 ‘建立時間',update_timedatetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新時間', 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 ‘建立時間',
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

結果如下：

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)

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

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

11、group by 使用臨時表

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

11.1 group by 的執行流程

假設有表結構：

CREATE TABLEstaff(idbigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘主鍵id',id_cardvarchar(20) NOT NULL COMMENT ‘身份證號碼',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=‘員工表';

我們檢視一下這個 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

12. 系統硬體或網路資源

如果資料庫伺服器記憶體、硬體資源，或者網路資源設定不是很好，就會慢一些哈。這時候可以升級設定。這就好比你的計算機有時候很卡，你可以加個記憶體條什麼的一個道理。如果資料庫壓力本身很大，比如高並行場景下，大量請求到資料庫來，資料庫伺服器 CPU 佔用很高或者 IO利用率很高，這種情況下所有語句的執行都有可能變慢的哈。

最後

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

到此這篇關於總結12個MySQL慢查詢的原因分析的文章就介紹到這了,更多相關 MySQL慢查詢 內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！