域名注册多少钱,网页优化包括,wordpress 文章内容页,淘宝网站的建设目标是文章目录 简介数据库缓冲池查看缓冲池的大小数据页加载的三种方式通过 last_query_cost 统计 SQL 语句的查询成本总结参考文献 简介
本节将介绍磁盘IO是如何加载数据的#xff0c;重点介绍一下数据库缓冲池的概念。主要包括#xff1a;
什么是数据库缓冲池#xff0c;它在… 文章目录 简介数据库缓冲池查看缓冲池的大小数据页加载的三种方式通过 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查询的成本
