mysql 减少文件排序_MySQL 文件排序 &索引排序

排序操作是非常消耗cpu的操作，当系统设置不当或query取出的字段过多时，还可以造成MySQL不得不放弃优化后的排序算法，而使用较为古老的需要两次IO的读取表数据的排序算法，使排序效率非常低下。

利用索引进行排序操作，主要是利用了索引的有序性。在通过索引进行检索的过程中，就已经得到了有序的数据访问顺序，依次读取结果数据后就不需要进行排序操作，进而避免了此操作，提高了排序结果集的query性能。

很简单，尽量使用索引排序，这就对了。

做个实验，表结构如下

mysql> show create table artist \G

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

Table: artist

Create Table: CREATE TABLE `artist` (

`artist_id` int(10) unsigned NOT NULL,

`type` enum('Band','Person','Unknown','Combination') NOT NULL,

`name` varchar(255) NOT NULL,

`gender` enum('Male','Female') DEFAULT NULL,

`founded` year(4) DEFAULT NULL,

`country_id` smallint(5) unsigned DEFAULT NULL,

PRIMARY KEY (`artist_id`),

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

使用文件排序：

mysql> Explain select name, founded from artist where name like 'AUSTRALIA%' order by founded \G

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

id: 1

select_type: SIMPLE

table: artist

type: range

possible_keys: name

key: name

key_len: 257

ref: NULL

rows: 22

Extra: Using index condition; Using filesort

1 row in set (0.00 sec)

查看结果：

mysql> show session status like '%sort%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Sort_merge_passes | 0 |

| Sort_range | 0 |

| Sort_rows | 0 |

| Sort_scan | 0 |

+-------------------+-------+

4 rows in set (0.00 sec)

mysql> select name, founded from artist where name like 'AUSTRALIA%' order by founded \G

......

22 rows in set (0.00 sec)

mysql> show session status like '%sort%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Sort_merge_passes | 0 |

| Sort_range | 1 |

| Sort_rows | 22 |

| Sort_scan | 0 |

+-------------------+-------+

4 rows in set (0.00 sec)

几个参数

sort_merge_passes  由于sort buffer不够大，不得不将需要排序的数据进行分段，然后再通过sort merge的算法完成整个过程的merge总次数，一般整个参数用来参考sort buffer size 是否足够。

sort range session/global级别(单位：次) 通过range scan完成的排序总次数。

sort rows   session/global 级别(单位：row) 排序的总行数。

sort scan   通过扫描表完成的排序总次数。

索引排序，其中extra中的filesort不见了

mysql> Explain select name, founded from artist where name like 'AUSTRALIA%' order by name \G

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

id: 1

select_type: SIMPLE

table: artist

type: range

possible_keys: name

key: name

key_len: 257

ref: NULL

rows: 22

Extra: Using index condition

1 row in set (0.00 sec)

结果：

mysql> show session status like '%sort%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Sort_merge_passes | 0 |

| Sort_range | 0 |

| Sort_rows | 0 |

| Sort_scan | 0 |

+-------------------+-------+

4 rows in set (0.00 sec)

mysql> select name, founded from artist where name like 'AUSTRALIA%' order by name \G

......

22 rows in set (0.00 sec)

mysql> show session status like '%sort%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Sort_merge_passes | 0 |

| Sort_range | 0 |

| Sort_rows | 0 |

| Sort_scan | 0 |

+-------------------+-------+

4 rows in set (0.00 sec)

这次所有的参数都没有改变，这就是索引排序的力量，呵呵。

原文：http://blog.csdn.net/shshyeagle/article/details/44589547