为什么排序行会减少行计数?

Question

我有一个大约有两三百万行的表…

mysql> select count(*) from tbl;
+----------+
| count(*) |
+----------+
|  2615889 |
+----------+
1 row in set (1.23 sec)
mysql> show indexes from tbl;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl   |          0 | PRIMARY           |            1 | tbl_id      | A         |     2284627 |     NULL | NULL   |      | BTREE      |         |               |
| ...
| tbl   |          1 | tbl_fld           |            1 | fld         | A         |     2284627 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.30 sec)

对于下面的查询，如果我添加一个order by子句，我似乎做得更好(即，我最终使用索引)…

mysql> explain select * from tbl
    -> where fld in (select fld from tbl group by fld having count(*)>1)
    -> limit 1000;
+----+--------------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+--------------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | PRIMARY            | tbl   | ALL   | NULL          | NULL    | NULL    | NULL | 2328333 | Using where |
|  2 | DEPENDENT SUBQUERY | tbl   | index | NULL          | tbl_fld | 15      | NULL |       1 | Using index |
+----+--------------------+-------+-------+---------------+---------+---------+------+---------+-------------+
2 rows in set (0.00 sec)
mysql> explain select * from tbl
    -> where fld in (select fld from tbl group by fld having count(*)>1)
    -> order by fld limit 1000;
+----+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | tbl   | index | NULL          | tbl_fld | 15      | NULL | 1000 | Using where |
|  2 | DEPENDENT SUBQUERY | tbl   | index | NULL          | tbl_fld | 15      | NULL |    1 | Using index |
+----+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

为什么?

Answer 1

乍一看，它似乎在第二个查询中使用了一个键，这是一件好事，因为它可以防止对表进行完整扫描。

Answer 2

LIMIT不应该在没有ORDER BY的情况下使用，因为没有明确定义将返回哪些行。正如你所发现的那样，按顺序…LIMIT优化不起作用(根本没有实现)，而当你添加ORDER BY时，优化开始，MySQL在找到足够的行后停止执行。