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

精选留言(25)

• 

  老毕

  顺序读的页面平均加载效率更高是因为顺序读更贴合存储介质的物理特性，即一次顺序读取一批相邻物理块的效率，大于多次随机访问不连续的物理块的效率。
  
  缓冲池机制和页面加载方式是计算机体系结构的经典方式，首先必须承认两个客观事实，一是资源有限，二是时间有限。从硬盘到内存再到CPU缓存，价格和效率永远存在矛盾，只能通过多级缓存的形式，将更贵的资源留给更热的数据。

  2019-08-14

  

   20
• 

  我不会算法

  请问下老师，缓冲池和查询缓存是一个东西吗？

  作者回复: 首先我们需要了解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-22

   2

   12
• 

  小年

  老师，不止可否在哪一期讲一讲面试的时候常考的一些SQL相关的内容呀？感觉这些索引深入了以后面试不太会涉及到，抱歉功利了点因为最近在秋招各种面试，担心看的太深了反而暂时用不到...

  2019-09-06

  

   3
• 

  lmtoo

  innodb_buffer_pool_size是缓存池总大小吗？如果缓存池个数大于1，那每个缓冲池大小是不是innodb_buffer_pool_size/innodb_buffer_pool_instances?

  2019-08-14

   2

   2
• 

  用0和1改变自己

  1,顺序读取是一种批量读取，读取的数据都是相邻的，所以不需要每一页都进行I/O操作，平均下来就效率更高了。
  2.缓存池的刷新机制和许多缓存都是一样的，达到一定数量后进行更新，以达到提升性能都目的。

  2019-08-14

   2

   1
• 

  梁

  那存在缓存池机制，我们在更新一条记录时，数据库是否会先将该页读到缓存中，再修改缓存中的页里面对应的记录，最后由checkpoint刷入磁盘呢？

  2019-11-22

  

  
• 

  梁

  我想SSD磁盘在单个页面随机读的效率是否不会比单个页面顺序读的效率差了，SSD寻道时间才0.1ms。

  2019-11-22

  

  
• 

  梁

  我想要是全闪存磁盘的话，就算是单个页面的随机读，效率也不会比顺序读取时平均一个页面的加载效率差，

  2019-11-22

  

  
• 

  扶幽

  是因为磁盘IO时寻道和半圈旋转时间较长吗？

  2019-11-07

  

  
• 

  雪飞鸿

  文中以MySQL为例讲解，SQL Server也是这样处理数据的吗？
  什么样的数据放到了内存但不放到缓冲池？缓冲池也在内存中，为什么还要区分数据是否存在与缓冲池中？

  2019-10-21

  

  
• 

  峻铭

  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-18

  

  
• 

  wonderq_gk

  如何把数据放到缓冲池
  
  

  作者回复: 需要的时候，MySQL会自动将数据加载到缓冲池的，避免每次查询数据都进行磁盘IO，同样因为缓冲池的大小有限，因此MySQL也会管理和淘汰缓冲池，使得缓冲池性能最大化

  2019-08-29

  

  
• 

  wonderq_gk

  老师，我这里size是32M，为什么也是有8个缓冲池

  2019-08-29

  

  
• 

  NO.9

  老师 你好，请教一个问题，
  看完本章前面的部分之后忽然间意识到：
        数据库Down掉之后的Recover，只能是用最新对backup+checkpoint+transaction的log 恢复，就是因为commit的内容还没有从缓冲池写入磁盘。

  2019-08-25

  

  
• 

  一米阳光

  老师，ssd硬盘也存在随机读吗，还是只是减少了机械硬盘的寻道

  2019-08-21

  

  
• 

  ABC

  感觉缓冲池很像MySQL里面的Redis。。
  
  老师，随机读取的意思是不是comment_id in (1001,10002,95230,5698)这样啊？

  2019-08-16

  

  
• 

  Geek_Wison

  老师，您好。这一节讲的数据库缓存池和新版本MySQL8.0取消的缓存指的是同一个东西吗？
  如果是的话，那这节课的内容只在旧版本的mysql成立，在新版本的mysql（取消了缓存的版本）就没用了？

  2019-08-16

   1

  
• 

  胡玲玲

  老师,有个问题请教一下:
  项目中使用kafka来做消息传递,监控的主库的binlog;但是由于功能场景需要再查询从库,这时候主从延迟会导致数据问题,出了重试机制还有其他解决办法吗?
  比如:能否在从库写入成功之后再发送消息

  2019-08-15

  

  
• 

  Demon.Lee

  数据页的加载方式，我可以手工调整是随机读取还是顺序读取吗？

  2019-08-14

  

  
• 

  asdf100

  对于上面的问题是不是想问的是“所谓顺序多个页，平均下来每个页的读取时间为什么比随机读取一个页的时候小”的么？如果这样的话，就容易理解多了。主要还是和磁盘存储原理有关

  2019-08-14

  

  