mysql - 每年对 3b 条记录的 mysql 表进行分区

Question

处理 3b 记录表(几天内并发读/写非常频繁)的好方法是什么？

Linux 服务器，运行 MySQL v8.0.15。

我有这个表将记录设备数据历史记录。该表需要将其数据保留一年，可能是两年。增长率非常高:8,175,000 条记录/天(1mo=245m 记录，1y=2.98b 记录)。在设备数量增长的情况下，该表预计能够处理。近几天读表比较频繁，超过一周则频率明显下降。

有多个并发连接对该表进行读写，并且读/写的目标彼此非常接近，因此发生死锁/表锁，但已得到处理(重试，事务大小较小)。

我现在使用每日分区，因为读取几乎不会跨越 >1 个分区。但是，分区太多，无法保留 1 年的数据。使用 cron 按计划创建或删除分区。

CREATE TABLE `table1` (
  `group_id` tinyint(4) NOT NULL,
  `DeviceId` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `DataTime` datetime NOT NULL,
  `first_log` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `first_res` tinyint(1) NOT NULL DEFAULT '0',
  `last_log` datetime DEFAULT NULL,
  `last_res` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`group_id`,`DeviceId`,`DataTime`),
  KEY `group_id` (`group_id`,`DataTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY RANGE (to_days(`DataTime`))
(
 PARTITION p_20191124 VALUES LESS THAN (737753) ENGINE = InnoDB,
 PARTITION p_20191125 VALUES LESS THAN (737754) ENGINE = InnoDB,
 PARTITION p_20191126 VALUES LESS THAN (737755) ENGINE = InnoDB,
 PARTITION p_20191127 VALUES LESS THAN (737756) ENGINE = InnoDB,
 PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

插入的执行大小约为 1500/批处理:

INSERT INTO table1(group_id, DeviceId, DataTime, first_result) 
VALUES(%s, %s, FROM_UNIXTIME(%s), %s) 
ON DUPLICATE KEY UPDATE last_log=NOW(), last_res=values(first_result);

选择主要是通过DataTime或DeviceId获取计数，针对特定分区。

SELECT DataTime, COUNT(*) ct FROM table1 partition(p_20191126) 
WHERE group_id=1 GROUP BY DataTime HAVING ct<50;

SELECT DeviceId, COUNT(*) ct FROM table1 partition(p_20191126) 
WHERE group_id=1 GROUP BY DeviceId HAVING ct<50;

所以问题是:

1. 根据RickJames blog ，在一张​​表中拥有> 50个分区并不是一个好主意，但是如果每月放置分区，则一个分区中有245m的rec。这里使用的最佳分区范围是多少？当前的 mysql 版本是否仍会出现 RJ 的博客？
2. 不对表进行分区是个好主意吗？ (atm指数运行良好)

注意:我已阅读this堆栈问题，拥有多个表很痛苦，因此，如果没有必要，我希望不要破坏表。此外，目前还无法进行分片。

Answer 1

首先，INSERTing 100 条记录/秒是一个潜在的瓶颈。我希望您使用 SSD。让我看看SHOW CREATE TABLE。解释数据如何到达(批量、一次一个、来自多个源等)，因为我们需要讨论对输入行进行批处理，即使您有 SSD。

保留 1 年或 2 年？是的，PARTITIONing 会有所帮助，但仅限于通过DROP PARTITION 进行删除。使用每月分区并使用 PARTITION BY RANGE(TO_DAYS(DataTime))。 (请参阅您已经找到的我的博客。)

DeviceID的平均长度是多少？通常我什至不会提及规范化 VARCHAR(10)，但对于数十亿行，这可能是值得的。

您拥有的主键意味着设备不会在不到一秒的时间内提供两个值？

列名称中的“first”和“last”是什么意思？

在旧版本的MySQL中，分区数量对性能有影响，因此推荐50。8.0的数据字典可能对此有有利的影响，但我还没有实验过50是否应该提出。

分区的大小对任何事情的影响都很小。

为了判断索引，让我们看看查询。

分片不可能吗？是否有太多查询需要同时获取多个设备？

你们有汇总表吗？这是数据仓库避免性能问题的主要方法。 (请参阅我的博客。)并且，如果您对输入进行某种“暂存”，则可以在接触事实表之前对汇总表进行扩充。此时，事实表只是一个存档；没有常规的SELECTs需要触摸它吗？ (再次，让我们看看主要查询。)

每天一张 table (或任何单位)是一个很大的禁忌。

通过 IODKU 摄取

对于通过 IODKU 批量插入，请考虑以下内容:

1. 收集临时表中的 1500 行，最好使用单个 1500 行INSERT。
2. 根据需要修改该数据

3. 执行一个 IODKU..SELECT:

   INSERT INTO table1(group_id, DeviceId, DataTime, first_result) 
       ON DUPLICATE KEY UPDATE
           last_log=NOW(), last_res=values(first_result)
       SELECT group_id, DeviceId, DataTime, first_result
           FROM tmp_table;
   

如果有必要，SELECT可以进行一些重复数据删除等操作。

这种方法可能比 1500 个单独的 IODKU 快得多。

设备ID

如果DeviceID始终为10个字符并且仅限于英文字母和数字，则将其设置为

CHAR(10) CHARACTER SET ascii

然后在 COLLATION ascii_general_ci 和 COLLATION ascii_bin 之间进行选择，具体取决于您是否允许大小写折叠。