The query does not use the index, which will lead to full table scanning, and the execution plan should be analyzed and optimized through EXPLAIN; 2. Unreasonable database configuration, such as too small buffer pool, will affect performance, and parameters such as innodb\_buffer\_pool\_size should be adjusted; 3. Unstandard table structure design, such as wrong field type or redundancy, will lead to inefficient query, and the design should be standardized and the field type should be selected reasonably. MySQL performance problems are common in improper index usage, unreasonable configuration and poor table structure design. During the inspection, you should give priority to checking slow query logs, execution plans and index usage, and then combine configuration tuning and table structure adjustment to improve performance.

MySQL performance issues are common, especially after data and visits increase. In many cases, the problem is not because the hardware is not strong enough, but because of improper configuration, poor query writing or poor indexing. The following aspects are the most likely places to fall into pitfalls in daily operation and maintenance, and are also priority for troubleshooting performance problems.

Slow query? First see if there is any index

This is one of the most common problems. When a query does not use an index to locate data, the database has to scan the full table, and the more data the slower it is. You can use EXPLAIN command to view the execution plan and see if there are any prompts like Using filesort or Using temporary , which usually means that you need to optimize the index or query statement.

For example: You have a user table that often searches user information based on your mobile phone number, but there is no index on the mobile phone number field. You have to scan thousands of rows or even more each time, which will naturally slow down.

• Suggested practices :
  • Fields that are often used for conditional queries should be indexed as much as possible.
  • Avoid function operations on fields in the where condition, which will invalidate the index
  • Pay attention to the order when using joint indexes, and do not ignore the leftmost matching principle

Unreasonable database configuration will also slow down performance

The default configuration is suitable for getting started, but not for high concurrency scenarios. For example, the connection limit is too small, the buffer pool is too small, and the log file configuration is inappropriate, etc., which will affect the overall performance. Especially the InnoDB settings, such as innodb_buffer_pool_size , if it is still the default value, it is likely that MySQL does not fully utilize the server memory.

For example, you find that the disk IO is very high during monitoring, but the CPU and memory are still free. This may be because the buffer pool is too small, causing frequent disk readings.

• Configuration items that can be checked :
  • max_connections : Is the number of connections sufficient?
  • innodb_buffer_pool_size : Is available memory allocated reasonably?
  • query_cache_type and query_cache_size : If you write more and read less, it is better to turn off the query cache.

The table structure design is unreasonable and it is difficult to optimize later

Many people only consider whether they can store data at the beginning when building tables, and do not consider how to check efficiently. For example, an order table puts all information in a big table, and as the data grows, addition, deletion, modification and search becomes slower. Or if some field types are selected in the wrong way, such as using varchar to store numbers, it not only wastes space, but also affects index efficiency.

There is also a large number of redundant fields and no standardized data structures, which will lead to update exceptions, increase in query complexity, and eventually become a performance bottleneck.

• Design suggestions :
  • Try to standardize and split into multiple small tables with clear logic
  • Choose the appropriate field type. For example, if you can use char, don’t use varchar, if you can use enum, don’t use strings
  • Plan the partition or partitioning strategy in advance for big data scales

Basically, these common performance problems are on the direction. When encountering slow situations, start with slow query logs, combine EXPLAIN to view the execution plan, and then go back to check the index and table structure. Most of the time, you can find the reason. Configuration tuning and architecture design are advanced content, but they are also based on basic investigation.

The above is the detailed content of Identifying Typical Performance Issues in MySQL. For more information, please follow other related articles on the PHP Chinese website!