MySQL表空间管理与优化（8/16）

表空间管理和优化

1. innodb_file_per_table参数（此参数在分区表章节中还会出现）：

   • 这个参数决定了InnoDB表数据的存储方式。当参数设置为ON时，每个InnoDB表的数据会单独存储在一个以.ibd为后缀的文件中，这有利于管理和回收空间。从MySQL 5.6.6版本开始，默认值就是ON。
   • 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；就是information_schema。

   一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。所以参数推荐为ON。

2. 空洞的产生：

   • 当删除表中的数据时，InnoDB引擎会标记记录为删除，而不是立即释放空间。这意味着即使删除了表中一半的数据，表文件的大小可能不会变化。

   • 删除数据或插入数据（导致数据页分裂）时，可能会在数据页上产生空洞。空洞是未被使用但已被标记为可复用的空间。大量增删改操作的表可能会存在很多空洞。

   • 要收缩表空间，可以通过重建表来实现。这涉及创建一个新表，将原表中的数据按主键ID递增顺序重新插入新表，从而消除空洞。

3. 重建表的方法：

4. 使用ALTER TABLE命令：

   • 这是最常用的重建表的方法。通过指定ENGINE=InnoDB，你可以让MySQL重新创建表的物理存储。这个过程会创建一个新的临时表，将原表中的数据按主键ID递增顺序导入新表，然后删除原表并将新表重命名为原表的名字。例如：

     ALTER TABLE table_name ENGINE=InnoDB;
     

   • 在MySQL 5.6及更高版本中，这个过程通常是Online的，也就是说，在重建表的过程中，可以继续对表进行增删改操作。

5. 使用OPTIMIZE TABLE命令：

   • 这个命令是ALTER TABLE ... ENGINE=InnoDB的别名，它会尝试优化表的存储。在某些情况下，这可能意味着重建表，但行为可能因MySQL版本和表的具体情况而异。例如：

     OPTIMIZE TABLE table_name;
     

   • 这个命令在执行时会获取表级锁，可能会影响业务操作。

6. 使用ALTER TABLE ... ALGORITHM=COPY：

   • 当你需要强制执行一个非在线的表重建时，可以使用这个选项。这会导致MySQL创建一个新表，并将数据从原表复制到新表中，然后删除原表并重新命名新表。例如：

     ALTER TABLE table_name ALGORITHM=COPY;
     

   • 这种方式不是在线的，可能会对业务操作产生影响。

7. 使用gh-ost工具：

• 对于大型生产数据库，建议使用gh-ost（GitHub开源的在线DDL工具）来进行表重建。gh-ost允许你在不锁定整个表的情况下进行DDL操作，从而减少对业务的影响。

5. 使用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、调优、经验分享、数据分析等方向内容，会根据大家的学习需求更新更多方向的内容。

🔗 立即扫描下方二维码，加入知识星球，与行业精英共同成长，开启您的专属学习之旅！