mysql是怎样使用内部临时表的

我们仍使用 实验 05 中的环境，略去准备数据的过程。

创新互联公司长期为上千客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为叶县企业提供专业的成都做网站、网站建设，叶县网站改版等技术服务。拥有十余年丰富建站经验和众多成功案例,为您定制开发。

我们仍然使用两个会话，一个会话 run，用于运行主 SQL；另一个会话 ps，用于进行 performance_schema 的观察：

主会话线程号为 29，

将 performance_schema 中的统计量重置，

临时表的表大小限制取决于参数  tmp_table_size 和 max_heap_table_size 中较小者，我们实验中以设置 max_heap_table_size 为例。

我们将会话级别的临时表大小设置为 2M（小于上次实验中临时表使用的空间），执行使用临时表的 SQL：

查看内存的分配记录：

会发现内存分配略大于 2M，我们猜测临时表会比配置略多一点消耗，可以忽略。

查看语句的特征值：

可以看到语句使用了一次需要落磁盘的临时表。

那么这张临时表用了多少的磁盘呢？

我们开启 performance_schema 中 waits 相关的统计项：

重做实验，略过。

再查看 performance_schema 的统计值：

可以看到几个现象：

1. 临时表空间被写入了 7.92MiB 的数据。

2. 这些数据是语句写入后，慢慢逐渐写入的。

来看看这些写入操作的特征，该方法我们在 实验 03 使用过：

可以看到写入的线程是 page_clean_thread，是一个刷脏操作，这样就能理解数据为什么是慢慢写入的。

也可以看到每个 IO 操作的大小是 16K，也就是刷数据页的操作。

结论：

我们可以看到，

1. MySQL 会基本遵守 max_heap_table_size 的设定，在内存不够用时，直接将表转到磁盘上存储。

2. 由于引擎不同（内存中表引擎为 heap，磁盘中表引擎则跟随 internal_tmp_disk_storage_engine 的配置），本次实验写磁盘的数据量和 实验 05 中使用内存的数据量不同。

3. 如果临时表要使用磁盘，表引擎配置为 InnoDB，那么即使临时表在一个时间很短的 SQL 中使用，且使用后即释放，释放后也会刷脏页到磁盘中，消耗部分 IO。

MySQL如何使用临时表

解决办法：

当临时表不存在时，将查询结果保存在临时表中：

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name；

我的具体代码是

CREATE temporary table tmp

(SELECT t1.pid a, t1.pname b,t2.pid c, t2.pdescribe d FROM

(SELECT pid,pname FROM admin_advert_place WHERE pid IN (3,4,5,6,7)) t1 LEFT JOIN

(SELECT pid,pdescribe FROM admin_advert_place

WHERE pgid IN (6,7,8)) t2 ON t1.pid = t2.pid)

在mysql里怎么用临时表

MySQL 需要创建隐式临时表来解决某些类型的查询。往往查询的排序阶段需要依赖临时表。例如，当您使用 GROUP BY，ORDER BY 或DISTINCT 时。这样的查询分两个阶段执行：首先是收集数据并将它们放入临时表中，然后是在临时表上执行排序。

对于某些 UNION 语句，不能合并的 VIEW，子查询时用到派生表，多表 UPDATE 以及其他一些情况，还需要使用临时表。如果临时表很小，可以到内存中创建，否则它将在磁盘上创建。MySQL 在内存中创建了一个表，如果它变得太大，就会被转换为磁盘上存储。内存临时表的最大值由 tmp_table_size 或 max_heap_table_size 值定义，以较小者为准。MySQL 5.7 中的默认大小为 16MB。如果运行查询的数据量较大，或者尚未查询优化，则可以增加该值。设置阈值时，请考虑可用的 RAM 大小以及峰值期间的并发连接数。你无法无限期地增加变量，因为在某些时候你需要让 MySQL 使用磁盘上的临时表。

注意：如果涉及的表具有 TEXT 或 BLOB 列，则即使大小小于配置的阈值，也会在磁盘上创建临时表。

MYSQL存储引擎InnoDB（三十五）：临时表空间

InnoDB使用会话临时表空间和全局临时表空间。

在InnoDB配置为磁盘内部临时表的存储引擎时，会话临时表空间存储用户创建的临时表和优化器创建的内部临时表。从 MySQL 8.0.16 开始，用于磁盘内部临时表的存储引擎固定为InnoDB。（之前，存储引擎由internal_tmp_disk_storage_engine的值决定 ）

在第一次请求创建磁盘临时表时会话临时表空间从临时表空间池中被分配给会话。一个会话最多分配两个表空间，一个用于用户创建的临时表，另一个用于优化器创建的内部临时表。分配给会话的临时表空间用于会话创建的所有磁盘临时表。当会话断开连接时，其临时表空间将被截断并释放回池中。服务器启动时会创建一个包含 10 个临时表空间的池。池的大小永远不会缩小，并且表空间会根据需要自动添加到池中。临时表空间池在正常关闭或中止初始化时被删除。会话临时表空间文件在创建时大小为 5 页，并且具有.ibt文件扩展名。

InnoDB为会话临时表空间保留了40 万个空间 ID。因为每次启动服务器时都会重新创建会话临时表空间池，所以会话临时表空间的空间 ID 在服务器关闭时不会保留，并且可以重复使用。

innodb_temp_tablespaces_dir 变量定义了创建会话临时表空间的位置。默认位置是 #innodb_temp数据目录中的目录。如果无法创建临时表空间池，则会拒绝启动。

在基于语句的复制 (SBR) 模式下，在副本上创建的临时表驻留在单个会话临时表空间中，该临时表空间仅在 MySQL 服务器关闭时被截断。

INNODB_SESSION_TEMP_TABLESPACES 表提供有关会话临时表空间的元数据。

该INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO表提供有关在InnoDB实例中处于活动状态的用户创建的临时表的元数据。

全局临时表空间 ( ibtmp1) 存储对用户创建的临时表所做的更改的回滚段。

innodb_temp_data_file_path 变量定义了全局临时表空间数据文件的相对路径、名称、大小和属性。如果没有为innodb_temp_data_file_path指定值 ，则默认行为是创建innodb_data_home_dir目录中命名为ibtmp1的单个自动扩展数据文件。初始文件大小略大于 12MB。

全局临时表空间在正常关闭或中止初始化时被删除，并在每次服务器启动时重新创建。全局临时表空间在创建时会收到一个动态生成的空间 ID。如果无法创建全局临时表空间，则拒绝启动。如果服务器意外停止，则不会删除全局临时表空间。在这种情况下，数据库管理员可以手动删除全局临时表空间或重新启动 MySQL 服务器。重新启动 MySQL 服务器会自动删除并重新创建全局临时表空间。

全局临时表空间不能驻留在原始设备上。

INFORMATION_SCHEMA.FILES提供有关全局临时表空间的元数据。发出与此类似的查询以查看全局临时表空间元数据：

默认情况下，全局临时表空间数据文件会自动扩展并根据需要增加大小。

要确定全局临时表空间数据文件是否正在自动扩展，请检查以下 innodb_temp_data_file_path 设置：

要检查全局临时表空间数据文件的大小，请使用与此类似的查询来查询INFORMATION_SCHEMA.FILES表：

TotalSizeBytes显示全局临时表空间数据文件的当前大小。

或者，检查操作系统上的全局临时表空间数据文件大小。全局临时表空间数据文件位于 innodb_temp_data_file_path 变量定义的目录中。

要回收全局临时表空间数据文件占用的磁盘空间，请重新启动 MySQL 服务器。重新启动服务器会根据innodb_temp_data_file_path定义的属性删除并重新创建全局临时表空间数据文件 。

要限制全局临时表空间数据文件的大小，请配置 innodb_temp_data_file_path以指定最大文件大小。例如：

配置 innodb_temp_data_file_path 需要重新启动服务器。

MySQL中关于临时表的一些基本使用方法

临时表可能是非常有用的，在某些情况下，保持临时数据。最重要的是应该知道的临时表是，他们将当前的客户端会话终止时被删除。

临时表中添加MySQL版本3.23。如果您使用的是旧版本的MySQL比3.23，可以不使用临时表，但可以使用堆表。

如前所述临时表将只持续只要的会话是存在的。如果运行一个PHP脚本中的代码，该临时表将被销毁时，会自动执行完脚本后。如果已连接到MySQL数据库的服务器上，通过MySQL的客户端程序的临时表将一直存在，直到关闭客户端或手动破坏的表。

实例

下面是一个例子，使用临时表在PHP脚本中，使用mysql_query()函数，可以使用相同的代码。

mysql

CREATE

TEMPORARY

TABLE

SalesSummary

(

-

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

);

Query

OK,

rows

affected

(0.00

sec)

mysql

INSERT

INTO

SalesSummary

-

(product_name,

total_sales,

avg_unit_price,

total_units_sold)

-

VALUES

-

('cucumber',

100.25,

90,

2);

mysql

SELECT

*

FROM

SalesSummary;

+--------------+-------------+----------------+------------------+

|

product_name

|

total_sales

|

avg_unit_price

|

total_units_sold

|

+--------------+-------------+----------------+------------------+

|

cucumber

|

100.25

|

90.00

|

2

|

+--------------+-------------+----------------+------------------+

1

row

in

set

(0.00

sec)

当发出一个SHOW

TABLES命令，那么临时表将不会被列在列表中。现在如果将MySQL的会话的注销，那么会发出SELECT命令，那么会发现没有在数据库中的数据。即使临时表也就不存在了。

删除临时表：

默认情况下，所有的临时表被删除时，MySQL的数据库连接被终止。不过要删除他们之前就应该发出DROP

TABLE命令。

下面的例子为删除一个临时表。

mysql

CREATE

TEMPORARY

TABLE

SalesSummary

(

-

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

);

Query

OK,

rows

affected

(0.00

sec)

mysql

INSERT

INTO

SalesSummary

-

(product_name,

total_sales,

avg_unit_price,

total_units_sold)

-

VALUES

-

('cucumber',

100.25,

90,

2);

mysql

SELECT

*

FROM

SalesSummary;

+--------------+-------------+----------------+------------------+

|

product_name

|

total_sales

|

avg_unit_price

|

total_units_sold

|

+--------------+-------------+----------------+------------------+

|

cucumber

|

100.25

|

90.00

|

2

|

+--------------+-------------+----------------+------------------+

1

row

in

set

(0.00

sec)

mysql

DROP

TABLE

SalesSummary;

mysql

SELECT

*

FROM

SalesSummary;

ERROR

1146:

Table

'TUTORIALS.SalesSummary'

doesn't

exist