使用复合索引优化 MySQL 查询

Question

>我有一个表，目前大约有 8000 万行，创建如下：

create table records
(
id      int auto_increment primary key,
created int             not null,
status  int default '0' not null
)
collate = utf8_unicode_ci;
create index created_and_status_idx
on records (created, status);

创建的列包含 unix 时间戳，状态可以是介于 -10 和 10 之间的整数。记录在创建日期方面均匀分布，其中大约一半的记录状态为 0 或 -10。

我有一个 cron 可以选择 32 到 8 天之间的记录，处理它们，然后删除它们，以获得某些状态。查询如下：

SELECT
records.id
FROM records
WHERE
(records.status = 0 OR records.status = -10)
AND records.created BETWEEN UNIX_TIMESTAMP() - 32 * 86400 AND UNIX_TIMESTAMP() - 8 * 86400
LIMIT 500

当记录位于创建间隔开始时，查询速度很快，但现在清理在间隔结束时到达记录，运行大约需要 10 秒。解释查询说它使用索引，但它解析了大约 4000 万条记录。

我的问题是，我是否可以做些什么来提高查询的性能，如果是，究竟如何提高。

谢谢。

Answer 1

我认为union all是你最好的方法：

(SELECT r.id
FROM records r
WHERE r.status = 0 AND
r.created BETWEEN UNIX_TIMESTAMP() - 32 * 86400 AND UNIX_TIMESTAMP() - 8 * 86400
LIMIT 500
) UNION ALL
(SELECT r.id
FROM records r
WHERE r.status = -10 AND
r.created BETWEEN UNIX_TIMESTAMP() - 32 * 86400 AND UNIX_TIMESTAMP() - 8 * 86400
LIMIT 500
) 
LIMIT 500;

这可以使用records(status, created, id)上的索引。 注意：如果records.id可能有重复项，请使用union。

您也在使用没有ORDER BYLIMIT。 这通常是不鼓励的。

Answer 2

索引顺序错误。 您应该将IN列(status(放在第一位(您将其表述为OR(，然后将"范围"列(created(放在最后：

INDEX(status, created)

(不要给我任何关于"基数"的唠叨;我们不是在看个别列。

表中真的只有 3 列吗？ 你需要id吗？ 如果没有，请删除它并更改为

PRIMARY KEY(status, created)

其他有效穿过大桌子的技巧。