避免在视图中使用 JSON_ARRAYAGG 和 GROUP BY 进行 MySQL 全表扫描

问：

我有两个表，它们具有使用链接表建立的一对多关系。practicefacilityfacility_practice

我想要一个将关系的“许多”部分聚合到 json 数组中的结果。以下查询将生成所需的结果：

查询

select 
    practice.id
    , practice.name
    , practice.code

    , json_arrayagg(json_object(
        "id", facility.id
        , "name", facility.name
        , "code", facility.code
    )) as facility_json
from practice
left join facility_practice
    on facility_practice.practice_id = practice.id
left join facility
    on facility.id = facility_practice.facility_id
where practice.id = 1
group by practice.id;

结果

+----+------+-------+---------------------------------------------------------------------------------------------+
| id | name |  code | facility_json                                                                               |
+----+------+-------+---------------------------------------------------------------------------------------------+
| 1  | Test |  NA   | [{"id": 1, "code": "NA", "name": "Test"}, {"id": 15, "code": "HV", "name": "Harbour View"}] |
+----+------+-------+---------------------------------------------------------------------------------------------+

如果我进行上述查询，我会得到我所期望的：explain

解释

+----+-------------+-------------------+--------+-------------+
| id | select_type | table             | type   | key         |
+----+-------------+-------------------+--------+-------------+
| 1  | SIMPLE      | practice          | const  | PRIMARY     |
+----+-------------+-------------------+--------+-------------+
| 1  | SIMPLE      | facility_practice | ref    | practice_id |
+----+-------------+-------------------+--------+-------------+
| 1  | SIMPLE      | facility          | eq_ref | PRIMARY     |
+----+-------------+-------------------+--------+-------------+

如果我随后尝试从该查询中创建一个视图，MySQL会突然对练习表执行全表扫描。我注意到，如果我删除查询的部分，它将不再执行全表扫描;但是，结果不再正确（显然）。group by

视图

drop view if exists practice_facility_v;    

create view practice_facility_v as
select 
    practice.id
    , practice.name
    , practice.code

    , json_arrayagg(json_object(
        "id", facility.id
        , "name", facility.name
        , "code", facility.code
    )) as facility_json
from practice
left join facility_practice
    on facility_practice.practice_id = practice.id
left join facility
    on facility.id = facility_practice.facility_id
group by practice.id;

select * from practice_facility_v where id = 1;

解释

+----+-------------+-------------------+------------+--------+---------------+-------------+---------+-------------------------------------------+------+----------+----------------+
| id | select_type | table             | partitions | type   | possible_keys | key         | key_len | ref                                       | rows | filtered | Extra          |
+----+-------------+-------------------+------------+--------+---------------+-------------+---------+-------------------------------------------+------+----------+----------------+
| 1  | PRIMARY     | <derived2>        | (NULL)     | ref    | <auto_key0>   | <auto_key0> | 4       | const                                     | 1    | 100.00   |                |
+----+-------------+-------------------+------------+--------+---------------+-------------+---------+-------------------------------------------+------+----------+----------------+
| 2  | DERIVED     | practice          | (NULL)     | ALL    | PRIMARY       | (NULL)      | (NULL)  | (NULL)                                    | 7    | 100.00   | Using filesort |
+----+-------------+-------------------+------------+--------+---------------+-------------+---------+-------------------------------------------+------+----------+----------------+
| 2  | DERIVED     | facility_practice | (NULL)     | ref    | practice_id   | practice_id | 4       | ChargeBook.practice.id                    | 2    | 100.00   |                |
+----+-------------+-------------------+------------+--------+---------------+-------------+---------+-------------------------------------------+------+----------+----------------+
| 2  | DERIVED     | facility          | (NULL)     | eq_ref | PRIMARY       | PRIMARY     | 4       | ChargeBook.facility_practice.facility_id  | 1    | 100.00   |                |
+----+-------------+-------------------+------------+--------+---------------+-------------+---------+-------------------------------------------+------+----------+----------------+