Mysql Order by 子句使用 "FileSort"

Question

我有一个像这样的表结构

comment_id primary key
comment_content 
comment_author
comment_author_url

当我像

这样触发查询时

explain SELECT * FROM comments  ORDER BY comment_id

输出结果为

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  comments    ALL     NULL    NULL    NULL    NULL    22563   Using filesort

为什么找不到我定义为主键的索引？

Answer 1

不是因为不能使用索引。这是因为优化器认为不使用索引并执行文件排序¹ 会更快。您应该会在 MyiSAM 和 InnoDB 表中看到不同的行为。

InnoDB 创建 PRIMARY 键作为集群键(如果没有定义主键，则创建第一个 UNIQUE)，这可用于具有 ORDER 的查询BY pk 或 WHERE pk BETWEEN low AND high 因为所有需要的值都在这个聚集键和连续位置(聚集键是表)。

MyISAM 表只有 B-tree 索引，所以如果查询使用这个索引，它必须读取整个索引并且它会以想要的顺序具有 comment_id 值(这真的很好)但是它也必须读取表格(不太好)才能获得所有其他想要的列。所以，优化器认为既然要读取表，为什么不扫描所有表并进行文件排序呢？您可以通过尝试进行测试:

SELECT comment_id FROM comments  ORDER BY comment_id ;

它将使用索引而不进行文件排序，因为查询只需要存储在索引中的值。

---

如果你想在 MyiSAM 中有类似的(与 InnoDB)行为，你可以尝试在 (comment_id, comment_content, comment_author, comment_author_url) 上创建一个索引，然后尝试你的查询。所有需要的值都将在索引中找到并以正确的顺序排列，因此不会执行文件排序。

当然，附加索引需要的磁盘空间几乎与表一样多。

---

¹:文件排序并不总是不好的，并不意味着文件保存在磁盘上。如果数据量较小，则在内存中执行。