汽车音响网站建设,长沙seo推广优化,品牌全案策划设计公司,河南省城乡和住房建设厅网站文章目录1.ORDEY BY排序查询优化方面的概念2.ORDER BY排序的优化原则3.ORDER BY排序优化的案例3.1.准备排序优化的表以及索引3.2.同时对nl和lxfs字段使用升序排序3.3.同时对nl和lxfs字段使用降序排序3.4.排序时调整联合索引中字段的位置顺序3.5.排序时一个字段使用升序一个字段…
文章目录1.ORDEY BY排序查询优化方面的概念2.ORDER BY排序的优化原则3.ORDER BY排序优化的案例3.1.准备排序优化的表以及索引3.2.同时对nl和lxfs字段使用升序排序3.3.同时对nl和lxfs字段使用降序排序3.4.排序时调整联合索引中字段的位置顺序3.5.排序时一个字段使用升序一个字段使用降序3.6.解决两个字段不同排序规则出现Using filesort4.ORDER BY多字段多排序方式的索引结构图1.ORDEY BY排序查询优化方面的概念
在MySQL数据库中有两种排序类型
Using filesort 通过表的索引或者全表扫描读取满足条件的数据行然后在排序缓冲区sortbuffer中完成排序的操作所有不是通过索引直接返回的排序结果都是filesort排序效率相对来说比较低。索引本身具有排序的功能如果索引的排序没有满足我们的排序条件此时读取到满足条件的数据后还需要在缓冲区中重新排序最后完成排序的操作这类的排序都是filesort。就和回表查询一样本来可以在二级索引中拿到数据但是还需要走回表查询才能完成需求filesort也一样如果不能再索引排序中拿到结果就需要在缓冲区中再次排序。 Using index 通过有序索引排序扫描后直接就能返回有序的数据满足排序需求这类的排序都是using index不需要额外的排序效率很高。索引排序默认是ASC升序排序如果我们的排序规则都是升序并且排序的字段也是索引字段此时就是using index排序。
对于这两种排序方式Using index的性能最高Using filesort的性能较低我们再优化排序操作时尽量的使用Using index。
索引的默认排序规则是升序排序下面来观察几种排序需求看看哪些是Using filesort那些是Using index。 当排序的字段都是升序时排序方式就是Using index。 当排序的字段都是降序时排序方式就是Using index因为还是在一个索引排序集里只不过反向排序即可。 当排序的字段一个是升序、一个是降序此时就会出现Using filesort因为第一个字段是升序也是默认的规则直接返回排序即可但是第二个字段是降序相当于又要重新读取一遍数据然后拿到缓冲区里再次排序。
2.ORDER BY排序的优化原则
将需要排序的字段建立联合索引多字段排序时也是需要遵循最左前缀法则的。查询返回的字段尽量使用覆盖索引的字段此时不需要回表查询效率很高。多字段排序时如果一个字段升序、一个字段降序那么就在创建联合索引时为字段设置排序规则。如果不能避免出现filesort那么可以适当的增大排序缓冲区的大小参数是sort_buffer_size默认为256k。
3.ORDER BY排序优化的案例
3.1.准备排序优化的表以及索引
1表数据 2索引
我们主要以nl和lxfs两个字段排序将这两个字段创建一个联合索引。
mysql create index idx_user_nl_lxfs on tb_user(nl,lxfs);3.2.同时对nl和lxfs字段使用升序排序
同时对nl和lxfs字段进行升序排序观察执行计划中排序方式是什么。
mysql explain select nl,lxfs from tb_user order by nl,lxfs;根据执行计划的输出我们可以看到同时对nl和lxfs字段进行升序排序时都走了索引排序方式是Using index此时的效率最高。 当然如果我们只根据nl排序排序方式也是Using index因为nl字段也在联合索引中。 3.3.同时对nl和lxfs字段使用降序排序
同时对nl和lxfs字段进行降序排序观察执行计划中的排序方式是什么。
mysql explain select nl,lxfs from tb_user order by nl desc,lxfs desc;根据执行计划的输出我们可以看到也是走的Using index但是还多了个一个Backward index scan这个代表使用了反向扫描索引因为我们使用的降序排序默认是升序排序叶子节点从小到大排序所以就会反向扫描整个索引结构。 3.4.排序时调整联合索引中字段的位置顺序
我们在排序时调整一下排序字段的位置顺序在创建联合索引时nl字段在最左侧lxfs字段在右侧。
在3.2.中排序时nl字段在左侧lxfs字段在右侧观察会有什么样的排序方式。
mysql explain select nl,lxfs from tb_user order by lxfs,nl;观察执行计划的输出我们可以看到既出现了Using index又出现了Using filesort原因就是调整了索引字段的位置nl字段是最左前缀我们将lxfs放在最前面相当于违背了最左前缀法则就会出现Using filesort。 3.5.排序时一个字段使用升序一个字段使用降序
排序时一个字段采用升序排序一个字段使用降序排序观察效果。
mysql explain select nl,lxfs from tb_user order by nl asc,lxfs desc;观察执行计划的输出我们可以看到既出现了Using index又出现了Using filesort那么为什么会出现这样的现象呢原因就是nl字段使用升序排序没问题只需要在索引结构中顺序拿到结果但是lxfs字段是降序排序只能拿着结果去排序缓冲区中再进行降序排序最终拿到结果。 3.6.解决两个字段不同排序规则出现Using filesort
针对3.5中排序后的现象两个字段不同的排序规则索引默认是升序排序nl字段的升序排序没有任何效率问题但是lxfs字段当下拿到的是nl字段升序后的结果集还想要对lxfs字段降序排序只能拿到排序缓冲区中进行额外的排序因为在nl字段升序后的结果集里lxfs字段也是升序的但是我们要的是降序的效果所以只能额外排序了。
想要解决这个问题其实非常简单只要能够实现一个索引结构里不同字段有不同的排序方式就行了例如nl字段在索引结构里走升序排序lxfs字段在索引结构里走降序就可以完美解决了。
不同字段的排序方式可以在创建索引时指定即可。
mysql create index idx_user_nl_lxfs_ad on tb_user (nl asc,lxfs desc);Column_name一列是该索引字段的排序规则A表示asc升序排序D表示desc降序排序。 创建完索引后我们再执行相同的SQL观察执行计划。
mysql explain select nl,lxfs from tb_user order by nl asc,lxfs desc;观察执行计划此时已经是Using index了。 4.ORDER BY多字段多排序方式的索引结构图
为不同的字段设置了不同的排序规则后在索引的叶子节点里就已经排序好了上面图的所有字段排序规则都是升序下面图中lxfs字段是降序可以看到每个索引元素中手机号都是降序排序了。
