28丨从磁盘I/O的角度理解SQL查询的成本
该思维导图由 AI 生成，仅供参考
数据库缓冲池
荧光笔
直线
曲线- 深入了解
- 翻译
- 解释
- 总结
本文深入探讨了SQL查询成本的相关概念和原理，从磁盘I/O的角度解释了SQL查询的成本。首先介绍了数据库存储的基本单位是页，以及数据库缓冲池的作用和管理器的工作原理。随后详细讨论了数据页加载的三种方式：内存读取、随机读取和顺序读取，以及它们的读取效率。最后，通过last_query_cost统计SQL语句的查询成本，展示了如何通过查询成本来评估SQL查询的效率。文章以技术性强、深入浅出的方式，帮助读者理解了SQL查询成本的相关概念和原理。通过本文，读者可以了解到SQL查询的成本是如何影响数据库性能的，以及如何通过优化数据加载方式来提高查询效率。文章内容丰富，对于数据库管理和SQL查询优化有很大的参考价值。
《SQL 必知必会》，新⼈⾸单¥68
全部留言(42)
- 最新
- 精选
NIXUS请问下老师，缓冲池和查询缓存是一个东西吗？作者回复: 首先我们需要了解InnoDB存储引擎中缓冲池都包括了哪些，在InnoDB存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存（In-Memory Structures）的大部分，它是用来存储各种数据的缓存，包括了数据页，索引页，插入缓冲，锁信息，自适应Hash，数据字典信息等。 为什么要使用缓冲池技术呢，这时因为InnoDB存储引擎是基于磁盘文件存储的，我们在访问物理硬盘和在内存中进行访问速度相差很大，为了尽可能弥补这中间的IO效率鸿沟，我们就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘IO，从而提升数据库整体的访问性能。所以说“频次X位置”的原则，帮我们对IO访问效率进行了优化： 1）位置决定效率，提供缓冲池就是在内存中可以直接访问到数据，因此效率可以大幅提升 2）频次决定优先级顺序，因为缓冲池的大小是有限的，比如我们的磁盘有200G，但是内存只有16G，缓冲池大小只有1G，那么这时无法将所有数据都加载到缓冲池里，这里就有个优先级顺序的问题，也就是对经常使用频次高的热数据进行加载。 在了解了缓冲池作用之后，我们还需要了解缓冲池的另一个特性：预读。 因为缓冲池的作用就是提升IO效率，而我们进行读取数据的存在一个“局部性原理”，也就是我们使用了一些数据，大概率还会使用它周围的一些数据。因此我们可以采用“预读”的机制来减少未来的磁盘IO操作，进行提前加载。 我们再来看下什么是查询缓存？ 查询缓存是提前把查询结果缓存起来，这样下次就不需要执行可以直接拿到结果。需要说明的是，在MySQL中的查询缓存，不是缓存查询计划，而是查询及对应的查询结果。这就意味着查询匹配的鲁棒性大大降低，只有相同的查询操作才会命中查询缓存。因此在MySQL的查询缓存命中率不高，在MySQL8.0版本中已经弃用了查询缓存功能。查看是否使用了查询缓存，使用命令：show variables like '%query_chache%'; 所以说缓冲池不等于查询缓存，他们两个存在共同的特点就是都是通过缓存的机制来提升效率。而缓冲池是服务于数据库整体的IO操作，通过建立缓冲池机制来弥补存储引擎的磁盘文件与内存访问之间的效率鸿沟，同时缓冲池会采用“预读”的机器提前加载一些马上会用到的数据，以提升整体的数据库性能。而查询缓存是服务于SQL查询和查询结果集的，因为命中条件苛刻，而且只要当数据表发生了变化，查询缓存就会失效，因此命中率低，在MySQL8.0版本中已经弃用了该功能。
2019-08-22780
DZ顺序读的页面平均加载效率更高是因为顺序读更贴合存储介质的物理特性，即一次顺序读取一批相邻物理块的效率，大于多次随机访问不连续的物理块的效率。 缓冲池机制和页面加载方式是计算机体系结构的经典方式，首先必须承认两个客观事实，一是资源有限，二是时间有限。从硬盘到内存再到CPU缓存，价格和效率永远存在矛盾，只能通过多级缓存的形式，将更贵的资源留给更热的数据。作者回复: 整理的不错
2019-08-14252
小年老师，不止可否在哪一期讲一讲面试的时候常考的一些SQL相关的内容呀？感觉这些索引深入了以后面试不太会涉及到，抱歉功利了点因为最近在秋招各种面试，担心看的太深了反而暂时用不到...作者回复: SQL主要就是基本语法和性能优化
2019-09-0611
lmtooinnodb_buffer_pool_size是缓存池总大小吗？如果缓存池个数大于1，那每个缓冲池大小是不是innodb_buffer_pool_size/innodb_buffer_pool_instances?作者回复: 对 总大小
2019-08-1447
峻铭SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id = 900001; SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id BETWEEN 900001 AND 900100; 这两句查询的last_query_cost都是4.724，说明这不是页 官网：https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html The total cost of the last compiled query as computed by the query optimizer. This is useful for comparing the cost of different query plans for the same query. The default value of 0 means that no query has been compiled yet. The default value is 0. Last_query_cost has session scope 对同一个查询语句的不同查询计划的代价进行计较，选择代价最小的。last_query_cost得到的值只是一个查询计划的评分值，不是页作者回复: 整理总结的不错
2019-09-1836
扶幽是因为磁盘IO时寻道和半圈旋转时间较长吗？作者回复: 硬盘平均寻道时间 7-14ms，假设硬盘是6000转，转一圈（100个山区）时间是0.01，平均一个扇区的时间是0.1ms
2019-11-075- NO.9老师 你好，请教一个问题， 看完本章前面的部分之后忽然间意识到： 数据库Down掉之后的Recover，只能是用最新对backup+checkpoint+transaction的log 恢复，就是因为commit的内容还没有从缓冲池写入磁盘。
作者回复: 对的 多谢分享
2019-08-255 - 用0和1改变自己1,顺序读取是一种批量读取，读取的数据都是相邻的，所以不需要每一页都进行I/O操作，平均下来就效率更高了。 2.缓存池的刷新机制和许多缓存都是一样的，达到一定数量后进行更新，以达到提升性能都目的。
作者回复: 总结的不错
2019-08-1444 
程序员花卷第一个问题 我们的数据在磁盘上是相邻存储的，当我们将数据从磁盘中加载读取到缓冲区的时候，就直接一堆一堆的读取，根本不需要单独的去对某个数据进行I/O的操作，这样效率就会很高，（重点在于数据在磁盘中是相邻存储的） 第二个问题 缓冲池这个东西应该跟计算机组成原理中的高速缓冲区（Cpu Cache)是一个道理的，访问速度比内存快，内存又比磁盘快，CPU在进行数据读取的时候，首先就会去访问Cache，只有当Cache中找不到数据的时候，CPU才会去访问内存，将内存中的数据加载到Cache中，然后在进行访问！这是缓冲区比较快的一方面原因，其次就是有个东西叫做“局部性原理“，包括了时间局部性和空间局部性，在访问数据的时候，这个时间局部性就起作用了，刚刚被访问过的数据，会很快的被访问到，这样CPU花在等待内存访问的时间就大大的缩短了。 我觉得CPU这个访问数据的方式和数据库中访问的方式的原理是一样的！CPU中是CPU Cache,数据库中呢是比如”Redis缓存、MemCache缓存”等！ 不知道上述总结是否正确，但是我觉得万变不离其宗，很多东西只是换了一种方式去操作而已，其根本的原理是不变的！有不对的地方，还请老师指正！作者回复: 总结的不错！
2019-12-243
wonderq_gk如何把数据放到缓冲池作者回复: 需要的时候，MySQL会自动将数据加载到缓冲池的，避免每次查询数据都进行磁盘IO，同样因为缓冲池的大小有限，因此MySQL也会管理和淘汰缓冲池，使得缓冲池性能最大化
2019-08-293
