首页 注册 登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
广告
yiplee
V2EX  ›  MySQL

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

  •  3      
  •   yiplee ·
    yiplee · 2022-09-15 10:56:43 +08:00 · 7239 次点击
    这是一个创建于 798 天前的主题，其中的信息可能已经有所发展或是发生改变。

    有一张简单的表 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 上就要二十几秒，可能是什么原因呢？

    第 1 条附言  ·  2022-09-15 12:40:40 +08:00

    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"
  }
]
  • null
  • MySQL
  • char
  • 主键
    58 条回复  •  2022-09-21 11:07:38 +08:00
    thinkershare
        1
    thinkershare  
       2022-09-15 11:05:54 +08:00
    where 匹配非常多, 优化器可能不走索引,本来就慢, 你这个做了读写分离没有? 插入很多时候会严重影响读取的性能.
    wanguorui123
        2
    wanguorui123  
       2022-09-15 11:10:45 +08:00
    用性能分析命令看看
    yiplee
        4
    yiplee  
    OP
       2022-09-15 11:16:06 +08:00
    @thinkershare #1 补充一些情况：

    - 没有读写分离
    - 这张表断断续续的有 insert ，没有 update ，insert 是起事务一次批量写入几十条
    xuanbg
        5
    xuanbg  
       2022-09-15 11:16:20 +08:00
    看下执行计划，讲道理这个查询是走索引的的，因为 b 列有索引。
    yiplee
        6
    yiplee  
    OP
       2022-09-15 11:22:29 +08:00
    @liuxu #3 赞，我学习下
    yiplee
        7
    yiplee  
    OP
       2022-09-15 11:22:44 +08:00
    @xuanbg #5 我也是这么以为的 😂
    circle33
        8
    circle33  
       2022-09-15 11:24:48 +08:00
    两台机器还有啥不一样吗？
    optional
        9
    optional  
       2022-09-15 11:25:05 +08:00 via iPhone   ❤️ 2
    有 order by id 和>id 走的应该是主键索引，或者直接全表了。
    yiplee
        10
    yiplee  
    OP
       2022-09-15 11:28:06 +08:00
    @circle33 #8 配置都不低，处理这张简单的表的数据量肯定是没问题的
    sulinwork
        11
    sulinwork  
       2022-09-15 11:28:29 +08:00
    explan 看看
    yiplee
        12
    yiplee  
    OP
       2022-09-15 11:28:48 +08:00
    @optional #9 看执行时间应该是扫描了很多行
    circle33
        13
    circle33  
       2022-09-15 11:31:35 +08:00   ❤️ 1
    会不会慢的那台机器 `c IS NOT NULL` 的数据太多了
    LeegoYih
        14
    LeegoYih  
       2022-09-15 11:34:13 +08:00
    试试用 force index
    或者 order by (id+0)
    9y7cz863P00C7Lie
        15
    9y7cz863P00C7Lie  
       2022-09-15 11:35:58 +08:00
    在两个版本的 MySQL 中 explain 语句，看下有没有什么不同
    yiplee
        16
    yiplee  
    OP
       2022-09-15 11:37:00 +08:00
    @circle33 #13 这张表 ```c IS NOT NULL``` 只有极少的行不满足，所以就没在 c 上加索引；两个数据库数据都是一样的。
    circle33
        17
    circle33  
       2022-09-15 11:41:09 +08:00
    explain 的 type 是什么
    rrfeng
        18
    rrfeng  
       2022-09-15 11:44:29 +08:00
    explain 打出来啊，看看走了哪个索引。
    比如走了 b 还扫描了非常多行，说明 b 的值不够分散，加索引也没用。
    wolfie
        19
    wolfie  
       2022-09-15 11:45:14 +08:00
    建联合索引，或者 force index(b)
    djoiwhud
        20
    djoiwhud  
       2022-09-15 12:00:13 +08:00 via Android
    有点好奇，你的 sql 可以执行？

    select * from table where order by limit n
    thinkershare
        21
    thinkershare  
       2022-09-15 12:35:14 +08:00
    @yiplee 具体的还是要走分析器看看. 另外确定一点, 你的插入是否高频? 你的插入是否会导致大规模索引重建?
    另外, 你确认 2 个表的存储引擎是一致的吗? 我在上千万的的 MySQL 上执行复杂查询, 就会比较慢, 感觉几百万-2000W 基本上查询还好, 后面就会越来越慢. 另外 2 台机器的磁盘 I/O 性能一样吗? 影响数据库性能的因素实在太多了.
    yiplee
        22
    yiplee  
    OP
       2022-09-15 12:36:42 +08:00
    @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"
    }
    ]
    ```
    yiplee
        23
    yiplee  
    OP
       2022-09-15 12:37:24 +08:00
    @djoiwhud #20 是我手打的，顺序打错了
    yiplee
        24
    yiplee  
    OP
       2022-09-15 12:39:33 +08:00
    @thinkershare #21 高频插入导致读很慢这个是有可能的，我之前没考虑到。打算从业务层面规避下，处理进度落后到一定值就先不要插入新数据了。
    pengtdyd
        25
    pengtdyd  
       2022-09-15 12:49:11 +08:00
    有没有考虑过是硬件的问题
    iseki
        26
    iseki  
       2022-09-15 13:07:16 +08:00
    b=?命中的数据太多？
    yiplee
        27
    yiplee  
    OP
       2022-09-15 13:19:14 +08:00 via iPhone
    @iseki 的确命中很多，但是索引是有序的带 limit 的情况下扫描前几行不就 OK 了吗
    1018ji
        28
    1018ji  
       2022-09-15 13:29:27 +08:00
    我感觉走 a 更快
    1018ji
        29
    1018ji  
       2022-09-15 13:30:45 +08:00
    @1018ji 错了，自增主键的索引，不知道有没
    zznext
        30
    zznext  
       2022-09-15 13:49:38 +08:00
    先锁表试试；
    YIsion
        31
    YIsion  
       2022-09-15 14:03:33 +08:00
    盲猜 id> ? 这个条件的问题。刚优化了一个类似的，我们业务系统的加这个条件虽然走索引，但会扫 40w 条数据。不加这个条件扫描 2w 条数据
    justfindu
        32
    justfindu  
       2022-09-15 14:04:25 +08:00
    试试去掉 order by, 也可以试试 select * from idin(select id.....order by id limit 32)
    yangxx
        33
    yangxx  
       2022-09-15 14:10:35 +08:00
    有一种可能，5.7 走的是主键索引，条件里还有一个 id>?，这个值在 b 列过滤出来的行可能比较靠后。b 列过滤出来 100 多万行，数据量不少
    yiplee
        34
    yiplee  
    OP
       2022-09-15 14:13:55 +08:00
    @YIsion #31 啊还会这样啊，但是 id > ? 这个条件必须得留着
    yiplee
        35
    yiplee  
    OP
       2022-09-15 14:15:08 +08:00
    @justfindu #32 先走索引覆盖取出 id ，再 join 原表，是个不错的思路。但是我这个查询条件里面有 c ，会导致无法触发索引覆盖 😂
    yiplee
        36
    yiplee  
    OP
       2022-09-15 14:16:58 +08:00
    @yangxx #33 我了解到 MySQL 的二级索引在尾部是包含主键的，也就是 INDEX( b ) = INDEX( b ,id )，b = ? AND id > ? 应该都用到的索引才对吧！？
    5boy
        37
    5boy  
       2022-09-15 14:17:36 +08:00
    force index (b),数据库执行时可能会用错索引
    rrfeng
        38
    rrfeng  
       2022-09-15 14:17:59 +08:00
    "rows": 1519240, 然后需要按 id 重新排序

    主要原因还是 b 命中太多了
    次要原因是需要 id 排序，在 b 索引下 id 有序性无法保证，除非你创建 b+id 联合索引
    yiplee
        39
    yiplee  
    OP
       2022-09-15 14:24:01 +08:00
    @rrfeng #38 突然意识到我对 innodb 的二级索引理解错了，我一直以为 INDEX( b ) = INDEX( b ,id )，实际上 id 是保存在 INDEX( b ) 的叶子节点上值，并不是有序的，所以无法用于 ORDER BY ，不知道这次理解对没 😂
    buster
        40
    buster  
       2022-09-15 17:08:32 +08:00
    看了各位大佬的分析，我觉得应该跟 C isnotnull 有关，这里可以试一下在 8 的版本下，把这个条件去掉看下执行速度的变化。
    sivacohan
        41
    sivacohan  
       2022-09-15 17:36:41 +08:00
    看一下数据库配置，你可能是 buffer size 不足，导致 order by 之前那个临时表落磁盘了。
    比较一下之前用 5.7 的 buffer size
    Egfly
        42
    Egfly  
       2022-09-15 18:11:27 +08:00
    可能的原因还是有很多的：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
        43
    bthulu  
       2022-09-15 18:37:00 +08:00
    降级到 mysql8.0 就行了, 你还真以为 oracle 接手后的 8.0 会正向优化? 那他的 oracle 还怎么卖
    HunterPan
        44
    HunterPan  
       2022-09-15 19:12:13 +08:00
    c is not null 要回表的吧 去掉试试
    wyx119911
        45
    wyx119911  
       2022-09-15 20:44:50 +08:00   ❤️ 1
    因为你最后用了 ORDER BY id ，导致必须走联合索引才能命中 id 的索引排序，且排序字段要为联合索引最后一个。
    预期走的联合索引是 b,c,id 。但是你只有 b 索引可走，导致 b 过滤完后剩下要扫表。
    所以需要加一个 b,c 索引，因为 id 为主键会自动加入联合索引中，刚好生成 b,c,id 索引满足查询条件。
    iseki
        46
    iseki  
       2022-09-15 23:37:39 +08:00 via Android
    @yiplee 可是看 explain 如果我没理解错它走了另一个索引（不太熟悉 MySQL 的 explain 输出
    akira
        47
    akira  
       2022-09-16 02:40:21 +08:00
    如果数据分布有规律的话，能否给出 id 的上限，可以的话可以考虑加个 id < ?
    v2orz
        48
    v2orz  
       2022-09-16 08:46:41 +08:00
    @wyx119911
    “主键会自动加入联合索引中”
    还有这个？学到了，感谢

    我去找一下资料看看
    joApioVVx4M4X6Rf
        49
    joApioVVx4M4X6Rf  
       2022-09-16 09:02:51 +08:00
    好帖子，又学到很多东西
    hoopan
        50
    hoopan  
       2022-09-16 09:03:18 +08:00
    盲猜，数据库或服务器配置问题。数据一样，sql 一样，不同的只有数据库版本、服务器了。
    winglight2016
        51
    winglight2016  
       2022-09-16 09:09:18 +08:00
    没有“自动”二级索引这种事情，只是 mysql 的索引树有两个，默认是 id 索引 b tree+, 其他索引是先索引到 id 再走 id 索引获取最终记录

    可以试试把 id 条件放在前面，两台机器查询速度不一样，可能是 sql engine 优化器设置不同，另一台机器也执行以下 explain 对比一下就知道了
    Flourite
        52
    Flourite  
       2022-09-16 10:10:36 +08:00
    盲猜两个可能原因
    1. innodb_buffer_pool_size 这个配置起决定性作用
    2. query_cache 8.0 已经删了
    wmwmajie
        53
    wmwmajie  
       2022-09-16 10:21:20 +08:00
    调整一下你 where 条件的顺序试试，可能是优化器走的索引规则不一样。
    wtfedc
        54
    wtfedc  
       2022-09-16 11:34:25 +08:00
    坐等查验结果
    fgd
        55
    fgd  
       2022-09-16 16:00:13 +08:00
    怀疑是排序过程的性能损耗，可能有很多磁盘 io 。
    1. 试一下去掉 order by 看下时间呢？看看 sort_buffer_size max_length_for_sort_data 这两个参数的值，两个数据库一致吗？
    coolstranger
        56
    coolstranger  
       2022-09-19 15:16:25 +08:00
    有结论了吗，最后是 mysql 的配置问题，还是两个表的数据问题，还是两个版本的执行策略有变化？
    yiplee
        57
    yiplee  
    OP
       2022-09-19 15:28:30 +08:00
    @coolstranger #56 还没有，暂时先把用 8.x 那台的服务给停了。
    对了，停掉服务之后，再手动跑了一下这个简单的查询，86 ms 就返回了 ：）
    ashmodeus
        58
    ashmodeus  
       2022-09-21 11:07:38 +08:00
    5.7.x 版本的 explain 也发上来看下吧，估计应该是走了 id>这个过滤条件的主键索引，因为 b=?命中非常多，所以走主键更快。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3049 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 13:37 · PVG 21:37 · LAX 05:37 · JFK 08:37
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.