Use and Analysis of EXPLAIN for query optimization

Different SQL statement writing methods often bring about great performance differences. How can we know the overhead of SQL query execution? MySQL provides the EXPLAIN keyword for us. When you add the EXPLAIN keyword before your select statement, MySQL will EXPLAIN how it processes the SELECT query and provide the order of how the table is joined and joined, the number of scanned records and other related information can be used to optimize your SQL query.
EXPLAIN select id, username from userinfo where username like '% peng % ';
The EXPLAIN keyword is added before the query statement, so we can get the following report.
Copy codeThe Code is as follows:
Id: 1
Select_type: SIMPLE
Table: userinfo
Type: ALL
Possible_keys: NULL
Key: NULL
Key_len: NULL
Rel: NULL
Rows: 6
Extra: Using where

Explain the meaning of each data item:
Id:SELECT identifier, the serial number of the SELECT query;
Select_type:SELECT type, which can be SIMPLE (SIMPLE query) and PRIMARY (outermost select;
Table:Tables Used
Type:Join type
Possible_keys:Available index Columns
Key:Actually used index Column
Key_len:Key Length
Rel:Which column or constant is used together with the key to select rows from the table?
Rows:Number of checked rows
Extra:This column contains detailed information about MySQL queries.

For more detailed explanations, refer to the MySQL manual. It doesn't make sense to copy the manual content here. When you have the reference data, mySQL query optimization can be completed more accurately.