mysql - 获取通过特定摄像头的汽车

Question

MYSQL/MARIADB 架构和示例数据:

CREATE DATABASE IF NOT EXISTS `puzzle` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

USE `puzzle`;

DROP TABLE IF EXISTS `event`;

CREATE TABLE `event` (
  `eventId` bigint(20) NOT NULL AUTO_INCREMENT,
  `sourceId` bigint(20) NOT NULL COMMENT 'think of source as camera',
  `carNumber` varchar(40) NOT NULL COMMENT 'ex: 5849',
  `createdOn` datetime DEFAULT NULL,
  PRIMARY KEY (`eventId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `event` (`eventId`, `sourceId`, `carNumber`, `createdOn`) VALUES
    (1, 44, '4456', '2016-09-20 20:24:05'),
    (2, 26, '26484', '2016-09-20 20:24:05'),
    (3, 5, '4456', '2016-09-20 20:24:06'),
    (4, 3, '72704', '2016-09-20 20:24:15'),
    (5, 3, '399606', '2016-09-20 20:26:15'),
    (6, 5, '4456', '2016-09-20 20:27:25'),
    (7, 44, '72704', '2016-09-20 20:29:25'),
    (8, 3, '4456', '2016-09-20 20:30:55'),
    (9, 44, '26484', '2016-09-20 20:34:55'),
    (10, 26, '4456', '2016-09-20 20:35:15'),
    (11, 3, '72704', '2016-09-20 20:35:15'),
    (12, 3, '399606', '2016-09-20 20:44:35'),
    (13, 26, '4456', '2016-09-20 20:49:45');

我想在 20:24 到 20:45 期间获取 sourceId = 3 AND (26 OR 44) 的 CarNumber(s)。查询需要快速，因为真实表包含超过 3 亿条记录。

到目前为止，下面是我可以进行查询的最大值(它甚至没有产生有效结果)

select * from event e where 
e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00' 
and e.sourceId IN(3,26,44) group by e.carNumber;

所提供数据的正确结果:

carNumber
4456
72704

我真是百思不得其解，卡壳了。我尝试了 EXISTS、Joins、子查询，但都没有成功，所以我想知道 SQL 是否能够解决这个问题，还是我应该使用后端编码？

正在使用的 MySQL/MariaDB 版本:

mariadb-5.5.50

mysql-5.5.51

Answer 1

如果您需要它更快，那么以下可能工作，假设您在 event(createdOn, carNumber, SourceId) 上有一个索引:

select e.carNumber 
from event e 
where e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00'
group by e.carNumber
having sum(e.sourceId = 3) > 0 and
       sum(e.sourceId IN (26, 44)) > 0;

我倾向于将其更改为:

select e.carNumber 
from event e 
where e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00' and
      e.sourceId in (3, 26, 44)
group by e.carNumber
having sum(e.sourceId = 3) > 0 and
       sum(e.sourceId IN (26, 44)) > 0;

然后为了性能，即使这样:

select carNumber
from ((select carNumber, sourceId
       from event e
       where e.sourceId = 3 and
             e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00'
      ) union all
      (select carNumber, sourceId
       from event e
       where e.sourceId = 26 and
             e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00'
      ) union all
      (select carNumber, sourceId
       from event e
       where e.sourceId = 44 and
             e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:45:00'
      )
     ) e
group by e.carNumber
having sum(e.sourceId = 3) > 0 and
       sum(e.sourceId IN (26, 44)) > 0;

此版本可以利用 event(sourceId, createdOn, carNumber) 上的索引。每个子查询都应该非常有效地使用这个索引，将少量数据聚集在一起以进行最终聚合。