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

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

DB Schema

>时间段：时间段具有一定的开始和结束时间(例如13:00 – 18:00).时间可以以15分钟的步长变化.在我们的应用程序中,我们希望在此时间段的持续时间内在组上安排一个孩子.
>时间片：在24小时内每15分钟存在一个时间片记录(96). 15分钟是最小的计划单位.在开始和结束时间之间覆盖每个切片的时间段(例如,时间段13：00-18：00将有一个指向时间片的记录[13：00,13：15,13：30 …… 17] ：45]).这使得可以计算在任何给定时间和日期有多少孩子“占用”相同的时间片.
>孩子：孩子只是被安排的实体
>组：组是具有特定容量的物理位置的表示
> GroupAssignment：组绑定及时绑定.在日期1和2之间它可以是组A,在日期2和3之间它可以是组B.
>占用率：主要的调度记录.这有一个timeslot_id,kid_id,开始和结束日期.注意：孩子安排在开始日和随后的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 | | 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 = "";
$SQLiteDB->query($SQL);


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

http://www.sqlite.org

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

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

采取多个较小步骤的一大优势是,您的代码可能变得更易读,也更容易理解.我不想成为十年后不得不改变你的SQL查询的人,因为你继续做其他事情.