导读
mysql 5.7的目标是成为发布以来最安全的mysql服务器,其在ssl/tls和全面安全开发方面有一些重要的改变。
mysql 5.7起支持独立临时表空间,但个别时候也可能会踩坑的。
mysql 5.7起,开始采用独立的临时表空间(和独立的undo表空间不是一回事哟),命名ibtmp1文件,初始化12m,且默认无上限。
选项 innodb_temp_data_file_path 可配置临时表空间相关参数。
innodb_temp_data_file_path = ibtmp1:12m:autoextend
临时表空间的几点说明
有时执行sql请求时会产生临时表,极端情况下,可能导致临时表空间文件暴涨,帮人处理过的案例中最高涨到快300g,比以前遇到的 ibdata1 文件暴涨还要猛…
临时表使用的几点建议
附:临时表测试案例
表ddl
create temporary table `tmp1` ( `id` int(10) unsigned not null default '0', `name` varchar(50) not null default '', `aid` int(10) unsigned not null auto_increment, `nid` int(11) unsigned generated always as ((`id` + 1)) virtual not null, `nnid` int(11) unsigned generated always as ((`id` + 1)) stored not null, primary key (`aid`), key `name` (`name`), key `id` (`id`), key `nid` (`nid`) ) engine=innodb default charset=utf8
原表大小只有 120mb,从这个表直接 insert…select 导数据到tmp1表。
-rw-r----- 1 yejr imysql 120m apr 14 10:52 /data/mysql/test/sid.ibd
生成临时表(去掉虚拟列,临时表不支持虚拟列,然后写入数据),还更大了(我也不解,以后有机会再追查原因)。
-rw-r----- 1 yejr imysql 140m jun 25 09:55 /users/yejinrong/mydata/ibtmp1
查看临时表元数据信息
yejr@imysql.com [test]>select * from information_schema.innodb_temp_table_info\g *********************** 1. row *********************** table_id: 405 name: #sql14032_300000005_3 n_cols: 6 space: 421 per_table_tablespace: false is_compressed: false
再删除索引,结果,又更大了
-rw-r----- 1 yejr imysql 204m jun 25 09:57 /data/mysql/ibtmp1
第二次测试删除索引后,变成了200m(因为第二次测试时,我设置了临时表最大200m)
innodb_temp_data_file_path = ibtmp1:12m:autoextend:max:200m
-rw-r----- 1 yejr imysql 200m jun 25 10:15 /data/mysql/ibtmp1
执行一个会产生临时表的慢sql。
注:mysql 5.7起,执行union all不再产生临时表(除非需要额外排序)。
yejr@imysql.com [test]>explain select * from tmp1 union select id,name,aid from sid\g *************************** 1. row *************************** id: 1 select_type: primary table: tmp1 partitions: null type: all possible_keys: null key: null key_len: null ref: null rows: 3986232 filtered: 100.00 extra: null *************************** 2. row *************************** id: 2 select_type: union table: sid partitions: null type: all possible_keys: null key: null key_len: null ref: null rows: 802682 filtered: 100.00 extra: null *************************** 3. row *************************** id: null select_type: union result table: <union1,2> partitions: null type: all possible_keys: null key: null key_len: null ref: null rows: null filtered: null extra: using temporary
文件涨到588m还没结束,我直接给卡了
-rw-r----- 1 yejr imysql 588m jun 25 10:07 /data/mysql/ibtmp1
第二次测试时,设置了临时表空间文件最大200m,再执行会报错:
yejr@imysql.com [test]>select * from tmp1 union select id,name,aid from sid; error 1114 (hy000): the table '/var/folders/bv/j4tjn6k54dj5jh1tl8yn6_y00000gn/t/#sql14032_5_8' is full
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对移动技术网的支持。
参考
https://dev.mysql.com/doc/refman/5.7/en/temporary-files.html
https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_temp_data_file_path
https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html
如对本文有疑问, 点击进行留言回复!!
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火,普罗米修斯的崛起
网友评论