Basic tutorial for sorting data using indexes in MySQL and basic tutorial for mysql

Basic tutorial for sorting data using indexes in MySQL and basic tutorial for mysql

In MySQL, there are two methods to generate an ordered result set: one is to use filesort, and the other is to scan by index order. Using indexes for sorting is very fast, and you can use the same index for searching and sorting at the same time. When the ORDER of indexes is the same as that of the columns in order by and all columns are in the same direction (all or all descending ORDER), you can use indexes to sort indexes. If the query is connected to multiple tables, the index is used only when all columns in order by are columns in the first table. Filesort is used in other cases.

MySQL indexes are usually used to increase the search speed of data rows matching other tables when the WHERE condition is matched or when the join operation is performed.
MySQL can also use indexes to quickly sort and group order by and group by statements.
Optimize the order by statement of MySQL through index optimization:

create table actor(actor_id int unsigned NOT NULL AUTO_INCREMENT,name   varchar(16) NOT NULL DEFAULT '',password    varchar(16) NOT NULL DEFAULT '',PRIMARY KEY(actor_id), KEY   (name)) ENGINE=InnoDBinsert into actor(name,password) values('cat01','1234567');insert into actor(name,password) values('cat02','1234567');insert into actor(name,password) values('ddddd','1234567');insert into actor(name,password) values('aaaaa','1234567');

 

mysql> explain select actor_id from actor order by actor_id \G

*************************** 1. row ***************************      id: 1 select_type: SIMPLE    table: actor     type: indexpossible_keys: NULL     key: PRIMARY   key_len: 4     ref: NULL     rows: 4    Extra: Using index1 row in set (0.00 sec)

 

mysql> explain select actor_id from actor order by password \G

*************************** 1. row ***************************      id: 1 select_type: SIMPLE    table: actor     type: ALLpossible_keys: NULL     key: NULL   key_len: NULL     ref: NULL     rows: 4    Extra: Using filesort1 row in set (0.00 sec)

 

mysql> explain select actor_id from actor order by name \G

*************************** 1. row ***************************      id: 1 select_type: SIMPLE    table: actor     type: indexpossible_keys: NULL     key: name   key_len: 18     ref: NULL     rows: 4    Extra: Using index1 row in set (0.00 sec)

The following lists some common optimizations of the index to orfer:

1. If an SQL statement is like:

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];

You can create an index on the [sort] column to optimize order by using the index.
2. Optimize the index of WHERE + order by, for example:

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];

Create a joint index (columnX, sort) to optimize order.
NOTE: If columnX corresponds to multiple values, the following statement cannot optimize order by using indexes.

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

3. WHERE + multiple fields ORDER

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

Creating an index (uid, x, y) to optimize order by is much better than creating an index (x, y, uid.
MySQL Order By cannot use indexes to optimize sorting
* Perform order by on different index keys: (create indexes for key1 and key2 respectively)

SELECT * FROM t1 ORDER BY key1, key2;

* Make order by :( key_part1, key_part2 joint index and then key2 index on the non-consecutive index key part)

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

* Both ASC and DESC :( key_part1 and key_part2 are used to create a joint index)

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

* The index key used for searching records and the order by statement are not the same: (key1 and key2 create indexes respectively)

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

* If expressions (functions) are applied in the WHERE and order by columns, indexes cannot be used to optimize order.

SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

When MySQL cannot use indexes for sorting, it uses its own Sorting Algorithm (quick sorting algorithm) to sort data in the memory (sort buffer). If the memory cannot be loaded, it will partition data on the disk, sort the data blocks, and combine the blocks into ordered result sets (in fact, external sorting ). For filesort, MySQL has two sorting algorithms.
1. Two passes)
The implementation is to first retrieve the fields to be sorted and the pointer information that can be directly located in the relevant row data, and then sort them in the Set memory (set by sort_buffer_size, after sorting, retrieve the required Columns through the row pointer information again.
Note: This algorithm is used before 4.1. It requires two accesses to the data. In particular, the second read operation will lead to a large number of random I/O operations. On the other hand, the memory overhead is small.
2. single pass)
This algorithm extracts all required Columns at a time, sorts the Columns in the memory, and outputs The results directly.
Note: This algorithm has been used since MySQL 4.1. It reduces the number of I/O operations and is efficient, but the memory overhead is also large. If we extract the Columns that are not needed, the memory required by the sorting process will be greatly wasted. In Versions later than MySQL 4.1, you can set the max_length_for_sort_data parameter to control whether MySQL chooses the first sort algorithm or the second sort algorithm. When the total size of all the large fields retrieved is greater than that set by max_length_for_sort_data, MySQL selects the first sort algorithm, and vice versa. To improve the sorting performance as much as possible, we naturally want to use the second sort algorithm. Therefore, it is necessary to retrieve only the Columns needed in the Query.

When sorting the join operation, if order by only references the column of the First table, MySQL performs the filesort operation on the table and then performs the connection processing, EXPLAIN outputs "Using filesort"; otherwise, MySQL must generate a temporary table for the query result set and perform the filesort operation after the connection is complete. At this time, EXPLAIN outputs "Using temporary; using filesort ".