SQL優化思路+經典案例分析

目錄

1.慢SQL優化思路。

1.1 慢查詢日志記錄慢SQL

1.2 explain檢視分析SQL的執行計劃

1.3 profile 分析執行耗時

1.4 Optimizer Trace分析詳情

1.5 确定問題并采用相應的措施

2. 慢查詢經典案例分析

2.1 案例1：隐式轉換

2.2 案例2：最左比對

2.3 案例3：深分頁問題

2.4  案例4：in元素過多

2.5 order by 走檔案排序導緻的慢查詢

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

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

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

2.9 group by使用臨時表

2.10  delete + in子查詢不走索引！

1.慢SQL優化思路。

1. 慢查詢日志記錄慢SQL
2. explain分析SQL的執行計劃
3. profile 分析執行耗時
4. Optimizer Trace分析詳情
5. 确定問題并采用相應的措施

1.1 慢查詢日志記錄慢SQL

如何定位慢SQL呢、我們可以通過慢查詢日志來檢視慢SQL。預設的情況下呢，MySQL資料庫是不開啟慢查詢日志（

slow query log

）呢。是以我們需要手動把它打開。

檢視下慢查詢日志配置，我們可以使用

show variables like 'slow_query_log%'

指令，如下：

• slow query log

  表示慢查詢開啟的狀态

• slow_query_log_file

  表示慢查詢日志存放的位置

我們還可以使用

show variables like 'long_query_time'

指令，檢視超過多少時間，才記錄到慢查詢日志，如下：

• long_query_time

  表示查詢超過多少秒才記錄到慢查詢日志。

我們可以通過慢查日志，定位那些執行效率較低的SQL語句，重點關注分析。

1.2 explain檢視分析SQL的執行計劃

當定位出查詢效率低的SQL後，可以使用

explain

檢視

SQL

的執行計劃。

當

explain

與

SQL

一起使用時，MySQL将顯示來自優化器的有關語句執行計劃的資訊。即

MySQL

解釋了它将如何處理該語句，包括有關如何連接配接表以及以何種順序連接配接表等資訊。

一條簡單SQL，使用了

explain

的效果如下：

一般來說，我們需要重點關注

type、rows、filtered、extra、key

。

1.2.1 type

type表示連接配接類型，檢視索引執行情況的一個重要名額。以下性能從好到壞依次：

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system：這種類型要求資料庫表中隻有一條資料，是

  const

  類型的一個特例，一般情況下是不會出現的。
• const：通過一次索引就能找到資料，一般用于主鍵或唯一索引作為條件，這類掃描效率極高，，速度非常快。
• eq_ref：常用于主鍵或唯一索引掃描，一般指使用主鍵的關聯查詢
• ref : 常用于非主鍵和唯一索引掃描。
• ref_or_null：這種連接配接類型類似于

  ref

  ，差別在于

  MySQL

  會額外搜尋包含

  NULL

  值的行
• index_merge：使用了索引合并優化方法，查詢使用了兩個以上的索引。
• unique_subquery：類似于

  eq_ref

  ，條件用了

  in

  子查詢
• index_subquery：差別于

  unique_subquery

  ，用于非唯一索引，可以傳回重複值。
• range：常用于範圍查詢，比如：between ... and 或 In 等操作
• index：全索引掃描
• ALL：全表掃描

1.2.2 rows

該清單示MySQL估算要找到我們所需的記錄，需要讀取的行數。對于InnoDB表，此數字是估計值，并非一定是個準确值。

1.2.3 filtered

該列是一個百分比的值，表裡符合條件的記錄數的百分比。簡單點說，這個字段表示存儲引擎傳回的資料在經過過濾後，剩下滿足條件的記錄數量的比例。

1.2.4 extra

該字段包含有關MySQL如何解析查詢的其他資訊，它一般會出現這幾個值：

• Using filesort：表示按檔案排序，一般是在指定的排序和索引排序不一緻的情況才會出現。一般見于order by語句
• Using index ：表示是否用了覆寫索引。
• Using temporary: 表示是否使用了臨時表,性能特别差，需要重點優化。一般多見于group by語句，或者union語句。
• Using where : 表示使用了where條件過濾.
• Using index condition：MySQL5.6之後新增的索引下推。在存儲引擎層進行資料過濾，而不是在服務層過濾，利用索引現有的資料減少回表的資料。

1.2.5 key

該清單示實際用到的索引。一般配合

possible_keys

列一起看。

1.3 profile 分析執行耗時

explain

隻是看到

SQL

的預估執行計劃，如果要了解

SQL

真正的執行線程狀态及消耗的時間，需要使用

profiling

。開啟

profiling

參數後，後續執行的

SQL

語句都會記錄其資源開銷，包括

IO，上下文切換，CPU，記憶體

等等，我們可以根據這些開銷進一步分析目前慢SQL的瓶頸再進一步進行優化。

profiling

預設是關閉，我們可以使用

show variables like '%profil%'

檢視是否開啟，如下：

可以使用

set profiling=ON

開啟。開啟後，可以運作幾條SQL，然後使用

show profiles

檢視一下。

show profiles

會顯示最近發給伺服器的多條語句，條數由變量

profiling_history_size

定義，預設是15。如果我們需要看單獨某條SQL的分析，可以

show profile

檢視最近一條SQL的分析，也可以使用

show profile for query id

（其中id就是show profiles中的QUERY_ID）檢視具體一條的SQL語句分析。

除了檢視profile ，還可以檢視cpu和io，如上圖。

1.4 Optimizer Trace分析詳情

profile隻能檢視到SQL的執行耗時，但是無法看到SQL真正執行的過程資訊，即不知道MySQL優化器是如何選擇執行計劃。這時候，我們可以使用

Optimizer Trace

，它可以跟蹤執行語句的解析優化執行的全過程。

我們可以使用

set optimizer_trace="enabled=on"

打開開關，接着執行要跟蹤的SQL，最後執行

select * from information_schema.optimizer_trace

跟蹤，如下：

大家可以檢視分析其執行樹，會包括三個階段：

• join_preparation：準備階段
• join_optimization：分析階段
• join_execution：執行階段

1.5 确定問題并采用相應的措施

最後确認問題，就采取對應的措施。

• 多數慢SQL都跟索引有關，比如不加索引，索引不生效、不合理等，這時候，我們可以優化索引。
• 我們還可以優化SQL語句，比如一些in元素過多問題（分批），深分頁問題（基于上一次資料過濾等），進行時間分段查詢
• SQl沒辦法很好優化，可以改用ES的方式，或者數倉。
• 如果單表資料量過大導緻慢查詢，則可以考慮分庫分表
• 如果資料庫在刷髒頁導緻慢查詢，考慮是否可以優化一些參數，跟DBA讨論優化方案
• 如果存量資料量太大，考慮是否可以讓部分資料歸檔

我之前寫了一篇文章，有關于導緻慢查詢的12個原因，大家看一看一下哈:盤點MySQL慢查詢的12個原因

2. 慢查詢經典案例分析

2.1 案例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 案例2：最左比對

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

中的第一個列，不滿足最左比對原則，是以索引不生效。在聯合索引中，隻有查詢條件滿足最左比對原則時，索引才正常生效。如下，查詢條件列是

user_id

2.3 案例3：深分頁問題

limit

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

limit深分頁為什麼會變慢呢？ 假設有表結構如下：

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

   掃描更多的行數，也意味着回表更多的次數。

如何優化深分頁問題?

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

标簽記錄法

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

假設上一次記錄到

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

内連接配接，這樣後面直接走了主鍵索引了，同時也減少了回表。

2.4  案例4：in元素過多

如果使用了

in

，即使後面的條件加了索引，還是要注意

in

後面的元素不要過多哈。

in

元素一般建議不要超過

200

個，如果超過了，建議分組，每次200一組進行哈。

反例:

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

如果我們對

in

的條件不做任何限制的話，該查詢語句一次性可能會查詢出非常多的資料，很容易導緻接口逾時。尤其有時候，我們是用的子查詢，in後面的子查詢，你都不知道數量有多少那種，更容易采坑.如下這種子查詢：

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

如果

type = 1

有1一千，甚至上萬個呢？肯定是慢SQL。索引一般建議分批進行，一次200個，比如：

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

in查詢為什麼慢呢？

這是因為

in

查詢在MySQL底層是通過

n*m

的方式去搜尋，類似

union

。

in查詢在進行cost代價計算時（代價 = 元組數 * IO平均值），是通過将in包含的數值，一條條去查詢擷取元組數的，是以這個計算過程會比較的慢，是以MySQL設定了個臨界值(eq_range_index_dive_limit)，5.6之後超過這個臨界值後該列的cost就不參與計算了。是以會導緻執行計劃選擇不準确。預設是200，即in條件超過了200個資料，會導緻in的代價計算存在問題，可能會導緻Mysql選擇的索引不準确。

2.5 order by 走檔案排序導緻的慢查詢

如果order by 使用到檔案排序，則會可能會産生慢查詢。我們來看下下面這個SQL：

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

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

檢視explain執行計劃的時候，可以看到Extra這一列，有一個

Using filesort

，它表示用到檔案排序。

order by檔案排序效率為什麼較低

大家可以看下這個下面這個圖:

order by

排序，分為

全字段排序

和

rowid排序

。它是拿

max_length_for_sort_data

和結果行資料長度對比，如果結果行資料長度超過

max_length_for_sort_data

這個值，就會走

rowid

排序，相反，則走全字段排序。

2.5.1 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

   為

   X

   ；
3. 到主鍵

   id索引樹

   拿到

   id=X

   的這一行資料， 取age和主鍵id的值，存到

   sort_buffer

   ；
4. 從索引樹

   idx_city

   拿到下一個記錄的

   主鍵id

   ，假設

   id=Y

   ；
5. 重複步驟 3、4 直到

   city

   的值不等于深圳為止；
6. 前面5步已經查找到了所有

   city

   為深圳的資料，在

   sort_buffer

   中，将所有資料根據

   age

   進行排序；周遊排序結果，取前10行，并按照id的值回到原表中，取出

   city、name 和 age

   三個字段傳回給用戶端。

2.5.2 全字段排序

同樣的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=X

   ；
3. 到主鍵id索引樹拿到

   id=X

   的這一行資料， 取

   name、age、city

   三個字段的值，存到

   sort_buffer

   ；
4. 從索引樹

   idx_city

    拿到下一個記錄的主鍵

   id

   ，假設

   id=Y

   ；
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

中的資料，放到臨時磁盤檔案，等到所有滿足條件資料都查完排完，再用歸并算法把磁盤的臨時排好序的小檔案，合并成一個有序的大檔案。

2.5.3  如何優化order by的檔案排序

order by

使用檔案排序，效率會低一點。我們怎麼優化呢？

• 因為資料是無序的，是以就需要排序。如果資料本身是有序的，那就不會再用到檔案排序啦。而索引資料本身是有序的，我們通過建立索引來優化

  order by

  語句。
• 我們還可以通過調整

  max_length_for_sort_data、sort_buffer_size

  等參數優化；

2.6 索引字段上使用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.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 左右連接配接，關聯的字段編碼格式不一樣

建立兩個表，一個

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.9 group by使用臨時表

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

2.9.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;
           

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，然後多一次回表操作，再傳回。

2.9.2 group by可能會慢在哪裡？

group by

使用不當，很容易就會産生慢

SQL

問題。因為它既用到臨時表，又預設用到排序。有時候還可能用到磁盤臨時表。

• 如果執行過程中，會發現記憶體臨時表大小到達了上限（控制這個上限的參數就是

  tmp_table_size

  ），會把記憶體臨時表轉成磁盤臨時表。
• 如果資料量很大，很可能這個查詢需要的磁盤臨時表，就會占用大量的磁盤空間。

2.9.3 如何優化group by呢

從哪些方向去優化呢？

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

我們一起來想下，執行

group by

語句為什麼需要臨時表呢？

group by

的語義邏輯，就是統計不同的值出現的個數。如果這個這些值一開始就是有序的，我們是不是直接往下掃描統計就好了，就不用臨時表來記錄并統計結果啦?

可以有這些優化方案：

• group by 後面的字段加索引
• order by null 不用排序
• 盡量隻使用記憶體臨時表
• 使用SQL_BIG_RESULT

2.10  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卻沒有對它做這個優化。

日常開發中，大家注意一下這個場景哈