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

This article mainly introduces the definition and interpretation of mysql implementation plan, the content of the article is carefully selected and edited by the author, mysql implementation plan definition and interpretation has a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the next topic content.

First of all, let's take a brief look at what the execution plan is.

Correlate the result of a simple order with the order item join:

Ok, the implementation schedule has a total of 12 columns, the meaning of each column, we come one by one.

1.id, which indicates the operation order of each clause. The larger the id, the higher the priority.

For each level query, the id is consistent, indicating the priority query of the operation

For sql with subqueries, you can see that subqueries have higher priority than outer queries. It is also more in line with our subjective consciousness, first check the sub-table, before we can check the main table.

Note, however, that not all sql with subquery statements are necessarily subqueries, such as the following statement:

I will not explain any more about the optimization rules of the sql optimizer. One is that the main content of this chapter is not about it, and the other is that I have not understood enough.

2.select_type, query type

1) SIMPLE, a simple query that does not contain subqueries or UNION clauses

2) PRIMARY, indicating that the query statement contains subqueries or UNION clauses, and PRIMARY represents the outer statement

3) SUBQUEY, subquery statement

4) the second and subsequent subqueries of UNION union in union are marked as union

5) the subquery contained by DERIVED in the from list is marked as derived (derivative)

6) the second or subsequent SELECT statement in DEPENDENT UNION UNION, depending on the external query

7) results of UNION RESULT UNION

I don't quite understand 6 and 7 myself, so I won't show them any more.

3.table, you need to query the table, which may be a database table or a temporary table

4.partitions, the matching partition, what happens in a split table query

5.type, access type, which is a very important indicator in sql query optimization.

Several enumerated values that often appear are as follows:

System: there is only one row of records in the table, which is equivalent to the system table

Const: match a row of data with a hit through the index

Eq_ref: unique index scan, for each index key, only one record in the table matches it, commonly used primary key or unique index scan

Ref: a non-unique index scan that returns all rows that match a single value, for columns with indexes of the =, operator

Range: retrieves only a given range of rows and uses an index to select rows, typically for between,

Index: traversing index trees only

All: full table scan

The execution efficiency decreases in turn from top to bottom, and the first five cases are ideal indexing cases. Usually optimize at least to the range level, preferably to ref.

Give some examples:

System is a special case of const that is used when there is only one row of data in the table.

I set up a unique index on the order number, and from the following figure (er, er, I don't know why the picture can't be sent, just paste the result), you can see that when the primary key or unique index is in the where condition, then the execution type is const.

Mysql > explain select * from `order` where title = 'C1086407110000019' +-+ | id | Select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- +-+ | 1 | SIMPLE | order | NULL | const | order | 1023 | const | 1 | 100.00 | NULL | + -+ 1 row in set 1 warning (0.00 sec) mysql > explain select * from `order` where id = 1 +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -+ | 1 | SIMPLE | order | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +- -+ 1 row in set 1 warning (0.00 sec)

The difference between eq_ref and eq_ref is that eq_ref is used in associative queries, as follows:

Mysql > explain select * from `order` left join order_item on `order`.id = order_item.order_id where `order`.id = 1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -- +-+ | 1 | SIMPLE | order | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | 1 | SIMPLE | order_item | NULL | ref | order number | order number | | 8 | const | 2 | 100.00 | NULL | + -+ 2 rows in set 1 warning (0.00 sec)

For non-unique indexes, such as the user id in our order, when we query, it is possible to use ref or range as follows:

Mysql > explain select * from `order` where customer_id = 55029 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | order | NULL | ref | customer_id | customer_id | 8 | const | 10 | 100.00 | NULL | +- -+-+ 1 row in set 1 warning (0.00 sec) mysql > explain select * from `order` where customer_id > 55029 and customer_id

< 55129;+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+| 1 | SIMPLE | order | NULL | range | customer_id | customer_id | 8 | NULL | 1 | 100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec) 当然如果没有索引的字段，那么执行方式只能是ALL了。 但是值得注意的是，并不是有了索引就一定不会走索引的。如果字段的差异性太小，例如性别字段，即使建了索引，那么也不会走索引的。这里我举一个例子，我拿来做测试的所有订单表中只有一个卖家，这个字段上是有索引的，但是我们来查询一下： mysql>

Explain select * from `order` where seller_id = 19 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+-+ -+ | 1 | SIMPLE | order | NULL | ALL | Merchant Seller_id | NULL | 2197 | 100.00 | Using where | +- -+-+ 1 row in set 1 warning (0.00 sec)

It is found that innodb does not actually choose to walk the index, because in this case, it is more expensive to walk the index than not to do so. With regard to cost, Innodb is based on CBO (the older way to judge is RBO, the details of which are not very clear, and you need to look up some relevant information if you want to know).

The above values are not all enumerated values, such as fulltext, index_merge, unique_subquery, and so on, but the frequency of occurrence is not as high as the above enumerated values.

System > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

After reading the above definition and interpretation of mysql implementation plan, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.

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.