提问人：Unnikrishnan 提问时间：9/28/2023 最后编辑：zealousUnnikrishnan 更新时间：11/16/2023 访问量：89
优化“ARCHIVED”状态选择的 MySQL 查询性能
Optimizing MySQL Query Performance for 'ARCHIVED' Status Selection
问：
我有一个名为“article”的表，其中包含 1,145,141 条记录，其中包含各种字段，包括
"id," "uiqPID," "tenant," "status," "title," "body," "user_id," "category_id," "created_at," and "updated_at."
“status”列可以包含以下三个值之一：“PUBLISHED”、“DRAFT”或“ARCHIVED”，计数如下：
- 发布时间： 2
- 吃水：26,145
- 存档： 1,118,993
我设置了以下索引：
- 'id' 上的 PRIMARY 索引
- article_abstract_unq_id 'uiqPID' 上的索引
- article_abstract_unq_id “tenant”索引
- article_status_idx “status”索引
- idx_composite_search 'id' 上的索引
- idx_composite_search“uiqPID”上的索引
- idx_composite_search 'created_at' 索引
我的问题是以下查询的性能，执行该查询需要 5.7 秒：
SELECT
a.id AS id,
a.created_at AS created_at
FROM
article a
WHERE
a.status = 'ARCHIVED'
ORDER BY a.created_at DESC
LIMIT 50;
但是，如果我删除 WHERE 条件或将其更改为 a.status = 'DRAFT'，则查询将在 1 秒内完成。
在检查查询计划时，我注意到执行策略的差异。使用“已存档”或“草稿”状态筛选器时，计划会显示：
key: article_status_idx
Extra: Using index condition; Using filesort
但是如果没有“存档”过滤器，该计划只是声明：
key:
Extra: Using filesort
我的问题是：如何优化查询性能以过滤“已存档”状态，确保其执行速度快于当前的 5.7 秒，类似于没有此条件或具有“草稿”状态的查询？
答：
0赞
Lajos Arpad
9/28/2023
#1
你所经历的行为实际上是我所期望的。将 1,118,993 个数字时间戳元组写入内存需要时间，尤其是在按 对结果进行排序时。您需要测试以下内容：created_at
SELECT
a.id AS id,
a.created_at AS created_at
FROM
article a
ORDER BY a.created_at DESC
LIMIT 0, 1118993;
如果执行此查询的时间与使用 where 子句执行查询的时间大致相同，则导致性能下降的不是 where 子句，而是对查询进行排序 + 将结果加载到内存中。同时运行以下命令：
SELECT
a.id AS id,
a.created_at AS created_at
FROM
article a
LIMIT 0, 1118993;
在这里，我们甚至没有订单。如果这同样很慢，那么你主要等待的就是把这些东西写到内存中。
无论如何，进行这些测量，结果将确认速度缓慢是由 where 或 order by 子句引起的，或者不是。如果这些条款导致速度变慢，请在评论部分告诉我，我将提供优化该问题的方法。但真正的问题很可能是您正在等待执行完整的查询。
也许最好将查询分解为多个分区并运行限制为 0、10000 的查询，然后限制 10000、10000 等，这样您就可以在等待其他结果的同时处理第一个结果，也许可以减少不耐烦的用户的挫败感。但这并不能改变加载所有这些东西需要时间的事实，即使如果显示部分结果有一些有用的意义，你可以使这种等待对用户更友好。
编辑
您可以尝试在创建复制表之前创建一个 （status， created_at） 键，也许这种索引对您的性能很有用，其想法是为每个状态类型设置一个索引顺序created_at。
如果所有其他方法都失败了，您可以创建一个如下所示的表：
create table article_archive(
id int primary key,
created_at timestamp
);
insert into article_archive(id, created_at)
select id, created_at
from article
where `status` = 'ARCHIVE';
ALTER TABLE article_archive ADD INDEX (created_at DESC);
然后你可以从，比如：article_archive
select id, created_at
from article_archive
order by created_at desc
limit 50;
您甚至可以将其加入 by .articleid
评论
1赞
Lajos Arpad
9/29/2023
@Unnikrishnan尝试过创建（状态、created_at）索引？
1赞
Lajos Arpad
9/29/2023
@Unnikrishnan我希望这会成功。但如果没有，那么您可以只创建存档的副本。但如果你这样做，你将需要维护它，也许是通过每小时的 cron 工作。
1赞
Bill Karwin
9/29/2023
FWIW，MySQL无法理解带有千位分隔符的整数。将整数写成 1118993，而不是 1,118,993。
1赞
Lajos Arpad
9/29/2023
@BillKarwin感谢您的建设性批评。你是绝对正确的，当然，这只是一个数字粘贴，我没有多看两眼。感谢您指出！
1赞
Rick James
9/29/2023
'（status， created_at）' 将起作用（即使没有），但答案直到最后才提到它。DESC
0赞
Amin Zayeromali
9/29/2023
#2
这是一种可以提高速度的替代方法，请尝试一下：
SELECT
a.id AS id,
a.created_at AS created_at
FROM (
SELECT id
FROM article
WHERE status = 'ARCHIVED'
ORDER BY created_at DESC
LIMIT 50
) AS subquery
JOIN article a ON subquery.id = a.id;
解释： 此查询利用子查询，首先根据created_at列标识最近 50 条“ARCHIVED”记录的 id 值。然后，它执行联接以从主表项目中检索其他列（id 和 created_at）。当您想要使用复杂的筛选和排序来优化查询时，此方法可能很有用。
请确保 status 列的 article_status_idx 索引以及 created_at 和 id 列的 idx_composite_search 索引得到妥善维护，以便高效执行。
评论
0赞
Unnikrishnan
9/29/2023
即使我们只选择 ID，它花费的时间也相同，第一次执行需要 5.7 秒，后续执行需要 3.2 秒。
0赞
Amin Zayeromali
9/29/2023
我认为因为你有很多存档状态表记录，所以最好不要使用这个 ORDER BY a.created_at DESC 并检查时间结果，因为你正在强制对整个记录进行排序，所以如果你想要这种模式最好对所有记录进行一次排序，或者为存档创建一个单独的排序表并使用它， 获取延迟的问题将减少。（我希望你明白我的意思，因为你也想排序，然后从中获取）。
0赞
Rick James
9/29/2023
在这种情况下，这不是一个有用的重新表述。无论如何都必须查看派生表（内部查询），因此在外部查询之前不获取它不会节省任何费用。created_at
4赞
Thorsten Kettner
9/29/2023
#3
您可以创建与查询完全匹配的复合索引：
create index idx on article (status, created_at desc);
因此，DBMS 可以转到索引中的 status = 'ARCHIVED'，读取前 50 个条目并完成任务。
https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html
评论
0赞
Rick James
9/29/2023
这是唯一具有最佳效果的答案，所以我对此投了赞成票。该索引是“覆盖”的，并且同样可以很好地处理任何值。INDEXstatus
评论
typerowstypekey: