Order BY 与 FileSort 疑问求大佬解答

[bluehr]

最近在看周阳的 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

[senninha]

select a,b where b > ? order a

这个是 order by a 先借助索引(a,b)取出有序数据，然后再 where 筛选结果，确实不需要 filesort 啊。

[bluehr]

@senninha 这里我一直以为是先执行 Where 条件后再执行 排序操作的。 如果这个表特别大，先取出有序序列岂不是先加载了大量数据后再进行筛选

[senninha]

@bluehr 你说的这种情况如果成立，就是不用索引，直接全表扫描，也会存在加载大量数据再进行筛选。
实际上，具体实现可能是边加载数据边筛选，留在内存中的只是结果集啊。

另外，select a,b where b > ? order a，这个查询刚好可以索引覆盖，相比直接全表扫描，这个索引使用减少了磁盘 io，并且不需要 filesort 。

[bluehr]

@senninha 多谢，有点理解了