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

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

  •  
  •   demonps · 15 天前 · 1596 次点击

    表结构

    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   15 天前
    ```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   15 天前
    所有的 in 全部拆成
    (c_type=a AND cat_id=b) OR (...)
    zhangysh1995
        3
    zhangysh1995   15 天前
    (c_type, cat_id) 是索引的一部分,所以建的索引不能用,慢
    where 里面的 col 都不能用索引,慢
    zhangysh1995
        4
    zhangysh1995   15 天前
    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   15 天前
    单从你的 sql 来看,索引顺序建错了吧?
    KEY `idx` (`c_user_id`,`l_user_id`,`c_type`,`cat_id`,`create_time`)
    zhangysh1995
        6
    zhangysh1995   15 天前
    自己打下脸,好像 (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   15 天前
    给一下
    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   15 天前
    参考 #5 试试
    然后再看
    demonps
        9
    demonps   15 天前
    @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   15 天前
    所以顺序错了,create_time 放第一个
    demonps
        11
    demonps   15 天前
    @Foredoomed 还是不对,create_time 放第一个 ,type 就变成 index 了 虽然没了 filesort
    lpts007
        12
    lpts007   15 天前
    这张表目前多少数据?
    这个语句目前耗时?
    有 filesort 怎么了?
    变成 index 怎么了?
    lpts007
        13
    lpts007   15 天前
    这个语句没有毛病。不太清楚要优化什么。
    ElmerZhang
        14
    ElmerZhang   15 天前
    没用到期望的索引时,就 force index 试试
    demonps
        15
    demonps   14 天前
    @lpts007 其实这个本来就是一张 index 作用的表,
    目前数据 300 万+
    高频最高耗时 0.13s
    本来这个是要 update ... from (select ...)
    因为读写频次高导致 锁表,所以 拆成两步,先 select 再 update 。
    本来就是 index 功能的中间表,扫 index 和扫全表就没啥差异了呀
    jiehuangwei
        16
    jiehuangwei   14 天前
    如果不是写的特别差的语句,其优化的空间很有限,投入产出比太低,不如从业务入手,梳理业务逻辑
    zhangysh1995
        17
    zhangysh1995   14 天前
    @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   14 天前 via Android
    目测 2 楼正解。建的索引问题不大,基数大的列放前面会更好。
    controller
        19
    controller   14 天前 via Android
    不等号不会走索引。。。
    demonps
        20
    demonps   13 天前
    @jiehuangwei 感谢🙏
    demonps
        21
    demonps   13 天前
    @zhangysh1995 试了好多方法 是 in 的锅 目前 in 不太好再抽一个量表示 蛋疼中~
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   3310 人在线   最高记录 5168   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 21ms · UTC 10:48 · PVG 18:48 · LAX 03:48 · JFK 06:48
    ♥ Do have faith in what you're doing.