服务器版本:
[root@cat best]# /usr/libexec/mysqld --version
/usr/libexec/mysqld Ver 5.1.47 for redhat-linux-gnu on i386 (Source distribution)
架构:
CREATE TABLE `Log` (
`EntryId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`EntryTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
`Severity` ENUM(
'LOG_LEVEL_CRITICAL',
'LOG_LEVEL_ERROR',
'LOG_LEVEL_WARNING',
'LOG_LEVEL_NOTICE',
'LOG_LEVEL_INFO',
'LOG_LEVEL_DEBUG'
) NOT NULL,
`User` TEXT,
`Text` TEXT NOT NULL,
PRIMARY KEY(`EntryId`),
KEY `TimeId` (`EntryTime`,`EntryId`)
) ENGINE=InnoDB COMMENT="Log of server activity";
查询:
SELECT
`EntryId`,
`EntryTime`, -- or, ideally: UNIX_TIMESTAMP(`EntryTime`) AS `EntryTime_UnixTS`
`Severity`,
`User`,
`Text`
FROM `Log`
ORDER BY `EntryTime` DESC, `EntryId` DESC
LIMIT 0, 20
根据执行计划(和观察),不使用索引:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Log ALL \N \N \N \N 720 Using filesort
我尝试过用一些方法重新组织它,但收效甚微,但最重要的是,我想了解为什么这种简单的方法会失败。我的理解是,任何键的最左边的子集都可以用来优化
ORDER BY
操作。我的索引错了吗?我可以优化查询吗?
请注意,我还想附加条件,例如。
WHERE `Severity` <= 'LOG_LEVEL_WARNING'
不过,如果这使解决方案非常不同的话,我想先让基本版本工作。
Reproduced on SQLFiddle under MySQL 5.5.32.
最佳答案
原因是索引中包含主键。由于它是InnoDB,默认情况下,PK作为最左边的字段被排除在所有其他索引中。即本例中的索引是(EntryId,EntryTime,EntryId)。
解决方案是仅启用此索引(EntryTime):
alter table Log drop index TimeId;
alter table Log add index TimeId(EntryTime);
explain SELECT `EntryId`, `EntryTime`, `Severity`, `User`, `Text` FROM `Log` ORDER BY `EntryTime` DESC, `EntryId` DESC LIMIT 0, 20;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------+
| 1 | SIMPLE | Log | index | NULL | TimeId | 4 | NULL | 20 | NULL |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------+
高温高压