MySQL的临时表：内部实现机制、内存与磁盘使用，以及如何优化避免生成？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 临时表：实现机制、资源使用与优化策略

大家好，今天我们来深入探讨 MySQL 临时表，一个在查询执行过程中经常被创建和销毁，但又容易被忽视的关键组成部分。我们将从临时表的内部实现机制入手，分析其内存与磁盘的使用情况，最后探讨如何优化查询，避免不必要的临时表生成，从而提升数据库性能。

一、临时表的内部实现机制

MySQL 在执行复杂的查询时，为了存储中间结果集，可能会创建临时表。这些临时表只在当前连接的生命周期内有效，连接断开后会自动被删除。MySQL 临时表有两种类型：

内存临时表（Memory Temporary Table）： 使用 MEMORY 存储引擎，数据存储在内存中，速度快，但受限于 tmp_table_size 和 max_heap_table_size 系统变量的限制。
磁盘临时表（MyISAM Temporary Table）： 使用 MyISAM 存储引擎，数据存储在磁盘上，不受内存限制，但速度较慢。

MySQL 会尽量使用内存临时表，如果内存临时表的大小超过了 tmp_table_size 或 max_heap_table_size，则会自动转换为磁盘临时表。

临时表的创建时机：

临时表的创建通常发生在以下几种情况下：

ORDER BY 和 GROUP BY 子句： 当 ORDER BY 或 GROUP BY 子句无法通过索引优化时，MySQL 会创建临时表来排序或分组结果。
UNION 和 UNION ALL 子句： UNION 操作需要去重，MySQL 会创建临时表来存储结果并去重。
复杂子查询： 某些复杂子查询的结果需要存储在临时表中，供外部查询使用。
DISTINCT 操作： 当 DISTINCT 操作无法通过索引优化时，MySQL 会创建临时表来去重结果。
多表连接： 在某些情况下，MySQL 会创建临时表来存储连接的中间结果。

示例：ORDER BY 创建临时表

假设我们有以下 employees 表：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    department VARCHAR(255),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 50000.00),
('Bob', 'Marketing', 60000.00),
('Charlie', 'Sales', 55000.00),
('David', 'Engineering', 70000.00),
('Eve', 'Marketing', 62000.00);

执行以下查询：

EXPLAIN SELECT * FROM employees ORDER BY salary DESC;

如果 salary 列没有索引，EXPLAIN 的输出会显示 Using filesort，这意味着 MySQL 需要创建临时表来排序结果。

示例：UNION 创建临时表

EXPLAIN SELECT name FROM employees WHERE department = 'Sales'
UNION
SELECT name FROM employees WHERE department = 'Marketing';

UNION 操作需要去重，因此 MySQL 会创建临时表来存储结果并去重。

二、内存与磁盘的使用情况

临时表的资源消耗主要体现在内存和磁盘 I/O 上。

内存临时表：

优点： 速度快，因为数据存储在内存中，读写速度远高于磁盘。
缺点： 受限于 tmp_table_size 和 max_heap_table_size 系统变量的限制。如果临时表的大小超过了这些限制，MySQL 会自动将其转换为磁盘临时表。
配置：
- tmp_table_size：控制单个会话中所有临时表的大小总和。
- max_heap_table_size：控制 MEMORY 存储引擎的最大大小，也影响内存临时表的大小。
查看内存临时表使用情况:

可以使用performance schema查看:

-- 开启performance schema (如果未开启)
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'wait/io/table/sql/%';
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE '%events_waits_summary_global_by_event_name%';

-- 查询临时表相关的等待事件
SELECT
    event_name,
    COUNT(*) AS event_count,
    SUM(timer_wait) AS total_wait_time,
    AVG(timer_wait) AS avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE 'wait/io/table/sql/%'
GROUP BY event_name
ORDER BY total_wait_time DESC;

-- 确认是否因为临时表导致性能问题
-- 如果发现大量的等待事件与临时表相关，则说明需要优化查询，避免临时表的生成。

磁盘临时表：

优点： 不受内存限制，可以存储大量数据。
缺点： 速度慢，因为数据存储在磁盘上，读写速度远低于内存。
配置：
- tmpdir：指定临时文件的存储目录。建议将 tmpdir 设置在独立的磁盘上，以避免与其他数据文件竞争 I/O 资源。
查看磁盘临时表使用情况：

可以使用 SHOW GLOBAL STATUS 命令查看与临时文件相关的状态变量：

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

Created_tmp_disk_tables 表示创建的磁盘临时表的数量，Created_tmp_tables 表示创建的临时表的总数（包括内存临时表和磁盘临时表）。如果 Created_tmp_disk_tables 的值很高，则说明需要优化查询，避免生成磁盘临时表。

资源消耗对比：

特性	内存临时表 (MEMORY)	磁盘临时表 (MyISAM)
存储介质	内存	磁盘
速度	快	慢
大小限制	受限于 `tmp_table_size` 和 `max_heap_table_size`	无限制
I/O 消耗	低	高
CPU 消耗	低	高

示例：临时表大小超过限制

假设 tmp_table_size 设置为 16MB，max_heap_table_size 设置为 16MB。如果一个查询需要创建一个大于 16MB 的临时表，MySQL 会自动将其转换为磁盘临时表。

三、优化策略：避免生成临时表

优化查询，避免生成不必要的临时表是提升数据库性能的关键。以下是一些常用的优化策略：

使用索引：
- 为 ORDER BY、GROUP BY 和 WHERE 子句中的列创建索引，可以避免 MySQL 创建临时表来排序、分组或过滤数据。
- 索引可以显著提高查询速度，减少资源消耗。
示例：ORDER BY 索引优化
```
-- 创建 salary 列的索引
CREATE INDEX idx_salary ON employees (salary);

EXPLAIN SELECT * FROM employees ORDER BY salary DESC;
```
现在 EXPLAIN 的输出应该显示 Using index，这意味着 MySQL 可以使用索引来排序结果，而无需创建临时表。
优化 GROUP BY 子句：
- 尽量使用覆盖索引来满足 GROUP BY 子句，避免回表查询。
- 使用 SQL_BIG_RESULT 或 SQL_SMALL_RESULT 提示 MySQL 使用哪种分组策略。
- 如果不需要排序，可以使用 ORDER BY NULL 禁用排序操作，避免创建临时表。
示例：GROUP BY 索引优化
```
-- 创建 department 和 salary 的复合索引
CREATE INDEX idx_department_salary ON employees (department, salary);

EXPLAIN SELECT department, AVG(salary) FROM employees GROUP BY department;
```
如果 department 列有索引，EXPLAIN 的输出应该显示 Using index for group-by，这意味着 MySQL 可以使用索引来分组数据，而无需创建临时表。

示例：禁用排序
```
EXPLAIN SELECT department, AVG(salary) FROM employees GROUP BY department ORDER BY NULL;
```
ORDER BY NULL 告诉 MySQL 不需要排序结果，可以避免创建临时表。
优化 UNION 子句：
- 尽量使用 UNION ALL 代替 UNION，因为 UNION ALL 不会去重，可以避免创建临时表。
- 如果必须使用 UNION，确保每个 SELECT 子句都使用了索引，以提高查询效率。
示例：使用 UNION ALL
```
EXPLAIN SELECT name FROM employees WHERE department = 'Sales'
UNION ALL
SELECT name FROM employees WHERE department = 'Marketing';
```
UNION ALL 不会去重，因此 MySQL 不需要创建临时表。

优化子查询：

尽量将子查询转换为 JOIN 操作，可以避免创建临时表。
使用 EXISTS 代替 IN，可以提高查询效率。

示例：子查询转换为 JOIN

-- 子查询
EXPLAIN SELECT * FROM employees WHERE department IN (SELECT department FROM departments WHERE location = 'New York');

-- 转换为 JOIN
EXPLAIN SELECT e.* FROM employees e JOIN departments d ON e.department = d.department WHERE d.location = 'New York';

JOIN 操作通常比子查询更有效率，可以避免创建临时表。

避免使用 DISTINCT：
- 尽量避免使用 DISTINCT，因为 DISTINCT 操作需要去重，可能会创建临时表。
- 如果必须使用 DISTINCT，确保查询使用了索引，以提高去重效率。
调整MySQL配置:
- 适当增加tmp_table_size和max_heap_table_size，允许更多操作在内存中完成，避免转化为磁盘临时表。但是，需要根据服务器的内存资源合理配置，避免过度消耗内存。
- 确保tmpdir指向一个具有足够空间且I/O性能良好的磁盘，以提高磁盘临时表的效率。
代码层面优化:
- 分批处理：如果需要处理大量数据，可以考虑分批处理，每次处理一部分数据，避免创建过大的临时表。
- 结果集限制：使用 LIMIT 限制结果集的大小，减少需要处理的数据量。

总结表格：优化策略与效果

优化策略	效果	适用场景
使用索引	避免创建临时表来排序、分组或过滤数据，提高查询速度，减少资源消耗。	`ORDER BY`、`GROUP BY` 和 `WHERE` 子句中使用了没有索引的列。
优化 `GROUP BY`	避免回表查询，使用覆盖索引，禁用排序操作，减少临时表的创建。	需要使用 `GROUP BY` 子句进行分组操作。
优化 `UNION`	使用 `UNION ALL` 代替 `UNION`，避免去重操作，减少临时表的创建。	需要使用 `UNION` 或 `UNION ALL` 子句合并多个结果集。
优化子查询	将子查询转换为 `JOIN` 操作，避免创建临时表，提高查询效率。	使用了子查询。
避免使用 `DISTINCT`	减少去重操作，避免创建临时表。	需要使用 `DISTINCT` 子句去重结果集。
调整MySQL配置	允许更多操作在内存中完成，提高磁盘临时表的效率。	内存资源充足，需要处理大量数据。
代码层面优化	分批处理，限制结果集大小，减少需要处理的数据量。	需要处理大量数据，或者结果集过大。

四、案例分析：一个复杂的查询优化过程

假设我们有以下两个表：

orders：订单表，包含订单ID、客户ID、订单日期、订单金额等信息。
customers：客户表，包含客户ID、客户姓名、客户地址等信息。

我们有一个复杂的查询，需要统计每个客户的订单总金额，并按照订单总金额排序：

SELECT
    c.customer_id,
    c.customer_name,
    SUM(o.order_amount) AS total_amount
FROM
    customers c
JOIN
    orders o ON c.customer_id = o.customer_id
WHERE
    o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
    c.customer_id, c.customer_name
ORDER BY
    total_amount DESC;

优化步骤：

分析 EXPLAIN 输出：

首先，我们使用 EXPLAIN 分析查询的执行计划，发现 GROUP BY 和 ORDER BY 子句都使用了 Using filesort，这意味着 MySQL 需要创建临时表来排序和分组数据。

创建索引：

为了避免创建临时表，我们为 orders 表的 customer_id 和 order_date 列创建索引，为 customers 表的 customer_id 列创建索引：

CREATE INDEX idx_customer_id ON orders (customer_id);
CREATE INDEX idx_order_date ON orders (order_date);
CREATE INDEX idx_customer_id ON customers (customer_id);

再次分析 EXPLAIN 输出：

创建索引后，我们再次使用 EXPLAIN 分析查询的执行计划，发现 GROUP BY 子句仍然使用了 Using filesort。这是因为 GROUP BY 子句包含了 customer_name 列，而 customer_name 列没有索引。
优化 GROUP BY 子句：

为了优化 GROUP BY 子句，我们可以创建一个包含 customer_id 和 customer_name 列的复合索引：
```
CREATE INDEX idx_customer_id_name ON customers (customer_id, customer_name);
```
再次分析 EXPLAIN 输出：

创建复合索引后，我们再次使用 EXPLAIN 分析查询的执行计划，发现 GROUP BY 子句已经使用了 Using index for group-by，这意味着 MySQL 可以使用索引来分组数据，而无需创建临时表。
优化 ORDER BY 子句：

ORDER BY 子句仍然使用了 Using filesort，这是因为 ORDER BY 子句按照 total_amount 排序，而 total_amount 是一个计算列，没有索引。为了优化 ORDER BY 子句，我们可以创建一个包含 customer_id 和 order_amount 列的复合索引，并使用 SQL_BIG_RESULT 提示 MySQL 使用哪种排序策略：
```
CREATE INDEX idx_customer_id_amount ON orders (customer_id, order_amount);
SELECT SQL_BIG_RESULT
    c.customer_id,
    c.customer_name,
    SUM(o.order_amount) AS total_amount
FROM
    customers c
JOIN
    orders o ON c.customer_id = o.customer_id
WHERE
    o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
    c.customer_id, c.customer_name
ORDER BY
    total_amount DESC;
```
但这种方法通常不可行，因为计算列无法直接创建索引。更好的方法是在代码层面进行排序，或者创建一个物化视图（Materialized View）来存储计算列，并为物化视图创建索引。
最终优化：

经过以上优化，我们成功避免了创建临时表来排序和分组数据，显著提高了查询性能。

五、结论：持续优化，提升性能

MySQL 临时表是查询执行过程中不可避免的组成部分，但通过合理的优化策略，我们可以减少临时表的生成，提高数据库性能。关键在于理解临时表的创建时机和资源消耗，并根据实际情况选择合适的优化方法。持续分析查询执行计划，调整索引和查询语句，是提升数据库性能的长期任务。

理解临时表原理，合理运用优化策略。