MySQL InnoDB – innodb_file_per_table缺点

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

我想知道为什么innodb_file_per_table没有默认启用。 使用它有缺点吗?

    我有这个完整的答案。

    一旦innodb_file_per_table到位,新的InnoDB表可以使用ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; 这将缩小新的.ibd文件的保证。

    如果你运行ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; 在使用innodb_file_per_table之前创build的InnoDB表上,它会将表中的数据和索引从ibdata1文件中抽出并存储在一个.ibd文件中,这将在ibdata1中留下一个永远不会被重用的永久鸽子。

    ibdata1文件通常包含四种types的信息

    • 表数据
    • 表索引
    • MVCC(Multiversioning并发控制)数据
      • 回滚段
      • 撤消空间
    • 表元数据(数据字典)
    • 双写缓冲区(防止依赖OScaching的背景写入)
    • 插入缓冲区(pipe理对非唯一二级索引的更改)
    • 参见Pictorial Representation of ibdata1Pictorial Representation of ibdata1

    这是有保证的方式来收缩ibdata1文件几乎永远…

    STEP 01)将所有数据库转储到SQL文本文件(称为SQLData.sql)

    步骤02)删除所有数据库(除了mysql,information_schema和performance_schema模式)

    STEP 03)closuresmysql

    STEP 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 

    附注:不pipe你为innodb_buffer_pool_size设置了什么,确保innodb_log_file_size是innodb_buffer_pool_size的25%。

    • STEP 05)删除ibdata1,ib_logfile0和ib_logfile1( 删除前请参阅下面的更新!

    此时,只应在/ var / lib / mysql中有mysql模式

    • 第06步)重新启动MySQL

    这将重新创build10MB的ibdata1(不要configuration选项),ib_logfile0和ib_logfile1,每个1G

    • STEP 07)将SQLData.sql重新加载到mysql中

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

    每个InnoDB表将在ibdata1外部存在

    假设你有一个名为mydb.mytable的InnoDB表。 如果你进入/var/lib/mysql/mydb ,你会看到两个表示表的文件

    • mytable.frm (存储引擎头)
    • mytable.ibdmytable.ibd的表数据和表索引的mydb.mytable

    ibdata1将永远不会包含InnoDB数据和索引。

    使用/etc/my.cnfinnodb_file_per_table选项,可以运行OPTIMIZE TABLE mydb.mytableALTER 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启动过程中被选中Crash Recovery。 0的设置是最好的。

    看错误 。

    使用它有缺点吗?

    • 更开放的文件
    • 打开/重新打开开销
    • .ibd文件不缩小(请参阅1,2 )

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

    innodb_file_per_table在MariaDB中默认启用。

    我之所以select不使用innodb_file_per_table ,是因为每个表都放在它自己的文件中,这意味着每个表都有它自己的单独开销(文件签名等),这会导致MySQL目录的总体大小要比使用共享表空间更大。 另外,当有多个小文件而不是单个大文件时,由于群集冗余而导致更多的浪费空间。

    当然,额外的开销在事物的macros伟计划中并不是一个巨大的数额,特别是如果你使用的是大型驱动器或者拥有巨大的数据库,但是对于我自己(可能还有许多“家庭用户”),对于那些有大型集群的小驱动器来说,仍然太过分了,因为我一直在维护MySQL存储。

    例如,我的数据库存储与我的WordPress数据库和一些其他的小型数据库(phpBB,开发,一些AMPtesting等),转换为每表修改它从32MB到50MB,甚至不包括ibdata1哪仍然需要 至less10MB ,总共至less 60MB。

    就像我说的,对于一些人,尤其是企业来说,这可能不是太大的问题,但是如果你只是一个只托pipe你的网站,博客等的家庭用户,那么它确实是一个因素,比如select一个主机提供程序,因为除了总的磁盘使用率以外,许多主机还会限制数据库的大小。

    用innodb_file_per_table = 1,drop table可以慢一点,看这里

    只是添加一些信息

    由于MySQL 5.6.6默认启用

    根据MariaDB文档 ,innodb_file_per_table默认是禁用的。 是的,它可以影响性能(就像在执行批量查询以在新克隆的WordPress实例中search和replace值时一样)。 但是总的来说,在单个数据库文件被破坏(或意外删除)的情况下,似乎增加了存活的可能性 – 所有新的系统pipe理员在启动时应该得到的一条指令应该是“触摸该死的ibdata文件并亲吻你的工作再见!“)。