MySQL——优化ORDER BY语句

MySQL——优化ORDER BY语句

本篇文章我们将了解ORDER BY语句的优化,在此之前,你需要对索引有基本的了解,不了解的老少爷们可以先看一下我之前写过的索引相关文章。现在让我们开始吧。

MySQL中的两种排序方式

1.通过有序索引顺序扫描直接返回有序数据

因为索引的结构是B+树,索引中的数据是按照一定顺序进行排列的,所以在排序查询中如果能利用索引,就能避免额外的排序操作。EXPLAIN分析查询时,Extra显示为Using index。

2.Filesort排序,对返回的数据进行排序

所有不是通过索引直接返回排序结果的操作都是Filesort排序,也就是说进行了额外的排序操作。EXPLAIN分析查询时,Extra显示为Using filesort。

ORDER BY优化的核心原则

尽量减少额外的排序,通过索引直接返回有序数据。

ORDER BY优化实战

用于实验的customer表的索引情况:

首先要注意:

MySQL一次查询只能使用一个索引,如果要对多个字段使用索引,建立复合索引。

ORDER BY优化

1.查询的字段,应该只包含此次查询使用的索引字段和主键,其余的非索引字段和索引字段作为查询字段则不会使用索引。

只查询用于排序的索引字段,可以利用索引排序:

explainselectstore_id,emailfromcustomer orderbystore_id,email;

但是要注意,排序字段在多个索引中,无法使用索引排序,查询一次只能使用一个索引:

explainselectstore_id,email,last_namefromcustomer orderbystore_id,email,last_name;

只查询用于排序的索引字段和主键,可以利用索引排序:

画外音:MySQL默认的InnoDB引擎在物理上采用聚集索引这种方式,按主键进行搜索,所以InnoDB引擎要求表必须有主键,即使没有显式指定主键,InnoDB引擎也会生成唯一的隐式主键,也就是说索引中必定有主键。

explainselectcustomer_id,store_id,emailfromcustomer orderbystore_id,email;

查询用于排序的索引字段和主键之外的字段,不会利用索引排序:

explainselectstore_id,email,last_namefromcustomer orderbystore_id,email;

explainselect*fromcustomer orderbystore_id,email;

WHERE + ORDER BY 优化

1.排序字段在多个索引中,无法利用索引排序

排序字段在多个索引(不在同一个索引)中,无法利用索引排序:

explainselect*fromcustomerwherelast_name='swj'orderbylast_name,store_id;

画外音:当排序字段不在同一个索引时,无法满足在一颗B+树中完成排序,必须再进行一次额外的排序

排序字段在一个索引中,并且WHERE条件和ORDER BY使用相同的索引,可以利用索引排序:

explainselect*fromcustomerwherelast_name='swj'orderbylast_name;

当然组合索引也可以利用索引排序:

注意字段store_id,email在一个组合索引中

explainselect*fromcustomerwherestore_id=5orderbystore_id,email;

2.排序字段顺序与索引列顺序不一致,无法利用索引排序

画外音:这条是针对组合索引而言的,我们都知道使用组合索引必要要遵循最左原则,WHERE子句必须有索引中第一列,虽然ORDER BY子句没有这个要求,但是也要求排序字段顺序和组合索引列顺序匹配。我们平常在使用组合索引的时候,一定要养成按照组合索引列顺序书写的好习惯。

排序字段顺序与索引列顺序不一致,无法利用索引排序:

explainselect*fromcustomerwherestore_id>5orderbyemail,store_id;

应该确保排序字段顺序与索引列顺序一致,这样可以利用索引排序:

explainselect*fromcustomerwherestore_id>5orderbystore_id,email;

ORDER BY子句不要求必须索引中第一列,没有仍然可以利用索引排序。但是有个前提条件,只有在等值过滤时才可以,范围查询时不可以

explainselect*fromcustomerwherestore_id=5orderbyemail;

explainselect*fromcustomerwherestore_id>5orderbyemail;

画外音:

其原因其实也很简单,范围查询时,第一列a肯定是排序好的(默认是升序),而第二个字段b其实就不是排序的了。但是如果a字段有相同的值时,那么b字段就是排序的了。所以如果是范围查询,就只能对b做一次额外的排序。

3.升降序不一致,无法利用索引排序

ORDER BY排序字段要么全部正序排序,要么全部倒序排序,否则无法利用索引排序。

explainselect*fromcustomerwherestore_id>5orderbystore_id,email;

explainselect*fromcustomerwherestore_id>5orderbystore_id desc,email desc;

explainselect*fromcustomerwherestore_id>5orderbystore_id desc,email asc;

总结:

上面的优化其实可以汇总为:WHERE条件和ORDER BY使用相同的索引,并且ORDER BY的顺序和索引顺序相同,并且ORDER BY的字段都是升序或者降序。否则肯定需要额外的排序操作,就会出现Filesort。

Filesort优化

通过创建合适的索引能够减少Filesort的出现,但是在某些情况下,无法完全让Filesort消失,此时只能想办法加快Filesort的操作。

Filesort的两种排序算法:

1.两次扫描算法

首先根据条件取出排序字段和行指针信息,之后在排序区sort buffer中排序。这种排序算法需要访问两次数据,第一次获取排序字段和行指针信息,第二次根据行指针获取记录,第二次读取操作可能会导致大量随即I/O操作。优点是排序的时候内存开销较小。

2.一次扫描算法

一次性取出满足条件的行的所有字段,然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比较大,但是排序效率比两次扫描算法要高。

根据两种排序算法的特性,适当加大系统变量maxlengthforsortdata的值,能够让MySQL选择更优化的Filesort排序算法。并且在书写SQL语句时,只使用需要的字段,而不是SELECT * 所有的字段,这样可以减少排序区的使用,提高SQL性能。

原文发布于微信公众号 - 撸码那些事(lumanxs)

原文发表时间:2018-08-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏祥子的故事

sql | 基础总结 | 思维导图

42060
来自专栏木子昭的博客

<导图>Mysql常用查询语法

普通查询 查看整个表 格式: select * from 表名; 示例: select * from students; 查询指定字段 格式 ...

33870
来自专栏Dawnzhang的开发者手册

数据库截取字符串SUBSTR函数的使用

今天中午做需求的时候,有类似于根据银行卡卡号的前几位判断出是哪个银行的情况,每个银行需要截取的位数都不一样,这时我就想到了SUBSTR

12020
来自专栏Java成神之路

Oracle学习笔记_02_基本SQL

        SQL 语言大小写不敏感。         SQL 可以写在一行或者多行 关键字不能被缩写也不能分行         各子句一般要分行写。 ...

9420
来自专栏Python爬虫实战

MySQL从零开始:05 MySQL数据类型

距离上次更新 MySQL 从零开始系列,已经过去了十几天,时间隔得有点长,由于我选用的是 MySQL 的最新版本,网上的教程大多停留在 MySQL 5.x,所以...

15930
来自专栏xcywt

学习SQLite之路(二)

  下面就是真正关于数据库的一些知识了: 20160614更新  参考: http://www.runoob.com/sqlite/sqlite-tutoria...

19870
来自专栏杨建荣的学习笔记

oracle中的数组(第一篇)(r4笔记第9天)

数组在各种编程语言中都是很重要的数据结构实现,在oracle中也有自己的一席之地。自己简单做了几个实验,发现很多东西还是眼高手低,真实去做的时候,里面还是有不少...

31760
来自专栏一个爱吃西瓜的程序员

学习SQL【7】-函数

终于可以开原创标识和留言功能了,开心。我坚信努力总会有收获的。 不仅SQL, 对所有的编程语言来说,函数都起着至关重要的作用。函数就像是编程语言的“道具箱”...

365120
来自专栏互联网开发者交流社区

用于 SELECT 和 WHERE 子句的函数

15530
来自专栏闻道于事

Hibernate框架HQL语句

这篇随笔将会记录hql的常用的查询语句,为日后查看提供便利。 在这里通过定义了三个类,Special、Classroom、Student来做测试,Special...

30950

扫码关注云+社区

领取腾讯云代金券