MySQL 未按預期使用帶有 WHERE IN 子句的索引

我有一個名為 mytable 的表，我正在嘗試優化它的查詢。我在其上生成了索引 ind5，它首先具有可為空的 int 列 col_1。該查詢有一個WHERE IN子句，似乎完全關閉了查詢優化器。這是可行的：

explain select status from mytable use index (ind5)
   where col_1 = 10 limit 0,10'

+----+-------------+-------------+------+-------------------+-------------------+---------+-------+--------+-------+
| id | select_type | table       | type | possible_keys     | key               | key_len | ref   | rows   | Extra |
+----+-------------+-------------+------+-------------------+-------------------+---------+-------+--------+-------+
|  1 | SIMPLE      | mytable     | ref  | ind5              | ind5              | 5       | const | 718156 | NULL  |
+----+-------------+-------------+------+-------------------+-------------------+---------+-------+--------+-------+

到現在為止還挺好。現在讓我們試試：

explain select status from mytable use index (ind5)
   where col_1 in (1, 10)  limit 0,10'

+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+
| id | select_type | table       | type | possible_keys     | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | mytable     | ALL  | ind5              | NULL | NULL    | NULL | 1436312 | Using where |
+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+

這裡有什麼變化？我正在使用 a WHERE IN，但這應該等同於col_1 = 1 OR col_1 = 10，所以我仍然希望 MySQL 使用索引，它顯然沒有這樣做。如果我這樣做怎麼辦：

explain select status from mytable use index (ind5)
   where col_1 = 10 or col_1 = 1 limit 0,10'

+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+
| id | select_type | table       | type | possible_keys     | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | mytable     | ALL  | ind5              | NULL | NULL    | NULL | 1436312 | Using where |
+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+

在這裡完全失去了我。發生了什麼？為什麼 MySQL 不使用 ind5？

這是 MySQL 5.6。

聽起來它col_1不是 index 中的第一（或唯一）列ind5。或者，許多行都有col_1 = 10. 請提供來自的輸出

SELECT col_1, COUNT(*) FROM mytable GROUP BY col_1;

同時，

INDEX(col_1, status)

對於所有三個查詢都是最佳的。

那IN和那OR被優化的一樣。

status加入索引使其“覆蓋”，因此無需進入數據的 BTree 。

這是檢查性能的另一種方法（即使“太少”行）：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

注意：如果超過 20% 的行有col_1=10，優化器將決定掃描表，而不是在索引的 BTree 和數據的 BTree 之間來回跳動。

引用自：https://dba.stackexchange.com/questions/299979