如果我们想要查找多行记录,查询时间是否会成倍地提升呢?其实数据库会采用缓冲池的方式提升页(page)的查找效率。数据库的缓冲池在数据库中起到了怎样的作用?如何查看一条 SQL 语句需要在缓冲池中进行加载的页的数量呢?


数据库缓冲池

​   磁盘 I/O 需要消耗的时间很多,而在内存中进行操作,效率则会高很多,为了能让数据表或者索引中的数据随时被我们所用,DBMS 会申请占用内存来作为数据缓冲池,这样做的好处是可以让磁盘活动最小化,从而减少与磁盘直接进行 I/O 的时间。要知道,这种策略对提升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里,那么访问的成本就会降低很多。
​   那么缓冲池如何读取数据呢?
​   缓冲池管理器会尽量将经常使用的数据保存起来,在数据库进行页面读操作的时候,首先会判断该页面是否在缓冲池中,如果存在就直接读取,如果不存在,就会通过内存或磁盘将页面存放到缓冲池中再进行读取。

查看缓冲池大小

​   如果使用的是 MyISAM 存储引擎(只缓存索引,不缓存数据),对应的键缓存参数为 key_buffer_size,可以用它进行查看。
​   如果使用的是 InnoDB 存储引擎,可以通过查看 innodb_buffer_pool_size 变量来查看缓冲池的大小,命令如下:

mysql> show variables like 'innodb_buffer_pool_size';


​   此时 InnoDB 的缓冲池大小只有 8388608/1024/1024=8MB,我们可以修改缓冲池大小为 128MB,方法如下:

mysql> set global innodb_buffer_pool_size = 1073741824;

​    在 InnoDB 存储引擎中,可以同时开启多个缓冲池,查看缓冲池的个数,使用命令:

mysql> show variables like 'innodb_buffer_pool_instances';


​   只有一个缓冲池。实际上innodb_buffer_pool_instances默认情况下为 8,为什么只显示只有一个呢?这里需要说明的是,如果想要开启多个缓冲池,你首先需要将innodb_buffer_pool_size参数设置为大于等于 1GB,这时innodb_buffer_pool_instances才会大于 1。你可以在 MySQL 的配置文件中对innodb_buffer_pool_size进行设置,大于等于 1GB,然后再针对innodb_buffer_pool_instances参数进行修改。

查看SQL语句的查询成本

​    一条 SQL 查询语句在执行前需要确定查询计划,如果存在多种查询计划的话,MySQL 会计算每个查询计划所需要的成本,从中选择成本最小的一个作为最终执行的查询计划。

​   如果查看某条 SQL 语句的查询成本,可以在执行完这条 SQL 语句之后,通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。这个查询成本对应的是 SQL 语句所需要读取的页(page)的数量。

mysql> show status like 'last_query_cost'

example

mysql> select userid,rating from movierating where userid = 4169;

结果:2313 rows in set (0.05 sec)

mysql> show status like 'last_query_cost';

mysql> select userid,rating from movierating where userid between 4168 and 4175;

结果:2643 rows in set (0.01 sec)

mysql> show status like 'last_query_cost';

  你能看到页的数量是刚才的 1.4 倍,但是查询的效率并没有明显的变化,实际上这两个 SQL 查询的时间基本上一样,就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中,然后再进行查找。虽然页数量(last_query_cost)增加了不少,但是通过缓冲池的机制,并没有增加多少查询时间。