MySQL OCP888题解066-sort_buffer_size和tmp_table_size

豪杰笑开怀

于 2023-03-27 17:04:20 发布

阅读量679

点赞数

分类专栏： MySQL OCP888题解(MySQL5.7) 文章标签： mysql 数据库服务器 ocp sql

本文链接：https://blog.csdn.net/oddrock/article/details/129799180

版权

MySQL OCP888题解(MySQL5.7) 专栏收录该内容

78 篇文章 13 订阅

订阅专栏

文章目录

1、原题
- 1.1、英文原题
- 1.2、答案
2、题目解析
- 2.1、题干解析
- 2.2、选项解析
3、知识点
- 3.1、知识点1：tmp_table_size系统变量
- 3.2、知识点2：在MySQL中使用内部临时表
4、总结

1、原题

1.1、英文原题

You have config file for a running DB with this excerpt:

A、Global variables are not persistent across server restarts.
B、Session variables are not persistent across server restart.
C、The query benefited from sort_buffer_size and tmp_table_size increases.
D、sort_buffer_size should match tmp_table_size to be optimal.
E、The query benefited from tmp_table_size increase.

1.2、答案

A、B、C

2、题目解析

2.1、题干解析

本题主要考察sort_buffer_size和tmp_table_size这两个系统变量。

2.2、选项解析

在客户端里设置的系统变量，无论是全局的还是会话级别的，重启后都会丢失，所以选项A和B正确。

3、知识点

3.1、知识点1：tmp_table_size系统变量

tmp_table_size系统变量代表内存中临时表的最大尺寸，这个变量不适用于用户创建的MEMORY表。对于内存中临时表的尺寸的实际限制是tmp_table_size和max_heap_table_size中较小的一个。当内存中的临时表超过限制时，MySQL自动将其转换为磁盘上的临时表。
如果你做许多高级GROUP BY查询，并且你有大量的内存，请增加tmp_table_size的值（如果有必要，也增加max_heap_table_size）。
你可以通过比较Created_tmp_disk_tables和Created_tmp_tables的值来比较创建的内部磁盘临时表的数量和创建的内部临时表总数。

官方参考文档

3.2、知识点2：在MySQL中使用内部临时表

服务器在以下情况下会创建临时表：

UNION语句。
一些视图，比如那些使用TEMPTABLE算法、UNION或者聚合的视图。
派生表。
为子查询或半连接物化而创建的表。
包含ORDER BY子句和不同GROUP BY子句的语句，或者ORDER BY或GROUP BY包含来自连接队列中第一个表以外的表的列。
DISTINCT与ORDER BY的组合可能需要一个临时表。
从同一表中选择并插入到同一表中的INSERT … SELECT语句，MySQL创建一个内部临时表来保存来自SELECT的行，然后将这些行插入到目标表中。
多表UPDATE语句。
GROUP_CONCAT()或COUNT(DISTINCT)表达式。

要确定一个语句是否需要一个临时表，可以使用EXPLAIN并检查Extra列，看它是否写着使用临时表。但对于派生或物化的临时表，EXPLAIN不一定说使用临时表。

一些查询条件阻止了内存中临时表的使用，在这种情况下，服务器会使用一个磁盘上的表来代替。

表中存在一个BLOB或TEXT列。
如果使用UNION或UNION ALL，且在SELECT列表中存在任何最大长度大于512的字符串列（二进制字符串为字节，非二进制字符串为字符）。

内部临时表存储引擎

内存临时表的引擎是MEMORY存储，磁盘临时表的引擎可以是InnoDB或MyISAM存储引擎。
如果一个内存内部临时表太大，MySQL会自动将其转换为磁盘临时表。内存中临时表的最大尺寸由tmp_table_size或max_heap_table_size值定义，以较小者为准。这与用CREATE TABLE明确创建的MEMORY表不同。对于这样的表，只有max_heap_table_size变量决定了一个表可以增长到多大，而且不需要转换为磁盘格式。
internal_tmp_disk_storage_engine变量定义了服务器用来管理磁盘上内部临时表的存储引擎。允许的值是INNODB（默认）和MYISAM。
当使用internal_tmp_disk_storage_engine=INNODB时，生成磁盘上内部临时表的查询如果超过InnoDB的行或列限制，会返回行大小过大或列数过多的错误。解决方法是将 internal_tmp_disk_storage_engine 设置为 MYISAM。
当在内存或磁盘上创建一个内部临时表时，服务器会增加Created_tmp_tables的值。当内部临时表在磁盘上被创建时，服务器会增加Created_tmp_disk_tables的值。如果在磁盘上创建了太多的内部临时表，可以考虑增加tmp_table_size和max_heap_table_size设置。

官方参考文档

4、总结

MySQL会在使用UNION、GROUP、ORDER、SELECT…INSERT等情况下使用内存中的临时表存储数据，内存中临时表的尺寸限制为tmp_table_size和max_heap_table_size两个变量中间的较小值。当内存中的临时表超过此限制时，MySQL自动将其转换为磁盘上的临时表。