MySQL的临时表：内部实现机制、磁盘与内存使用，以及如何通过JOIN优化避免临时表的生成？

MySQL 临时表：深入解析与优化

大家好，今天我们来深入探讨 MySQL 临时表这个话题。临时表在 MySQL 的查询执行过程中扮演着重要的角色，但如果不了解其内部机制和优化策略，很容易导致性能瓶颈。本次讲座将从临时表的内部实现机制、磁盘与内存的使用情况，以及如何通过优化 JOIN 操作来避免临时表的生成三个方面进行详细讲解。

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

MySQL 在执行复杂查询时，为了存储中间结果，可能会创建临时表。临时表可以分为两种类型：内存临时表和磁盘临时表。

• 内存临时表 (MEMORY/HEAP table): 存储在内存中，速度快，但受限于 tmp_table_size 和 max_heap_table_size 这两个系统变量的限制。 如果临时表的大小超过了这两个变量的最小值，MySQL 会自动将其转换为磁盘临时表。

• 磁盘临时表 (MyISAM/InnoDB table): 存储在磁盘上，不受内存大小限制，但速度较慢。

当MySQL需要创建一个临时表时，它会首先尝试使用内存临时表。如果内存临时表无法满足需求（例如，数据量过大），MySQL会自动将其转换为磁盘临时表。这个转换过程会导致性能下降，因为磁盘 I/O 的速度远慢于内存访问。

创建临时表的原因：

临时表通常在以下场景中创建：

1. ORDER BY 和 GROUP BY 组合： 当 ORDER BY 子句和 GROUP BY 子句使用的列不同时，MySQL 可能会使用临时表进行排序。

2. UNION 和 UNION ALL： UNION 操作需要去除重复行，这通常需要使用临时表。 UNION ALL 则不需要，因为它保留所有行，通常避免创建临时表。

3. 子查询： 某些类型的子查询，特别是那些返回多行的子查询，可能会导致临时表的创建。

4. DISTINCT： 当 DISTINCT 作用于多个列时，MySQL 可能会使用临时表来去重。

5. JOIN 操作： 复杂的 JOIN 操作，特别是当使用 LEFT JOIN 或 RIGHT JOIN 并且 ON 条件没有有效利用索引时，可能会导致临时表的创建。

临时表的生命周期：

临时表的生命周期很短，通常只在单个查询执行期间存在。查询执行完成后，临时表会被自动删除。

二、磁盘与内存使用

理解 MySQL 如何使用磁盘和内存来存储临时表至关重要。

1. 系统变量的影响：

系统变量	描述
tmp_table_size	内存临时表的最大大小。如果临时表超过这个大小，MySQL 会尝试将其转换为磁盘临时表。
max_heap_table_size	MEMORY 存储引擎创建的表的最大大小。这也会影响内存临时表的大小，因为 MySQL 使用 MEMORY 存储引擎来实现内存临时表。
tmpdir	用于存储磁盘临时表的目录。可以指定多个目录，MySQL 会轮流使用它们。如果磁盘空间不足，可能会导致查询失败。
internal_tmp_disk_storage_engine	用于存储磁盘临时表的存储引擎。 默认是default_tmp_storage_engine，而default_tmp_storage_engine默认是InnoDB，早期版本可能是MyISAM。InnoDB临时表在查询结束后会被删除，不占用undo空间，也避免了MyISAM临时表删除时的慢查询问题。

2. 内存临时表转换为磁盘临时表的标志：

通过查看 EXPLAIN 输出，可以判断是否使用了临时表以及临时表的类型。EXPLAIN 输出中的 Extra 列如果包含 Using temporary，表示使用了临时表。如果 Extra 列还包含 Using filesort，则表示使用了文件排序，这通常与临时表一起出现。

3. 如何查看临时表的大小：

MySQL 并没有直接提供查看临时表大小的命令。但是，可以通过以下方法进行估算：

• 观察 SHOW GLOBAL STATUS LIKE 'Created_tmp%'; 的结果: Created_tmp_disk_tables 变量记录了创建磁盘临时表的次数， Created_tmp_tables 记录了创建临时表的总次数（包括内存和磁盘）。

  SHOW GLOBAL STATUS LIKE 'Created_tmp%';

• 分析 EXPLAIN 输出： 虽然 EXPLAIN 不会直接显示临时表的大小，但它可以帮助你识别可能导致临时表创建的查询部分。

4. 磁盘I/O的影响：

磁盘临时表会显著增加磁盘 I/O，特别是当临时表很大时。这会导致查询性能下降，并可能影响整个系统的性能。因此，尽量避免创建磁盘临时表是优化查询的关键。

三、通过 JOIN 优化避免临时表的生成

JOIN 操作是导致临时表创建的常见原因之一。通过优化 JOIN 操作，可以有效地避免临时表的生成，从而提高查询性能。

1. 确保 JOIN 列上有索引：

这是最基本的优化措施。如果 JOIN 列没有索引，MySQL 将不得不进行全表扫描，这会导致性能下降，并可能导致临时表的创建。

-- 假设有两个表：orders 和 customers
-- orders 表包含 customer_id 列，用于关联 customers 表
-- 确保 orders.customer_id 和 customers.id 上都有索引
CREATE INDEX idx_customer_id ON orders (customer_id);
CREATE INDEX idx_id ON customers (id);

2. 使用 STRAIGHT_JOIN 强制连接顺序：

STRAIGHT_JOIN 强制 MySQL 按照 FROM 子句中表的顺序进行连接。这在某些情况下可以避免优化器选择错误的连接顺序，从而避免临时表的创建。但是，需要谨慎使用 STRAIGHT_JOIN，因为它可能会阻止优化器进行其他优化。

SELECT STRAIGHT_JOIN o.*, c.*
FROM orders o
JOIN customers c ON o.customer_id = c.id;

注意： 在MySQL 8.0.18版本之后，STRAIGHT_JOIN 在某些情况下可能被忽略。 建议使用优化器提示(optimizer hint)来达到同样的效果。 例如：

SELECT /*+ ORDERED */ o.*, c.*
FROM orders o
JOIN customers c ON o.customer_id = c.id;

/*+ ORDERED */ 提示告诉优化器按照表在查询中出现的顺序连接表。

3. 避免在 ON 子句中使用函数或表达式：

在 ON 子句中使用函数或表达式会阻止 MySQL 使用索引，从而导致全表扫描和临时表的创建。

-- 避免这种写法
SELECT *
FROM orders o
JOIN customers c ON YEAR(o.order_date) = YEAR(c.registration_date);

-- 应该尽量避免在join on 条件中使用函数。

4. 尽量使用 INNER JOIN：

INNER JOIN 只需要返回匹配的行，而 LEFT JOIN 和 RIGHT JOIN 需要返回所有左表或右表的行，即使没有匹配的行。这使得 INNER JOIN 更容易进行优化，并且通常可以避免临时表的创建。 如果可以使用 INNER JOIN 替代 LEFT JOIN 或 RIGHT JOIN，则应该优先选择 INNER JOIN。

5. 优化子查询：

如果查询包含子查询，可以尝试将子查询转换为 JOIN 操作。 这通常可以提高查询性能，并避免临时表的创建。

-- 原始查询（包含子查询）
SELECT *
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE city = 'New York');

-- 优化后的查询（使用 JOIN）
SELECT o.*
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.city = 'New York';

6. 使用 EXISTS 替代 DISTINCT：

当需要去重时，可以尝试使用 EXISTS 替代 DISTINCT。 在某些情况下，EXISTS 可以避免临时表的创建。

-- 原始查询（使用 DISTINCT）
SELECT DISTINCT customer_id
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

-- 优化后的查询（使用 EXISTS）
SELECT customer_id
FROM orders o
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
AND EXISTS (SELECT 1 FROM orders o2 WHERE o2.customer_id = o.customer_id LIMIT 1);

7. 减少返回的列：

只选择需要的列，避免使用 SELECT *。 这可以减少临时表的大小，并提高查询性能。

8. 合理设置 tmp_table_size 和 max_heap_table_size：

根据服务器的内存大小和查询的复杂程度，合理设置 tmp_table_size 和 max_heap_table_size。 如果内存足够，可以适当增加这两个变量的值，以减少磁盘临时表的创建。 但是，需要注意不要将这两个变量设置得过大，以免占用过多的内存。

9. 使用覆盖索引：

覆盖索引是指索引包含了查询所需的所有列。 如果查询可以使用覆盖索引，MySQL 将不需要访问表数据，从而可以避免临时表的创建。

10. 分析慢查询日志：

定期分析慢查询日志，找出执行时间长的查询，并针对这些查询进行优化。 慢查询通常是导致临时表创建的原因之一。

代码示例：

假设有两张表：products 和 categories，它们通过 category_id 关联。

CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category_id INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 插入一些示例数据
INSERT INTO categories (name) VALUES ('Electronics'), ('Clothing'), ('Books');

INSERT INTO products (name, category_id, price) VALUES
('Laptop', 1, 1200.00),
('T-Shirt', 2, 25.00),
('The Lord of the Rings', 3, 15.00),
('Smartphone', 1, 800.00),
('Jeans', 2, 50.00);

现在，考虑以下查询：

SELECT p.name, c.name AS category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
ORDER BY c.name;

如果 categories.name 上没有索引，MySQL 可能会创建临时表来排序结果。 为了避免这种情况，可以添加索引：

CREATE INDEX idx_category_name ON categories (name);

此外，如果只需要 products.name 和 categories.name 这两列，可以使用覆盖索引：

CREATE INDEX idx_category_name_id ON categories (name, id);  -- 创建包含 name 和 id 的组合索引

-- 修改查询，只选择需要的列
SELECT p.name, c.name AS category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
ORDER BY c.name;

在这个例子中，idx_category_name_id 覆盖了查询所需的所有列，因此 MySQL 可以直接从索引中获取数据，而不需要访问表数据，从而避免了临时表的创建。

表格总结优化技巧

优化方法	描述	适用场景
在 JOIN 列上创建索引	确保 JOIN 操作使用的列上有索引，避免全表扫描。	所有 JOIN 操作
使用 STRAIGHT_JOIN 或 优化器提示强制连接顺序	强制 MySQL 按照指定的顺序进行连接，避免优化器选择错误的连接顺序。	当优化器选择的连接顺序不佳时
避免在 ON 子句中使用函数或表达式	避免在 ON 子句中使用函数或表达式，以便 MySQL 可以使用索引。	所有 JOIN 操作
尽量使用 INNER JOIN	使用 INNER JOIN 替代 LEFT JOIN 或 RIGHT JOIN，如果可行的话。	当可以使用 INNER JOIN 替代 LEFT JOIN 或 RIGHT JOIN 时
优化子查询	将子查询转换为 JOIN 操作，提高查询性能。	包含子查询的查询
使用 EXISTS 替代 DISTINCT	当需要去重时，可以尝试使用 EXISTS 替代 DISTINCT。	需要去重的查询
减少返回的列	只选择需要的列，避免使用 SELECT *。	所有查询
合理设置 tmp_table_size 和 max_heap_table_size	根据服务器的内存大小和查询的复杂程度，合理设置这两个变量的值。	所有查询
使用覆盖索引	创建包含查询所需的所有列的索引，避免访问表数据。	当查询只需要访问索引中的列时
分析慢查询日志	定期分析慢查询日志，找出执行时间长的查询，并针对这些查询进行优化。	所有查询

四. 关于临时表的一些建议

临时表是优化器根据查询语句自动选择的，我们能做的就是通过修改查询语句来影响优化器的选择。

• 理解查询计划： 使用 EXPLAIN 分析查询计划，了解 MySQL 如何执行查询，并找出可能导致临时表创建的部分。
• 监控服务器状态： 监控服务器的 CPU 使用率、磁盘 I/O 和内存使用情况，以便及时发现性能瓶颈。
• 定期维护数据库： 定期进行数据库维护，例如优化表、更新索引统计信息，以提高查询性能。
• 测试和验证： 在生产环境部署优化后的查询之前，务必在测试环境中进行充分的测试和验证，以确保优化不会引入新的问题。

查询优化是一个持续的过程

通过理解临时表的内部机制、磁盘与内存的使用情况，以及如何通过优化 JOIN 操作来避免临时表的生成，我们可以更有效地优化 MySQL 查询，提高数据库性能。记住，查询优化是一个持续的过程，需要不断地学习和实践。希望今天的讲座对大家有所帮助。

一些思考点

• 优化器在选择临时表时的成本考量。
• 不同存储引擎对临时表性能的影响。
• 如何使用性能分析工具来诊断临时表相关的性能问题。

谢谢大家！