请教mysql查询时创建中间表的效率问题？

Question

数据库有一个学生表t_student有两个字段 id, name
我想找出name相同的所有数据的id，并根据name排序，好做对比。如

id	name
1	张三
2	李四
3	张三
4	王五
5	王五

我想找出的数据是

id	name
1	张三
3	张三
4	王五
5	王五

写下的sql如下

select id, name from t_student where name in (select name from t_student where name is not null group by name having count(*) > 1) order by name;

虽然能得到想要的数据，但是查询的速度很慢。要6s左右。
后面发现这种写法，在数据量大时，查询时间能缩短10倍以上。由6s -> 0.6s。而区别是多了个中间表。

select id, name from t_student where name in (select t.name from (select name from t_student where name is not null group by name having count(*) > 1) as t) order by name;

为了判断是否是中间表的创建导致的查询效率的提升，我换了一种写法

select ts.id, ts.name from t_student ts inner join (select t.name from t_student where name is not null group by name having count(*) > 1) as t on ts.name = t.name order by name;

通过创建中间表然后join的方式，还是很快，大概也是0.6s。
我想知道为什么这样，创建中间表为什么会比不创建要快这么多

Answer 1

在MySQL中，查询的效率取决于多种因素，包括表的大小、索引的使用、查询的复杂性以及数据库服务器的配置等。对于您提出的问题，以下是关于创建中间表与查询效率的一些解释：

1. 查询优化器：MySQL的查询优化器会根据查询的复杂性自动选择最佳的查询执行计划。在某些情况下，优化器可能无法选择最有效的查询方式，特别是当子查询的复杂性较高时。
2. 临时表的使用：在某些情况下，MySQL可能会使用临时表来存储中间结果，以提高查询性能。例如，当使用GROUP BY子句时，MySQL可能会创建一个临时表来存储分组结果。这种临时表的使用可以提高查询性能，因为它可以减少对原始表的重复扫描。
3. 中间表的选择性：在您的第二个查询中，您创建了一个中间表，其中包含不重复的name值。这个中间表的选择性更高（即，它包含的数据量更小），因此查询优化器可能更容易处理它，从而提高查询效率。
4. 索引的使用：确保查询中用到的字段已经建立了适当的索引，可以提高查询效率。如果没有适当的索引，数据库可能需要执行全表扫描，这通常会降低查询性能。
5. 查询的复杂性：在您的第一个查询中，您使用了嵌套子查询。这种复杂的查询结构可能导致查询优化器难以选择最佳的执行计划。通过将子查询的结果存储在临时表中，您可以简化查询结构，从而可能提高查询性能。

总的来说，创建中间表可以提高查询效率，但这并不是唯一的因素。确保正确使用索引、简化查询结构、优化数据库配置等也是提高查询效率的重要方面。如果您发现查询仍然很慢，建议使用EXPLAIN语句来分析查询的执行计划，以便更好地了解性能瓶颈并采取相应的优化措施。