聊聊重現一條簡單SQL的優化過程

作者：JennyYu 2023-03-26 22:42:02

優化器選擇先對兩個小表c，b進行關聯，然后得到的結果集再與大表a進行關聯，因為語句中c，b兩個表沒有字段進行直接關聯，所以這兩個表連接后的結果集是一個笛卡爾積25 *100=2500，因為大表的關聯字段上沒有索引，所以需要對最內層的大表全表掃描2500次。

背景

接到客戶訴求說一條SQL長時間運行不出結果，讓給看看怎么回事，SQL不復雜，優化措施也不復雜，但是要想SQL達到最優狀態，也是需要經過一番考量并做出選擇的。下面借實驗還原一下此SQL優化過程。

實驗：

數據庫環境：MySQL5.7.39

測試表結構如下：

mysql> show create table t_1\G
*************************** 1. row ***************************
       Table: t_1
Create Table: CREATE TABLE `t_1` (
  `w_id` int(11) DEFAULT NULL,
  `w_name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)


mysql> show create table t_2\G
*************************** 1. row ***************************
       Table: t_2
Create Table: CREATE TABLE `t_2` (
  `i_id` int(11) NOT NULL,
  `i_name` varchar(24) DEFAULT NULL,
  `i_price` decimal(5,2) DEFAULT NULL,
  `i_data` varchar(50) DEFAULT NULL,
  `i_im_id` int(11) NOT NULL,
  PRIMARY KEY (`i_im_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> show create table t_3\G
*************************** 1. row ***************************
       Table: t_3
Create Table: CREATE TABLE `t_3` (
  `s_w_id` int(11) NOT NULL,
  `s_i_id` int(11) NOT NULL,
  `s_quantity` int(11) DEFAULT NULL,
  `s_ytd` int(11) DEFAULT NULL,
  `s_order_cnt` int(11) DEFAULT NULL,
  `s_remote_cnt` int(11) DEFAULT NULL,
  `s_data` varchar(50) DEFAULT NULL,
  `s_dist_01` char(24) DEFAULT NULL,
  `s_dist_02` char(24) DEFAULT NULL,
  `s_dist_03` char(24) DEFAULT NULL,
  `s_dist_04` char(24) DEFAULT NULL,
  `s_dist_05` char(24) DEFAULT NULL,
  `s_dist_06` char(24) DEFAULT NULL,
  `s_dist_07` char(24) DEFAULT NULL,
  `s_dist_08` char(24) DEFAULT NULL,
  `s_dist_09` char(24) DEFAULT NULL,
  `s_dist_10` char(24) DEFAULT NULL,
  `t_2_id` int(11) DEFAULT NULL,
  `t_1_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`s_w_id`,`s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

Create Table: CREATE TABLE `t_4` (
  `w_name` varchar(10) DEFAULT NULL,
  `s_i_id` int(11) NOT NULL,
  `s_quantity` int(11) DEFAULT NULL,
  `s_ytd` int(11) DEFAULT NULL,
  `s_order_cnt` int(11) DEFAULT NULL,
  `s_remote_cnt` int(11) DEFAULT NULL,
  `s_data` varchar(50) DEFAULT NULL,
  `t_2_id` int(11) DEFAULT NULL,
  `i_name` varchar(24) DEFAULT NULL,
  `i_price` decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

其中t_1表25條記錄，t_2表100條記錄，t_3表500萬條數據。我這里實驗數據量少些，客戶實際業務表數據量分別是（30，150，2700萬）。t_4表為一個歷史數據歸檔表，用于插入數據。

SQL文本展示如下：

insert into t_4
SELECT
  c.w_name,
  a.s_i_id,
  a.s_quantity,
  a.s_ytd,
  a.s_order_cnt,
  a.s_remote_cnt,
  a.s_data,
  a.t_2_id,
  b.i_name,
  b.i_price
FROM
 t_3 a,
 t_2 b,
 t_1 c
WHERE
 a.t_2_id = b.i_id
and a.t_1_id = c.w_id
and a.s_ytd = 0;

查看語句中select部分的執行計劃如下圖所示：

看到這個計劃，就想對數據庫說一句："您辛苦了!"。

這是不是一個大工程呢？數據庫任勞任怨，你讓它干，它就干，只要你等得起就可以。事實上我們是沒有耐心等的。我本來還想看看數據庫到底用多久才能給出結果，等了10分鐘，實在沒有耐心繼續等下去了。

這條SQL不復雜吧，就是三張表進行關聯，但是關聯字段上都沒有索引，都進行了全表掃描。那么解決措施就是加索引，但是索引怎么加就需要做出選擇了。

有同事就提出這個SQL在大表上全表掃描2500次，在大表的關聯字段上加上索引就可以了，看到這里，你有沒有認同這個見解呢？我想應該有很多小伙伴是認同的。

不錯，給大表加上索引就不用全表掃描了，首先大表加索引，會鎖表很長時間，這個索引在客戶的生產環境須等到變更窗口才能加，客戶等不及，其次你有考慮過這真的是最好的辦法嗎？

因為我這是實驗環境，可以隨時給大表加索引，那接下來我們就給大表加上索引試試效果。

mysql> alter table t_3 add key(t_1_id,t_2_id);
Query OK, 0 rows affected (28.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引加好之后，執行計劃如下：

可以看出優化器并沒有選擇走索引，依然是使用BNL優化策略，進行全表掃描，為什么不走索引呢？應該是優化器認為索引掃描的成本高于全表掃描的成本，因為這條語句最終結果要返回大表的90%以上的數據，走索引后回表代價是很高的。這一點我們是不認同優化器的，怎么著2500次全表掃描也比每次通過索引范圍掃描的代價要高呀，好吧，既然不認同，那么使用force index來干涉優化器決策，讓它使用索引。

執行計劃如下圖所示：

執行計劃中顯示索引用上了，那實際執行效果如何呢？

mysql> insert into t_4
    -> SELECT
    ->   c.w_name,
    ->   a.s_i_id,
    ->   a.s_quantity,
    ->   a.s_ytd,
    ->   a.s_order_cnt,
    ->   a.s_remote_cnt,
    ->   a.s_data,
    ->   a.t_2_id,
    ->   b.i_name,
    ->   b.i_price
    -> FROM
    ->  t_3 a force index(t_1_id),
    ->  t_2 b,
    ->  t_1 c
    -> WHERE
    ->  a.t_2_id = b.i_id
    -> and a.t_1_id = c.w_id
    -> and a.s_ytd = 0;
Query OK, 4800000 rows affected (4 min 43.57 sec)
Records: 4800000  Duplicates: 0  Warnings: 0

確實效率不錯，500萬數據需要4 min 43.57 sec，生產環境的2700萬數據大概需要半個小時左右。

但這是不是效率最高的辦法呢，因為最終結果集會返回大表的90%以上的數據，所以需要對大量的索引數據回表，因為回表是會產生隨機IO的，這個回表代價確實比較高，優化器默認也沒有選擇這種執行計劃。如果我們給小表的關聯字段上加索引會是什么效果呢？

接下來我給兩個小表的關聯字段上加了索引。

mysql> alter table t_2 add key(i_id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_1 add key(w_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

我們去掉大表的force index，不干涉優化器，讓優化器自己做決策。執行計劃如下：

上圖的執行計劃顯示，優化器選擇了對大表全表掃描，大表做驅動表，驅動兩個小表。那這樣的實際效果如何呢？

mysql> insert into t_4
    -> SELECT
    ->   c.w_name,
    ->   a.s_i_id,
    ->   a.s_quantity,
    ->   a.s_ytd,
    ->   a.s_order_cnt,
    ->   a.s_remote_cnt,
    ->   a.s_data,
    ->   a.t_2_id,
    ->   b.i_name,
    ->   b.i_price
    -> FROM
    ->  t_3 a,
    ->  t_2 b,
    ->  t_1 c
    -> WHERE
    ->  a.t_2_id = b.i_id
    -> and a.t_1_id = c.w_id
    -> and a.s_ytd = 0;
Query OK, 4800000 rows affected (1 min 59.06 sec)
Records: 4800000  Duplicates: 0  Warnings: 0

這種方式耗時1min 59.06sec ，效率提高1倍多，生產環境的大數據量，效率提升應該更明顯。果然采用大表驅動小表這種方式效率提高了，優化器的選擇是對的。

選擇這種方式的好處：

1.SQL的執行效率高一倍

2.節省空間，因為大表的索引會占用很大的磁盤空間。

3.響應及時，避免了必須等到變更窗口才能加索引的麻煩。

4.不用修改SQL語句

該如何選擇是不是很清楚了呢？

到這里似乎優化就結束了，但是如果想要精益求精，追求極致的話，小表上的索引可以建成覆蓋索引，防止小表回表取數據。

mysql> alter table t_1 drop key w_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_2 drop key i_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_2 add key(i_id,i_name,i_price);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_1 add key(w_id,w_name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

執行效果如下：

mysql> insert into t_4
    -> SELECT
    ->   c.w_name,
    ->   a.s_i_id,
    ->   a.s_quantity,
    ->   a.s_ytd,
    ->   a.s_order_cnt,
    ->   a.s_remote_cnt,
    ->   a.s_data,
    ->   a.t_2_id,
    ->   b.i_name,
    ->   b.i_price
    -> FROM
    ->  t_3 a,
    ->  t_2 b,
    ->  t_1 c
    -> WHERE
    ->  a.t_2_id = b.i_id
    -> and a.t_1_id = c.w_id
    -> and a.s_ytd = 0;
Query OK, 4800000 rows affected (1 min 38.99 sec)
Records: 4800000  Duplicates: 0  Warnings: 0

可以看出，小表上的索引建成覆蓋索引，耗時又縮短了20秒，執行效率更高了。

至此該條SQL的優化結束。

總結

1.本條SQL的最終執行計劃是大表驅動小表，這也算是給上篇文章《NL連接一定是小表驅動大表效率高嗎》提供了一個案例。

2.優化措施可能有很多不同的選擇，要根據實際情況選擇最優的，不要草率做出決定。

3.精益求精是優化的極致，但是有時候也是需要做出折中選擇的，達到業務運行的要求是目的，這點以后遇到案例再說。

責任編輯：武曉燕來源： GreatSQL社區

SQL 關聯索引