带你慢慢摸索Mysql临时表那些不太明显但挺重要的细节和用法

说到MySQL的临时表,很多人可能只知道它是在内存或磁盘上临时存放数据用的，会话结束就没了，但真要把它用得好，用得巧，里面有不少细节值得慢慢琢磨，这些细节往往决定了你的查询是快如闪电，还是慢如蜗牛，甚至会影响数据的正确性。

最容易被忽略的一点是,临时表的作用域和可见性，根据MySQL官方文档的描述，临时表只在创建它的那个数据库连接（也就是会话）中可见，这意味着，你和另一个人同时连接到同一个数据库，各自创建的临时表是互相完全看不见的，名字就算一模一样也没关系，这听起来简单，但好处巨大，你在一个复杂的存储过程或者应用程序的某个会话里，可以放心大胆地用CREATE TEMPORARY TABLE tmp_orders ...这样的名字，完全不用担心其他会话会干扰你，或者你需要起一个又长又绕的名字来避免冲突，这种天然的“会话隔离”是临时表最大的优点之一。

这里有个重要的细节：在同一个会话内，临时表的名字不能和已经存在的普通表同名，但一旦创建了临时表，它会“遮蔽”同名的普通表，举个例子，假设你有一个普通的user表，你在会话里又创建了一个临时表也叫user，从创建那一刻起，在这个会话里，你所有对user表的操作（比如SELECT, INSERT）都会指向那个临时表，而不是原来的普通表，直到你删除这个临时表或者会话结束，原来的user表才会重新“可见”，这个特性非常有用，比如你可以临时创建一个结构一样的空表来做一些测试，而完全不影响生产数据，但反过来，这也可能是个陷阱，如果你不小心创建了和正式表同名的临时表，可能会导致数据写错地方，需要特别小心。

接下来说说临时表的存储引擎，MySQL可以让你指定临时表用什么存储引擎，比如Memory（内存）、MyISAM或InnoDB，但默认行为是很有意思的，在MySQL 5.7及之前，默认的临时表引擎是MyISAM，而从MySQL 8.0开始，默认变成了InnoDB，这个变化背后有原因，MyISAM引擎的临时表虽然创建速度快，但它不支持事务，也不支持行级锁，如果你的临时表操作涉及到复杂的、需要回滚的事务，或者有并发操作（比如在一个会话内，多个子查询或存储过程过程同时读写同一个临时表），用MyISAM就可能出问题，而InnoDB临时表支持事务，更安全，了解你的MySQL版本，并根据你的需求（是追求极速创建还是需要事务安全）来考虑是否要显式地指定存储引擎（例如CREATE TEMPORARY TABLE ... ENGINE=MEMORY），是一个进阶的用法。

然后是一个性能上的关键点：临时表到底存在哪里？ 很多人以为临时表都在内存里，所以一定快，其实不然，MySQL有一个叫做tmp_table_size的系统变量，它设定了一个阈值，当临时表的大小（包括初始创建和后续插入数据后）小于这个值时，MySQL会尽量把它放在内存中（使用Memory引擎），一旦临时表的大小超过了这个阈值，或者其结构包含了MEMORY引擎不支持的数据类型（比如TEXT或BLOB类型），MySQL就会自动在磁盘上创建一个同样结构的临时表（通常是InnoDB或MyISAM引擎），然后把内存里的数据转过去，后续操作就在磁盘上进行了，这个从内存到磁盘的转换过程是有性能开销的，如果你明知道要处理的临时数据量很大（比如几十万行），与其让MySQL自己判断和转换，不如一开始就主动创建磁盘临时表（比如指定ENGINE=InnoDB），或者尝试优化你的SQL语句（比如增加合适的索引到临时表上），来避免中间转换的损耗，监控Created_tmp_disk_tables这个状态变量，可以帮你了解有多少临时表被创建在了磁盘上，是性能调优的一个重要参考。

聊聊临时表一个非常实用的场景：优化复杂查询，有些查询非常复杂，可能涉及多个子查询、UNION操作或者复杂的GROUP BY和排序，MySQL的查询优化器有时会“卡住”，无法生成最优的执行计划，这时候，一个有经验的开发者会尝试“化繁为简”，使用临时表作为中间步骤，你可以分三步走：第一步，先把一个复杂的子查询SELECT ... FROM A WHERE ...的结果存到一个临时表tmp_stage1中，并可能为它加上一个针对后续连接查询的索引，第二步，再用另一个查询SELECT ... FROM B JOIN tmp_stage1 ON ...，这样做，相当于你手动帮查询优化器做了决策，把一个大查询拆解成几个步骤清晰、易于优化的小查询，这种方法在处理海量数据或者逻辑极其复杂的报表查询时，效果尤其显著，往往能带来性能的成倍提升。

MySQL的临时表远不止一个“临时存放数据”的工具，理解它的作用域隔离、对正式表的遮蔽效应、存储引擎的选择策略、内存与磁盘的使用机制，以及它在查询分解优化中的妙用，能让你在解决特定问题时多一把得心应手的利器。