This article explains how Mysql returns results based on the order of ID values.

I encountered a problem today that there is a query that needs to return the result set in the order of the specified ID value. In fact, it can also be placed in the program for sorting, but I suddenly wanted to see if I could directly use Mysql to directly query and return, so I looked for it. There is really an auxiliary function to implement.

Field() function

Mysql provides a function Field() that can customize sorting in the order we give

Example:

Suppose there is a city information table called regions with a primary key id and a name attribute name. Now I want to query the IDs 2, 3, and 1 and return them in this order

<p>select id, name from regions;#id        name<br> 1        北京 2        上海 3        深圳<br></p>

Use field()

<p>select id, name from regions order by field(id, 2, 3, 1);#id        name<br> 2        上海 3        深圳 1        北京<br></p>

This will achieve the purpose of sorting in a custom order

Performance

<p>mysql> explain select id from regions order by field(id, 2, 3, 1);+---+-------------+---------+------+---------------+-----+---------+-----+------+-----------------------------+|id | select_type | table   | type | possible_keys | key | key_len | ref | rows | Extra                       ||-- | ----------- | ------- | ---- | ------------- | --- | ------- | --- | ---- | ----------------------------|  |1  | SIMPLE      | regions | index| NULL          | id  | 4       | NULL| 3    | Using index; Using filesort |+---+-------------+---------+------+---------------+-----+---------+-----+------+-----------------------------+<br></p>

Because we are using Order By When Field is specified, the primary key is sorted according to the primary key ID. There is a Primary key index. It will use the ID to find records with conditions equal to 2, 3, and 1. So you can see that there is a Using index in Extra. If you change to another one, there is no index. The field will not have it here. The Order By clause cannot use this index and can only use Filesort sorting, which is the reason why there is Using filesort in Extra

The approximate process is as follows:

From the id index Starting from a leaf node, scan all leaf nodes in order
Go to the primary key index (clustered index) according to the primary key id recorded by each leaf node to find the real row data
Determine whether the row data satisfies id = 2, 3. 1. If condition is met, take out and return

Basically traverse the entire table. Some people say that it searches the id of the selected record in the FIELD list and returns the position, using the position as the sorting basis. .
Such usage will lead to Using filesort (of course, using Filesort is not necessarily slower, sometimes it is faster than not using it), which is a very inefficient sorting method.

Usually the ORDER BY clause is combined with the LIMIT clause to retrieve only some rows. If you sort all the rows just to take out the top1 row, this is obviously not an efficient approach.

Summary

Field() function can help us directly complete some required sorting at the database layer and simplify the business code, but at the same time it will also have compatibility and performance The problem suggestion can be used in places where the frequency of data changes is low or there is a long-term cache. When the amount of data is large, the database can be used to query the data and sort it in the program.

Recommended study:《mysql video tutorial》                                  

The above is the detailed content of This article explains how Mysql returns results based on the order of ID values.. For more information, please follow other related articles on the PHP Chinese website!