mysql - 如何使用 WHERE、RANGE 和 SORT 设置 MySql 表的索引？

Question

我有 MySql 表和查询，我正在尝试优化并有一些问题。

SELECT value FROM table WHERE userid=?userid AND date <= ?date AND deleted='False' ORDER BY date DESC LIMIT 1

表格:

CREATE TABLE `table` (
    `tableid` int(11) NOT NULL AUTO_INCREMENT,
    `userid` int(11) DEFAULT NULL,
    `value` double DEFAULT '0',
    `date` date DEFAULT NULL,
    `deleted` enum('False','True') DEFAULT 'False',
    PRIMARY KEY (`tableid`),
    KEY `userid_date` (`userid`)
) ENGINE=InnoDB;

我得到以下 EXPLAIN 查询结果:

id    select_type    table       type    possible_keys    key            key_len    ref    rows    Extra
1     SIMPLE         table       ref     userid_date      userid_date   5          const  4       Using where; Using filesort

如果我更改为在 userid_date 键中也使用日期 (KEY userid_date (userid,date)) 我会得到以下 EXPLAIN 结果:

id    select_type    table    type    possible_keys    key            key_len    ref    rows    Extra
1     SIMPLE         table    range   userid_date      userid_date  9          NULL   4       Using where

这更好，因为它没有使用文件排序，但看起来类型不如仅使用 userid 作为键时好。您将如何为表设置索引并进行这样的查询？使用日期类型作为索引好吗？

Answer 1

This is better as it is not using filesort

是的，因为索引中还有一个可用的过滤器。

您还可以考虑添加已删除的 (userid, deleted, date)。但是，请确保相等比较在您用于范围和排序子句的列之前进行——否则您的排序将返回。

but it seems like the type is not as good as when only using userid

为什么？我认为你的索引会足够好。 deleted 列可能没有值(value)，而且可能只是浪费空间。但这取决于其他查询是否也使用该列以及“错误”率在您的数据库中的情况。