为什么不使用键的最左侧子集来优化此ORDER BY? [英] Why is the leftmost subset of a key not being used to optimise this ORDER BY?

问题描述

服务器版本:

[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

根据执行计划(和观察)，未使用索引:

According to the execution plan (and observation), the index is not being used:

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操作.

I've tried re-organising it a few ways with little success but, more than anything, would like to understand why this simple approach is failing. My understanding was that a left-most subset of any key can be used to optimise an ORDER BY operation.

我的索引错误吗?我可以优化查询吗?

Is my index wrong? Can I optimise the query?

请注意，我也想有条件地添加，例如

Please note that I will also want to conditionally add, e.g.

WHERE `Severity` <= 'LOG_LEVEL_WARNING'

尽管我想让基本版本首先运行，如果这会使解决方案大为不同.

though I'd like to get the basic version working first if this makes the solution very different.

转载于MySQL 5.5.32下的SQLFiddle.

推荐答案

原因是索引在其中包括了主键.并且由于它是InnoDB，因此默认情况下，所有其他索引中的PK都作为最左侧的字段包含在其中.即在这种情况下，索引为(EntryId，EntryTime，EntryId).

The reason is that you index includes the primary key in it. and since it is InnoDB, by default the PK is ancluded in all other indexes as the left-most field. i.e. the index in this case is (EntryId, EntryTime, EntryId).

解决方案是仅在(EntryTime)上具有此索引:

The solution is to have this index only on (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  |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------+

HTH

这篇关于为什么不使用键的最左侧子集来优化此ORDER BY?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！