MySQL创建索引减慢

Question

我的分析表有1.9亿行，大约150 My的英寸大小。存储引擎是MyISAM与

key-buffer-size=16G
myisam_sort_buffer_size=2G
myisam-max-sort-file-size=200G.

机器有32 has的内存。在使用BTREE创建10个简单索引(没有覆盖，没有前缀，只是列名)时，我注意到：

• 第一指数- 40分钟
• 第二指数- 50分钟
• ..。
• 第五指数-2小时
• 第11指数-3 1/2小时

每个索引将1GB到3GB添加到.myi文件中。有人经历过类似的运行时间增长吗？是否有任何方法可以获得其他索引的第一个索引创建的性能？

Answer 1

您一定是在用DDL做一些奇怪的事情，或者您有一个非常非常老的MySQL版本。

MyISAM索引的创建可能非常乏味。我差不多十年前就写过这事了。

• Oct 10, 2006：为什么mysql在大表中的索引下降非常慢？(来自MySQL一般性讨论论坛)
• May 12, 2011：MySQL仍然以这种方式处理索引吗？ (我再次提到这一点)

没有你的任何其他细节，我只能猜测。下面是：

我怀疑你一定是在做以下事情：

ALTER TABLE mytable ADD INDEX (column01);
ALTER TABLE mytable ADD INDEX (column02);
ALTER TABLE mytable ADD INDEX (column03);
...
ALTER TABLE mytable ADD INDEX (column10);
ALTER TABLE mytable ADD INDEX (column11);

我想提出以下建议

建议1:同时构建所有11个索引

应该在一个DDL语句中创建所有11个索引。

ALTER TABLE myisamtable
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;

建议2:延迟索引构建

您可以禁用非唯一索引的构建，以便进行排序和执行。

这样做可以为每个索引生成一个较小的BTREE。

ALTER TABLE mytable DISABLE KEYS;
... 
ALTER TABLE mytable ENABLE KEYS;

建议#3 :使用临时表

如果希望手动测试此选项，可以创建一个临时表，将所有索引添加到空的临时表中，禁用索引，插入数据，启用索引，并切换表名。

这是我刚才向你描述的一个例子

CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;
ALTER TABLE mytable_new DISABLE KEYS;
INSERT INTO mytable_new SELECT * FROM mytable;
ALTER TABLE mytable_new ENABLE KEYS;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

试试看！

Answer 2

罗兰多，谢谢你的解释。在这场磨难中，我跟踪了磁盘大小和文件访问(46个小时，今早完成)，是的。即使在MySql 5.6中，情况也是如此。我的发言如下：

CREATE INDEX idx1 ....;
CREATE INDEX idx2 ....;
CREATE INDEX idx3 ....;
...

我在“显示处理列表”中看到了所有数据是如何首先被复制到临时表中，然后被索引的。将使用

ALTER TABLE ADD INDEX, ADD INDEX, ADD INDEX ...

在未来。

谢谢!问题解决了..。问题回答了！