歡迎光臨
每天分享高質量文章

這種 SQL 寫法會導致索引失效?

來自公眾號:譚小譚

網上經常能看到一些文章總結在 mysql 中不能命中索引的各種情況,其中有一種說法就是指使用了 or 的陳述句都不能命中索引。

 

這種說法其實是不夠正確的,正確的結論應該是,從 mysql5.0 後,如果在 or 連接的欄位上都有獨立的索引的話,是可以命中索引的,這裡就是用到了 index_merge 特性。

 

在 mysql5.0 版本以前一條 sql 只能選擇使用一個索引,而且如果 sql 中使用了 or 關鍵字,那麼已有的索引就會失效,會走全表掃描。因為無論走哪個索引,mysql 都不能一次性查找出符合條件的資料,所以只能放棄索引。

 

mysql 也是一直在不斷升級更新,所以在 mysql5.0 版本後,增加了 index_merge 索引合併這個特性,也因此支持了一條 sql 使用多個索引。

 

index_merge 核心思想就是先分別使用單個索引查出滿足要求的資料,然後再將這些資料合併到一起傳回。

 

我們可以看一個的例子。

這裡依然沿用我們前面文章中創建的表和測試資料,表中插入了 10 w 條測試資料,表結構如下。

CREATE TABLE `t` (
  `id` int(11NOT NULL,
  `a` int(11DEFAULT NULL,
  `b` int(11DEFAULT NULL,
  PRIMARY KEY (`id`)
ENGINE=InnoDB;

 

我們先來給  a 欄位添加一個索引,然後執行一條帶 or 的查詢陳述句看看。

 

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

mysql> explain select a from t where a=100 or b=6000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | ALL  | a_index       | NULL | NULL    | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

因為欄位 b 上沒有索引,mysql 認為走全表掃描代價更低一些,因為可以免去回表過程。

 

那麼我們給 b 欄位也加上索引試試,然後再執行剛剛那條 sql 。

 

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

mysql> explain select a from t where a=100 or b=6000;
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
| id | select_type | table | type        | possible_keys   | key             | key_len | ref  | rows | Extra                                     |
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
|  1 | SIMPLE      | t     | index_merge | a_index,b_index | a_index,b_index | 5,5     | NULL |    2 | Using union(a_index,b_index); Using where |
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
1 row in set (0.00 sec)

這回可以看到 mysql 同時使用了 a、b 兩個索引,並且看到 type 欄位的值為 index_merge。

 

接下來再來看另一條 sql,看看結果又是怎樣的。

 

mysql> explain select a from t where a>100 or b>6000;
+----+-------------+-------+------+-----------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+-----------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | ALL  | a_index,b_index | NULL | NULL    | NULL | 100332 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

 

這條 sql 僅僅是把等號改成了大於號,也就是說傳回的結果集是一個區間集,mysql 在這裡又放棄了索引,走的全表掃描,不過有看文章說在 mysql5.7 版本後優化了這個問題,即在區間查詢中也支持使用 index_merge,我的版本是 5.6 ,暫未驗證這個優化,有興趣的可以去驗證下。

 

其實在 mysql 中很多東西都是不絕對的,對於同一條 sql 不同 mysql 版本的內部處理方式有可能是不太一樣的,同時也可以看到 mysql 一直在不斷優化升級,一些老舊的知識點很容易就會不再適用了。

希望文章對你有幫助,歡迎關註,點個在看是對我最好的支持,感謝。

已同步到看一看
赞(0)

分享創造快樂