SQL进阶理论篇（八）：SQL查询的IO成本

简介

本节将介绍磁盘IO是如何加载数据的，重点介绍一下数据库缓冲池的概念。主要包括：

• 什么是数据库缓冲池，它在数据库中扮演了什么角色？
• 对数据页进行加载的几种方式
• 如何统计一条SQL语句中，需要在缓冲池中进行加载的页的数量。

数据库缓冲池

为了能够让数据表或者索引中的数据随时为我们所用，DBMS会申请一块内存来作为数据缓冲池。

数据缓冲池里会保存经常使用的数据，这样的话，当数据库进行页面读的时候，会首先来寻找该页面是否在缓冲池里，如果存在就直接读取，如果不存在，就会通过磁盘或者内存，将页面放进缓冲池里再进行读取。

缓冲池在数据库中的结构和作用如下图：

如果我们执行了类似update语句，改变了缓冲池里的数据，那么这些数据会立即同步到磁盘上吗？

当然不是。

实际上，当我们修改数据库中的记录时，首先会修改缓冲池中页的记录信息，然后数据库会以一定的频率将新的数据刷新回磁盘。所以不是每次发生更新操作 ，都会立即回写的。

比如说，当缓冲池空间不够用的时候，就需要释放掉一些不常用的页，这时候就会强行将这些页的数据回写到磁盘，然后在缓冲池里将这些页释放掉。

这里面有一个脏页（dirty Page）的概念，是指在缓冲池里被修改过，尚未回写，因此与磁盘上不同的数据页。

查看缓冲池的大小

如果使用的是MySQL的MyISAM引擎，其只缓存索引，不缓存数据，对应的键缓存参数为key_buffer_size，可以通过查看这个变量来查看缓冲池大小。

如果使用的是InnoDB引擎，则可以通过以下命令查看：

mysql > show variables like 'innodb_buffer_pool_size'

单位是B，转换成MB就是8MB。

如果想修改缓冲池大小为128MB，则可以通过：

set global innodb_buffer_pool_size = 134217728;

在InnoDB中，我们还可以同时开启多个缓冲池。

可以通过以下命令查看当前缓冲池的数量：

mysql > show variables like 'innodb_buffer_pool_instances'

默认情况下，其实是会有8个缓冲池，但是如果你的innodb_buffer_pool_size参数小于1G，那刚才的命令只会显示出1个缓冲池。

数据页加载的三种方式

如果缓冲池中没有我们想要的数据页，那么缓冲池有三种方式，可以将指定数据页加载进缓冲池，每种方式的读取效率会有不同。

• 内存读取

如果该数据页是在内存里，那么直接读进缓冲池，效率还是很高的。

• 随机读取

如果数据没有在内存里，那就是在磁盘里，因此我们需要在磁盘上对该页进行查找，假设整体时间是10ms，这 10ms 中有 6ms 是磁盘的实际繁忙时间（包括了寻道和半圈旋转时间），有 3ms 是对可能发生的排队时间的估计值，另外还有 1ms 的传输时间，将页从磁盘服务器缓冲区传输到数据库缓冲区中。

以上过程结束之后，我们才算完成了一页的读取，多页读取的话，时间会继续拉长。

• 顺序读取

顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘 I/O 操作了。

采用批量读取的方式，即使是从磁盘上进行读取，平均一页的读取效率也比从内存中单独读取一个页的效率要高。

通过 last_query_cost 统计 SQL 语句的查询成本

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

比如说，我们直接在聚集索引上查找一条指定记录：

mysql> SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id = 900001;

运行结果只有一条，运行时间为 0.042s。

然后再看下查询优化器的成本，执行以下代码：

mysql> SHOW STATUS LIKE 'last_query_cost';

可以看到，我们只检索了一页。

那我们把查询搞复杂点，比如说查询 comment_id 在 900001 到 9000100 之间的评论记录呢？

mysql> SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id BETWEEN 900001 AND 900100;

运行结果有100条记录，运行时间为 0.046s。

执行以下代码，查看查询优化器的成本：

mysql> SHOW STATUS LIKE 'last_query_cost';

可以看到我们大概进行了20个页的读取。

虽然读取的页变多了，但是两条SQL的查询时间基本一致。这是因为后台通过顺序读取，将页面一次性加载到了缓冲池里，然后再进行查找。所以虽然页数量增加了不少，但其实并没有消耗太多时间。

总结

注意，缓冲池跟我们在之前章里提过的查询缓存又不一样。

查询缓存服务的是查询结果集，它是指把查询结果缓存起来，这样下次遇到相同的查询就可以直接拿到结果。注意是相同查询才行，所以这种机制的查询缓存其实命中率不高，在MySQL8.0版本中已经弃用了查询缓存的功能。

而缓冲池是服务于数据库整体的IO操作，通过建立缓冲池来弥补磁盘文件和内存之间的速度鸿沟，从而提高整体的IO效率。

参考文献

1. 28丨从磁盘I/O的角度理解SQL查询的成本