MySQL explain keyword to see if the index is used

Explain shows how MySQL uses indexes to process SELECT statements and join tables. Can help select better indexes and write more optimized query statements. Simply put, its role is to analyze query performance.

The Explain keyword is very simple to use, just put it in front of the SELECT query statement.

MySQL to see if the index is used, simply look at the type types. If it is all, then the query statement iterates through all the rows and does not use the index.

Example: Explain select * from Company_info where cname like '% small% ' explain select * from Company_info where cname like ' small% '

1), the ID column of the larger the first to execute, if the number is the same size, then proceed from the top down, the ID is listed as NULL for the table is this is a result set, do not need to use it to query.

2), Select_type columns are common:
A:simple: Represents a simple select query that does not require a union operation or does not contain subqueries. When there is a connection query, the outer query is simple, and there is only one
B:primary: A select that requires a union operation or contains a subquery, and the select_type of the outermost unit query is primary. and only one
C:union:union connected Two select queries, the first query is a dervied derived table, except for the first table, the second table Select_type is the union
D:dependent Union: As with union, it appears in the Union or UNION ALL statement, but the query is affected by external queries
E:union Result: Contains the resultant Set of Union, in the Union and UNION all statements, because it does not need to participate in the query, so the ID field is NULL
F:subquery: In addition to subqueries contained in the FROM clause, subqueries that appear elsewhere may be subquery
G:dependent subquery: Similar to dependent union, indicates that this subquery query is affected by external table queries
A subquery, also called a derived table, that appears in a h:derived:from sentence may be called an inline view or a nested select in another database

3), table
The query table name is displayed, if the query uses an alias, then the alias is shown here, if the operation of the data table is not involved, then this is displayed as null if the <derived n> shown in angle brackets indicates that this is a temporary table, and the following N is the ID in the execution plan. Indicates that the result is derived from this query. If the <union m,n&gt is enclosed in angle brackets, like <derived n>, it is also a temporary table that indicates that the result is derived from the result set of the union query with the ID m,n.

4), type
From good to poor: System,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,all , except for all, other type can be used to the index, in addition to Index_merge, the other type can only use an index
A:system: There is only one row of data or empty table in the table and can only be used for MyISAM and memory tables. If it is a InnoDB engine table, the Type column in this case is usually all or index
B:const: When a unique index or primary key is used, the return record must be an equivalent where condition of 1 rows of records, usually the type is const. Other databases are also called unique index scans
C:eq_ref: In a query plan that is connected to a table, the driver table returns only one row of data, and the row 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, only when all columns are used as comparisons will appear eq_ref
D:ref: Unlike Eq_ref, which requires a connection order, there is no requirement for a primary key and a unique index, as long as an equality condition is used to retrieve it, a common search for equivalence with the secondary index is possible. Or a multi-column primary key, a unique index, the use of columns other than the first column as an equivalent lookup also appears, in short, the return data is not unique to the equivalent of a lookup can occur.
E:fulltext: Full-text index retrieval, note that full-text indexing is very high priority, if the full-text index and normal index exist simultaneously, MySQL regardless of the cost, priority to use full-text index
F:ref_or_null: Similar to the ref method, only increases the comparison of null values. The actual use is not much.
G:unique_subquery: For in-form subqueries in where, subqueries return distinct value unique values
H:index_subquery: Used for in-form subqueries with a secondary index or in constant list, the subquery may return duplicate values, and the subquery can be weighed using an index.
I:range: Index range scanning, common in queries that use operators such as >,<,is Null,between, in, and like.
J:index_merge: Indicates that the query uses more than two indexes, and finally takes the intersection or the set, common and, or the condition uses a different index, the official sort this after ref_or_null, but actually because to read the index, Performance may not be as good as range for most of the time
K:index: Index full table scan, sweep the index from start to finish, it is common to use indexed columns to handle queries that do not need to read data files, to sort or group queries by using indexes.
L:all: This is the full table scan data file, and then the server layer to filter back to meet the requirements of the record.

5), Possible_keys
The indexes that the query may use are listed here

6), Key
The index that the query really uses, when Select_type is Index_merge, there may be more than two indexes, and the other select_type will only appear here.

7), Key_len
The length of the index used to process the query, if it is a single-column index, then the entire index length, and if it is a multi-column index, then the query may not be able to use all the columns, the specific number of columns to use the index, here will be calculated, no use of the column, here is not counted in. Pay attention to the value of this column, and calculate the total length of your multi-column index to see if all the columns are used. Note that the index used by the ICP attribute of MySQL is not counted into it. In addition, Key_len only calculates the index length used by the Where condition, and sorting and grouping are not counted to Key_len, even if the index is used.

8), ref
If the constant equivalent query is used, the const is shown here, and if it is a connection query, the execution plan of the driver table shows the associated field of the driver table, if the condition uses an expression or function, or if the condition column has an internal implicit conversion, it may appear as Func

9), rows
Here is the estimated number of scanned rows in the execution plan, not the exact values

10), Extra
This column can display a lot of information, there are dozens of kinds, commonly used to have
A:DISTINCT: The DISTINC keyword was used in the Select section
B:no tables used: Query without FROM clause or from dual query
C: Use the Not in () Form of a subquery or a connection query for the NOT EXISTS operator, which is called an anti-join. That is, the general connection query is to query the inner table, and then query the appearance, anti-connection is to query the appearance first, then query the inner table.
D:using Filesort: This occurs when the index cannot be used when sorting. Common in order BY and GROUP by statements
E:using index: Query without the need to return to the table query, directly through the index to get the data of the query.
F:using Join buffer (block nested loop), using join buffer (batched key Accss): Version after 5.6.x optimizes the Bnl,bka attribute of the associated query. It is mainly to reduce the number of loops in the inner table and scan the query sequentially.
G:using sort_union,using_union,using intersect,using sort_intersection:
Using intersect: Represents a condition that is used to obtain an intersection from a processing result when using an index of and
Using union: Indicates that when a condition using an or connection is used for each index, that information represents a collection from the processing result
Using Sort_union and using sort_intersection: Similar to the previous two counterparts, except that they appear when querying for large amounts of information with and and OR, the primary key is queried, and then the merge is sorted before the record can be read and returned.
H:using Temporary: Indicates that interim tables are used to store intermediate results. Temporary tables can be memory temporary tables and disk temporary tables, the execution plan is not visible, you need to see the status variables, used_tmp_table,used_tmp_disk_table to see.
I:using Where: Indicates that not all of the records returned by the storage engine satisfy the query criteria and need to be filtered at the server level. The query criteria are divided into constraints and check conditions, before 5.6, the storage engine can only scan the data based on the constraints and return, and then the server layer is filtered based on the inspection criteria and then returns the data that really conforms to the query. After 5.6.x, the ICP feature can be pushed down to the storage engine layer, data that does not meet the check conditions and restrictions, and does not read directly, thus greatly reducing the number of records scanned by the storage engine. The extra column displays the using index condition
J:firstmatch (tb_name): 5.6.x introduces one of the new features of the optimized subquery, which is common in the WHERE clause contains a subquery of type in (). If the data volume of the inner table is larger, this may occur
K:loosescan (M.. N): One of the new features of the optimized subquery introduced after 5.6.x, which may occur when a subquery returns a duplicate record in the in () type subquery

In addition to these, there are a number of query data dictionary libraries, in the execution of the plan to find that there is no possibility of the results of some information

11), Filtered
This column appears when you use explain extended, and the version after 5.7 has this field by default and does not require the use of explain extended. This field indicates how much of the data returned by the storage engine is filtered by the server layer, the percentage of records that are satisfied with the query, and the percent, not the specific number of records.

Original address: https://www.cnblogs.com/acm-bingzi/p/mysqlExplain.html

MySQL explain keyword to see if the index is used