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

The following mainly brings you a detailed interpretation of mysql implementation plan, hoping that the detailed interpretation of mysql implementation plan can bring you practical use, which is also the main purpose of editing this article. Okay, no more nonsense, let's go straight to the following.

Continue with the execution plan section that was not completed last time

6.Possible_keys may use the index, this understanding, indicating the index that may be used when querying. It might be used, but it doesn't have to be. If it is empty, there must be no associated index. However, if it has value, it may not be able to be used.

7. The actual index used by key.

6 and 7, see the following example:

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 | NULL | NULL | 2197 | 100.00 | Using where |+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)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)

For the first sql, the result of possible_keys here is that it is possible to take the index on seller_id, but the actual key is NULL. As mentioned before, because there is only one seller, although there is an index, Innodb chose to scan the whole table, which is the result of selection based on CBO. Let's delete the index on seller_id and see what happens.

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 |merchants | NULL | NULL | NULL | 2197 | 100.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

It can be seen that what has already gone is a full sweep of the table.

The result of the second sql possible_keys here is seller_id, which is actually the seller_id of the walk, indicating that the index is effective.

8.key_len Length used to index field

Note: key_len is obtained by definition, indicating the maximum possible length of the index field, not the actual length.

Once stepped on a pit, because when the index was created, the index length was not actively defined, and the result was that the field index length of 20 was only 4. Then the index does not take effect causing the database avalanche. Therefore, it is best to develop a habit of adding length when defining an index.

9.ref Shows which field or constant is used with key

If is constant equivalent query used, const will be displayed here

If it is a join query, the execution plan of the driven table is displayed here with the associated fields of the driving table

If the condition uses an expression or function, or if the condition column has an internal implicit conversion, it may appear as func here.

If no index is used, NULL will be displayed here.

10.row scan lines

This number indicates how much data mysql has to traverse to find. It is based on statistics and may not be very accurate.

11.Filtered Percentage Filtered

An estimate (here a pessimistic estimate, the maximum) of the percentage of records for the condition, that is, the percentage of rows mysql will filter.

12. Extra information

Using index: indicates that the index is overwritten, and only the index value is queried to meet the requirements of sql.

mysql> explain select customer_id 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 | Using index |+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

For the above detailed interpretation of mysql implementation plan, do you think it is very helpful? If you need to know more, please continue to pay attention to our industry information, I believe you will like these contents.

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.