欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL-优化order by

程序员文章站 2024-03-23 15:16:16
...

索引优化排序

InnoDB和MyISAM的数据分布对比

了解存储的数据结构有助于理解索引优化排序是否有效的场景

MyISAM 按照数据插入的顺序存储在磁盘上。

二级索引:叶子节点存储的是行指针

InnoDB支持聚簇索引

聚簇索引: 叶子节点存储主键值、剩余其他咧、事务id、用于事务和MVCC的回滚指针。

二级索引(非聚簇索引):叶子节点存储的是主键值,并以此作为指向行的“指针”。相比于存储行指针会占用更多空间,好处是在移动行时不需要去更新二级索引中的这个指针。

MySQL-优化order by

使用索引扫描来排序

在MySQL中,如果where子句和order by子句符合索引排序的规则,可以有效避免filesort级别排序(成本高)。

使用索引解决order by问题的情况

-- 假设对于tableA存在索引index on (key_part1, key_part2)
CREATE TABLE `tableA` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key_part1` int(11) DEFAULT NULL,
  `key_part2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index` (`key_part1`,`key_part2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

-- 所有的列排序方向一致时(结合索引的数据结构B-Tree理解)才能使用索引排序
SELECT key_part1, key_part2 FROM tableA
  ORDER BY key_part1 DESC, key_part2 DESC;
-- 同为正序
SELECT key_part1, key_part2 FROM tableA
  ORDER BY key_part1 ASC, key_part2 ASC;

-- 因为主键(一般是id)存在二级索引的叶子节点中,索引select的字段中默认支持包含主键
SELECT id, key_part1, key_part2 FROM tableA
  ORDER BY key_part1, key_part2;

-- 即使order by中不符合前缀索引规范,但是其余前置的索引列在where子句中定义了常量,也会使用索引排序
SELECT key_part1,key_part2 FROM tableA
  WHERE key_part1 = 23
  ORDER BY key_part2;

不会使用索引解决排序问题的情况

以下样例在执行计划中type仍然可能为index,但是可以在extra下发现using filesort,实际并没有做到索引优化排序

-- 使用两个索引
SELECT * FROM t1 ORDER BY key1, key2;

-- order by 子句用了不连续的索引字段(但是如果where子句中能定义有中间列的常数,也会使用索引排序)
SELECT id, key_part1, key_part2 FROM tableA
  ORDER BY key_part1, key_part3;
  
-- 索引列排序顺序不同
SELECT key_part1, key_part2 FROM tableA
  ORDER BY key_part1 DESC, key_part2 ASC;
  
-- 索引列排序顺序不同
SELECT key_part1, key_part2 FROM tableA
  ORDER BY key_part1 DESC, key_part2 ASC;
  
-- where条件和order by对应的索引不同
SELECT * FROM tableA
	WHERE key1 = constant
  ORDER BY key2;
  
-- order by中需要使用完整字段, 下例不可用索引排序
SELECT key_part1, key_part2 FROM tableA
  ORDER BY key_part1 , right(key_part2, 3);
  
-- 不能使用加工过的数据
SELECT ABS(key_part1) AS b FROM t1 ORDER BY b;
  
其它情况:
  1. 索引不支持存储数据行本身,例如Memory引擎的hash索引。
  2. order by和group by的表达式不同。
  3. 如果是多表join的查询,order by条件不全是第一个非常数表的字段,将无法使用索引排序。

参考