在数据库性能方面，哪种方法更好 - 通过一张表进行分组操作还是多张表串行化事务？

Question

我正在开发一个基于 SQL 数据库的预订系统（对特定数据库没有限制）。

架构: 工作流程:

现在，我正在使用一个特殊的“实际预订”视图，该视图基于自定义分组和筛选逻辑来识别实际放置的预订。

CREATE VIEW `reserv-io`.`actual_reserved_resources` AS
  SELECT
    `r`.`id`,
    `a`.`resource_id`,
    `a`.`type_id`,
    `a`.`status_id`,
    `a`.`reservation_start`,
    `a`.`reservation_end`
  FROM `reserv-io`.`actions` `a`
    JOIN (SELECT
            `r`.`id`      `id`,
            MAX(`a`.`id`) `action_id`
          FROM `reserv-io`.`reservations` `r`
            JOIN `reserv-io`.`actions` `a` ON `r`.`id` = `a`.`reservation_id`
          WHERE (`r`.`id` NOT IN
                 (SELECT `r`.`id` `id`
                  FROM `reserv-io`.`reservations` `r`
                    JOIN `reserv-io`.`actions` `a` ON `r`.`id` = `a`.`reservation_id`
                  WHERE (`a`.`status_id` IN
                         (SELECT `id`
                          FROM `reserv-io`.`reservation_statuses`
                          WHERE `name` = 'Canceled')))
                 AND `a`.`status_id` IN
                     (SELECT `id`
                      FROM `reserv-io`.`reservation_statuses`
                      WHERE `name` = 'Approved' OR `name` = 'Accepted'))
          GROUP BY `r`.`id`) `b`
      ON `a`.`id` = `b`.`action_id`
    JOIN `reserv-io`.`reservations` `r` ON `r`.`id` = `a`.`reservation_id`;

通过以下步骤确定是否存在与其他预订重叠的情况：

CREATE PROCEDURE HAS_OVERLAPPING_RESERVATION_WITH_TYPE_SELF_CHECK(
  IN  RESERVATION      BIGINT,
  IN  RESOURCE         INT,
  IN  RESERVATION_TYPE INT,
  IN  STARTS_AT        DATETIME,
  IN  ENDS_AT          DATETIME,
  OUT RESULT           BIT)
  BEGIN
    SELECT CASE WHEN EXISTS(
        SELECT *
        FROM actual_reserved_resources r
        WHERE r.resource_id = RESOURCE
              AND r.type_id = RESERVATION_TYPE
              AND r.reservation_start < ENDS_AT
              AND r.reservation_end > STARTS_AT
              AND r.id <> RESERVATION)
      THEN TRUE
           ELSE FALSE END
    INTO RESULT;
  END$$

我已经在“actions”表上建立了良好的索引，但我不确定我的方法是将所有与预订相关的操作放在单个表中是否真的可扩展。我听说过一种方法，即将所有待处理和实际批准的预订存储到单独的表中，并手动将它们同步到事务中。

我的问题是，从您的角度来看，哪种方法在数据库性能、可扩展性和良好的数据设计方面更好？

- trofiv

Answer 1

我建议：

• 不要轻易相信别人的答案；相反，使用真实世界的数据进行基准测试，以衡量实际性能。

• 无论答案是什么，它都不会像你担心的那样重要。它肯定不会成为可扩展系统和不可扩展系统之间的区别。

• 如果事实上将记录分离成待处理和已批准有任何价值，您可以使用行分区（查询一下）来实现它，这样您就可以在一个逻辑表中保留所有内容，避免在性能的名义下弄乱模式。

- Mike Nakis

Answer 2

您没有提及数据集的大小或运行查询的系统的大小。在处理小型数据集时，这可能并不重要。但是，我个人处理大型数据集的经验是，跨越两个以上表格的连接可能会非常缓慢，因为系统开始将数据交换到磁盘而不是保留在内存中。多年前在Oracle上，我有一个需要涉及六个表格的特定查询。在一个查询中，它需要大约六个小时才能运行。将其拆分成每次涉及两个表格的单独查询，则只需15分钟。我在MySQL上有一个不同的查询，涉及一些表格。将其拆分成较小的查询可将总运行时间从约七个小时降低到七分钟。

- Kevin Postlewaite

---

好的，我在发布这个问题时应该明确说明。 数据大小-最多10000个用户，每年最多1000个预订，每个预订大约有5个操作，这给我们带来了每年5000万条记录。 - trofiv