主页 / dba / 问题 / 343077
Accepted
Stephan Vos
Asked: 2024-10-18 21:22:50 +0800 CST

大型分区表的最佳索引

  • 772

几年前，我使用 Rick James 的分区和索引指南创建了这个，但现在再次阅读该教程后，我不太确定我是否理解正确，以及下表是否是最佳的。（指南参考）

我认为其中一个常规索引是多余的，不确定我是否应该忽略分区键列 (datetime) 或更改其中一个索引以使分区键列 (datetime) 位于最后。在分区内选择特定日期时间范围时，将其作为索引的一部分是否有助于提高性能？

另外，是否有主键（deviceService，datetime）并在自动增量（id）上有一个索引会更好吗？

下面的基于时间序列的表格每个月分区有大约 8000 多万行。它几乎总是针对特定的日期时间范围和 deviceServiceId 的子集进行查询。

分区纯粹是为了存档和表维护，而不是为了性能。

CREATE TABLE `serviceResultLinkState` 
(
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `deviceServiceId` int(10) unsigned NOT NULL,
  `dateTime` datetime NOT NULL DEFAULT current_timestamp(),
  `priority` tinyint(1) unsigned NOT NULL,
  `alias` varchar(20) NOT NULL,
  `active` tinyint(3) unsigned DEFAULT NULL,
  `stable` tinyint(3) unsigned DEFAULT NULL,
  `attempts` int(5) unsigned DEFAULT NULL,
  `retries` int(5) unsigned DEFAULT NULL,
  `resultState` tinyint(3) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`dateTime`),
  KEY `dt_ds_idx` (`deviceServiceId`),
  KEY `datetime_idx` (`dateTime`,`deviceServiceId`)
) ENGINE=InnoDB AUTO_INCREMENT=24814201874 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY RANGE  COLUMNS(`dateTime`)
(PARTITION `p_202405` VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202406` VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202407` VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202408` VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202409` VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202410` VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202411` VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_max` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
mysql

2 个回答

  1. Best Answer
    • “不确定该表是否最佳” 最佳的是什么？速度？空间？插入？更新？选择？该表看起来可以“工作”。
    • 表定义暗示它可能会在大约 6 个月后被清除。如果是这样，那么你拥有的数据看起来不错。
    • “认为其中一个正常索引是多余的”——您拥有的 3 个（包括 PK）并不多余。有些可能没有使用，这是另一个问题。如果没有看到主要查询，我无法解决这个问题。
    • “主键（deviceService，datetime）”——如果该对一定是“唯一的”，那么它可能是一个更好的 PK。
    • id如果您有上述 PK，则可能不需要。 （同样，这取决于查询。）如果id需要，那么INDEX(id)除了上述 PK 之外， 就足够了AUTO_INCREMENT。
    • “将其作为索引的一部分是否有助于在分区内选择特定日期时间范围时提高性能？”也许吧。让我看看查询。问题归结为“这个复合索引对这个查询有用吗？”。我在配套的Index Cookbook中讨论了“这个查询的最佳复合索引是什么”
    • “查询特定日期时间范围和 deviceServiceId 子集”——WHERE x IN (...) AND y BETWEEN...很难优化。分区在某些情况下会有所帮助，但在其他情况下则不会。 会显示哪些分区用于查询，但不会提供太多线索，说明在其使用的每个分区中处理或处理EXPLAIN SELECT ...得如何。INBETWEEN
    • “分区纯粹是为了存档和表维护，而不是为了提高性能。”——这似乎使您的其余问题无效。
    • 这是“高级”数据吗？如果是的话，我还有更多评论。

    更多的

    • “所以我想知道使用 PK 是否deviceServiceId,datetime会缩短选择查找时间。”——是的。请参阅此处的讨论： https: //mysql.rjweb.org/doc.php/mysql_sensor
    • （我会每周进行分区并每周清除一次。但是每月一次也可以。）
    • 绘制数据时...如果绘图中有数百万个数据点，最好只绘制数百或数千个数据点。通过汇总表执行此操作
    • 我id甚至会避免“维护”操作。在某些情况下，id这会导致不必要的开销。
    • WHERE x IN (...) AND y BETWEEN...可能被 替换UNION ALL。视情况而定。
    • “老年数据”——我想我当时有点“老年痴呆”。我的意思是“传感器数据”。

    更多

    • 按小时和/或按天汇总（通过汇总表）。如果您需要按“小时”来汇总任何内容，那么按“天”汇总可能就是两倍。
    • 汇总可以（最好）包括最大值/最小值。
    • 0

    • “不确定该表是否最佳” 最适合什么？速度？空间？插入？更新？选择？该表看起来会“工作”。
      （选择速度更令人担忧，因为这主要用于绘制特定 deviceServiceId 的指标时间序列图。所以我想知道通过 deviceServiceId、datetime 进行 PK 是否会缩短选择查找时间。请参阅下面的主要查询）

    • “表定义暗示它可能会在大约 6 个月后被清除。如果是这样，那么你拥有的看起来不错。” （正确）

    • “认为其中一个正常索引是多余的”——您拥有的 3 个（包括 PK）不是多余的。有些可能没有使用，这是一个不同的问题。没有看到主要查询，我无法解决这个问题。
      （这是针对此表运行的最常见查询类型，该表按间隔分钟包含数据。可能具有日期时间变化，具体取决于查询数据的时间段：
      从 serviceResultLinkState sr 中选择 sr.dateTime、sr.alias、sr.resultState，其中 sr.deviceServiceId = 567403 和 dateTime >= curdate() - 间隔 24 小时；）

    • “主键（deviceService，datetime）”——如果此对必须是“唯一的”，那么它可能是一个更好的 PK。
      （我认为它将是唯一的，因为每分钟都会针对 deviceServiceId 保留指标，因此时间戳应该是唯一的）

    • “如果您有上述 PK，则可能不需要 id。 （同样，这取决于查询。）如果需要 id，那么除了上述 PK 之外，INDEX(id) 也足以满足 AUTO_INCREMENT 的要求。”
      （id 上的索引在过去很有用，因为它允许人们以更快的方式使用 id 范围来寻址特定数据块以进行维护类型的操作，但也许修改后的 pk 会有同样的好处？）

    • “将其作为索引的一部分是否有助于在分区内选择特定日期时间范围时提高性能？”也许吧。让我看看查询。问题归结为“这个复合索引对这个查询有用吗？”。我在配套的 Index Cookbook 中讨论了“这个查询的最佳复合索引是什么”，“查询特定日期时间范围和 deviceServiceId 的子集”——WHERE x IN (...) AND y BETWEEN... 很难优化。分区在某些情况下会有所帮助，但在其他情况下则不会。EXPLAIN SELECT ... 将显示哪些分区用于查询，但不会提供太多线索，说明 IN 或 BETWEEN 在其使用的每个分区中处理得如何。
      （实际上，IN 并不常用于访问此表 - 对于汇总摘要过程，我将单独处理每个 devicveServiceId 或加入另一个表，该表针对我们感兴趣的 deviceServiceId 列表进行过滤）

    • “分区纯粹是为了存档和表维护，而不是为了性能。”——这似乎使你的其他问题无效。
      （我们几乎总是会查询此表以获取最新数据，因此唯一可能跨越两个分区的情况是例如查看过去 7 天的数据，而这些数据可能在较早的分区中有一些天）

    • 这是“高级”数据吗？如果是，我还有更多评论。
      （不确定你的意思是什么？）

    • 0

相关问题