发布于 由admin

MySQL高阶讲座之：`MySQL`的`Temp Table`：内部临时表与外部临时表的生成时机与性能。

各位程序猿、攻城狮、代码界的段子手们，晚上好！我是老码农，今儿个咱们聊聊MySQL里那些“隐形英雄”——临时表（Temp Table）。

别看“临时”俩字，它们在SQL优化里可是扛把子级别的存在。搞清楚临时表的生成时机和性能，能让你写的SQL跑得更快，姿势更优雅，面试的时候也能侃侃而谈，秒杀面试官。

这次咱们主要扒一扒MySQL临时表的底裤：内部临时表和外部临时表的生成时机，以及它们对性能的影响。保证让你听完之后，不仅能写出更高质量的SQL，还能在深夜里默默欣赏代码运行时的美妙。

一、啥是临时表？为啥要有它？

简单来说，临时表就是MySQL在执行SQL语句时，为了存储中间结果而创建的表格。就像你做饭时，切好的菜先放在盘子里，这个盘子就相当于临时表。

为啥需要临时表？因为有些SQL操作，比如排序、分组、UNION，需要先计算出部分结果，才能进行下一步操作。这些中间结果没地方放，就只能临时创建一个表来存储。

如果没有临时表，MySQL就得把所有中间结果都放在内存里，那内存分分钟爆掉。临时表相当于给MySQL一个缓冲地带，让它可以安心地处理复杂的SQL操作。

二、临时表分类：内存型 vs. 磁盘型

临时表分为两种：

  • 内存临时表 (Memory Temp Table): 速度快，数据存在内存里，断电就没了。适合小数据量的临时存储。

  • 磁盘临时表 (MyISAM/InnoDB Temp Table): 速度相对慢，数据存在磁盘上，断电数据还在。适合大数据量的临时存储。

MySQL会根据数据量和配置自动选择使用哪种类型的临时表。当然，你也可以通过一些SQL技巧来影响MySQL的选择。

三、内部临时表：MySQL自己偷偷建的

内部临时表，顾名思义，就是MySQL在执行SQL语句时，根据需要自动创建的临时表。你可能都没意识到它的存在，但它就在那里默默地工作着。

1. 生成时机：常见的触发条件

以下是一些常见的触发MySQL创建内部临时表的场景：

  • ORDER BY 和 GROUP BY: 当你使用ORDER BY或GROUP BY对结果集进行排序或分组时，如果MySQL认为无法通过索引直接完成排序/分组，就会创建临时表来存储中间结果，然后排序/分组。

    -- 示例：需要创建临时表来排序
SELECT * FROM orders ORDER BY order_date DESC;

-- 示例：需要创建临时表来分组
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

  • UNION 和 UNION ALL: UNION 会去除重复行，而 UNION ALL 不会。UNION 操作通常需要创建一个临时表来存储所有结果，然后再去除重复行。

    -- 示例：需要创建临时表来去重
SELECT col1 FROM table1 UNION SELECT col1 FROM table2;

-- UNION ALL 生成临时表取决于优化器判断
SELECT col1 FROM table1 UNION ALL SELECT col1 FROM table2;

  • 子查询: 有些子查询的结果集需要存储起来，才能用于外部查询。这时，MySQL可能会创建临时表。特别是NOT IN，IN，EXISTS与子查询结合时，更容易产生。

    -- 示例：子查询结果可能需要存入临时表
SELECT * FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name LIKE '%Electronics%');

  • DISTINCT: DISTINCT 用于去除重复行，实现方式通常是先将结果集放入临时表，然后去除重复行。

    -- 示例：需要创建临时表来去重
SELECT DISTINCT product_name FROM products;

  • 多表关联 (JOIN) 的某些情况: 当多表关联的连接条件比较复杂，或者MySQL认为使用临时表可以优化查询时，也会创建临时表。

    -- 示例：复杂的 JOIN 操作可能需要临时表
SELECT o.*, c.* FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date > '2023-01-01';

2. 性能影响：内存 vs. 磁盘，大小是关键

内部临时表的性能影响主要取决于以下几个因素：

  • 临时表类型： 内存临时表速度快，但受内存限制；磁盘临时表速度慢，但可以处理更大的数据量。

  • 临时表大小： 临时表越大，需要的内存或磁盘空间就越多，读写速度也会下降。

  • 索引： 临时表如果能使用索引，可以大大提高查询效率。但MySQL不总是会为临时表创建索引。

3. 如何判断是否使用了临时表？

使用 EXPLAIN 命令可以查看SQL语句的执行计划，其中 Extra 列会显示是否使用了临时表。

EXPLAIN SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

如果 Extra 列包含 Using temporary，就表示使用了临时表。

4. 优化建议：避免不必要的临时表

  • 优化SQL语句： 尽量避免使用复杂的ORDER BY、GROUP BY、UNION和子查询。可以尝试使用更高效的SQL写法。
  • 添加索引： 在相关的列上添加索引，可以帮助MySQL避免使用临时表进行排序和分组。
  • 调整配置： 可以调整MySQL的配置参数，例如 tmp_table_size 和 max_heap_table_size，来控制临时表的大小和类型。
  • 尽量缩小数据集： 在排序和分组之前，尽量使用WHERE子句过滤掉不需要的数据，减少临时表的大小。

四、外部临时表：自己动手，丰衣足食

外部临时表是由用户显式创建的临时表。可以像普通表一样使用，但只在当前会话中有效，会话结束时会自动删除。

1. 创建语法：CREATE TEMPORARY TABLE

-- 创建一个外部临时表
CREATE TEMPORARY TABLE temp_orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

2. 使用场景：复杂的中间结果存储

外部临时表通常用于存储复杂的中间结果，方便后续的查询和处理。例如：

  • 复杂报表: 先将需要的数据放入临时表，然后进行复杂的计算和格式化。
  • 数据清洗: 先将需要清洗的数据放入临时表，然后进行清洗操作。
  • 批量处理: 先将需要处理的数据放入临时表，然后进行批量处理。

3. 性能考量：索引很重要

外部临时表的性能也受到索引的影响。如果在临时表上执行大量的查询操作，建议创建索引来提高查询效率。

-- 为外部临时表创建索引
CREATE INDEX idx_customer_id ON temp_orders (customer_id);

4. 注意事项：会话级别，自动删除

  • 外部临时表只在当前会话中有效，会话结束时会自动删除。
  • 不同会话可以创建同名的外部临时表，它们之间互不影响。
  • 外部临时表不能被其他会话访问。

五、实战演练：优化一个包含临时表的SQL

假设我们有一个需求：统计每个客户的订单总金额，并按照订单总金额降序排列。

1. 原始SQL (可能产生临时表)

SELECT c.customer_id, c.customer_name, SUM(o.total_amount) AS total_order_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY total_order_amount DESC;

这个SQL语句可能会创建临时表，因为需要先进行GROUP BY操作，然后再进行ORDER BY操作。如果orders表数据量很大，临时表可能会很大，影响性能。

2. 优化方案1：添加索引

如果在orders表的customer_id列上添加索引，可以帮助MySQL避免使用临时表进行GROUP BY操作。

CREATE INDEX idx_customer_id ON orders (customer_id);

3. 优化方案2：使用子查询和外部临时表

如果添加索引后性能仍然不理想，可以考虑使用子查询和外部临时表。

-- 创建外部临时表存储每个客户的订单总金额
CREATE TEMPORARY TABLE temp_customer_orders (
    customer_id INT PRIMARY KEY,
    total_order_amount DECIMAL(10, 2)
);

-- 将每个客户的订单总金额插入临时表
INSERT INTO temp_customer_orders (customer_id, total_order_amount)
SELECT customer_id, SUM(total_amount)
FROM orders
GROUP BY customer_id;

-- 从临时表中查询数据，并排序
SELECT c.customer_id, c.customer_name, t.total_order_amount
FROM customers c
JOIN temp_customer_orders t ON c.customer_id = t.customer_id
ORDER BY t.total_order_amount DESC;

这个方案将GROUP BY操作放到一个单独的步骤中，并使用外部临时表存储结果。这样可以避免在主查询中进行复杂的排序操作，提高查询效率。

六、总结：临时表是把双刃剑

临时表是MySQL优化中一个重要的概念。理解临时表的生成时机和性能影响，可以帮助你写出更高效的SQL语句。

  • 内部临时表： MySQL自动创建，用于存储中间结果。尽量避免不必要的内部临时表。
  • 外部临时表： 用户显式创建，用于存储复杂的中间结果。可以提高查询效率，但要注意会话级别和自动删除。
  • 索引： 索引是优化临时表性能的关键。

记住，优化SQL就像谈恋爱，需要耐心，需要技巧，更需要了解对方的脾气秉性。只有深入了解MySQL的内部机制，才能写出让它心悦诚服的高效SQL代码。

今天的讲座就到这里。希望大家以后在写SQL的时候，能够多想想临时表，多一份思考，少一份Bug。祝大家编码愉快，早日成为代码界的巨佬！下课！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注