MySQL 在处理临时结果集时,内部临时表会使用多少内存?内部临时表何时使用磁盘?

0    59    1

👉 本文共约3437个字,系统预计阅读时间或需13分钟。

问题:内部临时表会使用多少内存呢?

MySQL 在处理临时结果集(UNION 运算 / 聚合运算等)时,会用到内部临时表(internal temporary table)。

那么内部临时表会使用多少内存呢?

实验:

我们先创建一个测试用的数据库,

图片

然后准备好数据,

图片

我们使用一个带 UNION 的子表,使执行计划会使用内部临时表:

图片

可以看到执行计划确实使用了临时表:

图片

下面我们另起一个 session,用 performance_schema 对内存进行观察:

图片

在主 session 中,探查其连接号,并找到线程号:

图片

在 performance_schema 中,确认其内存分配的统计初始状态:

图片

在主 session 中执行 SQL:

图片

在 performance_schema 中,查看其内存分配:

图片

可知在这个 SQL 的处理过程中,总共分配了 4M 多的内存用于内部临时表:

图片

我们都知道内存临时表是 memory(heap) 引擎格式的表,那我们手工建一个显式的内存表,应当和内存临时表使用的内存相同,来试验一下。

在主 session 中创建一张内存表,将数据插入到内存表中:

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

图片

观察 performance_schema 可知:内存表驻留在内存里的字节数与之前临时表使用的字节数相同。

图片

结论

我们通过 performance_schema 观察了 memory 引擎的内存分配,由此推算了内部临时表的内存占用情况。

MySQL 在其他元数据中,诸如 information_schema.INNODB_TEMP_TABLE_INFO 中,并不展示内部临时表的信息,如图:

图片

另外值得注意的是:memory 引擎会多划分出不少空间,比如本例中我们的数据是 300025 行 * 4 字节 =~ 1.2M,而引擎分出了 4M 多的内存来进行存储。

因此如果进行估算时,需要将数据量乘以一个较大的系数,才能准确估算。

我们是第二次用到了 dbdeployer,介绍一下其身世:

dbdeployer 的前身是著名的 mysql-sandbox,是著名博主 Giuseppe Maxia 的扛鼎之作(http://datacharmer.blogspot.com),可以极其方便地搭建 MySQL 多种架构的测试环境,命令简单优雅。

今后在实验中,我们会多次用到 dbdeployer,或者使用 MySQL 容器进行快速搭建和试验。

问题:内部临时表何时使用磁盘?

在前边的实验中中,我们看到了内部临时表会使用到不少内存。

那么如果需要的临时表再大一些,必然要使用到磁盘来承载,那么内部临时表是何时使用磁盘的?

实验

我们仍使用 实验 05 中的环境,略去准备数据的过程。

我们仍然使用两个会话,一个会话 run,用于运行主 SQL;另一个会话 ps,用于进行 performance_schema 的观察:

主会话线程号为 29,

图片

将 performance_schema 中的统计量重置,

图片

临时表的表大小限制取决于参数 tmp_table_size 和 max_heap_table_size 中较小者,我们实验中以设置 max_heap_table_size 为例。

我们将会话级别的临时表大小设置为 2M(小于上次实验中临时表使用的空间),执行使用临时表的 SQL:

图片

查看内存的分配记录:

图片

会发现内存分配略大于 2M,我们猜测临时表会比配置略多一点消耗,可以忽略。

查看语句的特征值:

图片

可以看到语句使用了一次需要落磁盘的临时表。

那么这张临时表用了多少的磁盘呢?

我们开启 performance_schema 中 waits 相关的统计项:

图片

重做实验,略过。

再查看 performance_schema 的统计值:

图片

可以看到几个现象:

\1. 临时表空间被写入了 7.92MiB 的数据。

\2. 这些数据是语句写入后,慢慢逐渐写入的。

来看看这些写入操作的特征,该方法我们在 实验 03 使用过:

图片

可以看到写入的线程是 page_clean_thread,是一个刷脏操作,这样就能理解数据为什么是慢慢写入的。

也可以看到每个 IO 操作的大小是 16K,也就是刷数据页的操作。

结论:

我们可以看到,

  1. MySQL 会基本遵守 max_heap_table_size 的设定,在内存不够用时,直接将表转到磁盘上存储。

  2. 由于引擎不同(内存中表引擎为 heap,磁盘中表引擎则跟随 internal_tmp_disk_storage_engine 的配置),本次实验写磁盘的数据量和 实验 05 中使用内存的数据量不同。

  3. 如果临时表要使用磁盘,表引擎配置为 InnoDB,那么即使临时表在一个时间很短的 SQL 中使用,且使用后即释放,释放后也会刷脏页到磁盘中,消耗部分 IO。

参考

https://mp.weixin.qq.com/mp/homepage?__biz=MzU2NzgwMTg0MA==&hid=9&sn=616ddd6a2417f34f6d836a2c2d567f12&scene=1&devicetype=android-29&version=28001c57&lang=zh_CN&nettype=cmnet&ascene=7&session_us=gh_0dc4cf247b24&wx_header=3

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

3 + 14 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部