SQL必知必会
陈旸
清华大学计算机博士
立即订阅
10179 人已学习
课程目录
已完结 49 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词丨SQL可能是你掌握的最有用的技能
免费
第一章:SQL语法基础篇 (19讲)
01丨了解SQL:一门半衰期很长的语言
02丨DBMS的前世今生
03丨学会用数据库的方式思考SQL是如何执行的
04丨使用DDL创建数据库&数据表时需要注意什么?
05丨检索数据:你还在SELECT * 么?
06丨数据过滤:SQL数据过滤都有哪些方法?
07丨什么是SQL函数?为什么使用SQL函数可能会带来问题?
08丨什么是SQL的聚集函数,如何利用它们汇总表的数据?
09丨子查询:子查询的种类都有哪些,如何提高子查询的性能?
10丨常用的SQL标准有哪些,在SQL92中是如何使用连接的?
11丨SQL99是如何使用连接的,与SQL92的区别是什么?
12丨视图在SQL中的作用是什么,它是怎样工作的?
13丨什么是存储过程,在实际项目中用得多么?
14丨什么是事务处理,如何使用COMMIT和ROLLBACK进行操作?
15丨初识事务隔离:隔离的级别有哪些,它们都解决了哪些异常问题?
16丨游标:当我们需要逐条处理数据时,该怎么做?
17丨如何使用Python操作MySQL?
18丨SQLAlchemy:如何使用Python ORM框架来操作MySQL?
19丨基础篇总结:如何理解查询优化、通配符以及存储过程?
第二章:SQL性能优化篇 (18讲)
20丨当我们思考数据库调优的时候,都有哪些维度可以选择?
21丨范式设计:数据表的范式有哪些,3NF指的是什么?
22丨反范式设计:3NF有什么不足,为什么有时候需要反范式设计?
23丨索引的概览:用还是不用索引,这是一个问题
24丨索引的原理:我们为什么用B+树来做索引?
25丨Hash索引的底层原理是什么?
26丨索引的使用原则:如何通过索引让SQL查询效率最大化?
27丨从数据页的角度理解B+树查询
28丨从磁盘I/O的角度理解SQL查询的成本
29丨为什么没有理想的索引?
30丨锁:悲观锁和乐观锁是什么?
31丨为什么大部分RDBMS都会支持MVCC?
32丨查询优化器是如何工作的?
33丨如何使用性能分析工具定位SQL执行慢的原因?
34丨答疑篇:关于索引以及缓冲池的一些解惑
35丨数据库主从同步的作用是什么,如何解决数据不一致问题?
36丨数据库没有备份,没有使用Binlog的情况下,如何恢复数据?
37丨SQL注入:你的SQL是如何被注入的?
第三章:认识DBMS (7讲)
38丨如何在Excel中使用SQL语言?
39丨WebSQL:如何在H5中存储一个本地数据库?
40丨SQLite:为什么微信用SQLite存储聊天记录?
41丨初识Redis:Redis为什么会这么快?
42丨如何使用Redis来实现多用户抢票问题
43丨如何使用Redis搭建玩家排行榜?
44丨DBMS篇总结和答疑:用SQLite做词云
第四章:SQL项目实战 (3讲)
45丨数据清洗:如何使用SQL对数据进行清洗?
46丨数据集成:如何对各种数据库进行集成和转换?
47丨如何利用SQL对零售数据进行分析?
结束语 (1讲)
结束语 | 互联网的下半场是数据驱动的时代
SQL必知必会
登录|注册

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

陈旸 2019-08-14
在开始今天的内容前,我们先来回忆一下之前的内容。
数据库存储的基本单位是页,对于一棵 B+ 树的索引来说,是先从根节点找到叶子节点,也就是先查找数据行所在的页,再将页读入到内存中,在内存中对页的记录进行查找,从而得到想要数据。你看,虽然我们想要查找的,只是一行记录,但是对于磁盘 I/O 来说却需要加载一页的信息,因为页是最小的存储单位。
那么对于数据库来说,如果我们想要查找多行记录,查询时间是否会成倍地提升呢?其实数据库会采用缓冲池的方式提升页的查找效率。
为了更好地理解 SQL 查询效率是怎么一回事,今天我们就来看看磁盘 I/O 是如何加载数据的。
这部分的内容主要包括以下几个部分:
数据库的缓冲池在数据库中起到了怎样的作用?如果我们对缓冲池内的数据进行更新,数据会直接更新到磁盘上吗?
对数据页进行加载都有哪些方式呢?
如何查看一条 SQL 语句需要在缓冲池中进行加载的页的数量呢?

数据库缓冲池

磁盘 I/O 需要消耗的时间很多,而在内存中进行操作,效率则会高很多,为了能让数据表或者索引中的数据随时被我们所用,DBMS 会申请占用内存来作为数据缓冲池,这样做的好处是可以让磁盘活动最小化,从而减少与磁盘直接进行 I/O 的时间。要知道,这种策略对提升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里,那么访问的成本就会降低很多。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《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
收起评论
25
返回
顶部