SQL Compaction Basics (vi): Mqsql Explain

relational database, the Internet-related industry is undoubtedly the most use of MySQL, although we C # Developer a lot of SQL Server, but learning some of the knowledge of MySQL is also necessary, he stone of the mountain.

Let's start with an instance of explain, and I'll explain the meaning of each column in the table by my own understanding . (For example use only, the actual project does not recommend writing SQL as such).

Id

This field is used to determine the precedence of a query statement execution.

There are three cases of this value:

The ID value is the same: this situation means that the query statement executes from top to bottom in the explain result

The ID value is not the same: in this case, the ID value is incremented, the higher the ID value, the higher the priority of the corresponding SQL statement in the explain result, the more executed first. This usually appears in the subquery

The ID value has the same and different values: In this case, the higher theID value, the higher the priority, the more executed , then, for the same result as the ID value, MySQL executes the top-down from the ID in the explain result .

Select_type

Represents the type of query, first look at the table

PRIMARY: If a query contains several subqueries or nested queries, the outermost query will be marked as PRIMARY.

Subquery: include subqueries in select or where statements

DERIVED: Subqueries contained in the From list will be marked as DERIVED (derived), and MySQL will execute the subqueries recursively, placing the results in a temporary table.

Table

Which table, table name, or alias the corresponding row is accessing may be a few

1 Actual table names

2 Aliases for tables

For example, select * from Customer as C

3 Derived sub-query

<derivedxis a number, and my understanding is the result of the first few steps of execution.

4 null result of direct settlement, not walking table

The Association optimizer selects the Association order for the query, and the left depth first

When there is a subquery in the From, the table name is in the form of Derivedn, and N points to the subquery, which is the next column in the explain result

When there is a union result, the table name is the form of Union, and the query ID that participates in the Union

Note: MySQL treats these tables like normal tables, but these "temporary tables" are not indexed.

Type

Type shows the type of access, which is an important indicator, and the resulting values from good to bad are:

System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All, in general, ensure that queries reach at least the range level and are best to reach ref.

Ref uses a non-unique index scan or a unique index prefix scan (sometimes requiring a long index of character columns, which can make the index a lot more difficult.) You can usually index the beginning of some characters, which can greatly save the index space, thereby improving the efficiency of the index, return a single record, often appear in the associated query

Eq_ref is similar to ref, except that using a unique index, the associated query using the primary key

Possible_keys

Indicates which index MySQL can use to find records in the table, and if there are indexes on the fields involved in the query, the index will be listed but not necessarily used by the query.

The column is completely independent of the order of the tables shown in the explain output. This means that some keys in Possible_keys are not actually used in the generated table order.

If the column is null, there is no index associated with it. In this case, you can improve your query performance by examining the WHERE clause to see if it references some columns or columns that fit the index. If so, create an appropriate index and check the query again with explain

Key

Displays the keys (indexes) that MySQL actually decides to use. If no index is selected, the key is null. To force MySQL to use or ignore the indexes in the Possible_keys column, use the forces Index, using index, or ignore index in the query.

Key_len

The Key_len column shows the key lengths that MySQL decides to use. If the key is null, the length is null. The length of the index to use. Without loss of accuracy, the shorter the better.

Represents the number of bytes used by the query optimizer for indexing. This field evaluates whether the combined index is fully used, or only the leftmost field is used.

The Key_len calculation rules are as follows:

String

CHAR (n): N byte length

VARCHAR (n): If it is UTF8 encoded, it is 3 N + 2 bytes; If it is UTF8MB4 encoded, it is 4 n + 2 bytes.

Numeric type:

Tinyint:1 bytes

Smallint:2 bytes

Mediumint:3 bytes

Int:4 bytes

Bigint:8 bytes

Time Type

Date:3 bytes

Timestamp:4 bytes

Datetime:8 bytes

Field Properties: The NULL attribute occupies one byte. If a field is not NULL, this property is not.

Let's raise two simple chestnuts:

Mysql>EXPLAINSELECT *  fromOrder_infoWHERE user_id < 3  andProduct_Name= 'P1'  andProductor= 'whh'\g*************************** 1. Row***************************ID:1Select_type:simpleTable: Order_info partitions:NULLType:rangepossible_keys:user_product_detail_indexKey: User_product_detail_index Key_len:9Ref:NULLrows:5Filtered:11.11extra:usingwhere; UsingIndex1Rowinch Set,1Warning (0.00Sec

The example above is to query the specified content from table Order_info, and as we can see from the table's Build statement, table Order_info has a federated index:

KEY ' User_product_detail_index ' (' user_id ', ' product_name ', ' productor ')

However, this query statement WHERE user_id < 3 and product_name = ' P1 ' and productor = ' whh ' because the range query is first performed user_id, and according to the leftmost prefix matching principle, when a range query is encountered , the index's match is stopped, so in fact the fields we use for the index are only user_id, so in EXPLAIN, the key_len shown is 9. Because the user_id field is BIGINT, occupies 8 bytes, and the NULL attribute occupies one byte, the total is 9 bytes. If we change the user_id field to BIGINT () not NULL DEFAULT ' 0 ', then the key_length should be 8.

Above because of the leftmost prefix matching principle, our query only uses the USER_ID field of the federated index, so the efficiency is not high.

Let's take a look at the next example:

Mysql>EXPLAINSELECT *  fromOrder_infoWHERE user_id = 1  andProduct_Name= 'P1'\g;*************************** 1. Row***************************ID:1Select_type:simpleTable: Order_info partitions:NULLType:refpossible_keys:user_product_detail_indexKey: User_product_detail_index Key_len:161ref:const,const rows:2Filtered:100.00extra:usingIndex1Rowinch Set,1Warning (0.00Sec

In this query, we did not use the range query, the value of Key_len is 161. Why is it? Because of our query criteria where user_id = 1 and product_name = ' P1 ', only the first two fields in the federated index are used, so Keylen (user_id) + Keylen (product_name) = 9 + 50 * 3 + 2 = 161

Rows

Rows is also an important field. The MySQL query optimizer estimates the number of rows of data that SQL needs to scan to find the result set based on statistics.

This value is very intuitive to show the efficiency of SQL, in principle, the less the better.

Extra

Many of the additional information in the EXplain is displayed in the Extra field, which is common in the following categories:

Using join buffer: The change emphasizes that the index is not used when getting the join condition, and the connection buffer is required to store the intermediate result. If this value is present, it should be noted that depending on the specifics of the query, you may need to add an index to improve the performance.

Summarize:

explain won't tell you about triggers, stored procedures, or user-defined functions that affect queries

explain does not consider the various caches

Explain cannot display the optimizations that MySQL made when executing queries

• Some of the statistics are estimates, not exact values

• Expalin can only interpret the select operation, and other operations are rewritten as Select to view the execution plan .

SQL Compaction Basics (vi): Mqsql Explain