具有 8000 万条记录的表并添加索引需要超过 18 小时（或永远）！现在怎么办？

Question

简短回顾一下所发生的事情。我正在处理 7100 万条记录（与其他人处理的数十亿条记录相比，这并不多）。在另一个线程上，有人建议我的集群当前的设置不适合我的需要。我的表结构是：

CREATE TABLE `IPAddresses` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `ipaddress` bigint(20) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

然后我添加了7100万条记录，然后做了一个：

ALTER TABLE IPAddresses ADD INDEX(ipaddress);

已经14个小时了，操作仍然没有完成。经过谷歌搜索，我发现有一个众所周知的方法可以解决这个问题——分区。我知道我现在需要根据 IP 地址对表进行分区，但是我可以在不重新创建整个表的情况下执行此操作吗？我的意思是，通过 ALTER 语句？如果是，则有一个要求，即要分区的列应该是主键。我将使用这个 ipaddress 的 id 来构造一个不同的表，因此 ipaddress 不是我的主键。在这种情况下，如何对表进行分区？

Answer 1

好吧，事实证明这个问题不仅仅是一个简单的创建表、索引它然后忘记问题:)这是我所做的，以防其他人面临同样的问题（我使用了 IP 地址的示例，但它适用于其他人）数据类型）：

问题：您的表有数百万个条目，您需要非常快地添加索引

用例：考虑在查找表中存储数百万个 IP 地址。添加 IP 地址应该不是一个大问题，但为其创建索引需要超过 14 小时。

解决方案：使用 MySQL 的 Partitioning策略

情况#1：当您想要的表尚未创建时

CREATE TABLE IPADDRESSES(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ipaddress BIGINT UNSIGNED,
  PRIMARY KEY(id, ipaddress)
) ENGINE=MYISAM
PARTITION BY HASH(ipaddress)
PARTITIONS 20;

情况#2：当您想要的表已经创建时
似乎有一种方法可以使用 ALTER TABLE 来执行此操作，但我尚未找到适当的解决方案。相反，有一个效率稍低的解决方案：

CREATE TABLE IPADDRESSES_TEMP(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ipaddress BIGINT UNSIGNED,
  PRIMARY KEY(id)
) ENGINE=MYISAM;

将您的 IP 地址插入到此表中。然后创建带有分区的实际表：

CREATE TABLE IPADDRESSES(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ipaddress BIGINT UNSIGNED,
  PRIMARY KEY(id, ipaddress)
) ENGINE=MYISAM
PARTITION BY HASH(ipaddress)
PARTITIONS 20;

最后就是

INSERT INTO IPADDRESSES(ipaddress) SELECT ipaddress FROM IPADDRESSES_TEMP;
DROP TABLE IPADDRESSES_TEMP;
ALTER TABLE IPADDRESSES ADD INDEX(ipaddress)

这样...在具有 1GB RAM 的 3.2GHz 机器上为新表建立索引花了我大约 2 个小时:) 希望这会有所帮助。

Answer 2

使用 MySQL 创建索引很慢，但也没有那么慢。对于 7100 万条记录，应该需要几分钟，而不是 14 小时。可能的问题是：

• 您尚未配置排序缓冲区大小和其他配置选项，

请查看此处：http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_myisam_sort_buffer_size

如果您尝试使用 8MB 排序缓冲区生成 1GB 索引，则需要花费很多通行证。但如果缓冲区大于 CPU 缓存，速度就会变慢。所以你必须测试并看看什么最有效。

• 有人在表上有锁，
• 你的 IO 系统很糟糕，
• 你的服务器正在交换
• 等，

照常检查 iostat、vmstat、日志等。在你的表上发出一个 LOCK TABLE 以检查是否有人有锁。

仅供参考，在我的 64 位桌面上创建 10M 随机 BIGINT 索引需要 17 秒...

Answer 3

我遇到了一个问题，我想通过添加索引来加快查询速度。该表只有大约 300,000 条记录，但花费的时间也太长。当我检查mysql服务器进程时，发现我试图优化的查询仍在后台运行。 4次！在我终止这些查询后，索引很快就完成了。也许同样的问题也适用于您的情况。

Answer 4

您正在使用即将被弃用的 MyISAM。另一种选择是 InnoDB。

“InnoDB 是 MySQL 的事务安全（符合 ACID）存储引擎，具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁定（无需升级到较粗粒度的锁定）和 Oracle 风格的一致非锁定读取可提高多用户并发性和性能。InnoDB 将用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O。为了保持数据完整性，InnoDB 还支持 FOREIGN KEY 引用完整性约束，您可以自由地混合 InnoDB 表。与来自其他 MySQL 存储引擎的表，即使在同一语句中也是如此。"\

http://dev.mysql.com/doc/refman/5.0/en/innodb.html

根据：

http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html

，您应该能够在不同的引擎通过使用一个简单的更改命令来为您提供一定的灵活性。它还指出数据库中的每个表都可以独立配置。

Answer 5

在你的桌子上。您已经插入了 710 亿条记录。现在，如果您想在表的主键列上创建分区，可以使用 alter table 选项。给出一个例子供大家参考。

CREATE TABLE t1 (
    id INT,
    year_col INT
);

ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 8;