时间戳

首页 > 解决方案 > 在 MySQL 集群中导入和报告较大的数据集

php - 在 MySQL 集群中导入和报告较大的数据集

问题描述

我们有一个 MySQL (mariaDB/Galera) 集群，在一张表中包含数十亿个唯一数据点。我们需要将该表迁移到一个新的表，以整理出重复的条目，这需要很长时间，而且我们在这方面受到限制。下一步将是为给定的时间窗口和相应 NAS（现实世界中的路由器/位置）的 UUID 以及经常出现或切换 NAS 的用户的唯一 ID (MAC) 生成报告

MySQL (mariaDB/Galera) 数据库现在大小约为 25GB，这应该不是问题。但是查询用户的 UID/MAC 以及 UUID NASes/位置的报告需要很长时间。

表结构的布局如图所示。一个是实际的表，两个是可能的优化。但我真的不知道这是否会有所作为。

我们的数据库方法是正确的还是我们应该使用不同的方法（数据库、表结构、堆栈等等）（欢迎提出建议）

迁移查询（非常慢）如下：

INSERT INTO `metric_macs` m
        (`uuid`,`shortname`,`mac`,`start`,`stop`,`duration`)
    VALUES
        SELECT uuid, shortname, mac, a, b, duration
            FROM import i
    ON DUPLICATE KEY update m.id = m.id

查询唯一用户：

SELECT DISTINCT mac FROM `metric_macs` WHERE uuid in ('xxxx','yyyyy') and ( start BETWEEN '2020-01-01' and '2020-02-01' or stop BETWEEN '2020-01-01' and '2020-02-01') ;

所有数据集的计数

查询经常性用户：

SELECT id FROM `metric_macs`
    WHERE uuid in ('xxxx','yyyyy')
      and (    start BETWEEN '2020-01-01' and '2020-02-01'
            or stop BETWEEN '2020-01-01' and '2020-02-01')
    GROUP BY `mac`, `uuid`
    HAVING COUNT(*) > 1

所有数据集的计数

查询唯一位置切换用户：

SELECT uuid,mac FROM `metric_macs`
    WHERE uuid in ('xxxx','yyyyy')
      and (   start BETWEEN '2020-01-01' and '2020-02-01'
            or stop BETWEEN '2020-01-01' and '2020-02-01')
    GROUP BY `mac`, `uuid`

之后，php 用于计算具有两个以上不同 UUID 的所有用户。

该列表每 15 分钟更新一次，其中包含连接到 NAS 的 UID (MAC) 列表，该列表在过去 20 分钟内检查给定 UID(MAC) 的活动。如果有，我们更新最后一个条目的停止计数，增加 15 分钟并开始计算增益。

对不起，混乱。我们对这种报告生成方式相当陌生。有哪些可能的方法来优化数据库或查询以实现近乎即时的报告？

谢谢！

编辑：

CREATE TABLE `metric_macs` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`shortname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`mac` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
`start` datetime NOT NULL,
`stop` datetime NOT NULL,
`duration` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `metric_macs_uuid_index` (`uuid`),
KEY `metric_macs_mac_index` (`mac`),
KEY `metric_macs_start_stop_index` (`start`,`stop`),
KEY `metric_macs_uuid_start_stop_index` (`uuid`,`start`,`stop`),
KEY `metric_macs_uuid_stop_index` (`uuid`,`stop`)
) ENGINE=InnoDB AUTO_INCREMENT=357850432 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

标签： phpmysqlmariadbreportgalera

解决方案

摆脱 36 字节的 id 是件好事。但是，不要停留在 8 个字节；您可能不需要超过 4 个字节（INT UNSIGNED最大或 40 亿）或 3 个（MEDIUMINT UNSIGNED最大 16M）。

当您使用它时，删除破折号并取消 uuid 的十六进制，以便它们适合BINARY(16)（16 个字节）。

我认为您在转换中有 3 个问题需要解决：

  1. 有效地将当前架构更改为更好的架构。假设这有旧的、不变的行，您可以在后台执行此操作。
  2. 快速完成最后一步。（实际上，我们将最后这样做。）
  3. 将摄取更改为新格式。

第 0 步：获取最新的时间戳，以便在花费时间执行第 1 步后知道在哪里执行第 2 步和第 3 步。

第 1 步：构建usersand stations，它可能很简单

INSERT INTO users (user_id)
    SELECT UUID2BIN(userID)
        FROM ( SELECT DISTINCT userID ) FROM log;

（同样对于stations）

看到这个转换uuid：http: //mysql.rjweb.org/doc.php/uuid

这可能需要一些时间，但它可以有效地进行重复数据删除。

在填写步骤 2 之前，让我讨论步骤 3。

第 3 步：如果摄取率“高”，请参阅此处了解有关 ping-ponging 暂存表和批量标准化等的详细信息：http: //mysql.rjweb.org/doc.php/staging_table

但是，您的摄取率可能不会那么快。不要将IODKU 与用于从和LAST_INSERT_ID获取 id的技巧一起使用。它会“烧毁” id 并威胁要溢出您的 INT/MEDIUMINT 。相反，请参阅上面的链接。usersstationsid

插入time_table，如果不超过每秒 100 个 (HDD) 或 1000 个每秒 (SSD)，INSERT在您获得必要的 ID 时可能很简单

INSERT INTO time_table (user_id, station_id, start_time, stop_time)
    VALUES (
             ( SELECT id FROM users WHERE userID = uuid2bin('...') ),
             ( SELECT id FROM stations WHERE userID = uuid2bin('...') ),
           '...', '...'
           );

返回第 2 步。您在旧表中保存了一堆行。你保存了这些的开始日期。log像我的链接中讨论的那样，就好像它是“临时表”一样进行批量标准化和批量插入。

当新表“不完整”时，这应该允许您以零停机时间和少量时间进行转换。

我没有解释为什么“报告需要很长时间”。我需要看看SELECTs。同时，这里有两个想法：

如果您构建新的类似 INT 的 id，请按日期对它们进行排序，以便它们至少按时间顺序排序，因此对于某些类型的查询更好地聚集在一起。

通常，构建和维护“汇总表”可以使报表运行得更快。见http://mysql.rjweb.org/doc.php/summarytables

“查询经常性用户：”有多个查询性能问题。除非我的方法不够充分，否则我不想深入细节。

推荐阅读