SQL性能优化系列——从磁盘I/O角度理解SQL查询成本[九]

2019年8月19日01:08:11 发表评论 53
摘要

1.数据库的缓冲池在数据库中起到了怎样的作用?如果对缓冲池内的数据进行更新,数据会直接更新到磁盘上么?
2.对数据页加载都有哪些方式?
3.如何查看一条SQL语句在缓冲池中进行加载的页的数量?

数据库存储的基本单元是页,即使是查询一行数据,但是对于磁盘I/O来说要加载该行数据所在的一页的信息,因为页是最小的存储单位。如果查询数据是多行,查询时间是否会成本增加呢?其实数据库会采用缓冲池的方式提升页的查询效率。

数据库缓冲池

磁盘I/O需要消耗很多时间,而在内存中操作则效率会提升很多,为了能让数据表或索引中的数据随时被我们所用,DBMS会申请占用内存来作为数据缓冲池,这样可以让磁盘活动最小化,从而减少与磁盘直接进行I/O的时间,这样访问成本会低很多。

缓冲池如何读取数据呢?

缓冲池管理器会尽量将经常使用的数据保存起来,当数据库进行页面读操作的时候首先会判断该页是否在缓冲池中,如果存在就直接读取,如果不在就通过内存或磁盘将页面保存到缓冲池中再进行读取。

SQL性能优化系列——从磁盘I/O角度理解SQL查询成本[九]

如果执行SQL语句更新了缓冲池中的数据,这些数据会立即更新到磁盘上么?

事实上,当对数据库中的记录进行修改时,首先修改的是缓冲池中页里面的记录信息,然后数据库才会以一定的频率刷新到磁盘上。并不是每一次更新都会立即进行磁盘回写。缓冲池会采用一种叫做checkpoint的机制将数据回写到磁盘上,这样能提升数据库的整体性能。比如,当缓冲池不够用时,需要释放掉一些不常用的页,就采用checkpoint的方式将不常用的脏页回写到磁盘上,然后从缓冲池中释放掉这些页。脏页(dirty page)是指缓冲池中被修改过的页,与磁盘上的页不一致。

查看缓冲池大小

如果使用的是MySQL的 MyISAM 存储引擎,它只缓存索引,不缓存数据,对应的键缓存参数为 key_buffer_size 可以进行查看。

如果使用的是InnoDB 存储引擎,可以通过查看 innodb_buffer_size 变量查看缓冲池大小,如:

mysql > show variables like 'innodb_buffer_pool_size'

也可以通过命令修改缓冲池大小(如修改为128M):

mysql > set global innodb_buffer_pool_size = 134217728;

另外,在InnoDB存储引擎中可以开启多个缓冲池,通过命令可以查看缓冲池的数量:

mysql > show variables like 'innodb_buffer_pool_instances'

SQL性能优化系列——从磁盘I/O角度理解SQL查询成本[九]

实际上innodb_buffer_pool_instances默认情况下是8,为什么这里只显示为1?这是因为需要先将innodb_buffer_pool_size设置为大于1GB,缓冲池个数才会大于1。

数据页加载的三种方式

缓冲池中没有数据时,有三种读取数据方式,每种方式效率不同。

1.内存读取

如果数据存储在内存中,都取到缓冲池中约1ms,效率还是很高的。

2.随机读取

如果数据没有在内存中,就需要在磁盘上对该页进行查找,整体时间预估在 10ms 左右,这 10ms 中有 6ms 是磁盘的实际繁忙时间(包括了寻道和半圈旋转时间),有 3ms 是对可能发生的排队时间的估计值,另外还有 1ms 的传输时间,将页从磁盘服务器缓冲区传输到数据库缓冲区中。这 10ms 看起来很快,但实际上对于数据库来说消耗的时间已经非常长了,因为这还只是一个页的读取时间。

SQL性能优化系列——从磁盘I/O角度理解SQL查询成本[九]

3.顺序读取

顺序读取其实是一种批量读取的方式,因为我们请求的数据在磁盘上往往都是相邻存储的,顺序读取可以帮我们批量读取页面,这样的话,一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘 I/O 操作了。如果一个磁盘的吞吐量是 40MB/S,那么对于一个 16KB 大小的页来说,一次可以顺序读取 2560(40MB/16KB)个页,相当于一个页的读取时间为 0.4ms。采用批量读取的方式,即使是从磁盘上进行读取,效率也比从内存中只单独读取一个页的效率要高。

统计SQL语句的查询成本

前面说过,一条 SQL 查询语句在执行前需要确定查询计划,如果存在多种查询计划的话,MySQL 会计算每个查询计划所需要的成本,从中选择成本最小的一个作为最终执行的查询计划。

如果我们想要查看某条 SQL 语句的查询成本,可以在执行完这条 SQL 语句之后,通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。这个查询成本对应的是 SQL 语句所需要读取的页的数量。

以 product_comment 表为例,如果我们想要查询 comment_id=900001 的记录,然后看下查询成本,我们可以直接在聚集索引上进行查找:

mysql> SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id = 900001;

然后再看下查询优化器的成本,实际上我们只需要检索一个页即可:

mysql> SHOW STATUS LIKE 'last_query_cost';

SQL性能优化系列——从磁盘I/O角度理解SQL查询成本[九]

如果我们想要查询 comment_id 在 900001 到 9000100 之间的评论记录呢?

mysql> SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id BETWEEN 900001 AND 900100;

然后再看下查询优化器的成本,这时我们大概需要进行 20 个页的查询。

mysql> SHOW STATUS LIKE 'last_query_cost';

SQL性能优化系列——从磁盘I/O角度理解SQL查询成本[九]

你能看到页的数量是刚才的 20 倍,但是查询的效率并没有明显的变化,实际上这两个 SQL 查询的时间基本上一样,就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中,然后再进行查找。虽然页数量(last_query_cost)增加了不少,但是通过缓冲池的机制,并没有增加多少查询时间。

总结

SQL查询是一个动态过程,从页加载的角度看,可以得到以下两点结论:

1.位置决定效率。如果页就在数据库缓冲池中,那么效率是最高的,否则还需要从内存或者磁盘中进行读取,当然针对单个页的读取来说,如果页存在于内存中,会比在磁盘中读取效率高很多。

2.批量决定效率。如果我们从磁盘中对单一页进行随机读,那么效率是很低的(差不多 10ms),而采用顺序读取的方式,批量对页进行读取,平均一页的读取效率就会提升很多,甚至要快于单个页面在内存中的随机读取。

所以说,遇到 I/O 并不用担心,方法找对了,效率还是很高的。我们首先要考虑数据存放的位置,如果是经常使用的数据就要尽量放到缓冲池中,其次我们可以充分利用磁盘的吞吐能力,一次性批量读取数据,这样单个页的读取效率也就得到了提升。

(本文完)

  • 我的微信
  • 微信扫一扫
  • weinxin
  • 微信公众号
  • 微信公众号扫一扫
  • weinxin
  • A+
所属分类:SQL

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: