如何使用innodb引擎减少mysql中的表的索引大小？

Question

我在mysql中面临着一个性能问题，因为我的表上的索引太大了。索引大小已增长到6 6GB，我的实例运行在32 6GB内存上。几个小时后，该表中不需要大多数行，并且可以有选择地删除这些行。但是删除它们是一个耗时的解决方案，并且不会减小索引大小。

请建议一些解决方案来管理此索引。

Answer 1

你可以优化你的表来重建索引，如果在删除后没有得到更多的空间，你可以重新获得空间--

optimize table table_name;

但是，由于您的表很大，所以在优化表时它会锁定，而且您还面临着如何删除旧数据的问题，即使您不需要几个小时的旧数据。所以你可以按照下面的方法做-

Step1:在夜间或数据库流量较少时，首先重命名您的主表并创建一个同名的新表。现在将旧表中的几小时数据插入到新表中。

这样，您就可以删除不需要的数据，也可以优化新的表。

Step2:将来为了避免这个问题，您可以创建一个存储过程。它将每天只在夜间执行一次，并从该表中删除直到前一天(根据您的要求)的数据，或者将数据移动到任何历史表中。

Step3:因为现在您的表总是只保留一天的数据，所以您可以执行optimize table语句来轻松地重新构建和回收该表上的空间。

注意: delete语句不会重建索引，也不会释放服务器上的空间。为此，您需要优化您的表。它可以通过不同的方式，如alter语句或optimize语句等。

Answer 2

如果您可以删除所有超过X小时的行，那么PARTITIONing就是最佳选择。在小时上使用PARTITION BY RANGE，并使用DROP PARTITION删除旧小时，使用REORGANIZE PARTITION创建新小时。您应该有X+2分区。More details。

如果删除操作比较复杂，请提供更多详细信息；也许我们可以提出另一个解决方案来解决索引大小的问题。请包括SHOW CREATE TABLE。

即使您不能使用分区进行清除，将分区用于OPTIMIZE也可能很有用。不要使用OPTIMIZE PARTITION；它会优化整个表。相反，如果您发现需要缩小索引，请使用REORGANIZE PARTITION。

这张桌子有多大？

innodb_buffer_pool_size有多大？

(6 6GB的索引看起来并没有那么糟糕，特别是因为您有32 6GB的RAM。)