MySQL高级讲座篇之：MySQL的`Global Temporary Tables`：性能影响与适用场景。

大家好，我是你们的老朋友，今天咱们来聊点MySQL里有点意思的东西：Global Temporary Tables，也就是全局临时表。

这玩意儿，听起来挺高大上的，但其实用起来也挺接地气。别害怕，咱们把它拆开了揉碎了，保证你听完之后能玩得转。

一、啥是Global Temporary Tables？跟普通临时表有啥区别？

先说临时表，这玩意儿大家都知道，就是临时的，用完就扔的表。MySQL里有两种临时表：

• Session Temporary Tables（会话临时表）： 这是最常见的临时表，用CREATE TEMPORARY TABLE创建。每个MySQL客户端连接（session）只能看到自己创建的会话临时表。连接断开，表自动消失。
• Global Temporary Tables（全局临时表）： 这就是咱们今天的主角。用CREATE GLOBAL TEMPORARY TABLE创建。听名字就知道，好像很厉害的样子。

那区别在哪儿呢？

特性	Session Temporary Tables (会话临时表)	Global Temporary Tables (全局临时表)
创建方式	CREATE TEMPORARY TABLE	CREATE GLOBAL TEMPORARY TABLE
可见性	仅创建它的会话可见	所有会话可见，但每个会话只能看到自己的数据
数据隔离	会话之间数据完全隔离	会话之间数据隔离，每个会话独立存储数据
表结构定义	每个会话可以自定义表结构	表结构必须相同，由创建者定义
生命周期	会话结束时自动删除	MySQL服务重启时自动删除
存储引擎选择	可以选择不同的存储引擎，如MEMORY, InnoDB	只能使用InnoDB存储引擎

总结一下：

• CREATE GLOBAL TEMPORARY TABLE 创建的表，表结构是全局可见的。也就是说，任何连接到MySQL服务器的客户端都可以看到这个表的定义（列名、数据类型等）。
• 但是，每个客户端连接只能看到自己在这个表里插入的数据。 就像一个公共的容器，每个人都可以往里面放东西，但是别人看不到你放的是啥。
• Global Temporary Tables 存储在InnoDB引擎中，即使你指定了MEMORY引擎，最终也会被InnoDB管理。
• Global Temporary Tables 的生命周期是整个MySQL服务重启。也就是说，一旦MySQL服务重启，所有的Global Temporary Tables都会被自动删除。

二、语法，怎么创建和使用？

创建Global Temporary Tables的语法很简单：

CREATE GLOBAL TEMPORARY TABLE my_global_temp_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    value INT
);

注意：

• 必须要有GLOBAL关键字。
• 表名必须是唯一的，不能和现有表或者视图重名。
• 每个会话都可以执行CREATE GLOBAL TEMPORARY TABLE，即使表已经存在。但这只会影响当前会话，不会报错。

使用Global Temporary Tables和普通表一样：

-- 会话A
INSERT INTO my_global_temp_table (name, value) VALUES ('Alice', 10);
SELECT * FROM my_global_temp_table; -- 只能看到Alice的数据

-- 会话B
INSERT INTO my_global_temp_table (name, value) VALUES ('Bob', 20);
SELECT * FROM my_global_temp_table; -- 只能看到Bob的数据

-- 会话A 再次查询
SELECT * FROM my_global_temp_table; -- 还是只能看到Alice的数据

可以看到，虽然两个会话都使用了同一个表名，但它们的数据是完全隔离的。

你也可以像普通表一样，对Global Temporary Tables进行索引、更新、删除等操作。

-- 创建索引
CREATE INDEX idx_name ON my_global_temp_table (name);

-- 更新数据
UPDATE my_global_temp_table SET value = 15 WHERE name = 'Alice';

-- 删除数据
DELETE FROM my_global_temp_table WHERE name = 'Bob';

三、性能影响：这玩意儿快不快？

这才是大家最关心的问题。Global Temporary Tables的性能，不能一概而论，要看具体的使用场景。

• 优点：

  • 减少磁盘I/O： Global Temporary Tables的数据存储在InnoDB的临时表空间中，可以减少对磁盘的读写操作，提高查询效率。
  • 避免锁冲突： 每个会话只能访问自己的数据，可以避免多个会话之间的锁冲突，提高并发性能。
  • 简化复杂查询： 可以将复杂的查询逻辑拆分成多个步骤，将中间结果存储在Global Temporary Tables中，简化查询语句，提高可读性和可维护性。

• 缺点：

  • InnoDB存储引擎限制： 只能使用InnoDB存储引擎，无法利用MEMORY引擎的快速访问特性。
  • 无法持久化： 数据无法持久化，MySQL服务重启后数据丢失。
  • 复杂查询优化： 对于非常复杂的查询，Global Temporary Tables可能不是最优选择，需要仔细评估。

记住一条黄金法则： 任何优化都是有代价的。 使用Global Temporary Tables之前，一定要先评估其带来的性能提升是否大于其带来的额外开销。

四、适用场景：啥时候用它最爽？

Global Temporary Tables 的适用场景主要集中在以下几个方面：

1. 复杂查询的中间结果存储：

   当你的查询语句非常复杂，涉及到多个表的连接、子查询、聚合等操作时，可以将中间结果存储在Global Temporary Tables中，简化查询逻辑，提高可读性和可维护性。

   例如：你需要统计每个部门的平均工资，并且筛选出平均工资高于公司平均工资的部门。

   -- 计算公司平均工资
   CREATE GLOBAL TEMPORARY TABLE company_avg_salary AS
   SELECT AVG(salary) AS avg_salary FROM employees;
   
   -- 计算每个部门的平均工资
   CREATE GLOBAL TEMPORARY TABLE dept_avg_salary AS
   SELECT department_id, AVG(salary) AS avg_salary
   FROM employees
   GROUP BY department_id;
   
   -- 筛选出平均工资高于公司平均工资的部门
   SELECT d.department_id, d.avg_salary
   FROM dept_avg_salary d
   JOIN company_avg_salary c ON d.avg_salary > c.avg_salary;
   
   -- 注意：MySQL 8.0 之后 CTE (Common Table Expressions) 更适合这种场景，可读性更好，性能也可能更好。

2. 存储过程或函数中的临时数据：

   Global Temporary Tables可以在存储过程或函数中使用，存储临时数据，方便后续的逻辑处理。

   例如：创建一个存储过程，用于计算某个订单的总金额和优惠金额。

   DELIMITER //
   CREATE PROCEDURE calculate_order_amount(IN order_id INT)
   BEGIN
       -- 创建临时表存储订单明细
       CREATE GLOBAL TEMPORARY TABLE order_details AS
       SELECT product_id, quantity, price
       FROM order_items
       WHERE order_id = order_id;
   
       -- 计算总金额
       SELECT SUM(quantity * price) INTO @total_amount
       FROM order_details;
   
       -- 计算优惠金额 (假设有优惠券)
       SELECT discount_amount INTO @discount_amount
       FROM coupons
       WHERE order_id = order_id;
   
       -- 返回结果
       SELECT @total_amount AS total_amount, @discount_amount AS discount_amount;
   
       -- 删除临时表 (可选，MySQL重启会自动删除)
       -- DROP TEMPORARY TABLE order_details;
   END //
   DELIMITER ;
   
   CALL calculate_order_amount(123);

3. 数据转换和清洗：

   Global Temporary Tables 可以用于数据转换和清洗，将原始数据转换成目标格式，或者过滤掉不符合要求的数据。

   例如：你需要将一个包含多个字段的CSV文件导入到MySQL数据库，但是CSV文件中的数据格式不符合数据库的要求，可以使用Global Temporary Tables进行数据转换。

   -- 假设CSV文件已经导入到临时表 raw_data
   
   -- 创建目标表
   CREATE TABLE target_table (
       id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(255),
       age INT,
       email VARCHAR(255)
   );
   
   -- 创建Global Temporary Table进行数据转换
   CREATE GLOBAL TEMPORARY TABLE transformed_data AS
   SELECT
       CAST(column1 AS INT) AS id,
       column2 AS name,
       CAST(column3 AS INT) AS age,
       column4 AS email
   FROM raw_data
   WHERE column3 > 0; -- 过滤掉年龄小于0的数据
   
   -- 将转换后的数据插入到目标表
   INSERT INTO target_table (id, name, age, email)
   SELECT id, name, age, email FROM transformed_data;
   
   -- 删除Global Temporary Table (可选，MySQL重启会自动删除)
   -- DROP TEMPORARY TABLE transformed_data;

4. 测试和调试：

   Global Temporary Tables 可以用于测试和调试，模拟各种场景，验证代码的正确性。

   例如：你需要测试一个存储过程，但是不想修改现有的数据，可以使用Global Temporary Tables创建一个临时表，模拟数据，然后调用存储过程进行测试。

   -- 创建临时表模拟数据
   CREATE GLOBAL TEMPORARY TABLE test_data (
       id INT PRIMARY KEY,
       name VARCHAR(255),
       value INT
   );
   
   -- 插入测试数据
   INSERT INTO test_data (id, name, value) VALUES
   (1, 'A', 10),
   (2, 'B', 20),
   (3, 'C', 30);
   
   -- 调用存储过程进行测试
   CALL my_procedure(1);
   
   -- 查看结果
   SELECT * FROM test_data;
   
   -- 删除临时表 (可选，MySQL重启会自动删除)
   -- DROP TEMPORARY TABLE test_data;

五、注意事项：踩坑指南

1. 表名冲突：

   Global Temporary Tables 的表名是全局可见的，因此要避免表名冲突。建议使用具有唯一性的表名，例如加上会话ID或者时间戳。

2. 数据类型不匹配：

   在创建Global Temporary Tables时，要确保数据类型与原始数据类型匹配，避免数据转换错误。

3. 索引优化：

   如果Global Temporary Tables的数据量较大，可以考虑创建索引，提高查询效率。

4. 生命周期管理：

   Global Temporary Tables 的生命周期是MySQL服务重启，因此要注意数据的持久化问题。如果需要长期保存数据，应该将数据插入到普通表中。

5. 并发访问：

   虽然每个会话只能访问自己的数据，但是多个会话同时访问同一个Global Temporary Tables仍然可能导致性能问题。要尽量避免在高并发场景下使用Global Temporary Tables。

6. 存储引擎选择：

   Global Temporary Tables 只能使用InnoDB存储引擎，无法利用MEMORY引擎的快速访问特性。

7. MySQL版本兼容性：

   Global Temporary Tables 在MySQL 5.7 及以上版本支持较好，早期版本可能存在一些问题。

8. 不要过度使用：

   Global Temporary Tables 并不是万能的，过度使用可能会导致性能问题。要根据实际情况选择合适的解决方案。 很多时候，更好的索引，更优化的SQL查询，或者使用CTE，都能达到更好的效果。

六、实战案例：一个稍微复杂点的例子

假设我们有一个电商网站，需要统计每个用户的订单数量和订单总金额，并且筛选出订单数量超过5个，并且订单总金额超过1000元的优质用户。

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- 插入一些测试数据
INSERT INTO orders (user_id, order_date, total_amount) VALUES
(1, '2023-01-01', 100.00),
(1, '2023-01-05', 200.00),
(1, '2023-01-10', 300.00),
(1, '2023-01-15', 400.00),
(1, '2023-01-20', 500.00),
(1, '2023-01-25', 600.00),
(2, '2023-02-01', 100.00),
(2, '2023-02-05', 200.00),
(2, '2023-02-10', 300.00);

-- 使用Global Temporary Tables统计每个用户的订单数量和订单总金额
CREATE GLOBAL TEMPORARY TABLE user_order_stats AS
SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_amount
FROM orders
GROUP BY user_id;

-- 筛选出订单数量超过5个，并且订单总金额超过1000元的优质用户
SELECT
    user_id,
    order_count,
    total_amount
FROM user_order_stats
WHERE order_count > 5 AND total_amount > 1000;

-- 删除Global Temporary Table (可选，MySQL重启会自动删除)
-- DROP TEMPORARY TABLE user_order_stats;

这个例子展示了如何使用Global Temporary Tables进行数据统计和筛选，简化了复杂的查询逻辑。

七、总结：用还是不用，看情况！

Global Temporary Tables 是MySQL提供的一种非常有用的工具，可以简化复杂查询，提高性能，但是也存在一些限制。

• 在决定使用Global Temporary Tables 之前，一定要仔细评估其带来的性能提升是否大于其带来的额外开销。
• 要根据实际情况选择合适的解决方案，不要过度依赖Global Temporary Tables。
• 要充分了解Global Temporary Tables 的使用方法和注意事项，避免踩坑。

记住，没有银弹！ 只有最适合你的方案！

好了，今天的讲座就到这里。希望大家以后在面对复杂SQL的时候，能想起今天讲的内容，灵活运用Global Temporary Tables，让你的SQL代码更优雅，性能更卓越！ 咱们下次再见！