MYSQL order by sort and index relationship summary

MySQL InnoDB b-tree index using Tips

Here is the main discussion about the use of InnoDB B-tree index, do not mention the design, just use. The B-tree index primarily acts on the Where and order by clauses. It's all discussed here in the mysql-server-5.1.42 test.

CREATE TABLE ' friends ' (' ID ' int () UNSIGNED NOT NULL auto_increment, ' UID ' bigint (a) UNSIGNED not null DEFAULT ' 0 ', ' Fu Id ' bigint (a) UNSIGNED NOT null default ' 0 ', ' fname ' varchar (+) NOT null default ' ', ' fpicture ' varchar ($) NOT NULL DEF Ault ', ' fsex ' tinyint (1) NOT null default ' 0 ', ' status ' tinyint (1) NOT null default ' 0 ', PRIMARY KEY (' ID ')) Engine=innod B auto_increment=1 DEFAULT Charset=utf8; ALTER TABLE ' friends ' ADD INDEX uid_fuid (uid, fuid);

1. If multiple columns are indexed, follow the leftmost prefix rule. The so-called leftmost column, which refers to the query starting at the leftmost top of the index and does not skip the columns in the index.

The 2nd statement, starting from the second column of the index, fails with the index, causing MySQL to use the All Access policy, which is the full table query. In development, you should try to avoid full table queries.
2. When MySQL estimates that the number of rows checked may be "too much", range lookup optimizations will not be used.

The 2nd statement uses a full table query, and the only difference from the 1th statement is that the number of rows to be checked is much larger than the 1th statement. In your application, you may not encounter such a large query, but you should avoid such queries: Select UID from users where registered < 1295001384
3. The index column should not be used as part of an expression, or a function on an indexed column

Expressions are used in both the 2nd and 3 statements, and indexing is not useful.
4. Try to borrow the overlay index, reduce select * From ... Statement uses

The 1th sentence uses the using index in extra to indicate that the overwrite index is used. The 3rd sentence also uses the overwrite index, although the ID is not in the Index uid_fuid index column, but the value of the InnoDB two index (second index) leaf page is the PK value, unlike the MyISAM. The using index of the extra section indicates that the index is applied and is not confused with the index in type. The 2nd sentence does not use an overwrite index, because Fsex is not in the index.
5.ORDER by clause, try to sort using indexTo avoid sorting by using the Filesort method
MySQL supports two ways of sorting, Filesort and index, the latter of which is highly efficient, which refers to the MySQL scan index itself to complete sorting. The Filesort method is less efficient. Order by satisfies the following conditions and is sorted using the index method:
A) The ORDER by statement uses the leftmost column of the index. See the 1th sentence
b) Use the WHERE clause combined with the ORDER BY clause condition column to satisfy the leftmost top of the index. See the 2nd sentence.
Queries that use the Filesort method are used in the following cases

A) The number of rows checked is too high and the overwrite index is not used. In the 3rd sentence, the order by uses the leftmost UID of the index, as in the 2nd sentence, but still uses the Filesort method, because the status is not in the index, so there is no way to scan only the index.
b) using a different index, MySQL uses only one index per return. The 4th sentence, order by appears two indexes, respectively Uid_fuid and clustered index (PK)
c) both ASC and DESC are used for indexed columns. The WHERE statement converts the index column in order by to a constant, except. The 5th sentence, and the 6th sentence, in the ORDER BY clause, both ASC and DESC are sorted, but the 5th sentence uses the Filesort method, because the 6th sentence where UID takes out the sort required data, MySQL converts it to constant, and its ref column is Const.
D) The WHERE statement and the order BY statement use different indexes. See the 7th sentence.
e) The WHERE statement or an order BY statement uses an expression, including a function expression, in an indexed column. See 8th, 9 sentences
f) The WHERE statement is combined with the order BY statement to satisfy the leftmost prefix, but a conditional query is used in the where statement. See the 10th sentence, although where and order by make up the left-most-prefixed condition of the index, but a conditional query is used in the WHERE clause.
g) A non-indexed column is added to the ORDER BY clause, and the non-indexed column is not in the WHERE clause.
h) Order BY or it and where combination does not satisfy the leftmost index. See 11th and 12, where and order by combined, do not satisfy the leftmost column of the index. (UID, fsex) skipped Fuid.
i) when using the left JOIN, use the right table field to sort. See the 13th sentence, although User.uid is PK, it will still be sorted using Filesort.

6. Use the LEFT JOIN statement sparingly to avoid creating temporary tables when creating temporary tables using the LEFT JOIN statement. Try not to use left join, divide and conquer. When you want to use it, ask yourself if you really want to use it.

7. Highly selective indexed columns . Try to use highly selective over-attracting filtering data. High selectivity refers to the cardinality/#T越接近1, the higher the selectivity, where cardinality refers to the total number of distinct values (rows) of indexed columns in the table. PK and unique index, with the highest selectivity, that is, 1. The recommended option is more than 20%.

There are two indexes to use, and MySQL chooses primary because it has a higher selectivity.
8. Beware of OR in the WHERE clause. The where statement uses or, and the overwrite index is not used, and the full table is scanned. This or statement should be avoided as much as possible. Try to use union instead of or

The 1th sentence uses an index, but the check-in time is still not flattering, and MySQL has a lot of rows to check, but few rows are returned. The using where in extra indicates that unwanted rows of data need to be discarded through the WHERE clause.
9.LIMIT with overwrite index limit clause, using overwrite index is much faster than not using overwrite index

Transferred from: HTTP://MY.OSCHINA.NET/LONGNIAO/BLOG/110384?FROMERR=DKZAJDU1

MYSQL order by sort and index relationship summary