深度解析MySQL 5.7之临时表空间
临时表
临时表顾名思义,就是临时的,用完销毁掉的表。 数据既可以保存在临时的文件系统上,也可以保存在固定的磁盘文件系统上。
临时表有下面几种:
1、全局临时表
这种临时表从数据库实例启动后开始生效,在数据库实例销毁后失效。在MySQL里面这种临时表对应的是内存表,即memory引擎。
2、会话级别临时表
这种临时表在用户登录系统成功后生效,在用户退出时失效。在MySQL里的临时表指的就是以create temporary table
这样的关键词创建的表。
3、事务级别临时表
这种临时表在事务开始时生效,事务提交或者回滚后失效。 在MySQL里面没有这种临时表,必须利用会话级别的临时表间接实现。
4、检索级别临时表
这种临时表在SQL语句执行之间产生,执行完毕后失效。 在MySQL里面这种临时表不是很固定,跟随MySQL默认存储引擎来变化。比如默认存储引擎是MyISAM,临时表的引擎就是MyISAM,并且文件生成形式以及数据运作形式和MyISAM一样,只是数据保存在内存里;如果默认引擎是INNODB,那么临时表的引擎就是INNODB,此时它的所有信息都保存在共享表空间ibdata里面。
MySQL 5.7之临时表空间
MySQL 5.7对于InnoDB存储引擎的临时表空间做了优化。在MySQL 5.7之前,INNODB引擎的临时表都保存在ibdata里面,而ibdata的贪婪式磁盘占用导致临时表的创建与删除对其他正常表产生非常大的性能影响。在MySQL5.7中,对于临时表做了下面两个重要方面的优化:
1、MySQL 5.7 把临时表的数据以及回滚信息(仅限于未压缩表)从共享表空间里面剥离出来,形成自己单独的表空间,参数为innodb_temp_data_file_path
。
2、在MySQL 5.7 中把临时表的相关检索信息保存在系统信息表中:information_schema.innodb_temp_table_info
. 而MySQL 5.7之前的版本想要查看临时表的系统信息是没有太好的办法。
需要注意的一点就是:虽然INNODB临时表有自己的表空间,但是目前还不能自己定义临时表空间文件的保存路径,只能是继承innodb_data_home_dir。此时如果想要拿其他的磁盘,比如内存盘来充当临时表空间的保存地址,只能用老办法,做软链。举个小例子:
我现在用的OS是 Ubuntu12.X,想用tmpfs文件系统充当临时表空间,
root@ytt-master-VirtualBox:/usr/local/mysql/data# ln -s/run/shm/ /usr/local/mysql/data/tmp_space2 root@ytt-master-VirtualBox:/usr/local/mysql/data#ls -l | grep 'shm' lrwxrwxrwx1 root root 9 Nov 13 10:28tmp_space2 -> /run/shm/
然后把innodb_temp_data_file_path=tmp_space2/ibtmp2:200M:autoextend
添加到my.cnf里的[mysqld]下面一行, 重启MySQL服务后:
mysql>select @@innodb_temp_data_file_path\G ***************************1. row *************************** @@innodb_temp_data_file_path:tmp_space2/ibtmp2:200M:autoextend 1 rowin set (0.00 sec)
先写一个批量创建临时表的存储过程:
DELIMITER$$ USE`t_girl`$$ DROPPROCEDURE IF EXISTS `sp_create_temporary_table`$$ CREATEDEFINER=`root`@`localhost` PROCEDURE `sp_create_temporary_table`( IN f_cnt INT UNSIGNED ) BEGIN DECLARE i INT UNSIGNED DEFAULT 1; WHILE i <= f_cnt DO SET @stmt = CONCAT('create temporarytable tmp',i,' ( id int, tmp_desc varchar(60));'); PREPARE s1 FROM @stmt; EXECUTE s1; SET i = i + 1; END WHILE; DROP PREPARE s1; END$$ DELIMITER;
现在来创建10张临时表:
mysql>call sp_create_temporary_table(10); QueryOK, 0 rows affected (0.07 sec)
如果在以前,我们只知道创建了10张临时表,但是只能凭记忆或者手工记录下来临时表的名字等信息。
现在可以直接从数据字典里面检索相关数据。
mysql> select * frominformation_schema.innodb_temp_table_info; +----------+--------------+--------+-------+----------------------+---------------+ |TABLE_ID | NAME | N_COLS | SPACE| PER_TABLE_TABLESPACE | IS_COMPRESSED | +----------+--------------+--------+-------+----------------------+---------------+ | 56 | #sql1705_2_9 | 5 | 36 | FALSE |FALSE | | 55 | #sql1705_2_8 | 5 | 36 | FALSE |FALSE | | 54 | #sql1705_2_7 | 5 | 36 | FALSE |FALSE | | 53 | #sql1705_2_6 | 5 | 36 | FALSE |FALSE | | 52 | #sql1705_2_5 | 5 | 36 | FALSE |FALSE | | 51 | #sql1705_2_4 | 5 | 36 | FALSE |FALSE | | 50 | #sql1705_2_3 | 5 | 36 | FALSE |FALSE | | 49 | #sql1705_2_2 | 5 | 36 | FALSE |FALSE | | 48 | #sql1705_2_1 | 5 | 36 | FALSE |FALSE | | 47 | #sql1705_2_0 | 5 | 36 | FALSE |FALSE | +----------+--------------+--------+-------+----------------------+---------------+ 10rows in set (0.00 sec)
总结
功能性我就写到这里,大家性能方面如果有兴趣可以找时间去测试。希望本文的内容对大家学习或者使用mysql5.7能带来一定的帮助,如果有疑问大家可以留言交流。
相关文章
- 这篇文章主要介绍了mysql5.7 设置远程访问的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2021-02-08
- 这篇文章主要为大家分享了win7下MySql 5.7安装配置方法图文教程,感兴趣的小伙伴们可以参考一下...2016-05-20
mysql 5.7.17 安装配置方法图文教程(windows10)
这篇文章主要为大家分享了mysql 5.7.17 安装配置方法图文教程,具有一定的参考价值,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-01-22- 这篇文章主要为大家详细介绍了Linux虚拟机下mysql 5.7安装配置方法图文教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-01-15
- 这篇文章主要为大家分享了mysql5.7.11 winx64安装配置方法图文教程,感兴趣的朋友可以参考一下...2016-07-25
- 这篇文章主要为大家详细介绍了mysql5.7.17 zip 解压安装详细过程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-02-19
- 这篇文章主要为大家详细介绍了mysql 5.7.17 64bit解压缩版安装配置方法图文教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-01-15
- 这篇文章主要为大家分享了mysql 5.7.15安装配置方法图文教程,感兴趣的朋友可以参考一下...2016-10-02
- 这篇文章主要为大家详细介绍了Windows下php+mysql5.7配置教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下 WINDOWS 下 PHP+MYSQL配置下载mysql地址:https://d...2017-07-06
windows2008 64位系统下MySQL 5.7绿色版的安装教程
这篇文章主要给大家分享了在windows2008 64位系统下MySQL 5.7绿色版的安装教程,文中将安装步骤介绍的非常详细,相信会对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起看看吧。...2017-05-19- 本文给大家分享一个快速且简单的方法来解决Mysql5.7忘记root密码问题,非常不错,具有参考借鉴价值,需要的朋友参考下吧...2017-02-19
mysql 5.7.17 免安装版配置方法图文教程(windows10)
这篇文章主要为大家详细介绍了windows10下mysql 5.7.17 免安装版配置方法图文教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-01-22解析windows下使用命令的方式安装mysql5.7的方法
这篇文章主要介绍了windows下使用命令的方式安装mysql5.7的方法,本文图文并茂给大家介绍的非常详细,需要的朋友可以参考下...2017-01-15mysql 5.7.13 winx64安装配置方法图文教程(win10)
这篇文章主要为大家分享了mysql 5.7.13 winx64安装配置方法图文教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-01-22CentOS7.5使用mysql_multi方式安装MySQL5.7.28多实例(详解)
这篇文章主要介绍了CentOS7.5使用mysql_multi方式安装MySQL5.7.28多实例,非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下...2021-01-15mysql 5.7.16 安装配置方法图文教程(ubuntu 16.04)
这篇文章主要为大家分享了ubuntu 16.04下mysql 5.7.16 安装配置方法图文教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-01-26- 因为长时间不操作mysql而忘记root密码的朋友估计不在少数,最近发现在MySQL 5.7版本下用之前的方法修改密码不能成功了,所以只能重新想办法解决,下面这篇文章主要给大家介绍了MySQL 5.7忘记root密码后修改的详细教程,需要的朋友可以参考。...2017-05-19
- 这篇文章主要为大家详细介绍了MySQL 5.7.18 免安装版配置教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-05-09
- 这篇文章主要为大家分享了mysql 5.7.16winx64安装配置方法图文教程,感兴趣的朋友可以参考一下...2016-12-02
- 这篇文章主要介绍了CentOS6.5 安装 MySQL5.1.71的相关资料,非常不错,具有参考借鉴价值,需要的朋友可以参考下...2017-01-09