MySQL 自动构建虚拟表数据

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. 简单用例

1. 当 MySQL 查询不存在的数据时，会自动构建虚拟表数据
2. 测试，先建立一张空表
   
   查看表结构：

desc 表名;
# 或者
show columns from 表名;

3. 新增一笔数据
   
   此时表中只有一笔 admin 数据，那么我们查询数据
   
   参考链接：
   mysql虚拟表
   mysql中两种临时表