Mysql analysis-explain detailed introduction

Why you need to understand explain:

I want to understand the internal situation of select query, how the query optimizer works, and whether to use When it comes to indexing, explain can do it.

How the MySQL query optimizer works:

The MySQL query optimizer has several goals, but among them The main goal is to use indexes wherever possible, and to use the strictest index possible to eliminate as many rows of data as possible. The ultimate goal is to submit a SELECT statement to find rows of data, not exclude rows of data. The reason the optimizer tries to exclude rows is that the faster it can exclude rows, the faster it can find rows that match the condition. Queries can execute faster if the most rigorous tests are performed first.

1.explain There are ten attribute parameters

2. Explanation of EXPLAIN column:

##1.id: The sequence number of the query in the selected execution plan. Indicates the order in which select clauses or operation tables are executed in the query. The larger the id value, the higher the priority and the earlier it is executed. The IDs are the same, and the execution order is from top to bottom.

2.select_type：Query type, description ：

3.table：Display this row The data is about which table

4.type: This is Important column, showing what type is used for the connection.

The join types from best to worst are const, eq_reg, ref, range, index and ALL

5.possible_keys：DisplaypossibleIndex applied to this table. If empty, no index is possible. You can select an appropriate statement from the WHERE statement for the relevant domain

##6.key: The actual index used. If NULL, no index is used. Rarely, MYSQL will select an index that is under-optimized. In this case, you can use USEINDEX(indexname) in the SELECT statement to force the use of an index or use IGNORE INDEX(indexname) to force MYSQL to ignore index

7.key_len: The length of the index used. Without losing accuracy, the shorter the better

##8.ref : Shows which column of the index is used , if possible, is a constant

9.rows: The number of rows that MYSQL thinks must be checked to return the requested data

10.Extra: Extra information about how MYSQL parses the query. The bad example you can see here is Using temporary and Using filesort mean that MYSQL cannot use the index at all. The result is that the retrieval will be very slow and should be avoided.

Obviously, when type is ALL, it is the worst case. Using filesort also appears in Extra, which is also the worst case and optimization is necessary.

The more indexes you add, the better. In tables of different magnitudes, there are trade-offs. Maintaining the index itself may become a burden, so you must be able to add indexes reasonably.

Use explain to see if your statement should be optimized?

The above is the detailed introduction of Mysql analysis-explain. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!