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

The query skills in MYSQL and how to carry out MYSQL 8 parallel query. In view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Recently, the company's system began to split bit by bit, moving from ORACLE to MYSQL, some programmers still have not changed their ideas in using MYSQL, directly moving the query statements in ORALCE directly to MYSQL. Using MYSQL two important points, 1 logic up, the database is no longer the first choice to assume your logic, the proportion of the program will become more important 2 database containerization, the database will become less important, but only the place where the data is carried, or even advanced design, the database will become dispensable, which is of course linked to the business, not OK all over the world.

This directly raises the question of whether MYSQL's query skills are still important. Of course, if you still want to use MYSQL to extract and query the database, you must pay attention to the query skills of MYSQL.

The following figure is based on the version of mysql 8.015 as the basis for statement execution

In fact, the query result of the two statements is the same, only the writing method is not the same, the execution plan given is different.

With the latest version of MYSQL, from the above example, direct queries should be encouraged and subqueries should be avoided. Of course, if the sub-query can greatly reduce the amount of data involved in the calculation, it can still be compared to continue to use.

Let's see how much query performance can be improved by index merge in MYSQL.

1 Let's shut down index_merge first

Obviously, the index is in vain.

2 Let's open index_merge

Obviously, when using or in the query, index merge is very helpful to the data query.

In practice, (the fetch of 440 rows of data on SSD hard disk is not enabled for 0.195 seconds is equivalent to a full table scan, which is turned on for 0.001 seconds)

Mysql 8 is on by default.

3 the influence of time range on the selection index

The following two statements will make very different choices for the index. We will build four indexes.

Select * from employees where first_name = 'Georgi' or last_name =' Preusig' and birth_date > '1950-01-01' and birth_date

'1950-01-01'

As can be seen from the above picture, due to the time range, I finally went to different indexes. In fact, I did some other tests later, such as expanding the time range, and found that the current MYSQL 8.015 wisely walked the index that should be taken. The query optimizer in this version of MYSQL8.0 has made a lot of progress compared to MYSQL 5.7s.

Finally, let's take a look at the parallel query of MYSQL 8.0. in fact, parallel query can be found in PG, SQL SERVER and ORACLE, but the form is different. MYSQL 8 did not have the concept of parallel query before. MYSQL 8 introduced parallel query. Let's see how parallel query is helpful to query.

First of all, we reduce the parallelism to 1, and the default is 4, and the time we get is 0.109 seconds, and then we adjust the parallelism to 6 in the second query, and the time we get is 0.062 seconds.

Thus it can be seen that parallelism is helpful for queries, especially for query methods such as count (*), which are often criticized.

If you often look at MYSQL and POSTGRESQL, you will feel that open source databases are becoming more and more powerful, and there is not much time left for databases like ORACLE and SQL SERVER.

This is the answer to the query skills in MYSQL and how to carry out MYSQL 8 parallel query. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.