MySQL 优化查询以计算一段时间内的计划项目

在我正在处理的调度应用程序中，我正在处理一个相当复杂的数据库模式，以便描述在某些日期上的 时间段 上分配给组的一系列 child 。现在在这个模式中，我想查询数据库在某个日期范围内某个时间段内某个组中预定的 child 的数量是多少。

数据库架构

时间段:时间段有一定的开始和结束时间(例如 13:00 - 18:00)。时间可以以 15 分钟为单位变化。在我们的应用程序中，我们希望在这个时间段内安排一个 child 加入一个小组。

时间片:24 小时内每 15 分钟存在一个时间片记录 (96)。 15 分钟是最小的计划单位。一个时间段被分配给在其开始和结束时间之间覆盖的每个片段(例如，时间段 13:00-18:00 将有一个指向时间片段 [13:00, 13:15, 13:30...17 :45])。这使得可以计算在任何给定的时间和日期有多少 child “占用”相同的时间片。

Kid: child 只是被调度的实体

组:组是具有特定容量的物理位置的表示

GroupAssignment:一个组任务是有时间约束的。日期 1 和 2 之间可能是 A 组，日期 2 和 3 之间可能是 B 组。

Occupancy:主要调度记录。这有一个timeslot_id、kid_id、开始和结束日期。 注意: 一个 child 被安排在开始日和随后的每 7 天直到结束日期。

数据库模式 SQL

记录的数量可以从 auto_increment 值中粗略地推导出来。如果不存在，我会手动提及它们。

CREATE TABLE `group_assignment_caches` (
  `group_id` int(11) DEFAULT NULL,
  `occupancy_id` int(11) DEFAULT NULL,
  `start` date DEFAULT NULL,
  `end` date DEFAULT NULL,
  KEY `index_group_assignment_caches_on_occupancy_id` (`occupancy_id`),
  KEY `index_group_assignment_caches_on_group_id` (`group_id`),
  KEY `index_group_assignment_caches_on_start_and_end` (`start`,`end`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/* (~1500 records) */

CREATE TABLE `kids` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `archived` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=592 DEFAULT CHARSET=utf8;

CREATE TABLE `occupancies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kid_id` int(11) DEFAULT NULL,
  `timeslot_id` int(11) DEFAULT NULL,
  `start` date DEFAULT NULL,
  `end` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_occupancies_on_kid_id` (`kid_id`),
  KEY `index_occupancies_on_timeslot_id` (`timeslot_id`),
  KEY `index_occupancies_on_start_and_end` (`start`,`end`)
) ENGINE=InnoDB AUTO_INCREMENT=2675 DEFAULT CHARSET=utf8;

CREATE TABLE `time_slices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start` time DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_time_slices_on_start` (`start`)
) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=latin1;

CREATE TABLE `timeslot_slices` (
  `timeslot_id` int(11) DEFAULT NULL,
  `time_slice_id` int(11) DEFAULT NULL,
  KEY `index_timeslot_slices_on_timeslot_id` (`timeslot_id`),
  KEY `index_timeslot_slices_on_time_slice_id` (`time_slice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/* (~1500 records) */

CREATE TABLE `timeslots` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start` time DEFAULT NULL,
  `end` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8;

当前解决方案

到目前为止，我已经设计了以下查询来将它们联系在一起。虽然它确实有效，但它的扩展性很差。使用 1 个日期、1 个时隙和 1 个组运行查询大约需要 50 毫秒。但是，对于 100 个日期，这变成了 1000 毫秒，当您开始添加组和时隙时，这会在几秒钟内迅速呈指数增长。我注意到运行时高度依赖于时隙的大小。似乎当特定时隙覆盖更多时间片时，它会在运行时迅速升级!
SELECT subq.date, subq.group_id, subq.timeslot_id, MAX(subq.spots) AS max_spots FROM ( SELECT di.date, ts.start, gac.group_id AS group_id, tss2.timeslot_id AS timeslot_id, COUNT(*) AS spots FROM date_intervals di, timeslot_slices tss2, occupancies o JOIN timeslots t ON o.timeslot_id = t.id JOIN group_assignment_caches gac ON o.id = gac.occupancy_id JOIN timeslot_slices tss1 ON t.id = tss1.timeslot_id JOIN time_slices ts ON tss1.time_slice_id = ts.id JOIN kids k ON o.kid_id = k.id WHERE di.date BETWEEN gac.start AND gac.end AND di.date BETWEEN o.start AND o.end AND MOD(DATEDIFF(di.date, o.start),7)=0 AND k.archived = 0 AND tss1.time_slice_id = tss2.time_slice_id AND gac.group_id IN (3) AND tss2.timeslot_id IN (5) GROUP BY ts.start, di.date, group_id, timeslot_id ) subq GROUP BY subq.date, subq.group_id, subq.timeslot_id

请注意，单独运行派生子查询需要相同的时间。这将产生 1 个记录，其中包含给定时间段中给定组的每个时间片(15 分钟)的占用数。这非常适合调试。显然，我只对整个时间段的最大占用数感兴趣。

架构中未描述 Date_intervals。这是我在此过程调用开始时使用 REPEAT 语句填充的临时表。它唯一的列是“日期”，并且在大多数情况下通常会填充 10-300 个日期。查询应该能够处理这个。

如果我解释这个查询，我会得到以下结果。我不确定如何从这里走得更远。可以忽略关于派生表的第一行，因为执行子查询需要相同的时间。唯一不使用索引的其他表是 date_intervals di，它是一个包含 122 条记录的小型临时表。
+----+-------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+----------------------------+------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+----------------------------+------+------------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5124 | Using temporary; Using filesort | | 2 | DERIVED | tss2 | ref | index_timeslot_slices_on_timeslot_id,index_timeslot_slices_on_time_slice_id | index_timeslot_slices_on_timeslot_id | 5 | | 42 | Using where; Using temporary; Using filesort | | 2 | DERIVED | ts | eq_ref | PRIMARY | PRIMARY | 4 | ookidoo.tss2.time_slice_id | 1 | | | 2 | DERIVED | tss1 | ref | index_timeslot_slices_on_timeslot_id,index_timeslot_slices_on_time_slice_id | index_timeslot_slices_on_time_slice_id | 5 | ookidoo.tss2.time_slice_id | 6 | Using where | | 2 | DERIVED | o | ref | PRIMARY,index_occupancies_on_timeslot_id,index_occupancies_on_kid_id,index_occupancies_on_start_and_end | index_occupancies_on_timeslot_id | 5 | ookidoo.tss1.timeslot_id | 6 | Using where | | 2 | DERIVED | k | eq_ref | PRIMARY | PRIMARY | 4 | ookidoo.o.kid_id | 1 | Using where | | 2 | DERIVED | gac | ref | index_group_assignment_caches_on_occupancy_id,index_group_assignment_caches_on_start_and_end,index_group_assignment_caches_on_group_id | index_group_assignment_caches_on_occupancy_id | 5 | ookidoo.o.id | 1 | Using where | | 2 | DERIVED | di | range | PRIMARY | PRIMARY | 3 | NULL | 1 | Range checked for each record (index map: 0x1) | | 2 | DERIVED | t | eq_ref | PRIMARY | PRIMARY | 4 | ookidoo.o.timeslot_id | 1 | Using where; Using index | +----+-------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+----------------------------+------+------------------------------------------------+

当前结果

上述查询产生以下结果(122条记录，缩写)
date group_id timeslot_id max_spots +------------+----------+-------------+-----------+ | date | group_id | timeslot_id | max_spots | +------------+----------+-------------+-----------+ | 2012-08-20 | 3 | 5 | 12 | | 2012-08-27 | 3 | 5 | 12 | | 2012-09-03 | 3 | 5 | 12 | | 2012-09-10 | 3 | 5 | 12 | +------------+----------+-------------+-----------+ | 2014-11-24 | 3 | 5 | 15 | | 2014-12-01 | 3 | 5 | 15 | | 2014-12-08 | 3 | 5 | 15 | | 2014-12-15 | 3 | 5 | 15 | +------------+----------+-------------+-----------+

包起来

我想知道一种方法来重组我的查询甚至我的数据库架构，以便减少查询这些信息的时间。我无法想象这是不可能的，考虑到该数据库中存在的记录相对较少(大多数表为 10-1000 条)

最佳答案

任何足够复杂的问题都可能使计算机瘫痪。其实，创造一个复杂的问题很容易，而把一个复杂的问题简单化却很难。

您的单个查询非常复杂。它遍历整个数据库。那有必要吗？例如，如果您将其限制为一个日期，会发生什么情况？它的扩展性更好吗？

正如您所发现的那样，仅使用单个查询来执行复杂任务通常非常有效，但并非总是如此。我经常发现打破执行任务所需的指数时间的唯一方法是将其拆分为多个步骤。例如，一次一个约会。也许你并不总是需要它们？

在某些情况下，我使用驻留在内存中的中间 SQLite 数据库。在内存中的小型 (!) 临时数据库上的操作非常快。它是这样工作的:
$SQLiteDB = new PDO("sqlite::memory:"); $SQLiteDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $SQL = "<any valid sqlite query>"; $SQLiteDB->query($SQL);

首先检查您是否安装了 sqlite PHP 模块。阅读手册:

http://www.sqlite.org

使用它时，您首先在新数据库中创建表，然后用所需的数据填充它们。如果必须复制多行，则可以使用准备好的语句。

棘手的一点是拆分您的单个复杂查询。您将如何做到这一点取决于您要回答的确切问题。艺术是限制您必须使用的数据量。不要复制整个数据库，而是做出明智的选择。

采取多个较小步骤的一大优势是您的代码可能变得更具可读性和可理解性。我不想成为十年后必须更改您的 SQL 查询的人，因为您继续做其他事情。
关于MySQL 优化查询以计算一段时间内的计划项目，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25468757/