背景
接到客戶訴求說一條SQL長(zhǎng)時(shí)間運(yùn)行不出結(jié)果，讓給看看怎么回事，SQL不復(fù)雜，優(yōu)化措施也不復(fù)雜，但是要想SQL達(dá)到最優(yōu)狀態(tài)，也是需要經(jīng)過一番考量并做出選擇的。下面借實(shí)驗(yàn)還原一下此SQL優(yōu)化過程。
實(shí)驗(yàn)：
數(shù)據(jù)庫(kù)環(huán)境：MySQL5.7.39
測(cè)試表結(jié)構(gòu)如下：
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萬條數(shù)據(jù)。我這里實(shí)驗(yàn)數(shù)據(jù)量少些，客戶實(shí)際業(yè)務(wù)表數(shù)據(jù)量分別是（30，150，2700萬）。t_4表為一個(gè)歷史數(shù)據(jù)歸檔表，用于插入數(shù)據(jù)。
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;
查看語(yǔ)句中select部分的執(zhí)行計(jì)劃如下圖所示：
看到這個(gè)計(jì)劃，就想對(duì)數(shù)據(jù)庫(kù)說一句："您辛苦了!"。
優(yōu)化器選擇先對(duì)兩個(gè)小表c，b進(jìn)行關(guān)聯(lián)，然后得到的結(jié)果集再與大表a進(jìn)行關(guān)聯(lián)，因?yàn)檎Z(yǔ)句中c，b兩個(gè)表沒有字段進(jìn)行直接關(guān)聯(lián)，所以這兩個(gè)表連接后的結(jié)果集是一個(gè)笛卡爾積25 *100=2500，因?yàn)榇蟊淼年P(guān)聯(lián)字段上沒有索引，所以需要對(duì)最內(nèi)層的大表全表掃描2500次。
這是不是一個(gè)大工程呢？數(shù)據(jù)庫(kù)任勞任怨，你讓它干，它就干，只要你等得起就可以。事實(shí)上我們是沒有耐心等的。我本來還想看看數(shù)據(jù)庫(kù)到底用多久才能給出結(jié)果，等了10分鐘，實(shí)在沒有耐心繼續(xù)等下去了。
這條SQL不復(fù)雜吧，就是三張表進(jìn)行關(guān)聯(lián)，但是關(guān)聯(lián)字段上都沒有索引，都進(jìn)行了全表掃描。那么解決措施就是加索引，但是索引怎么加就需要做出選擇了。
有同事就提出這個(gè)SQL在大表上全表掃描2500次，在大表的關(guān)聯(lián)字段上加上索引就可以了，看到這里，你有沒有認(rèn)同這個(gè)見解呢？我想應(yīng)該有很多小伙伴是認(rèn)同的。
不錯(cuò)，給大表加上索引就不用全表掃描了，首先大表加索引，會(huì)鎖表很長(zhǎng)時(shí)間，這個(gè)索引在客戶的生產(chǎn)環(huán)境須等到變更窗口才能加，客戶等不及，其次你有考慮過這真的是最好的辦法嗎？
因?yàn)槲疫@是實(shí)驗(yàn)環(huán)境，可以隨時(shí)給大表加索引，那接下來我們就給大表加上索引試試效果。
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
索引加好之后，執(zhí)行計(jì)劃如下：
可以看出優(yōu)化器并沒有選擇走索引，依然是使用BNL優(yōu)化策略，進(jìn)行全表掃描，為什么不走索引呢？應(yīng)該是優(yōu)化器認(rèn)為索引掃描的成本高于全表掃描的成本，因?yàn)檫@條語(yǔ)句最終結(jié)果要返回大表的90%以上的數(shù)據(jù)，走索引后回表代價(jià)是很高的。這一點(diǎn)我們是不認(rèn)同優(yōu)化器的，怎么著2500次全表掃描也比每次通過索引范圍掃描的代價(jià)要高呀，好吧，既然不認(rèn)同，那么使用force index來干涉優(yōu)化器決策，讓它使用索引。
執(zhí)行計(jì)劃如下圖所示：
執(zhí)行計(jì)劃中顯示索引用上了，那實(shí)際執(zhí)行效果如何呢？
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
確實(shí)效率不錯(cuò)，500萬數(shù)據(jù)需要4 min 43.57 sec，生產(chǎn)環(huán)境的2700萬數(shù)據(jù)大概需要半個(gè)小時(shí)左右。
但這是不是效率最高的辦法呢，因?yàn)樽罱K結(jié)果集會(huì)返回大表的90%以上的數(shù)據(jù)，所以需要對(duì)大量的索引數(shù)據(jù)回表，因?yàn)榛乇硎菚?huì)產(chǎn)生隨機(jī)IO的，這個(gè)回表代價(jià)確實(shí)比較高，優(yōu)化器默認(rèn)也沒有選擇這種執(zhí)行計(jì)劃。如果我們給小表的關(guān)聯(lián)字段上加索引會(huì)是什么效果呢？
接下來我給兩個(gè)小表的關(guān)聯(lián)字段上加了索引。
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
我們?nèi)サ舸蟊淼膄orce index，不干涉優(yōu)化器，讓優(yōu)化器自己做決策。執(zhí)行計(jì)劃如下：
上圖的執(zhí)行計(jì)劃顯示，優(yōu)化器選擇了對(duì)大表全表掃描，大表做驅(qū)動(dòng)表，驅(qū)動(dòng)兩個(gè)小表。那這樣的實(shí)際效果如何呢？
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
這種方式耗時(shí)1min 59.06sec ，效率提高1倍多，生產(chǎn)環(huán)境的大數(shù)據(jù)量，效率提升應(yīng)該更明顯。果然采用大表驅(qū)動(dòng)小表這種方式效率提高了，優(yōu)化器的選擇是對(duì)的。
選擇這種方式的好處：
1.SQL的執(zhí)行效率高一倍
2.節(jié)省空間，因?yàn)榇蟊淼乃饕龝?huì)占用很大的磁盤空間。
3.響應(yīng)及時(shí)，避免了必須等到變更窗口才能加索引的麻煩。
4.不用修改SQL語(yǔ)句
該如何選擇是不是很清楚了呢？
到這里似乎優(yōu)化就結(jié)束了，但是如果想要精益求精，追求極致的話，小表上的索引可以建成覆蓋索引，防止小表回表取數(shù)據(jù)。
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
執(zhí)行效果如下：
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
可以看出，小表上的索引建成覆蓋索引，耗時(shí)又縮短了20秒，執(zhí)行效率更高了。
至此該條SQL的優(yōu)化結(jié)束。
總結(jié)
1.本條SQL的最終執(zhí)行計(jì)劃是大表驅(qū)動(dòng)小表，這也算是給上篇文章《NL連接一定是小表驅(qū)動(dòng)大表效率高嗎》提供了一個(gè)案例。
2.優(yōu)化措施可能有很多不同的選擇，要根據(jù)實(shí)際情況選擇最優(yōu)的，不要草率做出決定。
3.精益求精是優(yōu)化的極致，但是有時(shí)候也是需要做出折中選擇的，達(dá)到業(yè)務(wù)運(yùn)行的要求是目的，這點(diǎn)以后遇到案例再說。
