Preface: This article mainly explains how to use EXPLAIN, and explain various parameter concepts, and then talk about optimization.
A, Explain usage
Explain simulate how the Mysql optimizer executes SQL queries to see how Mysql processes your SQL statements. Analyze performance bottlenecks in your query or table structure.
Syntax: Explain + SQL statements;
Explain select * from user; The following SQL analysis results are generated. The following sections explain each field in detail
Second, the id
Is a group of numbers, representing the order of the query between multiple tables, or contains the order of the clause query statement. There are three types of IDS, which are explained in detail
- The ids are the same and the execution sequence is from top to bottom
- The IDS are different. For sub-queries, the IDS are incremented. A larger ID has a higher priority and is executed earlier
- The same id and different id exist at the same time
Third, select_type
Select_type contains the following values
- simple
- primary
- subquery
- derived
- union
- union result
simple
A simple SELECT query that does not contain subqueries or union queries
primary
If the SQL statement contains any subqueries, the outermost layer of the subquery is marked as primary
subquery
If a subquery is included in a SELECT or WHERE, the subquery is marked as a subQquery, as in 3. Two simultaneous occurrence
derived
Subqueries contained in from are marked as derived queries, and the query results are placed in a temporary table
union / union result
If you have two SELECT queries that are joined by a union, the second select will be marked as union, and the result of the union will be marked as union result. Its ID is null
Fourth, the table
Represents the data for which table this row is from
Five, the type,
Type indicates the index type used by MySQL. Different index types have different query efficiency
- system
- const
- eq_ref
- ref
- range
- index
- all
system
There is only one row in the table, and system is the const exception, which is almost never the case and can be ignored
const
MySQL can change the query condition to a constant that matches only one row of data and finds the data once
eq_ref
In multi-table queries, such as T1 and T2, only a single row can be found in T2 for a row in T1. To be clear, the condition for the associated query between T1 and T2 is primary key index or unique index. In this way, each row in T1 can be guaranteed to correspond to only one row in T2
EXPLAIN SELECT * from T1, t2 where t1.id = t2.id
ref
Not a primary key index, not a unique index, but a normal index that may return multiple rows that match the criteria.
range
It is reflected in the range search of an index, which generally occurs in the range search of BETWEEN, and, <, >, and in in where conditions.
index
Run through all index trees to find rows that match the criteria. The index file is still much smaller than the data file, so it is still much faster than a full table scan without an index.
all
Did not use the index, the simple table data are all traversed, find the data in line with the conditions
Six, possible_keys
If there are indexes on the fields involved in this query, they are listed, indicating the indexes that might be used, but not the ones that are actually required
Seven, the key
The index actually used in this query
Eight, key_len
Indicates the number of bytes used in the index. By using this attribute, you can know the index length used in the query. Note: This length is the maximum possible length, not the actual length
Nine, ref
Displays associated fields. If a constant equivalence query is used, const is displayed, and if a join query, the associated fields are displayed.
- Tb_emp table is a non-unique index scan, the actual index column used is IDx_name, because tb_emp.name=’rose’ is a constant, so ref=const.
- Table tb_EMP (ref=db01.tb_emp. depTID); table TB_EMP (ref=db01.tb_emp. depTID); table TB_EMP (ref=db01.tb_emp. depTID);
Ten, rows
Based on table statistics and index usage, a rough estimate of the number of rows that need to be read to find the desired record is as small as possible
Eleven, extra
Information that is not suitable for display in other columns, but is important for optimization purposes
Using fileSort
Select * from ‘order by’; select * from ‘group by’; select * from ‘select’; Select * from fileSort; select * from fileSort; select * from fileSort
Using temporary
Temporary tables are used to hold intermediate results, common in Order BY and Group by.
USING index
Coveing Index (Coveing Index) is used in the select operation, avoiding access to rows of the table. Using WHERE indicates that the index is used to perform a key lookup. If a using WHERE is not present at the same time, the surface index is used to read data rather than perform lookup actions.
Using wher
Indicates where filtering is used
using join buffer
Connection caching is used
impossible where
The value of the WHERE clause is always false and cannot be used to get any tuples
select tables optimized away
In the absence of the GROUPBY clause, optimization of MIN/MAX operations based on indexes or COUNT(*) operations for MyISAM storage engines does not have to wait until the execution stage to perform the calculation. Optimization is completed at the generation stage of the query execution plan.
distinct
Optimize distinct to stop looking for the same value once the first matching tuple is found
How to optimize MySQL index
Follow wechat public account: IT elder brother
Java foundation, Java Web, JavaEE all tutorials, including Spring Boot, etc
Reply: Resume template, you can get 100 beautiful resumes
Reply: Java learning route, you can get the latest and most complete a learning roadmap
Re: Java ebooks, get 13 must-read books for top programmers