MySQL - 创建索引需要多长时间？ [英] MySQL - how long to create an index?

问题描述

谁能告诉我如何在MySQL中添加密钥？我在数据库中有500,000,000行，trans，列i（INT UNSIGNED），j（INT UNSIGNED），nu（DOUBLE），A（DOUBLE）。
我尝试索引一列，例如。

Can anyone tell me how adding a key scales in MySQL? I have 500,000,000 rows in a database, trans, with columns i (INT UNSIGNED), j (INT UNSIGNED), nu (DOUBLE), A (DOUBLE). I try to index a column, e.g.

ALTER TABLE trans ADD KEY idx_A (A);

我等了。对于一张14,000,000行的表格，在我的MacBook Pro上执行大约需要2分钟，但对于整个5亿，这需要花费15个小时来计算。我做错了什么，或者我只是天真地对数据库的索引如何与行数进行扩展？

and I wait. For a table of 14,000,000 rows it took about 2 minutes to execute on my MacBook Pro, but for the whole half a billion, it's taking 15hrs and counting. Am I doing something wrong, or am I just being naive about how indexing a database scales with the number of rows?

推荐答案

那里有几个因素需要考虑：

There are a couple of factors to consider:

• 排序是N.log（N）操作。


• 14M行的排序可能很适合主内存; 500M行的排序可能没有，因此排序溢出到磁盘，这会大大减慢速度。

因为因素是大小约为30，大数据集的标称排序时间大约为50倍 - 在两小时之内。但是，每个数据值需要8个字节，另外还需要8个字节的开销（这是一个猜测 - 如果您对索引中存储的内容有更多了解，请调整到mySQL）。所以，14M×16≈220MB主内存。但500M×16≈8GB主存。除非您的计算机有足够的内存（并且MySQL已配置为使用它），否则大部分内容将溢出到磁盘，并且其余时间占很多。

Since the factor is about 30 in size, the nominal sort time for the big data set would be of the order of 50 times as long - under two hours. However, you need 8 bytes per data value and about another 8 bytes of overhead (that's a guess - tune to mySQL if you know more about what it stores in an index). So, 14M × 16 ≈ 220 MB main memory. But 500M × 16 ≈ 8 GB main memory. Unless your machine has that much memory to spare (and MySQL is configured to use it), then the big sort is spilling to disk and that accounts for a lot of the rest of the time.

这篇关于MySQL - 创建索引需要多长时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！