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

This article focuses on "MYSQL database optimization way to share", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "MYSQL database optimization way to share" it!

If in a system, users report that the system is too slow, then how can we optimize it?

If there are too many http requests, it can be determined that the web server is under too much pressure, and the web server can be added to do load balancing for optimization.

If we do not stutter when accessing the static interface, but stutter occurs when applying for dynamic data, it means that our database handles too many requests, and at this time there is a lot of query pressure on the database, so we need to consider the optimization of the database.

To know exactly what our database optimization is doing, we first need to understand the execution process of MYSQL.

The picture is from the network, if there is any infringement, please contact to delete.

Mysql execution process

When we request to connect to the mysql server, the mysql listens to the request. When our request arrives, the server gets the SQL statement we need to execute, and our SQL statement goes inside the MYSQL.

MYSQL will first query the cache to see whether the SQL statement has been executed. If we have executed this SQL statement, the execution result in the cache will be returned. But in MYSQL, this query cache is not enabled by default, because the query cache requires that the SQL statements and parameters are the same, so basically we can't hit all the data in the cache.

If we do not turn on the query cache, or if the query cache does not find the corresponding result, the SQL statement is passed to the parser. The parser parses the SQL statement to be executed, and then the SQL statement becomes a parsing tree, which cannot be executed immediately, and needs to be preprocessed to specify the storage location of constants, evaluate expressions and return results, and so on.

At the end of the preprocessing, the tree is the tree to be executed, and compared with the initial parsing tree, the tree is optimized.

The most critical knowledge point in MYSQL database is the query optimizer. For example, if we write a SQL statement: SELECT * FROM BOOK WHERE BOOKNAME = 'BODY' AND PRICE = 56, how will the SQL statement be executed? Will it execute BOOKNAME = 'BODY' or PRICE = 56 first? The execution order of the SQL statement is based on some information of the database on the statistical table of the data, such as how many rows of data are in the index or table. The MYSQL database will cache the data. At this time, the query optimizer will make a decision based on these data to determine which execution method is chosen during the execution of the SQL statement, and the running result may be faster. So this step is actually the most critical core of MYSQL performance, which is also the principle that we need to follow in optimization.

So what we usually say about optimizing SQL is that we want the query optimizer to choose the best execution plan according to our idea, because we know our data more clearly, and the data seen by MYSQL is only the part of the information collected by ourselves, and the correctness of this information cannot be guaranteed. MYSQL chooses what it thinks is the best implementation scheme based on the information it collects. But this plan is likely to be inconsistent with what we want it to implement.

The choice of execution plan mentioned here is actually the first execution of BOOKNAME = 'BODY' or PRICE = 56, which is passed to the query execution engine, and the engine will execute the received execution plan. At this time, there is a very important question: what is the most key reason that affects the performance of this query? In fact, we most often talk about IO, the execution time of a SQL statement is determined by the IO of this period of time. And the execution of IO is decided by the implementation plan passed by the database. If we turn on the query cache, the execution results will also be stored in the query cache before the results are returned to the client, and the next query will return the execution results directly in the query cache.

At this point, I believe that everyone on the "MYSQL database optimization way to share" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.