Fault analysis | why is MySQL 5.7 slow while the same SQL is normal in MariaDB?

Time:2020-10-18

Author: Wang Shun
I am a member of the DBA team of aikesheng, who is responsible for the project of the company, dealing with database problems. I like to learn technology and delve into technical problems.
Source: original contribution
*The original content is not allowed to be used without authorization. Please contact the editor and indicate the source.


The same SQL runs normally on MariaDB. Why does it run slowly on MySQL 5.7?

1、 Problem description

The database of the customer’s production environment is migrated. The database is migrated from MariaDB 10.4 to MySQL 5.7. A business SQL runs very slowly. From the customer’s understanding, the business was running normally in MariaDB before, but the business has not changed recently. The migration to MySQL 5.7 runs very slowly, which has affected the normal use of the business.

2、 Environmental inspection

(since the production environment involves sensitive information, the following information is the test environment information)

  1. MariaDB’s implementation plan

Fault analysis | why is MySQL 5.7 slow while the same SQL is normal in MariaDB?

  1. Implementation plan of MySQL 5.7

Fault analysis | why is MySQL 5.7 slow while the same SQL is normal in MariaDB?

  1. Table structure and column information

Fault analysis | why is MySQL 5.7 slow while the same SQL is normal in MariaDB?

3、 Analysis process

  1. Through execution plan analysis

Fault analysis | why is MySQL 5.7 slow while the same SQL is normal in MariaDB?

From the warnings of MySQL 5.7 execution plan, you can clearly see the field type or collation conversion of ID field, and the index cannot be used.

  1. Looking at the table structure and column information comparison in Figure 3, the ID field sorting rules of the two tables are different, and there is an implicit conversion. As follows: sbtest1 table ID field char (32) collation utf8_ Binsbtest2 table ID field char (32) collation utf8_ general_ Ci

4、 Solution:

  1. Sort sbtest1 table ID field utf8_ Change bin to utf8_ general_ Ci

Fault analysis | why is MySQL 5.7 slow while the same SQL is normal in MariaDB?

You can see that the execution plan is normal after the collation is changed.

  1. Use convert conversion

Fault analysis | why is MySQL 5.7 slow while the same SQL is normal in MariaDB?

Convert is used to convert the ID field of sbtest1 table, and the execution plan is normal.

5、 Conclusion

MySQL 5.7 detected that the ID field of table sbtest1 is different from the ID field of table sbtest2, and there is no normal index walking, resulting in slow query. It can be solved by changing the collation or using Convert transformation. Due to the different influence of the collation conversion rules of MariaDB and MySQL on the execution plan, the different collation in MariaDB does not affect the query efficiency.

Recommended Today

Less cycle

In this section, we will learn the loop in less. Loops should be very common in programming languages. There are loops in general programming languages, such as in JavaScriptforCirculationwhileHowever, there are no such two kinds of syntax in less, but they are used to realize the loop through its own call. The use of recycling […]