包含 8000 万条记录的表并添加索引需要超过 18 小时(或永远)！怎么办?
简要回顾所发生的事情.我正在处理 7100 万条记录(与其他人处理的数十亿条记录相比并不多).在另一个线程上，有人建议我的集群的当前设置不适合我的需要.我的表结构是:
A short recap of what happened. I am working with 71 million records (not much compared to billions of records processed by others). On a different thread, someone suggested that the current setup of my cluster is not suitable for my need. My table structure is:
CREATE TABLE `IPAddresses` (
`id` int(11) unsigned NOT NULL auto_increment,
`ipaddress` bigint(20) unsigned default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
然后我添加了 7100 万条记录，然后做了一个:
And I added the 71 million records and then did a:
ALTER TABLE IPAddresses ADD INDEX(ipaddress);
已经 14 小时了，操作仍未完成.通过谷歌搜索，我发现有一个众所周知的方法可以解决这个问题——分区.我知道我现在需要根据 ipaddress 对我的表进行分区，但是我可以在不重新创建整个表的情况下执行此操作吗?我的意思是，通过 ALTER 语句?如果是，则有一个要求说要分区的列应该是主键.我将使用这个 ipaddress 的 id 来构建一个不同的表，所以 ipaddress 不是我的主键.在这种情况下，我该如何分区我的表?
It's been 14 hours and the operation is still not completed. Upon Googling, I found that there is a well-known approach for solving this problem - Partitioning. I understand that I need to partition my table now based on the ipaddress but can I do this without recreating the entire table? I mean, through an ALTER statement? If yes, there was one requirement saying that the column to be partitioned on should be a primary key. I will be using the id of this ipaddress in constructing a different table so ipaddress is not my primary key. How do I partition my table given this scenario?
推荐答案
好吧，事实证明这个问题不仅仅是一个简单的创建表，索引它然后忘记问题:) 这是我所做的，以防其他人遇到同样的问题(我使用了 IP 地址的示例，但它也适用于其他数据类型):
Ok turns out that this problem was more than just a simple create a table, index it and forget problem :) Here's what I did just in case someone else faces the same problem (I have used an example of IP Address but it works for other data types too):
问题:您的表有数百万个条目，您需要非常快速地添加索引
用例:考虑在查找表中存储数百万个 IP 地址.添加 IP 地址应该不是什么大问题，但为它们创建索引需要 14 多个小时.
Usecase: Consider storing millions of IP addresses in a lookup table. Adding the IP addresses should not be a big problem but creating an index on them takes more than 14 hours.
解决方案:使用 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 来做到这一点，但我还没有找到合适的解决方案.相反，有一个效率稍低的解决方案:
Case #2: When the table you want is already created. There seems to be a way to use ALTER TABLE to do this but I have not yet figured out a proper solution for this. Instead, there is a slightly inefficient solution:
CREATE TABLE IPADDRESSES_TEMP(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ipaddress BIGINT UNSIGNED,
PRIMARY KEY(id)
) ENGINE=MYISAM;
将您的 IP 地址插入此表中.然后创建带有分区的实际表:
Insert your IP addresses into this table. And then create the actual table with partitions:
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)
就这样……在一台 3.2GHz 的机器上用 1GB 的内存在新表上建立索引花了我大约 2 小时:) 希望这会有所帮助.
And there you go... indexing on the new table took me about 2 hours on a 3.2GHz machine with 1GB RAM :) Hope this helps.
相关文章