MySQL数据库ORDER BY优化总结(为排序使用索引)-51CTO.COM
51CTO首页 内容精选 博客 学堂
精培 企业培训 CTO训练营 开源基础软件社区 LeaTech全球CTO领导力峰会 公众号矩阵 移动端 注册/登录 博客 论坛 免费课程 课程排行 直播课 软考学堂 精品班 厂商认证 IT技术 2022年软考 PMP项目管理 在线学习 企业服务 CTO训练营 技术经理研习营 LeaTech峰会 文章 资源 问答 开源课堂 专栏 直播 51CTO 开源基础软件社区 51CTO技术栈 51CTO官微 51CTO学堂 51CTO博客 CTO训练营 开源基础软件社区订阅号 51CTO学堂APP 51CTO学堂企业版APP 开源基础软件社区视频号 内容精选视频话题技术期刊技术大会 社区编辑申请 我关注的话题 我收藏的文章 账号设置 退出
注册/登录 MySQL数据库ORDER BY优化总结(为排序使用索引) 作者：波波说运维 2019-12-18 08:00:09 数据库 MySQL 在使用order by时，经常出现Using filesort，所以对于此类sql语句我们需要去尽力优化，使其尽量使用Using index。那么，我们对于这类型的语句我们怎么去做优化呢? 在使用order by时，经常出现Using filesort，所以对于此类sql语句我们需要去尽力优化，使其尽量使用Using index。
那么，我们对于这类型的语句我们怎么去做优化呢?因为这一块还是比较容易混淆的，所以我弄了个实验，相信大家跟我一起做下实验就都能理解了~
1. 环境准备
drop table if exists test; create table test( id int primary key auto_increment, c1 varchar(10), c2 varchar(10), c3 varchar(10), c4 varchar(10), c5 varchar(10) ) ENGINE=INNODB default CHARSET=utf8; insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5'); insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5'); insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5'); insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5'); insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
2. 创建btree索引
create index idx_c1234 on test(c1,c2,c3,c4); show index from test;
3. 范围扫导致全表扫描
explain select * from test where c1>'a1' order by c1;
分析：
在c1,c2,c3,c4上创建了索引，直接在c1上使用范围，导致了索引失效，全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，并不是查询。
使用c1进行排序，出现了Using filesort。
解决方法：使用覆盖索引。
4、覆盖索引--》优化
explain select c1 from testwhere c1>'a1' order by c1;
分析：
使用了覆盖索引，不走全扫，走索引范围扫描
排序时按照索引的顺序，所以不会出现Using filesort。
这里不懂没关系，后面我会分享索引的八大法则，保证看得懂...
5. 没有按最左列索引排序
explain select c1 from testwhere c1>'a1' order by c2;
分析：
这里出现了Using filesort，是因为排序用的c2，与索引的创建顺序(c1,c2,c3,c4)不一致。
6. 排序索引列与索引创建的顺序相反
explain select c1 from testwhere c1>'a1' order by c2,c1;
分析：
这里出现了Using filesort。因为排序索引列(c2,c1)与索引创建的顺序(c1,c2)相反，从而产生了重排，也就出现了Using filesort。
7. order by索引列排序不一致
explain select c1 from testwhere c1>'a1' order by c1 asc,c2 desc;
分析：
虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。如果是order by c1 asc,c2 asc或者order by c1 desc,c2 desc就会是using index了。
实验总结
1. MySQL支持两种方式的排序filesort和index
Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
2. 为排序使用索引
假设KEY test(a,b,c)
(1) order by 能使用索引最左前缀
-order by a -order by a,b -order by a,b,c -order by a asc,b asc,c asc -order by a desc,b desc,c desc
(2) 如果where使用索引最左前缀定位为常量，则order by可以使用索引
-where a= const order by b,c -where a= const and b= const order by c -where a= const and b> consst order by b,c
(3) 不能使用索引进行排序
-order by a asc,b desc, c desc /*排序不一致*/ -where g=const order by b,c /*丢失a索引*/ -where a=const order by c /*丢失b索引*/ -where a=const order by a,d /*d不是索引一部分*/ -where a in (....) order by b,c /*对于排序来说，多个相等条件也是范围查询*/
3. filesort有两种排序算法：双路排序和单路排序
双路排序：在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段。如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。
单路排序：从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的空间，因为它把每一行都保存在内存中了。但当读取数据超过sort_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产生多次I/O，反而增加其I/O运算。
解决方式：
增加sort_buffer_size参数的设置。
增大max_length_for_sort_data参数的设置。
4. 提升order by速度
在使用order by时，不要用select *，只查询所需的字段。因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作。
增加sort_buffer_size。
增加max_length_for_sort_data。
5. 优化group by
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候，也要对sort_buffer_size和max_length_for_sort_data参数进行调整。注意where高于having，能写在where中的限定条件就不要去having限定了。
责任编辑：赵宁宁
来源：
今日头条 MySQL数据库ORDER BY 分享到微信 微信扫码分享 分享到微博 相关推荐 Oracle数据库排序ORDER BY子句的使用总结篇 本文我们主要对Oracle数据库排序时的一些情况进行了总结，以方便各位初学者学习参考，希望能够对您有所帮助。 2011-07-29 15:31:52 Oracle数据库ORDER BY 关于MySQL数据库索引和ORDER BY子句的使用问题简介 本文主要介绍了MySQL数据库中的索引和OrderBy子句的使用的一些问题，希望能够对您有所帮助。 2011-07-28 16:16:27 MySQL数据库索引ORDER BY 数据库MySQL索引优化分析 为什么你写的sql查询慢？为什么你建的索引常失效？通过本章内容，你将学会MySQL性能下降的原因，索引的简介，索引创建的原则，explain命令的使用，以及explain输出字段的意义。助你了解索引，分析索引，使用索引，从而写出更高性能的sql语句。还在等啥子？卷起袖子就是干！ 2018-06-26 15:58:06 数据库MySQL索引优化 常用的数据库索引优化语句总结 在我们编写的SQL语句中，不正确地使用索引列可能会导致索引不被使用，而进行全表扫描，极大地降低了数据库的性能。因此，学习正确的索引的使用方法实在是很有必要的。 2016-12-12 13:07:57 数据库优化SQL MySQL性能优化，MySQL索引优化，order by优化，explain优化 今天我们来讲讲如何优化MySQL的性能，主要从索引方面优化。 2020-10-19 19:45:58 MySQL数据库优化 MySQL数据库优化 MySQL数据库的发展技术是非常先进的，这就源于MySQL数据库优化的贡献，数据库优化使MySQL数据库更为完善，下面就带着大家了解了解MySQL数据库的优化。 2011-03-03 17:56:52 MySQL数据库优化 MySQL索引与数据库高效运行优化实操 我们今天主要和大家一起分享的是MySQL索引的详细介绍，以及MySQL数据库高效运行优化的实际操作，以下就是文章的具体内容描述。 2010-05-21 12:15:52 MySQL数据库的优化（上）单机MySQL数据库的优化 公司网站访问量越来越大，导致MySQL的压力越来越大，让我们自然想到的对MySQL系统参数做优化。那么我们应该如何合理优化呢？笔者在下文中为大家介绍了单机MySQL数据库的优化。 2011-03-08 08:49:55 MySQL优化单机 MySQL数据库如何恰到好处地使用索引？ 本文简单地对MySQL数据库在什么情况下使用索引，在什么情况下不使用索引的情况进行了总结，希望能够对您有所帮助。 2011-08-05 09:15:27 MySQL数据库索引 MySQL数据库查询优化 总体上来说，对数据库查询的优化，我们采取了一些常规的优化之后，如果还没有取得想要的效果，我们有时候不必硬碰硬去优化查询本身，改变一下使用模式，找找业务处理流程是否还有可修改的，说不定就轻松解决了存在的难题。 2013-01-04 10:00:12 MySQL数据库数据库查询优化 MySQL数据库的基本结构与索引的优化方案 此文章主要向大家描述的是MySQL数据库的基本结构，MySQL数据库索引的优化方案与查询语句的优化这些内容，下面就是文章的主要内容描述。 2010-06-04 11:28:05 MySQL数据库 MySQL数据库移植总结 MySQL是一种关联数据库管理系统，它将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 2016-12-29 12:24:33 MySQL数据库移植 MySQL数据库设计总结 本文针对MySQL数据库设计做了19条规则总结，其中包括命名规则、数据库字段类型定义、合理构造Query语句等等。 2019-01-02 11:10:40 MySQL数据库数据库设计 关于数据库查询性能调优和索引优化的总结 本文我们主要对数据库查询性能调优和索引优化的知识进行了总结，了解这些知识有助于我们更好地提高SQL查询的性能，希望能够对您有所帮助。 2011-08-15 18:09:46 查询性能调优索引优化 数据库SQL优化大总结之 百万级数据库优化方案 网上关于SQL优化的教程很多，但是比较杂乱。近日有空整理了一下，写出来跟大家分享一下，其中有错误和不足的地方，还请大家纠正补充。 2014-07-18 09:33:53 数据库数据库优化 MySQL设置数据库为只读 默认情况下，我们的MySQL实例是可读写的。但有些情况下，我们可以将整个实例设置为只读状态，比如做迁移维护的时候或者将从库设为只读。本篇文章我们来看下MySQL设置只读相关知识。 2021-12-13 22:59:23 MySQL数据库SQL 从MySQL优化的角度来看：数据库回表与索引 本文从数据库MySQL优化的角度来看数据库的回表与索引，快来看看是否对你有帮助！ 2020-05-20 18:40:11 MySQL回表与索引数据库 优化体系 | 我是怎么计算MySQL数据库索引长度的？ 我们知道MySQLInnodb对于索引长度的限制为767字节，并且UTF8mb4字符集是4字节字符集，则767字节4字节每字符191字符(默认索引最大长度)，所以在varchar(255)或char(255)类型字段上创建索引会失败，提示最大索引长度为767字节。 2019-07-23 09:40:42 MySQL数据库索引数据结构 MySQL数据库Audit插件的使用技巧总结 本文详细介绍了MySQL数据库Audit插件的一些使用技巧，希望能给读者带来收获。 2011-07-19 16:36:25 Audit插件MySQL数据库 超详细MySQL数据库优化 数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷. 2019-04-02 10:36:17 数据库MySQL优化方法 相似话题 Oracle 2671内容 SQL Server 2747内容 MariaDB 67内容 PostgreSQL 179内容 全部话题 同话题下的热门内容 MySQL主从复制太慢，怎么办？一文带你掌握 Redis如果查询条件没有索引字段的话，是加「行锁」还是加「表锁」？InnoDB数据存储及事务两阶段提交原理解析MySQL中这14个小玩意，让人眼前一亮！！！ 编辑推荐 值得关注的MySQL高可用方案为什么说MySQL单表行数不要超过2000w?MySQL 连接怎么保活？我去面试聊了半天MySQL索引，结果面试官黑脸让我回家等结果...面试官问我MySQL索引失效怎么排查？懵逼了 相关专题 更多 勒索攻击离我们远吗？ Cortex 赋能自动化安全运营 2022-09-29 10:56:57 智算智存 全能担当!中小企业共享存储一站式解决方案 2022-12-22 16:54:11 我收藏的内容 微博 QQ 微信 复制链接 微信扫码分享 51CTO业务 媒体 51CTOCIOAgeHC3iTechplur社区 51CTO博客开源基础软件社区汽车开发者社区教育 51CTO学堂精培企业培训CTO训练营 51CTO学堂 51CTO学堂企业版 51CTO官微 51CTO 关于我们&条款 关于我们 站点地图 网站大事 意见反馈 English 用户协议 隐私协议
北京市海淀区中关村南1条甲1号ECO中科爱克大厦6-7层
北京市公安局海淀分局备案编号：110108002980号
营业执照
京ICP备09067568号
Copyright © 2005-2022 51CTO.COM
京ICP证060544 版权所有 未经许可 请勿转载
友情链接 新浪科技腾讯科技网易科技凤凰科技驱动科技科技行者TechWeb艾瑞网站长之家速途网中国经济新闻网IT之家工联网极客公园236视频会议中国IDC圈企业网D1Net投资界 51CTO技术栈公众号 51CTO技术栈公众号
业务
速览
在线客服 媒体 51CTO CIOAge HC3i 社区 51CTO博客 开源基础软件社区 教育 51CTO学堂 精培 企业培训 CTO训练营