Have you ever encountered a slow query execution after a table is indexed? Confused whether the index is in effect? With that in mind, let’s take a look at the next must-have tool for MySQL database developers — see the execution plan.
View execution plan methods
1, command line: EXPLAIN + SQL
2, interface mode: Navicat built-in
The most important columns are ID, Type, key, rows and Extra.
Detailed description of each field
id
Indicates the order in which tables are loaded in each SQL statement execution plan.
1. If the ID value is the same, the command is executed from top to bottom.
As shown above, the loading order of the corresponding tables is T1 > T3 > T2 (the tables here are aliases).
2. If the ID value is different, the one with a large ID value is preferentially executed.
As shown above, the loading sequence of the corresponding tables is T3 > T2 > T1 (the tables here are aliases).
3. If the IDS are the same and there are different IDS, the group with the same ID is a group, and the group with different IDS is executed from top to bottom.
As shown above, the loading sequence of the corresponding table is T3 > Derived2 > T2 > T4 (the table here is an alias). Derived2 is a derived table generated in the step with ID 2.
select_type
Query types are mainly used to distinguish common query, joint query, sub-query and other complex queries
1. SIMPLE: a SIMPLE select query that does not contain subqueries or unions
PRIMARY: The outermost query that contains any complex subparts is marked as PRIMARY
SUBQUERY: Contains subqueries in select or WHERE lists
4. DERIVED: Subqueries contained in the FROM list are labeled as DERIVED, and mysql, or recursively, executes these subqueries, placing the results in a zero-time table
5, UNION: if the second select occurs after the UNION, it is marked as UNION; If union is included in the subquery of the FROM clause, the outer select is marked as derived
6, UNION RESULT: select the RESULT from UNION table
type
The type of access, an important metric in SQL query optimization, results from good to bad are:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL;
In actual development scenarios, the following types are common:
Const > eq_ref > ref > range > index > ALL,
In general, good SQL queries are at least range and preferably ref.
1, const: indicates that the index is found once. Const is used for 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;
Eq_ref: unique index scan. For each index key, only one record in the table matches it. Common with primary key or unique index scans;
Ref: non-unique index scan, return all rows matching a single value;
Range: uses an index to retrieve rows in a specified range. Generally, range queries such as between, <, >, and IN will appear in the WHERE statement.
5, index: Full index Scan, only Scan index tree; Query only index columns and perform full table scan for indexes
6, ALL: Full Table Scan, find matching rows; Query columns containing non-index data, full table scan; Compared with index, ALL scans disk data, and index traverses the index tree.
possible_keys
If an index exists on a field involved in the query, the index will be listed but not necessarily used by the query
key
If the index is NULL, it indicates that the index is not used, which is also the key to determine whether the index is used.
If an overwrite index is used in the query, it only appears in the key list.
key_len
Indicates the number of bytes used by the index. Based on this value, you can determine the usage of the index. Especially when combining indexes, it is very important to determine how much part of the index is used. Key_len is computed from the table definition
ref
Shows which columns of the index are referenced, usually the corresponding field or const;
rows
Approximate the number of rows scanned to find the desired record data based on table statistics and index usage;
Extra
Contains additional information that is important but not suitable for display in other columns; The following messages usually appear:
1, Using the index
Indicates that the corresponding SELECT operation uses an overwrite index, avoiding the access to the data row of the table, which is efficient
Overwrite index: also called index overwrite. A column in a select list can be retrieved only from the index, without having to read the data file again against the index. In other words, the query column is overwritten by the created index. Note: a, if you want to use overwrite index, select a column from the select list, do not use select * B, if all the fields are indexed, the index file will be too large, which will reduce CRUD performance
2, Using filesort
MySQL uses an external index to sort data (file sort), instead of using an internal index. This situation needs to be avoided in SQL query, and it is best not to appear this type in Extra;
Sorting with unindexed fields is usually caused BY the use of an ORDER BY statement, as is sorting with indexed fields in the wrong way, such as when combined indexes are used incorrectly.
3, Using temporary
Create a temporary table to save intermediate results, this SQL is not allowed to meet a large amount of data, the basic can not run;
This type is often caused BY ORDER BY and GROUP BY, so when sorting data and grouping queries, it is important to pay attention to the proper use of indexes.
4, Using WHERE: Use where to filter data
5, Using join buffer
Indicates that the table join cache is used. If the amount of table data is large, the buffer may be too large and the query efficiency may be low. In this case, use indexes correctly in the table join fields. If the table join query is slow, try adding an index to the join field.
6, Impossible WHERE
Select ‘where’ from ‘where’;
