MySQL optimization-Optimization introduction and implementation steps of MySQL optimized for getting Started

What is optimized to optimize?
optimization has been one of the most frequently asked questions in the interview. because from the angle of optimization, the idea of optimization, we can see a person's technical accumulation. so, about system optimization, suppose this scenario, the user reflects the system too card (in fact, is high concurrency), then how can we optimize? If too many requests, to determine the Web server pressure is too large, increase the front-end Web server, do load Balancing , if the request static interface is not stuck, but the Dynamic data is a card, the MySQL processing requests too much, the application layer to increase the cache.
The database layer is actually the most vulnerable layer, generally in the application design in the upstream need to intercept the request, the database layer only assume the "ability range" Access request, so we put the queue and cache at the service layer, so that the bottom of the database peace of mind. But if the request surges, there is still a lot of query pressure to MySQL, this time will find a way to solve the MySQL bottleneck
Summing up is that the first step in system optimization is that the absolute round is not optimized for MySQL the reason we want to do the MySQL cluster, is generally done at the application level of caching, requests or too many cases to consider the problem.
MySQL's execution process
To know what we usually say about optimizing SQL in the end is what to optimize, we must understand the MySQL implementation process. This topic will be systematized from shallow to deep to explain some advanced usage of MySQL. I'm going to start with a lot of people's focus on how to use it (and how to improve it), and then talk about database and table Operations (many of the places we've learned to ignore), and then engines and more advanced queries, and so on. First simple rough on the implementation of a flowchart to feel the next

This can be roughly divided into the following 10 steps:

1. When we request the MySQL server, theMySQL front end will have a listener , after the request to the server to get the relevant SQL statements, before execution (dashed part of the execution), but also do the right to judge

2. after the permission, SQL into the MySQL inside, he will in the query cache, see if the SQL has been executed, if there is a query, then the cache results returned, inside MySQL, there is also a query cache. But this query cache is not enabled by default , this query cache is the same as our Hibernate,mybatis query cache, because the query cache requires SQL and parameters to be the same, so this hit rate is very low ( no OVA ).

3. If we do not open the query cache , or the cache does not find the corresponding results, then to the parser , the parser mainly on the SQL syntax parsing

4. After the resolution is turned into a parse tree , this parse tree actually in Hibernate is also some, we recall that in the past did hibernate project, there is not a antlr.jar. This is the tool that specializes in parsing grammar. Because in hibernate there is hql, it is through this tool to convert to SQL, our programming language has a lot of specifications, syntax, in fact, is to facilitate this parser parsing, this has learned the principle of compiling should know.

5. After the parse tree , cannot be executed immediately, this also need to preprocess The tree, that is, this tree, I did not go through any optimization of the tree, the preprocessor will this tree for some preprocessing, For example, where constants are placed, if there is something to calculate, calculate the results of the calculation and so on ...

6. after the pretreatment, we get a tree that is relatively standard, this tree is to take to do the execution of the tree, compared to the previous tree, this tree has been some optimization

7. query Optimizer, is the most critical thing in MySQL, we write any SQL, such as SELECT * from USER WHERE USERNAME = Toby and PASSWORD = 1, how will it execute? c2> does it perform username = Toby or password = 1 First? Each SQL execution order query optimizer is based on MySQL data on the statistics of some information, such as the index, such as how much data in the table, MySQL is cached, and before the real execution of SQL, he will according to his own data, a comprehensive decision, Judging this time in a variety of execution modes, the choice of which execution mode, the most likely to run the fastest. This step is the most critical core point in MySQL performance and is our optimization principle. We usually speak of the optimization of SQL, in fact, is to let the query optimizer, according to our ideas, to help us choose the best implementation, because we know more than MySQL our data. mysql looks at the data, just the information it collects, which may be inaccurate, MySQL chose a solution that it thinks is optimal, based on this information, but this scenario may not be the same as we imagined.

8. The query execution plan here, which is the execution plan in the MySQL query, such as whether to execute username = Toby or password = 1

9. This execution plan will be passed to the query execution engine , the execution engine chooses the storage engine to execute this pass-through plan, to the file in the disk to query, this time the focus is to affect the query performance is the most fundamental reason is? The mechanical motion of the hard disk, that is, we are familiar with the IO, so a query statement is fast or slow, is based on the IO to determine the time . Then what is the implementation of IO and what is determined? That's the plan of implementation. (optimization is the development of one of the fastest execution scenarios we think, the most IO-saving, and the fastest execution)

If the query cache is open, the results are returned to the client, and the query cache is put in a copy.

MySQL optimization-Optimization introduction and implementation steps of MySQL optimized for getting Started