带有或不带有索引的批量插入 [英] bulk insert with or without index

问题描述

在评论中，我读到

作为旁注，有时删除表的索引并在创建后重新创建它们会更快

Just as a side note, it's sometimes faster to drop the indices of your table and recreate them after the bulk insert operation.

这是真的吗？在什么情况下？

Is this true? Under which circumstances?

推荐答案

与乔尔一样，我将回覆这样的说法：可以。我发现，确定他提到的场景的关键在于数据的分布以及特定表上索引的大小。

As with Joel I will echo the statement that yes it can be true. I've found that the key to identifying the scenario that he mentioned is all in the distribution of data, and the size of the index(es) that you have on the specific table.

在我曾经支持的一个应用程序中，该应用程序进行了180万行的常规批量导入，该表具有4个索引，1个具有11列，表中共有90列。导入索引需要20多个小时才能完成。删除索引，插入和重新创建索引仅花费了1个小时25分钟。

In an application that I used to support that did a regular bulk import of 1.8 million rows, with 4 indexes on the table, 1 with 11 columns, and a total of 90 columns in the table. The import with indexes took over 20 hours to complete. Dropping the indexes, inserting, and re-creating the indexes only took 1 hour and 25 minutes.

所以这可能是一个很大的帮助，但是其中很多问题数据，索引和数据值的分布。

So it can be a big help, but a lot of it comes down to your data, the indexes, and the distribution of data values.

这篇关于带有或不带有索引的批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！