V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
demonps
V2EX  ›  程序员

请教这条 mysql 优化出路在哪?

  •  
  •   demonps · 2020-06-24 16:17:12 +08:00 · 2784 次点击
    这是一个创建于 1624 天前的主题,其中的信息可能已经有所发展或是发生改变。

    表结构

    create table assign_idx(
      it_id BIGINT UNSIGNED NOT NULL,
      cat_id BIGINT UNSIGNED NOT NULL,
      c_type BIGINT UNSIGNED NOT NULL,
      c_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
      l_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
      create_time BIGINT UNSIGNED NOT NULL,
      PRIMARY KEY(it_id),
      INDEX idx_c_type_cat_c_user_time(c_type, cat_id, c_user_id, create_time)
    ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 default charset utf8mb4;
    
    explain select it_id from assign_idx where c_user_id = 0 and l_user_id != 3333 and (c_type, cat_id) in ((30, 1), (30, 2), (30, 3), (20, 4), (20, 6), (20, 9), (20, 10)) order by create_time asc limit 1;
    

    explain 结果 总有 filesort 如何破

    21 条回复    2020-06-25 20:05:24 +08:00
    demonps
        1
    demonps  
    OP
       2020-06-24 16:22:14 +08:00
    ```mysql
    > explain select it_id from assign_idx where c_user_id = 0 and l_user_id != 3333 and (c_type, cat_id) in ((30, 1), (30, 2), (30, 3), (20, 4), (20, 6), (20, 9), (20, 10)) order by create_time asc limit 1;
    +----+-------------+------------+------------+------+----------------------------+------+---------+------+------+----------+-----------------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+------------+------------+------+----------------------------+------+---------+------+------+----------+-----------------------------+
    | 1 | SIMPLE | assign_idx | NULL | ALL | idx_c_type_cat_c_user_time | NULL | NULL | NULL | 1 | 100.00 | Using where; Using filesort |
    +----+-------------+------------+------------+------+----------------------------+------+---------+------+------+----------+-----------------------------+
    ```mysql
    TanLeDeDaNong
        2
    TanLeDeDaNong  
       2020-06-24 16:29:12 +08:00
    所有的 in 全部拆成
    (c_type=a AND cat_id=b) OR (...)
    zhangysh1995
        3
    zhangysh1995  
       2020-06-24 16:37:17 +08:00
    (c_type, cat_id) 是索引的一部分,所以建的索引不能用,慢
    where 里面的 col 都不能用索引,慢
    zhangysh1995
        4
    zhangysh1995  
       2020-06-24 16:39:55 +08:00
    https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html 文档

    MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.
    poisedflw
        5
    poisedflw  
       2020-06-24 16:45:30 +08:00
    单从你的 sql 来看,索引顺序建错了吧?
    KEY `idx` (`c_user_id`,`l_user_id`,`c_type`,`cat_id`,`create_time`)
    zhangysh1995
        6
    zhangysh1995  
       2020-06-24 16:51:55 +08:00
    自己打下脸,好像 (c_type, cat_id) 可以优化

    https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#row-constructor-range-optimization

    Only IN() predicates are used, not NOT IN().
    On the left side of the IN() predicate, the row constructor contains only column references.
    On the right side of the IN() predicate, row constructors contain only runtime constants, which are either literals or local column references that are bound to constants during execution.
    On the right side of the IN() predicate, there is more than one row constructor.
    zhangysh1995
        7
    zhangysh1995  
       2020-06-24 16:53:16 +08:00
    给一下
    explain select it_id where (c_type, cat_id) in ((30, 1), (30, 2), (30, 3), (20, 4), (20, 6), (20, 9), (20, 10));
    结果?
    wangyzj
        8
    wangyzj  
       2020-06-24 16:55:21 +08:00
    参考 #5 试试
    然后再看
    demonps
        9
    demonps  
    OP
       2020-06-24 17:03:46 +08:00
    @zhangysh1995 mysql> explain select it_id from assign_idx where (c_type, cat_id) in ((30, 1), (30, 2), (30, 3), (20, 4), (20, 6), (20, 9), (20, 10));
    +----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+--------------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+--------------------------+
    | 1 | SIMPLE | assign_idx | NULL | range | idx_c_type_cat_c_user_time | idx_c_type_cat_c_user_time | 16 | NULL | 86 | 100.00 | Using where; Using index |
    +----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+--------------------------+
    Foredoomed
        10
    Foredoomed  
       2020-06-24 17:04:10 +08:00
    所以顺序错了,create_time 放第一个
    demonps
        11
    demonps  
    OP
       2020-06-24 17:24:20 +08:00
    @Foredoomed 还是不对,create_time 放第一个 ,type 就变成 index 了 虽然没了 filesort
    lpts007
        12
    lpts007  
       2020-06-24 18:10:14 +08:00
    这张表目前多少数据?
    这个语句目前耗时?
    有 filesort 怎么了?
    变成 index 怎么了?
    lpts007
        13
    lpts007  
       2020-06-24 18:17:51 +08:00
    这个语句没有毛病。不太清楚要优化什么。
    ElmerZhang
        14
    ElmerZhang  
       2020-06-24 18:22:50 +08:00
    没用到期望的索引时,就 force index 试试
    demonps
        15
    demonps  
    OP
       2020-06-24 19:04:22 +08:00
    @lpts007 其实这个本来就是一张 index 作用的表,
    目前数据 300 万+
    高频最高耗时 0.13s
    本来这个是要 update ... from (select ...)
    因为读写频次高导致 锁表,所以 拆成两步,先 select 再 update 。
    本来就是 index 功能的中间表,扫 index 和扫全表就没啥差异了呀
    jiehuangwei
        16
    jiehuangwei  
       2020-06-24 19:33:13 +08:00
    如果不是写的特别差的语句,其优化的空间很有限,投入产出比太低,不如从业务入手,梳理业务逻辑
    zhangysh1995
        17
    zhangysh1995  
       2020-06-24 19:59:09 +08:00
    @demonps 问题应该在 where 里面前两个条件没有索引,可以拆开成 explain select it_id where c_user_id = 0 and l_user_id != 3333; 然后继续拆,单独看一下数据情况,估计会有至少一个估计不准确的(应该是 l_user_id 没有索引)。

    我写这句的时候突然有个想法,你可以试下:

    select it_id from assign_idx where (c_type, cat_id) in ((30, 1), (30, 2), (30, 3), (20, 4), (20, 6), (20, 9), (20, 10)) and c_user_id = 0 and l_user_id != 3333 order by create_time asc limit 1;
    guyskk0x0
        18
    guyskk0x0  
       2020-06-25 00:22:22 +08:00 via Android
    目测 2 楼正解。建的索引问题不大,基数大的列放前面会更好。
    controller
        19
    controller  
       2020-06-25 08:21:31 +08:00 via Android
    不等号不会走索引。。。
    demonps
        20
    demonps  
    OP
       2020-06-25 20:04:31 +08:00
    @jiehuangwei 感谢🙏
    demonps
        21
    demonps  
    OP
       2020-06-25 20:05:24 +08:00
    @zhangysh1995 试了好多方法 是 in 的锅 目前 in 不太好再抽一个量表示 蛋疼中~
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5598 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 03:37 · PVG 11:37 · LAX 19:37 · JFK 22:37
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.