preface

In every business function, there is always a need to sort by time or by other fields. When we think of sorting, we will think of order by in MySQL. In the process of use, will there be unreasonable use, resulting in the decline of query speed?

text

To avoid full table queries, we usually add an index to the order by field, which is why we need to add a corresponding index to the sort field.

explain select t.passenger_uuid ,t.order_no from yy_order t order by t.order_no limit 1000;

explain analysis results

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	458420	100	Using filesort

1. Full field sorting

Steps:

1. Initialize sort_buffer, t.passenger_uuid and order_no put

2. Find the qualified primary key ID from the index, obtain the whole row of data and store it in sort_ In buffer

3. Yes, sort_ The data in the buffer is sorted by field

When will external sorting be used?

When the number of sorts is greater than sort_ buffer_ External sorting will be used only when size is selected. Temporary tables are needed.

2. rowid sorting

The new algorithm is put into sort_ The buffer field has only the column to be sorted (order_no) and the primary key id.

The traversal result takes the first 1000 rows of data, and then queries the required fields by id

Query sort length parameter

show variables like  '%max_length_for_sort_data%';

Variable_name	Value
max_length_for_sort_data	1024

Summary:
We find that it is different from full field sorting: because of insufficient memory, sort is stored in rowid sorting_ The buffer field may be the value stored in order_no and ID, traverse the sorting results, and take out other fields according to ID.

A design idea of MySQL: if there is enough memory, we should make more use of memory and minimize disk access

3. Practice

Trigger the temporary table with the order by random value

-- Sort by random value
mysql> explain select word from words order by rand() limit 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | words | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9980 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

From Extra, you can see that temporary tables are used

# Time: 2021-04-28T06:56:04.664998Z
# User@Host: root[root] @  [172.17.0.1]  Id:    10
# Query_time: 0.003869  Lock_time: 0.000053 Rows_sent: 3  Rows_examined: 10003
SET timestamp=1619592964;
/* ApplicationName=DataGrip 2019.2.5 */ select word from words order by rand() limit 3;

A total of 10003 lines were scanned

1. Create a temporary table and create the fields word and id

2. rank() random number and word fields, stored in the temporary table and scanned 10000 rows

3. Initialize sort_ The buffer reads data from the temporary table and displays it in sort_ Sort in buffer

4. Take out limit 3, read word from the temporary table in turn, access three rows of data in the table, and scan three rows.

order by rand() uses the temporary memory table, and the rowid sorting method is used to sort the temporary memory table.

The optimizer gives priority, and the smaller the sorted rows, the better.

4. When will it be converted to disk temporary table?

mysql> show variables like  '%tmp_table_size%';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set (0.01 sec)

Parameter: tmp_table_size, 16M by default. If it is greater than 16M, it will be converted to disk temporary table