MySQL sort buffer and join buffer learning notes

Related Parameters View commands

SELECT @ @join_buffer_size;

SELECT @ @sort_buffer_size;

==========================================================================================

Join_buffer_size

The buffer used when our join is All,index,rang or index_merge.

This join is actually called a full join.

Virtually every table participating in a join requires a join buffer.

So when the join appears, it's at least 2.

This is only a maximum of 4G before the mysql5.1.23 version, but starting with the 5.1.23 version, the 64 on the platform beyond Windows can exceed the 4GB limit.

The system defaults to 128KB.

==========================================================================================

Sort_buffer_size

Sort buffer is the buffer used when sorting data in the system.

The sort buffer is also for a single thread, so multiple sort buffer appears in the system when multiple threads are sorted at the same time.

We can generally increase the processing performance of order by or group by by increasing the size of the sort buffer.

The system defaults to 2MB, the maximum limit is the same as the join buffer.

==========================================================================================

Optimization recommendations

If the join statement is rarely present in the application, you may not care too much about the setting size of the Join_buffer_size parameter.

If the join statement is not very small, personal suggestions can be appropriately increased join_buffer_size to 1MB or so, if sufficient memory can be set to 2MB.

For Sort_buffer_size, a general setting of 2-4MB can meet the needs of most applications.

Of course, if the order in the application is relatively large. You can also continue to increase the settings for the sort buffer size when there is sufficient memory but not a particularly large amount of concurrency.

The last thing to note is that each thread creates its own independent buffer rather than the entire system share, so the set value is too low for the system to be in memory.