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)
- MariaDB’s implementation plan
- Implementation plan of MySQL 5.7
- Table structure and column information
3、 Analysis process
- Through execution plan analysis
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.
- 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
- Sort sbtest1 table ID field utf8_ Change bin to utf8_ general_ Ci
You can see that the execution plan is normal after the collation is changed.
- Use convert conversion
Convert is used to convert the ID field of sbtest1 table, and the execution plan is normal.
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.