mysql - 如何解决 InnoDB 表的开销和有效问题？

Question

问题:

1 - 开销是什么意思？当我在 MyISAM 表上单击“优化表”按钮时，开销和有效数据都消失了。我想知道它对我的 table 有什么影响？

2 - 我真的需要关心开销和有效值吗？如何解决 InnoDB 表的 Overhead 和 Effective 问题？

Answer 1

修复 InnoDB 不像单击按钮那么简单。 MyISAM 是。

在幕后，OPTIMIZE TABLE将对名为 mytb 的 MyISAM 表执行此操作:

• 创建与 mytb 结构相同的空临时表
• 将 MyISAM 数据从 mytb 复制到临时表
• 删除表 mytb
• 将临时表重命名为 mytb
• 运行 ANALYZE TABLE针对 mytb 和存储索引统计

OPTMIZE TABLE 不适用于 InnoDB，主要有两个原因:

原因 #1:InnoDB 存储布局

默认情况下，InnoDB 有 innodb_file_per_table禁用。 InnoDB 及其祖母的所有内容都在 ibdata1 中。运行 OPTIMIZE TABLE 对名为 mytb 的 InnoDB 表执行以下操作:

• 创建与 mytb 结构相同的空 InnoDB 临时表
• 将 InnoDB 数据从 mytb 复制到临时表
• 删除表 mytb
• 将临时表重命名为 mytb
• 对 mytb 运行 ANALYZE TABLE 并存储索引统计信息

不幸的是，用于收缩 mytb 的临时表附加到 ibdata1。 ibdata1 的即时增长!!!鉴于此，ibdata1 永远不会缩小。更糟糕的是，ANALYZE TABLE 是无用的(在原因 #2 中解释)

如果您启用了 innodb_file_per_table，前四 (4) 个步骤将起作用，因为数据未存储在 ibdata1 中，而是存储在名为 mytb.ibd 的外部表空间文件中。那可以缩小。

原因 #2:总是重新计算索引统计信息

InnoDB 并不有效地存储索引统计信息。事实上，如果您在 mytb 上运行 ANALYZE TABLE，则会创建并存储统计信息。不幸的是，根据设计，InnoDB 将深入其索引的 BTREE 页面，猜测 关键基数，并使用这些数字来准备 MySQL 查询优化器。这是一个持续的过程。实际上，ANALYZE TABLE 是无用的，因为计算的索引统计信息会被针对该表执行的每个查询覆盖。 I wrote about this in the DBA StackExchange June 21, 2011 .

Percona explained this thoroughly in www.mysqlperformanceblog.com

至于 MyISAM 中的开销，这个数字是可以算出来的。

对于 MyISAM 表，开销表示内部碎片。这在经历 INSERT、UPDATE 和 DELETE 的表中很常见，尤其是当您有 BLOB 数据或 VARCHAR 列时。运行 OPTIMIZE TABLE 通过复制到临时表(自然不会复制空白空间)使此类碎片消失。

回到 InnoDB，你如何有效地消除浪费空间？您需要重新设计 ibdata1 以保存更少的信息。使用 ibdata1，您有四种类型的数据:

• 表格数据页
• 索引数据页
• 表元数据
• MVCC 交易数据

您可以永久地将表和索引从 ibdata1 中移出。已经存放在 ibdata1 中的数据和索引呢？

遵循我在 2010 年 10 月 29 日发布的 InnoDB 清理计划:Howto: Clean a mysql InnoDB storage engine?