懒惰地评估 MySQL 视图 [英] Lazily evaluate MySQL view

问题描述

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

I have some MySQL views which define a number of extra columns based on some relatively straightforward subqueries. The database is also multi-tenanted so each row has a company ID against it.

我遇到的问题是我的视图在被公司 ID 过滤之前对每一行进行评估，从而产生巨大的性能问题.有没有办法懒惰地评估视图，以便外部查询中的where"子句适用于视图中的子查询.或者有什么类似于我可以用来添加额外字段的视图.我想在 SQL 中计算它们，以便计算出的字段可用于过滤/搜索/排序/分页.

The problem I have is my views are evaluated for every row before being filtered by the company ID, giving huge performance issues. Is there any way to lazily evaluate the view so the 'where' clause in the outer query applies to the subqueries in the view. Or is there something similar to views that I can use to add the extra fields. I want to calculate them in SQL so the calculated fields can be used for filtering/searching/sorting/pagination.

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

I've taken a look at the MySQL docs that explain the algorithms available and am aware that the views can't be proccessed as a 'merge' since they contain subqueries.

查看

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"之后评估视图中的子查询.我无法在视图中对公司 ID 进行硬编码，因为我希望该视图可用于任何公司 ID.

I want the subqueries in the view to be evaluated AFTER applying the 'where company_id = 123' from the main query scope. I can't hard code the company ID in the view since I want the view to be usable for any company ID.

推荐答案

不能改变求值顺序，由 MySQL 服务器设置.

You cannot change the order of evaluation, that is set by the MySQL server.

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

However, in this particular case you could rewrite the whole sql statement to use joins and conditional counts instead of subqueries:

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 子句添加任何其他字段，因为 companys 表中的其他字段在功能上依赖于 company_id.在早期版本中，您可能需要列出 companys 表中的所有字段(取决于 sql 模式设置).

If you have MySQL v5.7, then you may not need to add any further fields to the group by clause since the other fields in the companies table would be functionally dependent on the company_id. In earlier versions you may have to list all fields in the companies table (depends on the sql mode settings).

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

Another way to optimalise such query would be using denormalisation. Your users and company_user table probably have a lot more records than your companies table. You could add a user_count, an active_user_count, and an inactive_user_count field to the companies table, add after insert / update / delete triggers to the company_user table and an after update to the users table and update these 2 fields there. This way you would not need to do the joins and the conditional counts in the view.

这篇关于懒惰地评估 MySQL 视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！