Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief introduction to the EXPLAIN interpretation command in MySQL. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the EXPLAIN interpretation command in MySQL can bring you some practical help.

1 EXPLAIN concept

EXPLAIN will provide us with some information that MySQL is executing sql:

EXPLAIN can explain SELECT, DELETE, INSERT, REPLACE, and UPDATE and other statements. When EXPLAIN is used with interpretable statements, mysql displays some information about the sql execution plan from the optimizer. That is, mysql explains how it handles these statements and how tables are joined. For more information about how EXPLAIN gets the execution plan. When the EXPLAIN is followed by a session's connection_id rather than an executable statement, it displays the session's information. For SELECT statements, EXPLAIN generates additional execution plan information, which can be displayed in SHOW WARNINGS. EXPLAIN is useful for checking queries for design partition tables. The FORMAT option can be used to select the output format, and if the FORMAT option is not configured, the output is tabular by default. The JSON option allows the information to be displayed in json format.

2 EXPLAIN output column information

Field information output by EXPLAIN

The first column: column name, the second column: the equivalent attribute name displayed in the output when FORMAT = JSON, and the third column: field meaning

ColumnJSON NameMeaningidselect_idselect identification number select_typeNoneselect type tabletable_name this row of data is about which table's partitionspartitions matches the partition. For an unpartitioned table, the value is the join category used by the empty typeaccess_type, whether or not the index possible_keyspossible_keysMySQL can use which index to find the row in the table the key (index) key_lenkey_lengthMySQL actually decides to use key_lenkey_lengthMySQL determines the key length to use. If the key is NULL, the length of the NULLrefref column associated with the index rowsrowsmysql thinks that the number of rows that must be checked when performing sql filteredfiltered represents the percentage of data filtered by this query condition ExtraNone additional information

2.1 id

SELECT identifier. The sequence number of SELECT in the query, which can be empty.

2.2 select_type

SELECT type, all types are shown in the following table, EXPLAIN in JSON format exposes the SELECT type as a property of query_block, unless it is SIMPLE or PRIMARY. The JSON name (not applicable to None) is also displayed in the table.

Select_type ValueJSON NameMeaningSIMPLENone simple SELECT (without UNION or subqueries, etc.) the second or subsequent SELECT statement in the outermost query UNIONNoneUNION when the PRIMARYNone is nested, the second or subsequent SELECT statement in the DEPENDENT UNIONdependent (true) UNION, depending on the result of the external query UNION RESULTunion_resultUNION, the first selection in the DEPENDENT SUBQUERYdependent (true) subquery Depending on the external query DERIVEDNone derived table (temporary table generated in the subquery) MATERIALIZEDmaterialized_from_subquery materialized subquery UNCACHEABLE SUBQUERYcacheable (false) cannot cache the results of the subquery, each row of the external query must be recalculated the second or later selection in the UNCACHEABLE UNIONcacheable (false) UNION that belongs to the non-cacheable subquery (see UNCACHEABLE SUBQUERY)

Table information (for later demonstration):

Mysql > show create table tweea -+ | tyoga | CREATE TABLE `ta` (`id` bigint (20) NOT NULL DEFAULT '0mm, `age` int (20) DEFAULT NULL, `code` int (20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_ code` (`code`) KEY `age_ key` (`age`) ENGINE=InnoDB DEFAULT CHARSET=gbk | +-+-- + 1 row in set (0.03 sec)

SIMPLE: simple SELECT (no UNION or subqueries, etc.)

Mysql > explain select * from Tricha where id = 1 +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -+ | 1 | SIMPLE | Tosca | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +-+ -+ 1 row in set 1 warning (0.03 sec)

PRIMARY: the outermost query in a nested query

Mysql > explain select * from Thora where num > (select num from Thoua where id = 3) +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -PRIMARY | Tosca | NULL | range | num_key | num_key | 5 | NULL | 6 | 100.00 | Using where Using index | | 2 | SUBQUERY | Tosca | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +-+-- -+-+ 2 rows in set 1 warning (0.03 sec)

The second or subsequent SELECT statement in UNION:UNION

Mysql > explain select * from Thora where id = 9 union all select * from Thoua +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | PRIMARY | Tosca | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | 2 | UNION | Tentra | NULL | index | NULL | num_key | 5 | | NULL | 9 | 100.00 | Using index | + -+-+ 2 rows in set 1 warning (0.04 sec)

The second or subsequent SELECT statement in DEPENDENT UNION:UNION, depending on the external query

Mysql > explain select * from Thora where id in (select id from Thora where id > 8 union all select id from Thoua where id = 5) +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | PRIMARY | Tosca | NULL | index | NULL | | num_key | 5 | NULL | 9 | 100.00 | Using where | Using index | | 2 | DEPENDENT SUBQUERY | Tosca | NULL | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | 100.00 | Using where Using index | | 3 | DEPENDENT UNION | Tosca | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index | + -+-- +-- + 3 rows in set 1 warning (0.08 sec)

Results of UNION RESULT:UNION

Mysql > explain select num from Trapa where id = 3 union select num from Tuba where id = 4 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | PRIMARY | Tosca | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | | 2 | UNION | Tamboa | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | NULL | UNION RESULT | | NULL | ALL | NULL | Using temporary | + -+ 3 rows in set 1 warning (0.03 sec)

SUBQUERY: the first choice in a subquery

Mysql > explain select * from Thora where num > (select num from Thoua where id = 3) +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -PRIMARY | Tosca | NULL | range | num_key | num_key | 5 | NULL | 6 | 100.00 | Using where Using index | | 2 | SUBQUERY | Tosca | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +-+-- -+-+ 2 rows in set 1 warning (0.03 sec)

DEPENDENT SUBQUERY: the first choice in a subquery, depending on the external query

Mysql > explain select * from Thora where num in (select num from Thora where id = 3 union select num from Tricha where id = 4) +- -id | select_type | table | partitions | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | PRIMARY | Tyoga | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using where Using index | | 2 | DEPENDENT SUBQUERY | Tosca | NULL | const | PRIMARY,num_key | PRIMARY | 8 | const | 1 | 100.00 | NULL | 3 | DEPENDENT UNION | Tosca | NULL | const | PRIMARY Num_key | PRIMARY | 8 | const | 1 | NULL | | NULL | UNION RESULT | | NULL | ALL | NULL | Using temporary | + -+ 4 rows in set 1 warning (0.12 sec)

DERIVED: derived tables (temporary tables generated in subqueries)

Mysql > explain select a.id from (select id from Thora where id > 8 union all select id from Thuna where id = 5) a +- -- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | PRIMARY | | NULL | ALL | NULL | 3 | | 100.00 | NULL | | 2 | DERIVED | Tosca | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where | Using index | | 3 | UNION | Tosca | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index | + -+-- +-- + 3 rows in set 1 warning (0.12 sec) 2.3 table

Show which table the data in this row is about, sometimes the real table name, and sometimes the following results

Indicates that id is the union of the result of the N row of MBI: this row refers to the derived table result of the row whose id value is n. Derived tables may come from subqueries in the from clause, for example. This row refers to the result of the materialized subquery of the row with id value n 2.4 partitions

The partition to which the record of the query belongs. For unpartitioned tables, the value is NULL.

2.5 type

Which categories are used for connections and whether indexes are used? the common types are: system, const, eq_ref, ref, range, index, ALL (from left to right, performance is getting worse and worse). For more information, please see EXPLAIN Join Types.

NULL:MySQL breaks up statements during optimization and executes without even accessing tables or indexes. For example, selecting a minimum value from an index column can be done through a separate index lookup.

System: this table (or perhaps a queried temporary table) has only one row of data (= system table). It is a special case of const.

Const: the table has at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constant by the rest of the optimizer. Const tables are fast because they are only read once! Const is used to query all parts when the condition is PRIMARY KEY or UNIQUE index and compared to a constant value.

In the following query, tbl_name can be used for the const table:

SELECT * from tbl_name WHERE primary_key=1;SELECT * from tbl_name WHERE primary_key_part1=1 and primary_key_part2=2 -- example mysql > explain select * from tweea where id = 1 +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -+ | 1 | SIMPLE | Tosca | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +-+ -+ 1 row in set 1 warning (0.07 sec)

Eq_ref: for each combination of rows in the previous tables, read a row from that table. Apart from system and const, this is the best connection type. It is used when the join uses all parts of the index and the index is a primary key or the only non-null index. Eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses the columns of the table read before the table.

In the following example, MySQL can use eq_ref joins to handle ref_tables:

Example of SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;-- (tfolb is the replicated table of tyoga, with the same table structure) mysql > explain select * from tabila where t_a.code=t_b.code +-- + -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- +-+ | 1 | SIMPLE | Tosca | NULL | ALL | uk_code | NULL | 9 | 100.00 | NULL | | 1 | SIMPLE | Tubb | NULL | eq_ref | | uk_code | uk_code | 4 | test.t_a.code | 1 | 100.00 | NULL | +-+ | -+ 2 rows in set 1 warning (0.03 sec)

Ref for each row combination from the previous table, all rows with matching index values will be read from this table. Use ref if the join uses only the leftmost prefix of the key, or if the key is not UNIQUE or PRIMARY KEY (in other words, if the join cannot query a single row based on the keyword). This join type is good if the keys used match only a small number of rows. Ref can be used for indexed columns that use the = or operator.

In the following example, MySQL can use ref joins to handle ref_tables:

Example of SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;-- (tfolb is a replicated table with the same table structure) mysql > explain select * from tactile where t_a.age=t_b.age +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -- +-+ | 1 | SIMPLE | Tosca | NULL | ALL | age_key | NULL | 9 | 100.00 | Using where | | 1 | SIMPLE | Tubb | NULL | | ref | age_key | age_key | 5 | test.t_a.age | 1 | 100.00 | NULL | + -+ 2 rows in set 1 warning (0.03 sec)

Fulltext: performing a join using a FULLTEXT index

Ref_or_null: the join type ref is similar, but MySQL is added to specifically search for rows that contain null values. The optimization of this join type is often used in solving subqueries.

In the following example, MySQL can use ref_or_null joins to handle ref_tables:

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;-- example mysql > explain select * from tweea where t_a.age = 3 or t_a.age is null +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+-- + | 1 | SIMPLE | Tosca | NULL | ref_or_null | age_key | age_key | 5 | | const | 2 | 100.00 | Using index condition | +-- +-| -+-+-- + 1 row in set 1 warning (0.03 sec)

Index_merge: this join type indicates that the index merge optimization method is used. In this case, the key column contains a list of the indexes used, and the key_len contains the longest key elements of the index used.

SELECT * FROM ref_table WHERE idx1=expr1 OR idx2 = expr2;-- example mysql > explain select * from tweea where t_a.code = 3 or t_a.age = 3 +- -+-- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+- -- + | 1 | SIMPLE | Tosca | NULL | index_merge | uk_code Age_key | uk_code,age_key | 4Page5 | NULL | 2 | 100.00 | Using union (uk_code,age_key) Using where | +- -+-+ 1 row in set 1 warning (0.03 sec)

Unique_subquery: this type replaces the ref of the in subquery in the following form:

Value IN (SELECT primary_key FROM single_table WHERE some_expr)

Unique_subquery is an index lookup function that can completely replace subqueries and is more efficient.

Index_subquery: this join type is similar to unique_subquery. In subqueries can be replaced, but only for non-unique indexes in the following forms of subqueries:

Value IN (SELECT key_column FROM single_table WHERE some_expr)

Range: only retrieve a given range of rows, using an index to select rows. The key column shows which index is used. Key_len contains the longest key element of the index used. In this type, ref is listed as NULL. When using =, >, > =, 8 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -- + | 1 | SIMPLE | Tosca | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where | + -+ 1 row in set 1 warning (0.03 sec)

Index: this join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files. MySQL can use this join type when the query uses only columns that are part of a single index.

ALL: perform a complete table scan for each row combination from the previous table. If the table is the first table that is not marked with const, this is usually not good and is usually bad in its case. It is usually possible to add more indexes instead of using ALL so that rows can be retrieved based on constant values or column values in the previous table.

2.6 possible_keys

The possible_ Keys column indicates which index MySQL can use to find rows in the table. Note that this column is completely independent of the order of the tables shown in the EXPLAIN output. This means that some keys in possible_keys cannot actually be used in the order of the generated table.

If the column is NULL, there is no associated index. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns that are suitable for the index. If so, create an appropriate index and check the query again with EXPLAIN

2.7 key

The key column shows the key (index) that MySQL actually decides to use. If no index is selected, the key is NULL. To force MySQL to use or ignore indexes in possible_keys columns, use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.

2.8 key_len

The key_len column shows the key length that MySQL decides to use. If the key is NULL, the length is NULL.

The length of the index used. The shorter the length, the better without losing accuracy.

2.9 ref

The ref column shows which column or constant is used to select rows from the table with key.

2.10 rows

The rows column shows the number of rows that MySQL thinks it must check when executing the query.

2.11 Extra

This column contains the details of the query resolved by MySQL, which are detailed below.

Distinct: once MYSQL finds a row that matches the row union, it no longer searches Not exists:MYSQL optimized LEFT JOIN, and once it finds a row that matches the LEFT JOIN standard, it no longer searches Range checked for each: no ideal index is found, so for each combination of rows from the previous table, MYSQL checks which index to use and uses it to return rows from the table. This is one of the slowest joins to use the index Using filesort: when you see this, the query needs to be optimized. MYSQL needs to take additional steps to discover how to sort the returned rows. It sorts all rows Using index by join type and row pointers that store the sort key values and matching criteria: the column data is returned from a table that only uses the information in the index and does not read the actual action, which occurs when all the request columns on the table are part of the same index Using temporary: when you see this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results, which usually happens on ORDER BY different sets of columns, rather than Using where on GROUP BY: the WHERE clause is used to restrict which rows will match the next table or be returned to the user. This happens if you do not want to return all the rows in the table and the join type is ALL or index, or there is a problem with the query

This is the end of the EXPLAIN explanation command in MySQL. If you want to know other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.