CNDBA社区
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”
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.
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，
mysql> SET GLOBAL sort_buffer_size = 1024*1024;
Query OK, 0 rows affected (0.00 sec)
重新登录Mysql控制台
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.
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后生效。
[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
