Summary of mysql performance optimization methods

MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently a product of Oracle. MySQL is one of the most popular relational database management systems. In terms of WEB applications, MySQL is the best RDBMS (Relational Database Management System) application software. (Related recommendation: "mysql tutorial")

MySQL is a relational database management system. The relational database stores data in different tables. Rather than placing all data in one large warehouse, this increases speed and flexibility.

In fact, in terms of web development programs, mysql still has many areas that can be optimized.

The following will introduce to you how to optimize mysql performance.

1: Choose the appropriate storage engine: InnoDB

2: Ensure that data is read from memory. Talk about storing data in memory

It is recommended to store all data in innodb_buffer_pool_size, that is, plan the capacity of innodb_buffer_pool_size according to the storage amount. This way you can read the data entirely from memory. Minimize disk operations.

Three: Regularly optimize and rebuild the database

mysqlcheck -o –all-databases will make ibdata1 continue to grow. The real optimization only involves rebuilding the data table structure.

Four: Reduce disk write operations

Five: Improve disk read and write speed

Six: Fully Using indexes

Seven: Analyze query logs and slow query logs

Eight: Radical method. Using memory disk

9: Using MYSQL in NOSQL way

10: Others

a. Single entry Add LIMIT 1 at the end of the query to stop the full table scan.

b. Separate non-"index" data, such as storing large articles separately, without affecting other automatic queries.

c. Do not use MYSQL built-in functions. Because the built-in function does not build the query cache.

d.PHP’s connection establishment speed is very fast, and no connection pool is needed. Otherwise, the number of connections may be exceeded. Of course, PHP programs without a connection pool may also

e. use @ignore_user_abort(TRUE);

f. use IP instead of domain name as the database path. Avoiding DNS resolution problems

This article is a summary of mysql performance optimization methods. I hope it will be helpful to friends in need!

The above is the detailed content of Summary of mysql performance optimization methods. For more information, please follow other related articles on the PHP Chinese website!