MySQL Note: performance optimization

First, query performance

SHOW STATUS like 'Connections'; #连接次数SHOW STATUS like 'Uptime'; #上线时间SHOW STATUS like 'slow_queries'; #慢查询次数SHOW STATUS like 'Com_select'; #查询次数SHOW STATUS like 'Com_insert'; #插入次数SHOW STATUS like 'com_update'; #更新次数SHOW STATUS like 'Com_delete'; #删除次数

View Code

Second, optimize the query

Parses a query statement example.

* from        MySQL.  User fromMySQL.  User;   

View Code

Third, optimize the database structure

Improve query speed.

• Decompose tables with many fields into multiple tables to avoid using low-frequency fields to reduce query speed.
• Increase the speed of the table query by increasing the intermediate table.
• Add redundant fields to minimize multi-table queries.

Increase the insertion speed.

• Disable indexing to prevent the index from being sorted when it is inserted to reduce the insertion speed.
• Disable uniqueness checks to avoid the overhead of school checks.
• Simplifies INSERT statements, reducing the overhead associated with attaching a database when multiple INSERT statements are executed.

ALTER TABLE Student  DISABLE KEYS; #禁用索引 ALTERTABLE  student  ENABLE KEYS; #开启索引 SET=0  ; #禁用唯一性检查 SET=1; #开启唯一性检查

View Code

Analyze, examine, and optimize tables.

TABLE MySQL. User ; CHECK TABLE MySQL. User  TABLE MySQL. user;

View Code

Iv. Optimizing Servers

You can improve server performance by modifying some database parameters. Some important parameters are as follows

Key_buffer_size	Index Cache Size
Table_cache	Number of open tables at the same time
Query_cache_size	Query Cache Size
Query_cache_type	Open state of query cache
Max_connections	Maximum number of connections
Sort_buffer_size	Sort Cache Size
Read_buffer_size	Thread Cache Size
Innodb_buffer_pool_size	InnoDB cache size for tables and indexes

MySQL Note: performance optimization