多少数据库索引太多了?
我正在处理一个具有相当大 Oracle 数据库的项目(尽管我的问题同样适用于其他数据库).我们有一个网络界面，允许用户搜索几乎所有可能的字段组合.
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.
空间不是问题；我们有一个 4 TB 的 RAID 驱动器，我们只使用了其中的一小部分.但是，我担心索引过多可能会导致性能下降.因为每次添加、删除或修改行时都需要更新这些索引，所以我想在一个表上有几十个索引是个坏主意.
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.
那么多少索引才算太多?10?25?50?或者我应该只涵盖真正、真正常见和明显的情况，而忽略其他一切?
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.
如果有很多 SELECT 和很少的变化，索引所有你喜欢的......这些将(可能)加速 SELECT 语句.
If there's lots of SELECTs and very few changes, index all you like.... these will (potentially) speed the SELECT statements up.
如果表受到 UPDATE、INSERTs + DELETEs 的严重影响......这些将非常慢，因为有很多索引，因为每次发生这些操作之一时都需要修改它们
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
话虽如此，您显然可以将许多毫无意义的索引添加到一个不会做任何事情的表中.将 B 树索引添加到具有 2 个不同值的列将毫无意义，因为它在查找数据方面没有添加任何内容.列中的值越独特，它就越能从索引中受益.
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.
相关文章