This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read optimization. When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. See Section 9.2.1.15, “ORDER BY Optimization”. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.
MySQL手册里关于read_rnd_buffer_size的解释如下： [ mrr order by ]
sort后，得到的是行数据指针，通过key-value的形式存在，对于MyISAM是数据的偏移量，对于innodb是主键或存储重新查询的全量数据（对于小片的数据是有益的）。
假设sort后的数据使用的是行指针，并且行中的字段能够被转换成固定的大小（除了BLOB/TEXT字段外），MySQL能够使用read_rnd_buffer_size优化数据读取。
因为sort后的数据是以key-value的形式存在的，使用这些行指针去读取数据，将是以指针数据物理的顺序去读取，很大程度上是随机的方式读取数据的。MySQL从 sort_buffer中读取这些行指针数据，然后通过指针排序后存入read_rnd_buffer中，之后再通过指针读取数据时，基本上都是顺序读取了。
read_rnd_buffer_size是很重要的参数，尤其工作在如下场景：
* sort_buffer中存的是行指针而不是要查询的数据。
* 查询的字段中包含Blob/Text字段。
* sort后有大量的数据行（limit 10并不能帮助你，因为MySQL是通过指针获取行数据的）
如果你取出很少字段的数据（小于max_length_for_sort_data）,行数据将会全部存储在sort buffer里，因此将不需要read_rnd_buffer_size这个参数。
而如果你查询的字段数据很长（这些字段很可能含有Text/Blob字段），比max_length_for_sort_data还长，read_rnd_buffer_size这个参数将派上用场。
mysql> show variables like "%max_length_for_sort_data%";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024 |
+--------------------------+-------+
1 row in set (0.23 sec)
Looking for documentation for read_rnd_buffer_size you would find descriptions such as “The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance” which is cool but it does not really tell you how exactly read_rnd_buffer_size works as well as which layer it corresponds to – SQL or storage engine.
Honestly as it had name very similar to read_buffer_size which is currently only used by MyISAM tables I thoughtread_rnd_buffer_size is also MyISAM only. But talking to Monty today I learned it is not the case.
read_rnd_buffer can be used for All storage engines not only by MyISAM. It is used for some sorts to optimally read rows after the sort. Here is how it works:
As sort is performed it can be performed having only row pointers together with key value – which are offsets for MyISAM and primary key values for Innodb or storing full data which is being retrieved (good for small data lengths).
In case sort with row pointer storage is used and the fields which are being length can be converted to fixed size (basically everything but BLOB/TEXT) MySQL can use read_rnd_buffer to optimize data retrieval – As data is sorted by the key value it needs to be accessed in pretty much random row pointer (typically physical) order. MySQL takes bunch of pointers from sort_buffer (just enough so all rows fit in read_rnd_buffer as they are read) and sorts them by row pointer, when performs reading into read_rnd_buffer in the sorted order – it can be pretty much sequential if you’re lucky.
The read_rnd_buffer_size is important (optimization works in following conditions):
- Row pointers are stored in the sort_buffer, not the whole data selected
- Blob/Text columns are not selected
- A lot of rows are retrieved after sort – if you have LIMIT 10 it is unlikely to help as MySQL will stop fetching rows by
pointers quickly
For me this means since MySQL 4.1 this option is used in narrow range of cases – if you retrieve few fields (less thanmax_length_for_sort_data) data should be stored in sort buffer and sort file so there would be no need for read_rnd_buffer, if the selected columns are long so they are longer than max_length_for_sort_data it would frequently mean there are some TEXT/BLOB columns among them. It would be used however if there is large number of columns or there are long VARCHAR columns used – it takes only couple of UTF8 VARCHAR(255) to create a row which is longer than max_length_for_sort_data in its static presentation.
We should do benchmarks sometime to see how it really impacts performance both for MyISAM and Innodb.