MySQL在索引的TIMESTAMP列上使用filesort [英] MySQL uses filesort on indexed TIMESTAMP column

问题描述

我有一个拒绝使用索引的表，它总是使用filesort。

I've got a table that refuses to use index, and it always uses filesort.

表是：

CREATE TABLE `article` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Category_ID` int(11) DEFAULT NULL,
  `Subcategory` int(11) DEFAULT NULL,
  `CTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Publish` tinyint(4) DEFAULT NULL,
  `Administrator_ID` int(11) DEFAULT NULL,
  `Position` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `Subcategory` (`Subcategory`,`Position`,`CTimestamp`,`Publish`),
  KEY `Category_ID` (`Category_ID`,`CTimestamp`,`Publish`),
  KEY `Position` (`Position`,`Category_ID`,`Publish`),
  KEY `CTimestamp` (`CTimestamp`),
  CONSTRAINT `article_ibfk_1` FOREIGN KEY (`Category_ID`) REFERENCES `category` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=94290 DEFAULT CHARSET=utf8

查询是：

SELECT * FROM article ORDER BY `CTimestamp`;

解释是：

+----+-------------+---------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL | 63568 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+-------+----------------+

当我删除ORDER BY时，所有这些都正常工作。所有其他索引（子类别，位置等）在其他查询中工作正常。不幸的是，即使使用我的简单选择查询，也会拒绝使用时间戳。我确定我在这里缺少一些重要的东西。

When I remove the "ORDER BY" then all are working properly. All other indices (Subcategory, Position, etc) are working fine in other queries. Unfortunately, the timestamp refuses to be used, even with my simple select query. I'm sure I'm missing something important here.

如何让MySQL使用时间戳索引？

How can I make MySQL use the timestamp index?

谢谢。

推荐答案

在这种情况下，MySQL没有使用你的索引进行排序，这是一件好事。
为什么？您的表只包含64k行，平均行宽约为26个字节（如果我添加了正确的列大小），因此磁盘上的总表大小应该在2MB左右。
从磁盘读入2MB数据到内存非常便宜（可能只需1-2次磁盘操作或搜索）然后只需在内存中执行filesort（可能是quicksort的变化）。

In this case, MySQL is not using your index for sorting, and it is a GOOD thing. Why? Your table contains just 64k rows, average row width is about 26 bytes (if I added column sizes right), so total table size on disk should be around 2MB. It is very cheap to read just 2MB of data from disk into memory (probably in just 1-2 disk operations or seeks) and then simply perform filesort in memory (probably variation of quicksort).

如果MySQL按照您的意愿按索引顺序进行检索，则必须执行64000个磁盘搜索操作，一个接一个记录！它会非常非常慢。

If MySQL did retrieval by index order as you wish, it would have to perform 64000 disk seek operations, one record after another! It would have been very, very slow.

当您可以使用它们快速跳转到大文件中的已知位置并读取少量数据时，索引可能会很好，喜欢在WHERE子句中。但是，在这种情况下，这不是一个好主意 - 而且MySQL并不愚蠢！

Indexes can be good when you can use them to quickly jump to known location in huge file and read just small amount of data, like in WHERE clause. But, in this case, it is not good idea - and MySQL is not stupid!

如果你的表非常大（超过RAM大小），那么MySQL肯定会开始使用你的索引 - 这也是好事。

If your table was very big (more than RAM size), then MySQL would certainly start using your index - and this is also good thing.

这篇关于MySQL在索引的TIMESTAMP列上使用filesort的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！