在查询中添加 LIMIT 会使查询时间增加 1000% 以上

Question

我正在运行以下查询：

SELECT 
        MyField,
        COUNT(*) AS MyCount
    FROM
        MyTable
    NATURAL JOIN
        AnotherTable
    WHERE
        Timestamp >= 1000 AND Timestamp <= 10000
    GROUP BY
        MyField
    ORDER BY
        MyCount DESC;

运行良好，大约需要 6 秒才能完成。如果我想将结果限制为仅显示最高的 20 个 MyCount，我会在查询末尾添加 LIMIT 20。突然需要6分钟才能完成！

原始查询的 EXPLAIN 输出：

+----+-------------+-------------+--------+---------------------------+---------+---------+---------------------------+---------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys             | key     | key_len | ref                       | rows    | Extra                                        |
+----+-------------+-------------+--------+---------------------------+---------+---------+---------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | MyTable     | ALL    | mytable_fkey              | NULL    | NULL    | NULL                      | 6858209 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | AnotherTable| eq_ref | PRIMARY                   | PRIMARY | 4       | test.MyTable.FKeyID       |       1 | Using index                                  |
+----+-------------+-------------+--------+---------------------------+---------+---------+---------------------------+---------+----------------------------------------------+

带有 LIMIT 20 的查询的 EXPLAIN 输出：

+----+-------------+-------------+--------+---------------------------+-------------------------+---------+---------------------------+---------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys             | key                     | key_len | ref                       | rows    | Extra                                        |
+----+-------------+-------------+--------+---------------------------+-------------------------+---------+---------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | MyTable     | index  | mytable_fkey              | myfield_timestamp_index | 771     | NULL                      | 6858209 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | AnotherTable| eq_ref | PRIMARY                   | PRIMARY                 | 4       | test.MyTable.FKeyID       |       1 | Using index                                  |
+----+-------------+-------------+--------+---------------------------+-------------------------+---------+---------------------------+---------+----------------------------------------------+

对此有何解释？有没有更好的方法可以限制行数？

Answer 1

如果您看到 Usingtemporary;在 EXPLAIN 输出中使用 filesort ，您可能会缺少合适的索引，并且因此而被杀死。

确保您的 JOIN 和 GROUP BY 字段在同一索引中均可用。