使用 count() 执行全表扫描的 MySQL View

Question

考虑以下 View

CREATE VIEW `my_view` AS
SELECT
    a.id,
    (SELECT
            COUNT( b.id )
        FROM
            another_table b
        WHERE
            b.a_id = a.id AND b.other_column = 'ABC'
    ) AS counter
FROM
    some_table a

some_table 有数百万行，another_table 在 a_id + other_column 上有一个索引。

现在，考虑以下 SQL:

SELECT vw.*
FROM some_table a
LEFT JOIN my_view vw on vw.id = a.id
WHERE a.id = 12345

谁能告诉我为什么这个 sql 查询要对 some_table 进行全表扫描？目标是让 View 返回 another_table 中的行数，其中 other_column 等于 'ABC'。

如果我将查询中的 LEFT JOIN my_view 替换为 LEFT JOIN( View 中的 AS CLAUSE)，它不会执行全表扫描并使用 a_id + other_column 索引。

我被难住了。感谢您的帮助。

Answer 1

MySQL 中的 View 一般。 . . [我不想使用那个这个词]。 . .表现不如预期或想要的那样好。您认为:“哦，SQL 引擎只会将 View 定义插入我的查询中，然后对其进行优化。”你没有完全错。 MySQL 就是这样做的。它被称为 View 的 MERGE 算法。

不幸的是，没有多少有用的 View 可以使用 MERGE 算法。基本上，您可以使用它在单个表上重命名字段和预计算表达式。

替代算法是 TEMPTABLE 算法。如您所见，这会创建一个临时表，因此会进行全表扫描。

当然，您可以在 documentation 中阅读更多相关信息.

我认为没有任何简单的解决方法。您可以创建一个汇总表并使用触发器使其保持最新。但我认为这并不“简单”。