mysql - 改进 SQL 查询以获取特定日期和设备类型的传感器事件

Question

我们有一个 event mysql 表，我们在其中存储从不同类型的传感器生成的事件。下面是同一张表的建表查询。

  CREATE TABLE `event` (
  `id` varchar(36) NOT NULL,
  `device_id` varchar(36) NOT NULL,
  `device_type` varchar(45) NOT NULL,
  `data` text NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `fk_event_device_idx` (`device_id`),
  KEY `event_device_type` (`device_type`),
  KEY `event_created_at_idx` (`created_at`),
  CONSTRAINT `fk_event_device` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

我们有一个来自device表的外键device_id，而device表有一个来自zone zone_id强>表。

我们想要获取某个日期(例如 2017-02-26)的特定区域 和设备类型(例如 THL 传感器) 的事件。下面是我正在运行的查询。

select e.data from event e 
left join device d on d.id = e.device_id 
where d.type = 'mdc' and d.zone_id = 'e451b2a1-5f6c-4a75-8038-30854926a9c0' and DATE(e.created_at) = '2018-03-01';

解释计划给出了相同的结果。

+----+-------------+-------+------------+------+--------------------------------------+---------------------+---------+--------------+------+----------+------------------------------------+
    | id | select_type | table | partitions | type | possible_keys                        | key                 | key_len | ref          | rows | filtered | Extra                              |
    +----+-------------+-------+------------+------+--------------------------------------+---------------------+---------+--------------+------+----------+------------------------------------+
    |  1 | SIMPLE      | d     | NULL       | ref  | PRIMARY,id_UNIQUE,fk_device_zone_idx | fk_device_zone_idx  | 110     | const        |   23 |    10.00 | Using index condition; Using where |
    |  1 | SIMPLE      | e     | NULL       | ref  | fk_event_device_idx                  | fk_event_device_idx | 110     | senzopt.d.id |  197 |   100.00 | Using where                        |
    +----+-------------+-------+------------+------+--------------------------------------+---------------------+---------+--------------+------+----------+------------------------------------+

事件表中的记录总数约为 500 万条，上述查询需要大约 1 秒的时间来执行并提供结果。我希望改善 sql 执行时间。需要相同的建议。请让我知道我本可以做对的事情。

注意:我知道我应该同样转向 NOSQL(Kafka/Cas​​sandra/Spark)。为此，我们也在并行工作。但是，我希望改进查询，以便在当前环境下更好地为我的客户服务。

Answer 1

以下是您以更易读的格式重复的查询:

SELECT
    e.data
FROM event e 
LEFT JOIN device d
    ON d.id = e.device_id 
WHERE
    d.type = 'mdc' AND
    d.zone_id = 'e451b2a1-5f6c-4a75-8038-30854926a9c0' AND
    DATE(e.created_at) = '2018-03-01';

我们可以通过添加适当的索引来改进此查询的性能，并且还可以重新措辞。

首先，您可以在 (type, zone_id) 上的 device 表中创建复合索引。这应该有助于 WHERE 子句。请注意，假设 device.id 是该表的主键，它应该已经被索引，这意味着您拥有的 LEFT JOIN 条件应该是最佳的。

您还可以在 event 表中的 event.created_at 列上创建索引。但是为了利用它，我们必须重写非 SARGable 条件 WHERE DATE(e.created_at) = '2018-03-01':

WHERE e.created_at >= '2018-03-01' AND e.created_at < '2018-03-02'

上面的意思相同，但是没有将 created_at 列包装在一个函数中。

您的最终查询可能如下所示:

SELECT
    e.data
FROM event e 
LEFT JOIN device d
    ON d.id = e.device_id     -- d.id already has an index
WHERE
    d.type = 'mdc' AND        -- index (type, zone_id)
    d.zone_id = 'e451b2a1-5f6c-4a75-8038-30854926a9c0' AND   -- same index as above
    e.created_at >= '2018-03-01' AND e.created_at < '2018-03-02'