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

MySQL 查询使用主键 id 排序竟然是走 filesort 的?

  •  
  •   NULL2020 · 2020-10-22 12:09:50 +08:00 · 3138 次点击
    这是一个创建于 1253 天前的主题,其中的信息可能已经有所发展或是发生改变。

    最近了解到,mysql 的排序也需要满足 [前导列 /最左前缀] 原则才会走索引排序,不然就是 filesort 。

    之前一直没留意过,工作中大量使用主键 id 排序,用 explain 分析后发现几乎都是走 filesort,不管是

    select * where  a=123 order by id desc;
    
    select id, a,b,c where  a=123 order by id desc;
    
    

    都无法走主键索引进行排序,id 是自增主键,a 列有索引,查询可以用到 a 列的索引,但排序依旧走 filesort.

    按理说,主键也有索引,为什么无法使用索引进行排序呢?

    16 条回复    2020-10-26 16:22:17 +08:00
    OysterQAQ
        1
    OysterQAQ  
       2020-10-22 12:17:09 +08:00 via iPhone
    每次查询只能命中一个索引,解决方法,加联合索引(a,is)
    user8341
        2
    user8341  
       2020-10-22 12:18:00 +08:00
    a 列的索引是按照 a 的大小顺序排列的,不是按照 id 的大小顺序排列的。
    OysterQAQ
        3
    OysterQAQ  
       2020-10-22 12:22:16 +08:00 via iPhone   ❤️ 1
    了解下索引排序的原理就明白了……本身索引是有序的才可以做到排序,order 之前的筛选项还得是=
    NULL2020
        4
    NULL2020  
    OP
       2020-10-22 12:25:46 +08:00
    @OysterQAQ #1 感谢,测试了下确实是这样,where 里使用无索引的列则会走索引排序
    user8341
        5
    user8341  
       2020-10-22 12:26:37 +08:00
    为什么不用主键索引?因为没用吧。

    你按 A 的索引找到一组记录,他们的 ID 是乱序的。
    然后你怎么用 ID 索引去排序?我是想不出来有什么办法。

    如果你先用 ID 的索引,那也没用,因为不符合 where 里面的条件,只能全表扫描,用不到索引。
    NULL2020
        6
    NULL2020  
    OP
       2020-10-22 12:29:01 +08:00
    @user8341 #5 1 楼大佬说的是正解。
    user8341
        7
    user8341  
       2020-10-22 12:32:20 +08:00
    @NULL2020 一楼说的没错啊。联合索引(a, id)就是按照 A 为主要关键字,id 为次要关键字排序。所以用这个索引,如果你 A 的条件是 = 的,ID 就是有序的。
    但是如果 A 条件是>, <之类的,恐怕 ID 还是无序,还是要 filesort 。
    user8341
        8
    user8341  
       2020-10-22 12:36:09 +08:00
    @OysterQAQ 不准确。现在的 mysql 还有 Index Merge Optimization 。
    OysterQAQ
        9
    OysterQAQ  
       2020-10-22 12:43:01 +08:00 via iPhone
    @user8341 建议实际上看看查询计划再说吧,不知道你有没有看过高性能 mysql,里面确实有说到几个单列索引的索引合并,但是实际上你应该去看看你的执行计划,这也是书中一直强调的,在不是很了解内部实现的情况下,以查询计划为主
    user8341
        10
    user8341  
       2020-10-22 13:41:53 +08:00
    @OysterQAQ 你说得没错。不过假如查询计划能解答”为什么“的问题,相信楼主也不会来问了。
    NULL2020
        11
    NULL2020  
    OP
       2020-10-22 13:58:29 +08:00
    @user8341 #10 他解答了我的问题啊, [一个查询只能命中一个索引] ,所以假如 where 条件里命中了索引,那排序就不会命中,只能 filesort
    user8341
        12
    user8341  
       2020-10-22 16:37:42 +08:00
    @NULL2020

    一个查询只能用一个索引是错的啊。再者,你怎么解释 a > 123 这样的条件,order by id 排序就要 filesort 呢?
    wakzz
        13
    wakzz  
       2020-10-22 20:03:50 +08:00
    楼主你确定你使用了 a 字段索引?如果使用了 a 字段索引,那么排序直接使用 a 索引,不需要额外排序也就是不需要 filesort 。
    wakzz
        14
    wakzz  
       2020-10-22 20:05:45 +08:00
    @user8341 索引树当索引值相同时,是以主键有序排列的,并不是乱序。如果是基于索引的范围查询确实整体乱序,但如果是基于索引的单值查询,那结果本身就是以主键排序的,并不是乱序。
    user8341
        15
    user8341  
       2020-10-22 22:15:35 +08:00
    @wakzz 你连他原帖都没仔细看。
    NULL2020
        16
    NULL2020  
    OP
       2020-10-26 16:22:17 +08:00
    @user8341 #7
    @OysterQAQ #9
    其实我的理解是, 根据 a 索引查询得到结果,结果列里包含了 id,再根据 id 进行排序,也应该能用到 id 主键的索引进行排序才对。

    索引合并好像只适用了查询条件,暂时没找到 [查询+排序] 的索引合并资料。

    业务中大部分查询都是类似这样,那排序走 filesort 是不是无解了?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5795 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 01:54 · PVG 09:54 · LAX 18:54 · JFK 21:54
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.