增加mysql的sortbuffer_[深入理解MySQL系列] - sort_buffer-阿里云开发者社区

注：本系列文章主要探讨 MySQL 内存利用以及执行计划相关的一些知识点，从而为 MySQL 优化打下更好的基础。

环境说明

OS: AS4U6, 2.6.9-67.0.15.ELsmp, 16G Ram, MD3000阵列, xfs文件系统

MySQL 5.1.26 - percona(innodb plugin, innodb stat, user stat, msl, show patch, acc-pslist 补丁)

MySQL 主要配置参数

default_table_type = innodb

log_slow_queries

long_query_time = 0.001

log_slow_verbosity=query_plan,innodb

innodb_data_file_path = ibdata1:1024M:autoextend

innodb_log_file_size = 400M

innodb_log_files_in_group = 3

innodb_file_per_table

innodb_file_format="Barracuda"

其他参数均为默认值，因此其他几个内存相关参数值如下：

innodb_buffer_pool_size = 8388608

join_buffer_size = 131072

key_buffer_size = 8388600

max_heap_table_size = 16777216

query_cache_size = 0

read_buffer_size = 131072

read_rnd_buffer_size = 262144

sort_buffer_size = 2097144

tmp_table_size = 16777216

以后的所有例子中，如果没有特地注明，则测试相关的表都使用 InnoDB 引擎。

1、 排序缓冲

相关参数：sort_buffer_size, read_rnd_buffer_size

1.1 利用InnoDB的主键进行排序

EXPLAIN SELECT SQL_NO_CACHE * FROM T1 WHERE ID<10000 ORDER BY ID DESC;

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

| 1 | SIMPLE | T1 | range | PRIMARY | PRIMARY | 8 | NULL | 14872 | Using where |

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

# Query_time: 0.207893 Lock_time: 0.000056 Rows_sent: 9999 Rows_examined: 9999

# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

# Filesort: No Filesort_on_disk: No Merge_passes: 0

# InnoDB_IO_r_ops: 91 InnoDB_IO_r_bytes: 1490944 InnoDB_IO_r_wait: 0.083391

# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000

# InnoDB_pages_distinct: 93

SELECT SQL_NO_CACHE * FROM T1 WHERE ID<10000 ORDER BY ID DESC;

由于是针对主键/索引进行排序，因此无需使用临时表

1.2 利用 InnoDB 使用非索引字段排序

EXPLAIN SELECT SQL_NO_CACHE * FROM T1 WHERE ID<10000 ORDER BY C1 DESC;

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+

| 1 | SIMPLE | T1 | range | PRIMARY | PRIMARY | 8 | NULL | 14872 | Using where; Using filesort |

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+

# Query_time: 0.120879 Lock_time: 0.000023 Rows_sent: 9999 Rows_examined: 19998

# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

# Filesort: Yes Filesort_on_disk: Yes Merge_passes: 1

# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000

# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000

# InnoDB_pages_distinct: 93

SELECT SQL_NO_CACHE * FROM T1 WHERE ID<10000 ORDER BY C1 DESC;

由于 C1 不是索引字段，因此需要额外排序，并且由于 sort_buffer 和 read_rnd_buffer 不够大，也用到了磁盘文件。

加大 sort_buffer_size，再看看

set session sort_buffer_size = 1024 * 1024 * 5;

再次执行刚才的测试，结果发生了变化。

# Query_time: 0.080727 Lock_time: 0.000030 Rows_sent: 9999 Rows_examined: 19998

# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

# Filesort: Yes Filesort_on_disk: No Merge_passes: 0

# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000

# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000

# InnoDB_pages_distinct: 93

SELECT SQL_NO_CACHE * FROM T1 WHERE ID<10000 ORDER BY C1 DESC;

可以看到，Filesort_on_disk 变成了 No, Merge_passes 也变成了 0，表示无需使用磁盘文件，而直接在内存里排序。

1.3 加大 read_rnd_buffer_size 看看对 filesort 是否有影响

EXPLAIN SELECT SQL_NO_CACHE * FROM T1 AS T1 WHERE ID<10000 ORDER BY C1 DESC;

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+

| 1 | SIMPLE | T1 | range | PRIMARY | PRIMARY | 8 | NULL | 14872 | Using where; Using filesort |

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+

# Query_time: 0.103654 Lock_time: 0.000045 Rows_sent: 9999 Rows_examined: 19998

# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

# Filesort: Yes Filesort_on_disk: Yes Merge_passes: 1

# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000

# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000

# InnoDB_pages_distinct: 93

SELECT SQL_NO_CACHE * FROM T1 AS T1 WHERE ID<10000 ORDER BY C1 DESC;

具体过程不再每次重复贴了，结果是从 1M 到 512M，发现一直没什么变化，对 filesort 没什么帮助。 待续......

本文转自叶金荣51CTO博客，原文链接：http://blog.51cto.com/imysql/308839，如需转载请自行联系原作者