为什么MySQL选择范围扫描(慢)和索引扫描(更快)

Question

我遇到了一个问题，我有一个表，描述如下：

mysql> desc myTable;
+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
| score               | int(11)      | YES  | MUL | 0       |                |
| created_at          | datetime     | YES  |     | NULL    |                |
| updated_at          | datetime     | YES  |     | NULL    |                |
| previous_score      | int(11)      | NO   | MUL | 0       |                |
+---------------------+--------------+------+-----+---------+----------------+

具有以下索引：

mysql> show indexes from myTable;
+-------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                           | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| myTable |          0 | PRIMARY                          |            1 | id             | A         |          48 |     NULL | NULL   |      | BTREE      |         |               |
| myTable |          1 | index_my_table_on_previous_score |            1 | previous_score | A         |          48 |     NULL | NULL   |      | BTREE      |         |               |
| myTable |          1 | index_my_table_on_score          |            1 | score          | A         |          48 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

问题如下：

mysql> select count(*) from myTable where score > 10;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.07 sec)

mysql> select count(*) from myTable ignore index(index_my_table_on_score) where score > 10;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.00 sec)

如您所见，使用索引会产生比执行全表扫描更慢的结果。由于我在列previous_score上有另一个索引，所以我决定使用explain来进一步理解这个问题：

mysql> select count(*) from myTable where previous_score > 10;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.00 sec)

所以，正如你所看到的，非常快。让我们进行explain比较：

mysql> explain select count(*) from myTable where score > 10;
+----+-------------+-------+-------+----------------------+----------------------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys           | key                     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------------+----------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | myTable | range | index_my_table_on_score | index_my_table_on_score | 5       | NULL |   24 | Using where; Using index |
+----+-------------+-------+-------+----------------------+----------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select count(*) from myTable where previous_score > 10;
+----+-------------+-------+-------+-------------------------------+-------------------------------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys                    | key                              | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+-------------------------------+-------------------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | myTable | index | index_my_table_on_previous_score | index_my_table_on_previous_score | 4       | NULL |   48 | Using where; Using index |
+----+-------------+-------+-------+-------------------------------+-------------------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

作为附加信息，我正在执行的查询将遍历表(48)中的所有结果。

• 为什么，如果定义的列完全相同，而且索引完全相同，那么score**，选择对进行范围扫描，对** previous_score**?**执行完整的索引扫描
• 其次，为什么range scan full index 比扫描慢？距离扫描比索引扫描要好，索引扫描要比全表扫描好。显然，对于我的特定用例来说，情况并非如此。
• 有没有办法建议MySQL做索引扫描而不是范围扫描？

任何解释或建议都是非常感谢的。

Answer 1

对于像这样的小桌子，这里有一种比计时更好的方法：

FLUSH STATUS;
SELECT SQL_NO_CACHE ...;
SHOW SESSION STATUS LIKE 'Handler%';

总结数字列是比较SELECT的一种变化和另一种变化的一个很好的标准。

两个EXPLAINs都说它们是Using index --实际上是在构成索引的BTree中执行查询。没有桌子扫描。

请提供SHOW CREATE TABLE，它比DESCRIBE更具描述性。

如果“缓存”是冷的，这可以解释0.07 sec。