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

CXJ • 3 年前 • 1300 次点击

我需要一个比我现在使用的更高的性能查询。我需要得到最高的分数 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;

身份证件 是唯一的顺序主键。

但是,此查询需要很多秒才能运行。我需要接近亚秒级的性能,即使是0.99也很好。

WHERE 关于 身份证件 列,例如 max(id) 和 max(id) - 10000 .用于替代 10000 它运行得非常快,这就足够了。问题是这些值在世界其他地方的分布 哪里 这个从句非常不规则。最后 10000 行可以包含也可以不包含任何与其他条件匹配的行。如果可能的话,我需要找到至少一行。

是否有一些小的SQL语句集(换句话说,多个快速查询就可以了)可以让我得到最后(最高)的结果 身份证件 )以高性能的方式匹配行?

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/127960

1300 次点击

文章 [ 3 ] | 最新文章 3 年前

• 1 楼

Rick James 4 年前

一个更好的指数会有所帮助,但不能完全超过预期 ORDER BY 和 LIMIT :

INDEX(stime, used, thing, flags)

“有索引”——我想你指的是每一列的单独索引。当你有 AND ,可能最好有一个“综合”指数。从 = 测试(注: IS NULL 算作 = ,然后做 IN (哪个 OR 变成了)。

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

• 2 楼

The Impaler 4 年前

您可以做一些更改来提高查询的性能。我要尝试的第一个方法是移除 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);

还有进一步的优化可以应用,但你可以开始尝试这个。

• 3 楼

Gordon Linoff 4 年前

如果你真的需要性能,最好是在虚拟生成的列上建立第二个索引:

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;

它应该使用索引进行过滤和排序。

登录后回复