MySQL Query Optimization-detailed explanation of adjusting internal variables

MySQL is so open that you can easily adjust its default settings to achieve better performance and stability. Key variables to be optimized are as follows:

　Change the index buffer length (key_buffer)

Generally, this variable controls the buffer length to be used when processing index tables (read/write operations. MySQL user manual points out that this variable can be continuously increased to ensure the optimal performance of the index table, and it is recommended to use 25% of the size of the system memory as the value of this variable. This is one of the most important configuration variables of MySQL. If you are interested in Optimizing and improving the system, you can change the value of the key_buffer_size variable.

　Change the table length (read_buffer_size)

When a query continuously scans a table, MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of the buffer. If you think continuous scanning is too slow, you can increase the performance by increasing the variable value and memory buffer size.

　Set the maximum number of opened tables (table_cache)

This variable controls the maximum number of tables opened by MySQL at any time, thereby controlling the server's ability to respond to input requests. It is closely related to the max_connections variable. Increasing the table_cache value enables MySQL to open more tables, just like increasing the max_connections value to increase the number of connections. When receiving a large number of requests from different databases and tables, consider changing the size of this value.

　Set a time limit for slow query (long_query_time)

MySQL has a "Slow query log", which automatically records all queries that have not been completed within a specific time range. This log is useful for tracking inefficient or misperforming queries and searching for optimization objects. The long_query_time variable controls the maximum time limit, in seconds.

The above discussion provides the usage methods of the three tools used to analyze and optimize SQL queries to improve the performance of your applications. Use them to optimize them happily!