MySQL Index and performance (4) sorting

We know that the B-tree index is ordered, so can you complete the order by operation by simply scanning the index? The answer is yes, but the conditions are also harsh: only if the column order of the index is exactly the same as the order in which the sequence is ordered by, and all the columns in the sentence are sorted by either ASC or all desc,mysql to use the index to sort the results ; If the query needs to correlate more than one table, the condition is more stringent and the index can be sorted only when the columns in the ORDER BY clause are all driver tables (in the execution plan).

Let's take a look at some examples: Suppose the Users table has an index (login_id,status)

Root@test 05:03:12>explain Select ID from the users order by Login_id,status\g *************************** 1. Row *************************** id:1 select_type:simple table:users type:index p Ossible_keys:null key:login_id key_len:387 ref:null rows:5894108 Ext Ra:using index 1 row in Set (0.00 sec) root@test 05:03:17>explain Select ID from the users ORDER by Status\g * 1. Row *************************** id:1 select_type:simple table:users type:index p Ossible_keys:null key:login_id key_len:387 ref:null rows:5894108 Ext Ra:using index; Using filesort 1 row in Set (0.00 sec) Root@test 05:03:26>explain the Select ID from the users order by login_id des C,status\g *************************** 1. Row *************************** id:1 Select_type: Simple table:users type:index possible_keys:null key:login_id key_len:387 Ref:null rows:5894108 extra:using Index; Using filesort 1 row in Set (0.00 sec) Root@test 05:04:03>explain the Select ID from the users order by login_id des C,status desc\g *************************** 1. Row *************************** id:1 select_type:simple table:users type:index p Ossible_keys:null key:login_id key_len:387 ref:null rows:5894108 Ext Ra:using index 1 row in Set (0.00 sec)

Note: If a using filesort appears in the extra column, it means that it cannot be scanned in indexed order.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/