Mysql查询优化与案例

Question

我有来自 vw_pharmacy_contracts View 的查询，我需要优化:

DROP VIEW IF EXISTS vw_pharmacy_contracts_per_period;
CREATE VIEW vw_pharmacy_contracts_per_period AS 
SELECT 
inn_prod.pharmacy_inn, 
inn_prod.period_id, 
SUM(CASE WHEN marketing_contract_types_id = 1 THEN 1 ELSE 0 END) AS zentiva_marketing_count,
SUM(CASE WHEN marketing_contract_types_id = 2 THEN 1 ELSE 0 END) AS direct_marketing_count,
SUM(CASE WHEN marketing_contract_types_id = 3 THEN 1 ELSE 0 END) AS rx_marketing_count,
SUM(CASE WHEN marketing_contract_types_id = 4 THEN 1 ELSE 0 END) AS diabetes_marketing_count,
CAST(GROUP_CONCAT(DISTINCT inn_prod.marketing_contract_types_id SEPARATOR ',') AS CHAR (63) CHARSET UTF8) AS marketing_contract_types,
CAST(GROUP_CONCAT(DISTINCT marketing_contracts.type_name SEPARATOR ',') AS CHAR (255) CHARSET UTF8) AS marketing_contract_types_name
FROM inn_to_marketing_products inn_prod 
LEFT JOIN marketing_contract_types marketing_contracts ON (inn_prod.marketing_contract_types_id = marketing_contracts.id)
group by inn_prod.pharmacy_inn, inn_prod.period_id;

DROP VIEW IF EXISTS vw_pharmacy_contracts;
CREATE VIEW vw_pharmacy_contracts AS 
SELECT pharma_state.period_id, pharma_state.pharmacy_id, pharma.inn,
       case when cnt.zentiva_marketing_count > 0 then true else false end as zentiva_marketing, 
       case when cnt.direct_marketing_count > 0 then true else false end as direct_marketing,  
       case when cnt.rx_marketing_count > 0 then true else false end as rx_marketing,
       case when cnt.diabetes_marketing_count > 0 then true else false end as diabetes_marketing,
       case when (cnt.marketing_contract_types IS NOT NULL) then true else false end as has_contract,
             cnt.marketing_contract_types AS marketing_contract_types,
       cnt.marketing_contract_types_name AS marketing_contract_types_name
FROM pharmacy_state pharma_state
left join pharmacy pharma on pharma.id = pharma_state.pharmacy_id
left join vw_pharmacy_contracts_per_period cnt on cnt.pharmacy_inn = pharma.inn and cnt.period_id = pharma_state.period_id;

explain from vw_pharmacy_contracts

explain from vw_pharmacy_contracts_per_period

这里是vw_pharmacy_contracts的回复: responce 。查询持续时间约为 21-23 秒。我需要加快这些 View 之一的速度。如果有人帮助我，我会很高兴。

Answer 1

我怀疑vw_pharmacy_contracts_per_period正在一遍又一遍地计算数据。也就是说，每次使用它时，它都会扫描所有数据并得出相同的结果。同时，这需要相当长的时间。

那么，让我们构建一个包含该 View 结果的“汇总表”。它可能有PRIMARY KEY(pharmacy_inn, period_id)。

“经期”是一天吗？每天晚上(？)只计算白天的(？)数据并将其添加到汇总表中。当你掌握了这些之后，我们就可以看看是否可以优化第二个 View 。

更多关于Summary Tables

同时，请使用JOIN，除非您确实需要右侧表格是可选的，因此可以证明LEFT。