在不锁定DB的情况下创建索引

I have a table with 10+ million rows. I need to create an index on a single column, however, the index takes so long to create that I get locks against the table.

我有一张超过10万行的表。我需要在单个列上创建索引,但是,索引需要很长时间才能创建,我会对表进行锁定。

It may be important to note that the index is being created as part of a 'rake db:migrate' step... I'm not adverse to creating the index manually if that will work.

值得注意的是,索引是作为“rake db:migrate”步骤的一部分创建的......如果可行的话,我不会手动创建索引。

UPDATE: I suppose I should have mentioned that this a write often table.

更新:我想我应该提到这是一个经常写表。

3 个解决方案

#1

MySQL NDBCLUSTER engine can create index online without locking the writes to the table. However, the most widely used InnoDB engine does not support this feature. Another free and open source DB Postgres supports 'create index concurrently'.

MySQL NDBCLUSTER引擎可以在线创建索引而无需锁定对表的写入。但是,使用最广泛的InnoDB引擎不支持此功能。另一个免费和开源的DB Postgres支持'并发创建索引'。

#2

you can prevent the blockage with something like this (pseudo-code):

你可以用这样的东西来防止堵塞(伪代码):

create table temp like my_table;
update logger to log in temp;
alter table my_table add index new_index;
insert into my_table select * from temp;
update logger to log in my_table;
drop table temp

Where logger would be whatever adds rows/updates to your table in regular use(ex.: php script). This will set up a temporary table to use while the other one updates.

记录器将在常规使用中添加行/更新的任何内容(例如:php脚本)。这将设置一个临时表,而另一个更新。

#3

Try to make sure that the index is created before the records are inserted. That way, the index will also be filled during the population of the table. Although that will take longer, at least it will be ready to go when the rake task is done.

尝试确保在插入记录之前创建索引。这样,索引也将在表的填充期间填充。虽然这需要更长的时间,但至少在rake任务完成时它已经准备好了。

#1

#2