MySQL File Sorting & Index sorting, mysql File Sorting

MySQL File Sorting & Index sorting, mysql File Sorting

Sorting is a very cpu-consuming operation. When the system is improperly set or the query retrieves too many fields, MySQL may have to discard the optimized sorting algorithm, however, the old sorting algorithm that requires two I/O reads of table data is used to make the sorting efficiency very low.

Sorting by indexes mainly utilizes the order of indexes. In the process of searching by index, the ordered data access order has been obtained. After reading the result data in sequence, you do not need to sort the data, thus avoiding this operation, improves the query performance of sorting result sets.

It's easy. Use index sorting whenever possible. That's right.

Make an experiment. The table structure is as follows:

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)

Sort by file:

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)

View results:

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)

Parameters

Sort_merge_passes because the sort buffer is not large enough, you have to segment the data to be sorted, and then use the sort merge algorithm to complete the total number of merge times in the process, generally, the whole parameter is used to check whether the sort buffer size is sufficient.

Total number of times the sort range session/global level is sorted by range scan.

Total number of rows sorted by sort rows session/global (unit: row.

The total number of times that sort scan sorts by scanning the table.

Index sorting, where the filesort in extra is missing

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)

Result:

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)

All the parameters have not changed this time. This is the power of index sorting.