MySQL-优化order by
程序员文章站
2024-03-23 15:16:16
...
索引优化排序
InnoDB和MyISAM的数据分布对比
了解存储的数据结构有助于理解索引优化排序是否有效的场景
MyISAM 按照数据插入的顺序存储在磁盘上。
二级索引:叶子节点存储的是行指针
InnoDB支持聚簇索引
聚簇索引: 叶子节点存储主键值、剩余其他咧、事务id、用于事务和MVCC的回滚指针。
二级索引(非聚簇索引):叶子节点存储的是主键值,并以此作为指向行的“指针”。相比于存储行指针会占用更多空间,好处是在移动行时不需要去更新二级索引中的这个指针。
使用索引扫描来排序
在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;
其它情况:
- 索引不支持存储数据行本身,例如Memory引擎的hash索引。
- order by和group by的表达式不同。
- 如果是多表join的查询,order by条件不全是第一个非常数表的字段,将无法使用索引排序。
参考
上一篇: Zookeeper知识点总结
下一篇: Flink快速入门
推荐阅读
-
MySQL优化之limit和order by
-
MySQL-优化order by
-
优化程序之——宏与内联函数
-
优化Swift的构建时间
-
MySQL工作/面试必备查询优化方案
-
SQL查询简单优化 博客分类: Oracle Oracle查询优化
-
MySQL Explain 详解(优化MySQL性能第一步) 数据库mysql
-
SQL查询简单优化 博客分类: Oracle Oracle查询优化
-
优化Spring启动速度的问题!!
SpringHibernateHSQLDBXMLJDBC">
关于
优化Spring启动速度的问题!! SpringHibernateHSQLDBXMLJDBC -
HSQLDB 文件 变大 优化处理 博客分类: HSQLDB hsqldb文件变大