[深入了解MySQL系列] - sort_buffer

作/譯者：葉金榮（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

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 也變成了

，表示無需使用磁盤檔案，而直接在記憶體裡排序。

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 沒什麼幫助。 待續......

本文出自 “MySQL中文網”部落格 http://www.imysql.cn/