Why does using filesort exist?

When you use explain to analyze SQL statements, one of the values of extra is using filesort, for example:

Primary Key ('id '),
Key 'uid' ('uid ')

Explain select * From t_talbe where uid = 1 order by ID;

Extra result: Using where; using filesort

In the manual, using filesort is interpreted as "MySQL must do an extra pass to find out how to retrieve the rows in sorted order. the sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that matchWHEREClause .".

Assume that two records a (id1, uid1, *) and B (Id2, uid2, *) are obtained based on the condition. Sort the records by key 'uid' ('uid, it is easy to get these two results. The two UIDs are uid1 and uid2. They are retrieved first (if uid1 is used) and then (if uid2 is used) the Order has known the relationship between them. However, who is the size of id1 and Id2? I don't know. You must compare them. If there are n result sets, you must compare them all to know which one is the first record. Second ,...... What about n?

Now let's take a look at the idea of index. Suppose there are "Key key_name ('k1 ', 'k2 ',......, 'Kn ') ", we know the record assembly:

1. First sort by field K1,

2. If k1 is the same, it is sorted by K2,

3. In the case that K2 is the same, sort by K3 and so on,

4. The last is kN.

According to the rules, in a queue lx, there are two neighboring points Li (KI, 1, KI, 2 ,......, Ki, n) and Li + 1 (KI +, Ki + ,......, Ki + 1, n). If Ki, 1 = Ki +, you can know the relationship between Ki, 2 and Ki + without comparing them.

For all the above examples, changing the key 'uid' ('uid') to the key 'U _ id' ('uid', 'id') can be solved.

However,

Example 2. Select * From t_talbe where uid = 1 order by ID, uid; there is still no way, because it is impossible to create index 'U _ id_u '('uid', 'id', 'uid ');

Example 3. Select * From t_talbe where uid! = 1 order by ID; (possible_keys: u_f, uid; key: NULL; extra: Using where; using filesort .)

Uid = 2 (ID | 1, 2, 3, 4), and uid = 3 (ID | 1, 2, 3, 4, for 'U _ id' ('uid', 'id'), all records must be retrieved and compared.

By the way, sometimes using filesort may not be a big deal:

Example 4. Select * From t_talbe order by ID; it only tells you that it uses "all rows ".