MySQL Large Table Sharding to Smaller Table based on Unique ID

Question

我们有一个包含以下列的大型 MySQL 表 (device_data):

ID (int)
dt (timestamp)
serial_number (char(20))
data1 (double)
data2 (double)
... // other columns

该表每天接收大约 1000 万行。

我们通过根据时间戳的日期 (device_data_YYYYMMDD) 分隔表来完成分片。但是，我们认为这不是有效的，因为我们的大多数查询(如下所示)总是检查“serial_number”并将在多个日期执行。

SELECT * FROM device_data WHERE serial_number = 'XXX' AND dt >= '2018-01-01' AND dt <= '2018-01-07';

因此，我们认为根据序列号创建分片会更有效。基本上，我们将有:

device_data_<serial_number>
device_data_0012393746
device_data_7891238456

因此，当我们想要查找特定设备的数据时，我们可以轻松地引用为:

SELECT * FROM device_data_<serial_number> WHERE dt >= '2018-01-01' AND dt <= '2018-01-07';

这种方法似乎很有效，因为:

1. 应用程序始终会首先访问基于设备的数据。
2. 我们已经检查过没有先指定设备序列号就访问数据的查询。
3. 每个设备的表会相对较小(每天 9000 行)

我们认为我们将面临的一些挑战是:

1. 我们有很多设备。这意味着表 device_data_ 也会有很多。我已经检查过 MySQL 没有对数据库中的表数提供限制。与将它们放在一张表中相比，这会影响性能吗？
2. 这对以后我们想要扩展 MySQL(例如使用主/从等)有何影响？
3. 是否有其他替代方案/解决方案来解决这个问题？

更新。下面是我们现有表的显示创建表结果:

CREATE TABLE `test_udp_new` (
 `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
 `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `device_sn` varchar(20) NOT NULL,
 `gps_date` datetime NOT NULL,
 `lat` decimal(10,5) DEFAULT NULL,
 `lng` decimal(10,5) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `device_sn_2` (`dt`,`device_sn`),
 KEY `dt` (`dt`),
 KEY `data` (`data`) USING BTREE,
 KEY `test_udp_new_device_sn_dt_index` (`device_sn`,`dt`),
 KEY `test_udp_new_device_sn_data_dt_index` (`device_sn`,`data`,`dt`)
) ENGINE=InnoDB AUTO_INCREMENT=44449751 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

运行最频繁的查询:

SELECT  *
    FROM  test_udp_new
    WHERE  device_sn = 'xxx'
      AND  dt >= 'xxx'
      AND  dt <= 'xxx'
    ORDER BY  dt DESC;

Answer 1

处理那个查询的最佳方式是在非分区表中

INDEX(serial_number, dt)

更好的方法是更改​​PRIMARY KEY。假设您当前有 id AUTO_INCREMENT，因为没有适合作为“自然 PK”的唯一列组合，

PRIMARY KEY(serial_number, dt, id),  -- to optimize that query
INDEX(id)  -- to keep AUTO_INCREMENT happy

如果有其他经常运行的查询，请提供；这可能会伤害他们。在大型表中，找到最佳索引是一项杂耍任务。

其他评论:

• 分区实际上可以加速处理的用例很少。
• 制作大量“相同”的表格是一场维护噩梦，而且同样不会带来性能优势。 stackoverflow 上可能有一百个问答大喊不要这样做。
• 通过在 PRIMARY KEY 中使用 serial_number first，所有引用单个 serial_number 的查询都可能受益。
• 一百万个serial_numbers？没问题。
• 分区的一个常见用例涉及清除“旧”数据。这是因为大的DELETE 比DROP PARTITION 的成本要高得多。这涉及 PARTITION BY RANGE(TO_DAYS(dt))。如果你对此感兴趣，我的PK建议仍然有效。 (无论有无此分区，相关查询的运行速度都差不多。)
• table 的容量将超过您的磁盘需要多少个月？ (如果这是一个问题，让我们讨论一下。)
• 是否需要 8 字节 DOUBLE？ FLOAT 具有大约 7 位有效数字的精度，仅占用 4 个字节。
• 您正在使用 InnoDB？
• serial_number 是否固定为 20 个字符？如果不是，请使用 VARCHAR。另外，CHARACTER SET ascii 可能比默认的 utf8 更好？
• 每个表(或表的每个分区)至少涉及操作系统必须处理的一个文件。当你有“太多”时，操作系统发出呻吟声，通常在 MySQL 发出呻吟声之前。 (很难使任何一方因过量服用而“死去”。)