关于mysql 5.6.20 innodb 对 read_buffer_size参数进行了测试
结论如下
mysql 5.6.20官方版本没有数据块多块读的功能,与是否设置 read_buffer_size参数无关。对大表扫描该参数基本没啥效果。
测试过程如下
关闭数据库
mysqladmin shutdown
关闭系统缓存
echo 1 > /proc/sys/vm/drop_caches
重启数据库
mysqld_safe &
查看read_buffer_size参数值为128K
mysql> show variables like '%read%';
+-----------------------------------------+---------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------+
| innodb_purge_threads | 1 |
.......................................................
| pseudo_thread_id | 1 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
.......................................................
+-----------------------------------------+---------------------------+
23 rows in set (0.00 sec)
查看执行计划
mysql> explain select count(*) from user_info4 where user_name='TTS';
+----+-------------+------------+------+---------------+------+---------+------+-----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+-----------+-------------+
| 1 | SIMPLE | user_info4 | ALL | NULL | NULL | NULL | NULL | 158311631 | Using where |
+----+-------------+------------+------+---------------+------+---------+------&#