文章目录
- 简介
- 数据库缓冲池
- 查看缓冲池的大小
- 数据页加载的三种方式
- 通过 last_query_cost 统计 SQL 语句的查询成本
- 总结
- 参考文献
简介
本节将介绍磁盘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效率。
参考文献
- 28丨从磁盘I/O的角度理解SQL查询的成本
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
本文深入探讨了云计算的起源、发展路径、当前的技术应用、安全与合规性及未来展望。通过详尽的分析,揭示了云计算如何从基础设施服务发展到现今的多元化技术领域,包括无服务器计算、容器化、云原生技术等,并强调了安全与合规在云计算广泛采用中的重要性。同时,展望了云计算与A…