mysql - 使用返回多行的 SELECT 子查询优化 SELECT IN () SQL 查询

Question

我刚刚发现，由于某个 SQL 查询等原因，我的应用中的一个页面加载速度非常慢。

我已阅读this document about subquery optimization但它似乎概述了 MySQL 如何优化子查询，而不是我如何优化查询。我确实尝试了从文档中得到的一些想法，但没有成功。

这是目前我的慢速查询。为了便于阅读，我简化了表和列名称:

SELECT
    a.one, a.two, a.three, a.four,
    b.*,
    a.id,
    b.id,
    c.one, c.id,
    d.one,
    f.one
FROM a
JOIN b ON a.id = b.a_id
JOIN c ON c.id = b.c_id
JOIN e ON b.e_id = e.id
JOIN d ON d.id = e.d_id
JOIN f ON f.id = b.f_id
WHERE a.id IN (
    SELECT a_id FROM b WHERE a_id IS NOT NULL AND g_id = 95
)

SELECT 子查询当前返回 750 多行，我认为这会导致父查询的延迟。整个查询需要 25 秒。

我如何优化这个查询？

Answer 1

在 5.6.5 之前，MySQL 不会具体化子查询。这意味着对于连接上的每条记录，它将运行以下相关查询:

SELECT  1
FROM    b
WHERE   a_id IS NOT NULL
        AND g_id = 95
        /* optimizer added */
        AND a_id = a.id
LIMIT   1

，优化器添加了一个附加条件。

从 5.6.5 开始，MySQL 能够将 IN 子查询的结果具体化到临时表中，并像其他表一样连接它。

如果您使用的是 5.6.5 之前的 MySQL，您可以尝试将条件重写为联接:

SELECT  a.one, a.two, a.three, a.four,
        b.*,
        a.id,
        b.id,
        c.one, c.id,
        d.one,
        f.one
FROM    (
        SELECT  DISTINCT a_id
        FROM    b
        WHERE   a_id IS NOT NULL
                AND g_id = 95
        ) bi
JOIN a ON a.id = bi.a_id
JOIN b ON a.id = b.a_id
JOIN c ON c.id = b.c_id
JOIN e ON b.e_id = e.id
JOIN d ON d.id = e.d_id
JOIN f ON f.id = b.f_id

当然，还要正确索引所有相关字段。