Mysql Query optimization--to adjust the internal variables of the detailed _mysql

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

　　Change index buffer Length (key_buffer)

In general, the variable controls the length of the buffer used when processing an index table (read/write operation). The MySQL manual indicates that the variable can be increased to ensure optimal performance of the index table, and it is recommended to use the size of system memory 25% as the value of the variable. This is one of the most important configuration variables for MySQL, and if you are interested in optimizing and improving system performance, you can start by changing the value of the key_buffer_size variable.

　　Change the length of the table (read_buffer_size)

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

　　Sets the maximum number of open tables (Table_cache)

This variable controls the maximum number of tables that MySQL opens at any time, thereby controlling the server's ability to respond to input requests. It is closely related to the max_connections variable, and increasing the Table_cache value allows MySQL to open more tables, such as increasing the max_connections value to increase the number of connections. When you receive a large number of requests for different databases and tables, consider changing the size of this value.

　　Set a time limit for slow-length queries (long_query_time)

MySQL has a "slow query log" that automatically records all of the queries that have not yet ended within a specific time span. This log is useful for tracking inefficient or misbehaving queries and looking for optimized objects. Long_query_time variable controls This maximum time limit, in seconds.

The above discussion and the use of three tools for analyzing and optimizing SQL queries to improve your application performance. Use them to optimize it happily!