Home > Database > Mysql Tutorial > body text

Overall analysis of Mysql optimization and indexing [Illustration]

黄舟
Release: 2017-03-02 16:27:43
Original
1342 people have browsed it

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 (m.sbmmt.com)!


Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!