1. MySQL临时表
1.1 两种临时表
1.2 内部临时表
如果HEAP临时表存储的数据大于MAX_HEAP_TABLE_SIZE(详情请参考MySQL手册中系统变量部分),HEAP临时表将会被自动转换成OnDisk临时表
2. 外部临时表的用法
外部临时表是通过CREATE TEMPORARY TABLE
及DROP TABLE
来操作的,但是SHOW TABLES
命令显示数据表列表时,无法看到自己创建的临时表的。并且在退出当前会话后,临时表就会被自动销毁。当然也可以手动销毁。
示例:
mysql> CREATE TEMPORARY TABLE tmpSalesSum (product_name VARCHAR(50) NOT NULL, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tmpSalesSum (product_name, total_sales, avg_unit_price, total_units_sold) VALUES ('coco', 110.5, 80, 4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tmpSalesSum;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| coco | 110.50 | 80.00 | 4 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> select * from tmpSalesSum, tmpSalesSum as temp;
ERROR 1137 (HY000): Can't reopen table: 'tmpSalesSum'
mysql> drop table tmpSalesSum;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tmpSalesSum;
ERROR 1146 (42S02): Table 'goframe.tmpsalessum' doesn't exist
3. 内部临时表的用法
示例
mysql> CREATE TABLE insideTable( a int, b int);
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO insideTable VALUES(11,22),(33,44);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
-- 在SQL语句中使用SQL_BUFFER_RESULT hint; SQL_BUFFER_RESULT主要用来让MySQL尽早的释放表上的锁。因为如果数据量很大的话,需要较长时间将数据发送到客户端,通过将数据缓冲到临时表中可以有效的减少读锁对表的占用时间。
mysql> explain format=json select SQL_BUFFER_RESULT * from insideTable;
+-----------------------------------------------------------------------------------------+
| EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "0.45"
},
"buffer_result": {
"using_temporary_table": true,
"table": {
"table_name": "insideTable",
"access_type": "ALL",
"rows_examined_per_scan": 2,
"rows_produced_per_join": 2,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.20",
"prefix_cost": "0.45",
"data_read_per_join": "32"
},
"used_columns": [
"a",
"b"
]
}
}
}
} |
--如果DISTINCT语句没有被优化掉,即DISTINCT语句被优化转换为GROUP BY操作或者利用UNIQUE INDEX消除DISTINCT, 内部临时表将会被使用。
mysql> explain format=json select distinct a from insideTable;
- 如果查询带有ORDER BY语句,并且不能被优化掉。下面几种情况会利用到内部临时表缓存中间数据,然后对中间数据进行排序。
- 如果连接表使用BNL(Batched Nestloop)/BKA(Batched Key Access)
- ORDER BY的列不属于执行计划中第一个连接表的列。
- 如果ORDER BY的表达式是个复杂表达式。
- ORDER BY的列包含聚集函数
- ORDER BY的列中包含有SCALAR SUBQUERY,当然该SCALAR SUBQUERY没有被优化掉
- 如果查询既带有ORDER BY同时也有GROUP BY语句,但是两个语句使用的列不相同
4. 创建磁盘临时表
5. 简单用例
- 当 MySQL 查询不存在的数据时,会自动构建虚拟表数据
- 测试,先建立一张空表
查看表结构:
desc 表名;
# 或者
show columns from 表名;
- 新增一笔数据
此时表中只有一笔 admin 数据,那么我们查询数据
参考链接:
mysql虚拟表
mysql中两种临时表