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

一尘不染

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

mysql

简要回顾发生的事情。我正在处理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个小时了，操作仍然没有完成。在Googling上，我发现有一种解决此问题的著名方法-
分区。我知道我现在需要基于ipaddress对表进行分区，但是我可以在不重新创建整个表的情况下执行此操作吗？我的意思是，通过ALTER语句？如果是，则有一项要求说要分区的列应该是主键。我将在构造另一个表时使用此ipaddress的ID，因此ipaddress不是我的主键。在这种情况下，如何对表进行分区？

阅读 632

2020-05-17

共1个答案

一尘不染

好吧，事实证明，这个问题不仅仅是一个简单的创建表，为索引编入索引而忘记的问题：)这是我做的，以防其他人遇到相同的问题（我使用了IP地址示例，但它可以解决其他问题）数据类型）：

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

用例： 考虑在查找表中存储数百万个IP地址。添加IP地址应该不是什么大问题，但是在IP地址上创建索引要花费14个小时以上。

解决方案
：使用MySQL的分区策略对表进行分区

情况＃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个小时：)希望这会有所帮助。

2020-05-17