mysql增加了表格字段，结果表格数据体积减小了？

问题描述

问 题

发现了两个需要新存储的字段，就通过alter add添加了两个字段，一个int, 一个float，结果发现变更完之后表格占用的体积反而减少了。

变更前：43个字段，14个索引字段，8141516 kb；
变更后：45个字段，14个索引字段，8120649 kb；
数据表大约226w条数据，myisam引擎，新添加的两个字段里面尚未写入数据，但减少了大约20M。

是因为在做数据表变更时同时做了优化、压缩之类的操作吗？

解决方案

alter table在大部分情况下，会对原来的表生成一个临时的副本（临时表）。然后更新会进行到这个临时表里，创建一个新的表，删除原来的表。所以可以通过alter table 来优化表空间，修复操作产生的碎片空间。所以表空间变小了。我描述的可能不是很好，官方文档原话是这样子的

In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. The temporary copy of the original table is created in the database directory of the new table. This can differ from the database directory of the original table for ALTER TABLE operations that rename the table to a different database.

具体官方文档链接可以参考
https://dev.mysql.com/doc/ref...

PS: 可以看文档下面的comments，有人说到

You can use Alter Table to optimise a table without locking out
selects (only writes), by altering a column to be the same as it's
current definition. This is better than using repair table which
obtains a read/write lock.

截个图

这篇关于mysql增加了表格字段，结果表格数据体积减小了？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！