mysql - 删除 "Using filesort"以进行简单的 MySQL 查询

Question

我正在尝试从一个简单的 MySQL 查询中删除文件排序。

该表有 active 列，它是 0 或 1，还有 season_average 列，它是一个整数，没有连接。

我的select语句如下:

SELECT 
*
FROM `group` WHERE active=1 
ORDER BY season_average DESC

我已经在 active、season_average 和 (active, season_average) 和 (season_average) 上尝试了多个列, active) 这些都不能解决问题。

这个 SQL Fiddle 显示了我遇到的问题:http://sqlfiddle.com/#!9/77861/2

有没有办法解决这个问题？

Answer 1

Subrata Dey Pappu 的想法是正确的，他说用小样本集来判断查询性能是查看查询性能的糟糕测试。在您的情况下，查询优化器确定对 5 行进行全表扫描并使用 quicksort 对顺序进行排序更容易。而不是使用您创建的复合索引 (active, season_average)。通过运行解释计划可以明显看出这一点:
它将可能的键选择显示为 active_seasonAverage，但是没有 key 用于该小样本数据集。

虽然增加样本集大小会调整查询优化器访问数据的方式(如 Subrata Dey Pappu 在上面的评论中指出的那样)，但您可以通过运行来欺骗它(不建议将其硬编码到您的应用程序中)同样的查询，但像这样:

 SELECT 
*
FROM `group` force index (active_seasonAverage) WHERE active=1 
ORDER BY season_average DESC

这会强制查询优化器使用您的复合键，解释计划将如下所示:

请注意 key 不是 NULL，而是使用 active_seasonAverage 并且在 Extra 下没有使用文件排序。

---

此外，对于小型数据集测试，请考虑以下摘自 Mysql 手册页 How MySQL Uses Indexes 的摘录:

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.

因此，使用各种索引和实际的大型数据集执行测试，通过 Explain 运行测试。 .