Recommended learning: mysql video tutorial
Use the explain keyword to simulate the optimizer executing SQL query statements, so as to know that MySQL is How to handle your SQL statements and analyze the performance bottlenecks of your query statements or table structures.
#The most important fields are: id, type, key, rows, Extra
Select query sequence number, including a set of numbers, indicating the order in which select clauses or operation tables are executed in the query
Three situations:
1. Same id: execution order from top to bottom
2. Different id: if it is a child For queries, the serial number of the id will increase. The larger the id value, the higher the priority and the earlier it will be executed
3. The id is the same but different (both cases are at the same time Exists) : If the id is the same, it can be considered as a group and executed sequentially from top to bottom; among all groups, the larger the id value, the higher the priority, and the earlier it is executed
The type of query is mainly used to distinguish complex queries such as ordinary queries, joint queries, subqueries, etc.
Access type, a very important indicator in SQL query optimization, the result values from best to worst are:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
Generally speaking, a good sql query reaches at least the range level, and preferably reaches the ref
##1. system: The table has only one row of records (equal to System table), this is a special case of the const type, which does not usually appear and can be ignored
2. const: means it is found through the index once, const is used to compare the primary key or unique index. Because you only need to match one row of data, it's very fast. If the primary key is placed in the where list, mysql can convert the query into a const
: unique index scan, for For each index key, only one record in the table matches it. Commonly seen in primary key or unique index scans.
Note: ALL full table scan of the table with the fewest records, such as the t1 table
: non-unique index Scan to return all rows matching a single value. Essentially, it is also an index access, which returns all rows that match a single value. However, it may find multiple rows that meet the criteria, so it should be a mixture of search and scan
5, range: Retrieve only rows in a given range, using an index to select rows. The key column shows which index is used. Generally, queries such as bettween, , in, etc. appear in the where statement. This range scan on index columns is better than a full index scan. It only needs to start at a certain point and end at another point, without scanning the entire index
6, index: Full Index Scan, index and ALL The difference is that the index type only traverses the index tree. This is usually ALL blocks, as index files are usually smaller than data files. (Although Index and ALL both read the entire table, index is read from the index, while ALL is read from the hard disk)
7, ALL: Full Table Scan, traverse the entire table to find matching rows
There is an index on the fields involved in the query, then the index will Listed, but not necessarily used by the query
The index actually used, if NULL, no index is used.
If a covering index is used in the query, the index will only appear in the key list
indicates the number of bytes used in the index and the length of the index used in the query (the maximum possible length), not the actual length used. In theory, the shorter the length, the better. key_len is calculated based on the table definition, not retrieved from the table.
The column showing the index is used. If possible, it is a constant const.
Based on table statistics and index selection, roughly estimate the number of rows that need to be read to find the required records
Not suitable for display in other fields, but very important additional information
1. Using filesort:
mysql uses an external index to sort the data, rather than sorting by The index within the table performs sorted reads. That is to say, mysql cannot use the index to complete the sorting operation to become "file sorting"
Since the index is sorted by email first and then by address, if you query directly by Address sorting, the index cannot meet the requirements, and mysql must implement "file sorting" again
2. Using temporary:
Use temporary tables to save intermediate results. That is to say, mysql uses temporary tables when sorting query results, which is common in order by and group by
3. Using index:
Indicates that Covering Index (Covering Index) is used in the corresponding select operation, which avoids accessing the data rows of the table and is highly efficient.
If Using where appears at the same time, it indicates that the index has been Used to perform search of index key values (refer to the picture above)
If it is not used and "Using where" appears at the same time, it indicates that the index is used to read data rather than perform search actions
Covering Index (Covering Index): Also called index coverage. It is the fields in the select list that can be obtained only from the index. There is no need to read the data file again according to the index. In other words, the query column must be covered by the built index .
Note:
4. Using where:
Using where filtering
5. Using join buffer:
Using link cache
6. Impossible WHERE:
where clause The value is always false and cannot be used to obtain any ancestor
7. Select tables optimized away:
without group by In the case of clauses, optimizing the MIN/MAX operation based on the index or optimizing the COUNT(*) operation for the MyISAM storage engine does not have to wait until the execution phase to perform calculations. The optimization can be completed during the query execution plan generation phase
8. distinct:
Optimize distinct operation, stop looking for equally worthy actions after finding the first matching ancestor
1 (id = 4), [select id, name from t2]: select_type is union, specify id The selection of =4 is the second selection in the union.
2 (id = 3), [select id, name from t1 where address = '11']: Because it is a subquery included in the from statement, it is marked as DERIVED (derived), where address = '11' can be retrieved through the composite index idx_name_email_address, so the type is index.
3 (id = 2), [select id from t3]: Because it is a subquery included in select, it is marked as SUBQUERY.
4 (id = 1), [select d1.name, … d2 from … d1]: select_type is PRIMARY, which means that the query is the outermost query, and the table column is marked as "derived3", which means that the query results come from In a derived table (select result of id = 3).
5 (id = NULL), [... union...]: Represents the stage of reading rows from the union's temporary table. "Union 1, 4" in the table column means id=1 and id=4 Perform union operation on the select results.
Recommended learning: mysql video tutorial
The above is the detailed content of How to view execution plan in Mysql. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
