表空间管理和优化
-
innodb_file_per_table参数(此参数在分区表章节中还会出现):
- 这个参数决定了InnoDB表数据的存储方式。当参数设置为ON时,每个InnoDB表的数据会单独存储在一个以.ibd为后缀的文件中,这有利于管理和回收空间。从MySQL 5.6.6版本开始,默认值就是ON。
- 这个参数设置为 OFF 表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起;就是information_schema。
一个表单独存储为一个文件更容易管理,而且在你不需要这个表的时候,通过 drop table 命令,系统就会直接删除这个文件。而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的。所以参数推荐为ON。
-
空洞的产生:
-
当删除表中的数据时,InnoDB引擎会标记记录为删除,而不是立即释放空间。这意味着即使删除了表中一半的数据,表文件的大小可能不会变化。
-
删除数据或插入数据(导致数据页分裂)时,可能会在数据页上产生空洞。空洞是未被使用但已被标记为可复用的空间。大量增删改操作的表可能会存在很多空洞。
-
要收缩表空间,可以通过重建表来实现。这涉及创建一个新表,将原表中的数据按主键ID递增顺序重新插入新表,从而消除空洞。
-
-
重建表的方法:
-
使用
ALTER TABLE
命令:- 这是最常用的重建表的方法。通过指定
ENGINE=InnoDB
,你可以让MySQL重新创建表的物理存储。这个过程会创建一个新的临时表,将原表中的数据按主键ID递增顺序导入新表,然后删除原表并将新表重命名为原表的名字。例如:ALTER TABLE table_name ENGINE=InnoDB;
- 在MySQL 5.6及更高版本中,这个过程通常是Online的,也就是说,在重建表的过程中,可以继续对表进行增删改操作。
- 这是最常用的重建表的方法。通过指定
-
使用
OPTIMIZE TABLE
命令:- 这个命令是
ALTER TABLE ... ENGINE=InnoDB
的别名,它会尝试优化表的存储。在某些情况下,这可能意味着重建表,但行为可能因MySQL版本和表的具体情况而异。例如:OPTIMIZE TABLE table_name;
- 这个命令在执行时会获取表级锁,可能会影响业务操作。
- 这个命令是
-
使用
ALTER TABLE ... ALGORITHM=COPY
:- 当你需要强制执行一个非在线的表重建时,可以使用这个选项。这会导致MySQL创建一个新表,并将数据从原表复制到新表中,然后删除原表并重新命名新表。例如:
ALTER TABLE table_name ALGORITHM=COPY;
- 这种方式不是在线的,可能会对业务操作产生影响。
- 当你需要强制执行一个非在线的表重建时,可以使用这个选项。这会导致MySQL创建一个新表,并将数据从原表复制到新表中,然后删除原表并重新命名新表。例如:
-
使用
gh-ost
工具:
- 对于大型生产数据库,建议使用
gh-ost
(GitHub开源的在线DDL工具)来进行表重建。gh-ost
允许你在不锁定整个表的情况下进行DDL操作,从而减少对业务的影响。
-
使用
ANALYZE TABLE
命令:- 虽然这个命令不会重建表,但它可以更新表的索引统计信息,有助于优化查询性能。例如:
ANALYZE TABLE table_name;
- 这个操作通常很快,并且对业务操作的影响很小。
- 虽然这个命令不会重建表,但它可以更新表的索引统计信息,有助于优化查询性能。例如:
在进行表重建操作时,需要考虑操作对业务的影响。Online DDL方法(如ALTER TABLE
和OPTIMIZE TABLE
)通常更适合生产环境,因为它们允许在重建过程中继续进行业务操作。对于大型表,应该在业务低峰期进行操作,并考虑使用gh-ost
等工具来最小化对业务的影响。同时,确保在执行这些操作之前备份数据,以防万一出现问题。
作者与版本更新计划
感谢您的阅读与支持!本文是《MySQL实战与优化》专栏中的一篇精选文章,该专栏共包含16篇文章,旨在为您提供实战中可直接应用的宝贵知识。
关注公众号【数舟】,获取作者最新动态,公众号后台回复【mysql2024】,即可免费领取这份包含16篇文章的完整的PDF专栏!
目前版本为v1.0,更新时间2024年4月10日。后续此文档更新与版本发布会同步到知识星球【数舟】中。
知识整理与创作不易,感谢大家理解与支持!
加入知识星球,您将获得更多独家内容、专栏更新以及与行业内专家和同行的互动交流机会。我们在知识星球等您,一起探索MySQL的深层次世界!
星球内目前包含300+精品文章,内容涵盖大数据、MySQL、运维、Python、调优、经验分享、数据分析等方向内容,会根据大家的学习需求更新更多方向的内容。
🔗 立即扫描下方二维码,加入知识星球,与行业精英共同成长,开启您的专属学习之旅!文章来源:https://www.toymoban.com/news/detail-849120.html
文章来源地址https://www.toymoban.com/news/detail-849120.html
到了这里,关于MySQL表空间管理与优化(8/16)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!