MySQL创建临时表及内存与磁盘临时表区别

创建MySQL临时表的方法是使用CREATE TEMPORARY TABLE语句，1.语法与普通表相同但需添加TEMPORARY关键字；2.临时表在会话结束后自动删除；3.不同会话可创建同名表互不影响；4.用户需拥有CREATE TEMPORARY TABLES权限；5.磁盘空间不足或tmpdir配置问题会导致创建失败；6.内存临时表适用于小数据量、速度快，磁盘临时表适合大数据量或含BLOB/TEXT字段；7.可通过EXPLAIN命令查看是否使用临时表及类型；8.优化技巧包括避免复杂WHERE条件、合理设置tmp_table_size和max_heap_table_size、使用索引、避免BLOB/TEXT字段；9.监控可使用SHOW GLOBAL STATUS、PERFORMANCE_SCHEMA或第三方工具；10.存储过程中可创建临时表处理中间结果，函数中受限；11.相比子查询，临时表更适合多次使用或大数据量场景；12.MySQL 8.0支持WITH子句、CREATE OR REPLACE语法并优化存储引擎提升性能。

创建MySQL临时表，本质上就是创建一个只在当前会话存在的表。会话断开，表自动消失，数据也随之不见。它像一个草稿本，用完就扔，非常适合处理一些中间结果集，避免污染正式的数据库表。

创建临时表使用 CREATE TEMPORARY TABLE 语句。语法和创建普通表几乎一样，只是多了 TEMPORARY 关键字。

CREATE TEMPORARY TABLE temp_orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO temp_orders (order_id, customer_id, order_date, total_amount)
SELECT order_id, customer_id, order_date, total_amount FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

-- 使用临时表进行查询
SELECT customer_id, SUM(total_amount) AS total_spent
FROM temp_orders
GROUP BY customer_id
ORDER BY total_spent DESC;

-- 会话结束，临时表自动删除

临时表最大的好处是隔离性。不同会话可以创建同名的临时表，互不影响。这在并发环境下非常有用，避免了数据冲突。

MySQL临时表创建失败的常见原因及解决方法

临时表创建失败，除了语法错误，更常见的原因是权限问题。用户需要拥有 CREATE TEMPORARY TABLES 权限。另外，如果磁盘空间不足，也可能导致创建失败。

解决方法也很简单：

1. 检查语法：仔细核对 SQL 语句，特别是表名、字段类型等。
2. 检查权限：使用 SHOW GRANTS FOR 'your_user'@'your_host'; 查看用户权限，如果没有 CREATE TEMPORARY TABLES 权限，需要管理员授权。
3. 检查磁盘空间：使用 df -h 命令查看磁盘空间，如果空间不足，需要清理磁盘或增加磁盘空间。
4. 检查 tmpdir 配置：临时表默认存储在 tmpdir 指定的目录下。如果该目录不存在或权限不足，也会导致创建失败。可以在 MySQL 配置文件（my.cnf 或 my.ini）中修改 tmpdir 的值。
5. 并发冲突：在高并发环境下，如果多个会话同时创建同名的临时表，可能会出现冲突。可以尝试使用更复杂的表名，或者使用锁机制避免冲突。

一个容易被忽略的点是，如果临时表依赖于其他表，而你没有这些表的访问权限，创建过程也会失败。

内存临时表与磁盘临时表：性能差异与选择策略

内存临时表和磁盘临时表，顾名思义，一个存储在内存中，一个存储在磁盘上。 性能差异巨大。内存临时表速度飞快，但受限于内存大小；磁盘临时表速度较慢，但可以存储更多数据。

MySQL 决定使用哪种类型的临时表，主要取决于几个因素：

• 临时表的大小：如果临时表的大小超过 tmp_table_size 和 max_heap_table_size 这两个参数的最小值，MySQL 会自动将内存临时表转换为磁盘临时表。
• 字段类型：如果临时表包含 BLOB 或 TEXT 类型的字段，MySQL 也会直接使用磁盘临时表。
• 索引：内存临时表不支持 BLOB 和 TEXT 类型的字段建立索引，如果需要对这些字段进行索引，只能使用磁盘临时表。

选择策略也很简单：

• 小数据量：优先使用内存临时表，速度更快。
• 大数据量：只能使用磁盘临时表。
• 需要对 BLOB 或 TEXT 类型的字段进行索引：只能使用磁盘临时表。

可以通过 EXPLAIN 命令查看 MySQL 是否使用了临时表，以及使用了哪种类型的临时表。

EXPLAIN SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY customer_id
ORDER BY total_spent DESC;

如果 Extra 列包含 Using temporary，则表示使用了临时表。如果还包含 Using filesort，则表示使用了磁盘临时表。

优化临时表性能：避免磁盘临时表的出现

尽可能避免使用磁盘临时表，是优化 SQL 性能的关键。以下是一些常用的优化技巧：

1. 优化 SQL 语句：避免在 WHERE 子句中使用复杂的表达式或函数，尽量使用索引。
2. 增加 tmp_table_size 和 max_heap_table_size 的值：增加内存临时表的最大大小，减少转换为磁盘临时表的可能性。但要注意，这两个参数的值不能设置过大，否则会占用过多的内存。
3. 使用索引：在经常用于 GROUP BY 和 ORDER BY 的字段上建立索引，可以避免使用临时表。
4. 避免使用 BLOB 和 TEXT 类型的字段：尽量使用 VARCHAR 或 TEXT 类型的字段代替 BLOB 和 TEXT 类型的字段。
5. 使用 SQL_BIG_RESULT 或 SQL_SMALL_RESULT 提示：SQL_BIG_RESULT 告诉 MySQL 结果集可能会很大，应该使用磁盘临时表；SQL_SMALL_RESULT 告诉 MySQL 结果集可能会很小，应该使用内存临时表。

需要注意的是，过度优化也可能导致性能下降。例如，过度增加 tmp_table_size 和 max_heap_table_size 的值，可能会导致内存不足，反而降低性能。因此，需要根据实际情况进行调整。

如何监控MySQL临时表的使用情况？

监控 MySQL 临时表的使用情况，可以帮助我们发现潜在的性能问题。

1. 使用 SHOW GLOBAL STATUS LIKE 'Created_tmp%'; 命令查看全局临时表的统计信息。

   SHOW GLOBAL STATUS LIKE 'Created_tmp%';

   • Created_tmp_disk_tables：表示创建的磁盘临时表的数量。
   • Created_tmp_tables：表示创建的内存临时表的数量。

   如果 Created_tmp_disk_tables 的值很高，则表示存在大量的磁盘临时表，需要进行优化。

2. 使用 PERFORMANCE_SCHEMA 数据库：PERFORMANCE_SCHEMA 数据库提供了更详细的临时表信息。

   SELECT
       OBJECT_NAME,
       SUM(IF(OBJECT_TYPE='TABLE',1,0)) AS TABLES,
       SUM(IF(OBJECT_TYPE='TABLE',COUNT,0)) AS TABLE_COUNT,
       SUM(IF(OBJECT_TYPE='TABLE',SUM_TIMER_WAIT,0)) AS TABLE_SUM_TIMER_WAIT
   FROM performance_schema.table_io_waits_summary_by_index_usage
   WHERE INDEX_NAME IS NULL AND OBJECT_SCHEMA = 'your_database'
   GROUP BY OBJECT_NAME
   ORDER BY TABLE_SUM_TIMER_WAIT DESC;

   这个查询可以显示每个表的 I/O 等待时间，可以帮助我们找到哪些表使用了大量的磁盘 I/O。

3. 使用第三方监控工具：例如 Prometheus、Grafana 等。这些工具可以提供更全面的监控指标，例如 CPU 使用率、内存使用率、磁盘 I/O 等。

监控临时表的使用情况，是一个持续的过程。需要定期检查，并根据实际情况进行调整。

临时表在存储过程和函数中的应用场景

存储过程和函数中，临时表可以发挥很大的作用。比如，在复杂的报表统计中，可以将中间结果存储在临时表中，然后进行进一步的计算。

DELIMITER //
CREATE PROCEDURE CalculateMonthlySales(IN year INT, IN month INT)
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS MonthlyOrders (
        order_id INT PRIMARY KEY,
        customer_id INT,
        total_amount DECIMAL(10, 2)
    );

    -- 清空临时表
    TRUNCATE TABLE MonthlyOrders;

    -- 插入当月订单数据
    INSERT INTO MonthlyOrders (order_id, customer_id, total_amount)
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE YEAR(order_date) = year AND MONTH(order_date) = month;

    -- 计算总销售额
    SELECT SUM(total_amount) AS total_sales
    FROM MonthlyOrders;

    -- 删除临时表（可选，存储过程结束会自动删除）
    -- DROP TEMPORARY TABLE MonthlyOrders;
END //
DELIMITER ;

-- 调用存储过程
CALL CalculateMonthlySales(2023, 01);

在这个例子中，MonthlyOrders 临时表用于存储当月的订单数据。存储过程首先清空临时表，然后插入当月订单数据，最后计算总销售额。

需要注意的是，在存储过程中创建的临时表，在存储过程执行完毕后会自动删除。因此，如果需要在存储过程外部访问临时表，需要手动删除临时表。

另外，在函数中使用临时表需要注意一些限制。例如，函数不能修改全局变量，因此不能在函数中创建或删除临时表。

临时表与子查询：性能对比与最佳实践

临时表和子查询都可以用于处理中间结果集，但它们的性能差异很大。

• 临时表：将中间结果存储在临时表中，可以避免重复计算。临时表可以被多个查询使用，因此可以提高性能。
• 子查询：每次执行查询时，都需要重新计算子查询的结果。如果子查询的结果集很大，或者子查询的执行时间很长，则会导致性能下降。

一般来说，如果子查询只被使用一次，且结果集很小，则可以使用子查询。如果子查询被多次使用，或者结果集很大，则应该使用临时表。

以下是一个使用临时表代替子查询的例子：

-- 使用子查询
SELECT customer_id, (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) AS order_count
FROM customers c;

-- 使用临时表
CREATE TEMPORARY TABLE IF NOT EXISTS CustomerOrderCount (
    customer_id INT PRIMARY KEY,
    order_count INT
);

INSERT INTO CustomerOrderCount (customer_id, order_count)
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;

SELECT c.customer_id, coc.order_count
FROM customers c
LEFT JOIN CustomerOrderCount coc ON c.customer_id = coc.customer_id;

DROP TEMPORARY TABLE CustomerOrderCount;

在这个例子中，使用临时表 CustomerOrderCount 存储每个客户的订单数量，然后将客户表和临时表进行连接，得到每个客户的订单数量。使用临时表可以避免重复计算子查询的结果，从而提高性能。

最佳实践是，在复杂的查询中，优先考虑使用临时表，特别是当子查询被多次使用，或者结果集很大时。

MySQL 8.0 对临时表的改进

MySQL 8.0 对临时表进行了多项改进，包括：

• 支持 TEMPORARY TABLE 的 WITH 子句：可以在 WITH 子句中创建临时表，使 SQL 语句更简洁。
• 支持 TEMPORARY TABLE 的 CREATE OR REPLACE 语法：如果临时表已经存在，则会先删除临时表，然后重新创建临时表。
• 优化了临时表的存储引擎：MySQL 8.0 使用更高效的存储引擎存储临时表，提高了性能。

这些改进使得临时表更加易于使用，性能也更高。

总的来说，临时表是 MySQL 中一个非常重要的功能。掌握临时表的使用技巧，可以帮助我们编写更高效的 SQL 语句。