In our daily work, we will sometimes open slow queries to record some SQL that takes a long time to execute Statement. Finding out these SQL statements does not mean that the job is over. Sometimes we often use the explain command to view the execution plan of these SQL statements, to see if the SQL statement uses an index, and whether a full table scan has been performed. , Which can be viewed through the explain command. So we have an in-depth understanding of MySQL’s cost-based optimizer, and can also get a lot of details of the access strategy that may be considered by the optimizer, and which strategy is expected to be adopted by the optimizer when running SQL statements.
-- Actual SQL, find the employee whose username is Jefabc
select * from emp where name = ‘Jefabc’;
-- To check whether SQL uses an index, add explain before it
explain select * from emp where name ='Jefabc';
The information from expain has 10 columns, namely id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
< strong>Summary description:
id: select identifier
select_type: indicates the type of query.
table: table of output result set
partitions: matched partition
type: indicates the connection type of the table
possible_keys: indicates the index that may be used when querying
key: indicates the actual use Index
key_len: length of index field
ref: comparison between column and index
rows: number of scanned rows (estimated number of rows)
filtered: percentage of rows filtered by table conditions
Extra: Description and explanation of the implementation situation
The following explains the possible occurrence of these fields:
1. id
SELECT identifier. This is the query sequence number of SELECT
My understanding is the identification of the order in which SQL is executed, SQL is executed from large to small
1. id phase At the same time, the order of execution is from top to bottom
2. If it is a subquery, the id number will increase. The larger the id value, the higher the priority, and the earlier it will be executed
3. If the id is the same, it can be considered as a group and executed in order from top to bottom; in all groups, the greater the id value, the higher the priority, and the first to execute
- View the employees who are in the R&D department and whose name starts with Jef, classic query
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like'Jef%' and d.name ='R&D department';
Second, select_type
Show the type of each select clause in the query
( 1) SIMPLE (simple SELECT, without UNION or subquery, etc.)
(2) PRMARY (the outermost query in the subquery, if the query contains any complex subparts, the outermost select Marked as PRIMARY)
(3) UNION (the second or subsequent SELECT statement in UNION)
(4) DEPENDENT UNION (the second or subsequent UNION in The SELECT statement depends on the query outside)
(5) UNION RESULT (the result of UNION, all selects after the second select in the union statement)
(6) SUBQUERY (The first SELECT in the subquery, the result does not depend on the external query)
(7) DEPENDENT SUBQUERY (the first SELECT in the subquery, depends on the external query)
(8) DERIVED (Subqueries in the SELECT and FROM clauses of derived tables)
(9) UNCACHEABLE SUBQUERY (The result of a subquery cannot be cached, and the first row of the outer link must be re-evaluated)
Three, table
Display the name of the table in the database accessed in this step (show which row of data is about Table), sometimes it is not the real table name, it may be a short name, such as e and d above, or it may be the abbreviation of the result of the first step.
The access method to the table indicates the way that MySQL finds the required row in the table, also known as the “access type”.
The commonly used types are: ALL, index, range, ref, eq_ref, const, system,NULL (from left to right, performance from poor to good)< /strong>
ALL: Full Table Scan, MySQL will traverse the entire table to find matching rows
index: Full Index Scan, the difference between index and ALL is that the index type only traverses the index tree
range: Only retrieve rows in a given range, use an index to select rows
ref: Indicates the connection matching condition of the above table, that is, which columns or constants are used to find the index Column value
eq_ref: Similar to ref, the difference is that the index used is a unique index. For each index key value, there is only one record in the table that matches. In simple terms, it is used in a multi-table connection Primary key or unique key as associated conditions
const, system: When MySQL optimizes a certain part of the query and converts it to a constant, use these types of access. If the primary key is placed in the where list, MySQL can convert the query into a constant. System is a special case of the const type. When the query table has only one row, use system
NULL: MySQL is in In the optimization process, the statement is decomposed, and the table or index is not even accessed during execution. For example, selecting the minimum value from an index column can be done through a separate index search.
Five, possible_keys
Point out which index MySQL can use to find records in the table, and the query involves If there is an index on the field, the index will be listed, but it may not be used by the query (the index can be used by the query, if there is no index, it will display null)
The column is completely independent The order of the table shown in the EXPLAIN output. This means that some keys in possible_keys cannot actually be used in the order of the generated table.
If the column is NULL, there is no related index. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns suitable for indexing. If so, create an appropriate index and check the query with EXPLAIN again
Six. Key
key The column shows the key (index) that MySQL actually decides to use, which must be included in possible_keys
If no index is selected, the key is NULL. To force MySQL to use or ignore the index in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.
Seven, key_len
Indicates the number of bytes used in the index, which can be used to calculate the number of bytes in the query The length of the index used (the value displayed by key_len is the maximum possible length of the index field, not the actual length used, that is, key_len is calculated according to the table definition, not retrieved from the table)
< p>Without loss of accuracy, the shorter the length, the better8. ref
The comparison between the column and the index indicates the connection matching condition of the above table, that is, which columns or constants are used to find the value on the index column
Nine, rows
Estimate the number of rows in the result set, which means that MySQL estimates based on table statistics and index selection The number of lines that need to be read to find the required record
Ten, Extra
This column contains the detailed information of MySQL to solve the query, there are the following situations:
Using where: Without reading all the information in the table, the required data can be obtained only through the index. Occurs when all the requested columns of the table are part of the same index, which means that the MySQL server will filter the rows after the storage engine retrieves the rows.
Using temporary: Indicates that MySQL needs to use a temporary table for storage Result set, commonly used in sorting and grouping queries, common group by; order by
Using filesort: When the Query contains an order by operation, and the sorting operation that cannot be done using the index is called “file sorting”
p>-- Test Extra filesort
explain select * from emp order by name;
Using join buffer: The changed value emphasizes that the index is not used when obtaining the connection conditions, and the connection buffer is required to store intermediate results . If this value appears, it should be noted that, depending on the specific situation of the query, you may need to add an index to improve performance.
Impossible where: This value emphasizes that the where statement will result in no eligible rows (it is impossible to have results by collecting statistics).
Select tables optimized away: This value means that only by using the index, the optimizer may only return one row from the result of the aggregate function
No tables used: Use from dual or in the Query statement Does not contain any from clause
-- explain select now() from dual;
< strong>Summary:
• EXPLAIN will not tell you information about triggers, stored procedures or the impact of user-defined functions on queries
• EXPLAIN does not consider various Caches
• EXPLAIN cannot display the optimization work done by MySQL during query execution
• Some statistics are estimated, not exact values
• EXPALIN can only explain SELECT operations, other operations should be rewritten as SELECT and then view the execution plan .
It is impossible to have results by collecting statistical information
Source: http://www.cnblogs. com/tufujie/
In our daily work, we will sometimes Open a slow query to record some SQL statements that have been executed for a long time. Finding out these SQL statements does not mean that the job is over. Sometimes we often use the explain command to view the execution plan of these SQL statements and see if the SQL statement has You can use the explain command to check whether the index is used or the full table scan is done. So we have an in-depth understanding of MySQL’s cost-based optimizer, and can also get a lot of details of the access strategy that may be considered by the optimizer, and which strategy is expected to be adopted by the optimizer when running SQL statements.
-- Actual SQL, find the employee whose username is Jefabc
select * from emp where name = ‘Jefabc’;
-- To check whether SQL uses an index, add explain before it
explain select * from emp where name ='Jefabc';
The information from expain has 10 columns, namely id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
< strong>Summary description:
id: select identifier
select_type: indicates the type of query.
table: table of output result set
partitions: matched partition
type: indicates the connection type of the table
possible_keys: indicates the index that may be used when querying
key: indicates the actual use Index
key_len: length of index field
ref: comparison between column and index
rows: number of scanned rows (estimated number of rows)
filtered: percentage of rows filtered by table conditions
Extra: Description and explanation of the implementation situation
The following explains the possible occurrence of these fields:
1. id
SELECT identifier. This is the query sequence number of SELECT
My understanding is the identification of the order in which SQL is executed, SQL is executed from large to small
1. id phase At the same time, the order of execution is from top to bottom
2. If it is a subquery, the id number will increase. The larger the id value, the higher the priority, and the earlier it will be executed
3. If the id is the same, it can be considered as a group and executed in order from top to bottom; in all groups, the greater the id value, the higher the priority, and the first to execute
- View the employees who are in the R&D department and whose name starts with Jef, classic query
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like'Jef%' and d.name ='R&D department';
Second, select_type
Show the type of each select clause in the query
( 1) SIMPLE (simple SELECT, without UNION or subquery, etc.)
(2) PRMARY (the outermost query in the subquery, if the query contains any complex subparts, the outermost select Marked as PRIMARY)
(3) UNION (the second or subsequent SELECT statement in UNION)
(4) DEPENDENT UNION (the second or subsequent UNION in The SELECT statement depends on the query outside)
(5) UNION RESULT (the result of UNION, all selects after the second select in the union statement)
(6) SUBQUERY (The first SELECT in the subquery, the result does not depend on the external query)
(7) DEPENDENT SUBQUERY (the first SELECT in the subquery, depends on the external query)
(8) DERIVED (Subqueries in the SELECT and FROM clauses of derived tables)
(9) UNCACHEABLE SUBQUERY (The result of a subquery cannot be cached, and the first row of the outer link must be re-evaluated)
Three, table
Display the name of the table in the database accessed in this step (show which row of data is about Table), sometimes it is not the real table name, it may be a short name, such as e and d above, or it may be the abbreviation of the result of the first step.
Four, type< /p>
Access to the table means the way that MySQL finds the required row in the table, also known as the “access type”.
The commonly used types are: ALL, index, range, ref, eq_ref, const, system,NULL (from left to right, performance from poor to good)< /strong>
ALL: Full Table Scan, MySQL will traverse the entire table to find matching rows
index: Full Index Scan, the difference between index and ALL is that the index type only traverses the index tree
range: Only retrieve rows in a given range, use an index to select rows
ref: Indicates the connection matching condition of the above table, that is, which columns or constants are used to find the index Column value
eq_ref: Similar to ref, the difference is that the index used is a unique index. For each index key value, there is only one record in the table that matches. In simple terms, it is used in a multi-table connection Primary key or unique key as associated conditions
const, system: When MySQL optimizes a certain part of the query and converts it to a constant, use these types of access. If the primary key is placed in the where list, MySQL can convert the query into a constant. System is a special case of the const type. When the query table has only one row, use system
NULL: MySQL is in In the optimization process, the statement is decomposed, and the table or index is not even accessed during execution. For example, selecting the minimum value from an index column can be done through a separate index search.
Five, possible_keys
Point out which index MySQL can use to find records in the table, and the query involves If there is an index on the field, the index will be listed, but it may not be used by the query (the index can be used by the query, if there is no index, it will display null)
The column is completely independent The order of the table shown in the EXPLAIN output. This means that some keys in possible_keys cannot actually be used in the order of the generated table.
If the column is NULL, there is no related index. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns suitable for indexing. If so, create an appropriate index and check the query with EXPLAIN again
Six. Key
key The column shows the key (index) that MySQL actually decides to use, which must be included in possible_keys
If no index is selected, the key is NULL. To force MySQL to use or ignore the index in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.
Seven, key_len
Indicates the number of bytes used in the index, which can be used to calculate the number of bytes in the query The length of the index used (the value displayed by key_len is the maximum possible length of the index field, not the actual length used, that is, key_len is calculated according to the table definition, not retrieved from the table)
< p>Without loss of accuracy, the shorter the length, the better8. ref
The comparison between the column and the index indicates the connection matching condition of the above table, that is, which columns or constants are used to find the value on the index column
Nine, rows
Estimate the number of rows in the result set, which means that MySQL estimates based on table statistics and index selection The number of lines that need to be read to find the required record
Ten, Extra
This column contains the detailed information of MySQL to solve the query, there are the following situations:
Using where: Without reading all the information in the table, the required data can be obtained only through the index. Occurs when all the requested columns of the table are part of the same index, which means that the MySQL server will filter the rows after the storage engine retrieves the rows.
Using temporary: Indicates that MySQL needs to use a temporary table for storage Result set, commonly used in sorting and grouping queries, common group by; order by
Using filesort: When the Query contains an order by operation, and the sorting operation that cannot be done using the index is called “file sorting”
p>-- Test Extra filesort
explain select * from emp order by name;
Using join buffer: The changed value emphasizes that the index is not used when obtaining the connection conditions, and the connection buffer is required to store intermediate results . If this value appears, it should be noted that, depending on the specific situation of the query, you may need to add an index to improve performance.
Impossible where: This value emphasizes that the where statement will result in no eligible rows (it is impossible to have results by collecting statistics).
Select tables optimized away: This value means that only by using the index, the optimizer may only return one row from the result of the aggregate function
No tables used: Use from dual or in the Query statement Does not contain any from clause
-- explain select now() from dual;
< strong>Summary:
• EXPLAIN will not tell you information about triggers, stored procedures or the impact of user-defined functions on queries
• EXPLAIN does not consider various Caches
• EXPLAIN cannot display the optimization work done by MySQL during query execution
• Some statistics are estimated, not exact values
• EXPALIN can only explain SELECT operations, other operations should be rewritten as SELECT and then view the execution plan .
It is impossible to have results by collecting statistical information
Source: http://www.cnblogs. com/tufujie/
In our daily work, we will sometimes open slow queries to record some SQL statements that have been executed for a long time, and find out these SQL statements It does not mean that it is over. Sometimes we often use the explain command to view the execution plan of these SQL statements, to see whether the SQL statement uses an index, whether to perform a full table scan, this can be done through the explain command Come and see. So we have an in-depth understanding of MySQL’s cost-based optimizer, and can also get a lot of details of the access strategy that may be considered by the optimizer, and which strategy is expected to be adopted by the optimizer when running SQL statements.
-- Actual SQL, find the employee whose username is Jefabc
select * from emp where name = ‘Jefabc’;
-- To check whether SQL uses an index, add explain before it
explain select * from emp where name ='Jefabc';
The information from expain has 10 columns, namely id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
< strong>Summary description:
id: select identifier
select_type: indicates the type of query.
table: table of output result set
partitions: matched partition
type: indicates the connection type of the table
possible_keys: indicates the index that may be used when querying
key: indicates the actual use Index
key_len: length of index field
ref: comparison between column and index
rows: number of scanned rows (estimated number of rows)
filtered: percentage of rows filtered by table conditions
Extra: Description and explanation of the implementation situation
The following explains the possible occurrence of these fields:
1. id
SELECT identifier. This is the query sequence number of SELECT
My understanding is the identification of the order in which SQL is executed, SQL is executed from large to small
1. id phase At the same time, the order of execution is from top to bottom
2. If it is a subquery, the id number will increase. The larger the id value, the higher the priority, and the earlier it will be executed
3. If the id is the same, it can be considered as a group and executed in order from top to bottom; in all groups, the greater the id value, the higher the priority, and the first to execute
- View the employees who are in the R&D department and whose name starts with Jef, classic query
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like'Jef%' and d.name ='R&D department';
Second, select_type
Show the type of each select clause in the query
( 1) SIMPLE (simple SELECT, without UNION or subquery, etc.)
(2) PRMARY (the outermost query in the subquery, if the query contains any complex subparts, the outermost select Marked as PRIMARY)
(3) UNION (the second or subsequent SELECT statement in UNION)
(4) DEPENDENT UNION (the second or subsequent UNION in The SELECT statement depends on the query outside)
(5) UNION RESULT (the result of UNION, all selects after the second select in the union statement)
(6) SUBQUERY (The first SELECT in the subquery, the result does not depend on the external query)
(7) DEPENDENT SUBQUERY (the first SELECT in the subquery, depends on the external query)
(8) DERIVED (Subqueries in the SELECT and FROM clauses of derived tables)
(9) UNCACHEABLE SUBQUERY (The result of a subquery cannot be cached, and the first row of the outer link must be re-evaluated)
Three, table
Display the name of the table in the database accessed in this step (show which row of data is about Table), sometimes it is not the real table name, it may be a short name, such as e and d above, or it may be the abbreviation of the result of the first step.
Four, type
Access to the table means MyS The way QL finds the required row in the table is also known as the “access type”.
The commonly used types are: ALL, index, range, ref, eq_ref, const, system,NULL (from left to right, performance from poor to good)< /strong>
ALL: Full Table Scan, MySQL will traverse the entire table to find matching rows
index: Full Index Scan, the difference between index and ALL is that the index type only traverses the index tree
range: Only retrieve rows in a given range, use an index to select rows
ref: Indicates the connection matching condition of the above table, that is, which columns or constants are used to find the index Column value
eq_ref: Similar to ref, the difference is that the index used is a unique index. For each index key value, there is only one record in the table that matches. In simple terms, it is used in a multi-table connection Primary key or unique key as associated conditions
const, system: When MySQL optimizes a certain part of the query and converts it to a constant, use these types of access. If the primary key is placed in the where list, MySQL can convert the query into a constant. System is a special case of the const type. When the query table has only one row, use system
NULL: MySQL is in In the optimization process, the statement is decomposed, and the table or index is not even accessed during execution. For example, selecting the minimum value from an index column can be done through a separate index search.
Five, possible_keys
Point out which index MySQL can use to find records in the table, and the query involves If there is an index on the field, the index will be listed, but it may not be used by the query (the index can be used by the query, if there is no index, it will display null)
The column is completely independent The order of the table shown in the EXPLAIN output. This means that some keys in possible_keys cannot actually be used in the order of the generated table.
If the column is NULL, there is no related index. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns suitable for indexing. If so, create an appropriate index and check the query with EXPLAIN again
Six. Key
key The column shows the key (index) that MySQL actually decides to use, which must be included in possible_keys
If no index is selected, the key is NULL. To force MySQL to use or ignore the index in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.
Seven, key_len
Indicates the number of bytes used in the index, which can be used to calculate the number of bytes in the query The length of the index used (the value displayed by key_len is the maximum possible length of the index field, not the actual length used, that is, key_len is calculated according to the table definition, not retrieved from the table)
< p>Without loss of accuracy, the shorter the length, the better8. ref
The comparison between the column and the index indicates the connection matching condition of the above table, that is, which columns or constants are used to find the value on the index column
Nine, rows
Estimate the number of rows in the result set, which means that MySQL estimates based on table statistics and index selection The number of lines that need to be read to find the required record
Ten, Extra
This column contains the detailed information of MySQL to solve the query, there are the following situations:
Using where: Without reading all the information in the table, the required data can be obtained only through the index. Occurs when all the requested columns of the table are part of the same index, which means that the MySQL server will filter the rows after the storage engine retrieves the rows.
Using temporary: Indicates that MySQL needs to use a temporary table for storage Result set, commonly used in sorting and grouping queries, common group by; order by
Using filesort: When the Query contains an order by operation, and the sorting operation that cannot be done using the index is called “file sorting”
p>-- Test Extra filesort
explain select * from emp order by name;
Using join buffer: The changed value emphasizes that the index is not used when obtaining the connection conditions, and the connection buffer is required to store intermediate results . If this value appears, it should be noted that, depending on the specific situation of the query, you may need to add an index to improve performance.
Impossible where: This value emphasizes that the where statement will result in no eligible rows (it is impossible to have results by collecting statistics).
Select tables optimized away: This value means that only by using the index, the optimizer may only return one row from the result of the aggregate function
No tables used: Use from dual or in the Query statement Does not contain any from clause
-- explain select now() from dual;
< strong>Summary:
• EXPLAIN will not tell you information about triggers, stored procedures or the impact of user-defined functions on queries
• EXPLAIN does not consider various Caches
• EXPLAIN cannot display the optimization work done by MySQL during query execution
• Some statistics are estimated, not exact values
• EXPALIN can only explain SELECT operations, other operations should be rewritten as SELECT and then view the execution plan .
It is impossible to have results by collecting statistical information
Source: http://www.cnblogs. com/tufujie/
-- Actual SQL, find the employee whose username is Jefabc
select * from emp where name = ‘Jefabc’;
-- To check whether SQL uses an index, add explain before it
explain select * from emp where name = ‘Jefabc’;
-- View employees in the R&D department whose name starts with Jef, classic query
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like'Jef%' and d.name ='R&D department';
-- Test Extra filesort
explain select * from emp order by name;
-- explain select now() from dual;
Author: Jake Si Le
Source: http://www.cnblogs.com/tufujie/