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

This article mainly introduces the use of the explain command in MySQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Let's talk about the explain command provided by the MySQL:

Syntax: explain SQL statements such as:

1explain select * from user where id=1

After execution, its output has the following fields:

Id

Select_type

Table

Partitions

Type

Possible_keys

Key

Key_len

Ref

Rows

Extra

If you want to know how to use explain naming, you must figure out these fields

1. Id

The identifier of the SELECT query, and each SELECT statement is automatically assigned a unique identifier

2. Select_type

The type, specific type and corresponding function of each select query sentence are shown in the following table:

Type name interprets SIMPLE simple SELECT without using UNION or subquery if any complex subparts are included in the PRIMARY query, the outermost select is marked as the second or subsequent SELECT statement in the PRIMARYUNIONUNION, the second or subsequent SELECT statement in the DEPENDENT UNIONUNION, depending on the result of the external query UNION RESULTUNION, the first SELECT in the first SELECTDEPENDENT subquery subquery Depending on the SELECT of the external query DERIVED derived table, the result of a subquery UNCACHEABLE SUBQUERY of the FROM clause cannot be cached, and the first row of the outer link must be reevaluated

3. Table

Which table is used to show the data in this row, but sometimes the short circuit does not show the real table name.

4. Partitions

Matching partition (this is of little use at the moment)

5. Type

Access type, indicating how MySQL finds the required rows in the table. The corresponding values and explanations are as follows:

The type name level explains that the system1 table has only one row, and the const2 table has at most one matching row, which is read at the beginning of the query. Eq_ref3 uses primary key or unique key as the condition for multi-table join, and only reads a row of ref4 from this table as the query condition. The index of matching index values in each table is read from the table. Fulltext5 full-text index retrieval ref_or_null6 is consistent with ref. But null value query support index_merge7 indicates that index merge optimization method unique_subquery8 is used to replace in subquery index_subquery9 is used to replace in subquery, but only for non-unique indexes in subquery range10 only retrieves a given range of rows, uses an index to select row index11 full table scan, but the way to scan the table is to scan the table by ALL12 full table scan in the order of the index

Type as an access type, its value represents the type used in the current query, and is an important indicator of performance. As can be seen from the table, from top to bottom, the way of scanning the table is getting wider and wider, and the performance is getting worse and worse. Therefore, for a query, it is best to keep it above the range level.

6. Possible_keys

Proactively indicate which index the query can use to find records in the table, that is, the fields that have indexes in the fields in the query will be listed, but not necessarily used by the query.

7. Key

Displays the index / key actually used in the re-query, or NULL if there is no index. However, if you want to force or ignore the index in the possible_keys column in the query, you can use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.

8. Key_len

Represents the number of bytes used in the index.

9. Ref

Indicates which columns or constants are used to find values on indexed columns.

10. Rows

Displays the estimated number of rows of records required to find a matching record by the current query.

11. Extra

Displays the solution used for the current query, which has the following situations:

The type name explains that the Using where column data is returned from the table that only uses the information in the index and does not read the actual action. Using temporary said that MySQL needs to use temporary tables to store the result set. It is common in sorting and grouping query Using filesortMySQL that the sort operation that can not be done with the index is called "file sort" Using join buffer change emphasizes that the index is not used when getting join conditions, and that join buffers are needed to store intermediate results. If this value occurs, it should be noted that indexes may need to be added to improve energy depending on the specific circumstances of the query. The value of Impossible where emphasizes that the where statement results in no rows that match the criteria. The value of Select tables optimized away means that the optimizer may return only one row from the aggregate function result by using the index alone.

After talking about the grammar, let's actually do a wave, first create a table:

-- create table CREATE TABLE test (id INT (11) NOT NULL AUTO_INCREMENT,uname VARCHAR (255), PRIMARY KEY (id))

Then index the uname field:

-- add index ALTER TABLE test ADD INDEX uname_index (uname)

Check to see if the index was added successfully:

-- check if there is an index SHOW INDEX FROM test

The output is as follows:

You can see that the index has been created successfully, and then add some data:

-- add some data INSERT INTO test VALUES (1); INSERT INTO test VALUES (2); INSERT INTO test VALUES (3); INSERT INTO test VALUES (4)

When everything is ready, use the command explain to explore whether some like statements have indexes. There are four cases of like: no%,%%, left%, right%,

1. Like field name

EXPLAIN SELECT * FROM test WHERE uname LIKE 'j'

The output is:

You can see that the value of type is: the value of range,key is uname _ index, that is, in this case, the index is used.

2. Like% Field name%

EXPLAIN SELECT * FROM test WHERE uname LIKE'% j%'

The output is:

You can see that the value of type is ALL, which means full table scan, and the value of key is NULL, which means no indexes are used.

3. Like% field name

EXPLAIN SELECT * FROM test WHERE uname LIKE'% j'

The output is:

You can see that the value of type is ALL,key and the value of NULL is NULL, and the index is also not used.

4. Like field name%

EXPLAIN SELECT * FROM test WHERE uname LIKE'j%'

The output is:

You can see that the value of type is: the value of range,key is uname _ index, that is, in this case, the index is used.

Thank you for reading this article carefully. I hope the article "what is the use of explain commands in MySQL" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.