有多少个数据库索引太多？ [英] How many database indexes is too many?

问题描述

我正在开发一个拥有相当大的Oracle数据库的项目（虽然我的问题同样适用于其他数据库）。我们有一个网络界面，允许用户搜索几乎任何可能的字段组合。

为了使这些搜索快速，我们将添加索引到字段和组合的领域，我们相信用户将通常搜索。然而，由于我们不真正知道我们的客户将如何使用这个软件，很难知道要创建哪些索引。

空间不是一个问题;我们有一个4 TB的RAID驱动器，我们只使用一小部分。但是，我担心索引太多可能导致性能损失。因为这些索引需要在每次添加，删除或修改一行时更新，我想在一个表上有几十个索引是个坏主意。

那么多少个索引被认为太多？ 10？ 25？ 50？

解决方案

这取决于发生的操作

如果有很多SELECT和很少的修改，索引所有你喜欢的....这些将（可能）加快SELECT语句。 p>

如果表受到UPDATE，INSERTs + DELETEs的严重影响，那么这些表将会非常缓慢，因为它们都需要在每次执行这些操作时修改发生

说完之后，你可以清楚地添加许多无意义的索引到一个不会做任何事情的表。将B树索引添加到具有2个不同值的列中将是无意义的，因为它不会在查找数据方面添加任何内容。列中的值越唯一，就越能从索引中获益。

I'm working on a project with a rather large Oracle database (although my question applies equally well to other databases). We have a web interface which allows users to search on almost any possible combination of fields.

To make these searches go fast, we're adding indexes to the fields and combinations of fields on which we believe users will commonly search. However, since we don't really know how our customers will use this software, it's hard to tell which indexes to create.

Space isn't a concern; we have a 4 terabyte RAID drive of which we are using only a small fraction. However, I'm worried about the possible performance penalties of having too many indexes. Because those indexes need to be updated every time a row is added, deleted, or modified, I imagine it'd be a bad idea to have dozens of indexes on a single table.

So how many indexes is considered too many? 10? 25? 50? Or should I just cover the really, really common and obvious cases and ignore everything else?

解决方案

It depends on the operations that occur on the table.

If there's lots of SELECTs and very few changes, index all you like.... these will (potentially) speed the SELECT statements up.

If the table is heavily hit by UPDATEs, INSERTs + DELETEs ... these will be very slow with lots of indexes since they all need to be modified each time one of these operations takes place

Having said that, you can clearly add a lot of pointless indexes to a table that won't do anything. Adding B-Tree indexes to a column with 2 distinct values will be pointless since it doesn't add anything in terms of looking the data up. The more unique the values in a column, the more it will benefit from an index.

这篇关于有多少个数据库索引太多？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！