mysql - MySql ORDER BY 连接慢，2 个查询快

Question

当我提供内联值时，为什么以下查询慢而快？

select u.* from user u
join user_group g on u.group_id = g.id
where g.account_id = 1
order by u.id limit 10;
-- takes ~30ms

select id from user_group where account_id = 1;
-- which is (99,198,297,396,495,594,693,792,891,990)

select * from user
where group_id in (99,198,297,396,495,594,693,792,891,990)
order by id limit 10;
-- takes ~1ms

子查询也很慢。计划与加入相同。

select u.* from user u
where u.group_id in (select id from user_group where account_id = 1)
order by u.id limit 10;
-- ~30ms

所有查询都产生相同的结果。

98  0   99
197 0   198
296 0   297
395 0   396
494 0   495
593 0   594
692 0   693
791 0   792
890 0   891
989 0   990

架构

我有以下简单的表结构，有 100 个帐户 1k user_groups 和 1000 万用户。

create table account(
  id int primary key auto_increment
);

create table user_group(
  id int primary key auto_increment,
  account_id int not null,
  foreign key (account_id) references account(id)
);

create table user(
  id int primary key auto_increment,  
  deleted tinyint default 0,
  group_id int not null,
  foreign key (group_id) references user_group(id)
);

-- I've been trying with this index, but it doesnt seem to help.
create index user_1 on user(group_id, id, deleted);

计划

使用索引进行连接并进行临时文件排序的计划。

我不明白为什么 MySql 似乎认为它实际上需要执行完整的连接来过滤数据。我们显然没有从user_group.

为了比较，我在 PostgreSQL 中尝试了同样的事情，并且两个查询都运行得很快。

为什么它很慢，有没有办法编写快速完成的查询（单个查询！）？子选择不起作用。

这个 dbfiddle 显示了问题。 https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8ed68310d8ca72e9daef389dc0469a6f

使用 MySQL 8.0.23

谢谢

编辑

以下是完整的会话状态处理程序调试详细信息。看起来慢速查询正在读取与快速查询不同的每个值。

-- FLUSH STATUS;
-- select u.* from user u join user_group g on u.group_id = g.id where g.account_id = 1 order by u.id limit 10;
-- SHOW SESSION STATUS LIKE 'Handler%';

Handler_commit  1
Handler_delete  0
Handler_discover    0
Handler_external_lock   4
Handler_mrr_init    0
Handler_prepare 0
Handler_read_first  0
Handler_read_key    11
Handler_read_last   0
Handler_read_next   100110
Handler_read_prev   0
Handler_read_rnd    0
Handler_read_rnd_next   0
Handler_rollback    0
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  0
Handler_write   0

-- FLUSH STATUS;
-- set @uGroups := (select group_concat(id) from user_group where account_id = 1 group by account_id);
-- select * from user where group_id in (select @uGroups) order by id limit 10;
-- SHOW SESSION STATUS LIKE 'Handler%';

Handler_commit  2
Handler_delete  0
Handler_discover    0
Handler_external_lock   4
Handler_mrr_init    0
Handler_prepare 0
Handler_read_first  0
Handler_read_key    2
Handler_read_last   0
Handler_read_next   19
Handler_read_prev   0
Handler_read_rnd    0
Handler_read_rnd_next   0
Handler_rollback    0
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  0
Handler_write   0

标签： mysqlsql-order-byquery-optimization

Answer 1

使用 straight_join 可以获得我预期的性能。

select straight_join u.* from user u
join user_group g on u.group_id = g.id
where g.account_id = 1
order by u.id limit 10;

谢谢秋名https://dba.stackexchange.com/a/289710/227119