本文档风哥主要介绍TiDB临时表与CTE优化相关知识，包括临时表基础、CTE基础、优化原理、临时表规划、CTE规划、性能建议、临时表实施方案、CTE实施方案、优化实施方案等内容，风哥教程参考TiDB官方文档SQL优化章节，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 临时表基础
临时表的核心概念：
- 临时表：是一种特殊的表，只在当前会话中存在，会话结束后自动删除。
- 会话级临时表：只对创建它的会话可见，会话结束后自动删除。
- 事务级临时表：只在当前事务中存在，事务结束后自动删除。
- 临时表的优势：减少中间结果的存储开销，提高查询性能，简化复杂查询。
- 临时表的使用场景：复杂查询的中间结果存储，数据转换，批量数据处理等。
- 临时表的名称可以与普通表相同，但会优先使用临时表
- 临时表不会产生binlog，因此不会影响主从同步
- 临时表的性能通常比普通表高，因为它们存储在内存中（如果内存足够）
- 临时表的大小受系统参数限制
1.2 CTE基础
CTE（Common Table Expression，公用表表达式）的核心概念：
- CTE：是一种临时结果集，只在当前SQL语句中存在。
- 递归CTE：可以递归引用自身，用于处理层次结构数据。
- 非递归CTE：不引用自身，用于简化复杂查询。
- CTE的优势：提高查询可读性，简化复杂查询，支持递归查询。
- CTE的使用场景：复杂查询的分解，递归查询，数据转换等。
风哥提示：
## 非递归CTE
WITH cte_name (column1, column2, …) AS (
SELECT column1, column2, …
FROM table_name
WHERE condition
)
SELECT * FROM cte_name;
## 递归CTE
WITH RECURSIVE cte_name (column1, column2, …) AS (
— 锚点查询
SELECT column1, column2, …
FROM table_name
WHERE condition
UNION ALL
— 递归查询
SELECT column1, column2, …
FROM cte_name
JOIN table_name ON condition
WHERE condition
)
SELECT * FROM cte_name;
1.3 优化原理
临时表与CTE的优化原理：
## 1. 存储优化
– 临时表优先存储在内存中，内存不足时会存储到磁盘
– 临时表的存储引擎通常为内存引擎（如MEMORY）或临时文件
– 临时表的索引结构与普通表相同，但更轻量
## 2. 执行计划优化
– 临时表可以缓存中间结果，减少重复计算
– 临时表可以避免子查询的重复执行
– 临时表可以使用索引加速查询
## 3. 并发优化
– 临时表只对当前会话可见，不会影响其他会话
– 临时表的锁竞争较少，提高并发性能
# CTE优化原理
## 1. 查询分解
– CTE将复杂查询分解为多个简单的子查询
– 提高查询可读性和可维护性
– 便于优化器生成更高效的执行计划
## 2. 递归优化
– 递归CTE可以高效处理层次结构数据
– 避免了传统循环查询的性能开销
– 优化器可以对递归查询进行特殊处理
## 3. 执行计划复用
– CTE的结果集可以在同一SQL语句中多次引用
– 避免了重复计算，提高查询性能
– 优化器可以对CTE进行缓存
Part02-生产环境规划与建议
2.1 临时表规划
临时表规划：
## 1. 临时表类型选择
– 会话级临时表：适合需要在多个事务中使用的临时数据
– 事务级临时表：适合只在单个事务中使用的临时数据
## 2. 临时表大小规划
– 根据数据量大小选择合适的临时表类型
– 对于小数据量，使用内存临时表
– 对于大数据量，使用磁盘临时表
## 3. 临时表索引规划
– 为临时表创建合适的索引，提高查询性能
– 避免创建过多索引，增加维护成本
– 选择合适的索引类型（B-tree、Hash等）
## 4. 临时表命名规范
– 使用有意义的名称，便于理解
– 避免与普通表重名，减少混淆
– 统一命名格式，便于管理
学习交流加群风哥QQ113257174
## 5. 临时表生命周期管理
– 及时清理不再使用的临时表
– 避免临时表占用过多资源
– 监控临时表的使用情况
2.2 CTE规划
CTE规划：
## 1. CTE使用场景
– 复杂查询的分解
– 递归查询
– 数据转换
– 报表生成
## 2. CTE命名规范
– 使用有意义的名称，便于理解
– 避免与表名重名，减少混淆
– 统一命名格式，便于管理
## 3. CTE复杂度控制
– 避免嵌套过深的CTE，影响性能
– 控制CTE的数量，避免过度使用
– 合理分解复杂查询，提高可读性
## 4. 递归CTE规划
– 控制递归深度，避免无限递归
– 设置合理的终止条件
– 监控递归查询的性能
## 5. CTE性能监控
– 监控CTE的执行时间
– 分析CTE的执行计划
– 优化CTE的查询语句
2.3 性能建议
性能建议：
## 1. 临时表性能建议
– 只在必要时使用临时表
– 合理设置临时表的大小
– 为临时表创建合适的索引
– 避免在临时表上进行大量操作
– 及时清理临时表
## 2. CTE性能建议
– 避免过度使用CTE
– 控制CTE的复杂度
– 合理使用递归CTE
– 监控CTE的执行性能
– 优化CTE的查询语句
## 3. 系统参数建议
– 调整tmp_table_size参数，控制内存临时表的大小
– 调整max_heap_table_size参数，控制内存表的大小
– 调整tmpdir参数，指定临时文件的存储位置
– 确保临时文件存储在高速存储设备上
## 4. 硬件建议
– 增加内存容量，提高临时表的内存存储能力
– 使用高速存储设备，提高临时文件的读写性能
– 优化存储子系统，减少I/O瓶颈
## 5. 监控建议
– 监控临时表的使用情况
– 监控CTE的执行性能
– 分析慢查询日志，识别性能问题
– 定期优化临时表和CTE的使用
Part03-生产环境项目实施方案
3.1 临时表实施方案
3.1.1 临时表创建与使用
## 1. 创建会话级临时表
CREATE TEMPORARY TABLE fgedu_temp_users (
id INT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255)
);
## 2. 插入数据
INSERT INTO fgedu_temp_users (id, name, email) VALUES
(1, ‘user1’, ‘user1@fgedu.net.cn’),
(2, ‘user2’, ‘user2@fgedu.net.cn’),
(3, ‘user3’, ‘user3@fgedu.net.cn’);
## 3. 查询数据
SELECT * FROM fgedu_temp_users;
## 4. 更新数据
UPDATE fgedu_temp_users SET name = ‘updated_user1’ WHERE id = 1;
## 5. 删除数据
DELETE FROM fgedu_temp_users WHERE id = 3;
## 6. 查看临时表结构
DESCRIBE fgedu_temp_users;
## 7. 删除临时表
DROP TEMPORARY TABLE IF EXISTS fgedu_temp_users;
3.1.2 临时表索引优化
## 1. 创建带索引的临时表
CREATE TEMPORARY TABLE fgedu_temp_orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
order_date DATETIME,
INDEX idx_user_id (user_id),
INDEX idx_order_date (order_date)
);
## 2. 插入数据
INSERT INTO fgedu_temp_orders (id, user_id, amount, order_date) VALUES
(1, 1, 100.00, ‘2023-01-01 10:00:00’),
(2, 1, 200.00, ‘2023-01-02 11:00:00’),
(3, 2, 150.00, ‘2023-01-03 12:00:00’),
(4, 2, 300.00, ‘2023-01-04 13:00:00’),
(5, 3, 250.00, ‘2023-01-05 14:00:00’);
## 3. 使用索引查询
EXPLAIN SELECT * FROM fgedu_temp_orders WHERE user_id = 1;
## 4. 复合索引优化
CREATE TEMPORARY TABLE fgedu_temp_products (
id INT PRIMARY KEY,
category_id INT,
price DECIMAL(10,2),
INDEX idx_category_price (category_id, price)
);
## 5. 插入数据
INSERT INTO fgedu_temp_products (id, category_id, price) VALUES
(1, 1, 50.00),
(2, 1, 100.00),
(3, 2, 150.00),
(4, 2, 200.00),
(5, 3, 250.00);
## 6. 使用复合索引查询
EXPLAIN SELECT * FROM fgedu_temp_products WHERE category_id = 1 AND price > 80;
3.2 CTE实施方案
3.2.1 非递归CTE使用
## 1. 基本CTE
WITH fgedu_cte_users AS (
SELECT id, name, email
FROM fgedudb.fgedu_users
WHERE id > 5
)
SELECT * FROM fgedu_cte_users;
## 2. 多CTE
WITH
fgedu_cte_users AS (
SELECT id, name, email
FROM fgedudb.fgedu_users
),
fgedu_cte_orders AS (
SELECT id, user_id, amount
FROM fgedudb.fgedu_orders
)
SELECT u.name, o.amount
FROM fgedu_cte_users u
JOIN fgedu_cte_orders o ON u.id = o.user_id;
## 3. CTE与聚合
WITH fgedu_cte_order_stats AS (
SELECT user_id, SUM(amount) AS total_amount
FROM fgedudb.fgedu_orders
GROUP BY user_id
)
SELECT u.name, s.total_amount
FROM fgedudb.fgedu_users u
JOIN fgedu_cte_order_stats s ON u.id = s.user_id
ORDER BY s.total_amount DESC;
3.2.2 递归CTE使用
## 1. 层次结构查询
WITH RECURSIVE fgedu_cte_org AS (
— 锚点查询
SELECT id, name, parent_id
FROM fgedudb.fgedu_organizations
WHERE parent_id IS NULL
UNION ALL
— 递归查询
SELECT o.id, o.name, o.parent_id
FROM fgedudb.fgedu_organizations o
JOIN fgedu_cte_org c ON o.parent_id = c.id
)
SELECT * FROM fgedu_cte_org;
## 2. 数字序列生成
WITH RECURSIVE fgedu_cte_numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM fgedu_cte_numbers
WHERE n < 10
)
SELECT * FROM fgedu_cte_numbers;
## 3. 日期序列生成
WITH RECURSIVE fgedu_cte_dates AS (
SELECT '2023-01-01' AS date
UNION ALL
SELECT DATE_ADD(date, INTERVAL 1 DAY)
FROM fgedu_cte_dates
WHERE date < '2023-01-10'
)
SELECT * FROM fgedu_cte_dates;
3.3 优化实施方案
3.3.1 临时表优化
## 1. 内存临时表优化
— 设置内存临时表大小
SET tmp_table_size = 64M;
SET max_heap_table_size = 64M;
— 创建内存临时表
CREATE TEMPORARY TABLE fgedu_temp_memory (
id INT PRIMARY KEY,
name VARCHAR(255)
) ENGINE=MEMORY;
## 2. 临时表缓存
— 重复使用临时表
CREATE TEMPORARY TABLE IF NOT EXISTS fgedu_temp_cache (
id INT PRIMARY KEY,
data VARCHAR(255)
);
— 清空临时表
TRUNCATE TABLE fgedu_temp_cache;
— 插入数据
INSERT INTO fgedu_temp_cache (id, data) VALUES
(1, ‘data1’),
(2, ‘data2’);
## 3. 临时表分区
— 创建分区临时表
CREATE TEMPORARY TABLE fgedu_temp_partitioned (
id INT PRIMARY KEY,
created_at DATETIME
) PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);
3.3.2 CTE优化
## 1. CTE与索引
WITH fgedu_cte_indexed AS (
SELECT id, name, email
FROM fgedudb.fgedu_users
WHERE id > 5
)
SELECT * FROM fgedu_cte_indexed WHERE name LIKE ‘user%’;
## 2. CTE与子查询对比
— 使用CTE
WITH fgedu_cte AS (
SELECT id, name
FROM fgedudb.fgedu_users
)
SELECT * FROM fgedu_cte WHERE id IN (SELECT user_id FROM fgedudb.fgedu_orders);
— 使用子查询
SELECT * FROM (
SELECT id, name
FROM fgedudb.fgedu_users
) AS sub WHERE id IN (SELECT user_id FROM fgedudb.fgedu_orders);
## 3. CTE与视图对比
— 创建视图
CREATE VIEW fgedu_view_users AS
SELECT id, name, email
FROM fgedudb.fgedu_users;
— 使用视图
SELECT * FROM fgedu_view_users WHERE id > 5;
— 使用CTE
WITH fgedu_cte_users AS (
SELECT id, name, email
FROM fgedudb.fgedu_users
)
SELECT * FROM fgedu_cte_users WHERE id > 5;
Part04-生产案例与实战讲解
4.1 临时表使用案例
临时表使用案例：
## 案例1：复杂查询的中间结果存储
### 步骤1：创建临时表存储中间结果
CREATE TEMPORARY TABLE fgedu_temp_order_stats (
user_id INT,
total_amount DECIMAL(10,2),
order_count INT
);
### 步骤2：插入中间结果
INSERT INTO fgedu_temp_order_stats
SELECT user_id, SUM(amount), COUNT(*)
FROM fgedudb.fgedu_orders
GROUP BY user_id;
### 步骤3：使用临时表进行后续查询
SELECT u.name, s.total_amount, s.order_count
FROM fgedudb.fgedu_users u
JOIN fgedu_temp_order_stats s ON u.id = s.user_id
ORDER BY s.total_amount DESC;
## 案例2：数据转换
### 步骤1：创建临时表
CREATE TEMPORARY TABLE fgedu_temp_transformed (
id INT,
name VARCHAR(255),
normalized_name VARCHAR(255)
);
### 步骤2：插入并转换数据
INSERT INTO fgedu_temp_transformed
SELECT id, name, LOWER(REPLACE(name, ‘ ‘, ‘_’))
FROM fgedudb.fgedu_users;
### 步骤3：使用转换后的数据
SELECT * FROM fgedu_temp_transformed;
## 案例3：批量数据处理
### 步骤1：创建临时表
CREATE TEMPORARY TABLE fgedu_temp_batch (
id INT,
status VARCHAR(50)
);
### 步骤2：插入需要处理的数据
INSERT INTO fgedu_temp_batch
SELECT id, status
FROM fgedudb.fgedu_orders
WHERE status = ‘pending’;
### 步骤3：批量更新
UPDATE fgedudb.fgedu_orders o
JOIN fgedu_temp_batch b ON o.id = b.id
SET o.status = ‘processed’;
4.2 CTE使用案例
CTE使用案例：
## 案例1：复杂查询分解
WITH
fgedu_cte_users AS (
SELECT id, name, email
FROM fgedudb.fgedu_users
WHERE active = 1
),
fgedu_cte_orders AS (
SELECT user_id, SUM(amount) AS total_amount
FROM fgedudb.fgedu_orders
WHERE order_date >= ‘2023-01-01’
GROUP BY user_id
),
fgedu_cte_result AS (
SELECT u.name, u.email, o.total_amount
FROM fgedu_cte_users u
LEFT JOIN fgedu_cte_orders o ON u.id = o.user_id
)
SELECT * FROM fgedu_cte_result
ORDER BY total_amount DESC;
## 案例2：递归查询
### 组织架构查询
WITH RECURSIVE fgedu_cte_org AS (
SELECT id, name, parent_id, 0 AS level
FROM fgedudb.fgedu_organizations
WHERE parent_id IS NULL
UNION ALL
SELECT o.id, o.name, o.parent_id, c.level + 1
FROM fgedudb.fgedu_organizations o
JOIN fgedu_cte_org c ON o.parent_id = c.id
)
SELECT id, name, parent_id, level
FROM fgedu_cte_org
ORDER BY level, id;
## 案例3：报表生成
WITH
fgedu_cte_daily_sales AS (
SELECT
DATE(order_date) AS sale_date,
SUM(amount) AS daily_total
FROM fgedudb.fgedu_orders
GROUP BY DATE(order_date)
),
fgedu_cte_monthly_sales AS (
SELECT
DATE_FORMAT(sale_date, ‘%Y-%m’) AS month,
SUM(daily_total) AS monthly_total
FROM fgedu_cte_daily_sales
GROUP BY DATE_FORMAT(sale_date, ‘%Y-%m’)
)
SELECT month, monthly_total
FROM fgedu_cte_monthly_sales
ORDER BY month;
4.3 优化案例
优化案例：
## 案例1：临时表优化
### 优化前
SELECT
u.id, u.name,
(SELECT SUM(amount) FROM fgedudb.fgedu_orders WHERE user_id = u.id) AS total_amount,
(SELECT COUNT(*) FROM fgedudb.fgedu_orders WHERE user_id = u.id) AS order_count
FROM fgedudb.fgedu_users u;
### 优化后
CREATE TEMPORARY TABLE fgedu_temp_order_stats (
user_id INT PRIMARY KEY,
total_amount DECIMAL(10,2),
order_count INT
);
INSERT INTO fgedu_temp_order_stats
SELECT user_id, SUM(amount), COUNT(*)
FROM fgedudb.fgedu_orders
GROUP BY user_id;
SELECT u.id, u.name, s.total_amount, s.order_count
FROM fgedudb.fgedu_users u
LEFT JOIN fgedu_temp_order_stats s ON u.id = s.user_id;
## 案例2：CTE优化
### 优化前
SELECT *
FROM (
SELECT
id, name,
(SELECT COUNT(*) FROM fgedudb.fgedu_orders WHERE user_id = u.id) AS order_count
FROM fgedudb.fgedu_users u
) AS sub
WHERE order_count > 5;
### 优化后
WITH fgedu_cte_user_orders AS (
SELECT user_id, COUNT(*) AS order_count
FROM fgedudb.fgedu_orders
GROUP BY user_id
)
SELECT u.id, u.name, o.order_count
FROM fgedudb.fgedu_users u
JOIN fgedu_cte_user_orders o ON u.id = o.user_id
WHERE o.order_count > 5;
## 案例3：临时表与CTE结合优化
WITH fgedu_cte_order_stats AS (
SELECT user_id, SUM(amount) AS total_amount
FROM fgedudb.fgedu_orders
GROUP BY user_id
)
SELECT *
FROM fgedu_cte_order_stats
WHERE total_amount > 1000;
Part05-风哥经验总结与分享
5.1 最佳实践
最佳实践：
## 1. 临时表最佳实践
– 只在必要时使用临时表
– 合理设置临时表的大小和索引
– 及时清理不再使用的临时表
– 避免在临时表上进行大量操作
– 使用合适的临时表类型（会话级或事务级）
## 2. CTE最佳实践
– 避免过度使用CTE
– 控制CTE的复杂度和嵌套深度
– 合理使用递归CTE
– 为CTE中的查询创建合适的索引
– 监控CTE的执行性能
## 3. 性能优化最佳实践
– 分析查询执行计划，识别性能瓶颈
– 合理使用临时表和CTE，避免过度使用
– 优化临时表和CTE的查询语句
– 调整系统参数，提高临时表和CTE的性能
– 定期监控和优化临时表和CTE的使用
## 4. 命名规范最佳实践
– 使用有意义的名称，便于理解
– 统一命名格式，便于管理
– 避免与普通表重名，减少混淆
– 使用前缀标识临时表和CTE
## 5. 维护最佳实践
– 定期清理临时表和CTE
– 监控临时表和CTE的使用情况
– 分析慢查询日志，识别性能问题
– 定期优化临时表和CTE的使用
– 建立临时表和CTE的使用规范
5.2 常见问题与解决方案
常见问题与解决方案：
## 1. 临时表相关问题
### 问题1：临时表空间不足
– 原因：临时表大小超过系统限制
– 解决方案：调整tmp_table_size和max_heap_table_size参数，或使用磁盘临时表
### 问题2：临时表索引失效
– 原因：索引设计不合理或统计信息不准确
– 解决方案：重新设计索引，更新统计信息
### 问题3：临时表性能下降
– 原因：临时表数据量过大或查询语句优化不当
– 解决方案：优化查询语句，合理使用索引，考虑使用分区临时表
## 2. CTE相关问题
### 问题1：CTE执行时间过长
– 原因：CTE查询复杂度高或数据量过大
– 解决方案：分解CTE，优化查询语句，创建合适的索引
### 问题2：递归CTE无限递归
– 原因：递归条件设置不当
– 解决方案：设置合理的终止条件，控制递归深度
### 问题3：CTE内存使用过高
– 原因：CTE结果集过大
– 解决方案：限制CTE结果集大小，使用分页查询
## 3. 性能相关问题
### 问题1：查询性能下降
– 原因：临时表和CTE使用不当
– 解决方案：分析执行计划，优化查询语句，合理使用临时表和CTE
### 问题2：系统资源占用过高
– 原因：临时表和CTE使用过多
– 解决方案：减少临时表和CTE的使用，优化系统参数
### 问题3：并发性能下降
– 原因：临时表和CTE的锁竞争
– 解决方案：减少临时表和CTE的使用，优化并发控制
5.3 未来发展
未来发展：
## 1. 临时表发展趋势
– 内存临时表的优化：提高内存使用效率，支持更大的内存临时表
– 临时表的并行处理：支持多线程处理临时表数据
– 临时表的自动优化：根据数据量和查询模式自动选择最优的临时表类型
## 2. CTE发展趋势
– CTE的并行执行：支持CTE的并行处理，提高查询性能
– CTE的缓存机制：缓存CTE结果集，避免重复计算
– CTE的自动优化：根据查询模式自动优化CTE的执行计划
## 3. 优化技术发展
– 智能化优化：使用机器学习等技术自动优化临时表和CTE的使用
– 自适应优化：根据系统负载和数据分布自动调整优化策略
– 实时优化：在查询执行过程中动态调整优化策略
## 4. 工具支持发展
– 可视化工具：提供临时表和CTE的可视化管理工具
– 监控工具：提供临时表和CTE的实时监控和分析工具
– 优化工具：提供临时表和CTE的自动优化工具
## 5. 生态系统发展
– 与其他数据库的兼容性：提高临时表和CTE与其他数据库的兼容性
– 与大数据技术的集成：支持临时表和CTE与Hadoop、Spark等大数据技术的集成
– 与云服务的集成：支持临时表和CTE在云环境中的优化使用
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
