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

咨询关于 order_by 和 group_by 的一些索引问题

  •  
  •   SjwNo1 · 2020-11-18 14:34:09 +08:00 · 2761 次点击
    这是一个创建于 1227 天前的主题,其中的信息可能已经有所发展或是发生改变。

    表结构

    表名 label_file_bridges
    +------------+------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +------------+------------+------+-----+---------+----------------+
    | id | int(32) | NO | PRI | NULL | auto_increment |

    | label_id | int(32) | NO | MUL | NULL | |

    | file_id | int(32) | NO | | NULL | |

    | is_active | tinyint(1) | NO | | NULL | |
    +------------+------------+------+-----+---------+----------------+

    索引

    PRIMARY id
    idx_label_file_bridge_label_id_file_id_is_active (label_id, file_id, is_active)

    sql

    select file_id, group_concat(label_id) from label_file_bridges where label_id in (1,2,3,4,5,6,7,8,9,10) and is_active=1 group by file_id;

    explain

    id: 1
    select_type: SIMPLE
    table: label_file_bridges
    partitions: NULL
    type: index
    possible_keys: idx_label_file_bridge_label_id_file_id_is_active
    key: idx_label_file_bridge_label_id_file_id_is_active
    key_len: 9
    ref: NULL
    rows: 48182
    filtered: 10.00
    Extra: Using where; Using index; Using filesort

    疑问

    表数据量百万级,以上是测试数据, 使用此 sql 速度还行,但是存在外层排序 filesort, 调试发现用 in + order_by 或者 in + group_by 都会有这个情况 请教一下这个 sql 或者表结构 ( 索引 ) 有可优化的地方吗,有可能避免 filesort 吗

    10 条回复    2020-11-18 16:38:35 +08:00
    lpts007
        1
    lpts007  
       2020-11-18 14:56:54 +08:00 via Android   ❤️ 1
    加 order by null 试过没
    fhsan
        2
    fhsan  
       2020-11-18 15:18:57 +08:00   ❤️ 1
    order by label_id, file_id, is_active
    group by file_id
    Egfly
        3
    Egfly  
       2020-11-18 15:32:39 +08:00   ❤️ 1
    索引位置换一下 file_id 放在 label_id 前面就行
    SjwNo1
        4
    SjwNo1  
    OP
       2020-11-18 15:33:37 +08:00
    @lpts007 试了 没有改变哦
    SjwNo1
        5
    SjwNo1  
    OP
       2020-11-18 15:36:52 +08:00
    @fhsan 不行哈哈
    SjwNo1
        6
    SjwNo1  
    OP
       2020-11-18 15:38:46 +08:00
    @Egfly 可以啦哈哈,原因是什么呢
    Egfly
        7
    Egfly  
       2020-11-18 15:52:29 +08:00   ❤️ 2
    @SjwNo1

    主要是 in 导致的。经过 label_id 的 in 查询之后的结果集不是有序的(虽然你 in 的数据是有序的,但是优化器并不知道)。所以需要使用临时表完成 group_by 操作。

    如果你把 in 换成 label_id=xxx,应该也是不用 file_sort,可以直接走原索引,你可以 explain 看一下。
    SjwNo1
        8
    SjwNo1  
    OP
       2020-11-18 16:02:56 +08:00
    @Egfly 懂了 谢谢
    zlowly
        9
    zlowly  
       2020-11-18 16:10:35 +08:00   ❤️ 1
    是否可以考虑用表分区?
    is_active 字段从名字上猜测是可变的,不适合分区。但这个 label_id 不知道它的数据是否是可以考虑进行分区?
    SjwNo1
        10
    SjwNo1  
    OP
       2020-11-18 16:38:35 +08:00
    @zlowly 有在考虑分区,不过这张表堆积的相关业务太多了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5422 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 35ms · UTC 07:37 · PVG 15:37 · LAX 00:37 · JFK 03:37
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.