如何优化需要花费大量时间的“优化”MYSQL查询

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (9)

我有一张桌子(innodb),每周有100万个新插页(20GB)。我只需要1周的数据,所以我在7天后删除它,所以每天我们删除大约3GB并插入3GB新。该表已与其余数据库位于不同的数据库中。

问题是磁盘空间仅在优化查询后释放,因此我们每隔几周运行一次。它工作,但它需要30分钟,并冻结整个数据库服务器的时间,而不仅仅是特定的数据库。

有没有办法更快地加入?

如果我们每次删除数据时都运行优化,那么它会比每隔几周运行一次优化更快吗?我认为当需要从磁盘中删除3GB已删除的行时运行它可能会更快,如果我们在20天之后运行它是60GB。是对的吗?还有另一种方法来优化优化吗?

提问于
用户回答回答于

OPTIMIZE TABLE让我们摆脱对它的需求,而不是担心加速。

PARTITION BY RANGE(TO_DAYS(...)) ...

然后DROP PARTITION每晚; 这比使用快得多DELETE,并且避免了使用OPTIMIZE

一定要有innodb_file_per_table=ON

同样在夜间,用于REORGANIZE PARTITIONfuture分区转换为明天的分区和新的空分区。

详情请访问:http//mysql.rjweb.org/doc.php/partitionmaint

请注意,每个PARTITION实际上都是一个单独的表,因此DROP PARTITION实际上是一个删除表。

应该有10个分区:

  • 1个启动表,以避免分区时出现故障的开销DATETIME
  • 每日7个分区
  • 额外的一天,这将是一个完整的 7天的价值。
  • 1个空future分区,以防你的夜间脚本无法运行。
用户回答回答于

由于你有一个没有的古董版本PARTITIONing,这是另一种解决方案:

  • 压缩html并存储到BLOB(而不是TEXT)。
  • 在客户端进行压缩和解压缩。
  • 这种技术可以将磁盘占用空间缩小到3:1以上。

这不会消除这个OPTIMIZE问题,但它会

  • 使用更少的磁盘空间。
  • 更快(由于挖掘的数据较少)。

但是,正如已经提到的,InnoDB在某种程度上清理了自由空间。我怀疑在Optimize之后表格没有超过2倍?通常情况下,在没有自由空间的情况下开始的BTree在大量流失后会降低到大约69%。但后来它保持这个比例。

电子邮件,HTML,文本,代码 - 所有这些都与任何体面的压缩库(zlib,PHP compress()等)缩小约3:1 。大多数图像格式和pdf都已经过压缩; 他们没有受益于第二次压缩。

扫码关注云+社区

领取腾讯云代金券