mysql - 如何在这个简单的查询中避免文件排序？ (没有加入)

Question

我做了一个数据点表 - 它们有一个键(数据类型)、值(数据值)、时间戳(记录数据的时间)。

表定义:

CREATE TABLE IF NOT EXISTS datapoints (
  point_id int(11) NOT NULL AUTO_INCREMENT,
  point_user_id int(11) NOT NULL,
  point_key varchar(32) NOT NULL,
  point_value longtext NOT NULL,
  point_timestamp int(11) NOT NULL,
  PRIMARY KEY (point_id),
  KEY datapoint_search (point_key,point_timestamp,point_user_id,point_value(64))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

现在我进行查询以获取某个日期范围(在我们的示例中为 1 天)内特定类型的所有数据点，并按数据点值降序对其进行排序:

EXPLAIN SELECT * FROM datapoints
WHERE point_key = 'body_temperature'
      AND point_timestamp >= UNIX_TIMESTAMP('2013-11-20')
      AND point_timestamp < UNIX_TIMESTAMP('2013-11-21')
      AND point_user_id = 1
ORDER BY point_value DESC;

不幸的是，这会在 EXPLAIN 中产生一个文件排序:

id | select_type | table      | type  | possible_keys    | key              | key_len | ref  | rows   | Extra                       
 1 | SIMPLE      | datapoints | range | datapoint_search | datapoint_search | 106     | NULL | 175642 | Using where; Using filesort

是否可以避免这种文件排序？我已经创建了一个索引 (datapoint_search)，并使用了它，但仍会调用文件排序。

附言。 point_value 列必须是文本或长文本，或者至少可以处理非常大的数据(最多 8KB)，同时仍可排序。

Answer 1

以下索引满足您的 where 子句:

datapoints(point_key, point_user_id, point_timestamp);

这可能会显着提高查询的性能，但不会删除文件排序。

从理论上讲，以下内容可以:

datapoints(point_key, point_user_id, point_value, point_timestamp);

但是，我认为 MySQL 不够聪明，无法匹配 where 子句和 order by 的一部分，而其余的过滤在排序之后完成。值得一试。

以下将不起作用:

datapoints(point_key, point_user_id, point_timestamp, point_value);

数据将按时间戳顺序检索以满足 where 子句。 point_value 的排序次于时间戳。

编辑:

如果 where 找到的行数是“常数”，那么性能应该是相似的。如果您没有太多匹配 point_key、point_user_id，那么以下技巧可能会有所帮助:

select dp.*
from (SELECT *
      FROM datapoints
      WHERE point_key = 'body_temperature' AND point_user_id = 1
      ORDER BY point_value DESC
     ) dp
where point_timestamp >= UNIX_TIMESTAMP('2013-11-20') AND
      point_timestamp < UNIX_TIMESTAMP('2013-11-21');

连同索引 datapoints(point_key, point_user_id, point_value)。

不幸的是，MySQL 不保证内部子查询中的排序实际上为外部查询保持行的顺序(我认为它在实践中确实如此，至少通常如此)。这将为内部查询使用索引，然后为第二个 where 子句扫描临时表。

此外，如果您不需要所有列，那么我建议将您想要的列放入索引中。这将在匹配时保存全表的随机扫描。