Overall analysis of Mysql optimization and indexing [Illustration]
- 黄舟Original
- 2017-03-02 16:27:431396browse
1. Why do you never feel confident when people ask you about MySQL optimization knowledge
1. Because you just answer some big questions The tuning principles of optimization,
a. For example: "Establish a reasonable index" (What kind of index is reasonable?)
b. "Separation of tables and databases" (What strategy should be used to divide tables and databases?)
c. "Master-slave separation" (What middleware should be used?)
2. There is no analysis from a detailed to quantitative level.
a.What if qps improves %N?
b. Has the file sorting been reduced?
c. How much has the number of scanned lines of statements been reduced?
2. How to do it?
When mysql is not efficient, what we ## must do first is not to optimize, but Yes---Diagnostic analysis to find out where the bottleneck is. CPU, memory, IO? Peak value, single statement
1. Analyze common commands
a).show processlist Enable slow query to obtain the problematic SQL
b).explain analysis statement
2. The statement waiting time is long:
c). Tuning server parameters (such as buffer, number of threads, etc.)
3. Statement execution time is long:
d). There are many table associations (table design is defective)
e). The index is not optimized
f). Statements are not optimized (where conditional calculation statements, etc.)
.
Wait
This is just a simple description.
So, this series of blogs will analyze the aspects related to MySQL, from optimization to index creation, consolidate the study and make a summary, and share my experience with everyone.
I hope this embarrassing situation will never happen again in the future. I think I understand, but in fact I still don’t.
In the form of pictures, this section gives an overall overview of the context that needs to be mastered, which is also the route of your own learning. The overall picture is as follows:
Figure 1. MySQL server tuning ideas
Figure 2. SQL optimization
Figure 3. Index learning
Above This is the content of Mysql optimization and indexing general analysis [illustration]. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!