☰

更新时间 2013-07-02 15:08 EDT

tag-icon • tag-icon mysql innodb
更新时间 2013-07-02 15:08 EDT

默认情况下，MySQL InnoDB 将所有数据库的所有表存储在一个全局文件中。您可以通过在配置中设置 innodb_file_per_table 来更改此设置，然后为每个表创建一个数据文件。

我想知道为什么innodb_file_per_table默认情况下不启用。使用它有什么缺点吗？

答案1

我对这个问题有完整的答案。

一次表 1. innodb_file_per_table已到位，并且可以使用ALTER TABLE <innodb-table-name> ENGINE=InnoDB';这将缩小新的 InnoDB 表，保证会缩小新.ibd文件。

如果您ALTER TABLE <innodb-table-name> ENGINE=InnoDB';在使用 innodb_file_per_table 之前创建的 InnoDB 表上运行，它会从 ibdata1 文件中提取该表的数据和索引并将其存储在文件中.ibd，这会在 ibdata1 中留下一个永久的、永远无法重复使用的鸽子。

该ibdata1文件通常包含四类信息

这是永久缩小 ibdata1 文件的保证方法......

步骤 01）MySQLDump 所有数据库到一个 SQL 文本文件中（称之为 SQLData.sql）

步骤 02）删除所有数据库（mysql、information_schema 和 performance_schema 模式除外）

步骤 03）关闭 mysql

步骤 04）将以下行添加到 /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend

旁注：无论您对 innodb_buffer_pool_size 设置什么，请确保 innodb_log_file_size 是 innodb_buffer_pool_size 的 25％。

  • 步骤 05) 删除 ibdata1、ib_logfile0 和 ib_logfile1（删除之前请参阅下面的更新！）

此时，/var/lib/mysql 中应该只有 mysql 模式

  • 步骤 06）重新启动 mysql

这将重新创建 10MB 的 ibdata1（不配置选项），以及 1G 的 ib_logfile0 和 ib_logfile1

  • 步骤 07）将 SQLData.sql 重新加载到 mysql

ibdata1将会增长但仅包含表元数据和间歇性 MVCC 数据。

每个 InnoDB 表将存在于ibdata1

假设您有一个名为 mydb.mytable 的 InnoDB 表。如果您进入/var/lib/mysql/mydb，您将看到两个代表该表的文件

  • mytable.frm（存储引擎头）
  • mytable.ibd（的表数据和表索引主页mydb.mytable）

ibdata1将不再包含 InnoDB 数据和索引。

随着表 1. innodb_file_per_table选项中/etc/my.cnf，您可以运行OPTIMIZE TABLE mydb.mytableOR ALTER TABLE mydb.mytable ENGINE=InnoDB;，文件/var/lib/mysql/mydb/mytable.ibd实际上会缩小。

在我担任 MySQL DBA 的职业生涯中，我曾多次这样做，从那以后从未遇到过任何问题。事实上，我第一次这样做时，将 50GB 的 ibdata1 文件压缩为 50MB。

试试吧。如果您对此有其他问题，请给我发电子邮件。相信我。这在短期和长期内都会有效。

更新时间 2013-07-02 15:08 EDT

我在这方面有一个警告，我在其他帖子中更新过，但我错过了这一点：我正在用以下方法更新我的答案innodb_fast_shutdown因为我以前会重启 mysql 并停止 mysql 来执行此操作。现在，这一步至关重要，因为每个未提交的事务可能在 InnoDB 事务日志 (查看 InnoDB 基础设施）。

请注意设置innodb_fast_shutdown设置为 2 也会清除日志，但仍然存在更多移动部件，并在 mysqld 启动期间被选为崩溃恢复。设置为 0 是最佳选择。

答案2

看漏洞。

使用它有什么缺点吗？

  • 更多打开的文件
  • 打开/重新打开开销
  • .ibd 文件不会缩小（参见1，2）

我总是在大型数据库上使用 innodb_file_per_table。

答案3

innodb_file_per_table 在 MariaDB 中默认启用。

答案4

只是想补充一点信息

从 mysql 5.6.6 开始，它默认启用

相关内容