按原因排序 文件排序

Question

我仍然有以下问题

EXPLAIN EXTENDED SELECT
  `item`.`id`,
  `item`.`timestamp`,
  `item`.`label`
  FROM
  item
WHERE
  item.dataTypeId=30
GROUP BY
  item.id
ORDER BY
  item.timestamp DESC
LIMIT 0,6;

Id & timestamp是主键对（mediumint+datetime） dataTypeId 是外键 (mediumint) 表创建为 InnoDb

可以有更多具有相同 id 和不同时间戳的记录（同一项目的版本）。这就是分组依据的原因。

例如，我读过这个：stackoverflow上的类似主题

但这并没有解决我的问题。

我尝试创建以下索引：

1. (dataTypeId, id, timestamp) 上的索引 - 按照
2. (dataTypeId, timestamp) 上的索引 - 按照
3. id 上的
4. 索引 时间戳

有点绝望

上的索引 最后两个我认为 我一定想念一些基本的东西 -
但实在不知道是什么。
不要指望解决方案（这会很好:)只要以正确的方式踢我:)

sort_buffer_size 现在是 4194288

编辑： 解释 - 没有索引

"1" "SIMPLE"    "item"  "ref"   "FK_dataTypeId" "FK_dataTypeId" "4" "const" "5608"  "Using where; Using temporary; Using filesort"

解释创建的索引

"1" "SIMPLE"    "item"  "ref"   "FK_udssDataItem_1,testIndexType,testIndexTypeTimestamp,testIndexTypeIdTime"    "FK_udssDataItem_1" "4" "const" "5632"  "Using where; Using temporary; Using filesort"

Answer 1

您的查询有问题。当您执行“按 id 分组”时，同一 id 可能有不同的时间戳，并且未指定使用哪一个（Min()、max() 等），“标签”字段也会出现类似的问题。

http://dev.mysql.com/tech-resources /articles/debunking-group-by-myths.html

因此，您需要对时间戳和标签进行聚合函数，否则返回的值可能是不可预测的。

当您按 id 分组并按时间戳排序时，MySQL 会为每组提取一个时间戳，因此索引并没有多大帮助。您可能无法使用此查询摆脱文件排序。

Answer 2

所以你的问题是“如何避免对查询进行文件排序”？
在这种情况下，要让 MySQL 进行索引排序，您需要在 where 子句中包含索引中的所有列。

使用 id,timestamp 主键，您还必须

where id = myid and item.timestamp between (t1,t2)

注意开放范围（和 timestamp < now() ）

我不确定 datatypeID 是什么，但如果这是您唯一的条件，则仅在该列上添加索引还应该建议索引排序。但您可能必须按照 (timestamp,datatypeID) ...按该顺序创建索引...。