mysql - 对大型MySQL InnoDB表进行分区的方法

Question

我有一个表，每年将接收4500-6000万行物联网类型的数据。最初的愿望是永远不要删除数据，因为我们可能会将其用于不同类型的“大数据分析”。今天这个表需要支持我们的在线应用程序。应用程序需要快速的数据查询时间，通常是在过去的30或90天内。所以我认为分区可能是个好主意。
我们目前的想法是使用“老化”列，在本例中称为partition_id。过去30天内的记录是分区id=0。31天到90天的记录是分区id=1，其他的都在分区id=2中。
所有查询都将“知道”要使用哪个分区id。其中，查询总是按sensor_id、badge_id等（请参见索引）组中的所有sensor_id或badge_id，即sensor_id in ( 3, 15, 35, 100, 1024)等。
这是表的定义

    CREATE TABLE 'device_messages' (
    'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
    'partition_id' tinyint(3) unsigned NOT NULL DEFAULT '0',
    'customer_id' int(10) unsigned NOT NULL,
    'unix_timestamp' double(12, 2) NOT NULL,
    'timestamp' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    'timezone_id' smallint(5) unsigned NOT NULL,
    'event_date' date NOT NULL,
    'is_day_shift' tinyint(1) unsigned NOT NULL,
    'msg_id' tinyint(3) unsigned NOT NULL,
    'sensor_id' int(10) unsigned NOT NULL,
    'sensor_role_id' int(10) unsigned NOT NULL,
    'sensor_box_build_id' int(10) unsigned NOT NULL,
    'gateway_id' int(10) unsigned NOT NULL,
    'location_hierarchy_id' int(10) unsigned NOT NULL,
    'group_hierarchy_id' int(10) unsigned DEFAULT NULL,
    'badge_id' int(10) unsigned NOT NULL,
    'is_badge_deleted' tinyint(1) DEFAULT NULL,
    'user_id' int(10) unsigned DEFAULT NULL,
    'is_user_deleted' tinyint(1) DEFAULT NULL,
    'badge_battery' double unsigned DEFAULT NULL,
    'scan_duration' int(10) unsigned DEFAULT NULL,
    'reading_count' tinyint(3) unsigned DEFAULT NULL,
    'median_rssi_reading' tinyint(4) DEFAULT NULL,
    'powerup_counter' int(10) unsigned DEFAULT NULL,
    'tx_counter' int(10) unsigned DEFAULT NULL,
    'activity_counter' int(10) unsigned DEFAULT NULL,
    'still_counter' int(10) unsigned DEFAULT NULL,
    'created_at' timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ('id', 'partition_id', 'sensor_id', 'event_date'),
    KEY 'sensor_id_query_index' ('partition_id', 'sensor_id', 'event_date'),
    KEY 'badge_id_query_index' ('partition_id', 'badge_id', 'event_date'),
    KEY 'location_hierarchy_id_query_index' ('partition_id', 'location_hierarchy_id', 'event_date'),
    KEY 'group_hierarchy_id_query_index' ('partition_id', 'group_hierarchy_id', 'event_date')
    ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci
    PARTITION BY RANGE (partition_id)
    SUBPARTITION BY HASH (sensor_id)
    (PARTITION fresh VALUES LESS THAN (1)
    (SUBPARTITION f0 ENGINE = InnoDB,
    SUBPARTITION f1 ENGINE = InnoDB,
    SUBPARTITION f2 ENGINE = InnoDB,
    SUBPARTITION f3 ENGINE = InnoDB,
    SUBPARTITION f4 ENGINE = InnoDB,
    SUBPARTITION f5 ENGINE = InnoDB,
    SUBPARTITION f6 ENGINE = InnoDB,
    SUBPARTITION f7 ENGINE = InnoDB,
    SUBPARTITION f8 ENGINE = InnoDB,
    SUBPARTITION f9 ENGINE = InnoDB),
    PARTITION archive VALUES LESS THAN (2)
    (SUBPARTITION a0 ENGINE = InnoDB,
    SUBPARTITION a1 ENGINE = InnoDB,
    SUBPARTITION a2 ENGINE = InnoDB,
    SUBPARTITION a3 ENGINE = InnoDB,
    SUBPARTITION a4 ENGINE = InnoDB,
    SUBPARTITION a5 ENGINE = InnoDB,
    SUBPARTITION a6 ENGINE = InnoDB,
    SUBPARTITION a7 ENGINE = InnoDB,
    SUBPARTITION a8 ENGINE = InnoDB,
    SUBPARTITION a9 ENGINE = InnoDB),
    PARTITION deep_archive VALUES LESS THAN MAXVALUE
    (SUBPARTITION C0 ENGINE = InnoDB,
    SUBPARTITION C1 ENGINE = InnoDB,
    SUBPARTITION C2 ENGINE = InnoDB,
    SUBPARTITION C3 ENGINE = InnoDB,
    SUBPARTITION C4 ENGINE = InnoDB,
    SUBPARTITION C5 ENGINE = InnoDB,
    SUBPARTITION C6 ENGINE = InnoDB,
    SUBPARTITION C7 ENGINE = InnoDB,
    SUBPARTITION C8 ENGINE = InnoDB,
    SUBPARTITION C9 ENGINE = InnoDB)) ;

这个表定义目前正在处理1600万行数据，查询似乎很快。然而，我担心这种实施的长期可持续性。另外，我现在看到，随着我们通过每周更新成千上万条记录的分区id来“老化”记录，我们在分区上做了大量的改动。
这些查询几乎总是这种情况的一种变体：

    SELECT * FROM device_messages
    WHERE partition_id = 0
      AND 'event_date' BETWEEN '2019-08-07' AND '2019-08-13'
      AND 'sensor_id' in ( 3317, 3322, 3323, 3327, 3328, 3329, 3331, 3332, 3333, 3334, 3335, 3336, 3337, 3338, 3339, 3340, 3341, 3342 )
      ORDER BY 'unix_timestamp' asc

列表中可能只有一个传感器id，但通常会有几个。
我花了好几个小时研究分区，但是还没有找到一个例子或者讨论这个用例的分区。因为，我们使用的是 partition_id的人工老化列，所以我也意识到我不能对分区进行任何真正的操作，所以我认为我至少失去了分区的一些值。
关于分区方案甚至其他方法的建议将非常受欢迎。

Answer 1

PARTITIONing不是性能灵丹妙药。
不删除？好的，主要用途（DROP PARTITION比DELETE快）不可用。
摘要表是解决数据仓库性能问题的答案。见http://mysql.rjweb.org/doc.php/summarytables
（现在我将详细阅读问题和任何答案；也许我会回来有一些改变。）
图式批判
由于您预计有数百万行，因此收缩数据类型相当重要。
customer_id是一个4字节整数。如果预期不超过几千，请使用2字节SMALLINT UNSIGNED。另请参见MEDIUMINT UNSIGNED。其他所有INTs也一样。
'unix_timestamp' double(12, 2)很奇怪。TIMESTAMP(2)有什么问题，它会更小？
'badge_battery' double——分辨率过高？DOUBLE是8字节；FLOAT是4，有~7个显著数字。
大多数列都NULLable。它们真的是可选的吗？（NULL的开销很小；在实际情况下使用NOT NULL。）
当行变得不再“新鲜”时，您是否要做大量的UPDATE来更改该列？请考虑该声明将产生的巨大影响。最好创建新分区并更改查询。如果您有AND some_date > some_column并且该列是PARTITION BY RANGE(TO_DAYS(..))的话，这个方法尤其有效。
我还没有看到SUBPARTITIONing的理由。
非分区
鉴于这是典型的：

SELECT * FROM device_messages
WHERE partition_id = 0
  AND 'event_date' BETWEEN '2019-08-07' AND '2019-08-13'
  AND 'sensor_id' in ( 3317, 3322, 3323, 3327, 3328, 3329, 3331, 3332,
                       3333, 3334, 3335, 3336, 3337, 3338, 3339, 3340, 3341, 3342 )
  ORDER BY 'unix_timestamp' asc

我建议如下：
无分区（且无 partition_key）
掷 event_date；改为使用 unix_timestamp
按如下所示更改选择：
...

SELECT * FROM device_messages
WHERE `unix_timestamp` >= '2019-08-07'
  AND `unix_timestamp`  < '2019-08-07' + INTERVAL 1 WEEK
  AND sensor_id in ( 3317, 3322, 3323, 3327, 3328, 3329, 3331, 3332,
                     3333, 3334, 3335, 3336, 3337, 3338, 3339, 3340, 3341, 3342 )
  ORDER BY `unix_timestamp` asc

加上

INDEX(sensor_id, `unix_timestamp`)

我想以下是处理过程。（注意：这可能比MySQL/MariaDB的一些旧版本更糟。）
向下钻取新指数的BTree至[3317，'2019-08-07']
向前扫描（将行收集到临时文件中）一周
对每个传感器重复1,2次。
对临时表进行排序（以满足 ORDER BY）。
传递结果行。
这里的关键点是它只读取需要传递的行（加上每个传感器一个额外的行，以实现一周结束）。因为这是一张很大的桌子，所以这是最好的
额外的排序（cf Explain的“filesort”）是必需的，因为无法按 ORDER BY顺序获取行。
还有另一个优化。。。
在上面，索引是有序的，但数据不是。我们可以解决如下问题：

PRIMARY KEY(sensor_id, `unix_timestamp`, id),  -- (`id` adds uniqueness)
INDEX(id),   -- to keep AUTO_INCREMENT happy

（跳过我之前的索引建议）
如果表变得比缓冲池大，则此修改将特别有用。这是因为修改后的PK提供了“集群”。
更规范化
我怀疑这30列中的许多行与行是相同的，特别是对于同一个传感器（又称为“设备”）。如果我是正确的，那么您“应该”从这个巨大的表中删除这些列，并将它们放入另一个表中，进行重复数据消除。
这将比调整int等节省更多的空间。
汇总表
同样，使用您的查询，让我们讨论一下什么汇总表是有用的。但首先，我不认为有什么可以总结的。我希望看到 device_value FLOAT或类似的东西。我将用它作为一个假设的例子：

CREATE TABLE Summary (
        event_date DATE NOT NULL, -- reconstructed from `unix_timestamp`
        sensor_id ...,
        ct SMALLINT UNSIGNED,  -- number of readings for the day
        sum_value FLOAT NOT NULL,  -- SUM(device_value)
        sum2  -- if you need standard deviation
        min_value, etc   -- if you want those
        PRIMARY KEY(sensor_id, event_date)
    ) ENGINE=InnoDB;

每天一次：

INSERT INTO Summary (sensor_id, event_date, ct, sum_value, ...)
        SELECT sensor_id, DATE(`unix_timestamp`),
                          COUNT(*), SUM(device_value), ...
            FROM device_messages
            WHERE `unix_timestamp` >= CURDATE() - INTERVAL 1 DAY
             AND `unix_timestamp`  < CURDATE()
           GROUP BY sensor_id;

（有更有力的方法；有更及时的方法；等等）或者你可以用小时而不是一天来总结。在任何情况下，您都可以通过对每日摘要的总和求和来获得任意的日期范围。

 Average:  SUM(sum_value) / SUM(ct)

裁员？
unix_timestamp、 timestamp、 event_date、 created_at——都有“相同”的价值和意义？?
关于〈cc〉的一个注：几乎总是比有一个额外的列更容易区分〈cc〉或〈cc〉，尤其是比同时有〈cc〉和〈cc〉更容易。
如果没有日期栏，检查一天内的所有读数需要如下所示：

    WHERE `dt` >= '2019-08-07'
      AND `dt`  < '2019-08-07' + INTERVAL 1 DAY