Order BY 与 FileSort 疑问求大佬解答

2020-06-11 10:59:01 +08:00
 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

1755 次点击
所在节点    MySQL
4 条回复
senninha
2020-06-11 11:11:06 +08:00
select a,b where b > ? order a

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

另外,select a,b where b > ? order a,这个查询刚好可以索引覆盖,相比直接全表扫描,这个索引使用减少了磁盘 io,并且不需要 filesort 。
bluehr
2020-06-11 12:17:28 +08:00
@senninha 多谢,有点理解了

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/680581

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX