SQL > create index (s); The following uses the mysql database.
select * from test where a=? and b? Order by c limit 0,100
The results may surprise you. Let’s first prepare the runtime environment and then verify by following the left-most prefix rule and the Explain keyword. The result was a real reversal of what XjjDog had known for years.
Preparation stage
To verify, let’s create a simple data table. There are three simple int fields: A, B, and C.
Next, write a simple stored procedure to insert 10W pieces of data. Wait about 1 minute for data to be inserted.
Since mysql has the left-most prefix rule, we create 6 indexes for ABC columns. These six indexes cover all queries based on ABC.
Verify using Explain
1. Automatically select indexes
explain select * from test where a10 and b 10 order by c
First, we take the above SQL statement to verify. It turns out that the query uses the index IDx_A_b_C and uses only the prefixes A, b. The Extra part, however, uses filesort, which is very poor performance.
Let’s try changing the position of the query parameters.
explain select * from test where c10 and b 10 order by a
This time, idx_b_a_c is automatically selected, but filesort is still used, the query effect is the same. According to the above logic, should not select idx_b_c_A?
2. Specify the index
Next, use force index to force an index.
Here is the direct result, which is the following SQL.
explain select * from test
FORCE INDEX(idx_c_b_a) where a10 and b 10 order by c
The results are as follows.
We use force index to specify the index to use. This time it works very well, showing that index is used, where is used, and the operation is done only on the index. But the number of rows scanned has increased.
However, this is contrary to our experience. The index of idx_C_b_A is created on fields (c,b,a). The leftmost search criteria are C, CB, and CBA. In this example, the argument after order by is treated as the header of the prefix.
We delete the other indexes except idx_C_b_A, and then remove the force index part. As it turns out, mysql can now select indexes automatically.
Let’s look at another case where order by has two arguments.
explain select * from test FORCE INDEX(idx_b_c_a) where a10 order by b,c
The result is as follows: idx_b_c_A is used without filesort. None of the other indexes is optimal.
3. Meaning of return value in explain section
Our conclusions are based on the explain tools provided by mysql itself. This tool can output some useful information. The following is the relevant part of the return value meaning.
select_type
Indicates the type of SELECT. Common values are as follows:
SIMPLE SIMPLE table, without table join or subquery.
PRIMARY Indicates the PRIMARY query, that is, the outer query.
UNION The second or subsequent query statement in the UNION.
SUBQUERY The first SUBQUERY.
type
Represents the way MySQL finds the desired rows in the table, or the access type. Common access types are as follows, from bottom to top, performance deteriorates.
The system,const table has only one row of records (equal to the system table), which is a const column.
Eq_ref unique index scan, for each index key, only one record in the table matches it.
A ref non-unique index scan, which returns all rows matching a single value, is essentially an index access, which returns all rows matching a single value. However, it may find multiple rows matching a single value, so it should be a mixture of lookup and scan.
Range retrieves only the rows of a given range, using an index to select the rows, and the key column shows which index is used. This scope-scan index is better than a full table scan because it only needs to start at one point in the index and end at another without scanning the whole index.
Index Full Index Scan: The index type is different from All. This is usually faster than ALL because index files are usually smaller than data files.
All Indicates that the performance of all table scanning is the worst
Extra
using index
The corresponding SELECT operation uses an overwrite index to avoid accessing rows of the table. Using WHERE indicates that the index is used to perform a key lookup. If using WHERE is not present at the same time, the index is used to read data rather than perform lookup actions.
using filesort
Note mysql uses an external index sort for data, rather than reading data in the order of the indexes in the table. A sort operation in MySQL that cannot be done with an index is called “file sort”.
using temporary
Instead of using temporary tables to hold intermediate results, mysql uses temporary tables when sorting query results. Common in sort order by and group by queries.
End
As you can see, when we create multiple indexes, mysql query optimization can not be intelligent parsing, use the optimal way, need to use force index to specify the index.
Indexes in mysql are mainly used in where conditions and sort actions. There are two cases.
1. Filter first, then sort, using the index parameter in the filter condition, but sorting will use the slower external sort. Because the result set is filtered, there is no index involved.
2. Sort first and then filter. You can use the same index, and the sorting priority is higher than the filtering priority. Select the right index and do the filtering at the same time. But the number of rows scanned will increase.
I don’t think mysql knows which of these two processes is faster and which is slower, so it chose the most general method and chose the first method directly. Even when the index is very much, directly dizzy. If you build too many indexes, you may be indirectly harming mysql. This is the phenomenon, as for the deep reason, welcome to read mysql related source code to explain.
This is not a good sign for code that often changes fields to sort. Considering the stability of the program, I think we should minimize the result set filtered by where conditions. In this case, creating a joint index of (a, b) may be a compromise.