如何用SELECT子查询优化SQL查询

提问者：小点点

如何用SELECT子查询优化SQL查询

我在MySQL5.6中有两个用于收集事件数据的表。当一个事件发生时，它在一定的时间段内生成数据。名为“event”的父表记住事件的最后状态。名为'event_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`)
);

我想获取在选定时间段内添加了新行的所有event_version行。例如:有一个事件，event.id=21出现在2019-04-28，它生成的版本位于:

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

我希望在搜索period from2019-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>);

问题是，当我使用第二个子查询时，大约需要8s来生成结果（大约500万条记录）。用第一个子查询创建相同的结果需要3分钟15秒。

我不能理解为什么即使子查询产生相同的结果列表，执行时间也会有如此大的差异。我想使用像第一个示例中那样的子查询，因为它只依赖于event_time，而不依赖于父表中的附加数据。我有更多类似的表，在这些表中我只能依赖event_time。

我的问题:是否有可能优化查询以仅使用EVENT_TIME生成预期结果？

共2个答案

匿名用户

我猜event_version表比event表大得多。子查询很容易实现，只需扫描一次表中的谓词，然后返回行。当您在子查询中这样做时，在外部查询检查的第一行，子查询将被执行。因此，如果event_version有1m行，它将执行子查询1m次。显然有一些更聪明的逻辑来避免它变得如此极端，但原则仍然存在。

无论如何，我看不出第三个问题的意义。如果将第3个查询与第1个查询作为子查询一起使用，则会得到与第1个查询作为Select all from event_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';

？

匿名用户

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

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()的速度会变慢（至少在旧版本中是这样）。

尝试将子查询作为派生表进行联接:

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)

看看上面提到的指数在这里是否有帮助。

尝试EXISTS子查询:

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条件转换为联接，因为返回的列是event表的主键。