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

Recently, a problem was encountered in MySQL maintenance. The whole SQL statement was executed in less than 1 second in the test environment of mysql 5.6, but in the

It takes nearly 5 minutes to execute in the production environment of mysql 5.7. the same database in mysql 5.7 has the same amount of data. Execute after updating the statistics of the table.

The speed is still two minutes. The handling of this problem has nothing to do with the sql statement itself, but only related to the optimizer parameters of the mysql database itself, as follows

In the process of problem analysis and troubleshooting, the sql statement in the problem analysis does not need to be displayed. It can be made clear that sql is a multi-table join connection and the business is not allowed to change it.

1. First, look at the execution plan of mysql's sql statement in both the test environment and the production environment

In the test environment, the execution plan only needs to show part of the problem.

-- in the production environment, the execution plan only needs to show the partial explanation.

2. From the comparison between the test and the sql statement in the production environment, it is obvious that the execution plan of sql is inconsistent, and it is found in the follow-up investigation.

A. The statistics of the tables and indexes involved in sql in the test environment are up-to-date for the day, while the statistics of related tables and indexes in the production environment are old.

B. Test environment mysql major version is 5.6and production environment mysql major version is 5.7,

3. Problem handling

A. Due to the inconsistent execution plan of sql and the old statistical information of production environment, the statistical information of production environment table is collected again, and the execution speed of sql after collection is not

Improve, compared to the test is still very slow.

B. Follow the test environment execution plan derived and. This feature is related to the parameter derived_merge of mysql. Check the setting of this parameter.

-- Test environment

MySQL [(none)] > show global variables like'% switch%'

+- - -+

| | Variable_name | Value |

+- - -+

| | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,. | | |

+- - -+

1 row in set (0.00 sec)

MySQL [(none)] >

In the production environment, in order to facilitate the explanation, the redundant parameter display is omitted.

Mysql > show variables like'% switch%'

+- - - -- +

| | Variable_name | Value |

+- - - -- +

| | optimizer_switch |. Derived_merge=on |

+- - - -- +

1 row in set (0.00 sec)

Mysql >

C. According to the official statement, the parameter derived_merge is introduced in the mysql5.7 version, which is used to merge join (select) table joins. In this case, sql has

A large number of join (select) causes sql to perform result set merging and sql execution speed is extremely slow.

D. Temporarily cancel the parameter through the session level, observe the sql execution plan, and find that the sql execution plan is normal, and the execution speed of sql is less than 1 second similar to that of the test environment.

-- cancel the optimizer derived_merge parameter

Mysql > set optimizer_switch= "derived_merge=off"

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 8980

Current database: mysql

Query OK, 0 rows affected (0.02 sec)

Mysql >

-- observe the problem sql execution plan, which is the same as the test environment.

-- the execution speed of the problem sql has changed from nearly 5 minutes to about 1 second now.

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.