I have a database table with over 128 million rows.
我有一个超过1.28亿行的数据库表。
The issue I have to deal with is the indexes, the database performs very bad over time, I put it down partly to the indexes getting fragmented. One of the current indexes on the large table is around 50% Total fragmentation.
我必须处理的问题是索引,数据库随着时间的推移表现非常糟糕,我把它部分地归结为索引变得支离破碎。大表上的当前索引之一是大约50%的总碎片。
Reorganizing did about 1% in 1 hour so it would take way too long.
重组在1小时内完成了约1%,因此需要花费太长时间。
On such a large table, a re-index can take as much as 5 hours if not more, and I haven't found a real way of monitoring progress. What would be the best and quickest way to re-build an index on such a large table? Should I set the database as "OFFLINE" ?
在如此庞大的表上,重新索引可能需要多达5个小时(如果不是更多),而且我还没有找到一种监控进度的真实方法。在这么大的表上重建索引的最佳和最快方法是什么?我应该将数据库设置为“OFFLINE”吗?
The database also runs a very large and busy website, so I've scheduled a maximum of 6 hour downtime to get this done but need the quickest and best way possible to do this.
该数据库还运行一个非常庞大而繁忙的网站,因此我计划最多停机6小时才能完成此任务,但需要以最快和最好的方式执行此操作。
I also need to update all other indexes on the database, but this table is the hardest one.
我还需要更新数据库上的所有其他索引,但这个表是最难的。
1 个解决方案
#1
3
If you havent measured the following 2 things on this particular index, you probably don't yet have a reason to rebuild.
如果你没有在这个特定索引上测量以下两件事,你可能还没有理由重建。
- How much (if any) your queries actually improve immediately after the rebuild.
- How long the improvement lasts (ie. How long your index takes to return to a steady state of 50% fragmentation after a rebuild.
重建后,您的查询实际上会立即改进多少(如果有)。
改进持续多长时间(即,重建后您的索引恢复到50%碎片的稳定状态需要多长时间。
B-Tree indexes are designed to have fragmentation / bloat / free-space. Indexes often quickly return to their steady state of fragmentation. They usually perform decently in this state tend to want to return to steady state.
B树索引旨在具有碎片/膨胀/*空间。索引通常会很快恢复到稳定的碎片状态。他们通常在这种状态下表现得体面,往往希望恢复稳定状态。
#1
3
If you havent measured the following 2 things on this particular index, you probably don't yet have a reason to rebuild.
如果你没有在这个特定索引上测量以下两件事,你可能还没有理由重建。
- How much (if any) your queries actually improve immediately after the rebuild.
- How long the improvement lasts (ie. How long your index takes to return to a steady state of 50% fragmentation after a rebuild.
重建后,您的查询实际上会立即改进多少(如果有)。
改进持续多长时间(即,重建后您的索引恢复到50%碎片的稳定状态需要多长时间。
B-Tree indexes are designed to have fragmentation / bloat / free-space. Indexes often quickly return to their steady state of fragmentation. They usually perform decently in this state tend to want to return to steady state.
B树索引旨在具有碎片/膨胀/*空间。索引通常会很快恢复到稳定的碎片状态。他们通常在这种状态下表现得体面,往往希望恢复稳定状态。