默认情况下,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的信息
Pictorial Representation of ibdata1
的Pictorial 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%。
此时,只应在/ var / lib / mysql中有mysql模式
这将重新创build10MB的ibdata1(不要configuration选项),ib_logfile0和ib_logfile1,每个1G
ibdata1
将会增长,但只包含表元数据和间歇性的MVCC数据。
每个InnoDB表将在ibdata1
外部存在
假设你有一个名为mydb.mytable的InnoDB表。 如果你进入/var/lib/mysql/mydb
,你会看到两个表示表的文件
mytable.frm
(存储引擎头) mytable.ibd
( mytable.ibd
的表数据和表索引的mydb.mytable
) ibdata1
将永远不会包含InnoDB数据和索引。
使用/etc/my.cnf
的innodb_file_per_table选项,可以运行OPTIMIZE TABLE mydb.mytable
或ALTER TABLE mydb.mytable ENGINE=InnoDB;
而文件/var/lib/mysql/mydb/mytable.ibd
实际上会缩小。
在我的职业生涯中,我曾经多次做过MySQL DBA,但之后没有遇到任何单一的问题。 事实上,我第一次这样做的时候,我把50GB的ibdata1文件压缩成了50MB。
试一试。 如果您还有其他问题,请给我发电子邮件。 相信我。 这将在短期内和长期的工作。
有一个告诫,我在这方面,我更新了我的其他职位,但我错过了这一点:我用innodb_fast_shutdown多一点更新我的答案,因为我用来重新启动mysql并停止mysql来做到这一点。 现在,这一步是至关重要的,因为每个未提交的事务可能在InnoDB事务日志内外都有其他移动部分( 见InnoDB基础架构 )。
请注意,将innodb_fast_shutdown设置为2会将日志清理干净,但更多移动部分仍然存在,并在mysqld启动过程中被选中Crash Recovery。 0的设置是最好的。
看错误 。
使用它有缺点吗?
我总是在大型数据库上使用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文件并亲吻你的工作再见!“)。