Mysql explain display using Filesort introduction _mysql

The syntax format is as follows
EXPLAIN Tbl_name
Or:
EXPLAIN SELECT select_options
The EXPLAIN statement can be used as a synonym for DESCRIBE, or it can be used to get information about a SELECT statement that MySQL is going to execute.

EXPLAIN tbl_name syntax is the same as DESCRIBE tbl_name or show COLUMNS from Tbl_name.

When the keyword EXPLAIN is used before a SELECT statement, MySQL explains how the SELECT statement is about to run, showing how the table is connected, the order of the connections, and so on.

The following information is a reference:

When explain the SQL we use, often encountered using Filesort this situation, originally thought to be due to the same column values caused, the result was yesterday to see a company SQL, and colleagues discussed the next plus they did some tests, suddenly found that their original idea is wrong.

First, a using filesort may appear only in the Order by data column, and if you do not set an index on this column of order by, the using Filesort will appear regardless of whether the column values are the same. As a result, this column should have an index for it as long as it is used.

Second, in this test, a slightly more complex example is used to illustrate the problem, and here's a detailed example:

SELECT * from DB. TB WHERE id=2222 and FID in (9,8,3,13,38,40) Order by inverse_date LIMIT 0, 5
The index built inside is a three-column, multiple-column index: IDX (Id,fid, inverse_date). Inverse_date This is the reverse index of time.

For this SQL I was at the beginning to think that should be a good state of optimization, there should be no flaws, the results of a explain found unexpectedly appeared: Using where; Using Filesort.

Why, then after analysis, we learned that it was established in a B-tree structure when the multiple-column index was established, so when indexing is established, the index is created sequentially, the index of FID is established in the case of the same ID, and then the INVERSE_ is established with the same FID. The index of the date order, if the number of columns is more, and so on. With this theory we can see that when this SQL uses this IDX index, it is only used before the order by Inverse_date is actually using Filesort. Khan's dead. So if we want to optimize this SQL, we should build another index IDX (id,inverse_date) for it, which eliminates the using Filesort speed much faster. The problem has finally been solved.