MySQL Learning four SQL optimization

mysql logical architecture:

The first layer: the client layer, connection processing, authorization authentication, security and other functions.

Second layer: core layer, query parsing, analysis, optimization, caching, built-in functions (time, math, encryption), stored procedures, triggers, views

Tier Three: the storage engine. Responsible for the storage and extraction of data in MySQL.

　　

MySQL query process

　　Client/server-side communication protocol:

　　

　　

　　
　　

　　

　　

1 Learning to use explain

？？？

2 Creating the correct index

The index of the database is the same as the index of the book, where their location information is saved and contains the primary information of the database. You can use explain to find

The missing index.

3 deny default settings : There are three settings for MySQL performance optimizations:

Innodb_buffer_pool_size: Data and indexes are used as buffer pools for caching. When the database server has a large amount of system memory, it can be used.

This setting should not be too large, and do not cause frequent exchange.

Innodb_log_file_size: Single InnoDB log file size.

Max_connections: Maximum number of connections

4 loading the database into memory

　　Put frequently accessed data into memory (e.g. 30% of data into memory)

5 SSD Storage

6 horizontal expansion??

　Vertical expansion

Horizontal Scaling

7 Pursuit of Visualization

　　Database is affected by traffic load, application and other errors, in order to quickly and effectively solve the problem, need to have a monitoring mechanism.

Common monitoring tools: MySQL Enterprise monitor/Monyog/percona

　　

　　

　　

　　

　　

　　

　　

　　

MySQL Learning four SQL optimization