JOIN 和 IN 之间的 MySQL 性能差异

Question

我想在一个约 500 万行的表中找到所有具有后继者的每小时记录。

我试过了:

SELECT DISTINCT (date_time)
FROM my_table
JOIN (SELECT DISTINCT (DATE_ADD( date_time, INTERVAL 1 HOUR)) date_offset
      FROM my_table) offset_dates
ON date_time = date_offset

和

SELECT DISTINCT(date_time)
FROM my_table
WHERE date_time IN (SELECT DISTINCT(DATE_ADD(date_time, INTERVAL 1 HOUR))
                    FROM my_table)

第一个在几秒钟内完成，第二个挂起数小时。我能理解越快越好，但为什么会有如此巨大的性能差距？

-------- 编辑----------------

这是两个查询的EXPLAIN

id  select_type table       type    possible_keys   key     key_len ref                         rows    Extra
1   PRIMARY     <derived2>  ALL     NULL            NULL    NULL    NULL                        1710    Using temporary
1   PRIMARY     my_table    ref     PRIMARY         PRIMARY 8       offset_dates.date_offset    555     Using index
2   DERIVED     my_table    index   NULL            PRIMARY 13      NULL                        5644204 Using index; Using temporary


id  select_type        table    type    possible_keys   key     key_len ref     rows    Extra
1   PRIMARY            my_table range   NULL            PRIMARY 8       NULL    9244    Using where; Using index for group-by
2   DEPENDENT SUBQUERY my_table index   NULL            PRIMARY 13      NULL    5129983 Using where; Using index; Using temporary

Answer 1

通常，使用连接的查询比使用 IN (...) 的等效查询执行得更好，因为前者可以利用索引而后者不能；必须为可能返回的每一行扫描整个 IN 列表。

(请注意，在这种情况下，某些数据库引擎的性能优于其他数据库引擎；例如，SQL Server can produce equivalent performance for both types of queries。)

您可以通过在查询前添加 EXPLAIN 并运行它来查看 MySQL 查询优化器打算如何处理给定的 SELECT 查询。除其他外，这将为您提供引擎必须为查询中的每个步骤检查的行数；将这些计数相乘以获得引擎必须访问的总行数，这可以作为对可能性能的粗略估计。