MySQL高阶讲座之：`MySQL`的`Temp Table`：`Disk Temp Table`与`Memory Temp Table`的性能分析。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，掌声欢迎来到今天的MySQL高阶讲座！今天咱不搞虚的，直奔主题——MySQL的临时表（Temp Table）。这玩意儿，用得好，性能蹭蹭往上涨；用不好，慢得让你怀疑人生。今天咱们就来扒一扒Disk Temp Table（磁盘临时表）和Memory Temp Table（内存临时表），看看它们到底有啥区别，以及在什么情况下该选谁。

开场白：临时表是个啥？

临时表，顾名思义，就是MySQL在执行查询过程中临时创建的表。它只在当前会话有效，会话结束后自动消失，就像灰姑娘的魔法一样。那MySQL为啥要搞这么个东西出来呢？

主要原因是为了优化查询。某些复杂的查询，比如涉及大量的JOIN、GROUP BY、ORDER BY等操作，直接在原表上操作效率很低。这时候，MySQL会把中间结果放到临时表里，然后再对临时表进行操作，从而提高整体性能。

临时表的两种类型：Disk vs. Memory

MySQL的临时表有两种类型：

Memory Temp Table (内存临时表): 存储在内存中，速度快，但受限于tmp_table_size和max_heap_table_size这两个参数的限制。
Disk Temp Table (磁盘临时表): 存储在磁盘上，速度慢，但不受内存限制，可以处理更大的数据量。

这两兄弟，一个速度快但容量小，一个速度慢但容量大，各有优劣。具体选择哪个，取决于你的查询需求和MySQL配置。

Memory Temp Table：快如闪电，奈何容量有限

Memory Temp Table，也叫HEAP表，是MySQL默认使用的临时表类型。它的优点是速度快，因为所有数据都存储在内存中，避免了磁盘IO。但是，它的缺点也很明显，就是容量有限。

限制条件：
- tmp_table_size: 限制单个会话所有临时表加起来的总大小。
- max_heap_table_size: 限制单个Memory Temp Table的最大大小。
如果临时表的大小超过了这两个参数的限制，MySQL会自动将Memory Temp Table转换为Disk Temp Table，这就是我们常说的“转磁盘”。
触发条件:
- 表大小超过tmp_table_size或max_heap_table_size。
- 包含BLOB或TEXT列。
- 包含超过max_length_for_sort_data长度的VARCHAR或VARBINARY列，并且使用了ORDER BY。
例子：

-- 先看看当前的tmp_table_size和max_heap_table_size
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

-- 创建一个简单的表
CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    age INT,
    department VARCHAR(255)
);

-- 插入一些数据
INSERT INTO employee (name, age, department) VALUES
('张三', 25, '研发部'),
('李四', 30, '市场部'),
('王五', 28, '研发部'),
('赵六', 35, '市场部'),
('孙七', 22, '测试部');

-- 使用GROUP BY，可能会用到临时表
EXPLAIN SELECT department, AVG(age) FROM employee GROUP BY department;

在这个例子中，如果employee表的数据量不大，MySQL很可能会使用Memory Temp Table来存储GROUP BY的中间结果。但是，如果数据量很大，超过了tmp_table_size或max_heap_table_size的限制，MySQL就会自动转磁盘。

Disk Temp Table：稳如老狗，速度感人

Disk Temp Table，就是存储在磁盘上的临时表。它的优点是容量大，不受内存限制，可以处理更大的数据量。但是，它的缺点也很明显，就是速度慢，因为涉及到磁盘IO。

触发条件：
- Memory Temp Table转磁盘。
- 使用了DISTINCT、GROUP BY、UNION等操作，并且数据量很大。
- 使用了ORDER BY，并且没有合适的索引。
- 包含了BLOB或TEXT列。
存储位置：

Disk Temp Table存储在tmpdir变量指定的目录下。可以使用SHOW VARIABLES LIKE 'tmpdir';来查看tmpdir的值。
例子：

-- 创建一个包含BLOB列的表
CREATE TABLE large_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data BLOB
);

-- 插入一些数据
INSERT INTO large_data (data) VALUES
(REPEAT('A', 1024000)); -- 插入1MB的数据
INSERT INTO large_data (data) VALUES
(REPEAT('B', 1024000)); -- 插入1MB的数据

-- 使用ORDER BY，并且没有合适的索引
EXPLAIN SELECT * FROM large_data ORDER BY data;

在这个例子中，由于large_data表包含了BLOB列，MySQL会直接使用Disk Temp Table来存储ORDER BY的结果。此外，由于data列没有索引，ORDER BY操作也会导致全表扫描，进一步加剧了磁盘IO。

如何判断MySQL使用了哪种类型的临时表？

要判断MySQL使用了哪种类型的临时表，可以使用EXPLAIN命令。EXPLAIN命令可以显示MySQL的查询执行计划，其中包括是否使用了临时表，以及临时表的类型。

EXPLAIN SELECT department, AVG(age) FROM employee GROUP BY department;
EXPLAIN SELECT * FROM large_data ORDER BY data;

在EXPLAIN的结果中，如果Extra列包含了Using temporary，则表示使用了临时表。如果Extra列还包含了Using filesort，则表示使用了文件排序（通常意味着使用了Disk Temp Table）。

性能优化：避免不必要的临时表

临时表虽然可以提高查询性能，但过度使用也会导致性能下降。因此，我们需要尽量避免不必要的临时表。

优化GROUP BY和ORDER BY:
- 尽量使用索引来避免全表扫描。
- 避免在GROUP BY和ORDER BY中使用复杂的表达式。
- 如果只需要部分列，尽量只选择需要的列，避免SELECT *。
优化JOIN:
- 使用合适的JOIN类型。
- 确保JOIN的列上有索引。
- 尽量避免在JOIN中使用子查询。
优化DISTINCT和UNION:
- 尽量使用索引来避免全表扫描。
- 避免在DISTINCT和UNION中使用复杂的表达式。
调整MySQL配置:
- 适当增加tmp_table_size和max_heap_table_size的值，以减少Memory Temp Table转磁盘的概率。
- 将tmpdir设置为一个高速磁盘（如SSD），以提高Disk Temp Table的性能。

案例分析：实战演练

假设我们有一个orders表，包含了订单信息：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

-- 插入一些数据
INSERT INTO orders (customer_id, order_date, amount) VALUES
(1, '2023-01-01', 100.00),
(1, '2023-01-02', 200.00),
(2, '2023-01-03', 300.00),
(2, '2023-01-04', 400.00),
(3, '2023-01-05', 500.00);

现在，我们需要查询每个客户的订单总金额：

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

如果orders表的数据量很大，这个查询可能会使用临时表。我们可以使用EXPLAIN命令来查看执行计划：

EXPLAIN SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

如果EXPLAIN的结果显示使用了临时表，我们可以尝试添加索引来优化查询：

CREATE INDEX idx_customer_id ON orders (customer_id);

添加索引后，再次执行EXPLAIN命令，看看是否还使用了临时表。如果仍然使用了临时表，可以尝试调整tmp_table_size和max_heap_table_size的值，看看是否可以避免转磁盘。

总结：临时表，用好是宝，用不好是草

总而言之，临时表是MySQL优化查询的重要工具，但需要谨慎使用。了解Memory Temp Table和Disk Temp Table的区别，以及如何避免不必要的临时表，是提高MySQL性能的关键。

特性	Memory Temp Table	Disk Temp Table
存储介质	内存	磁盘
速度	快	慢
容量	小 (受限于参数)	大 (受限于磁盘)
触发条件	超出内存限制，BLOB/TEXT列等	Memory Temp Table转磁盘等
适用场景	数据量小，需要快速处理	数据量大，内存不足

记住，优化是一个持续的过程，需要不断地分析和调整。希望今天的讲座对大家有所帮助！下课！

发表回复 取消回复

发表回复取消回复