mysql 创建大量磁盘临时表

发布时间:2020-07-18 12:14:25 作者:geamover
来源:网络 阅读:2320

发现 mysql 中创建的临时表基本都是磁盘临时表,对此进行排查。最后为临时表机制说明。


通过查看 mysql 状态变量,看到创建的临时表基本都是磁盘临时表。

mysql 创建大量磁盘临时表


查看临时表配置,默认配置 16M,配置并没有问题。

mysql 创建大量磁盘临时表


创建两个表,做一个查询测试

create table t1(f1 int auto_increment primary key, f2 char(50), f3 datetime default now());
insert into t1 (f2) values ('赵'),('钱'),('孙'),('李');
create table t2(f1 int auto_increment primary key, f2 int, f3 char(50), f4 datetime default now());
insert into t2 (f2, f3) values ('3', '悟空'),('1', '四'),('4', '白');


刷新状态,进行查询,发现创建的是磁盘表

mysql 创建大量磁盘临时表


两张小表的查询完全满足创建内存临时表的条件,看来是配置的问题。查看配置文件中有配置 big_tables。

查看官方文档对 big_tables 的说明:

    如果设置为1,则所有临时表都存储在磁盘上而不是内存中。 这有点慢,但是对于需要大型临时表的 SELECT 操作,不会报 The table tbl_name is full 的错误。 新连接的默认值为 0(使用内存中的临时表)。 通常,您不需要设置此变量,因为根据需要将内存表自动转换为基于磁盘的表。


修改该系统变量再测试一下,这次创建的临时表就是内存临时表了。

mysql 创建大量磁盘临时表



mysql 中内部临时表(internal temporary table)机制

某些情况下,mysql 在执行语句的时候会创建内部临时表。用户不能直接控制何时发生。


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

  ● 评估 UINON 语句;

  ● 评估一些视图,例如使用 TEMPTABLE 算法,UNION 或聚合的视图;

  ● 派生表的评估(FROM子句中的子查询);

  ● 为子查询或半连接实现创建表;

  ● 评估包含 ORDER BY 子句和不同 GROUP BY 子句的语句,或 ORDER BY 或 GROUP BY 包含来自除加入队列中第一个表之外的表的列的语句;

  ● DISTINCT与ORDER BY结合使用可能需要一个临时表;

  ● 对于使用 SQL_SMALL_RESULT 修饰符的查询,MySQL 使用内存中的临时表,除非查询还包含需要磁盘存储的元素;

  ● 评估多表 UPDATE 语句;

  ● 评估 GROUP_CONCAT() 或 COUNT(DISTINCT) 表达式。


想要知道一条语句有没有使用临时表,通过 EXPLAIN 查看 Extra 列有没有说明 Using temporary。EXPLAIN 不一定会对临时表或派生临时表使用临时表。

当服务器创建一个内部临时表的时候(无论是内存表或磁盘表),会增加 Created_tmp_tables 状态变量的值。如果服务器创建一个磁盘临时表(直接建立或者从内存表转换)会增加 Created_tmp_disk_tables 状态变量的值。通过 show global status like 'Create%'; 语句查看。


某些查询条件会阻止使用内存中的临时表,在这种情况下,服务器会使用磁盘上的表:

  ● 表中存在BLOB或TEXT列;

  ● 如果使用 UNION 或 UNION ALL,则在 SELECT 列表中存在最大长度大于512(二进制字符串的字节,非二进制字符串的字符)的任何字符串列

  ● SHOW COLUMNS 和 DESCRIBE 语句使用 BLOB 作为某些列的类型,因此用于结果的临时表是一个磁盘上表。


推荐阅读:
  1. 【MySQL】【翻译】 MySQL 5.7 的内部临时表新特性
  2. MySQL性能调优技巧以及Monyog线程缓存监测

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

临时表 big tables

上一篇:DNS服务正向、反向解析区域,主/从区域数据库复制,子域授权及基本安全控制

下一篇:Android 关于百度地图Android SDK几处修正使用说明(非官方)

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》