盤點MySQL慢查詢的12個原因

PHPer技術棧發表於2022-05-25
MySql

1. SQL沒加索引

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

反例：

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

圖片

正例：

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

圖片

2. SQL 索引不生效

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

盤點MySQL慢查詢的12個原因

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的執行流程：

  1. 通過普通二級索引樹idx_create_time，過濾create_time條件，找到滿足條件的主鍵id。

  2. 通過主鍵id，回到id主鍵索引樹，找到滿足記錄的行，然後取出需要展示的列（回表過程）

  3. 掃描滿足條件的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;

  1. 對於這條更新SQL，執行器會先找引擎取id=666這一行。如果這行所在的資料頁本來就在記憶體中的話，就直接返回給執行器。如果不在記憶體，就去磁碟讀入記憶體，再返回。

  2. 執行器拿到引擎給的行資料後，給這一行C的值加一，得到新的一行資料，再呼叫引擎介面寫入這行新資料。

  3. 引擎將這行新資料更新到記憶體中，同時將這個更新操作記錄到redo log裡面，但是此時redo log是處於prepare狀態的哈。

  4. 執行器生成這個操作的binlog，並把binlog寫入磁碟。

  5. 執行器呼叫引擎的提交事務介面，引擎把剛剛寫入的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 講）：

圖片

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

  1. redo log寫滿了，要刷髒頁。這種情況要儘量避免的。因為出現這種情況時，整個系統就不能再接受更新啦，即所有的更新都必須堵住。

  2. 記憶體不夠了，需要新的記憶體頁，就要淘汰一些資料頁，這時候會刷髒頁

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

  1. MySQL 認為系統空閒的時候，也會刷一些髒頁

  2. MySQL 正常關閉時，會把記憶體的髒頁都 flush 到磁碟上

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

  1. redo log寫滿了，要刷髒頁，這時候會導致系統所有的更新堵住，寫效能都跌為0了，肯定慢呀。一般要杜絕出現這個情況。

  2. 一個查詢要淘汰的髒頁個數太多，一樣會導致查詢的響應時間明顯變長。

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;

  1. MySQL 為對應的執行緒初始化sort_buffer，放入需要排序的age欄位，以及主鍵id；

  2. 從索引樹idx_city， 找到第一個滿足 city='深圳’條件的主鍵id，也就是圖中的id=9；

  3. 到主鍵id索引樹拿到id=9的這一行資料， 取age和主鍵id的值，存到sort_buffer；

  4. 從索引樹idx_city拿到下一個記錄的主鍵id，即圖中的id=13；

  5. 重複步驟 3、4 直到city的值不等於深圳為止；

  6. 前面5步已經查詢到了所有city為深圳的資料，在sort_buffer中，將所有資料根據age進行排序；

  7. 遍歷排序結果，取前10行，並按照id的值回到原表中，取出city、name 和 age三個欄位返回給客戶端。

圖片

全欄位排序

同樣的SQL，如果是走全欄位排序是這樣的：

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

  1. MySQL 為對應的執行緒初始化sort_buffer，放入需要查詢的name、age、city欄位；

  2. 從索引樹idx_city， 找到第一個滿足 city='深圳’條件的主鍵 id，也就是圖中的id=9；

  3. 到主鍵id索引樹拿到id=9的這一行資料， 取name、age、city三個欄位的值，存到sort_buffer；

  4. 從索引樹idx_city拿到下一個記錄的主鍵id，即圖中的id=13；

  5. 重複步驟 3、4 直到city的值不等於深圳為止；

  6. 前面5步已經查詢到了所有city為深圳的資料，在sort_buffer中，將所有資料根據age進行排序；

  7. 按照排序結果取前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詳解

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;

  1. 建立記憶體臨時表，表裡有兩個欄位city和num；

  2. 全表掃描staff的記錄，依次取出city = 'X'的記錄。

  • 判斷臨時表中是否有為 city='X'的行，沒有就插入一個記錄(X,1);

  • 如果臨時表中有city='X'的行，就將X這一行的num值加 1；

  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詳解

12. 系統硬體或網路資源

  • 如果資料庫伺服器記憶體、硬體資源，或者網路資源配置不是很好，就會慢一些哈。這時候可以升級配置。這就好比你的計算機有時候很卡，你可以加個記憶體條什麼的一個道理。

  • 如果資料庫壓力本身很大，比如高併發場景下，大量請求到資料庫來，資料庫伺服器CPU佔用很高或者IO利用率很高，這種情況下所有語句的執行都有可能變慢的哈。

最後

如果測試環境資料庫的一些引數配置，和生產環境引數配置不一致的話，也容易產生慢SQL哈。之前見過一個慢SQL的生產案例，就是測試環境用了index merge，所以檢視explain執行計劃時，是可以走索引的，但是到了生產，卻全表掃描，最後排查發現是生產環境配置把index merge關閉了。大家是否還遇到其他場景的慢SQL呢？如果有的話，歡迎評論區留言交流哈

參考文件

time.geekbang.org/column/article/8...

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章