为什么这个简单的查询在 MySQL 8.0.25 这么慢？

[yiplee]

有一张简单的表 t ，总共 11 个字段 ，关键字段如下：

1. id int64 自增主键
2. a char(36) NOT NULL
3. b char(36) NOT NULL
4. c char(36) NULL

除主键索引之外，还有两个索引：

1. UNIQUE INDEX (a)
2. INDEX (b)

现在有一个简单的查询

SELECT * FROM t WHERE b = ? AND c IS NOT NULL AND id > ? LIMIT 32 ORDER BY id ；

在表有大几千万条数据，并且 WHERE 匹配到的行数很多的时候，同样的数据在 MySQL 5.7.x 版本查询非常快，1s 以内，但是在另外一台 MySQL 8.0.25 上就要二十几秒，可能是什么原因呢？

[sivacohan]

看一下数据库配置，你可能是 buffer size 不足，导致 order by 之前那个临时表落磁盘了。
比较一下之前用 5.7 的 buffer size

[Egfly]

可能的原因还是有很多的：mysql 8.0 的设置问题（比如 buffer size 的大小）？ 服务器资源大小的区别？第一次执行从磁盘读取到内存？
在去除上面这些因素外猜测一下：可能是 c is not null 和 order by id 的问题

原因：index(b) 命中行数太多，c is not null 这个条件导致需要多回表一次。然后从 sql 的执行顺序来看 order by id 是执行在 limit 32 前面的

OP 可以分别试一下去掉 c is not null 、order by id 、将 select * 改为 select id, b 去试试。

OP 顺便可以发一下在 mysql 5.7 下的 explain

[bthulu]

降级到 mysql8.0 就行了, 你还真以为 oracle 接手后的 8.0 会正向优化? 那他的 oracle 还怎么卖

[HunterPan]

c is not null 要回表的吧 去掉试试

[wyx119911]

因为你最后用了 ORDER BY id ，导致必须走联合索引才能命中 id 的索引排序，且排序字段要为联合索引最后一个。
预期走的联合索引是 b,c,id 。但是你只有 b 索引可走，导致 b 过滤完后剩下要扫表。
所以需要加一个 b,c 索引，因为 id 为主键会自动加入联合索引中，刚好生成 b,c,id 索引满足查询条件。

[iseki]

@yiplee 可是看 explain 如果我没理解错它走了另一个索引（不太熟悉 MySQL 的 explain 输出

[akira]

如果数据分布有规律的话，能否给出 id 的上限，可以的话可以考虑加个 id < ?

[v2orz]

@wyx119911
“主键会自动加入联合索引中”
还有这个？学到了，感谢

我去找一下资料看看

[joApioVVx4M4X6Rf]

好帖子，又学到很多东西

[hoopan]

盲猜，数据库或服务器配置问题。数据一样，sql 一样，不同的只有数据库版本、服务器了。

[winglight2016]

没有“自动”二级索引这种事情，只是 mysql 的索引树有两个，默认是 id 索引 b tree+, 其他索引是先索引到 id 再走 id 索引获取最终记录

可以试试把 id 条件放在前面，两台机器查询速度不一样，可能是 sql engine 优化器设置不同，另一台机器也执行以下 explain 对比一下就知道了

[dV9zZM1wROuBT16X]

盲猜两个可能原因
1. innodb_buffer_pool_size 这个配置起决定性作用
2. query_cache 8.0 已经删了

[wmwmajie]

调整一下你 where 条件的顺序试试，可能是优化器走的索引规则不一样。

[r4aAi04Uk2gYWU89]

坐等查验结果

[fgd]

怀疑是排序过程的性能损耗，可能有很多磁盘 io 。
1. 试一下去掉 order by 看下时间呢？看看 sort_buffer_size max_length_for_sort_data 这两个参数的值，两个数据库一致吗？

[coolstranger]

有结论了吗，最后是 mysql 的配置问题，还是两个表的数据问题，还是两个版本的执行策略有变化？

[yiplee]

@coolstranger #56 还没有，暂时先把用 8.x 那台的服务给停了。
对了，停掉服务之后，再手动跑了一下这个简单的查询，86 ms 就返回了 ：）

[ashmodeus]

5.7.x 版本的 explain 也发上来看下吧，估计应该是走了 id>这个过滤条件的主键索引，因为 b=?命中非常多，所以走主键更快。