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

This article will explain in detail how to use explain in MySQL, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

First of all, there is a more practical usage, the query statement we can see the execution plan, if it is a DML statement, it is important to understand whether it has changed directly or just generated the execution plan.

Explain generates an execution plan for DML

For further verification, we chose three versions, 5.5, 5.6, and 5.7, to test.

The first is to initialize the data, which is exactly the same in different versions.

Create a table test and insert two rows of data.

> create table test (id int,name varchar (20))

Query OK, 0 rows affected (0.01 sec)

> insert into test values (1 recorder AA'), 2 recorder BB')

Query OK, 2 rows affected (0.01sec)

Records: 2 Duplicates: 0 Warnings: 0

MySQL 5.5

Taking a look at the execution plan of the DML statement, it is found that it is not supported.

> explain insert into test values (3 not cc')

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into test values (3meme cc')' at line 1

Another DML, such as update, is not supported.

> explain update test set name='cc' where id=2

Here, we can't come to a final conclusion that doesn't support DML. Let's take a look at the result of 5.6 GI 5.7.

MySQL 5.6

From the point of view of the results in 5.6, it is supported, so the most concerned question is whether the data will change.

> explain insert into test values (3 not cc')

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |

+-- +

1 row in set (0.00 sec)

Check the data and make it clear at a glance.

> select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | aa |

| | 2 | bb |

+-+ +

2 rows in set (0. 00 sec) and changing to a DML, such as update, has a similar effect. The data is not modified directly.

MySQL 5.7

A few more changes have been made in 5.7. the support for DML has been improved. You can clearly see which type of DML (insert,update,delete) it is through the execution plan of the statement. Of course, the execution plan of insert has some chicken ribs, because there is really nothing to deal with.

> explain insert into test values (3 not cc')

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | INSERT | test | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |

+-- +

If you look at the execution plan of update, you can see that under the current table structure, the execution efficiency of this statement is still very limited.

> explain update test set name='cc' where id=2

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | UPDATE | test | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |

+-+ is obvious This process does not modify the data.

> select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | aa |

| | 2 | bb |

+-+ +

New features of explain for connection

If you're starting to feel something about explain, let's take a look at a new feature in 5.7. that is, the parsing of connection, the explain for connection feature.

Let's assume a scenario where the execution efficiency of a SQL statement is very poor, and we can see through show processlist, but why is the efficiency of the statement so poor? an effective way to analyze the problem is to look at the execution plan. Well, back to the core of the problem, that is, how to get the execution plan of the statement. If we follow the way we deal with the existing problems, it is to check the slow log and then parse it. Or use third-party tools to get some reports that work better.

One of the more tangled situations is that you see that the execution efficiency of the statement is very poor, but such a process will take at least a few minutes, and when you are about to parse it, you will find that the statement has been returned. So grabbing data in real time is a sharp tool to improve the happiness of DBA. Then we simulate a poor performance SQL, such as the following anti-join statement, the execution efficiency is very poor. Let's try to grab the execution plan.

> select account

From t_fund_info

Where money > = 300

And account not in (select distinct (account))

From t_user_login_record

Where add_time > = '2016-06-01')

When we grab the session through mysqladmin pro, which is similar to the result of show processlist, we can obviously see that the first column is connection id 6346185. Let's parse this connection.

# mysqladmin pro | grep t_fund_info

| | 6346185 | root | localhost | test | Query | 8 | Sending data | select account from t_fund_info where money > = 300 and account not in (select distinct (account) | 0 | 0 |

> explain for connection 6346185

+-

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |

| | 1 | PRIMARY | t_fund_info | NULL | ALL | NULL | NULL | NULL | NULL | 1826980 | 100.00 |

| | 2 | SUBQUERY | t_user_login_record | NULL | ALL | NULL | NULL | NULL | NULL | 1740589 | 33.33 |

+-

On how to use explain in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.