MySQL优化表请求在写操作和复制环境中使用安全吗？

Question

我的MySQL DB有多达300个具有类似结构的InnoDB表，每个表在正常情况下大约有700 K行，大小约160米。这个表彼此独立，没有外键。

此表主要用于RW操作，表使用模式如下所示：

1. 每15分钟将数据的新部分(300-1200行)插入到每个表中。
2. 每天一次，从每个表中删除大于30天的数据。
3. 每个用户请求定期从随机表中读取数据。

几个月后，DB性能显著下降。经过调查，发现了这样的问题:表文件越来越大，每个文件占用了大约300米的磁盘空间。在运行手动表优化之后，问题得到了解决，但几周后DB性能又开始下降。

作为保持DB健康的解决方案，每次删除数据后都会添加优化表请求。

问题如下：

1. 对于MySQL性能退化问题还有其他解决方案吗？
2. 在运行优化表时，是否可能丢失插入到表中的数据(如果在优化表请求仍在进行时执行插入请求)？
3. 对于意外的MySQL服务器关闭，优化表请求是否安全(如果在插入操作完成时出现PC电源丢失，但优化表请求不安全)，是否有可能丢失已提交的数据？
4. 在简单复制的情况下使用优化表请求是否安全(在主服务器上执行优化表请求，在仅用作备份工具的从服务器上复制表请求，并且除了复制之外没有IO )？

编辑: MySQL 5.7.15，InnoDB表。并使用MySQL 8.0.4RC构建复制环境。

编辑2:表结构：

CREATE TABLE `data_2235353676` (
`id` BIGINT(20) NOT NULL,
`inst` VARCHAR(100) NULL DEFAULT NULL,
`if_i` BIGINT(20) NOT NULL,
`prt` BIGINT(20) NULL DEFAULT NULL,
`if_t` BIGINT(20) NULL DEFAULT NULL,
`path` BIGINT(20) NULL DEFAULT NULL,
`period` BIGINT(20) NOT NULL,
`type` BIGINT(20) NOT NULL,
`servicetype` INT(11) NOT NULL,
`sdv_time` BIGINT(20) NULL DEFAULT NULL,
`srv_time` BIGINT(20) NOT NULL,
`err_c` BIGINT(20) NULL DEFAULT NULL,
`err_s` BIGINT(20) NULL DEFAULT NULL,
`srv_err_s` BIGINT(20) NULL DEFAULT NULL,
`una_s` BIGINT(20) NULL DEFAULT NULL,
`valid` BIGINT(1) NULL DEFAULT NULL,
`r_err` BIGINT(20) NULL DEFAULT NULL,
`k_err` BIGINT(20) NULL DEFAULT NULL,
`l2CounterType` BIGINT(20) NOT NULL,
`l2Count` BIGINT(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`, `if_i`, `period`, `type`, `servicetype`, `srv_time`, `l2CounterType`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

Answer 1

INSERT和OPTIMIZE相互争斗；我的建议将从几个方面减少这种冲突，最重要的是摆脱OPTIMIZE。

GAfor8.0是可用的

哪个列控制“超过30天”？在一个非分区表中，看看把它放在PRIMARY KEY中是否实用。通过“聚类”将所有行插入到一起，I/O将大大减少。在分区表中(如下面所述)，尝试将该列移动到PK中的稍后位置。(分区将减少I/O，因为您将只在“最后”分区中插入。)

确保您正在使用innodb_file_per_table=ON。

不要盲目地使用8字节的BIGINT，要找到一种合适的INT，它会更小，但范围足够大。

使用PARTITION BY RANGE(TO_DAYS(...))将表分解为大约32个分区。这将使旧数据的下降非常迅速和有效，而不需要OPTIMIZE TABLE。更多讨论：http://mysql.rjweb.org/doc.php/partitionmaint

注意:分区表有一些额外的“空闲空间”，所以我的建议仍然会导致每个表大约300 my。然而，自由空间不会影响性能。如果可行的话，有更少的分区--比如说，12个3天的分区。

你做得怎么样？一个LOAD DATA？一批INSERT？(我希望你不要一次插一行。)

大多数列是真正的NULLable吗？

OPTIMIZE在所有方面都是“安全的”，因为它锁定了表，复制了所有的数据，然后将新副本重命名为旧副本。(实际上有一个很小的漏洞窗口，但8.0用它的“数据字典”覆盖了这个漏洞。)