mysql - 向 MySQL 5.5 表添加索引时何时创建 btree？

Question

我在 MySQL 5.5 中有一个相当大的表，大约 200M 行，我想向该表中的其中一列添加索引(btree 类型)。该列是整数类型，包含广泛分布的整数。

我的问题是什么时候计算 btree？

当我执行简单的创建索引查询时:

ALTER TABLE bigtable ADD INDEX (column3);

它立即返回。 btree 的计算是否在后台进行？我无法想象 MySQL 能够如此快速地创建具有 ~200M 值且整数分布广泛的 btree。

Answer 1

简短回答:是。

长答案:看一下 MySQL Documentation对于 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.

因此，当您创建索引时，索引是在表的临时副本上创建的，然后在完成时导入它来代替现在删除的原始表。