在MySQL中，使用ALTER TABLE建立索引需要多长时间？

Question

这可能有点像询问一根绳子的长度，但统计数据如下：

• Intel双核心4GB RAM
• 包含800万行、约20列的表格，大多数为自增主键的varchar类型
• 查询语句是：ALTER TABLE my_table ADD INDEX my_index (my_column);
• my_column是varchar（200）
• 存储引擎是MyISAM

数量级应该是1分钟、10分钟还是100分钟？

谢谢

编辑：好吧，它花费了2小时37分钟，而较低规格的机器只用了0小时33分钟，两个机器的设置基本相同。我不知道为什么它需要这么长时间。唯一的可能性是生产机器硬盘已经使用了85％，剩余100GB。应该足够了，但我想这取决于这些剩余空间如何分配。

- Richard H

---

在测试环境中需要多长时间？服务器负载可能是唯一无法预测的因素，但我认为不会超过一分钟。 - OMG Ponies

我现在只是在开发环境中运行。我原本预计大约需要5分钟，但30分钟过去了什么也没完成。此外，在top命令中，mysqld进程看起来非常不活跃，而在开发环境中，我看到CPU占用率超过60%。 - Richard H

检查您是否已经耗尽磁盘空间，特别是在MySQL临时目录中。 - nos

Answer 1

如果您只添加单个索引，则大约需要10分钟。但是，如果您没有该索引文件在内存中，则需要100分钟或更长时间。

您的带有800万行的200 varchar将最多占用1.6GB的空间，但是由于所有索引开销，它将占用大约2-3GB的空间。但是，如果大多数行少于200个字符，则会减少所需的空间（您可能需要执行select sum(length(my_column))来查看所需的空间量）。

您需要编辑您的/etc/mysql/my.cnf文件。尝试调整以下设置；

myisam_sort_buffer_size = 100M
sort_buffer_size = 100M

祝你好运。

- vy32

---

嗨，谢谢。我之前用了大约4百万行数据完成了这个任务，速度非常快，所以使用8百万行数据可能会把索引推到磁盘上。 - Richard H

Answer 2

在我的测试MusicBrainz数据库中，track表在25分钟内建立了一个PRIMARY KEY和三个次要索引。

CREATE TABLE `track` (
  `id` int(11) NOT NULL,
  `artist` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gid` char(36) NOT NULL,
  `length` int(11) DEFAULT '0',
  `year` int(11) DEFAULT '0',
  `modpending` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `gid` (`gid`),
  KEY `artist` (`artist`),
  KEY `name` (`name`)
) DEFAULT CHARSET=utf8

表格中有9001870条记录。

机器配置为：Intel(R) Core(TM)2 CPU 6400 @ 2.13GHz，内存为2Gb RAM，操作系统为Fedora Core 12，数据库为MySQL 5.1.42。

@@myisam_sort_buffer_size为256M。

- Quassnoi

---

哦，看起来我的 my.cnf 文件中没有指定 myisam_sort_buffer_size 或 sort_buffer_size... - Richard H

好的，所以在我的测试环境中已经完成了，该环境规格较低（3GB，稍微慢一些的CPU），用了33分钟。生产环境仍在运行中。我希望有一个状态更新或其他信息…… - Richard H

@Richard：如果没有指定，则默认值为（8M）。您可以通过发出 SELECT @@myisam_sort_buffer_size 命令来检查它。这个值太低了，你应该增加它（特别是如果你有 3 Gb 的 RAM）。这个内存只在创建或修复索引时使用（和分配），所以增加它是没问题的。@@sort_buffer_size 不影响索引创建速度，它只影响查询。 - Quassnoi

Answer 3

此外，如果您需要构建多个索引，则最好一次性创建所有索引，而不是单独创建。原因是：它基本上似乎会重新编写所有索引页面，以包含您的新索引和其他所有内容。我曾经在一个超过2GB的表上创建大约15个索引，并发现单独创建每个索引的时间逐渐增长。然后尝试一次全部创建，与单独创建3个索引相比，时间略长，因为它按记录构建并一次性写入所有内容，而不必不断重建页面。

- DRapp