二、MYSQL相關(guān)參數(shù) sort_buffer_size: 當(dāng)然也就是每次排序的buffer，用作內(nèi)部快速排序用，如果buffer越大當(dāng)然產(chǎn)生的物理文件也就越少，但是這個(gè) 參數(shù)是會話級別的，過分加大會造成內(nèi)存不足，默認(rèn)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
2、一次訪問排序 這個(gè)就簡單了，二次訪問排序是把排序鍵值和rowid(primary key)放到sort buffer， 這個(gè)就是關(guān)于需要的數(shù)據(jù)字段全部放到sort buffer比如： select id,name1,name2 from test order by id;
3、5.7.3后一次訪問排序算法的優(yōu)化 使用一個(gè)叫做pack優(yōu)化的方法，目的在于壓縮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的時(shí)候發(fā)現(xiàn)這還有一個(gè)unpack的過程，并且每一行每一個(gè)字段都需要pack unpack 隨后證明
分別在max_length_for_sort_data為1024和max_length_for_sort_data為4對 select * from testmer order by id1; 生成trace文件 意義也就是使用一次訪問排序和二次訪問排序,因?yàn)閿?shù)據(jù)量少也就在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 臨時(shí)文件數(shù)量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 臨時(shí)文件數(shù)量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上的源碼
點(diǎn)擊(此處)折疊或打開
/* 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));
