mysql排序ORDER BY Optimization
来源:互联网 发布:c语言里的共用体怎么用 编辑:程序博客网 时间:2024/05/22 00:55
有的时候,MySQL不用额外的排序而是使用索引就能满足order by操作。
即使order by不直接匹配索引,索引也能用到,只要所有未用到索引的部分和order by之外的索引列在where字句中,
下面的查询order by部分使用到了索引
SELECT * FROM t1
ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1
WHERE key_part1 = constant
ORDER BY key_part2;
SELECT * FROM t1
ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1
WHERE key_part1 = 1
ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1
WHERE key_part1 > constant
ORDER BY key_part1 ASC;
SELECT * FROM t1
WHERE key_part1 < constant
ORDER BY key_part1 DESC;
SELECT * FROM t1
WHERE key_part1 = constant1 AND key_part2 > constant2
ORDER BY key_part2;
但是有的时候,MySQL不会使用索引来解决order by的问题,即使where条件已经用到索引
1.order by使用不同的索引
SELECT * FROM t1 ORDER BY key1, key2;
2.order by部分列非索引前缀
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
3.升降序混合
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
4.获取行的索引与order by部分不是同一个索引
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
5.order by列上有函数
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;
即使order by不直接匹配索引,索引也能用到,只要所有未用到索引的部分和order by之外的索引列在where字句中,
下面的查询order by部分使用到了索引
SELECT * FROM t1
ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1
WHERE key_part1 = constant
ORDER BY key_part2;
SELECT * FROM t1
ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1
WHERE key_part1 = 1
ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1
WHERE key_part1 > constant
ORDER BY key_part1 ASC;
SELECT * FROM t1
WHERE key_part1 < constant
ORDER BY key_part1 DESC;
SELECT * FROM t1
WHERE key_part1 = constant1 AND key_part2 > constant2
ORDER BY key_part2;
但是有的时候,MySQL不会使用索引来解决order by的问题,即使where条件已经用到索引
1.order by使用不同的索引
SELECT * FROM t1 ORDER BY key1, key2;
2.order by部分列非索引前缀
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
3.升降序混合
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
4.获取行的索引与order by部分不是同一个索引
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
5.order by列上有函数
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;
阅读全文
0 0
- mysql排序ORDER BY Optimization
- MySQL ORDER BY LIMIT Performance Optimization
- MySQL排序:SELECT ORDER BY
- mysql order by内排序
- mysql排序order by新发现
- 8.2.1.15 ORDER BY Optimization
- mysql order by 排序 varchar 类型数据
- mysql order by 排序 varchar 类型数据
- MySQL排序检索数据----ORDER BY
- mysql 使用order by field() 自定义排序
- MYSQL ORDER BY 排序
- MySQL order by case when 排序
- MySQL ORDER BY 排序 IF 及 IN
- mysql的排序检索顺序ORDER BY
- mysql order by 多字段排序
- mysql order by 中文排序 错乱问题
- mysql中order by 可以指定排序
- mysql 使用order by field() 自定义排序
- 字符串的查找
- mysql 随机获取几条数据
- MySQL查询指定时间的数据
- xPath用法
- 使用MyBatis Generator自动创建代码
- mysql排序ORDER BY Optimization
- Atcoer Grand Contest 001 F 拓补排序 STL
- Python最简单的图形编程
- HOG梯度直方图计算原理
- SpringBoot整合Mybatis实例
- 互联网协议入门(二)
- Strncmp函数
- SSM整合与测试(Spring、Spring MVC、MyBatis)
- printf解析方式