MySQL Order By实现原理分析和Filesort优化

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

1、利用有序索引获取有序数据

2、文件排序

在使用explain分析查询的时候利用有序索引获取有序数据显示Using index。而文件排序显示Using filesort。

1.利用有序索引获取有序数据

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

          这种方式,在使用explain分析查询的时候,显示Using index。而文件排序显示Using filesort。

         注意:MySQL在查询时最多只能使用一个索引。因此,如果WHERE条件已经占用了索引,那么在排序中就不使用索引了。

1.1 按照索引对结果进行排序:order by 使用索引是有条件

1)  返回选择的字段,即只包括在有选择的此列上(select后面的字段),不一定适应*的情况):

CREATE TABLE `test` (  
  `id` int(11) NOT NULLAUTO_INCREMENT,  
  `rdate` datetime NOT NULL,  
  `inventid` int(11) NOT NULL,  
  `customerid` int(11) NOT NULL,  
  `staffid` int(11) NOT NULL,  
  `data` varchar(20) NOT NULL,  
  PRIMARY KEY (`id`),  
  UNIQUE KEY `rdate`(`rdate`,`inventid`,`customerid`),  
  KEY `inventid` (`inventid`),  
  KEY `customerid` (`customerid`),  
  KEY `staffid` (`staffid`)  
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1

如:

mysql>   
explain select inventid from test where rdate='2011-12-1400:00:00' order by  inventid , customerid;  

+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys |key    | key_len |ref      | rows |Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 |  SIMPLE      | test    |ref   |        rdate          |rdate  |      8     |const |   10   | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
Select选择的列使用索引,而下面不使用索引:
mysql> explain select * from test where rdate='2011-12-14 00:00:00'order by  inventid , customerid ;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key     | key_len|ref     | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | test   | ALL  | rdate               | NULL  | NULL    |NULL |  13   |Using where;Using filesort|
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

2)  只有当ORDER BY中所有的列必须包含在相同的索引,并且索引的顺序和order by子句中的顺序完全一致,并且所有列的排序方向(升序或者降序)一样才有,(混合使用ASC模式和DESC模式则不使用索引)

mysql>   
xplain select inventid from test order byrdate, inventid ;

+—-+————-+——-+——-+—————+——-+———+——+——+————-+

| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |

+—-+————-+——-+——-+—————+——-+———+——+——+————-+

|  1 | SIMPLE      | test  | index | NULL          | rdate |16      | NULL |   13 |Using index|

+—-+————-+——-+——-+—————+——-+———+——+——+————-+

1 row in set (0.00 sec)

mysql>   
explain select inventid from test where rdate="2011-12-16" order by  inventid ,staffid;

+—-+————-+——-+——+—————+——-+———+——-+——+————————–

| id | select_type | table | type | possible_keys |key   | key_len | ref   | rows | Extra                       |

+—-+————-+——-+——+—————+——-+———+——-+——+————————–

|  1 | SIMPLE      | test | ref  | rdate         | rdate | 8       | const |    1 |Using where;Using filesort

+—-+————-+——-+——+—————+——-+———+——-+——+————————–

1 row in set (0.00 sec)

由于rdate, inventid使用了同一个索引。排序使用到了索引。这个也是满足了前缀索引。但是order  by  inventid ,staffid;就不是使用了索引,因为staffid和inventid不是同一个索引

3)     where 语句与ORDER BY语句组合满足最左前缀:

  1. mysql>   
    explain select inventid from test whererdate="2011-12-16" order by  inventid ;

+—-+————-+——-+——+—————+——-+———+——-+——+————————–+

| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                    |

+—-+————-+——-+——+—————+——-+———+——-+——+————————–+

|  1 | SIMPLE      | test | ref  | rdate         | rdate | 8       | const |    1 | Using where;Using index |

+—-+————-+——-+——+—————+——-+———+——-+——+————————–+

1 row in set (0.00 sec)

4)     如果查询联接了多个表,只有在order by子句的所有列引用的是第一个表的列才可以。

5)     在其他的情况下,mysql使用文件排序  例如:

1)   where语句与order by语句,使用了不同的索引

2)   检查的行数过多,且没有使用覆盖索引

3)   ORDER BY中的列不包含在相同的索引,也就是使用了不同的索引

4)   对索引列同时使用了ASC和DESC

5)   where语句或者ORDER BY语句中索引列使用了表达式,包括函数表达式

6)   where 语句与ORDER BY语句组合满足最左前缀,但where语句中使用了条件查询。查见第10句,虽然where与order by构成了索引最左有缀的条件,但是where子句中使用的是条件查询

mysql> explain select inventid from test where  rdate>"2011-12-16" order by  inventid;

+—-+————-+——-+——-+—————+——-+———+——+——+—————-

| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                                    

+—-+————-+——-+——-+—————+——-+———+——+——+—————-

|  1 |SIMPLE      | test  | range | rdate         | rdate | 8       | NULL |    1 | Using where; Using index;Usingfilesort |

+—-+————-+——-+——-+—————+——-+———+——+——+—————-

1 row in set (0.00sec)

               7)   当使用left join,使用右边的表字段排序

2.文件排序

       这个 filesort 并不是说通过磁盘文件进行排序,而只是告诉我们进行了一个排序操作。即在MySQL Query Optimizer 所给出的执行计划(通过 EXPLAIN 命令查看)中被称为文件排序(filesort)

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

在MySQL中filesort 的实现算法实际上是有两种:

双路排序:是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息,然后在sort buffer 中进行排序。

单路排序:是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序。

在MySQL4.1版本之前只有第一种排序算法双路排序,第二种算法是从MySQL4.1开始的改进算法,主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作,将两次变成了一次,但相应也会耗用更多的sortbuffer 空间。当然,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 参数值之后,系统负载马上得到了大的缓解,响应也快了很多。

2.1 MySQL 需要使用filesort 实现排序的实例

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

# mysql
>show create table A\G
   *************************** 1. row ***************************
   Table: A
   Create Table: CREATE TABLE `A` (
   `id` int(11) NOT NULL default '0',
    `c2` char(2) default NULL,
   `c3` varchar(16) default NULL, 
   `c4` datetime default NULL, 
   PRIMARY KEY (`id`) 
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8
#:mysql
> show create table B\G
   *************************** 1. row ***************************    Table: B
   Create Table: CREATE TABLE `B` ( 
   `id` int(11) NOT NULL default '0', 
   `c2` char(2) default NULL,
   `c3` varchar(16) default NULL, 
   PRIMARY KEY (`id`),
   KEY `B_c2_ind` (`c2`)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8

A.c2不是索引将使用: 

sky@localhost : example 01:54:23> EXPLAIN SELECT A.* FROM A,B WHERE A.id >2 AND A.c2 <5 AND A.c2 = B.c2 ORDER BY A.c2\G
  *************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: A
  type: range
  possible_keys: PRIMARY
  key: PRIMARY
  key_len: 4
  ref: NULL
  rows: 3
  Extra: Using where; Using filesort
 
*************************** 2. row ***************************
  id: 1
  select_type: SIMPLE
  table: B
  type: ref
  possible_keys: B_c2_ind
  key: B_c2_ind
  key_len: 7
  ref: example.A.c2
  rows: 2
  Extra: Using where; Using index

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

1.gif

2.2 MySQL 需要使用Using temporary 临时表来filesort

         如果order by的子句只引用了联接中的第一个表,MySQL会先对第一个表进行排序,然后进行联接。也就是expain中的Extra的Using Filesort.否则MySQL先把结果保存到临时表(Temporary Table),然后再对临时表的数据进行排序.此时expain中的Extra的显示Using temporary Using Filesort.

例如如果我们的排序数据如果是两个(或者更多个) Table 通过 Join所得出的,如下例所示:

sky@localhost : example 02:46:15> explain select A.* from A,B
where A.id > 2 and A.c2 < 5 and A.c2 = B.c2 order by B.c3\G
  *************************** 1. row***************************
  id: 1
   select_type: SIMPLE
  table: A
  type: range
  possible_keys: PRIMARY
  key: PRIMARY
  key_len: 4
  ref: NULL
   rows: 3
Extra: Using where; Using temporary; Using filesort
  *************************** 2. row ***************************
  id: 1
  select_type: SIMPLE
  table: B
  type: ref
  possible_keys: B_c2_ind
  key: B_c2_ind
  key_len: 7
  ref: example.A.c2
  rows: 2
  Extra: Using where

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

2.gif 

 

3. 优化Filesort

当无法避免排序操作时,又该如何来优化呢?很显然,应该尽可能让 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 不得不使用临时表来进行交换排序。

转自:http://blog.csdn.net/hguisu/article/details/7161981

原创文章,作者:s19930811,如若转载,请注明出处:http://www.178linux.com/3114

(0)
s19930811s19930811
上一篇 2015-04-13
下一篇 2015-04-13

相关推荐

  • 2018近期it运维大会合集,这五场值得关注!

    17年,我们不仅看到Google、Facebook、Amazon、LinkedIn、Netflix、Airbnb等互联网巨头在与DevOps亲密接触,传统软件公司如Adobe、IBM、Microsoft、SAP等,亦或是网络业务非核心的苹果、沃尔玛、索尼影视娱乐、星巴克等都在采用DevOps,更看到了国内一大片企业开始钟情于此。

    2018-03-01
  • 魔术方法

    Edit 魔术方法 使用Python的魔术方法的最大优势在于他们提供了一种简单的方法来让对象可以表现的像内置类型一样。那意味着你可以避免丑陋的,违反直觉的,不标准的的操作方法。 特殊属性 属性 含义 __name__ 类、函数、方法等名字 __module__ 类定义所在的模块名? __class__ 对象或类所属的类 __bases__ 类的基类的元组,顺…

    Linux干货 2017-11-21
  • 【招聘福利】上海/北京/深圳丨 云络科技丨招募linux系统运维工程师

    岗位要求: 1,安装服务器 – Linux CentOS + netinstall 2.服务器安全 – 用户、权限、iptables、sudo 3.计划部署Linux?高可用性,包括HA,LB 4.协助Leader完成复杂的网络设计 (完全冗余/故障迁移/高可用性) 5.用linux命令查看服务器(top/iostat/vmstat/sar/等) 6.安装、…

    Linux干货 2016-04-05
  • CentOS7下利用rsyslog+loganalyzer配置日志服务器及Linux和windows客户端配置

    随着机房内的服务器和网络设备增加,日志管理和查询就成了让系统管理员头疼的事。 系统管理员遇到的常见问题如下: 1、日常维护过程中不可能登录到每一台服务器和设备上去查看日志; 2、网络设备上的存储空间有限,不可能存储日期太长的日志,而系统出现问题又有可能是很久以前发生的某些操作造成的; 3、在某些非法入侵的情况下,入侵者一般都会清除本地日志…

    2017-03-15
  • 脚本进阶笔记整理

    一、逻辑运算 变量:   本地变量、环境变量、局部变量、位置参数变量、特殊变量   变量赋值:name=value,export name=value,declare -x name=value   变量引用:$name,${name}   注意:有些时候{}不能省略,例如 &n…

    Linux干货 2017-03-26
  • CA服务器的搭建以及证书签署、dropbear的编译安装

    CA服务器的搭建以及证书签署、dropbear的编译安装 一、CA Server和Client: 1、CA server:创建私钥CA (1)   openssl的配置文件:/etc/pki/tls/openssl.conf   如果Client端的申请是来自不同的国家,则需要将下图中红色框内的三项,由“match”改为“opt…

    Linux干货 2016-09-23