当我将自动增量主键应用到原始数据表中时，MySQL 8中的遮罩下发生了什么？

Question

我负责使用MySQL 8.0 (InnoDb引擎)建立生产数据库。对于我来说，有必要从一个相当大的表中归档数据(数据文件占硬盘驱动器的大约50% )。

我已经了解到，使用DELETE FROM x WHERE y是一个可怕的想法，并不一定会减少磁盘空间的使用。我的新策略是：

1. 将我需要存档的数据导出到CSV (我之前就这样做过)
2. 选择我希望保存在DB中的数据，并将其放在一个新的表中(使用CREATE TABLE ... SELECT)
3. 将现有表中的PK AI NN BIGINT主键应用到新表中
4. 从当前表中删除非FK索引(以减少磁盘使用率)
5. 将FK索引应用于新表
6. 将非FK索引应用于新表。
7. 重命名旧表和新表
8. 验证新表是否有效
9. 放下旧桌子

我的问题/问题实际上是从第3步开始的:我的原始表在应用主键之前消耗了44.6GB，而当我键入这个问题时，替换文件正在按52.2GB。从技术上讲，这似乎是一个基于已经存在的数据的主键的很大空间。

有人能解释一下为什么要占用这么多空间吗？我现在没有硬盘空间了，不知道如何估计最终数据文件的大小。

下面是我用来生成临时表的CREATE语句：

CREATE TABLE ts_temp
SELECT * FROM timeseries
WHERE
        (type_id IN (1, 4, 5) AND date > CURDATE() - INTERVAL 24 MONTH)
    OR (type_id IN (8) AND date > CURDATE())
    OR (type_id IN (10, 11) AND date > CURDATE() - INTERVAL 9 MONTH)
    OR (type_id IN (12, 13, 14, 15, 22) AND date > CURDATE() - INTERVAL 6 MONTH)
    OR (type_id IN (16, 17, 19, 20, 21) AND date > CURDATE() - INTERVAL 2 MONTH)
    OR (type_id IN (2, 6) AND date > CURDATE() - INTERVAL 1 MONTH);

从这个表的CREATE工作台获取后续的MySQL语句如下所示：

CREATE TABLE `ts_temp` (
  `id` bigint unsigned NOT NULL DEFAULT '0',
  `item_id` int DEFAULT NULL,
  `date` date DEFAULT NULL,
  `type_id` int DEFAULT NULL,
  `node_id` int DEFAULT NULL,
  `value` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

然后，我使用以下方法应用PK：

ALTER TABLE `ts_temp` 
CHANGE COLUMN `id` `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
ADD PRIMARY KEY (`id`);

我也许应该提到，这是一个timeseries表，当它是一个(默认)有符号的INT字段时，我们用完了AI数字，随后我将它转换为BIGINT无签名。

Answer 1

您是否关心是否保留旧的auto_increment值？你的过程似乎失去了它们。

在创建原始表时，是否打开了innodb_file_per_table？如果是，则有更简单的方法；如果不是，则需要转储所有表并进行重建。

一定要有innodb_file_per_table=ON

表中要保留的百分比是多少？如果这是一个很小的百分比，不要费心于CSV步骤，只需将新表重新构建为InnoDB。另外，你也不需要重建人工智能。

在进行此操作时，请考虑缩小数据类型。INT需要4个字节，BIGINT需要8个字节。查看更小的数据类型。和UNSIGNED。(例如，SMALLINT UNSIGNED占用2个字节，具有0..64K的范围。)

添加和删除辅助索引(而不是PK)在8.0中是有效的。您是否有其他索引，甚至是复合索引？它们中有一个是“独一无二的”吗？如果是这样的话，你能摆脱id并使用这个索引作为PRIMARY KEY吗？

每个列真的是NULL吗？如果必要的话，可以考虑修复它。

在这些步骤中重命名。这将尽量减少停机时间：

RENAME TABLE real TO old,
             temp TO real;
DROP TABLE old;

关于大删除和相关内容的更多提示：http://mysql.rjweb.org/doc.php/deletebig