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

Order BY 与 FileSort 疑问求大佬解答

  •  
  •   bluehr · 2020-06-11 10:59:01 +08:00 · 1671 次点击
    这是一个创建于 1387 天前的主题,其中的信息可能已经有所发展或是发生改变。

    最近在看周阳的 Mysql 视频学习如何优化索引 ,对其中的一个例子始终疑惑,没找到答案,我比较菜,对 mysql 的 innodb 引擎还没有深入理解学习,所以只能依据一些简单的规则结论来分析问题,还望大佬解释的时候稍微简单一点

    假如对表 table ( a,b,c) 建立复合索引 index(a,b)

    有如下 case

    1. select a,b order by a 不会产生 filesort

    2. select a,b order by b 会产生 filesort

    3. select a,b order by a,b 不会产生 filesort

    4. select a,b order by b,a 会产生 filesort

    5. select a,b where a=? 能用到索引

    6. select a,b where b=? 用不到索引

    这里涉及到的结论有

    1. order by 和 where 都按照最左前列 来使用索引才不会使索引失效
    2. where 条件范围查询后的索引全失效

    我的问题就是下面这条语句为什么还能使用索引排序不产生 filesort

    select a,b where b > ? order a

    我认为这条语句有几个问题会无法使用索引才对:

    1. where 条件没有符合索引最左前列
    2. where 条件还是范围查询,如果是先执行的 where 再执行 order by 那么光是看 where 就已经跟上面的 case 6 一样了
    3. where 和 order 组合查询通常也得符合索引最左前列规则才不会产生 filesort,比如 where a=? order by b where a>? order by a

    完整的建表语句和 问题 sql 执行 explain 结果

    CREATE TABLE `tbla`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `age` int(11) NULL DEFAULT NULL,
      `birth` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
      `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `test` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `idx_A_ageBirth`(`age`, `birth`, `name`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of tbla
    -- ----------------------------
    INSERT INTO `tbla` VALUES (1, 22, '2020-06-10 21:51:42', 'abc', NULL);
    INSERT INTO `tbla` VALUES (2, 23, '2020-06-10 21:51:42', 'bcd', NULL);
    INSERT INTO `tbla` VALUES (3, 24, '2020-06-10 21:51:42', 'def', NULL);
    
    SET FOREIGN_KEY_CHECKS = 1;
    

    问题 Sql explain

    image.png

    4 条回复    2020-06-11 12:17:28 +08:00
    senninha
        1
    senninha  
       2020-06-11 11:11:06 +08:00
    select a,b where b > ? order a

    这个是 order by a 先借助索引(a,b)取出有序数据,然后再 where 筛选结果,确实不需要 filesort 啊。
    bluehr
        2
    bluehr  
    OP
       2020-06-11 11:21:28 +08:00
    @senninha 这里我一直以为是先执行 Where 条件后再执行 排序操作的。 如果这个表特别大,先取出有序序列岂不是先加载了大量数据后再进行筛选
    senninha
        3
    senninha  
       2020-06-11 11:40:04 +08:00
    @bluehr 你说的这种情况如果成立,就是不用索引,直接全表扫描,也会存在加载大量数据再进行筛选。
    实际上,具体实现可能是边加载数据边筛选,留在内存中的只是结果集啊。

    另外,select a,b where b > ? order a,这个查询刚好可以索引覆盖,相比直接全表扫描,这个索引使用减少了磁盘 io,并且不需要 filesort 。
    bluehr
        4
    bluehr  
    OP
       2020-06-11 12:17:28 +08:00
    @senninha 多谢,有点理解了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   4751 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 05:37 · PVG 13:37 · LAX 22:37 · JFK 01:37
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.