高性能MySQL查询，从匹配的行中选择最高ID

Question

我需要一个比现在使用的查询性能更高的查询。我需要从一组匹配其他条件的行中获得具有最高id的行。现在我有这样的东西：

SELECT * FROM mytable 
WHERE (thing = 'foo' OR thing = 'bar')
AND used IS NULL
AND stime = 0
AND ~flags & 1
ORDER BY id DESC
LIMIT 1;

• id是唯一的顺序主键
• 其他列是非唯一的，但上面有索引

但是，此查询需要几秒钟才能运行。我需要接近亚秒的表现，即使是.99也很棒。

在实验中，我尝试在id列的WHERE子句中设置附加条件，例如max(id)和max(id) - 10000。对于一些代替10000的值，它的工作速度非常快，这就足够了。问题是WHERE子句中其他列中的值的分布非常不规则。最后的CCD_ 9行可以包含也可以不包含与其他条件匹配的任何行。如果可能的话，我需要始终找到至少一行。

是否有一些小的SQL语句集(换句话说，多个快速查询就可以了(可以以高性能的方式获得最后一行(最高id(匹配行？

Answer 1

如果您确实需要性能，最好的方法是在虚拟生成列上创建第二个索引：

alter table mytable add column my_conditions int generated always as
(thing in ('foo', 'bar') AND used IS NULL AND stime = 0 AND (~flags & 1) );
create index idx_mytable_my_conditions on (my_conditions, id desc)

然后您可以将该表查询为：

SELECT *
FROM mytable 
WHERE my_conditions = 1
ORDER BY id DESC
LIMIT 1;

它应该同时使用索引进行筛选和排序。

Answer 2

您可以做一些更改来提高查询的性能。我尝试的第一个方法是删除OR运算符并添加索引。

移除OR运算符

使用UNION重新表述您的查询。例如：

select * from (
select *
from mytable 
where thing = 'foo' and used is null and stime = 0 and ~flags & 1
order by id desc
limit 1
) z
union all
select * from (
select *
from mytable
where thing = 'bar' and used is null and stime = 0 and ~flags & 1
order by id desc
limit 1
) y 
order by id desc
limit 1;

添加索引

以下索引可以帮助优化器：

create index ix1 on mytable (thing, stime, used, flags, id);

可以应用进一步的优化，但您可以开始尝试。

Answer 3

更好的索引会有所帮助，但不能完全超过ORDER BY和LIMIT:

INDEX(stime, used, thing, flags)

"上面有索引"——我想你的意思是每列都有一个单独的索引。当你有AND时，最好有一个"；复合物"；指数从=测试开始(注意：IS NULL计数为=，然后执行IN(OR变为(。

有关创建索引的详细信息：http://mysql.rjweb.org/doc.php/index_cookbook_mysql