30th-Improving performance
This chapter will review some of the key points related to MySQL performance.
30.1 Improving performance
Database administrators spend a considerable portion of their lives tuning, experimenting to improve DBMS performance. Poorly performing databases (and database queries) are often the most common culprit in diagnosing sluggish behavior and performance problems in your applications. As you can see, the following content does not completely determine the performance of MySQL. We just want to review the focus of the previous chapters and provide a starting point for the discussion and analysis of performance optimization.
- First, MySQL (as with all DBMS) has specific hardware recommendations. When learning and studying MySQL, you can use any old computer as a server. However, these hardware recommendations should be adhered to for the server used for production.
- In general, the critical production DBMS should be running on its own dedicated server.
- MySQL is preconfigured with a set of default settings, which are usually good to start with. But after a while you may need to adjust memory allocations, buffer sizes, and so on. (To view the current settings, you can use show VARIABLES, and Showstatus;.) ）
- MySQL is a multi-user multithreaded DBMS, in other words, it often performs multiple tasks at the same time. If one of these tasks is slow, all requests are slow to execute. If you experience significant performance degradation, you can use show processlist to show all active processes (along with their thread ID and execution time). You can also use the KILL command to end a particular process (use this command to log in as an administrator).
- There is always more than one way to write the same SELECT statement. The best method should be found by testing the coupling, the sub-query and so on.
- Use the explain statement to let MySQL explain how it will execute a SELECT statement.
- In general, a stored procedure executes faster than executing each of the MySQL statements in a single line.
- You should always use the correct data type.
- Never retrieve more data than you need. In other words, don't use SELECT * (unless you really need each column).
- Some operations (including insert) support an optional delayed keyword that, if used, will return control to the caller immediately and, if possible, actually perform the operation.
- When importing data, you should turn off autocommit. You may also want to delete the indexes (including the Fulltext index) and then rebuild them after the import is complete.
- Database tables must be indexed to improve the performance of data retrieval. Determining what an index is not a trivial task, you need to parse the SELECT statement used to find the duplicate where and order BY clauses. If a simple where clause takes too long to return a result, you can conclude that the column (or columns) used in it is the object that needs to be indexed.
- Do you have a series of complex or conditions in your SELECT statement? You can see great performance improvements by using multiple SELECT statements and the union statement that joins them.
- Indexes improve the performance of data retrieval, but impair the performance of data insertions, deletions, and updates. If you have tables that collect data and are not often searched, do not index them until necessary. (indexes can be added and removed as needed.) ）
- Like is very slow. In general, it is better to use fulltext rather than like.
- The database is a constantly changing entity. A set of well-optimized tables may be unrecognizable after a while. Due to the use of tables and changes in content, ideal optimization and configuration will also change.
- The most important rule is that each rule will be broken under certain conditions. 27830.2 Summary 211 Browse documents in the MySQL document there are many tips and tricks (even with user-provided comments and feedback). Be sure to check out these very valuable materials.
30.2 Summary
This chapter reviews some of the tips and instructions related to MySQL performance. Of course, this is only a small part, but since you've completed the book, you should be able to experiment and master what you think is best for you.
MySQL must know-30th chapter-Improve performance
