如何使用子查询中的where优化sql查询

i7uq4tfw 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(344)

MySQL5.6中有两个表用于收集事件数据。当事件发生时，它会在特定的时间段内生成数据。名为“event”的父表会记住事件的最后一个状态。名为“event\u version”的子表会记住任何事件生成的所有数据版本。此表的架构如下所示：

CREATE TABLE `event` (
`id` BIGINT(20) NOT NULL,
`version_id` BIGINT(20)', -- refers to last event_version   
`version_number` BIGINT(20)', -- consecutive numbers increased when new version appears 
`first_event_time` TIMESTAMP(6),  -- time when a set of event data was generated first time,
-- it is immutable after creation
`event_time` TIMESTAMP(6), -- time when a set of event data changed last time
`other_event_data` VARCHAR(30),--more other columns
PRIMARY KEY (`id`),
INDEX `event_time` (`event_time`),
INDEX `version_id` (`version_id`),
CONSTRAINT `FK_version_id` FOREIGN KEY (`version_id`) REFERENCES `event_version` (`id`)
);

CREATE TABLE `event_version` (
`id` BIGINT(20) NOT NULL,
`event_id` BIGINT(20)', -- refers to event  
`version_number` BIGINT(20)', -- consecutive numbers increased when new version appears 
`event_time` TIMESTAMP(6) NULL DEFAULT NULL, -- time when a set of event data was generated
`other_event_data` VARCHAR(30),--more other columns
PRIMARY KEY (`id`),
INDEX `event_time` (`event_time`), -- time when a set of event data changed
INDEX `event_id` (event_id),
CONSTRAINT `FK_event_id` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`)
);

我要获取在选定时间段中添加了新行的所有事件\版本行。例如：2019-04-28出现了一个event.id=21的na事件，它在以下位置生成了版本：

2019-04-28 version_number: 1, event_version.event_id=21  
2019-04-30 version_number: 2, event_version.event_id=21  
2019-05-02 version_number: 3, event_version.event_id=21  
2019-05-04 version_number: 4, event_version.event_id=21

我希望在搜索期间开始时找到此记录 2019-05-01 to 2019-06-01 .
其思想是查找在选定时段中创建的所有event_version.event_id，然后从该列表中查找event_version中具有event_id的所有行。要创建事件id列表，我有一个内部选择查询：第一个查询：

SELECT DISTINCT event_id FROM event_version WHERE event_time>='2019-05-01' AND event_time<'2019-06-01';

它需要大约10秒，返回大约50万条记录。
但我有第二个查询，它使用父表，如下所示：

SELECT id FROM event WHERE (first_event_time>='2019-05-01' AND first_event_time<'2019-06-01') OR (first_event_time<'2019-05-01' AND event_time>'2019-05-01');

它大约需要7秒并返回相同的ID集。
然后在最后一个查询中使用以下子查询：

SELECT * FROM event_version WHERE event_id IN (<one of prvious two queries>);

问题是，当我使用第二个子查询时，产生结果（大约500万条记录）大约需要8秒。用第一个子查询创建相同的结果需要3分钟15秒。
我不明白为什么即使子查询产生相同的结果列表，执行时间也会有如此大的差别。我想使用第一个示例中的子查询，因为它只依赖于事件时间，而不依赖于父表中的其他数据。我有更多类似的表，在那里我只能依靠事件时间。
我的问题是：是否有可能优化查询以仅使用事件时间生成预期结果？

sql mysql query-optimization

来源：https://stackoverflow.com/questions/62344007/how-to-optimize-sql-query-with-where-in-subquery

2条答案

按热度按时间

ou6hu8tu1#

据我所知，您希望优化以下查询：

SELECT * 
FROM event_version
WHERE event_id IN (
  SELECT DISTINCT event_id
  FROM event_version
  WHERE event_time >= '2019-05-01'
    AND event_time <  '2019-06-01'
)

我会尝试的事情：
在上创建索引 event_version(event_time, event_id) . 这将通过避免第二次查找来获得子查询，从而提高子查询的性能 event_id . 尽管整体表现可能相似。原因是 WHERE IN (<subquery>) 当子查询返回大量行时，速度往往很慢（至少在旧版本中是这样）。
尝试将子查询作为派生表进行联接：

SELECT * 
FROM (
  SELECT DISTINCT event_id
  FROM event_version
  WHERE event_time >= '2019-05-01'
    AND event_time <  '2019-06-01'
) s
JOIN event_version USING(event_id)

看看上面提到的索引在这里是否有用。
尝试现有子查询：

SELECT v.*
FROM event e
JOIN event_version v ON v.event_id = e.id
WHERE EXISTS (
  SELECT *
  FROM event_version v1
  WHERE v1.event_id = e.id
    AND v1.event_time >= '2019-05-01'
    AND v1.event_time <  '2019-06-01'
)

这里你需要一个索引 event_version(event_id, event_time) . 尽管表现可能更糟。我敢打赌派生的表连接解决方案。
我猜，为什么第二个查询运行得更快，是因为优化器能够将in条件转换为join，因为返回的列是 event table。

赞(0）回复(0）举报 2021-08-09

e7arh2l62#

我猜event\u version表比event表大得多。子查询很容易，只需扫描一次表中的 predicate 并返回行。在子查询中执行此操作时，在外部查询检查的每一行的前面，都会执行子查询。因此，如果event\u version有1m行，它将执行子查询1m次。也许有一些更聪明的逻辑不让它变得如此极端，但原则仍然存在。
然而，我没有明白第三个问题的意义。如果将第3个查询与第1个查询一起用作子查询，则会得到与第一个查询完全相同的行，如果将第一个查询用作select all from event\ u version，那么为什么要使用子查询呢？
这不是：

SELECT * FROM event_version WHERE event_id IN (insert query 1);

与…相同

SELECT * FROM event_version WHERE event_time>='2019-05-01' AND event_time<'2019-06-01';

赞(0）回复(0）举报 2021-08-09

我来回答

如何使用子查询中的where优化sql查询

2条答案

相关问题

热门标签

最新问答