MySQL在WHERE子句中不按预期使用索引

Question

我有一个名为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 |
+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+

这里发生了什么变化？我使用的是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。

Answer 1

听起来，col_1不是索引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_烹饪书_mysql#handler_数数

注意:如果超过20%的行有col_1=10，优化器将决定扫描表，而不是在索引的BTree和数据的BTree之间来回跳。