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

This article mainly shows you the "MYSQL 8 and MYSQL 5.7 in complex queries what are the differences", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn what are the differences between MYSQL 8 and MYSQL 5.7 in complex queries "this article.

MySQL 8 eventually has to replace MYSQL5.7 in a large area, and the previous text may give people the impression that MYSQL 8 is not as good as MYSQL5.7. In fact, anything new must have a problem, just solve it. In complex queries, MYSQL5.7 is indeed different from MYSQL 8. What help developers write SQL can take a look at some examples below.

Here is the execution plan for MYSQL 8 and MYSQL 5.7 in a slightly more complex query

Compared with the above diagram, the same statement, the same database, the same table, the same number of data rows and contents, mysql 8 removed using firesort due to various optimizations, and saved nearly 20 seconds because of this item.

There are also related examples below, or the version of MYSQL is inconsistent, including all the other hardware is the same, mysql 8 is 4 times faster than mysql 5.7. the difference between 34 seconds and 128seconds, the difference is still the filesort.

Mysql 8.018

Mysql 5.7.23

Through these two examples, we can see that in the use of statements like GROUP BY, without special optimization, MYSQL 8 is no longer using FILESORT sorting, the speed has been greatly improved, which shows that without optimization, MYSQL 8 is beneficial for queries such as sorting and GROUP BY, and as more data is extracted, the faster it is, which is good news for DEVELOPER.

MYSQL 8

MySQL 5.7

In the above test, if there is no Join salaries, the actual situation is that MYSQL 5 and 7 will be slightly better. MYSQL 8 will Materialized the sub-queries in not exists not in first. Relatively speaking, if there is less data to be excluded in not exists not in, the more accurate the conditions are, the better, so the antijoin function of MYSQL 8 will help query to exclude data more effectively. Here, in all the same cases, MYSQL 8 is twice as fast as MYSQL 5.7s.

Of course, there are some unsatisfactory ones. The time of the following two queries is basically the same, and it may take more time to reformat the statement. Mysql 8 is also 0.2 seconds slower.

MYSQL 8

Overall, mysql's new filesort-free feature in hash join is helpful for most query statements, but there are actually some simple statements in the test that MYSQL 8 can't take advantage of, or may be "blink of an eye" slower than MYSQL 5.7s.

These are all the contents of the article "what are the differences between MYSQL 8 and MYSQL 5.7in complex queries?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.