下边将通过实例解析二种排序落成形式及完结图解:要是有 Table A 和 B
四个表结构分别如下:sky@localhost : example 01:48:21> show create
table AG*************************** 1. row
***************************Table: ACreate
Table: CREATE TABLE `A` NOT NULL default ‘0′,`c2` char default
NULL,`c3` varchar default NULL,`c4` datetime default NULL,PRIMARY
KEY ENGINE=MyISAM DEFAULT CHARSET=utf8

 总体上看,在 MySQL 中的OKoleosDER
BY有二种排序完毕方式,黄金年代种是接纳有序索引获取有序数据,另黄金时代种则是因此相应的排序算法,将拿到的数额在内部存款和储蓄器中开展排序。

sky@localhost : example 01:48:32> show create table
BG*************************** 1. row
***************************Table: BCreate
Table: CREATE TABLE `B` NOT NULL default ‘0′,`c2` char default
NULL,`c3` varchar default NULL,PRIMARY KEY ,KEY `B_c2_ind`
ENGINE=MyISAM DEFAULT CHARSET=utf8

  上边将通超过实际例深入分析二种排序实现方式及得以完结图解:

1、利用有序索引举办排序,实际上就是当我们 Query 的 O奥德赛DEENCORE BY 条件和 Query
的实行安插中所利用的 Index 的索引键完全风流倜傥致,且索引访谈格局为 rang、 ref
大概 index 的时候,MySQL
能够动用索引顺序而直接获得已经排好序的数额。这种方法的 OPRADODESportage BY
基本上能够说是最优的排序格局了,因为 MySQL 无需张开实际的排序操作。

  要是有 Table A 和 B 五个表结构分别如下:

假若大家在Table A 和 B 上试行如下SQL:sky@localhost : example
01:44:28> EXPLAIN SELECT A.* FROM A,B-> WHERE A.c1 > 2 AND
A.c2 < 5 AND A.c2 = B.c2 ORDER BY
A.c1G*************************** 1. row
***************************id: 1select_type:
SIMPLEtable: Atype: rangepossible_keys: PRIMARYkey: PRIMARYkey_len:
4ref: NULLrows: 3Extra: Using
where*************************** 2. row
***************************id: 1select_type:
SIMPLEtable: Btype: refpossible_keys: B_c2_indkey:
B_c2_indkey_len: 7ref: example.A.c2rows: 2Extra: Using where; Using
index

  1   [email protected] : 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   [email protected] : 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

咱俩经过实行布置得以见到,MySQL实际上并从未进展实际的排序操作,实际上其整个实践进度如下图所示:

  1、利用有序索引进行排序,实际上就是当大家 Query 的 OKoleosDE讴歌MDX BY 条件和
Query 的举办安插中所利用的 Index
的索引键(或前边多少个索引键)完全黄金时代致,且索引访谈格局为 rang、 ref 或者index 的时候,MySQL
能够利用索引顺序而直接得到已经排好序的数据。这种方法的 OEscortDEKoleos BY
基本上能够说是最优的排序格局了,因为 MySQL 不必要开展实际的排序操作。

2、通过相应的排序算法,将获取的数额在内部存储器中举办排序方式,MySQL
比须求将数据在内部存款和储蓄器中张开排序,所运用的内存区域相当于大家透过
sort_buffer_size 系统变量所设置的排序区。这几个排序区是种种 Thread
独享的,所以说可能在相通期刻在 MySQL 中或然存在三个 sort buffer
内部存款和储蓄器区域。

 要是大家在Table A 和 B 上举行如下SQL:

第二种方法在 MySQL Query Optimizer 所付出的实行陈设中被称之为
filesort。在这里种措施中,首假若由于并未有得以应用的稳步索引拿到有序的数目,MySQL只好因此将得到的数码在内部存款和储蓄器中开展排序然后再将数据再次回到给客户端。在
MySQL 中 filesort
的兑现算法实际上是有三种的,少年老成种是率先依据对应的法则收取相应的排序字段和能够直接固定行数据的行指针音讯,然后在
sort buffer 中实行排序。别的风度翩翩种是三次性收取满意条件行的全体字段,然后在
sort buffer 中进行排序。

  1   [email protected] : 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

在 MySQL4.1 版本在此以前独有首先种排序算法,第三种算法是从
MySQL4.1发端的修正算法,首要目的是为了减小第一遍算法中供给四次访谈表数据的
IO 操作,将四回成为了三遍,但对应也会耗用越多的 sort buffer
空间。当然,MySQL4.1始发的未来全数版本同不常候也支撑第后生可畏种算法,MySQL
首要通过相比较我们所设定的系统参数 max_length_for_sort_data 的轻重和
Query 语句所收取的字段类型大小总和来判别须求动用哪风姿罗曼蒂克种排序算法。假设max_length_for_sort_data
更加大,则动用第三种优化后的算法,反之使用第生龙活虎种算法。所以借使指望 OSportageDER
BY 操作的频率尽大概的高,一定要理念 max_length_for_sort_data
参数的设置。曾经就有同事的数据库现身多量的排序等待,产生系统负荷超级高,並且响适那时候间变得不长,最终查出正是因为
MySQL 使用了思想的首先种排序算法而招致,在加大了
max_length_for_sort_data
参数值之后,系统负荷立时获得了大的解决,响应也快了大多。

  大家由此实施布署得以看见,MySQL实际上并不曾打开实际的排序操作,实际上其任何试行进程如下图所示:

小编们再看看 MySQL 供给利用 filesort 实现排序的实例。

  

倘诺大家退换一下我们的
Query,换到通过A.c2来排序,再看看意况:sky@localhost : example
01:54:23> EXPLAIN SELECT A.* FROM A,B-> WHERE A.c1 > 2 AND
A.c2 < 5 AND A.c2 = B.c2 ORDER BY
A.c2G*************************** 1. row
***************************id: 1select_type:
SIMPLEtable: Atype: rangepossible_keys: PRIMARYkey: PRIMARYkey_len:
4ref: NULLrows: 3Extra: Using where; Using
filesort*************************** 2. row
***************************id: 1select_type:
SIMPLEtable: Btype: refpossible_keys: B_c2_indkey:
B_c2_indkey_len: 7ref: example.A.c2rows: 2Extra: Using where; Using
index

图片 1

MySQL 从 Table A 中抽取了相符条件的数码,由于得到的数额并不满意 OHavalDEEvoque BY
条件,所以 MySQL 举行了 filesort 操作,其总体实施进度如下图所示:

  2、通过相应的排序算法,将获得的数目在内存中张开排序格局,MySQL
比需求将数据在内部存储器中开展排序,所采纳的内部存款和储蓄器区域也正是大家透过
sort_buffer_size 系统变量所设置的排序区。那几个排序区是种种 Thread
独享的,所以说或许在长期以来时刻在 MySQL 中恐怕存在四个 sort buffer
内部存款和储蓄器区域。

在 MySQL 中,filesort
操作还会有叁个相比较奇异的节制,那正是其数据源必得是缘于三个Table,所以,倘若大家的排序数据假设是两个 Table 通过 Join所得出的,那么
MySQL
必得通过先创设二个一时表,然后再将此有时表的数额开展排序,如下例所示:

  第三种方法在 MySQL Query Optimizer 所提交的试行安顿(通过 EXPLAIN
命令查看)中被喻为
filesort。在此种办法中,首即便由于尚未得以行使的安如泰山索引获得有序的数量,MySQL只好通过将赢得的数据在内部存款和储蓄器中开展排序然后再将数据重返给客商端。在
MySQL 中 filesort
的兑现算法实际上是有两种的,生龙活虎种是首先依据对应的法规收取相应的排序字段和能够直接固定行数据的行指针音信,然后在
sort buffer 中展开排序。其余少年老成种是一回性抽出满足条件行的装有字段,然后在
sort buffer 中张开排序。

sky@localhost : example 02:46:15> explain select A.* from A,B->
where A.c1 > 2 and A.c2 < 5 and A.c2 = B.c2 order by
B.c3G*************************** 1. row
***************************id: 1select_type:
SIMPLEtable: Atype: rangepossible_keys: PRIMARYkey: PRIMARYkey_len:
4ref: NULLrows: 3Extra: Using where; Using temporary; Using
filesort*************************** 2. row
***************************id: 1select_type:
SIMPLEtable: Btype: refpossible_keys: B_c2_indkey:
B_c2_indkey_len: 7ref: example.A.c2rows: 2Extra: Using where

  在 MySQL4.1 版本早前唯有首先种排序算法,第二种算法是从
MySQL4.1方始的校勘算法,重要指标是为了裁减第贰回算法中要求三次访谈表数据的
IO 操作,将五次成为了一次,但对应也会耗用更加多的 sort buffer
空间。当然,MySQL4.1开首的之后全体版本同期也支撑第意气风发种算法,MySQL
首要通过相比较大家所设定的系统参数 max_length_for_sort_data 的轻重和
Query 语句所收取的字段类型大小总和来剖断须求采纳哪风姿罗曼蒂克种排序算法。假设max_length_for_sort_data
更加大,则应用第三种优化后的算法,反之使用第生机勃勃种算法。所以要是愿意 OHighlanderDER
BY 操作的频率尽只怕的高,一定要思想 max_length_for_sort_data
参数的装置。曾经就有同事的数据库现身大批量的排序等待,变成系统负荷异常高,并且响适当时候间变得十分短,末了查出正是因为
MySQL 使用了金钱观的第后生可畏种排序算法而以致,在加大了
max_length_for_sort_data
参数值之后,系统负荷立时获得了大的消除,响应也快了广大。

其风度翩翩履行布置的输出依然有一点点意想不到的,不知晓为何,MySQL Query Optimizer 将
“Using temporary” 进度彰显在率先行对 Table A
的操作中,难道只是为让施行计划的出口少后生可畏行?

  大家再看看 MySQL 要求运用 filesort 完毕排序的实例。

网站地图xml地图