Explanation keyword of MySQL

In this paper, from ：

• https://segmentfault.com/a/1190000021458117?utm_source=tag-newest

• https://www.cnblogs.com/tufujie/p/9413852.html

One 、 Start

When one SQL Query is slow , Want to see this SQL When implementing , We can use explain Key words to SQL Analyze .

Use explain keyword , It will return a result , There are 12 A field , Each field identifies a meaning , We analyze it according to its meaning .

key	desc
id	select The serial number of the query , Contains a set of numbers , Represents execution in a query select The order of clauses or operation tables
select_type	Query type
table	Which table is being visited
partitions	Matching partition
type	Type of visit
possible_keys	Show the indexes that may be applied to this table , One or more , But it doesn't necessarily apply to
key	Index actually used , If NULL, No index is used
key_len	Represents the number of bytes used in the index , You can use this column to calculate the length of the index used in the query
ref	Shows which column of the index is used , If possible , It's a constant , Which columns or constants are used to find... On indexed columns
rows	According to table statistics and index selection , Roughly estimate the number of rows to read to find the required record
filtered	The percentage of table rows queried in the table
Extra	Contains additional information that is not suitable for display in other columns but is important

Two 、 Field meaning

2-1、id

1. id Phase at the same time , The order of execution is from top to bottom

2. If it's a subquery ,id The serial number of will increase ,id The higher the value, the higher the priority , The first to be executed

3. id If the same , It can be thought of as a group , From top to bottom ; In all groups ,id The bigger the value is. , The higher the priority , Execute first

2-2、select_type

Each select The type of clause

key	desc
SIMPLE	Simple SELECT, Don't use UNION Or subquery, etc
PRIMARY	The outermost query in the subquery , If the query contains any complex sub parts , The outermost select Marked as PRIMARY
UNION	If the second select Appear in the uion after , Is marked as UNION
DEPENDENT UNION	UNION The second or later of SELECT sentence , Depends on external queries
UNION RESULT	UNION Result ,UNION The second... In the sentence SELECT Start all the back SELECT
SUBQUERY	First in subquery SELECT, The results don't depend on external queries
DEPENDENT SUBQUERY	First in subquery SELECT, Depends on external queries
DERIVED	Derived from table SELECT, FROM A subquery of a clause
UNCACHEABLE SUBQUERY	The result of a subquery cannot be cached , The first line of the outer link must be reevaluated

2-3、table

Show the table names in the database accessed in this step （ Show which table this row's data is about ）, Sometimes it's not a real name , It could be an alias .

2-4、partitions

Matching partition

2-5、type

How to access the table , Express MySQL How to find the desired row in the table , also called Access type

Common types are ： ALL、index、range、 ref、eq_ref、const、system、NULL（ From left to right , Performance from poor to good ）

key	desc
ALL	Full Table Scan, Will traverse the entire table to find the matching row
index	Full Index Scan,index And ALL The difference for index Type only traverses the index tree
range	Retrieve only rows in the given range , Use an index to select rows
ref	Non unique index scan , Returns all rows that match a single value
eq_ref	similar ref, The difference is that the index used is the only index , For each index key value , Only one record in the table matches , Simply speaking , It is used in multi table connection primary key perhaps unique key As a condition of Association
const、system	When MySQL Optimize some part of the query , And convert to a constant , Use these types to access . For example, place the primary key in where In the list ,MySQL You can convert the query to a constant ,system yes const Special case of type , When the query table has only one row , Use system
NULL	MySQL Decompose statements during optimization , Execute without even accessing tables or indexes , For example, selecting the minimum value from an index column can be done through a separate index search .

2-6、possible_keys

Show the indexes that may be applied to this table , One or more . If there is an index in the field involved in the query , Then the index will be listed , But it doesn't have to be used .

2-7、key

key Columns show MySQL Actually decide which key to use （ Indexes ）, Must be included in possible_keys in

2-8、key_len

Represents the number of bytes used in the index , You can use this column to calculate the length of the index used in the query （key_len The displayed value is the maximum possible length of the index field , It's not the actual length , namely key_len It is calculated according to the table definition , It is not retrieved from the table ）

Without loss of accuracy , The shorter the length, the better

2-9、ref

Shows which column of the index is used , If possible , It's a constant , Which columns or constants are used to find values on index columns

2-10、rows

According to table statistics and index selection , Roughly estimate the number of rows to read to find the required record

2-11、filtered

The percentage of table rows queried in the table

2-12、Extra

Contains additional information that is not suitable for display in other columns but is important

key	desc
Using filesort	explain MySQL Will use an external index to sort the data , Instead of reading in the order of the indexes in the table
Using temporary	Temporary tables are used to save intermediate results ,MySQL Use temporary tables when sorting results , It is common in sorting order by And group queries group by
Using index	It means corresponding select The override index is used in the operation （Covering Index）, Avoid accessing the data rows of the table , Good efficiency
Using where	Used where Conditions
Using join buffer	The change emphasizes that no index is used to get the connection conditions , And you need to connect buffers to store intermediate results . If this value appears , That should pay attention to , Depending on the specific situation of the query, you may need to add indexes to improve the performance .
impossible where	This value emphasizes where Statement will result in no eligible rows
distinct	once mysql Found a row that matches the row Union , No more searching
Select tables optimized away	SELECT The operation has been optimized so that it can no longer be optimized （MySQL Return data without traversing the table or index at all ）
No tables used	Query Use in statement from dual Or without any from Clause