好的，下面是关于MySQL临时表的讲座文章：
MySQL临时表：Using temporary的产生原因与优化方法
大家好，今天我们来深入探讨MySQL优化器与执行计划中的一个重要概念：临时表。具体来说，我们会重点关注Using temporary这个在EXPLAIN结果中经常出现的提示，理解它的含义、产生原因，并学习如何优化它，提升查询性能。
1. 什么是临时表？
临时表，顾名思义，是在执行SQL查询的过程中，MySQL为了存储中间结果而创建的表。它存在于内存（MEMORY引擎）或磁盘（MyISAM或InnoDB引擎）上，并在查询结束后自动删除。临时表的存在对于复杂的查询至关重要，它允许MySQL分解问题，逐步处理数据，最终得到最终结果。
2. Using temporary的含义
当你在使用EXPLAIN命令分析SQL查询时，如果在Extra列中看到Using temporary，这意味着MySQL在执行该查询时创建了临时表。这通常表明查询的效率可能不高，因为创建和操作临时表会带来额外的开销，包括CPU时间、内存/磁盘空间以及I/O操作。
3. 临时表的创建时机与引擎选择
MySQL决定是否创建临时表取决于多个因素，包括查询的复杂性、数据量、索引的使用情况以及MySQL的配置。
临时表的引擎选择也至关重要。通常，MySQL会优先选择MEMORY引擎，因为它速度快。但是，MEMORY引擎有大小限制，如果临时表超过了这个限制，MySQL会自动将其转换为MyISAM引擎，存储在磁盘上。从MySQL 5.7.6开始，如果internal_tmp_disk_storage_engine变量被设置为InnoDB，则会将临时表存储在InnoDB引擎上。
你可以使用以下语句查看当前internal_tmp_disk_storage_engine的设置:
SHOW VARIABLES LIKE 'internal_tmp_disk_storage_engine';4. 常见Using temporary的产生原因
理解Using temporary出现的原因是优化查询的关键。以下是一些常见的原因：
-
ORDER BY和GROUP BY子句与索引不匹配当
ORDER BY或GROUP BY子句中使用的列没有合适的索引，或者索引的顺序与排序/分组的顺序不一致时，MySQL可能需要创建一个临时表来排序或分组数据。示例：
假设我们有一个
users表，包含id、name和age列。CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(255), age INT ); INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35), (4, 'David', 28), (5, 'Eve', 32);如果我们执行以下查询：
EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY age ORDER BY name;如果没有关于
age和name的联合索引，EXPLAIN 的结果中会显示Using temporary和Using filesort，因为 MySQL 需要创建一个临时表来分组数据，然后对结果进行排序。 -
DISTINCT操作当查询中使用
DISTINCT关键字，并且没有合适的索引支持时，MySQL可能需要创建一个临时表来存储唯一的值。示例：
EXPLAIN SELECT DISTINCT age FROM users;如果没有
age列上的索引，这个查询很可能触发Using temporary。 -
UNION操作UNION操作（特别是UNION DISTINCT）需要去除重复行，这通常需要创建一个临时表。UNION ALL则不需要去重，因此通常不会产生临时表。示例：
EXPLAIN SELECT age FROM users WHERE age > 30 UNION SELECT age FROM users WHERE age < 28;这个查询可能会产生临时表，因为需要合并结果集并去除重复项。
-
子查询结果集过大
如果子查询的结果集很大，MySQL可能会将其存储在临时表中，以便外部查询使用。
示例：
EXPLAIN SELECT * FROM users WHERE age IN (SELECT age FROM users WHERE age > 30);如果子查询
SELECT age FROM users WHERE age > 30返回大量数据，可能导致创建临时表。 -
多表连接
在复杂的连接查询中，MySQL可能需要创建临时表来存储中间结果，特别是当连接条件没有合适的索引支持时。
示例：
假设我们还有另一个表
orders：CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, order_date DATE ); INSERT INTO orders (id, user_id, order_date) VALUES (1, 1, '2023-01-01'), (2, 2, '2023-01-05'), (3, 1, '2023-01-10'), (4, 3, '2023-01-15');EXPLAIN SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name;如果没有
orders.user_id上的索引，或者连接的表非常大，这个查询可能需要临时表。 -
复杂的
WHERE条件复杂的
WHERE条件，特别是涉及多个OR运算符或IN列表时，可能会导致MySQL难以优化查询，从而创建临时表。
5. 优化Using temporary的方法
以下是一些优化Using temporary的常用方法：
-
添加合适的索引
这是最常见也是最有效的优化方法。确保
ORDER BY、GROUP BY、DISTINCT和连接条件中使用的列都有索引。索引的顺序也很重要，应该与排序/分组的顺序一致。示例：
针对上面的例子，我们可以添加以下索引：
ALTER TABLE users ADD INDEX idx_age_name (age, name); ALTER TABLE users ADD INDEX idx_age (age); ALTER TABLE orders ADD INDEX idx_user_id (user_id);添加索引后，再次执行
EXPLAIN命令，看看Using temporary是否消失。 -
优化SQL语句
重新审视你的SQL语句，看看是否有可以简化或重写的地方。例如，尽量避免在
WHERE子句中使用OR运算符，或者将复杂的WHERE条件分解为多个简单的条件。示例：
将以下查询：
SELECT * FROM users WHERE age = 30 OR age = 35;可以改写为：
SELECT * FROM users WHERE age IN (30, 35);或者，使用
UNION ALL代替UNION DISTINCT，如果不需要去除重复行。 -
增大
tmp_table_size和max_heap_table_size这两个参数控制了MEMORY引擎临时表的大小。如果临时表的大小超过了
tmp_table_size，MySQL会将其转换为MyISAM引擎，存储在磁盘上。增大这两个参数可以减少磁盘I/O，提高性能。 但是如果设置的过大，会消耗过多的内存。SET tmp_table_size = 67108864; -- 64MB SET max_heap_table_size = 67108864; -- 64MB注意：这些设置只对当前会话有效。要永久更改这些参数，需要在MySQL配置文件中进行设置。
-
避免在
WHERE子句中使用函数在
WHERE子句中使用函数会导致索引失效，从而可能导致创建临时表。尽量将函数操作移到查询之外。示例：
避免使用：
SELECT * FROM users WHERE YEAR(birthdate) = 1990;可以考虑创建一个基于年份的计算列，并对其建立索引。
-
使用
STRAIGHT_JOIN强制连接顺序在某些情况下，MySQL的优化器可能会选择错误的连接顺序，导致创建临时表。可以使用
STRAIGHT_JOIN关键字强制MySQL按照指定的顺序连接表。但请谨慎使用，因为强制连接顺序可能会导致其他性能问题。 确保你知道你在做什么。示例：
SELECT * FROM table1 STRAIGHT_JOIN table2 ON table1.id = table2.table1_id; -
使用覆盖索引
如果查询只需要索引中的列，那么使用覆盖索引可以避免回表查询，从而提高性能。
**示例：**
如果经常需要根据`name` 查询 `id`，可以建立一个覆盖索引：
```sql
ALTER TABLE users ADD INDEX idx_name_id (name, id);
```
然后执行查询：
```sql
SELECT id FROM users WHERE name = 'Alice';
```-
分析慢查询日志
开启MySQL的慢查询日志，可以帮助你找到执行时间长的查询，这些查询很可能需要优化。分析慢查询日志，找到产生临时表的查询，然后针对性地进行优化。
-
升级MySQL版本
新版本的MySQL通常包含优化器改进和性能提升，升级到最新版本可能会自动解决一些临时表问题。
6. 案例分析
假设我们有一个电商网站，包含products和categories表。
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(255),
category_id INT,
price DECIMAL(10, 2)
);
CREATE TABLE categories (
id INT PRIMARY KEY,
name VARCHAR(255)
);
INSERT INTO categories (id, name) VALUES
(1, 'Electronics'),
(2, 'Clothing'),
(3, 'Books');
INSERT INTO products (id, name, category_id, price) VALUES
(1, 'Laptop', 1, 1200.00),
(2, 'T-Shirt', 2, 25.00),
(3, 'Harry Potter', 3, 15.00),
(4, 'Smartphone', 1, 800.00),
(5, 'Jeans', 2, 50.00);我们想要查询每个分类下最贵的产品名称和价格。
SELECT c.name AS category_name, p.name AS product_name, p.price
FROM categories c
JOIN products p ON c.id = p.category_id
WHERE (p.category_id, p.price) IN (
SELECT category_id, MAX(price)
FROM products
GROUP BY category_id
)
ORDER BY c.name;这个查询可能会产生Using temporary，因为子查询GROUP BY和ORDER BY都可能需要临时表。
优化步骤：
-
添加索引： 在
products.category_id和products.price上添加索引。ALTER TABLE products ADD INDEX idx_category_id (category_id); ALTER TABLE products ADD INDEX idx_price (price); -
重写查询： 使用
JOIN和GROUP BY代替IN子查询。SELECT c.name AS category_name, p.name AS product_name, p.price FROM categories c JOIN ( SELECT category_id, MAX(price) AS max_price FROM products GROUP BY category_id ) AS max_prices ON c.id = max_prices.category_id JOIN products p ON max_prices.category_id = p.category_id AND max_prices.max_price = p.price ORDER BY c.name;或者使用窗口函数
SELECT c.name AS category_name, p.name AS product_name, p.price FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as rn FROM products ) p JOIN categories c ON c.id = p.category_id WHERE p.rn = 1 ORDER BY c.name; -
分析执行计划： 使用
EXPLAIN命令分析优化后的查询，确保Using temporary已经消失，并且查询使用了索引。
7. 注意事项
- 优化是一个迭代的过程，需要不断地分析和调整。
- 不同的查询场景需要不同的优化策略。
- 在生产环境中进行任何优化之前，务必进行充分的测试。
Using temporary并非总是坏事，有时候它是MySQL为了执行复杂查询所必需的。关键是要理解它的含义，并判断是否可以进行优化。- 优化是一个trade-off 的过程，有些优化可能会提高某些查询的性能，但同时降低其他查询的性能。
总结
优化 Using temporary 的关键在于理解其产生的根本原因，并针对性地采取措施。 索引优化通常是最有效的手段，但SQL语句的重写、参数调整以及硬件升级等方法同样不可忽视。 持续地监控和分析查询性能，可以帮助你及时发现和解决临时表问题，从而提升MySQL数据库的整体性能。