我正在尝试提高以下查询的性能,该查询需要 1 分钟以上的时间才能执行:
SELECT *
FROM test
WHERE ( created_at < '2023-3-31 06:10:20.871' )
AND ( ( id > '2a95048f' )
OR ( id = '2a95048f'
AND created_at > '2022-12-27 23:53:24.958' ) )
ORDER BY id ASC,
created_at ASC
LIMIT 1000;
我通过切换 ORDER BY 中的顺序来更改查询,查询在 549 毫秒内返回结果:
SELECT *
FROM test
WHERE ( created_at < '2023-3-31 06:10:20.871' )
AND ( ( id > '2a95048f' )
OR ( id = '2a95048f'
AND created_at > '2022-12-27 23:53:24.958' ) )
ORDER BY created_at ASC,
id ASC
LIMIT 1000;
以下是有关索引的一些信息:
- ID - 基数 680 万,唯一
- CREATED_AT - 基数 710 万,不是唯一的
为什么一个比另一个性能显着?
更新 - “显示来自测试的索引”
为了获得更准确的答案,您应该在表上发布索引定义以及两个查询的查询计划。
但是,如果我不得不猜测,我会说性能差异可能是由于 ORDER BY 和 LIMIT 的组合造成的。
您可能在 (created_at, id) 上有索引,但在 (id, created_at) 上没有索引
如果你有一个索引以相同的顺序覆盖 order by columns,mysql 可以使用索引搜索表,获取所需顺序的行,并在找到 1000 行满足 where 条件后停止。
如果没有这样的索引,mysql 必须选择所有满足 where 条件的行(可能有几千行),然后对它们进行排序,只有在排序之后它才能保留前 1000 行并丢弃其余的。
正如 Andrea 提到的,请将您的索引定义添加到您的帖子中,以解决与性能相关的问题。
但是假设 Andrea 是正确的,并且您的索引是在上面定义的,那么
(created_at, id)
回答您的问题“为什么 ORDER BY 中索引列的顺序会影响性能? ”是因为指定列的顺序是数据排序的顺序- 如果这些排序在您的子句和索引定义之间不匹配ORDER BY
,则索引不能用于消除查询的排序操作。通过在 上定义索引
(created_at, id)
,您告诉索引首先对数据进行排序created_at
,然后在原始排序中对数据进行第二次排序。id
因此,对于索引定义,可以将其视为电话簿。电话簿通常
LastName
先排序,再FirstName
排序。如果您的ORDER BY
子句想要在 之前对该电话簿中的数据进行排序,则LastName, FirstName
无需执行任何工作,因为电话簿已经按照这种方式预先排序(即索引已经按照这种方式存储数据)。但是如果你想ORDER BY
反对FirstName, LastName
电话簿,那么整个电话簿需要重新排序才能完成(即你的索引在(LastName, FirstName)
这里不能帮助你)。