在我正在处理的调度应用程序中，我正在处理一个相当复杂的数据库模式，以描述在时间段分配给组的一系列 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 “占用”同一时间片成为可能。
• child : child 只是被安排的实体
• 组:组是具有特定容量的物理位置的表示
• GroupAssignment:小组作业是有时间限制的。在日期 1 和 2 之间，它可以是 A 组，在日期 2 和 3 之间，它可以是 B 组。
• 占用:主要的调度记录。这有一个 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/