17 points for MySQL performance optimization

Database administrators spend a considerable part of their lives adjusting and experimenting to improve DBMS performance. When it comes to diagnosing application lags and performance issues, a poorly performing database (and database query) is often the most common culprit.

It can be seen that the following content does not completely determine the performance of MySQL. We just want to review the key points of the previous chapters and provide a starting point for discussion and analysis of performance optimization.

Here are 17 key points for specific MySQL performance optimization:

1. First, MySQL (like all DBMS) has specific hardware recommendations. When learning and researching MySQL, using any old computer as a server will work. But for servers used in production, you should stick to these hardware recommendations.

2. Generally speaking, key production DBMS should run on its own dedicated server.

3.MySQL is pre-configured with a series of default settings, and it is usually good to start with these settings. But after a while you may need to adjust memory allocation, buffer size, etc. (To view the current settings, use SHOW VARIABLES; and SHOW STATUS;.)

4.MySQL is a multi-user and multi-threaded DBMS. In other words, it often performs multiple tasks at the same time. If one of these tasks is slow, all requests will be slow. If you experience significant poor performance, use SHOW PROCESSLIST to display all active processes (along with their thread IDs and execution times). You can also use the KILL command to terminate a specific process (you need to be logged in as an administrator to use this command).

5. There is always more than one way to write the same SELECT statement. You should experiment with joins, unions, subqueries, etc. to find the best approach.

6. Use the EXPLAIN statement to let MySQL explain how it will execute a SELECT statement.

7. Generally speaking, stored procedures execute faster than executing each MySQL statement one by one.

8. The correct data type should always be used.

9. Never retrieve more data than required. In other words, don't use SELECT * (unless you really need every column).

10. Some operations (including INSERT ) support an optional DELAYED keyword, which, if used, will return control immediately to the calling program and actually perform the operation as soon as possible.

11. When importing data, automatic submission should be turned off. You may also want to drop indexes (including FULLTEXT indexes) and then rebuild them after the import is complete.

12. Database tables must be indexed to improve the performance of data retrieval. Determining what to index is not a trivial task and requires analyzing the SELECT statements used to find duplicate WHERE and ORDER BY clauses. If a simple WHERE clause takes too long to return results, you can conclude that the column (or columns) used in it is the object that needs to be indexed.

13. Do you have a series of complex OR conditions in your SELECT statement? By using multiple SELECT statements and a UNION statement connecting them, you can see dramatic performance improvements.

14. Indexes improve the performance of data retrieval, but harm the performance of data insertion, deletion and update. If you have tables that collect data and are not searched frequently, don't index them until necessary. (Indexes can be added and removed as needed.)

15.LIKE is slow. In general, it's better to use FULLTEXT instead of LIKE.

16. Databases are ever-changing entities. A well-optimized set of tables may become unrecognizable after a while. As table usage and contents change, the ideal optimization and configuration will change.

17. The most important rule is that every rule will be broken under certain conditions.

【Related recommendations】

1. Mysql free video tutorial

2. Mysql diagnosis startup problems and detailed explanation of viewing log files

3. Detailed explanation of backup and maintenance of MySQL database

4. Detailed explanation of MySQL access permission setting example

5. MySQL creates user accounts and deletes user accounts

The above is the detailed content of 17 points for MySQL performance optimization. For more information, please follow other related articles on the PHP Chinese website!