問題描述

發(fā)現(xiàn)了兩個(gè)需要新存儲(chǔ)的字段，就通過alter add添加了兩個(gè)字段，一個(gè)int, 一個(gè)float，結(jié)果發(fā)現(xiàn)變更完之后表格占用的體積反而減少了。

變更前：43個(gè)字段，14個(gè)索引字段，8141516 kb；變更后：45個(gè)字段，14個(gè)索引字段，8120649 kb；數(shù)據(jù)表大約226w條數(shù)據(jù)，myisam引擎，新添加的兩個(gè)字段里面尚未寫入數(shù)據(jù)，但減少了大約20M。

是因?yàn)樵谧鰯?shù)據(jù)表變更時(shí)同時(shí)做了優(yōu)化、壓縮之類的操作嗎？

問題解答

alter table在大部分情況下，會(huì)對(duì)原來的表生成一個(gè)臨時(shí)的副本（臨時(shí)表）。然后更新會(huì)進(jìn)行到這個(gè)臨時(shí)表里，創(chuàng)建一個(gè)新的表，刪除原來的表。所以可以通過alter table 來優(yōu)化表空間，修復(fù)操作產(chǎn)生的碎片空間。所以表空間變小了。我描述的可能不是很好，官方文檔原話是這樣子的

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 outselects (only writes), by altering a column to be the same as it’scurrent definition. This is better than using repair table whichobtains a read/write lock.

截個(gè)圖