浅析数据库缓冲池与SQL查询成本

如果我们想要查找多行记录，查询时间是否会成倍地提升呢？其实数据库会采用缓冲池的方式提升页(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）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。

                                                                                                  

本文分享自微信公众号 - 大数据技术与数仓（gh_95306769522d）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。