MySQL文件排序&索引排序_MySQL

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

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

做个实验，表结构如下

mysql> show create table artist /G*************************** 1. row ***************************       Table: artistCreate 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=latin11 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: rangepossible_keys: name          key: name      key_len: 257          ref: NULL         rows: 22        Extra: Using index condition; Using filesort1 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: rangepossible_keys: name          key: name      key_len: 257          ref: NULL         rows: 22        Extra: Using index condition1 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)