注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

phperwuhan的博客

记载一个phper的历程!phperwuhan.blog.163.com

 
 
 

日志

 
 

MySQL 中的ORDER BY两种排序实现方式  

2009-10-15 16:54:34|  分类: mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

来源:http://www.phpchina.com/html/10/n-35010.html

总的来说,在 MySQL 中的ORDER BY有两种排序实现方式,一种是利用有序索引获取有序数据,另一种则是通过相应的排序算法,将取得的数据在内存中进行排序。

  下面将通过实例分析两种排序实现方式及实现图解:

  假设有TableA 和 B 两个表结构分别如下:

  1   sky@localhost : example 01:48:21> show create table A\G

  2

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

  4

  5   Table: A

  6

  7   Create Table: CREATE TABLE `A` (

  8

  9   `c1` int(11) NOT NULL default '0',

  10

  11   `c2` char(2) default NULL,

  12

  13   `c3` varchar(16) default NULL,

  14

  15   `c4` datetime default NULL,

  16

  17   PRIMARY KEY (`c1`)

  18

  19   ) ENGINE=MyISAM DEFAULT CHARSET=utf8

  20

  21   sky@localhost : example 01:48:32> show create table B\G

  22

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

  24

  25   Table: B

  26

  27   Create Table: CREATE TABLE `B` (

  28

  29   `c1` int(11) NOT NULL default '0',

  30

  31   `c2` char(2) default NULL,

  32

  33   `c3` varchar(16) default NULL,

  34

  35   PRIMARY KEY (`c1`),

  36

  37   KEY `B_c2_ind` (`c2`)

  38

  39   ) ENGINE=MyISAM DEFAULT CHARSET=utf8

  1、利用有序索引进行排序,实际上就是当我们 Query 的 ORDER BY 条件和 Query 的执行计划中所利用的 Index 的索引键(或前面几个索引键)完全一致,且索引访问方式为 rang、 ref 或者 index 的时候,MySQL 可以利用索引顺序而直接取得已经排好序的数据。这种方式的 ORDER BY 基本上可以说是最优的排序方式了,因为 MySQL 不需要进行实际的排序操作。

 假设我们在TableA 和 B 上执行如下SQL:

  1   sky@localhost : example 01:44:28> EXPLAIN SELECT A.* FROM A,B

  2

  3   -> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c1\G

  4

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

  6

  7   id: 1

  8

  9   select_type: SIMPLE

  10

  11   table: A

  12

  13   type: range

  14

  15   possible_keys: PRIMARY

  16

  17   key: PRIMARY

  18

  19   key_len: 4

  20

  21   ref: NULL

  22

  23   rows: 3

  24

  25   Extra: Using where

  26

  27   *************************** 2. row ***************************

  28

  29   id: 1

  30

  31   select_type: SIMPLE

  32

  33   table: B

  34

  35   type: ref

  36

  37   possible_keys: B_c2_ind

  38

  39   key: B_c2_ind

  40

  41   key_len: 7

  42

  43   ref: example.A.c2

  44

  45   rows: 2

  46

  47   Extra: Using where; Using index

  我们通过执行计划可以看出,MySQL实际上并没有进行实际的排序操作,实际上其整个执行过程如下图所示:

  

MySQL 中的ORDER BY两种排序实现方式 - phperwuhan - phperwuhan的博客

  2、通过相应的排序算法,将取得的数据在内存中进行排序方式,MySQL 比需要将数据在内存中进行排序,所使用的内存区也就是我们通过 sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread独享的,所以说可能在同一时刻在 MySQL 中可能存在多个 sort buffer 内存区域。

  第二种方式在 MySQL Query Optimizer 所给出的执行计划(通过 EXPLAIN 命令查看)中被称为 filesort。在这种方式中,主要是由于没有可以利用的有序索引取得有序的数据,MySQL只能通过将取得的数据在内存中进行排序然后再将数据返回给客户端。在 MySQL 中 filesort 的实现算法实际上是有两种的,一种是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息,然后在 sort buffer 中进行排序。另外一种是一次性取出满足条件行的所有字段,然后在 sort buffer 中进行排序。

  在 MySQL4.1 版本之前只有第一种排序算法,第二种算法是从 MySQL4.1开始的改进算法,主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作,将两次变成了一次,但相应也会耗用更多的 sort buffer 空间。当然,MySQL4.1开始的以后所有版本同时也支持第一种算法,MySQL 主要通过比较我们所设定的系统参数 max_length_for_sort_data 的大小和 Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data 更大,则使用第二种优化后的算法,反之使用第一种算法。所以如果希望 ORDER BY 操作的效率尽可能的高,一定要主义 max_length_for_sort_data 参数的设置。曾经就有同事的数据库出现大量的排序等待,造成系统负载很高,而且响应时间变得很长,最后查出正是因为 MySQL 使用了传统的第一种排序算法而导致,在加大了 max_length_for_sort_data 参数值之后,系统负载马上得到了大的缓解,响应也快了很多。

  我们再看看 MySQL 需要使用 filesort 实现排序的实例。

  假设我们改变一下我们的 Query,换成通过A.c2来排序,再看看情况:

  1   sky@localhost : example 01:54:23> EXPLAIN SELECT A.* FROM A,B

  2

  3   -> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c2\G

  4

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

  7   id: 1

  8

  9   select_type: SIMPLE

  10

  11   table: A

  12

  13   type: range

  14

  15   possible_keys: PRIMARY

  16

  17   key: PRIMARY

  18

  19   key_len: 4

  20

  21   ref: NULL

  22

  23   rows: 3

  24

  25   Extra: Using where; Using filesort

  26

  27   *************************** 2. row ***************************

  28

  29   id: 1

  30

  31   select_type: SIMPLE

  32

  33   table: B

  34

  35   type: ref

  36

  37   possible_keys: B_c2_ind

  38

  39   key: B_c2_ind

  40

  41   key_len: 7

  42

  43   ref: example.A.c2

  44

  45   rows: 2

  46

  47   Extra: Using where; Using index

  MySQL 从TableA 中取出了符合条件的数据,由于取得的数据并不满足 ORDER BY 条件,所以 MySQL 进行了 filesort 操作,其整个执行过程如下图所示:

  

MySQL 中的ORDER BY两种排序实现方式 - phperwuhan - phperwuhan的博客

  在 MySQL 中,filesort 操作还有一个比较奇怪的限制,那就是其数据源必须是来源于一个 Table,所以,如果我们的排序数据如果是两个(或者更多个) Table 通过 Join所得出的,那么 MySQL 必须通过先创建一个临时表(Temporary Table),然后再将此临时表的数据进行排序,如下例所示:

  1   sky@localhost : example 02:46:15> explain select A.* from A,B

  2

  3   -> where A.c1 > 2 and A.c2 < 5 and A.c2 = B.c2 order by B.c3\G

  4

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

  6

  7   id: 1

  8

  9   select_type: SIMPLE

  10

  11   table: A

  12

  13   type: range

  14

  15   possible_keys: PRIMARY

  16

  17   key: PRIMARY

  18

  19   key_len: 4

  20

  21   ref: NULL

  22

  23   rows: 3

  24

  25   Extra: Using where; Using temporary; Using filesort

     26

  27   *************************** 2. row ***************************

  28

  29   id: 1

  30

  31   select_type: SIMPLE

  32

  33   table: B

  34

  35   type: ref

  36

  37   possible_keys: B_c2_ind

  38

  39   key: B_c2_ind

  40

  41   key_len: 7

  42

  43   ref: example.A.c2

  44

  45   rows: 2

  46

  47   Extra: Using where

  这个执行计划的输出还是有点奇怪的,不知道为什么,MySQL Query Optimizer 将 “Using temporary” 过程显示在第一行对 Table A 的操作中,难道只是为让执行计划的输出少一行?

  实际执行过程应该是如下图所示:

  

MySQL 中的ORDER BY两种排序实现方式 - phperwuhan - phperwuhan的博客

TAG: mysql MySQL Mysql MYSQL MySQl Order ORDER

  评论这张
 
阅读(379)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017