1. 临时表定义

临时表特点：
– 会话级别或连接级别
– 会话结束后自动删除
– 对其他会话不可见
– 支持索引和约束

临时表优势：
– 存储中间结果
– 简化复杂查询
– 提高查询性能
– 隔离数据处理

临时表用途：
– 复杂查询分解
– 批量数据处理
– 中间结果存储
– 数据转换

2. 创建临时表

创建基本临时表：
mysql> CREATE TEMPORARY TABLE temp_orders (
id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10,2)
);

输出示例：
Query OK, 0 rows affected (0.01 sec)

基于查询创建临时表：
mysql> CREATE TEMPORARY TABLE temp_high_value AS
SELECT * FROM orders WHERE amount > 10000;

输出示例：
Query OK, 100 rows affected (0.02 sec)

基于现有表结构创建：
mysql> CREATE TEMPORARY TABLE temp_orders_like LIKE orders;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 临时表操作

插入数据：
mysql> INSERT INTO temp_orders VALUES (1, 100, 500.00);

输出示例：
Query OK, 1 row affected (0.00 sec)

查询数据：
mysql> SELECT * FROM temp_orders;

输出示例：
+—-+————-+——–+
| id | customer_id | amount |
+—-+————-+——–+
| 1 | 100 | 500.00 |
+—-+————-+——–+

删除临时表：
mysql> DROP TEMPORARY TABLE IF EXISTS temp_orders;

输出示例：
Query OK, 0 rows affected (0.00 sec)

4. 查看临时表

查看当前会话临时表：
mysql> SHOW TABLES;

输出示例：
+————————+
| Tables_in_production_db|
+————————+
| temp_orders |
| orders |
+————————+

查看临时表结构：
mysql> SHOW CREATE TABLE temp_orders\G

输出示例：
*************************** 1. row ***************************
Table: temp_orders
Create Table: CREATE TEMPORARY TABLE `temp_orders` (
`id` int NOT NULL,
`customer_id` int DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

5. 临时表生命周期

会话级别临时表：
mysql> — 创建临时表
mysql> CREATE TEMPORARY TABLE session_temp (id INT);
mysql> — 断开连接后自动删除

查看会话信息：
mysql> SELECT CONNECTION_ID();

输出示例：
+—————–+
| CONNECTION_ID() |
+—————–+
| 123 |
+—————–+

临时表在会话结束后自动清理：
mysql> — 退出会话
mysql> exit

— 重新连接后临时表不存在

1. 用户临时表

特点：
– 使用CREATE TEMPORARY TABLE创建
– 会话级别可见
– 会话结束后自动删除

创建用户临时表：
mysql> CREATE TEMPORARY TABLE user_temp (
id INT PRIMARY KEY,
name VARCHAR(100)
) ENGINE=InnoDB;

输出示例：
Query OK, 0 rows affected (0.01 sec)

指定存储引擎：
mysql> CREATE TEMPORARY TABLE user_temp_memory (
id INT PRIMARY KEY,
value VARCHAR(100)
) ENGINE=MEMORY;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 内部临时表

特点：
– MySQL自动创建
– 用于复杂查询处理
– 用户不可见

查看内部临时表使用：
mysql> EXPLAIN FORMAT=JSON
SELECT DISTINCT customer_id FROM orders\G

输出示例：
*************************** 1. row ***************************
EXPLAIN: {
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “100.00”
},
“ordering_operation”: {
“using_temporary_table”: true,
“using_filesort”: false
}
}
}

触发内部临时表的场景：
– GROUP BY + ORDER BY
– DISTINCT + ORDER BY
– UNION查询
– 派生表
– 子查询

3. 内存临时表 vs 磁盘临时表

内存临时表：
mysql> CREATE TEMPORARY TABLE mem_temp (
id INT,
value VARCHAR(100)
) ENGINE=MEMORY;

输出示例：
Query OK, 0 rows affected (0.01 sec)

磁盘临时表：
mysql> CREATE TEMPORARY TABLE disk_temp (
id INT,
data TEXT
) ENGINE=InnoDB;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查看临时表存储位置：
mysql> SHOW VARIABLES LIKE ‘%tmpdir%’;

输出示例：
+—————+——-+
| Variable_name | Value |
+—————+——-+
| tmpdir | /tmp |
+—————+——-+

4. 临时表存储引擎

查看默认存储引擎：
mysql> SHOW VARIABLES LIKE ‘default_tmp_storage_engine’;

输出示例：
+—————————+——–+
| Variable_name | Value |
+—————————+——–+
| default_tmp_storage_engine| InnoDB |
+—————————+——–+

设置默认引擎：
mysql> SET default_tmp_storage_engine = ‘MEMORY’;

输出示例：
Query OK, 0 rows affected (0.00 sec)

各引擎对比：
+——————-+——————+——————+
| 特性 | InnoDB | MEMORY |
+——————-+——————+——————+
| 持久化 | 是（临时文件） | 否 |
| 事务支持 | 是 | 否 |
| BLOB/TEXT | 支持 | 不支持 |
| 索引类型 | B-tree | HASH/B-tree |
+——————-+——————+——————+

5. 全局临时表模拟

MySQL不支持全局临时表，可通过以下方式模拟：

使用固定命名约定：
mysql> CREATE TABLE global_temp_session (
session_id VARCHAR(64),
data VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_session (session_id)
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

使用触发器清理：
mysql> DELIMITER //
mysql> CREATE EVENT clean_global_temp
ON SCHEDULE EVERY 1 HOUR
DO
DELETE FROM global_temp_session
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR); mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 会话隔离

特点：每个会话的临时表相互独立

会话1：
mysql> CREATE TEMPORARY TABLE session_test (id INT);
mysql> INSERT INTO session_test VALUES (1);
mysql> SELECT * FROM session_test;

输出示例：
+——+
| id |
+——+
| 1 |
+——+

会话2：
mysql> SELECT * FROM session_test;

输出示例：
ERROR 1146 (42S02): Table ‘production_db.session_test’ doesn’t exist

2. 同名表覆盖

特点：临时表可以与永久表同名

创建永久表：
mysql> CREATE TABLE orders (
id INT PRIMARY KEY,
amount DECIMAL(10,2)
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

创建同名临时表：
mysql> CREATE TEMPORARY TABLE orders (
id INT PRIMARY KEY,
status VARCHAR(20)
);

输出示例：
Query OK, 0 rows affected (0.01 sec)

查询访问的是临时表：
mysql> SELECT * FROM orders;

输出示例：
+—-+——–+
| id | status |
+—-+——–+
+—-+——–+

删除临时表后恢复访问永久表：
mysql> DROP TEMPORARY TABLE orders;
mysql> SELECT * FROM orders;

输出示例：
+—-+——–+
| id | amount |
+—-+——–+
+—-+——–+

3. 自动清理

特点：会话结束后自动删除

查看当前连接：
mysql> SELECT CONNECTION_ID();

输出示例：
+—————–+
| CONNECTION_ID() |
+—————–+
| 456 |
+—————–+

创建临时表：
mysql> CREATE TEMPORARY TABLE auto_clean (id INT);

断开连接后：
mysql> exit

临时表自动删除

4. 索引支持

创建带索引的临时表：
mysql> CREATE TEMPORARY TABLE temp_with_index (
id INT PRIMARY KEY,
name VARCHAR(100),
INDEX idx_name (name)
);

输出示例：
Query OK, 0 rows affected (0.01 sec)

查看索引：
mysql> SHOW INDEX FROM temp_with_index;

输出示例：
+——————+————+———-+————–+————-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+——————+————+———-+————–+————-+
| temp_with_index | 0 | PRIMARY | 1 | id |
| temp_with_index | 1 | idx_name | 1 | name |
+——————+————+———-+————–+————-+

5. 事务支持

InnoDB临时表支持事务：
mysql> CREATE TEMPORARY TABLE temp_innodb (
id INT PRIMARY KEY,
value VARCHAR(100)
) ENGINE=InnoDB;

输出示例：
Query OK, 0 rows affected (0.01 sec)

事务操作：
mysql> START TRANSACTION;
mysql> INSERT INTO temp_innodb VALUES (1, ‘test’);
mysql> ROLLBACK;
mysql> SELECT * FROM temp_innodb;

输出示例：
Empty set (0.00 sec)

MEMORY临时表不支持事务：
mysql> CREATE TEMPORARY TABLE temp_memory (
id INT PRIMARY KEY
) ENGINE=MEMORY;

输出示例：
Query OK, 0 rows affected (0.01 sec)

mysql> START TRANSACTION;
mysql> INSERT INTO temp_memory VALUES (1);
mysql> ROLLBACK;

输出示例：
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM temp_memory;

输出示例：
+—-+
| id |
+—-+
| 1 |
+—-+

1. 存储引擎选择

根据数据特点选择引擎：
+——————-+——————+——————+
| 场景 | 推荐引擎 | 原因 |
+——————-+——————+——————+
| 小数据量 | MEMORY | 性能最优 |
| 大数据量 | InnoDB | 无大小限制 |
| 需要事务 | InnoDB | 支持事务 |
| BLOB/TEXT | InnoDB | MEMORY不支持 |
+——————-+——————+——————+

设置默认引擎：
vim /etc/my.cnf

[mysqld]
default_tmp_storage_engine = InnoDB

重启MySQL：
systemctl restart mysqld

输出示例：
MySQL service restarted.

2. 内存配置

配置内存临时表大小：
mysql> SHOW VARIABLES LIKE ‘%tmp_table_size%’;

输出示例：
+—————-+———-+
| Variable_name | Value |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

mysql> SHOW VARIABLES LIKE ‘%max_heap_table_size%’;

输出示例：
+———————+———-+
| Variable_name | Value |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

调整内存大小：
mysql> SET GLOBAL tmp_table_size = 64*1024*1024;
mysql> SET GLOBAL max_heap_table_size = 64*1024*1024;

输出示例：
Query OK, 0 rows affected (0.00 sec)

3. 磁盘临时表配置

配置临时文件位置：
mysql> SHOW VARIABLES LIKE ‘tmpdir’;

输出示例：
+—————+——-+
| Variable_name | Value |
+—————+——-+
| tmpdir | /tmp |
+—————+——-+

设置专用临时目录：
vim /etc/my.cnf

[mysqld]
tmpdir = /data/mysql_tmp

创建目录并设置权限：
mkdir -p /data/mysql_tmp
chown mysql:mysql /data/mysql_tmp
chmod 750 /data/mysql_tmp

输出示例：
Directory created and permissions set.

4. 索引设计

为临时表添加索引：
mysql> CREATE TEMPORARY TABLE temp_analysis (
id INT,
category VARCHAR(50),
amount DECIMAL(10,2),
INDEX idx_category (category),
INDEX idx_amount (amount)
);

输出示例：
Query OK, 0 rows affected (0.01 sec)

或创建后添加索引：
mysql> CREATE TEMPORARY TABLE temp_analysis (
id INT,
category VARCHAR(50),
amount DECIMAL(10,2)
);

mysql> CREATE INDEX idx_category ON temp_analysis (category);

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 命名规范

临时表命名约定：
+——————-+————————+
| 类型 | 命名规则 |
+——————-+————————+
| 用户临时表 | temp_xxx |
| 批处理临时表 | tmp_batch_xxx |
| 分析临时表 | tmp_analysis_xxx |
| 导入临时表 | tmp_import_xxx |
+——————-+————————+

示例：
mysql> CREATE TEMPORARY TABLE temp_order_analysis (
order_id INT,
customer_id INT,
amount DECIMAL(10,2)
);

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 减少内部临时表使用

优化GROUP BY查询：
— 低效：使用临时表
mysql> EXPLAIN SELECT category, COUNT(*)
FROM products
GROUP BY category
ORDER BY COUNT(*) DESC\G

输出示例：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: products
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using temporary; Using filesort

— 优化：添加索引
mysql> CREATE INDEX idx_category ON products (category);

mysql> EXPLAIN SELECT category, COUNT(*)
FROM products
GROUP BY category\G

输出示例：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: products
partitions: NULL
type: index
possible_keys: idx_category
key: idx_category
key_len: 153
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using index

2. 优化UNION查询

使用UNION ALL代替UNION：
— 使用UNION（需要临时表去重）
mysql> EXPLAIN
SELECT id FROM orders WHERE status = ‘pending’
UNION
SELECT id FROM orders WHERE status = ‘processing’\G

输出示例：
*************************** 1. row ***************************
Extra: Using temporary

— 使用UNION ALL（不需要临时表）
mysql> EXPLAIN
SELECT id FROM orders WHERE status = ‘pending’
UNION ALL
SELECT id FROM orders WHERE status = ‘processing’\G

输出示例：
*************************** 1. row ***************************
Extra: NULL

3. 优化子查询

使用临时表替代复杂子查询：
— 低效：复杂子查询
mysql> SELECT * FROM orders
WHERE customer_id IN (
SELECT customer_id FROM customers
WHERE region = ‘East’
);

— 优化：使用临时表
mysql> CREATE TEMPORARY TABLE temp_east_customers AS
SELECT customer_id FROM customers WHERE region = ‘East’;

mysql> SELECT o.* FROM orders o
INNER JOIN temp_east_customers t ON o.customer_id = t.customer_id;

输出示例：
+—-+————-+——–+
| id | customer_id | amount |
+—-+————-+——–+
| 1 | 100 | 500.00 |
+—-+————-+——–+

4. 内存临时表优化

增加内存限制：
mysql> SET SESSION tmp_table_size = 256*1024*1024;
mysql> SET SESSION max_heap_table_size = 256*1024*1024;

输出示例：
Query OK, 0 rows affected (0.00 sec)

监控内存临时表转换：
mysql> SHOW STATUS LIKE ‘Created_tmp%’;

输出示例：
+————————-+——-+
| Variable_name | Value |
+————————-+——-+
| Created_tmp_disk_tables | 10 |
| Created_tmp_tables | 100 |
+————————-+——-+

计算磁盘临时表比例：
mysql> SELECT
Created_tmp_disk_tables AS disk_tables,
Created_tmp_tables AS total_tables,
ROUND(Created_tmp_disk_tables / Created_tmp_tables * 100, 2) AS disk_ratio
FROM (
SELECT
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = ‘Created_tmp_disk_tables’) AS Created_tmp_disk_tables,
(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = ‘Created_tmp_tables’) AS Created_tmp_tables
) t;

输出示例：
+————-+————-+————-+
| disk_tables | total_tables| disk_ratio |
+————-+————-+————-+
| 10 | 100 | 10.00 |
+————-+————-+————-+

5. 批量操作优化

使用临时表进行批量操作：
mysql> CREATE TEMPORARY TABLE temp_batch (
id INT PRIMARY KEY,
status VARCHAR(20)
);

mysql> INSERT INTO temp_batch VALUES
(1, ‘processed’), (2, ‘processed’), (3, ‘processed’);

mysql> UPDATE orders o
INNER JOIN temp_batch t ON o.id = t.id
SET o.status = t.status;

输出示例：
Query OK, 3 rows affected (0.01 sec)

mysql> DROP TEMPORARY TABLE temp_batch;

输出示例：
Query OK, 0 rows affected (0.00 sec)

1. 监控临时表创建

查看临时表统计：
mysql> SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;

输出示例：
+————————-+——-+
| Variable_name | Value |
+————————-+——-+
| Created_tmp_disk_tables | 50 |
| Created_tmp_files | 10 |
| Created_tmp_tables | 500 |
+————————-+——-+

重置统计：
mysql> FLUSH STATUS;

输出示例：
Query OK, 0 rows affected (0.00 sec)

2. 监控内存使用

查看内存临时表配置：
mysql> SELECT
@@tmp_table_size / 1024 / 1024 AS tmp_table_size_mb,
@@max_heap_table_size / 1024 / 1024 AS max_heap_size_mb;

输出示例：
+——————-+——————-+
| tmp_table_size_mb | max_heap_size_mb |
+——————-+——————-+
| 16.00 | 16.00 |
+——————-+——————-+

3. 监控磁盘临时表

查看临时文件位置：
mysql> SHOW VARIABLES LIKE ‘tmpdir’;

输出示例：
+—————+——-+
| Variable_name | Value |
+—————+——-+
| tmpdir | /tmp |
+—————+——-+

查看磁盘临时文件：
ls -la /tmp/ | grep -i mysql

输出示例：
-rw-rw—- 1 mysql mysql 8388608 Apr 1 10:00 /tmp/ib*.tmp
-rw-rw—- 1 mysql mysql 8388608 Apr 1 10:00 /tmp/SQL*.tmp

4. 创建监控视图

创建临时表监控视图：
mysql> CREATE VIEW v_temp_table_stats AS
SELECT
VARIABLE_NAME,
VARIABLE_VALUE,
CASE
WHEN VARIABLE_NAME = ‘Created_tmp_disk_tables’ THEN ‘disk’
WHEN VARIABLE_NAME = ‘Created_tmp_tables’ THEN ‘total’
ELSE ‘other’
END AS type
FROM performance_schema.global_status
WHERE VARIABLE_NAME LIKE ‘Created_tmp%’;

查询监控数据：
mysql> SELECT * FROM v_temp_table_stats;

输出示例：
+————————-+—————+——-+
| VARIABLE_NAME | VARIABLE_VALUE| type |
+————————-+—————+——-+
| Created_tmp_disk_tables | 50 | disk |
| Created_tmp_tables | 500 | total |
+————————-+—————+——-+

5. 告警配置

创建告警存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE check_temp_table_ratio()
BEGIN
DECLARE disk_tables INT;
DECLARE total_tables INT;
DECLARE ratio DECIMAL(5,2);

SELECT VARIABLE_VALUE INTO disk_tables
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Created_tmp_disk_tables’;

SELECT VARIABLE_VALUE INTO total_tables
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Created_tmp_tables’;

SET ratio = (disk_tables / total_tables) * 100;

IF ratio > 25 THEN
SELECT CONCAT(‘Warning: Disk temp table ratio is ‘, ratio, ‘%’) AS alert;
ELSE
SELECT CONCAT(‘OK: Disk temp table ratio is ‘, ratio, ‘%’) AS status;
END IF;
END //
mysql> DELIMITER ;

执行检查：
mysql> CALL check_temp_table_ratio();

输出示例：
+——————————————+
| status |
+——————————————+
| OK: Disk temp table ratio is 10.00% |
+——————————————+

1. 临时表创建规范

标准临时表模板：
mysql> CREATE TEMPORARY TABLE temp_standard (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
batch_id VARCHAR(50),
data VARCHAR(500),
status TINYINT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_batch (batch_id),
INDEX idx_status (status)
) ENGINE=InnoDB
COMMENT ‘标准临时表模板’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 使用规范

使用临时表的标准流程：
— 1. 检查并删除已存在的临时表
mysql> DROP TEMPORARY TABLE IF EXISTS temp_process;

— 2. 创建临时表
mysql> CREATE TEMPORARY TABLE temp_process AS
SELECT * FROM source_table WHERE condition;

— 3. 添加必要的索引
mysql> CREATE INDEX idx_key ON temp_process (key_column);

— 4. 处理数据
mysql> UPDATE temp_process SET status = 1 WHERE …;

— 5. 使用完毕后清理
mysql> DROP TEMPORARY TABLE IF EXISTS temp_process;

输出示例：
Query OK, 0 rows affected (0.00 sec)

3. 命名规范

临时表命名约定：
+——————-+————————+——————+
| 用途 | 命名规则 | 示例 |
+——————-+————————+——————+
| 通用临时表 | temp_xxx | temp_orders |
| 批处理 | tmp_batch_xxx | tmp_batch_import |
| 数据分析 | tmp_analysis_xxx | tmp_analysis_sales|
| 数据转换 | tmp_transform_xxx | tmp_transform_data|
| 中间结果 | tmp_intermediate_xxx | tmp_intermediate |
+——————-+————————+——————+

4. 清理规范

自动清理脚本：
mysql> DELIMITER //
mysql> CREATE PROCEDURE cleanup_temp_tables()
BEGIN
— 清理本会话的临时表
DECLARE done INT DEFAULT FALSE;
DECLARE tbl_name VARCHAR(100);
DECLARE cur CURSOR FOR
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME LIKE ‘temp_%’;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO tbl_name;
IF done THEN
LEAVE read_loop;
END IF;
SET @sql = CONCAT(‘DROP TEMPORARY TABLE IF EXISTS ‘, tbl_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;

SELECT ‘Cleanup completed’ AS status;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 监控规范

创建监控存储过程：
mysql> DELIMITER //
mysql> CREATE PROCEDURE log_temp_table_usage(
IN p_table_name VARCHAR(100),
IN p_operation VARCHAR(20),
IN p_rows_affected INT
)
BEGIN
INSERT INTO temp_table_log
(table_name, operation, rows_affected, session_id)
VALUES
(p_table_name, p_operation, p_rows_affected, CONNECTION_ID());
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 复杂查询分解

场景：多表关联复杂查询

原始复杂查询：
mysql> SELECT
c.customer_name,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_amount,
AVG(o.amount) AS avg_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE c.region = ‘East’
AND o.order_date >= ‘2026-01-01’
GROUP BY c.id
HAVING total_amount > 10000;

使用临时表分解：
— 步骤1：筛选客户
mysql> CREATE TEMPORARY TABLE temp_east_customers AS
SELECT id, customer_name FROM customers WHERE region = ‘East’;

输出示例：
Query OK, 100 rows affected (0.01 sec)

— 步骤2：汇总订单
mysql> CREATE TEMPORARY TABLE temp_order_summary AS
SELECT
o.customer_id,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_amount,
AVG(o.amount) AS avg_amount
FROM orders o
WHERE o.order_date >= ‘2026-01-01’
GROUP BY o.customer_id;

输出示例：
Query OK, 500 rows affected (0.02 sec)

— 步骤3：最终查询
mysql> SELECT
c.customer_name,
os.order_count,
os.total_amount,
os.avg_amount
FROM temp_east_customers c
INNER JOIN temp_order_summary os ON c.id = os.customer_id
WHERE os.total_amount > 10000;

输出示例：
+—————+————-+————–+————+
| customer_name | order_count | total_amount | avg_amount |
+—————+————-+————–+————+
| Customer A | 10 | 15000.00 | 1500.00 |
| Customer B | 15 | 20000.00 | 1333.33 |
+—————+————-+————–+————+

2. 批量数据处理

场景：批量更新状态

创建临时表存储待处理ID：
mysql> CREATE TEMPORARY TABLE temp_pending_ids (
id INT PRIMARY KEY
) ENGINE=MEMORY;

输出示例：
Query OK, 0 rows affected (0.01 sec)

插入待处理数据：
mysql> INSERT INTO temp_pending_ids
SELECT id FROM orders WHERE status = ‘pending’ LIMIT 1000;

输出示例：
Query OK, 1000 rows affected (0.01 sec)

批量更新：
mysql> UPDATE orders o
INNER JOIN temp_pending_ids t ON o.id = t.id
SET o.status = ‘processing’;

输出示例：
Query OK, 1000 rows affected (0.05 sec)

3. 数据导入导出

场景：数据格式转换

创建导入临时表：
mysql> CREATE TEMPORARY TABLE temp_import (
raw_data VARCHAR(500),
col1 VARCHAR(100),
col2 VARCHAR(100),
col3 DECIMAL(10,2),
processed TINYINT DEFAULT 0
);

输出示例：
Query OK, 0 rows affected (0.01 sec)

加载原始数据：
mysql> LOAD DATA INFILE ‘/tmp/import.csv’
INTO TABLE temp_import (raw_data);

输出示例：
Query OK, 1000 rows affected (0.10 sec)

解析数据：
mysql> UPDATE temp_import SET
col1 = SUBSTRING_INDEX(raw_data, ‘,’, 1),
col2 = SUBSTRING_INDEX(SUBSTRING_INDEX(raw_data, ‘,’, 2), ‘,’, -1),
col3 = CAST(SUBSTRING_INDEX(raw_data, ‘,’, -1) AS DECIMAL(10,2)),
processed = 1;

输出示例：
Query OK, 1000 rows affected (0.05 sec)

插入目标表：
mysql> INSERT INTO target_table (col1, col2, col3)
SELECT col1, col2, col3 FROM temp_import WHERE processed = 1;

输出示例：
Query OK, 1000 rows affected (0.02 sec)

4. 报表生成

场景：多维度报表

创建报表临时表：
mysql> CREATE TEMPORARY TABLE temp_report (
category VARCHAR(50),
region VARCHAR(50),
sales_amount DECIMAL(12,2),
order_count INT,
avg_order_value DECIMAL(10,2)
);

输出示例：
Query OK, 0 rows affected (0.01 sec)

计算各维度数据：
mysql> INSERT INTO temp_report
SELECT
p.category,
c.region,
SUM(o.amount) AS sales_amount,
COUNT(o.id) AS order_count,
AVG(o.amount) AS avg_order_value
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY p.category, c.region;

输出示例：
Query OK, 50 rows affected (0.05 sec)

生成报表：
mysql> SELECT * FROM temp_report ORDER BY sales_amount DESC;

输出示例：
+———-+——–+————–+————-+—————-+
| category | region | sales_amount | order_count | avg_order_value|
+———-+——–+————–+————-+—————-+
| Electronics| East | 500000.00 | 500 | 1000.00 |
| Clothing | West | 300000.00 | 300 | 1000.00 |
+———-+——–+————–+————-+—————-+

5. 数据对比

场景：数据差异分析

创建对比临时表：
mysql> CREATE TEMPORARY TABLE temp_current AS
SELECT * FROM products WHERE updated_at >= ‘2026-04-01’;

输出示例：
Query OK, 100 rows affected (0.01 sec)

mysql> CREATE TEMPORARY TABLE temp_previous AS
SELECT * FROM products_backup WHERE backup_date = ‘2026-03-31’;

输出示例：
Query OK, 95 rows affected (0.01 sec)

找出新增记录：
mysql> SELECT c.* FROM temp_current c
LEFT JOIN temp_previous p ON c.id = p.id
WHERE p.id IS NULL;

输出示例：
+—-+————+——-+————+
| id | name | price | updated_at |
+—-+————+——-+————+
| 96 | Product 96 | 99.99 | 2026-04-01 |
+—-+————+——-+————+

找出修改记录：
mysql> SELECT c.id, c.name AS new_name, p.name AS old_name,
c.price AS new_price, p.price AS old_price
FROM temp_current c
INNER JOIN temp_previous p ON c.id = p.id
WHERE c.name != p.name OR c.price != p.price;

输出示例：
+—-+———–+———–+———–+———–+
| id | new_name | old_name | new_price | old_price |
+—-+———–+———–+———–+———–+
| 1 | Product A | Product 1 | 29.99 | 19.99 |
+—-+———–+———–+———–+———–+

1. 定期清理

清理过期临时表：
mysql> — 会话结束后自动清理
mysql> — 手动清理当前会话临时表
mysql> DROP TEMPORARY TABLE IF EXISTS temp_xxx;

输出示例：
Query OK, 0 rows affected (0.00 sec)

查看当前会话临时表：
mysql> SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME LIKE ‘temp_%’;

输出示例：
+——————+
| TABLE_NAME |
+——————+
| temp_orders |
| temp_analysis |
+——————+

2. 监控临时表使用

查看临时表创建统计：
mysql> SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;

输出示例：
+————————-+——-+
| Variable_name | Value |
+————————-+——-+
| Created_tmp_disk_tables | 50 |
| Created_tmp_files | 10 |
| Created_tmp_tables | 500 |
+————————-+——-+

计算磁盘临时表比例：
mysql> SELECT
VARIABLE_VALUE AS disk_tables,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Created_tmp_tables’) AS total_tables,
ROUND(VARIABLE_VALUE /
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Created_tmp_tables’) * 100, 2) AS disk_ratio
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Created_tmp_disk_tables’;

输出示例：
+————-+————-+————-+
| disk_tables | total_tables| disk_ratio |
+————-+————-+————-+
| 50 | 500 | 10.00 |
+————-+————-+————-+

3. 优化内存配置

调整临时表内存大小：
mysql> SET GLOBAL tmp_table_size = 128*1024*1024;
mysql> SET GLOBAL max_heap_table_size = 128*1024*1024;

输出示例：
Query OK, 0 rows affected (0.00 sec)

永久配置：
vim /etc/my.cnf

[mysqld]
tmp_table_size = 128M
max_heap_table_size = 128M

重启MySQL：
systemctl restart mysqld

输出示例：
MySQL service restarted.

4. 磁盘空间管理

检查临时目录空间：
df -h /tmp

输出示例：
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 50G 10G 40G 20% /

清理临时文件：
find /tmp -name “*.tmp” -mtime +1 -delete

输出示例：
Old temporary files deleted.

5. 性能分析

分析临时表使用情况：
mysql> SELECT
DIGEST_TEXT,
COUNT_STAR,
SUM_CREATED_TMP_TABLES,
SUM_CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10;

输出示例：
+———————————-+————+————————+——————————-+
| DIGEST_TEXT | COUNT_STAR | SUM_CREATED_TMP_TABLES | SUM_CREATED_TMP_DISK_TABLES |
+———————————-+————+————————+——————————-+
| SELECT DISTINCT … | 100 | 100 | 50 |
| SELECT … GROUP BY … | 200 | 200 | 30 |
+———————————-+————+————————+——————————-+

# 案例：多表关联报表查询优化

# 问题描述：
# 原始查询执行时间超过30秒

# 步骤1：分析原始查询
mysql> EXPLAIN SELECT
c.customer_name,
p.category,
SUM(oi.quantity) AS total_qty,
SUM(oi.quantity * oi.price) AS total_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date BETWEEN ‘2026-01-01’ AND ‘2026-03-31’
GROUP BY c.customer_name, p.category
ORDER BY total_amount DESC;

# 输出示例：
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: c
# type: ALL
# possible_keys: NULL
# key: NULL
# key_len: NULL
# ref: NULL
# rows: 10000
# filtered: 100.00
# Extra: Using temporary; Using filesort

# 步骤2：使用临时表分解查询
mysql> CREATE TEMPORARY TABLE temp_orders_q1 AS
SELECT o.id, o
# type: ref
# possible_keys: idx_customer
# key: idx_customer
# rows: 1000
# Extra: Using temporary; Using filesort

# 步骤2：使用临时表分解查询
mysql> CREATE TEMPORARY TABLE temp_orders AS
SELECT o.id, o.customer_id
FROM orders o
WHERE o.order_date BETWEEN ‘2026-01-01’ AND ‘2026-03-31’;

# 输出示例：
# Query OK, 10000 rows affected (0.50 sec)

mysql> CREATE INDEX idx_customer ON temp_orders (customer_id);

# 输出示例：
# Query OK, 0 rows affected (0.10 sec)

# 步骤3：创建汇总临时表
mysql> CREATE TEMPORARY TABLE temp_summary AS
SELECT
o.customer_id,
p.category,
SUM(oi.quantity) AS total_qty,
SUM(oi.quantity * oi.price) AS total_amount
FROM temp_orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY o.customer_id, p.category;

# 输出示例：
# Query OK, 500 rows affected (2.00 sec)

# 步骤4：最终查询
mysql> SELECT
c.customer_name,
s.category,
s.total_qty,
s.total_amount
FROM temp_summary s
JOIN customers c ON s.customer_id = c.id
ORDER BY s.total_amount DESC;

# 输出示例：
# +—————+———–+———–+————–+
# | customer_name | category | total_qty | total_amount |
# +—————+———–+———–+————–+
# | Customer A | Electronics| 100 | 50000.00 |
# | Customer B | Clothing | 200 | 30000.00 |
# +—————+———–+———–+————–+

# 性能对比：
# +——————+————+
# | 方法 | 执行时间 |
# +——————+————+
# | 原始查询 | 30秒 |
# | 临时表分解 | 3秒 |
# +——————+————+

# 性能提升：10倍

# 案例：批量订单状态更新

# 问题描述：
# 需要更新100万条订单状态

# 步骤1：创建临时表存储待处理ID
mysql> CREATE TEMPORARY TABLE temp_order_ids (
id INT PRIMARY KEY,
new_status VARCHAR(20)
) ENGINE=MEMORY;

# 输出示例：
# Query OK, 0 rows affected (0.01 sec)

# 步骤2：分批加载待处理数据
mysql> INSERT INTO temp_order_ids
SELECT id, ‘completed’
FROM orders
WHERE status = ‘processing’
AND updated_at < '2026-03-01' LIMIT 100000; # 输出示例： # Query OK, 100000 rows affected (1.00 sec) # 步骤3：批量更新 mysql> UPDATE orders o
INNER JOIN temp_order_ids t ON o.id = t.id
SET o.status = t.new_status,
o.updated_at = NOW();

# 输出示例：
# Query OK, 100000 rows affected (5.00 sec)

# 步骤4：清空临时表继续处理
mysql> TRUNCATE TABLE temp_order_ids;

# 输出示例：
# Query OK, 0 rows affected (0.01 sec)

# 步骤5：循环处理直到完成
# 重复步骤2-4直到所有数据处理完成

# 性能对比：
# +——————+————+
# | 方法 | 处理时间 |
# +——————+————+
# | 单条更新 | 30分钟 |
# | 批量更新 | 5分钟 |
# +——————+————+

# 性能提升：6倍

# 案例：减少磁盘临时表使用

# 问题描述：
# 磁盘临时表比例过高（50%）

# 步骤1：检查当前配置
mysql> SELECT
@@tmp_table_size / 1024 / 1024 AS tmp_table_size_mb,
@@max_heap_table_size / 1024 / 1024 AS max_heap_size_mb;

# 输出示例：
# +——————-+——————-+
# | tmp_table_size_mb | max_heap_size_mb |
# +——————-+——————-+
# | 16.00 | 16.00 |
# +——————-+——————-+

# 步骤2：检查磁盘临时表比例
mysql> SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;

# 输出示例：
# +————————-+——-+
# | Variable_name | Value |
# +————————-+——-+
# | Created_tmp_disk_tables | 500 |
# | Created_tmp_tables | 1000 |
# +————————-+——-+

# 磁盘临时表比例：50%

# 步骤3：增加内存配置
mysql> SET GLOBAL tmp_table_size = 256*1024*1024;
mysql> SET GLOBAL max_heap_table_size = 256*1024*1024;

# 输出示例：
# Query OK, 0 rows affected (0.00 sec)

# 步骤4：重启MySQL应用配置
systemctl restart mysqld

# 输出示例：
# MySQL service restarted.

# 步骤5：验证优化效果
mysql> SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;

# 输出示例：
# +————————-+——-+
# | Variable_name | Value |
# +————————-+——-+
# | Created_tmp_disk_tables | 50 |
# | Created_tmp_tables | 1000 |
# +————————-+——-+

# 磁盘临时表比例：5%

# 性能提升：
# +——————+————+
# | 指标 | 优化前 | 优化后 |
# +——————+————+———–+
# | 磁盘临时表比例 | 50% | 5% |
# | 平均查询时间 | 100ms | 20ms |
# +——————+————+———–+

1. 设计原则

– 选择合适的存储引擎
– 合理设置内存大小
– 添加必要的索引
– 使用规范的命名

2. 使用原则

– 及时清理临时表
– 避免创建过多临时表
– 使用IF EXISTS防止错误
– 会话结束前主动清理

3. 性能原则

– 减少内部临时表使用
– 优化查询避免临时表
– 增加内存减少磁盘IO
– 使用临时表分解复杂查询

4. 监控原则

– 监控临时表创建数量
– 监控磁盘临时表比例
– 监控临时文件空间
– 定期分析慢查询

1. 会话限制

– 仅当前会话可见
– 会话结束后自动删除
– 不能跨会话共享

2. 功能限制

– 不能在同一查询中引用两次
– 不能与永久表同名后使用RENAME
– 不支持外键约束

3. 存储限制

– MEMORY引擎不支持BLOB/TEXT
– 受tmp_table_size限制
– 受max_heap_table_size限制

4. 性能限制

– 创建和删除有开销
– 磁盘临时表性能较差
– 大量临时表影响性能

5. 使用限制

– 不能在存储函数中使用
– 不能在触发器中使用（部分场景）
– 需要CREATE TEMPORARY TABLE权限

1. 设计阶段检查

[ ] 是否选择了合适的存储引擎
[ ] 是否评估了内存需求
[ ] 是否设计了必要的索引
[ ] 是否使用了规范的命名

2. 开发阶段检查

[ ] 是否在使用后清理临时表
[ ] 是否处理了异常情况
[ ] 是否测试了性能
[ ] 是否验证了数据正确性

3. 运维阶段检查

[ ] 是否监控了临时表创建数量
[ ] 是否监控了磁盘临时表比例
[ ] 是否定期检查临时目录空间
[ ] 是否优化了内存配置

4. 监控阶段检查

[ ] 是否设置了告警阈值
[ ] 是否分析了慢查询
[ ] 是否优化了高临时表查询
[ ] 是否生成了分析报告