MySQL操作开销和处理大表

UUID效率低下

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

即使是通过主键获取一行的简单操作,通常也会涉及磁盘命中。这加起来会产生大量的I/O,可能太多了,无法容忍。

50M选择/天=600/秒。你的磁盘系统每秒能处理近600次读取吗?

不记录

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

一定要避免部分缓解这个问题 SELECT * 并指定 只有 您实际需要的列。这个 五月 避免一些额外的磁盘命中。

这一建议尤其适用于 VALUE 和 ALERT .

懒惰评估

如果选择有过滤( WHERE , LIMIT 等)没有充分索引,您可能会获取那些庞大的列,只是在过滤时丢弃它们。

有时可以通过构建一个执行过滤的派生表来避免额外的I/O,留下外部 SELECT 只获取最少量的列。(向我们展示您正在使用的一个非平凡的查询;我们可以进一步讨论。)

缩小UUID

您使用的是哪种类型的UUID?如果它是MySQL使用的“类型1”,则可以重新排列id的位,使其大致按时间顺序排列;这对以下方面有很大帮助 某些 查询。

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

细节: http://mysql.rjweb.org/doc.php/uuid

即使没有这个,36字节的UUID也可以很容易地缩小到16字节,以便放入 BINARY(16) 而不是你所拥有的38个字节(字符串36个,不必要的2个 VAR .)

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

MariaDB 10.7“将具有”UUID 数据类型 这消除了本答案中讨论的大部分内容。

压缩

(我不赞成InnoDB的压缩,所以我不会提及。不管怎样,它不太可能提供超过2倍的压缩。)

如果压缩这些XML字符串,它们将收缩(大约)3倍。这将节省约2TB。

但是在客户端进行压缩(和解压缩);这减轻了服务器的负担,降低了客户端和服务器之间的带宽。

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

注意,压缩后,该列应 VARBINARY(...) 或 ...BLOB ,不是文本类型。

其他“文本”列也是如此,通常是“大”列。

拆分桌子

我什么也看不到 重要的 将两个笨重的柱子分开的好处或缺点。事实上,它们是“非正式的”,这意味着InnoDB已经提供了你提出的大部分好处。(MyISAM会从你的拆分中受益。但不要使用那个引擎。)

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

10M插入件

1000万1行 INSERT 声明?按照你目前的设计,这相当于3000万次磁盘点击。您的磁盘每秒可以支持多少次写入?

我建议对插件进行“批处理”。单身 插入 100行 正常地 运行速度是100个单行插入的10倍。但有3个 随机 hits——一个用于PK,一个用于每个二级索引。所以,我不知道在你的情况下,这种10倍的加速是否真的会发生。

皇家音乐学院

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

如果5%的基于uuid的索引可以放入缓冲池,那么95%的选择将需要命中磁盘。 这是我大部分讨论背后的原则。

注:PK是一个索引,但包括所有数据。

注意:通过辅助索引的查找涉及两个BTree查找。如果每个都基于一个uuid,那么很有可能出现两次磁盘命中。

注意:您的双表方法将涉及2次查找。每一项都可能低于上述“95%”,但仍然如此。

分区和并行查询

Aurora在这方面领先于MySQL(和MariaDB)。但仍然没有多少好处。

将每个分区划分为不同的分区,消除了较浅的BTree的好处。(这甚至可能会减缓速度。)

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

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

一种方式 PARTITIONing 当您需要二维索引时,可以从中受益。例如: WHERE some date range AND some other test .通过按日期分区,同时拥有 PRIMARY KEY “分区修剪”有助于“其他测试”,选择要查看的分区,然后PK更快地到达所需的行。(这似乎不是你的用例。)

您的主要疑问不会从任何形式的 分区 。所以,我推荐 反对 分区。

索引

鉴于几乎所有 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问题主导了性能问题。)

正如你所说,如果没有几天的停机时间,你的桌子现在太大了,无法重新组织。所以再加一张桌子。

要向这样的实体(表)添加属性(列)而不完全重新组织表,请创建另一个表,一个键值表。它应该与此表具有相同的PK,并将保存新数据。

CREATE TABLE app_uses_attributes (
  ID varchar(36) NOT NULL,
  NAME varchar(255) NOT NULL,
  VALUE varchar(4000),
  PRIMARY KEY (ID, NAME),
  INDEX app_uses_attributes_byvalue (NAME, VALUE(400)),  
  INDEX app_uses_attributes_byid (NAME, ID, VALUE(400))  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

现在,您可以将新的属性数据插入到这个新表中,而不是将其插入到现有的怪物表中。

假设你有两个新属性。我们给他们打电话吧 SENDER 和 RECIPIENT 例如。你可以这样插入它们,当你知道 ID 价值。

INSERT INTO app_uses_attributes (ID, NAME, VALUE) VALUES(?, 'SENDER', 'OJones')
INSERT INTO app_uses_attributes (ID, NAME, VALUE) VALUES(?, 'RECIPIENT', 'SUDARSHAN')

然后,当你想检索它们时,使用一些左键。

SELECT app_uses.*, sender.VALUE SENDER, recip.VALUE RECIPIENT
  FROM app_uses
  LEFT JOIN app_uses_attributes sender 
           ON app_uses.ID = sender.ID AND sender.NAME = 'SENDER'
  LEFT JOIN app_uses_attributes recip ON app_uses.ID = recip.ID
           ON app_uses.ID = sender.ID AND sender.NAME = 'RECIPIENT'
 WHERE whatever filters you need

要搜索,你可以做这样的事情。由于 (NAME, VALUE(400)) 新桌子上的索引。

SELECT app_uses.*, recip.VALUE RECIPIENT
  FROM app_uses
  LEFT JOIN app_uses_attributes recip ON app_uses.ID = recip.ID
           ON app_uses.ID = recip.ID AND recip.NAME = 'RECIPIENT'
 WHERE recip.VALUE LIKE 'SUDAR%'

对于像您这样的大型表中的实体添加属性,这种键值设置是一个好主意。

1. 您可以在不重新组织主表的情况下添加属性。
2. 您可以随时添加新属性。只需使用新 NAME 对于每一个新的。以后您不需要更改此表来添加新属性。
3. 您没有旧行的新属性的数据。在太空中,你无需付出任何代价。设置它也不会花费你很多时间。新表中缺少的行只意味着NULL数据(这就是LEFT JOIN为您做的)。
4. 您现有的软件不需要更改。只有使用新属性的软件才会受到影响。

查询当然更复杂,但您可以创建视图来封装它们。

众所周知,这种方法既常见又稳健:WordPress使用它。