MYSQL 临时表用法总结
1.背景
老项目最近被吐槽性能不行,经排查发现mysql占用cpu过高,梳理流程发现一些经常被使用的字段需要多张表关联之后可获取,于是想到了临时表;
2.临时表用法总结
- 将正常的CREATE TABLE语句改为CREATE TEMPORARY TABLE即可;
- 临时表将在你连接MySQL期间存在。当你断开时,MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。
- 如果在你创建名为test_table_20200313临时表时名为test_table_20200313的表在数据库中已经存在,临时表将隐藏非临时表test_table_20200313;
- 创建临时表时如果指定ENGINE=memory,系统会在内存中创建它;因为表存储在内存中,所以对它运行的查询可能比磁盘上的临时表快些;
2.1 磁盘临时表(和数据库其他表一样,都被存放在物理磁盘中)
mysql> CREATE TEMPORARY TABLE `test_table_20200313` (
-> `commandid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'id',
-> `idcid` varchar(18) NOT NULL DEFAULT '' COMMENT '',
-> `add_time` varchar(19) NOT NULL DEFAULT '' COMMENT '',
-> `local` tinyint(1) NOT NULL DEFAULT '0'
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test_table_20200313 VALUES (369,'abc','123456789',0);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test_table_20200313;
+-----------+-------+-----------+-------+
| commandid | idcid | add_time | local |
+-----------+-------+-----------+-------+
| 369 | abc | 123456789 | 0 |
+-----------+-------+-----------+-------+
1 row in set (0.00 sec)
mysql> desc test_table_20200313;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| commandid | bigint(20) unsigned | NO | | 0 | |
| idcid | varchar(18) | NO | | | |
| add_time | varchar(19) | NO | | | |
| local | tinyint(1) | NO | | 0 | |
+-----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show tables;
+---------------------+
| Tables_in_chang_dep |
+---------------------+
| emp |
| emp_bak |
+---------------------+
2 rows in set (0.00 sec)
mysql> DROP TABLE test_table_20200313;
Query OK, 0 rows affected (0.00 sec)
2.2 内存临时表
mysql> CREATE TEMPORARY TABLE `test_table_20200313` (
-> `commandid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'id',
-> `idcid` varchar(18) NOT NULL DEFAULT '' COMMENT '',
-> `add_time` varchar(19) NOT NULL DEFAULT '' COMMENT '',
-> `local` tinyint(1) NOT NULL DEFAULT '0'
-> ) ENGINE=memory;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test_table_20200313 VALUES (369,'abc','123456789',0);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test_table_20200313;
+-----------+-------+-----------+-------+
| commandid | idcid | add_time | local |
+-----------+-------+-----------+-------+
| 369 | abc | 123456789 | 0 |
+-----------+-------+-----------+-------+
1 row in set (0.00 sec)
mysql> desc test_table_20200313;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| commandid | bigint(20) unsigned | NO | | 0 | |
| idcid | varchar(18) | NO | | | |
| add_time | varchar(19) | NO | | | |
| local | tinyint(1) | NO | | 0 | |
+-----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show tables;
+---------------------+
| Tables_in_chang_dep |
+---------------------+
| emp |
| emp_bak |
+---------------------+
2 rows in set (0.00 sec)
mysql> DROP TABLE test_table_20200313;
Query OK, 0 rows affected (0.00 sec)
2.3 二者区别
- 磁盘临时表建表时允许直接将查询结果导入临时表,且临时表中的字段类型和原表中的完全一致;内存临时表无法实现建表时直接将查询结果导入临时表;
mysql> CREATE TEMPORARY TABLE `test_table_20200313_1` (
-> `commandid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '指令id',
-> `idcid` varchar(18) NOT NULL DEFAULT '' COMMENT '许可证号',
-> `add_time` varchar(19) NOT NULL DEFAULT '' COMMENT '时间',
-> `local` tinyint(1) NOT NULL DEFAULT '0'
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test_table_20200313_1 VALUES (369,'abc','2222222222',0);
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TEMPORARY TABLE `test_table_20200313_2` (
-> `commandid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '指令id',
-> `name` varchar(18) NOT NULL DEFAULT '' COMMENT '姓名'
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test_table_20200313_2 VALUES (369,'tom');
Query OK, 1 row affected (0.01 sec)
mysql> select a.commandid,a.idcid,b.name from test_table_20200313_1 a ,test_table_20200313_2 b where a.commandid=b.commandid and a.idcid='abc';
+-----------+-------+------+
| commandid | idcid | name |
+-----------+-------+------+
| 369 | abc | tom |
+-----------+-------+------+
1 row in set (0.00 sec)
mysql> CREATE TEMPORARY TABLE test_table_20200313
-> (
-> select a.commandid,a.idcid,b.name from test_table_20200313_1 a ,test_table_20200313_2 b where a.commandid=b.commandid and a.idcid='abc'
-> );
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from test_table_20200313;
+-----------+-------+------+
| commandid | idcid | name |
+-----------+-------+------+
| 369 | abc | tom |
+-----------+-------+------+
1 row in set (0.00 sec)
mysql> desc test_table_20200313;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| commandid | bigint(20) unsigned | NO | | 0 | |
| idcid | varchar(18) | NO | | | |
| name | varchar(18) | NO | | | |
+-----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> CREATE TEMPORARY TABLE test_table_20200313_mem
-> (
-> select a.commandid,a.idcid,b.name from test_table_20200313_1 a ,test_table_20200313_2 b where a.commandid=b.commandid and a.idcid='abc'
-> ) ENGINE=memory;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=memory' at line 4
mysql>