"mysql Optimization topic" This is probably one of the best MySQL Optimization starter articles (1)

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, assuming that this scenario, the user reflects the system is too card (in fact, high concurrency), then how can we optimize?

• If too many requests, 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

So, knowing what we usually say about optimizing SQL is exactly what is being optimized, we have to understand the MySQL execution 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, the MySQL 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, the default is not open, this query cache, The query cache is the same as our hibernate,mybatis, because the query cache requires both SQL and parameters to be the same, so this hit rate is very low (no OVA meant).

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, can not be executed immediately, which also need to pre-processing the tree, that is, this tree, I did not go through any optimization of the tree, the preprocessor will be this tree for some preprocessing, such as the constant place, if there is a calculation of things, 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? It is performed first USERNAME = Toby or password = 1? The execution order query optimizer for each SQL is based on some information from MySQL on the data tables, such as the index, such as how much data the table has, MySQL is cached, and before actually executing the SQL, he will, according to his own data, Make a comprehensive decision, judging this time in a variety of execution methods, the choice of which execution mode, the most likely to run the fastest. This step is the most critical core point of MySQL performance, and also our optimization principle. What we usually talk about is optimized SQL, in fact, is to let the query optimizer, According to our ideas, help us choose the best implementation, because we know more about our data than MySQL. mysql look at the data, just the information they collected, this information may be inaccurate, MySQL based on this information to choose a solution that it thinks best, But this plan 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 is passed to the query execution engine, the execution engine chooses the storage engine to execute this pass-through plan, goes to the file in the disk to query, this time the emphasis comes, the impact of this query performance is the most fundamental reason is what? is the mechanical movement of the hard disk, that is, our usual familiar Io, So whether a query statement is fast or slow is based on the IO of this time. How does io be determined? That's the plan of implementation.

10. If the query cache is open, the results are returned to the client, and the query cache is also placed in one copy.

"mysql Optimization topic" This is probably one of the best MySQL Optimization starter articles (1)