MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html 有一张简单的表 t ，总共 11 个字段 ，关键字段如下：
除主键索引之外，还有两个索引：
现在有一个简单的查询
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 上就要二十几秒，可能是什么原因呢？
explain 的结果
[ { "id": 1, "select_type": "SIMPLE", "table": "t", "partitions": null, "type": "range", "possible_keys": "b", "key": "b", "key_len": 152, "ref": null, "rows": 1519240, "filtered": 10, "Extra": "Using index condition; Using where" } ]  | 1 thinkershare Sep 15, 2022 where 匹配非常多, 优化器可能不走索引,本来就慢, 你这个做了读写分离没有? 插入很多时候会严重影响读取的性能. |
 | 2 wanguorui123 Sep 15, 2022 用性能分析命令看看 |
 | 3 liuxu Sep 15, 2022 |
 | 4 yiplee OP |
 | 5 xuanbg Sep 15, 2022 看下执行计划，讲道理这个查询是走索引的的，因为 b 列有索引。 |
 | 8 circle33 Sep 15, 2022 两台机器还有啥不一样吗？ |
 | 9 optional Sep 15, 2022 via iPhone  2 有 order by id 和>id 走的应该是主键索引，或者直接全表了。 |
 | 11 sulinwork Sep 15, 2022 explan 看看 |
| 13 circle33 Sep 15, 2022 1 会不会慢的那台机器 `c IS NOT NULL` 的数据太多了 |
 | 14 LeegoYih Sep 15, 2022 试试用 force index 或者 order by (id+0) |
 | 15 9y7cz863P00C7Lie Sep 15, 2022 在两个版本的 MySQL 中 explain 语句，看下有没有什么不同 |
| 16 yiplee OP @circle33 #13 这张表 ```c IS NOT NULL``` 只有极少的行不满足，所以就没在 c 上加索引；两个数据库数据都是一样的。 |
| 17 circle33 Sep 15, 2022 explain 的 type 是什么 |
 | 18 rrfeng Sep 15, 2022 explain 打出来啊，看看走了哪个索引。 比如走了 b 还扫描了非常多行，说明 b 的值不够分散，加索引也没用。 |
 | 19 wolfie Sep 15, 2022 建联合索引，或者 force index(b) |
 | 20 djoiwhud Sep 15, 2022 via Android 有点好奇，你的 sql 可以执行？ select * from table where order by limit n |
| 21 thinkershare Sep 15, 2022 @yiplee 具体的还是要走分析器看看. 另外确定一点, 你的插入是否高频? 你的插入是否会导致大规模索引重建? 另外, 你确认 2 个表的存储引擎是一致的吗? 我在上千万的的 MySQL 上执行复杂查询, 就会比较慢, 感觉几百万-2000W 基本上查询还好, 后面就会越来越慢. 另外 2 台机器的磁盘 I/O 性能一样吗? 影响数据库性能的因素实在太多了. |
| 22 yiplee OP @wanguorui123 #2 @xuanbg #5 @sulinwork #11 @circle33 #17 @rrfeng #18 因为 8.x 那台控制权不在我手里，刚拿到 explain 的结果 ```json [ { "id": 1, "select_type": "SIMPLE", "table": "outputs", "partitions": null, "type": "range", "possible_keys": "b", "key": "b", "key_len": 152, "ref": null, "rows": 1519240, "filtered": 10, "Extra": "Using index condition; Using where" } ] ``` |
| 24 yiplee OP @thinkershare #21 高频插入导致读很慢这个是有可能的，我之前没考虑到。打算从业务层面规避下，处理进度落后到一定值就先不要插入新数据了。 |
 | 25 pengtdyd Sep 15, 2022 有没有考虑过是硬件的问题 |
 | 26 iseki Sep 15, 2022 b=?命中的数据太多？ |
 | 28 1018ji Sep 15, 2022 我感觉走 a 更快 |
 | 30 zznext Sep 15, 2022 先锁表试试； |
 | 31 YIsion Sep 15, 2022 盲猜 id> ? 这个条件的问题。刚优化了一个类似的，我们业务系统的加这个条件虽然走索引，但会扫 40w 条数据。不加这个条件扫描 2w 条数据 |
 | 32 justfindu Sep 15, 2022 试试去掉 order by, 也可以试试 select * from idin(select id.....order by id limit 32) |
 | 33 yangxx Sep 15, 2022 有一种可能，5.7 走的是主键索引，条件里还有一个 id>?，这个值在 b 列过滤出来的行可能比较靠后。b 列过滤出来 100 多万行，数据量不少 |
| 36 yiplee OP @yangxx #33 我了解到 MySQL 的二级索引在尾部是包含主键的，也就是 INDEX( b ) = INDEX( b ,id )，b = ? AND id > ? 应该都用到的索引才对吧！？ |
 | 37 5boy Sep 15, 2022 force index (b),数据库执行时可能会用错索引 |
| 38 rrfeng Sep 15, 2022 "rows": 1519240, 然后需要按 id 重新排序 主要原因还是 b 命中太多了 次要原因是需要 id 排序，在 b 索引下 id 有序性无法保证，除非你创建 b+id 联合索引 |
| 39 yiplee OP @rrfeng #38 突然意识到我对 innodb 的二级索引理解错了，我一直以为 INDEX( b ) = INDEX( b ,id )，实际上 id 是保存在 INDEX( b ) 的叶子节点上值，并不是有序的，所以无法用于 ORDER BY ，不知道这次理解对没 |
 | 40 buster Sep 15, 2022 看了各位大佬的分析，我觉得应该跟 C isnotnull 有关，这里可以试一下在 8 的版本下，把这个条件去掉看下执行速度的变化。 |
 | 41 sivacohan PRO 看一下数据库配置，你可能是 buffer size 不足，导致 order by 之前那个临时表落磁盘了。 比较一下之前用 5.7 的 buffer size |
 | 42 Egfly Sep 15, 2022 可能的原因还是有很多的：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 |
 | 43 bthulu Sep 15, 2022 降级到 mysql8.0 就行了, 你还真以为 oracle 接手后的 8.0 会正向优化? 那他的 oracle 还怎么卖 |
 | 44 HunterPan Sep 15, 2022 c is not null 要回表的吧 去掉试试 |
 | 45 wyx119911 Sep 15, 2022 1 因为你最后用了 ORDER BY id ，导致必须走联合索引才能命中 id 的索引排序，且排序字段要为联合索引最后一个。 预期走的联合索引是 b,c,id 。但是你只有 b 索引可走，导致 b 过滤完后剩下要扫表。 所以需要加一个 b,c 索引，因为 id 为主键会自动加入联合索引中，刚好生成 b,c,id 索引满足查询条件。 |
 | 47 akira Sep 16, 2022 如果数据分布有规律的话，能否给出 id 的上限，可以的话可以考虑加个 id < ? |
 | 49 joApioVVx4M4X6Rf Sep 16, 2022 好帖子，又学到很多东西 |
 | 50 hoopan Sep 16, 2022 盲猜，数据库或服务器配置问题。数据一样，sql 一样，不同的只有数据库版本、服务器了。 |
 | 51 winglight2016 Sep 16, 2022 没有“自动”二级索引这种事情，只是 mysql 的索引树有两个，默认是 id 索引 b tree+, 其他索引是先索引到 id 再走 id 索引获取最终记录 可以试试把 id 条件放在前面，两台机器查询速度不一样，可能是 sql engine 优化器设置不同，另一台机器也执行以下 explain 对比一下就知道了 |
 | 52 dV9zZM1wROuBT16X Sep 16, 2022 盲猜两个可能原因 1. innodb_buffer_pool_size 这个配置起决定性作用 2. query_cache 8.0 已经删了 |
 | 53 wmwmajie Sep 16, 2022 调整一下你 where 条件的顺序试试，可能是优化器走的索引规则不一样。 |
 | 54 r4aAi04Uk2gYWU89 Sep 16, 2022 坐等查验结果 |
 | 55 fgd Sep 16, 2022 怀疑是排序过程的性能损耗，可能有很多磁盘 io 。 1. 试一下去掉 order by 看下时间呢？看看 sort_buffer_size max_length_for_sort_data 这两个参数的值，两个数据库一致吗？ |
 | 56 coolstranger Sep 19, 2022 有结论了吗，最后是 mysql 的配置问题，还是两个表的数据问题，还是两个版本的执行策略有变化？ |
| 57 yiplee OP @coolstranger #56 还没有，暂时先把用 8.x 那台的服务给停了。 对了，停掉服务之后，再手动跑了一下这个简单的查询，86 ms 就返回了 ：） |
 | 58 ashmodeus Sep 21, 2022 5.7.x 版本的 explain 也发上来看下吧，估计应该是走了 id>这个过滤条件的主键索引，因为 b=?命中非常多，所以走主键更快。 |
Select Language