注：本系列文章主要探讨 mysql 内存利用以及执行计划相关的一些知识点，从而为 mysql 优化打下更好的基础。
作/译者：叶金荣（email: ），来源：http://imysql.cn，转载请注明作/译者和出处，并且不能用于商业用途，违者必究。
注：本系列文章主要探讨 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
explain select sql_no_cache * from t1 where id+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| 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由于是针对主键/索引进行排序，因此无需使用临时表
1.2 利用 innodb 使用非索引字段排序explain select sql_no_cache * from t1 where id+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
| 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由于 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可以看到，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+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
| 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具体过程不再每次重复贴了，结果是从 1m 到 512m，发现一直没什么变化，对 filesort 没什么帮助