A SQL was an ignorant teenager, a ravaged, thrown to the end of the MySQL server, the young waiting, but can not wait for the fruitful results. So young angry, painstakingly want to seek help from Baidu there, in the face of executive plan EXPLAIN, but what wait is endless torture and crazy.
Using the Explain command, you can find SQL statements with performance problems according to the execution plan to help you optimize the SQL and provide direction and basis.
If you’re confused, or even mad, about executing your plan, then you really need to get serious about understanding it. Execution plans are really important in database performance optimization, and they help us to be more specific about SQL optimization. This article starts with an execution plan, explaining how it is used, and what the columns mean.
One, implementation plan?
An execution plan is an SQL statement that steps through what is actually done when executed in the database. This is the information that is displayed when we analyze an SQL statement with EXPLAIN.
EXPLAIN command is to look at how the query optimizer is the main method, decided to execute the query from the query results in a SQL statement can know is how to perform each step of the way, have experienced something, which is divided into a few steps, have used the index, which fields used to what kind of index, are there any place can be optimized, This information is the basis of our SQL optimization.
To use ·EXPLAIN, simply add EXPLAIN before the SELECT keyword in the query. The syntax is as follows:
EXPLAIN + SELECT query statement;
When an execution plan is executed, only information about each step in the execution plan is returned. It returns one or more lines of information showing each part of the execution plan and the order in which it was executed.
Such as:
If you are querying multiple associated tables, the execution plan result may be multiple rows.
In the next example involves table, all from the official MySQL sakila sample database, download script: downloads.mysql.com/docs/sakila…
Execute the columns in the plan
EXPLAIN results always have the same columns, with each column representing a different meaning, only the number of rows and content changing. From the above example, we can see that there are many columns returned. To understand the meaning of each column, we can better optimize the SQL.
The columns involved are:
| The column name | meaning |
|---|---|
| id | Id column, representing the order in which the SELECT clause or operation table is executed in the query. |
| select_type | Query types are mainly used to distinguish common query, joint query, sub-query and other complex queries. |
| table | Indicates which table the corresponding row is accessing. |
| partitions | Query the partitions involved. |
| type | Access type that determines how to find rows in a table. |
| possible_keys | Which indexes can be used by the query. |
| key | The actual used index, if NULL, is not used. |
| key_len | The number of bytes used in the index, the length of the index used in the query (maximum possible length), is not the actual length used, in theory the shorter the better. |
| ref | The column that shows the index is used. |
| rows | Estimate the number of rows to read to find the desired one. |
| filtered | The number of rows returned as a percentage of the number of rows read, the larger the value, the better. |
| Extra | Additional information, but very important. |
1. The id column
The ID column is a number that identifies the serial number of the SELECT query and represents the order in which the SELECT clause or operation table is executed during the SQL query.
If there are no subqueries or associated queries in SQL, the ID columns will all display a 1. Otherwise, the inner SELECT statements are usually numbered sequentially.
The ID column is divided into three cases:
1) Have the same ID
The following ordinary query, no sub-query.
explain select f.* from film f,film_actor fa,actor a where f.film_id = fa.film_id and fa.actor_id = a.actor_id and a.first_name = 'NICK';
Copy the code
2) Different ids
If subqueries exist, the id sequence increases. A larger ID has a higher priority and is executed earlier.
explain select * from film where film_id = (select film_id from film_actor where actor_id = 2 limit 1);
Copy the code
3) The id is the same but different
1), 2) both conditions exist simultaneously. If the ids are the same, they are considered a group and executed from the top down. In all groups, the greater the ID value, the higher the priority and the earlier the execution.
2. Select_type columns
The select_Type column indicates the query type of the corresponding row. Whether the query type is simple or complex, the select_type column is used to distinguish the common query, joint query, and subquery.
The select_Type column has the following values:
| Select_type value | instructions |
|---|---|
| SIMPLE | Simple queries, meaning that subqueries orUNION. |
| PRIMARY | The outermost query that contains any complex subparts is marked asPRIMARY |
| SUBQUERY | inselect 或 whereThe list contains subqueries |
| DERIVED | Include infromClauseselect, MySQL will recursively execute and put the results into a temporary table, called a “derived table” because the temporary table is derived from the subquery. |
| UNION | The second SELECT appears inUNIONAfter that, it is marked asUNION. |
| UNION RESULT | fromUNIONTable to obtain the resultselect. |
3. The table columns
The table column represents which table the row is executing, and either the name of the table, or an alias for that table if an alias is defined in SQL.
4. The partitions are listed
Query the partitions involved.
5. The type column
The type column refers to the access type, which is how MySQL decides to find rows in the table.
Is an important metric in SQL query optimization and has a number of values, from worst to best:
ALL < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < fulltext < ref < eq_ref < const < system
1) ALL
Known as a full table scan, this means scanning the entire table to find matching rows, which is obviously slow.
This situation, the worst performance, when writing SQL to avoid this situation.
Examples are as follows:
explain select * from film;
Copy the code
It’s easy to understand why you should avoid using SELECT * when writing SQL. In other words, to avoid a full table scan, which is the worst performance.
2) the index
Full index scan is similar to full table scan ALL. Tables are scanned in index order rather than row, that is, only the index tree is traversed.
Both index and ALL read the entire table, but index is read from the index, and ALL is read from the hard disk. Obviously, index is superior to ALL in performance. Adding indexes properly will help improve performance.
Examples are as follows:
explain select title from film;
explain select description from film;
Copy the code
From explain results, the title field in film is scanned by index (type is index) and the description field is scanned by full table (type is ALL). Why is this?
Next, let’s look at the structure of film:
Select title from film; select description from film; select title from film; select title from film; select description from film;
From the above example comparison, also fully confirmed the importance of the index.
3) range
Only rows of a given range are retrieved, using an index to select rows. The key column shows which index is used. A bettween, <, >, in, etc. query appears in a WHERE statement. This kind of range scan on index columns is better than a full index scan of index.
Examples are as follows:
explain select * from film where film_id between 1 and 10;
Copy the code
4) the ref
A non-unique index scan that returns all rows matching a single value. It is essentially an index access that returns all rows that match a single value, but it may find multiple rows that match, so it is a mixture of lookup and scan.
This type occurs only if a non-unique index or a non-unique prefix for a unique index is used.
Examples are as follows:
show index from film;
explain select * from film where title = 'ACADEMY DINOSAUR';
Copy the code
5) eq_ref
** Unique index scan. ** is common in primary key or unique index scans.
6) const
Found by index once, const is used to compare primary key or unique indexes. Because you only need to match one row of data, everything is fast. If you place the primary key in the WHERE list, mysql can convert the query to a const.
Examples are as follows:
show index from film;
explain select * from film where film_id = 1;
Copy the code
7) system
A table has only one row. This is a special case of const type and is rare, such as a system table.
6. Possible_keys columns
Shows which indexes are used in the query.
7. The key columns
The actual used index, if NULL, is not used. If an overwrite index is used in the query, it only appears in the key column.
The Possible_keys column shows which index makes for a more efficient query, while the key column shows which index was actually optimized to make it more efficient.
Examples are as follows:
show index from film_actor;
explain select actor_id,film_id from film_actor;
Copy the code
8. Key_len columns
Represents the number of bytes used in the index, the length of the cable used in the query (maximum possible length), not the actual length used, in theory the shorter the better. Key_len is computed from the table definition, not retrieved from the table.
9. Ref
Represents the column or constant const used to find the value in the index of the key column record.
10. Rows
Estimate the number of rows to read to find the desired one.
This number is the number of loops in the inline loop association plan. It is not the number of rows that are ultimately read from the table, but the average number of rows that MySQL must read in order to find rows that match the query, and can only be measured relative to each other.
11. Filtered column
The number of rows returned as a percentage of the number of rows read, the larger the value, the better.
Examples are as follows:
Select * from film_actor where acTOR_ID = 1 and select * from film_actor where ACTOR_ID = 1
12. Extra columns
Additional information, but very important.
Common values are as follows:
1) Using the index
Indicates that an overwrite index is used in SQL.
Examples are as follows:
2) Using the where
Many WHERE conditions involve columns in the index that can be checked by the storage engine when it reads the index, so not all queries with the · WHERE clause will display “Using WHERE”.
3) Using temporary
When sorting query results, a temporary table is used, as seen in order by and Group by.
4) Using filesort
An external index sort is used on the data, rather than reading the data sorted by the index in the table. That is, a sort that MySQL cannot do with an index is called a “file sort”.
Third, summary
What can we gain from this understanding of the execution plan?
- How does SQL use indexes
- Order of execution of complex SQL
- Query scanned data functions
- …
When faced with less-than-optimal SQL, we first look at the execution plan and analyze the possible problems based on the execution plan results to help guide us whether to add indexes, adjust SQL order, avoid improper writing, and so on.
The above is all the content of this article, I hope the content of this article for you in SQL performance optimization, SQL writing, there is a certain help.
Execution plans are really important, especially when it comes to SQL tuning.