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 · 6654 次点击
    这是一个创建于 561 天前的主题,其中的信息可能已经有所发展或是发生改变。

    有一张简单的表 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"
      }
    ]
    
    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)
    123qwerty
        15
    123qwerty  
       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
    “主键会自动加入联合索引中”
    还有这个?学到了,感谢

    我去找一下资料看看
    v2exblog
        49
    v2exblog  
       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   ·   我们的愿景   ·   实用小工具   ·   2876 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 33ms · UTC 14:22 · PVG 22:22 · LAX 07:22 · JFK 10:22
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.