Shulou(Shulou.com)05/31 Report--

This article mainly introduces "mysql obtains the information of query execution plan through explain". In daily operation, I believe many people have doubts about mysql obtaining the information of query execution plan through explain. Xiaobian consulted all kinds of materials and sorted out simple and easy operation methods. I hope to help you answer the doubts of "mysql obtains the information of query execution plan through explain"! Next, please follow the small series to learn together!

explain Get information about the query execution plan

Add explain before select keyword in query, and return information about each step in execution plan when query is executed.

mysql> select 1\G;

* 1. row *

1: 1

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> explain select 1\G;

* 1. row *

id: 1

select_type: SIMPLE

table: NULL

partitions: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

filtered: NULL

Extra: No tables used

1 row in set, 1 warning (0.00 sec)

ERROR:

No query specified

explain two variations

explain extended: Tells the server to "decompile" the execution plan into a select speech, which can be seen by running show warnings immediately after it. This statement comes directly from the executor

Draw, rather than the original sql statement, has become a data structure at this point. You can examine exactly how the query optimizer transforms statements. explain extended Available in Mysql and later versions

explain partitions displays partitions that the query will access if the query is based on partitioned tables. It exists in MySQL 5.1 and later.

Column in explain

ID column

If there is no subquery or union in the statement, then there is only a unique select, so each row will display 1 in this column. Otherwise, the inner select statement will generally be sequentially compiled.

corresponding to its position in the original sentence.

mysql divides select queries into simple and complex types, and complex types can be divided into three categories: simple subqueries, so-called derived tables (subqueries in the from clause), and UNION queries.

Example:

Simple query: select 1;

complex-simple subquery: select( select 1 from us_user limit 1) from us_user_role;

complex-derived table: select * from us_user where id in (select user_id from us_user_role);

Complex union queries: select 1 union select 2;

select_type column

Displays whether the corresponding row is a simple or complex select, and if it is a complex select, displays which of the three complex types it is. A SIMPLE value means that the query does not include subqueries and unions. If the query has any complexity

The outermost part is labeled PRIMARY, and the other parts are labeled as follows.

SUBQUERY: Select (not in the from clause) in a subquery included in the select list is marked as subquery.

DERIVED: select contained in the subquery of the FROM clause, MYSQL executes recursively and puts the results into a temporary table, internally called a "derived table" because the temporary table is derived from the subquery

UNION: The second and subsequent select in UNION is marked UNION

UNION RESULT: SELECT tag used to retrieve from UNION's anonymous temporary table is UNION RESULT.

table column

Show which table is being accessed. or the alias of the table (if an alias is defined in sql)

When there are subqueries in the from clause, the table column is formatted to indicate that the current query depends on the query with id=N, so the query with id=N is executed first. When there is a union, the union results.

The table column has values of 1 and 2 for the select row id participating in the union.

partitions

Partition visited

type

Represents an administrative type or access type, i.e. mysql determines how rows in a table are looked up.

From best to worst, they are: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range >

index > ALL

const, system: mysql can optimize some part of the query and convert it into a constant (see the results of show warnings). All columns and constants for primary key or unique key

Compare, so the table has at most one matching row, read 1 time, faster.

eq_ref: All parts of the primary key or unique key index are concatenated and only one eligible record is returned. This is probably the best join type out of const, simple

This type does not appear in select queries.

ref: instead of using a unique index, eq_ref uses a common index or partial prefix of a unique index, which is compared to a value and may find multiple rows that match the criteria.

ref_or_null: Similar to ref, but rows with NULL values can be searched.

index_merge: indicates that the optimization method of index merge is used.

Range: Range scanning usually occurs in operations such as in(), between ,> ,=, etc. Retrieves a given range of rows using an index.

index: Like ALL, mysql only scans the index tree, which is usually faster than ALL.

ALL: Full table scan, meaning mysql needs to find the required rows from beginning to end. Usually this requires adding indexes to optimize.

NULL: mysql can decompose query statements during optimization without accessing tables or indexes during execution. For example: Select the minimum value in the index column, you can look up the index separately to complete, do not need to access it at execution time

table

possible_keys column

Show which indexes queries can use.

When explaining, possible_keys may have a value, but key shows NULL. This situation is because there is not much data in the table, mysql thinks that the index is not helpful for this query, and selects the full table query.

If the column is NULL, there is no associated index. In this case, you can improve query performance by examining the where clause to see if you can create an appropriate index, and then using explain to see the effect.

key column

This column shows which index mysql actually uses to optimize access to the table.

If no index is used, the column is NULL. If you want to force mysql to use or ignore indexes in the possible_keys column, use force index, ignore index in queries.

key_len column

This column shows the number of bytes mysql uses in the index, and this value can be used to figure out which columns in the index are used.

Key_len is calculated as follows:

string

char(n): length of n bytes

varchar(n): 2 bytes storage string length, if utf-8, length 3n + 2

numeric types

tinyint: 1 byte

smallint: 2 bytes

int: 4 bytes

Bigint: 8 bytes

time type

Date: 3 bytes

timestamp: 4 bytes

datetime: 8 bytes

If NULL is allowed in the field, 1 byte is required to record whether NULL is allowed.

The maximum length of the index is 768 bytes. When the string is too long, mysql will do a process similar to the left prefix index, extracting the first half of the characters for index.

ref column

This column shows the columns or constants used to find values in the index of the key column record. Common ones are: const (constant), func, NULL.

rows column

This column is mysql's estimate of the number of rows to read and detect. Note that this is not the number of rows in the result set.

filtered column

Displays a pessimistic estimate of the percentage of records in the table that meet a condition (Where clause or join condition). If you multiply the rows column by this percentage, you can see that mysql estimates that it will match the query record.

The number of rows associated with the previous table in the record.

Extra column

Contains additional information that does not fit into other columns.

The most common and important values are as follows:

Using index: This occurs when the columns requested for the table are all part of the same index, and the column data returned uses only the information in the index, without accessing the row records in the table. It's high performance.

distinct: once mysql finds a row that matches the row union, it stops searching

Using where: The mysql server will filter the rows after the storage engine retrieves them. That is, read the whole row of data first, and then check according to the where condition, keep it if it meets, and discard it if it does not meet.

Using temporary: mysql needs to create a temporary table to process queries. This situation is generally to optimize, the first is to think of using the index to optimize.

Using filesort: mysql sorts results using an external index instead of reading rows from the table in index order. Mysql browses through all eligible records based on the join type, saves the sort key and row pointer, and then sorts the key and retrieves the row information in order. In this case, it is generally necessary to consider the use of indexes to optimize.

At this point, the study of "mysql obtains information about query execution plan through explain" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.