mysql - 为什么MySQL的 `explain`在使用 `filesort`时不说 `filesort`？

Question

我有一个类似于以下内容的查询:

SELECT * FROM tablename WHERE condition = 1 ORDER BY id LIMIT 1000;

id是主键。我有一个索引 condition以及 id 上的索引(我还没有在 condition 和 id 上添加复合索引)。此查询仅使用 condition索引，而不是 id索引。

如果我explain查询，它只是说 Using where 。我预计它会说 filesort 。但是，由于此查询在不使用索引的情况下进行排序，因此它必须使用文件排序。此外，该查询超时，这是它使用文件排序的另一个线索。如果我运行没有 order by 的查询，它不会超时。

为什么有时不说 filesort ？我认为应该使用 filesort在这两种情况下，因为查询太慢了。

Answer 1

我怀疑问题在于您是按 id 订购的。如果id是一个自动递增的整数，则表可能已经按id排序。例如...

mysql> describe tablename;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| value  | int(11) | YES  | MUL | NULL    |                |
| status | int(11) | YES  | MUL | NULL    |                |
+--------+---------+------+-----+---------+----------------+

mysql> show indexes from tablename;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tablename |          0 | PRIMARY  |            1 | id          | A         |      199824 |     NULL | NULL   |      | BTREE      |         |               |
| tablename |          1 | value    |            1 | value       | A         |      101829 |     NULL | NULL   | YES  | BTREE      |         |               |
| tablename |          1 | status   |            1 | status      | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select * from tablename where status = 1 order by id limit 1000;
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows  | filtered | Extra                 |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+-----------------------+
|  1 | SIMPLE      | tablename | NULL       | ref  | status        | status | 5       | const | 99912 |   100.00 | Using index condition |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

按 id 排序时，不进行文件排序。观察当我们按另一个索引列排序时会发生什么...

mysql> explain select * from tablename where status = 1 order by value limit 1000;
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+---------------------------------------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows  | filtered | Extra                                 |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+---------------------------------------+
|  1 | SIMPLE      | tablename | NULL       | ref  | status        | status | 5       | const | 99912 |   100.00 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

这就是您想要的文件类型。

<小时/>

事实上，select ... 和 select ... order by id 都以相同的顺序返回。 order by id 是隐式排序。

mysql> SELECT * FROM tablename WHERE status = 1 order by id LIMIT 1000,10;
+------+-------+--------+
| id   | value | status |
+------+-------+--------+
| 1935 |    12 |      1 |
| 1939 |    59 |      1 |
| 1940 |    56 |      1 |
| 1941 |    21 |      1 |
| 1942 |     5 |      1 |
| 1943 |    68 |      1 |
| 1944 |    65 |      1 |
| 1947 |    27 |      1 |
| 1948 |    44 |      1 |
| 1950 |    75 |      1 |
+------+-------+--------+
10 rows in set (0.01 sec)

mysql> SELECT * FROM tablename WHERE status = 1 LIMIT 1000,10;
+------+-------+--------+
| id   | value | status |
+------+-------+--------+
| 1935 |    12 |      1 |
| 1939 |    59 |      1 |
| 1940 |    56 |      1 |
| 1941 |    21 |      1 |
| 1942 |     5 |      1 |
| 1943 |    68 |      1 |
| 1944 |    65 |      1 |
| 1947 |    27 |      1 |
| 1948 |    44 |      1 |
| 1950 |    75 |      1 |
+------+-------+--------+
10 rows in set (0.00 sec)

您不能依赖此默认顺序，但 MySQL 可以。