MySQL - 创建索引需要多长时间？

Question

有人能告诉我在MySQL中如何添加索引以进行扩展吗？我有500,000,000条记录在一个名为“trans”的数据库中，它有三个列：i（INT UNSIGNED）、j（INT UNSIGNED），nu（DOUBLE）和A（DOUBLE）。 我尝试对一列创建索引，例如：

ALTER TABLE trans ADD KEY idx_A (A);

我在等待。对于一张有1400万行的表，在我的MacBook Pro上执行大约需要2分钟，但是对于整个半亿行，它正在进行中并且已经超过了15小时。我是做错了什么，还是我太天真了，不知道索引数据库时随着行数增加而扩展的规模？

- xnx

---

你可能想要考虑将你的表进行分割（数据库分片）。 - Mihai

Answer 1

需要考虑以下几个因素：

• 排序是一个N.log(N)操作。
• 对于1400万行的排序可能适合主内存；但是对于5亿行的排序可能不适合，因此排序将溢出到磁盘上，这会极大地降低速度。

由于两者规模差约30倍，因此大数据集的理论排序时间将是小数据集的50倍左右——少于两个小时。然而，每个数据值需要8个字节，再加上大约8个字节的开销（这只是猜测，请根据MySQL中索引存储的了解进行调整）。所以，14M x 16 ≈ 220 MB主内存，但是500M x 16 ≈ 8 GB主内存。除非你的机器有足够多的内存可以使用（并且MySQL已配置为使用它），否则大型排序将溢出到磁盘上，这就占用了其余时间的大部分。

- Jonathan Leffler

---

非常感谢，我明白了 - 我只有4 GB。看起来按照上面建议的拆分（分区？）数据是很有道理的。 - xnx

你把表格分割了吗？需要多长时间？ - sites

Answer 2

首先，您的表定义在这里可能会有很大的差异。如果您不需要在列中使用NULL值，请定义它们为NOT NULL。这将节省索引空间，并且可以假定在创建时也会节省时间。

CREATE TABLE x ( 
  i INTEGER UNSIGNED NOT NULL, 
  j INTEGER UNSIGNED NOT NULL, 
  nu DOUBLE NOT NULL, 
  A DOUBLE NOT NULL 
);

关于创建索引所需的时间，这需要进行表扫描，并将显示为REPAIR BY SORTING。在您的情况下（即大规模数据集），创建一个具有所需索引的新表并将数据插入其中可能更快，因为这将避免REPAIR BY SORTING操作，而是按顺序在插入时构建索引。在这篇文章中解释了类似的概念。

CREATE DATABASE trans_clone;
CREATE TABLE trans_clone.trans LIKE originalDB.trans;
ALTER TABLE trans_clone.trans ADD KEY idx_A (A);

然后按照文章中的方法将插入操作分成多个块，或使用MYSQLDUMP导出数据：

mysqldump originalDB trans  --extended-insert --skip-add-drop-table --no-create-db --no-create-info > originalDB .trans.sql
mysql trans_clone < originalDB .trans.sql

这将插入数据，但不需要重建索引（索引在每行插入时构建），并且应该完成得更快。

- Andy

---

请注意，与执行 mysqldump 和还原相比，这种方法不需要在整个过程中持续锁定表来进行。您可以使用 Percona Toolkit 中的 pt-online-schema-change 工具，它会负责多次创建和复制数据，并消除连续锁定的要求。 - syneticon-dj

Answer 3

根据我的经验：如果硬件能够承受，使用MySQL为大型表建立索引通常会呈现出相对线性的扩展。到目前为止，我已经尝试过100,000,000行左右的表格，但不是在笔记本电脑上，而是在强大的服务器上进行的。

我认为这主要取决于硬件因素、您所使用的表格引擎（MyIsam、INNO或其他）以及表格是否在期间被其他程序使用。当我做这个时，通常磁盘使用量飙升，而CPU使用率则不高。不确定MacBook的硬盘速度如何，但我猜它们不是最快的。

如果您有MyISAM表，请仔细查看表目录中的索引文件，并观察它随时间的变化情况。

- Bjoern

---

感谢您的快速回复，Björn。我按照您的建议去做了。我理解这两个文件#sql-a8_6.MYD（目前为7,455,506,432字节）和 #sql-a8_6.MYI（目前为2,148,865,024字节）分别是正在构建的新版本数据库和我请求的索引？ 那么如果原始表是trans.MYD（12,645,156,375字节）我完成了大约60％的工作？ 看起来我最好将巨大的表拆分成20个左右的小表。 谢谢， 克里斯蒂安 - xnx

总的来说，就是这样了。好吧，这完全取决于您想如何处理这么多数据。500,000,000行数据实在太多了，如果您想在之后生成一些高级报告，请尝试最小化数据。可以尝试将其拆分，或者查看MySQL 5.1版本中的分区功能。 - Bjoern

Answer 4

理论上，如果排序步骤是一个N.log(N)的操作，将大表分区可以节省操作时间

对于一个包含500,000,000行数据的表进行100等份文件的分区，可以获得约30%的收益：因为 500,000,000 * log(500,000,000) = 4,349,485,002 而 100 * (500,000,000/100 * LOG(500,000,000/100)) = 3,349,485,002

- Laurent PELE

Answer 5

在我的情况下，我有一个包含4个列的4000万记录表，其中包括一个varchar列。在一个INT列上建立索引，使用8核CPU和16G内存会花费我大约4分钟的时间。

- s.abbaasi