explain field description

Adding explain in front of the sql statement to be executed can simulate the execution process of the statement by the optimizer.
Example description: explain select * from sys_user;

explain will return one or more pieces of data. The meaning of each column is explained below:

1. id execution order.

This is a relative size, which indicates the order of execution of each statement. The larger the size, the first to execute. When the id is the same, execute from top to bottom. In addition, during union query, the id of the outer union result statement is null, and the table column of this row will be in the form of < unionm, N >, indicating the union of rows with reference id values of M and N.

explain select realname from sys_user
union select par_name from  sys_param 

Query sys first_ Param table, and then query sys_user table, and finally combine the results of 1 and 2. The third row union1 and union2 correspond to the above two queries with id=1 and id=2

2, select_type query type.

Several common values are listed below

• SIMPLE simple query without union and sub query
• PRIMARY outermost query
• When there are sub queries, the outermost query is PRIMARY. In the union statement, the first query is also PRIMARY, as shown in the figure above. Note that it is not a PRIMARY key query and has nothing to do with the PRIMARY key
  
• In a UNION query, the second and subsequent queries are marked UNION

explain select realname from sys_user
union select par_name from  sys_param 

• DEPENDENT UNION
  In union statements that depend on external queries, the second and subsequent queries are marked as DEPENDENT UNION.
• DEPENDENT SUBQUERY depends on the first query in the subquery of the external query

What does the dependent external query mean here? That is, it is a sub query, but it refers to a table in the external query.

• Union result is the result of union. Generally, the id is null
  Typical examples:

explain select 
u.USER_USERNAME
from SYS_USER u 
where exists (select  UR_ROLE_ID from SYS_USER_ROLE where  UR_USER_ID= u.USER_USERNAME
union select ORG_NAME from SYS_ORGANIZATION org
where org.ORG_CODE = u.USER_ORGANIZATION_CODE) 

• Subquery in the SUBQUERY select clause or where condition (of course, this subquery does not depend on external queries)

explain select 
u.USER_USERNAME,(SELECT  max(ORG_NAME) from SYS_ORGANIZATION org1 where org1.ORG_CODE = u.USER_ORGANIZATION_CODE) as org_name,
(SELECT  min(ORG_NAME) from SYS_ORGANIZATION org2) as org_name1
from SYS_USER u 

• DERIVED derived query
  The subqueries contained in the FROM list are marked DERIVED

explain select 
u.USER_USERNAME
from SYS_USER u 
left join (select ORG_CODE,ORG_NAME from SYS_ORGANIZATION ) org  on org.ORG_CODE = u.USER_ORGANIZATION_CODE
left join (select UR_USER_ID,UR_ROLE_ID  from SYS_USER_ROLE) ur 
ON ur.UR_USER_ID = u.ID

• MATERIALIZED subquery, UNCACHEABLE SUBQUERY, UNCACHEABLE SUBQUERY (recalculate for each row of the outer query result), UNCACHEABLE UNION, UNCACHEABLE UNION query (in UNCACHEABLE SUBQUERY query, the second and subsequent Union)

3. table name

The table name of the query, but there are also three special cases

• 1. < unionm, N >: refers to the union of queries with id values of M and N
  
• 2. : derived table result referencing row with id value N

explain select 
u.USER_USERNAME
from SYS_USER u 
left join (select ORG_CODE,ORG_NAME from SYS_ORGANIZATION ) org  on org.ORG_CODE = u.USER_ORGANIZATION_CODE
left join (select UR_USER_ID,UR_ROLE_ID  from SYS_USER_ROLE) ur 
ON ur.UR_USER_ID = u.ID

Line 3 indicates the result of the derived query with reference id=3

4. Partitions partition (there is no such column in some old versions of mysql. You can add explain partitions before the query statement to appear)

The partition where the queried record is located. If it is not a partitioned table, the value is null

5. type (key!)

It should be called access type. It reflects how to query the table, whether it is possible to use the index, whether it is actually used, which indexes are used, whether the whole table is scanned, etc.
The result values from good to bad are: system > const > Eq_ ref > ref > full_ text > ref_ or_ null > index_ merge > unique_ subquery > index_ subquery > range > index > all
Common: const > EQ_ ref > ref > range > index > all
Now let's explain one by one

• system
  There is only one record in the table, which is similar to the system table. It is a special case of const, which is generally rare
• const
  There is only one matching record in the table, which can be found by indexing once. It is usually used to query that the primary key index or unique index is equal to a constant value

explain 
select * from sys_config
where id= 4

• eq_ref
  When comparing with other table records, only one record is matched. Common in primary key index or unique index scanning.

## HN_ENT_VISIT_INFO Manager_ Index creation id, Sys_ The primary key of user is the id field
explain 
select u.USER_REAL_NAME,c.* from HN_ENT_VISIT_INFO c left join SYS_USER u on u.id = c.manager_id

• ref non unique index scanning, which matches all rows corresponding to a value, including the leftmost prefix matching
• ref can be used to compare index columns using the = or < =, > = operators

explain select b.* from cs_mid_baseinfo b left join labels_enterprise_base leb on leb.pripid = b.pripid
## Put LEB Pripid = B. replace pripid with LEB Pripid > = B. so is pripid

• fulltext uses full-text indexing
• ref_or_null
  Similar to ref, but with additional optimizations for rows containing NULL values.
  ref_or_null first reads the reference key and then searches for rows with NULL key values separately, but note that this optimization can only handle one IS NULL.

##   You can use ref_or_null optimization
SELECT * FROM ref_table  WHERE key_column=expr OR key_column IS NULL;
## Null is used to optimize index lookup on expression (t1.a=t2.a AND t2.a IS NULL), and null optimization function cannot be used on b
SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL)
  OR (t1.b=t2.b AND t2.b IS NULL);
  

• index_merge indicates that index merge optimization is used. In this case, the key field lists the index list (multiple indexes) used, and the key_len lists the length list of the corresponding index

Index Merge our where condition or join involves multiple fields, and or between them. At this time, it is possible to use Index Merge. Index merging range scans multiple indexes of the same table, and then merges the results into one.
Note that the same table is mentioned here, and multiple indexes of multiple tables cannot be merged

explain select * from cs_mid_baseinfo
where pripid like  '3301020000131583%' or unicode = '913310030656305487'

The detailed contents of index merging will not be discussed here

• unique_subquery unique subquery. Similar to eq_ref, just in the subquery statement
  This type replaces the EQ of some IN subqueries IN the following form_ ref:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

explain 
select c.* from HN_ENT_VISIT_INFO c 
where c.manager_id in (select id from SYS_USER)

• index_subquery and unique_subquery is similar to subquery, except that it uses ordinary indexes instead of unique indexes. It usually appears in the following sub query

value IN (SELECT key_column FROM single_table WHERE some_expr)

• Range uses index range lookup
  When comparing an index column with a constant, it includes =, < >, >, <, > =, < =, in, like, is null, between

explain select * from cs_mid_baseinfo
where pripid like  '3301020000131583%' ;

explain select * from cs_mid_baseinfo
where pripid in( '3301020000131583' ,'3301840000298653');

• Because the full index is faster than the full index.
  index occurs in both cases
  1. The overlay index can meet the query requirements. In this case, the using index will appear in the Extra field
  2. The data scanned by the whole table needs to be sorted according to the index field. At this time, the using index will not appear in the Extra field

# pripid is the index field
explain select pripid from HN_ENT_VISIT_INFO；

• ALL full table scan

Generally, you should at least reach the range level. ALL must be avoided unless the table is small

6. possible keys the index that may be used when executing a query

7. Index used by key execution

The Mysql optimizer will evaluate whether to use the index and what index to use according to the table size, retrieval ratio and other aspects. For example, sometimes the amount of table data is small, and the cost of using the index may be greater than that of full table scanning (one more step back to table operation is used for index), which is not as good as full table scanning

8,key_ The length of the index used by Len. In the case of joint index, you can judge which part of the index is used according to this field

For the specific calculation method of index length, refer to Mysql federated index

9. ref reference information

10. The rows optimizer estimates the number of rows to scan

This data is not accurate, it's just an estimate. Of course, the smaller the value, the better. It can be used as a reference

11. Additional important information during Extra execution

Here are some important values, and some unimportant ones will not be explained one by one. You can refer to the official Mysql document: Mysql Document

1. Using Index
   Indicates that the current query uses the overlay index. Only the index tree is scanned, and there is no need to query data rows.
2. Using where indicates where criteria are used for filtering in query criteria

explain select pripid,unicode from cs_mid_baseinfo_copy
where pripid like '3301020000131583%' 
and unicode = '91330000CSXM11459E';

1. Using Index Condition indicates that index push down optimization is used
   In version 5.6 and later, mysql introduced a new optimization feature: index push down.

explain select * from cs_mid_baseinfo_copy
where pripid like '3301020000131583%' 
and unicode = '91330000CSXM11459E';

What is index push down optimization? I'll write it later
4. Using index for group-by
Similar to Using index, it means that MySQL has found an index that can be used to retrieve all columns of GROUP BY or DISTINCT query without any additional disk access to the actual table
5. Using join buffer join query uses block based nested loop join algorithm.
What is the block based nested loop join algorithm? I'll write it later

explain select * from cs_mid_baseinfo_copy c inner join  cs_ent_unicode u on u.entname = c.entname
where c.pripid like '3301020000131583%' 
and c.unicode = '91330000CSXM11459E';

6. Using filesort indicates that Mysql uses external sorting instead of index sorting. Note that although it is called filesort, it does not necessarily require file sorting. This depends on the amount of data to be sorted and sort_buffer_size variable value

explain select * from cs_mid_baseinfo_copy
order by entname

1. . Using temporary uses temporary tables to store query results

explain select pripid,entname,dom from cs_mid_baseinfo_copy
group by pripid
order by entname