书籍 / MySQL 性能优化完全指南
第 18 章

临时表与内存表

MySQL 临时表与内存表优化

临时表和基于内存的存储启用高效的中间操作。理解它们的权衡对于查询优化至关重要。

1. 临时存储类型


自动临时表：

MySQL为以下操作创建临时表：
├─ GROUP BY无序
├─ DISTINCT非索引列
├─ 复杂JOIN的中间结果

监控：
SHOW STATUS LIKE 'Created_tmp_tables';
SHOW STATUS LIKE 'Created_tmp_disk_tables';

如果 Created_tmp_disk_tables 很高：
└─ 问题：磁盘I/O比内存慢得多

解决方案：
SET GLOBAL tmp_table_size = 512M;
SET GLOBAL max_heap_table_size = 512M;

显式临时表：

CREATE TEMPORARY TABLE temp_results (
  id INT,
  name VARCHAR(100),
  total DECIMAL(10,2)
);

特点：
├─ 仅对创建会话可见
├─ 连接关闭时自动删除
├─ 可以有索引、约束等
└─ 可以使用任何存储引擎

2. MEMORY vs InnoDB


MEMORY引擎：

CREATE TABLE mem_table (
  id INT PRIMARY KEY,
  name VARCHAR(100)
) ENGINE = MEMORY;

特点：
├─ 整个表在RAM中
├─ 哈希索引（O(1)查询）
├─ 重启时丢失
├─ 受限于 max_heap_table_size

性能：
├─ 读：非常快（内存访问）
├─ 写：快但单线程
├─ 大小限制：可能溢出到磁盘

InnoDB with缓冲池：

特点：
├─ 持久化到磁盘
├─ ACID事务
├─ 行级锁定

性能：
├─ 读：缓冲池中快，磁盘上慢
├─ 写：中等（重做日志、事务开销）
└─ 可以超过内存大小

选择：

用MEMORY表示：
├─ 小参考表（<100MB）
├─ 会话状态/缓存
└─ 临时聚合（保证能拟合）

用InnoDB表示：
├─ 大临时结果
├─ 复杂多步查询
└─ 需要ACID保证

3. 调优临时表性能


问题：查询创建巨大磁盘临时表

监控：
SHOW STATUS LIKE 'Created_tmp_disk_tables';
-- 结果高：1, 000, 000（很糟！）

解决方案1：增加内存限制

SET GLOBAL tmp_table_size = 2G;
SET GLOBAL max_heap_table_size = 2G;

解决方案2：优化查询避免临时表

❌ 慢（创建临时表）：
SELECT DISTINCT country FROM users;

✓ 快（使用索引）：
CREATE INDEX idx_country ON users(country);
SELECT DISTINCT country FROM users;

解决方案3：显式临时表+索引

CREATE TEMPORARY TABLE temp_users AS
SELECT * FROM users WHERE age > 18;
CREATE INDEX idx_country ON temp_users(country);
SELECT DISTINCT country FROM temp_users;

解决方案4：批处理

✓ 分批处理（较小临时表）：
FOR i = 0 TO 25 DO
  SELECT name FROM users
  WHERE name LIKE CONCAT(CHAR(65+i), '%');
END FOR

4. 常见用例


去重：

✓ 快速方法：
CREATE TEMPORARY TABLE temp_dedup AS
SELECT MIN(id) as keep_id FROM events GROUP BY event_key;
DELETE FROM events WHERE id NOT IN (SELECT keep_id FROM temp_dedup);

复杂ETL：

提取 → 验证 → 转换 → 加载
每步使用临时表，最后加载到生产表

A/B测试分析：

创建两个临时表（变体A和B）
进行统计比较

5. 内存管理


监控RAM使用：

-- 查看当前临时表活动
SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;

-- 监控创建速率
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

配置：

-- 内存设置
max_heap_table_size（默认 16MB）
tmp_table_size（默认 16MB）

这些限制MEMORY引擎表大小
如果超过，转换为基于磁盘的InnoDB

示例：增加大型聚合的内存

SET GLOBAL max_heap_table_size = 512M;
SET GLOBAL tmp_table_size = 512M;

5. 最佳实践

总结

临时表对于复杂数据处理至关重要，但如果管理不当会成为瓶颈。通过优化查询、使用适当的存储引擎（MEMORY vs InnoDB）和监控创建率，可保持良好性能。对于复杂操作，显式带索引的临时表通常优于隐式MySQL生成的临时表。

本章评分
4.7  / 5  (14 评分)

💬 留言讨论