时间戳

首页 > 解决方案 > 带有多个子查询优化的 Sql 选择查询

mysql - 带有多个子查询优化的 Sql 选择查询

问题描述

我有带有此类表的 MySql 数据库：

PageviewEvents:
pageviewId | eventId | eventValue | eventTime

SessionPageviews:
id | sessionId | page

PageviewEvents.pageviewId 是指 SessionPageviews.id 作为外键。

当我需要通过 eventId 选择一些数据时，我使用这个查询：

SELECT 
    sp.page as Page, count(*)
from PageviewEvents pe
left join SessionPageviews sp on sp.id = pe.pageviewId
where pe.eventId = 1
GROUP by sp.page
order BY 2 DESC

并得到一个这样的表：

page | count_of_event_1

但现在我需要选择更多数据：

page | count_of_event_1 | count_of_event_2 ... | count_of_event_N

我从 2 个事件开始，并尝试将这样的东西变白：

SELECT 
    sp.page as Page, 
    (SELECT count(*) from PageviewEvents pe1 left join SessionPageviews sp1 on sp1.id = pe1.pageviewId where pe1.eventId = 1 and sp1.page = sp.page) as count_of_event_1,
    (SELECT count(*) from PageviewEvents pe1 left join SessionPageviews sp1 on sp1.id = pe1.pageviewId where pe1.eventId = 2 and sp1.page = sp.page) as count_of_event_2 
from PageviewEvents pe
left join SessionPageviews sp on sp.id = pe.pageviewId
where pe.eventId = 1 OR pe.eventId = 2
GROUP by sp.page
order BY 2 DESC

当我在远程服务器上运行此查询时，它会冻结。

我的查询中有任何错误吗？如何优化它？

标签： mysqlsql

解决方案

您可以尝试使用条件聚合：

SELECT
    sp.page AS Page,
    COUNT(CASE WHEN pe.eventId = 1 THEN 1 END) AS count_of_event_1,
    COUNT(CASE WHEN pe.eventId = 2 THEN 1 END) AS count_of_event_2
FROM PageviewEvents pe
LEFT JOIN SessionPageviews sp
    ON sp.id = pe.pageviewId
WHERE
    pe.eventId IN (1, 2)
GROUP BY
    sp.page
ORDER BY
    2 DESC;

除了上述之外，您可以考虑将以下索引添加到您的表中：

CREATE INDEX idx ON SessionPageviews (pageviewId, eventId);

这可能有助于加快两个表之间的连接。

推荐阅读