mysql - 延迟评估 MySQL View

Question

我有一些 MySQL View ，它们根据一些相对简单的子查询定义了许多额外的列。该数据库也是 Multi-Tenancy 的，因此每一行都有一个公司 ID。

我遇到的问题是，在按公司 ID 过滤之前，我的 View 会针对每一行进行评估，从而造成巨大的性能问题。有什么方法可以延迟评估 View ，以便外部查询中的“where”子句适用于 View 中的子查询。或者是否有类似于我可以用来添加额外字段的 View 的东西。我想在 SQL 中计算它们，以便计算字段可用于过滤/搜索/排序/分页。

我查看了解释可用算法的 MySQL 文档，我知道 View 不能作为“合并”处理，因为它们包含子查询。

查看

create view companies_view as
select *,
(
    select count(id) from company_user where company_user.company_id = companies.id
) as user_count,
(
    select count(company_user.user_id)
    from company_user join users on company_user.user_id = users.id
    where company_user.company_id = companies.id
        and users.active = 1
) as active_user_count,
(
    select count(company_user.user_id)
    from company_user join users on company_user.user_id = users.id
    where company_user.company_id = companies.id
        and users.active = 0
as inactive_user_count
from companies;

查询

select * from companies_view where company_id = 123;

我希望在从主查询范围应用“where company_id = 123”后评估 View 中的子查询。我无法在 View 中对公司 ID 进行硬编码，因为我希望该 View 可用于任何公司 ID。

Answer 1

您不能更改由 MySQL 服务器设置的求值顺序。

但是，在这种特殊情况下，您可以重写整个 sql 语句以使用连接和条件计数而不是子查询:

select c.*,
       count(u.id) as user_count,
       count(if(u.active=1, 1, null)) as active_user_count,
       count(if(u.active=0, 1, null)) as inactive_user_count 
from companies c
left join company_user cu on c.id=cu.company_id
left join users u on cu.user_id = u.id
group by c.company_id, ...

如果您有 MySQL v5.7，那么您可能不需要向 group by 子句添加任何其他字段，因为 companies 表中的其他字段在功能上依赖于 company_id。在早期版本中，您可能必须列出 companies 表中的所有字段(取决于 sql 模式设置)。

优化此类查询的另一种方法是使用非规范化。您的 users 和 company_user 表可能比您的 companies 表有更多的记录。您可以在 companies 表中添加一个 user_count、一个 active_user_count 和一个 inactive_user_count 字段，在 insert/更新/删除 company_user 表的触发器和 users 表的后续更新并在那里更新这 2 个字段。这样您就不需要在 View 中进行连接和条件计数。