当前位置:首页 > mysql > 正文内容

mysql中order by的实现与优化

phpmianshi4年前 (2017-04-21)mysql206

概念


在 MySQL 中,ORDER BY 的实现有如下两种类型:

◆ 一种是通过有序索引而直接取得有序的数据,这样不用进行任何排序操作即可得到满足客户端 要求的有序数据返回给客户端;

◆ 另外一种则需要通过 MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回给客户端;


示例


首先分析一下第一种不用排序的实现方式


EXPLAIN SELECT m.id,m.subject,c.content 
FROM group_message m,group_message_content c 
WHERE m.group_id = 1 AND m.id = c.group_msg_id 
ORDER BY m.user_id\G

我们只列重点:

*************************** 1. row ***************************

key: idx_group_message_gid_uid

Extra: Using where


明明有 ORDER BY user_id,为什么在执行计划中却没有排序操作呢?其实这里正是因为 MySQL Query Optimizer 选择了一个有序的索引来进行访问表中的数据 (idx_group_message_gid_uid),这样,我们通过 group_id 的条件得到的数据已经是按照 group_id 和 user_id 进行排序的了。而虽然我们的排序条件仅仅只有一个 user_id,但是我们的 WHERE 条件决定 了返回数据的 group_id 全部一样,也就是说不管有没有根据 group_id 来进行排序,返回的结果集都是完全一样的。

我们可以通过如下的图示来描述整个执行过程:

图片.png

图中的 Table A 和 Table B 分别为上面 Query 中的 group_message 和 gruop_message_content 这两个表。


这种利用索引实现数据排序的方法是 MySQL 中实现结果集排序的最佳做法,可以完全避免因为排序 计算所带来的资源消耗。所以,在我们优化 Query 语句中的 ORDER BY 的时候,尽可能利用已有的索引来避免实际的排序计算,可以很大幅度的提升 ORDER BY 操作的性能。在有些 Query 的优化过程中,即使为了避免实际的排序操作而调整索引字段的顺序,甚至是增加索引字段也是值得的。当然,在调整索引之前,同时还需要评估调整该索引对其他 Query 所带来的影响,平衡整体得失。


如果没有索引利用的时候,MySQL 又如何来实现排序呢?MySQL 目前可以通过两种算法来实现数据的排序操作。

1. 取出满足过滤条件的用于排序条件的字段以及可以直接定位到行数据的行指针信息,在 Sort Buffer 中进行实际的排序操作,然后利用排好序之后的数据根据行指针信息返回表中取得客户端请 求的其他字段的数据,再返回给客户端;

2. 根据过滤条件一次取出排序字段以及客户端请求的所有其他字段的数据,并将不需要排序的字 段存放在一块内存区域中,然后在 Sort Buffer 中将排序字段和行指针信息进行排序,最后再利用 排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配合并结果集,再按照顺 序返回给客户端。


上面第一种排序算法是 MySQL 一直以来就有的排序算法,而第二种则是从 MySQL4.1 版本才开始增 加的改进版排序算法。第二种算法与第一种相比较,主要优势就是减少了数据的二次访问。在排序之后不需要再一次回到表中取数据,节省了 IO 操作。当然,第二种算法会消耗更多的内存,正是一种典型的通过内存空间换取时间的优化方式。下面我们同样通过一个实例来看看当 MySQL 不得不使用排序算法 的时候的执行计划,仅仅只是更改一下排序字段:

explain select m.id,m.subject,c.content 
FROM group_message m,group_message_content c
WHERE m.group_id = 1 AND m.id = c.group_msg_id 
ORDER BY m.subject\G

只列重点:

*************************** 1. row ***************************

key: idx_group_message_gid_uid

Extra: Using where; Using filesort


Extra 信息中,多了一个“Using filesort”的信息,实际上这就是 MySQL Query Optimizer 在告诉我们,他需要进行排序操作才能按照客户端的要求返回有序的数据。

执行图如下:

图片.png

这里我们看到了,MySQL 在取得第一个表的数据之后,先根据排序条件将数据进行了一次 filesort,也就是排序操作。然后再利用排序后的结果集作为驱动结果集来通过 Nested Loop Join 访 问第二个表。当然,大家不要误解,这个 filesort 并不是说通过磁盘文件进行排序,仅仅只是告诉我 们进行了一个排序操作。


上面,我们看到了排序结果集来源仅仅只是单个表的比较简单的 filesort 操作。而在我们实际应 用中,很多时候我们的业务要求可能并不是这样,可能需要排序的字段同时存在于两个表中,或者 MySQL 在经过一次 Join 之后才进行排序操作。这样的排序在 MySQL 中并不能简单的里利用 Sort Buffer 进行排序,而是必须先通过一个临时表将之前 Join 的结果集存放入临时表之后再将临时表的数据取到Sort Buffer 中进行操作。下面我们通过再次更改排序要求来示例这样的执行计划,当我们选择 通过 group_message_content 表上面的 content 字段来进行排序之后:

explain select m.id,m.subject,c.content 
FROM group_message m,group_message_content c 
WHERE m.group_id = 1 AND m.id = c.group_msg_id 
ORDER BY c.content\G

只列重点:

*************************** 1. row ***************************

key: idx_group_message_gid_uid

Extra: Using temporary; Using filesort

这时候的执行计划中出现了“Using temporary”,正是因为我们的排序操作需要在两个表 Join 之 后才能进行,下图展示了这个 Query 的执行过程:

图片.png

首先是 Table A 和 Table B 进行 Join,然后结果集进入临时表,再进行 filesort,最后得到有序的结果集数据返回给客户端。


上面我们通过两个不同的示例展示了当 MySQL 无法避免要使用相应的排序算法进行排序操作的时候 的实现原理。虽然在排序过程中所使用的排序算法有两种,但是两种排序的内部实现机制大体上差不多。


总结


当我们无法避免排序操作的时候,我们又该如何来优化呢?很显然,我们应该尽可能让 MySQL 选择 使用第二种算法来进行排序。这样可以减少大量的随机 IO 操作,很大幅度的提高排序工作的效率。


1. 加大 max_length_for_sort_data 参数的设置;

  在 MySQL 中,决定使用第一种老式的排序算法还是新的改进算法的依据是通过参数 max_length_for_sort_data 来决定的。当我们所有返回字段的最大长度小于这个参数值的时候, MySQL 就会选择改进后的排序算法,反之,则选择老式的算法。所以,如果我们有充足的内存让 MySQL 存放需要返回的非排序字段的时候,可以加大这个参数的值来让 MySQL 选择使用改进版的排 序算法。


2. 去掉不必要的返回字段;

  当我们的内存并不是很充裕的时候,我们不能简单的通过强行加大上面的参数来强迫 MySQL 去 使用改进版的排序算法,因为如果那样可能会造成 MySQL 不得不将数据分成很多段然后进行排使用 序,这样的结果可能会得不偿失。在这种情况下,我们就需要去掉不必要的返回字段,让我们的返 回结果长度适应 max_length_for_sort_data 参数的限制。


3. 增大 sort_buffer_size 参数设置;

  增大 sort_buffer_size 并不是为了让 MySQL 可以选择改进版的排序算法,而是为了让 MySQL 可以尽量减少在排序过程中对需要排序的数据进行分段,因为这样会造成 MySQL 不得不使用临时表 来进行交换排序。

版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

innodb中统计数据是如何收集的

InnoDB 统计数据如何查看    1. 通过SHOW TABLE STATUS可以看到关于表的统计数据    2....

mysql中长事务详解

什么是长事务运行时间比较长,长时间未提交的事务,也可以称之为大事务。这类事务往往会造成大量的阻塞和锁超时,容易造成主从延迟,要尽量避免使用长事务。下面我将演示下如何开启事务及模拟长事务:#假设我们有一...

mysql中数据页的相关概念

mysql中数据页的相关概念

概念在 InnoDB 存储引擎中,所有的数据都被逻辑地存放在表空间中,表空间(tablespace)是存储引擎中最高的存储逻辑单位,在表空间的下面又包括段(segment)、区(extent)、页(p...

mysql中AnalyzeTable优化

Analyze TableMySQL的Optimizer(优化元件)在优化SQL语句时,首先需要收集一些相关信息,其中就包括表的cardinality(可以翻译为“散列程度”),它表示某个索引对应的列...

mysql中性能分析Profiling

mysql中性能分析Profiling

概念Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于Sql调优的测量。示例1、先查看是否开启了此功能,默认情况下,参数处于关闭状态,为OFF状态 ...

InnoDB体系结构及工作原理

InnoDB体系结构及工作原理

概念InnoDB主要包括了内存池、后台线程以及存储文件。INNODB的三大特性:插入缓存,两次写,自适应hash内存池又是由多个内存块组成的,主要包括Buffer Pool、redo log缓冲等,解...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。