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
zuiye111
V2EX  ›  MySQL

请教一条 mysql 慢查询问题

  •  
  •   zuiye111 · 2020-02-22 14:29:49 +08:00 · 4665 次点击
    这是一个创建于 1497 天前的主题,其中的信息可能已经有所发展或是发生改变。
    有一张表,大概 180w 条数据,有一条 sql,如下
    ```
    select * from order where 1 = 1 and user_id = 12345 and mchcode = '56789' and (0 or order_state = 2 or order_state = 4 or order_state = 5 ) and (order_property_bit & 128)=128 and channel_type = 2 order by create_time desc LIMIT 1 OFFSET 0;
    ```
    查询花了 5s,我 explain 了下这条 sql,大概是这样的

    -------+---------------+-------------+---------+------+------+----------+-------------+
    | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +-------+-------+-------+-------------+---------+------+------+----------+-------------+
    | index | mchcode | create_time | 5 | NULL | 1311 | 0.00 | Using where |
    +-------------+-------------------------+------------+-------+---------------+----------

    可见,确实用到了索引 create_time,但为何还是这么慢呢?
    我的表索引结构大概是这样的
    PRIMARY KEY (`auto_id`),
    UNIQUE KEY `order_id` (`order_id`),
    KEY `user_id` (`user_id`),
    KEY `create_time` (`create_time`),
    KEY `mchcode` (`mchcode`)

    字段:`mchcode` varchar(20) DEFAULT NULL, `create_time` int(11) DEFAULT NULL, mchcode 是字符串,create_time 是时间戳,都建了索引

    在查问题过程中,还发现一些很奇怪的问题,就是这条 sql,有时查又很快,快的时候,我 explain 时,发现索引用的是 mchcode,慢的时候,如上,索引用的是 create_time,为何同样一条 sql,会用不同的索引呢,为何用 mchcode 索引查时快,用 create_time 时慢呢?
    45 条回复    2020-02-23 14:47:54 +08:00
    jnduan
        1
    jnduan  
       2020-02-22 14:33:18 +08:00   ❤️ 2
    看见 where 1 = 1 就头大
    MoYi123
        2
    MoYi123  
       2020-02-22 14:47:04 +08:00
    如果有非常大量相同的 mchcode 话,有可能会选择 create_time 索引。所以 reindex 一下看看吧。
    ZehaiZhang
        3
    ZehaiZhang  
       2020-02-22 14:55:36 +08:00
    @jnduan 应该是业务代码拼接的原因吧
    smilepig
        4
    smilepig  
       2020-02-22 15:01:10 +08:00
    查询的时候不一定用的是 explain 给的索引,如果不是一个实例的话;
    用 create time 慢我觉得和 limit 有关;
    goobai
        5
    goobai  
       2020-02-22 15:02:32 +08:00 via Android
    试一试联合索引
    smilepig
        6
    smilepig  
       2020-02-22 15:07:00 +08:00
    说错了,create time 慢可能和 order by,limit 有关,记得之前遇到过一次索引失效的问题
    GGGG430
        7
    GGGG430  
       2020-02-22 15:11:30 +08:00
    首先该一下你的 sql:
    select * from order
    where user_id = 12345
    and mchcode = '56789'
    and order_state in (2,4,5)
    and channel_type = 2;

    然后增加索引(先不不考虑列选择性):
    create index idx_xx on order (user_id, mchcode, order_state, channel_type)

    最后业务层通过上面的 sql 应该查不来的结果集很小了, 手动代码处理:
    (order_property_bit & 128) == 128
    order by order by create_time desc LIMIT 1 OFFSET 0;

    你试试
    zuiye111
        8
    zuiye111  
    OP
       2020-02-22 15:16:44 +08:00
    @smilepig 我把 order by create_time limit 1 去掉后,查询就很快,然后 explain 了下,这时走了 mchcode 索引。
    所以问题就是,为何 create_time 上加了索引,然后 order by create_time,就变慢了
    Sasasu
        9
    Sasasu  
       2020-02-22 15:16:51 +08:00
    索引用的是 create_time: 因为 order by create_time , 按 create_time 顺序扫出所有的 id 再对每个 id 用 auto_id 找 mchcode user_id sorder_state 等等字段并 limit 1,最好复杂度 1 最坏扫全表

    索引用的是 mchcode: 因为 mchcode = '56789',扫所有的 mchdoe= '56789' 的,取出 id 扫 auto_id 找 mchcode user_id sorder_state 等等字段, 按 time 排序 并 limit 1。最好复杂度=最坏复杂度,都是 mchcode = '56789' 的数量

    优化器只能在最好复杂度和最坏复杂度之间蒙一个
    Sasasu
        10
    Sasasu  
       2020-02-22 15:19:03 +08:00
    你自己按 time 排序并 limit 1 复杂度 log n, 这种事就别让给数据库干
    zuiye111
        11
    zuiye111  
    OP
       2020-02-22 15:19:27 +08:00
    @GGGG430 我明白你的意思,尽量用联合索引,但这里还有个背景,业务方有很多个,并不都是通过这种方式查的,有的业务方可能不传 user_id,有的业务方只传 mchcode,所以这里单独建了索引
    lasuar
        12
    lasuar  
       2020-02-22 15:20:41 +08:00
    SQL 优化建议:
    1、or 关键字相邻字段必须建立索引(尽量不使用 or )
    2、尽量不要在 where 语句汇中将条件字段进行运算(如函数调用、位运算等),否则不会用到索引
    还有就是,你应该把排序去掉之后再 explain。
    GGGG430
        13
    GGGG430  
       2020-02-22 15:24:52 +08:00
    @zuiye111 如果是这样的话, 你仍然可以用我上面那个 sql, 只不过那个联合索引改成多个单列索引或者多个较小的联合索引, 但是这个(order_property_bit & 128)=128 语句一定要移出来在业务层处理, 这个会导致很多索引用不上, 而移出这行也就要导致移出后面的 order by 到业务层面处理
    Sasasu
        14
    Sasasu  
       2020-02-22 15:28:50 +08:00
    (0 or order_state = 2 or order_state = 4 or order_state = 5 ) and (order_property_bit & 128)=128 and channel_type = 2

    这里堆经过 code gen 之后就是几个 if 而已,带一个和带一大对没什么区别
    zuiye111
        15
    zuiye111  
    OP
       2020-02-22 15:28:56 +08:00
    @Sasasu 如阁下所说,只要是 order by xxx 形式,即使 xxx 上建了索引,仍然有可能会扫全表?那么,什么情况会是你说的最好复杂的 1,什么情况下最坏扫全表?
    其次,同样的 sql,explain 时,为何有时索引用 create_time,有时又用 mchcode? (大部分情况确实用的 mchcode 索引)我知道这个是优化器的一个策略,但为何会算出不同的策略呢?
    zuiye111
        16
    zuiye111  
    OP
       2020-02-22 15:33:22 +08:00
    @GGGG430 嗯,感谢指导,这些道理我也明白,尽量不要把运算搞到 sql 中,但这里的问题不是因为这个运算引起的,因为换使用 mchcode 索引时,查询仍然很快,使用 create_time 就很慢
    Sasasu
        17
    Sasasu  
       2020-02-22 15:33:36 +08:00
    order by xxx,xxx 上有索引所以 order by 不会触发排序

    但是你有 filter, xxx 上的索引不够运行你的 filter,需要按主键(无论你用的是啥)取出你需要的字段做 filter。

    xxx 上的索引扫到最后 limit 1 还得不到足够的结果就是最坏情况,扫全表
    zuiye111
        18
    zuiye111  
    OP
       2020-02-22 15:36:28 +08:00
    @lasuar 为啥要把排序去掉再 explain? 这样 explain 出来的结果,对原 sql 有指导意义吗
    GGGG430
        19
    GGGG430  
       2020-02-22 15:43:55 +08:00
    @zuiye111 很显然使用 mchcode 索引时(其`选择性`很大), 存储引擎该索引筛选出的结果集很小了啊, 而 create_time 并没有筛除任何数据啊, 你没看到用 create_time 索引时 filtered 这列为 0 吗? 你再看看用 mchcode 索引时, 这一列应该有几十了
    zuiye111
        20
    zuiye111  
    OP
       2020-02-22 15:51:02 +08:00
    @Sasasu 按你的解释,那我这 sql,由于有 filter,如果 mysql 使用了 create_time 索引,那必定会扫全表了?因为必须得一条一条的按 filter 过滤
    Sasasu
        21
    Sasasu  
       2020-02-22 15:54:00 +08:00
    过滤到一个 sql 就结束,比扫全表稍微好一点点
    zuiye111
        22
    zuiye111  
    OP
       2020-02-22 15:54:29 +08:00
    @GGGG430 你说的有点问题,explain 的这个 filtered 字段,是百分比。。。当用 mchcode 索引时,这列是 0.27
    GGGG430
        23
    GGGG430  
       2020-02-22 15:56:46 +08:00
    @zuiye111 是百分比啊, 存储引擎返回的数据在 server 层过滤后, 剩下多少满足查询的记录数量的比例
    你能贴一下当用 mchcode 索引时的 explain 记录吗, 看一下 rows 这列值
    zuiye111
        24
    zuiye111  
    OP
       2020-02-22 15:57:35 +08:00
    @Sasasu 哦好像明白了,因为有 limit 1,所以过滤到一个就结束,假如没有 limit 1,索引又是 create_time,估计会扫全表,或者根本就没有满足 filter 条件的数据,也会扫全表。是这个道理吗?
    zuiye111
        25
    zuiye111  
    OP
       2020-02-22 15:59:07 +08:00
    @GGGG430 这是用 mchcode 时的 explain
    ------+---------------+---------+---------+-------+------+----------+----------------------------------------------------+
    type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    ------+---------------+---------+---------+-------+------+----------+----------------------------------------------------+
    ref | mchcode | mchcode | 63 | const | 1312 | 0.27 | Using index condition; Using where; Using filesort |
    ------+---------------+---------+---------+-------+------+----------+----------------------------------------------------+
    GGGG430
        26
    GGGG430  
       2020-02-22 16:00:27 +08:00
    哪来的扫全表呢, rows: 预估需要扫描的行数, 你的值在使用 create_time 索引时才 1131
    zuiye111
        27
    zuiye111  
    OP
       2020-02-22 16:05:20 +08:00
    @GGGG430 嗯,这里我也有这个疑惑,用 mchcode 索引或者 create_time 索引,rows 都差不多,但查询时间相差巨大,所以解读 explain 时,关键是看哪个字段? ref ? rows ?
    Sasasu
        28
    Sasasu  
       2020-02-22 16:06:26 +08:00
    Using filesort 看来 mysql 选择了对时间做外排序,复杂度 nlogn,实际上只选出几个元素有 logn 的算法的。或许 limit 1 优化器没利用上

    > rows: 预估需要扫描的行数, 你的值在使用 create_time 索引时才 1131

    最好情况扫 1 行,最坏情况扫 180w 行,mysql 的神奇优化器预估扫 1131 行就行了,但实际执行估计扫了十几万行
    GGGG430
        29
    GGGG430  
       2020-02-22 16:10:31 +08:00
    我建议直接强制使用两种索引的情况下,分别打印一下时间耗在哪里, 一会贴出来看看
    set profiling = 1;
    select * from order ... force index (xxx);
    show profiles;
    GGGG430
        30
    GGGG430  
       2020-02-22 16:13:06 +08:00
    @zuiye111 另外, filtered 这个值范围是 0-100, 你理解的 0-1 是错的
    zuiye111
        31
    zuiye111  
    OP
       2020-02-22 16:16:57 +08:00
    @Sasasu 使用 create_time 时,是 1311 哦
    最好情况下扫 1 行,正好 create_time 最大的那行,满足 filter 条件。最坏的情况,扫到 create_time 最小的那行,还是不满足 filter 条件,是这样吗?
    然后 rows 的值,只有参考意义,但实际扫了多少行,是不知道的,是这样吗?
    zuiye111
        32
    zuiye111  
    OP
       2020-02-22 16:21:24 +08:00
    @GGGG430 嗯,这个是线上生产哦,不敢随便乱搞。。。
    force index 可以强制让 mysql 使用哪个索引,单纯测试可以用,但不是这里的解放办法
    GGGG430
        33
    GGGG430  
       2020-02-22 16:22:01 +08:00
    第一列 type:
    ref: 使用非唯一索引(即非 unique,primary key)扫描或唯一索引的前缀扫描,
    index: 索引全扫描, MySQL 遍历整个索引来查询匹配的行

    这个估计表明 create_time 了扫描了过多的索引,

    另外我觉得纠结这些没必要, 就像上面说的, 优化你的 sql 才是关键
    GGGG430
        34
    GGGG430  
       2020-02-22 16:24:40 +08:00
    force index 只是测试用一下, 只对当前 sql 有效, 不影响其他语句的
    goodboy95
        35
    goodboy95  
       2020-02-22 16:26:08 +08:00
    @jnduan where 1=1 猜测是系统的权限模块搞的鬼,因为我们系统就用 where 1=1 或者 1=-1 来控制用户能不能拿到数据……
    zuiye111
        36
    zuiye111  
    OP
       2020-02-22 16:28:52 +08:00
    慢的问题大概清楚了,但还有个问题不太清楚
    同一条 sql,explain 时,为何有时用了 create_time 索引,有时又用 mchcode 索引?
    zuiye111
        37
    zuiye111  
    OP
       2020-02-22 16:31:44 +08:00
    @goodboy95 不用想那么复杂哈,这里就是写 sql 时为了拼接,仅此而已
    GGGG430
        38
    GGGG430  
       2020-02-22 16:52:14 +08:00
    msyql 优化器会根据非常多的优化策略后, 最终决定当前使用索引是哪个, 且线上数据随时都在变, 也会影响最终实际使用的索引, 这个估计一般的 dba 都没法回答你
    jnduan
        39
    jnduan  
       2020-02-22 17:26:10 +08:00
    @goodboy95 何苦把这种判断下沉到 SQL 这一层面……本应该是业务层解决的问题……
    gy123
        40
    gy123  
       2020-02-22 17:30:21 +08:00
    1.where 最左原则
    2.union all 代替 or
    3.应用层解决
    以上建议~
    akira
        41
    akira  
       2020-02-22 23:59:46 +08:00
    order_property_bit & 128 这种用法不建议。 如果确实必要,那还不如额外建个列来保存这个数值。
    qza1212
        42
    qza1212  
       2020-02-23 03:49:02 +08:00
    #2 楼提到了问题的关键

    mysql 查询优化器要么用 mchcode 索引 要么用 create_time 索引 (假如 where 只考虑 mchcode )

    1 如果用 mchcode 索引:从 mchcode 索引树里拿到所有满足条件的主键->回表->排序拿到第一个
    优点是用到 mchcode 索引,查询比较快 缺点是要排序

    2 如果用 create_time 索引:对 create_time 索引树扫全表->每次都要回表判断 where 条件->一旦满足条件直接返回
    优点是不用额外排序了,缺点是 where 条件过滤无法用到索引,只能全表扫描

    所以如果 mchcode 过滤之后结果非常多,导致额外的排序非常耗时那么 2 比较好,反之 1 比较好
    查询优化器根本无法判断,这种只有执行了才知道
    sansanhehe
        43
    sansanhehe  
       2020-02-23 09:03:12 +08:00
    建立多列索引不香吗:user_id + mchcode + createtime
    encro
        44
    encro  
       2020-02-23 10:19:53 +08:00
    1,大多数情况 create_time 索引不是需要的。。。要也是放在组合索引末尾,除非你前台大量 between 类查询。
    2,Or 查询和 in 查询不能很好利用组合索引;
    3, order_property_bit & 128 不能很好利用索引;
    4,查询优化器自动预估,不知道你的 possible_keys 为什么没有 user_id
    zuiye111
        45
    zuiye111  
    OP
       2020-02-23 14:47:54 +08:00
    感谢上述各位大佬解答,很有参考意义
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5369 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 08:02 · PVG 16:02 · LAX 01:02 · JFK 04:02
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.