二、MYSQL相关参数 sort_buffer_size: 当然也就是每次排序的buffer，用作内部快速排序用，如果buffer越大当然产生的物理文件也就越少，但是这个 参数是会话级别的，过分加大会造成内存不足，默认256K。注意： On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values
3、5.7.3后一次访问排序算法的优化 使用一个叫做pack优化的方法，目的在于压缩NULL减少一次访问排序算法对sort buffer和tempfile的过多使用 原文： without packing, a VARCHAR(255) column value containing only 3 characters takes 255 characters in the sort buffer. With packing, the value requires only 3 characters plus a two-byte length indicator. NULL values require only a bitmask. 但是我在做MYSQL TRACE的时候发现这还有一个unpack的过程，并且每一行每一个字段都需要pack unpack 随后证明
分别在max_length_for_sort_data为1024和max_length_for_sort_data为4对 select * from testmer order by id1; 生成trace文件 意义也就是使用一次访问排序和二次访问排序,因为数据量少也就在sort_buffer 排序就好了。
mysql> select count(*) from testshared3; +----------+ | count(*) | +----------+ | 1048576 | +----------+ 1 row in set (28.31 sec)
mysql> set sort_buffer_size=50000; Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%sort_buffer_size%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | sort_buffer_size | 50000 | +-------------------------+---------+
mysql> show status like '%Sort_merge%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 0 | +-------------------+-------+ 1 row in set (0.00 sec)
mysql> explain select * from testshared3 order by id limit 1048570,1; +----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | 1 | SIMPLE | testshared3 | NULL | ALL | NULL | NULL | NULL | NULL | 1023820 | 100.00 | Using filesort | +----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+----------------+ mysql> select * from testshared3 order by id limit 1048570,1; +------+ | id | +------+ | 1 | +------+ 1 row in set (5 min 4.76 sec) 完成后 mysql> show status like '%Sort_merge%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 63 | +-------------------+-------+ 1 row in set (0.21 sec)
opt: number_of_tmp_files: 378 临时文件数量378
然后加大sort_buffer_size
mysql> show variables like '%sort_buffer_size%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | sort_buffer_size | 262144 | +-------------------------+---------+
mysql> show status like '%Sort_merge%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 0 | +-------------------+-------+ 1 row in set (0.04 sec)
还是同样的语句
mysql> select * from testshared3 order by id limit 1048570,1; +------+ | id | +------+ | 1 | +------+ 1 row in set (5 min 4.76 sec) mysql> show status like '%Sort_merge%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 11 | +-------------------+-------+ opt: number_of_tmp_files: 73 临时文件数量73
(gdb) n 250 if (param->sort_length < 10) (gdb) list 245 than quicksort seems to be somewhere around 10 to 40 records. 246 So we're a bit conservative, and stay with quicksort up to 100 records. 247 */ 248 if (count <= 100) 249 { 250 if (param->sort_length < 10) 251 { 252 std::sort(m_sort_keys, m_sort_keys + count, 253 Mem_compare(param->sort_length)); 254 return;
这部分mysql上的源码
点击(此处)折叠或打开
/* std::stable_sort has some extra overhead in allocating the temp buffer, which takes some time. The cutover point where it starts to get faster than quicksort seems to be somewhere around 10 to 40 records. So we're a bit conservative, and stay with quicksort up to 100 records. */ if (count <= 100) { if (param->sort_length < 10) { std::sort(m_sort_keys, m_sort_keys + count, Mem_compare(param->sort_length)); return; } std::sort(m_sort_keys, m_sort_keys + count, Mem_compare_longkey(param->sort_length)); return; } // Heuristics here: avoid function overhead call for short keys. if (param->sort_length < 10) { std::stable_sort(m_sort_keys, m_sort_keys + count, Mem_compare(param->sort_length)); return; } std::stable_sort(m_sort_keys, m_sort_keys + count, Mem_compare_longkey(param->sort_length));
