How to optimize MySQL index function based on Explain keyword

EXPLAIN shows how MySQL uses indexes to process select statements and join tables. It can help choose better indexes and write more optimized query statements. Simply put, its function is to analyze query performance.

The usage of the explain keyword is very simple, just put it in front of the select query statement.

To check whether MySQL uses an index, simply look at the type. If it is all, it means that this query statement traverses all rows and does not use the index.

For example: explain select * from company_info where cname like '%小%'

explain select * from company_info where cname like '小%'

the following

1) The larger the number in the id column, the earlier it is executed. If the numbers are the same, they are executed from top to bottom. If the id column is null, it means that this is a result set and there is no need to use it for query.

2) Common select_type columns include:

• A: simple: indicates a simple select query that does not require a union operation or does not contain a subquery. When there is a join query, the outer query is simple and there is only one
• B: primary: A select that requires a union operation or contains a subquery. The select_type of the outermost unit query is primary. And only one
• C: union: two select queries connected by union, the first query is a derived table, except for the first table, the select_type of the second and subsequent tables is union
• D: Dependent union: Like union, it appears in a union or union all statement, but this query is affected by the external query
• E: union result: contains the result set of the union. In the union and union all statements, the id field is null because it does not need to participate in the query.
• F: subquery: Except for the subquery contained in the from clause, any subquery that appears elsewhere may be a subquery.
• G: dependent subquery: Similar to dependent union, it means that the query of this subquery is affected by the query of the external table.
• H: derived: The subquery that appears in the from clause is also called a derived table. Other databases may call it an inline view or nested select.
  

3) table

The query table name is displayed. If the query uses an alias, the alias is displayed here. If it does not involve operations on the data table, it is displayed as null. If it is displayed as <derived N> enclosed in angle brackets, it means that this is a temporary table. The N behind it is the id in the execution plan, indicating that the result comes from this query. If it is <union M,N> enclosed in angle brackets, it is similar to <derived N> and is also a temporary table, indicating that this result comes from the result set of the union query with id M,N.

4) Type

From best to worst: system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL. Except for all, all other types can use indexes. Except for index_merge, other types can only use one index.

• A: system: The table contains only one row of data or is an empty table, and can only be used for MyISAM and Memory tables. If it is an Innodb engine table, the type column in this case is usually all or index
• B: const: When using a unique index or primary key and the returned records must be equal to one row of records in the where condition, the type is usually const. Other databases also call it unique index scan
• C: eq_ref: Appears in the query plan to connect to two tables. The driving table returns only one row of data, and this row of data is the primary key or unique index of the second table, and must be not null. When the unique index and primary key are multiple columns, eq_ref will appear only when all columns are used for comparison.
• D: ref: Unlike eq_ref, it does not require a connection order, nor does it have a primary key or unique index requirement. It can appear when searching with equal conditions, and is commonly used for equal value searches with auxiliary indexes. Or in multi-column primary keys and unique indexes, using columns other than the first column as equal value searches may also occur. In short, equal value searches that return non-unique data may occur.
• E: fulltext: Full-text index retrieval. Please note that the full-text index has a high priority. If the full-text index and the ordinary index exist at the same time, MySQL will give priority to the full-text index regardless of the cost.
• F: ref_or_null: Similar to the ref method, except that a comparison of null values ​​is added. Not much actually used.
• G: unique_subquery: used for in-form subqueries in where, the subquery returns unique values ​​without duplication
• H: index_subquery: used for in-form subqueries that use auxiliary indexes or in constant lists. The subquery may return duplicate values, and the index can be used to deduplicate the subquery.
• I: range: index range scan, commonly used in queries using operators such as >, <, is null, between, in, like, etc.
• J: index_merge: indicates that the query uses more than two indexes, and finally takes the intersection or union. Common and or conditions use different indexes. The official ranking is after ref_or_null, but in fact, since all indexes need to be read, the performance may not be as good as range most of the time.
• K: index: full table scan of the index, scanning the index from beginning to end. It is common to use index columns to process queries that do not need to read data files, and queries that can use index sorting or grouping.
• L: all: This is to scan the data file in the entire table, and then filter it at the server level to return the records that meet the requirements.
  

5), possible_keys

All indexes that may be used by the query are listed here

6) key

Query the indexes actually used. When select_type is index_merge, more than two indexes may appear here. For other select_types, only one index will appear here.

7) key_len

The length of the index used to process the query. If it is a single-column index, the entire index length is included. If it is a multi-column index, the query may not use all columns. The specific number of columns used will be calculated here. Columns that are not used will not be calculated here. Pay attention to the value of this column and calculate the total length of your multi-column index to see if all columns are used. Note that indexes used by MySQL's ICP feature are not counted. In addition, key_len only calculates the length of the index used in the where condition, and even if the index is used for sorting and grouping, it will not be calculated in key_len.

8) ref

If the query is a constant equal value query, const will be displayed here. If it is a join query, the execution plan of the driven table will display the associated fields of the driving table. If the condition uses an expression or function, or the condition column has an internal implicit conversion, it may be displayed as func.

9) rows

Here is the estimated number of scan rows in the execution plan, not the exact value

10) Extra

This column can display a lot of information, there are dozens of types, the commonly used ones are

• A: distinct: The distinct keyword is used in the select part
• B: no tables used: query without from clause or from dual query
• C: A join query using a subquery of the form not in() or the not exists operator is called an anti-join. That is, a general join query queries the inner table first and then the outer table, while an anti-join queries the outer table first and then the inner table.
• D: using filesort: This occurs when the index cannot be used during sorting. Commonly used in order by and group by statements
• E: using index: When querying, there is no need to return to the table for query. The queried data can be obtained directly through the index.
• F: using join buffer (block nested loop), using join buffer (batched key access): Versions after 5.6.x optimize the BNL and BKA features of associated queries. The main purpose is to reduce the number of loops in the inner table and to compare sequential scan queries.
• G: using sort_union, using_union, using intersect, using sort_intersection:
• using intersect: indicates the conditions for using each index of and, this information indicates that the intersection is obtained from the processing results
• Using union: indicates that when using or to connect the conditions that use the index, this information indicates that the union is obtained from the processing results.
• using sort_union and using sort_intersection: Similar to the previous two, except that they appear when using and and or to query a large amount of information. The primary key is queried first, and then the sorting and merging are performed before the records can be read and returned.
• H: using temporary: indicates that a temporary table is used to store intermediate results. Temporary tables can be memory temporary tables and disk temporary tables. They cannot be seen in the execution plan and can only be seen by checking the status variables, used_tmp_table, and used_tmp_disk_table.
• I: using where: indicates that not all records returned by the storage engine meet the query conditions and need to be filtered at the server level. The query conditions are divided into restriction conditions and check conditions. Before 5.6, the storage engine could only scan and return data based on the restriction conditions, and then the server layer would filter based on the check conditions and return the data that actually met the query. 5.6.x and later versions support the ICP feature, which can push the check conditions down to the storage engine layer. Data that does not meet the check conditions and restriction conditions will not be read directly, which greatly reduces the number of records scanned by the storage engine. The extra column shows using index condition
• J: firstmatch(tb_name): One of the new features for optimizing subqueries introduced in 5.6.x, commonly seen in where clauses containing in() type subqueries. If the amount of data in the internal table is large, this may occur
• K: loosescan(m..n): One of the new features for optimizing subqueries introduced after 5.6.x. This may occur when the subquery returns duplicate records in an in() type subquery.
  

In addition to these, there are many query data dictionary libraries, and some prompt messages that are impossible to obtain results are found during the execution plan.

11), filtered

This column appears when explain extended is used. Versions after 5.7 have this field by default, so explain extended is no longer needed. This field indicates the ratio of the number of records that satisfy the query after the data returned by the storage engine is filtered at the server level. Note that it is a percentage, not a specific number of records.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.