时间戳

首页 > 解决方案 > MySQL 操作开销和处理大表

mysql - MySQL 操作开销和处理大表

问题描述

这是我当前的表 DDL。它是一个 7TB 的大表，其中 6 TB 是数据，1TB 是索引。

该表中的记录数为 30 亿。

目前表上有 1000 万个插入，这是仅追加表，没有更新或删除。

我们每天从应用程序中进行 5000 万次选择。

CREATE TABLE `app_uses` (
`ID` varchar(36) NOT NULL,
`ACTION` varchar(255) NOT NULL,
`EVENT` varchar(255) NOT NULL,
`CUST_ID` varchar(36) DEFAULT NULL,
`VALUE` longtext,
`OBJECT` varchar(255) NOT NULL,
`DATE_TIME` datetime(6) DEFAULT NULL,
`GROUP` varchar(36) DEFAULT NULL,
`DISPLAY_NAME` varchar(2001) DEFAULT NULL,
`OBJ_ID` varchar(36) DEFAULT NULL,
`USER_DISPLAY` varchar(1500) DEFAULT NULL,
`USER_ID` varchar(36) DEFAULT NULL,
`NOTICE` varchar(1000) DEFAULT NULL,
`ALERT` varchar(4000) DEFAULT NULL,

PRIMARY KEY (`ID`),
KEY `IDX_APP_USES_CID_OT` (`CUST_ID`,`OBJECT`) USING BTREE,
KEY `IDX_APP_USES_OBJ_ID` (`OBJ_ID`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

应用程序性能是否良好，除了一个操作问题外没有其他问题。我们无法执行更改表（添加新的可空列），我们必须使用需要超过 1 周的 percona 工具。

存储 XML 文件的 VALUE 和 ALERT 列也有 3 TB 的存储贡献。

现在的讨论是我们是否可以将这两列存储到另一个表中，这样原始表将轻 3 TB，但我们仍然无法非常快速地执行 Alter table。

所以问题是我们应该做这个拆分吗？

迁移和破坏此表将花费很长时间且非常困难，因此在尝试此操作之前，如果值得付出如此多的努力，我们将对其进行精简。

我们正在使用 Aurora 并行查询，因此我们无法对该表进行分区，因为 Aurora 并行查询不适用于分区表

我们还必须在我们的应用程序中进行更改以适应损坏的表更改。

如果将所有数据保存在同一张表中，我们有什么好处吗？

标签： mysqlquery-optimizationamazon-aurora

解决方案

UUID 效率低下

将 UUID 与大型表混合时，您将遇到性能灾难。

即使是通过主键获取一行的简单操作通常也会涉及磁盘命中。这增加了很多 I/O —— 可能太多了，无法容忍。

50M 选择/天 = 600/秒。您的磁盘系统可以处理近 600 次读取/秒吗？

非记录

另一个问题——有很多“大”列。而且，平均每行 2KB，可能有很多行使用“非记录”存储。这涉及另一个磁盘命中。是什么ROW_FORMAT？

部分缓解此问题的方法是确保避免SELECT *并仅指定您实际需要的列。这可以避免一些额外的磁盘命中。

这个建议特别适用于VALUE和ALERT。

懒惰评估

如果选择具有未充分索引的过滤（WHERE、、LIMIT等），您可能正在获取那些庞大的列，只是在过滤时丢弃它们。

有时可以通过构建一个执行过滤的派生表来避免额外的 I/O，而让外部表SELECT只获取最少数量的列。（向我们展示您正在使用的重要查询；我们可以进一步讨论。）

收缩 UUID

您使用的是什么类型的 UUID？如果它是“类型 1”，就像 MySQL 使用的那样，可以重新排列 id 的位以使它们大致按时间顺序排列；这对某些查询有很大帮助。

89f7eecd-a2ac-11eb-a9c1-5c80b6213dd8
              ^   This digit is the "type"

详情：http: //mysql.rjweb.org/doc.php/uuid

即使没有这个，36 字节的 UUID 也可以很容易地缩小到 16 字节，以放入 aBINARY(16)而不是 38 字节用于您拥有的内容（36 用于字符串，2 用于不必要的VAR.）

上面的链接讨论了这种收缩。此外，8.0 内置了必要的功能。

压缩

（我不赞成 InnoDB 的压缩，所以就不提了。反正提供超过 2x 压缩的可能性不大。）

如果您压缩这些 XML 字符串，它们将缩小（大约）3 倍。这将节省大约 2TB。

但是在客户端进行压缩（和解压缩）；这减轻了服务器的负担并减少了客户端和服务器之间的带宽。

XML 是一种表示数据的庞大方式，但重新格式化它可能并非易事。（因此，我只提到压缩。）

注意，压缩后的列应该是VARBINARY(...)or ...BLOB，而不是文本类型。

对于“文本”且通常为“大”的其他列也是如此。

拆分表

我不认为拆分这两个庞大的列有任何显着的好处或缺点。它们“不公开”的事实意味着 InnoDB 已经提供了您提出的大部分好处。（MyISAM 将从您的拆分中受益。但不要使用该引擎。）

这是拆分表的一些好处，但可能没有足够的好处（在您的情况下）来保证更改。如果新表也有一个 uuid 作为 PK，则尤其如此，即使它与当前 ID 相同。

10M 插入

1000 万条 1 行INSERT语句？这将类似于您当前设计的 30M 磁盘命中。您的磁盘每秒可以支持多少次写入？

我会建议像“批量”插入这样的事情。INSERT具有 100 行的单个通常运行速度是 100 个单行插入的 10 倍。但是有 3 个随机命中 - 一个用于 PK，一个用于每个二级索引。所以，我不知道这 10 倍的加速是否真的会发生在你的情况下。

内存

RAM 越多越好。比如说，有了 7TB 的 RAM（未来是 25TB），我所说的大部分内容都会消失。但这在今天是不切实际的。因此，我正在推动缩小表大小、避免 UUID 或使它们按时间顺序排列（如果有用的话）等。

如果 5% 的基于 uuid 的索引可以放入 buffer_pool，那么 95% 的选择将需要命中磁盘。这是我很多讨论背后的原则。

注意：PK 是一个索引，但包含所有数据。

注意：通过二级索引查找涉及两次 BTree 查找。如果每个都基于一个 uuid，那么很有可能会发生两次磁盘命中。

注意：您的两表方法将涉及 2 次查找。每个都可能低于上面的“95%”，但仍然如此。

分区和并行查询

Aurora 在这方面领先于 MySQL（和 MariaDB）。但是，仍然没有太大的好处。

将每个分区放入不同分区的努力消除了较浅 BTree 的好处。（它甚至可能减慢速度。）

如果您受 CPU 限制，并行查询会有所帮助。但我预测你是 I/O-bound 并且会在 25TB 时崩溃。每个并行查询将花费大部分时间等待从磁盘读取块。

我假设每天 50M 的选择来自不同的连接？其中很多是“同时”发生的？这使您可以“并行”执行查询。我认为 Aurora 的“并行查询”是针对单一的、复杂的、SELECT可以从多个线程同时执行部分任务中受益的。

一种PARTITIONing可以受益的方法是当您需要二维索引时。例如： WHERE some date range AND some other test。通过在“其他测试”的帮助下按日期进行PRIMARY KEY分区，“分区修剪”选择要查看的分区，然后 PK 更快地到达所需的行。（这似乎不是您的用例。）

您的主要查询不会受益于任何形式的PARTITIONing. 所以，我建议不要分区。

索引

鉴于几乎所有这些SELECTs都是这两个：

select * from app_uses where ID='5labcvnaxvb11egw4w0or0wq4';
SELECT * FROM app_uses where OBJ_ID = '5ldfjkhgdfkjhg631exlwu9tkrsmv'
     ORDER BY DATE_TIME DESC;

这些是最佳的：

PRIMARY KEY(ID) -- as you have

INDEX(OBJ_ID, DATE_TIME) -- replace key(obj_id) with this

第二种情况的建议更改避免了对结果进行排序的需要，因为可以简单地按所需顺序获取所需的行。（除非它有很多行，否则这不太可能产生太大的性能差异。UUID 问题主导了性能问题。）

推荐阅读