主页 / dba / 问题 / 190167
Accepted
kezsto
Asked: 2017-11-06 18:24:36 +0800 CST

具有开始和结束日期时间列的事件表的 MySQL 分区

  • 772

我很好奇有什么解决方案可以对记录事件的表进行分区，既可以进行高效查询，又可以在截断数据时发挥修剪的优势。

假设我有一个简单的表来记录来自不同位置的事件：

tblEvents (
  event_id,
  location_id,
  start_datetime,
  end_datetime
)

此表上的大多数查询将采用以下形式：

SELECT    event_id
FROM      tblEvents
WHERE     location_id = @queried_location_id
AND       start_datetime < @queried_end_datetime
AND       end_datetime > @queried_start_datetime

迄今为止，我已经按 location_id 进行了分区，并且只是对日期时间列进行了索引。就性能而言，这已经足够了，而且我从来没有计划在数据库中保留超过几个月的数据，所以它似乎也是面向未来的。

当我实际从这个表中清除数据时出现了问题（它非常大并且还涉及复制）。事实证明，使用DELETE FROM tblEvents WHERE start_datetime < @some_date非常慢，并且会在其他客户端发出请求时产生问题（不足为奇）。

按任一日期时间列进行分区都不好。虽然它解决了我的数据清除问题，但它使上面的查询效率低下，因为它仍然需要根据查询的日期查看多个分区。

我在这里缺少一个通用的解决方案吗？有没有更有效的方法来清除我的数据？还是有一种我错过的更智能的分区/索引方法？

mysql index

1 个回答

  1. Best Answer

    给定位置的时间范围是否重叠？

    如果需要删除where的所有行start_datetime < @some_date，那么最好的办法是通过PARTITIONingbased on start_datetime。使用PARTITION BY RANGE(TO_DAYS(start_datetime))它并基于几天、几周或几个月，这样你就有大约 20-50 个分区。查看详情。

    方案A：

    一旦你以这种方式分区，然后按此顺序拥有PRIMARY KEYbe 。如果这 3 列不足以成为唯一的，并且，比如说，你有，那么这样做：(location_id, end_datetime, start_datetime)id AUTO_INCREMENT

    PRIMARY KEY(location_id, end_datetime, start_datetime, id),
INDEX(id)

    现在我们来分析

    WHERE     location_id = @queried_location_id
AND       start_datetime < @queried_end_datetime
AND       end_datetime > @queried_start_datetime

    发生以下步骤：

    1. 基于start_datetime. 这可能会或可能不会有多大帮助，具体取决于“开始”的新程度。平均（？），它将消除一半的分区（没有太大好处）。
    2. 使用 PK，它将找到合适location_id的和end_datetime.
    3. 在数据中向前扫描（因为 PK 与数据“聚集”在一起），检查其余的 location_ids 是否匹配。
    4. 对步骤 1 未修剪的每个分区重复
    5. 合并分区的结果。

    B计划：

    如果您的开始..结束范围主要是最近的日期，并且如果您可以基于 清除end_datetime，那么这可能会更好，因为它主要关注最后一个分区：

    PARTITION BY RANGE(TO_DAYS(end_datetime))

PRIMARY KEY(location_id, start_datetime, end_datetime, id),
INDEX(id)

    如果范围不重叠，则折叠可能会显着提高性能。

    但是......如果end_datetime是NULL最初，这会将行放入“第一个”分区。所以...确保在开始时有一个额外的分区 - 否则DROP用于清除旧记录的将错误地扔掉它们。也就是说，第一个范围是VALUES LESS THAN (0)。然后让第二个范围成为最旧的月份（或周或其他），并删除它以进行清除。

    另一个问题NULL- 当您执行 set 时end_datetime，必须将记录从一个分区移动到另一个分区，从而使该UPDATE语句的成本有些高。（它实际上是一个DELETE加号INSERT。）因此，我不推荐非常活跃的计划 B。

    • 2

相关问题