mysql連線查詢關聯欄位是否有索引的比較

先介紹下有如下兩張表：

dm_scheduler（節目排期表）有3509條記錄

dm_scheduler_seat（排期座位表）有193967條記錄

dm_scheduler。id和dm_scheduler_seat。scheduleId是關聯欄位，其中id也是dm_scheduler的主鍵

1.首先不給dm_scheduler_seat.scheduleId新增索引

SELECT COUNT（*） FROM dm_scheduler s LEFT JOIN dm_scheduler_seat ss ON s。`id`=ss。`scheduleId`； —— 看下執行結果COUNT（*） ——————1953671 queries executed， 1 success， 0 errors， 0 warnings共 1 行受到影響執行耗時： 46。919 sec傳送時間： 0 sec總耗時： 46。920 sec

竟然花費了46s多，分析下執行計劃

explainSELECT COUNT（*） FROM dm_scheduler s LEFT JOIN dm_scheduler_seat ss ON s。`id`=ss。`scheduleId`； id select_type table partitions type possible_keys key key_len ref rows filtered Extra ———— ——————- ———— —————— ———— ——————- ——————————- ————- ———— ———— ———— —————————————————————————— 1 SIMPLE s （NULL） index （NULL） dm_scheduler_ibfk_1 9 （NULL） 3582 100。00 Using index 1 SIMPLE ss （NULL） ALL （NULL）（NULL）（NULL）（NULL） 179494 100。00 Using where； Using join buffer （Block Nested Loop）

主要原因還是dm_scheduler_seat的scheduleId沒有索引，導致了將dm_scheduler的結果集一行一行地傳給（準確地說是多行一起傳入，在Using index中可以看到用的是BNL演算法，會將讀取到的多條記錄放入一個buffer）dm_scheduler_seat進行匹配的時候走了全表掃描。

換一種寫法：

SELECT COUNT（*）FROM dm_scheduler s INNER JOIN dm_scheduler_seat ss ON s。`id`=ss。`scheduleId`；—— 看下執行結果COUNT（*） ——————1939671 queries executed， 1 success， 0 errors， 0 warnings共 1 行受到影響執行耗時： 0。265 sec傳送時間： 0 sec總耗時： 0。266 sec

可以看到只花費了0。2s多，分析下執行計劃

EXPLAINSELECT COUNT（*）FROM dm_scheduler s INNER JOIN dm_scheduler_seat ss ON s。`id`=ss。`scheduleId`； id select_type table partitions type possible_keys key key_len ref rows filtered Extra ———— ——————- ———— —————— ———— ——————- ————- ————- —————————————— ———— ———— ——————- 1 SIMPLE ss （NULL） ALL （NULL）（NULL）（NULL）（NULL） 179494 100。00 Using where 1 SIMPLE s （NULL） eq_ref PRIMARY PRIMARY 8 dm_scheduler。ss。scheduleId 1 100。00 Using index

雖然dm_scheduler_seat是全表掃描，但是隻會操作一次，然後將結果集代入dm_scheduler進行比對，因為dm_scheduler。id是主鍵索引，所以速度會很快

那麼如何讓left join 的速度加快呢？解決方法就是給dm_scheduler_seat。schedulerId加上索引就可以了

ALTER TABLE dm_scheduler_seat ADD INDEX `scheduleId`（`scheduleId`）；SELECT COUNT（*） FROM dm_scheduler s LEFT JOIN dm_scheduler_seat ss ON s。`id`=ss。`scheduleId`； —— 看下執行結果COUNT（*） ——————1953671 queries executed， 1 success， 0 errors， 0 warnings共 1 行受到影響執行耗時： 0。135 sec傳送時間： 0 sec總耗時： 0。136 sec

這次從46s執行變成了0。1s，分析下執行計劃

EXPLAINSELECT COUNT（*） FROM dm_scheduler s LEFT JOIN dm_scheduler_seat ss ON s。`id`=ss。`scheduleId`； id select_type table partitions type possible_keys key key_len ref rows filtered Extra ———— ——————- ———— —————— ———— ——————- ——————————- ————- ————————- ———— ———— ——————- 1 SIMPLE s （NULL） index （NULL） dm_scheduler_ibfk_1 9 （NULL） 3582 100。00 Using index 1 SIMPLE ss （NULL） ref scheduleId scheduleId 9 dm_scheduler。s。id 85 100。00 Using index

可以看到沒有了 Using join buffer （Block Nested Loop）了，因為加上了索引使用的就是B+樹查找了。

2.這個時候我們繼續將dm_scheduler_seat.scheduler.id索引刪除，做另外一個測試

查詢dm_scheduler。id在dm_scheduler_seat中存在的記錄

SELECT COUNT（*） FROM dm_scheduler s WHERE EXISTS （SELECT 1 FROM dm_scheduler_seat WHERE scheduleId=s。`id`）—— 看下執行結果COUNT（*） ——————21091 queries executed， 1 success， 0 errors， 0 warnings共 1 行受到影響執行耗時： 11 min 29 sec傳送時間： 0。001 sec總耗時： 11 min 29 sec

可以看到執行了接近11min，分析下執行計劃

EXPLAINSELECT COUNT（*） FROM dm_scheduler s WHERE EXISTS （SELECT 1 FROM dm_scheduler_seat WHERE scheduleId=s。`id`） id select_type table partitions type possible_keys key key_len ref rows filtered Extra ———— —————————— ————————- —————— ———— ——————- ——————————- ————- ———— ———— ———— —————————————— 1 PRIMARY s （NULL） index （NULL） dm_scheduler_ibfk_1 9 （NULL） 3582 100。00 Using where； Using index 2 DEPENDENT SUBQUERY dm_scheduler_seat （NULL） ALL （NULL）（NULL）（NULL）（NULL） 179494 10。00 Using where

其實就是將dm_scheduler的結果集一條條傳入dm_scheduler_seat進行一個全表掃描

換一種寫法

SELECT COUNT（*） FROM dm_scheduler s WHERE s。`id` IN （SELECT scheduleId FROM dm_scheduler_seat）—— 看下執行結果COUNT（*） ——————21091 queries executed， 1 success， 0 errors， 0 warnings共 1 行受到影響執行耗時： 0。291 sec傳送時間： 0 sec總耗時： 0。291 sec

我們可以看到這個語句花費了0。2s多，分析下執行計劃

EXPLAINSELECT COUNT（*） FROM dm_scheduler s WHERE s。`id` IN （SELECT scheduleId FROM dm_scheduler_seat） id select_type table partitions type possible_keys key key_len ref rows filtered Extra ———— —————— ————————- —————— ———— ——————- ——————————- ————- ————————- ———— ———— —————————————— 1 SIMPLE s （NULL） index PRIMARY dm_scheduler_ibfk_1 9 （NULL） 3582 100。00 Using where； Using index 1 SIMPLE （NULL） eq_ref 9 dm_scheduler。s。id 1 100。00 （NULL） 2 MATERIALIZED dm_scheduler_seat （NULL） ALL （NULL）（NULL）（NULL）（NULL） 179494 100。00 （NULL）

我們可以看到執行計劃比之前多了一條記錄，因為mysql把 in 裡面的 SELECT scheduleId FROM dm_scheduler_seat 做了優化了，MATERIALIZE表示把子查詢的結果物化到臨時表，執行連線的時候，可以用臨時表的索引（MySQL自動為臨時表建立索引）完成連線操作

那麼如何最佳化帶有exists的sql呢？這個時候我們再給dm_scheduler_seat。schedulerId加上索引就可以了

ALTER TABLE dm_scheduler_seat ADD INDEX `scheduleId`（`scheduleId`）；SELECT COUNT（*） FROM dm_scheduler s WHERE EXISTS （SELECT 1 FROM dm_scheduler_seat WHERE scheduleId=s。`id`）—— 看下執行結果COUNT（*） ——————21091 queries executed， 1 success， 0 errors， 0 warnings共 1 行受到影響執行耗時： 0。240 sec傳送時間： 0 sec總耗時： 0。241 sec

這個時候只花費了0。2秒多，看下執行計劃

EXPLAINSELECT COUNT（*） FROM dm_scheduler s WHERE EXISTS （SELECT 1 FROM dm_scheduler_seat WHERE scheduleId=s。`id`） id select_type table partitions type possible_keys key key_len ref rows filtered Extra ———— —————————— ————————- —————— ———— ——————- ——————————- ————- ————————- ———— ———— —————————————— 1 PRIMARY s （NULL） index （NULL） dm_scheduler_ibfk_1 9 （NULL） 3582 100。00 Using where； Using index 2 DEPENDENT SUBQUERY dm_scheduler_seat （NULL） ref scheduleId scheduleId 9 dm_scheduler。s。id 85 100。00 Using index

可以看到在掃描dm_scheduler_seat的時候type變為了ref

總結:使用連線查詢的時候需要額外關注關聯的欄位是否新增索引