Mysql sort_buffer_size参数

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sort_buffer_size
按照官网的解释:
Each session that must perform a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization. At minimum the sort_buffer_size value must be large enough to accommodate fifteen tuples in the sort buffer. Also, increasing the value of max_sort_length may require increasing the value of sort_buffer_size. For more information, see Section 8.2.1.14, “ORDER BY Optimization”https://www.cndba.cn/hbhe0316/article/22612https://www.cndba.cn/hbhe0316/article/22612

If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing.https://www.cndba.cn/hbhe0316/article/22612

sort_buffer_size 是一个connection级参数，在每个connection需要buffer的时候，一次性分配的内存。
sort_buffer_size 并不是越大越好，过大的设置+高并发可能会耗尽系统内存资源。

1.查看sort_buffer_size默认值，默认值为256K

mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

2.设置global级别的sort_buffer_size值，设置sort_buffer_size=1M，

https://www.cndba.cn/hbhe0316/article/22612

mysql> SET GLOBAL sort_buffer_size = 1024*1024;
Query OK, 0 rows affected (0.00 sec)

重新登录Mysql控制台https://www.cndba.cn/hbhe0316/article/22612https://www.cndba.cn/hbhe0316/article/22612

mysql> show variables like 'sort_buffer_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 1048576 |
+------------------+---------+
1 row in set (0.01 sec)

3.设置session级别的sort_buffer_size值，设置sort_buffer_size=2M.https://www.cndba.cn/hbhe0316/article/22612https://www.cndba.cn/hbhe0316/article/22612https://www.cndba.cn/hbhe0316/article/22612

mysql> SET session sort_buffer_size = 2*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sort_buffer_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 2097152 |
+------------------+---------+
1 row in set (0.00 sec)

4.设置永久生效，需要修改/etc/my.cnf文件，重启Mysql后生效。https://www.cndba.cn/hbhe0316/article/22612

[root@mysql57 logs]# cat /etc/my.cnf | grep -i sort-buffer-size
sort-buffer-size = 2M

[root@mysql57 logs]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.. SUCCESS! 

mysql> show variables like 'sort_buffer_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 2097152 |
+------------------+---------+
1 row in set (0.00 sec)

版权声明：本文为博主原创文章，未经博主允许不得转载。

Linux,oracle