blocks|key|1739326|text|这取决于在表上发生的操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739327|如果有很多选择，只有很少的更改，那么索引所有你喜欢的……这些将(潜在地)加速SELECT语句。|1739328|如果表受到更新的严重影响，则插入%2B删除...使用大量索引时，这些操作将非常慢，因为每次执行其中一个操作时，都需要对它们进行修改|1739329|话虽如此，您可以清楚地将许多毫无意义的索引添加到不会做任何事情的表中。将B-Tree索引添加到具有两个不同值的列是没有意义的，因为它不会添加任何数据查找方面的内容。列中的值越唯一，它将从索引中获益越多。|1739330|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

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.

blocks|key|1524360|text|最终，您需要多少索引取决于运行在数据库服务器上的应用程序的行为。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1524361|通常，插入操作越多，索引就会变得越痛苦。每次执行insert操作时，必须更新包含该表的所有索引。|1524362|现在，如果您的应用程序具有相当大的读取量，或者甚至更多，如果它几乎都是读取的，那么索引是可行的，因为将以很少的成本获得主要的性能改进。|1524363|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Ultimately how many indexes you need depend on the behavior of your applications that ride on top of your database server.

In general the more inserting you do the more painful your indexes become. Each time you do an insert, all the indexes that include that table have to be updated.

Now if your application has a decent amount of reading, or even more so if it's almost all reading, then indexes are the way to go as there will be major performance improvements for very little cost.

blocks|key|1524431|text|如果您主要进行读取(很少更新)，那么没有理由不对需要索引的所有内容进行索引。如果您经常更新，那么您可能需要谨慎对待您有多少索引。没有硬数字，但当事情开始变慢时，你会注意到的。确保您的聚集索引是基于数据的最有意义的索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1524432|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you do mostly reads (and few updates) then there's really no reason not to index everything you'll need to index. If you update often, then you may need to be cautious on how many indexes you have. There's no hard number, but you'll notice when things start to slow down. Make sure your clustered index is the one that makes the most sense based on the data.

blocks|key|1739496|text|有多少列？我一直被告知要建立单列索引，而不是多列索引。所以索引的数量不能超过列数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739497|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

How many columns are there?
I have always been told to make single-column indexes, not multi-column indexes. So no more indexes than the amount of columns, IMHO.

blocks|key|1739541|text|您可以考虑的一件事是构建索引，以针对标准的搜索组合。如果通常搜索column1，并且经常将column2与其一起使用，并且column3有时与column2和column1一起使用，那么按照该顺序对column1、column2和column3进行索引可以用于这三种情况中的任何一种，尽管它只是一个必须维护的索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739542|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

One thing you may consider is building indexes to target a standard combination of searches. If column1 is commonly searched, and column2 is often used with it, and column3 is sometimes used with column2 and column1, then an index on column1, column2, and column3 in that order can be used for any of those three circumstances, though it is only one index that has to be maintained.

blocks|key|1739598|text|归根结底，不要添加索引，除非您知道(这通常意味着收集使用统计信息)，索引的使用频率将远远高于更新频率。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739599|任何不符合该标准的索引都将花费更多的重建成本，而不是在偶尔使用它的情况下没有它的性能损失。|1739600|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

What it really comes down to is, don't add an index unless you know (and this often means gathering usage statistics) that it will be used far more often than it's updated. 

Any index that doesn't meet that criteria will cost you more to rebuild than the performance penalty of not having it in the odd case it got used.

blocks|key|1524645|text|在Einstein关于简单性的解释中，可以根据需要添加任意数量的索引，但不要添加更多索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1524646|但是，认真地说，每当向表中添加数据时，您添加的每个索引都需要维护。在主要是只读的表上，大量的索引是一件好事。在高度动态的表上，越少越好。|1524647|我的建议是涵盖常见和明显的情况，然后，当您遇到需要更快地从特定表中获取数据的问题时，在这一点上评估并添加索引。|1524648|此外，每隔几个月重新评估您的索引方案也是一个好主意，看看是否有任何新的索引需要索引，或者您创建的任何索引没有任何用途，应该删除。|1524649|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikiquote.org/wiki/Albert_Einstein^0|1|8|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|C|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|K|$L|$5|M|N|O|C|$P|Q]]]]

In a paraphrase of <a href="http://en.wikiquote.org/wiki/Albert_Einstein" rel="noreferrer">Einstein</a> about simplicity, add as many indexes as you need and no more.

Seriously, however, every index you add requires maintenance whenever data is added to the table. On tables that are primarily read only, lots of indexes are a good thing. On tables that are highly dynamic, fewer is better.

My advice is to cover the common and obvious cases and then, as you encounter issues where you need more speed in getting data from specific tables, evaluate and add indices at that point.

Also, it's a good idea to re-evaluate your indexing schemes every few months, just to see if there is anything new that needs indexing or any indices that you've created that aren't being used for anything and should be gotten rid of.

blocks|key|1739771|text|在我看来，没有静态的答案，这类事情属于“性能调优”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739772|这可能是你的应用程序所做的一切都是通过主键来查找的，也可能是相反的，因为查询是在不受限制的字段组合上完成的，特别是任何一个字段都可以在任何给定的时间使用。|1739773|除了简单的索引，还有重新规划你的数据库，包括计算的搜索字段，拆分表等-这真的取决于你的负载形状和查询参数，有多少/什么数据‘真正’需要由查询返回。|1739774|如果您的整个数据库前面都有存储过程facades，那么转换就会更容易一些，因为您不必担心每个即席查询。或者，您可能对将命中数据库的查询类型有深入的了解，并可以将调优限制在这些查询上。|1739775|对于SQL+Server，我发现Database+Engine+Tuning+advisor非常有用--您可以设置“典型”工作负载，它可以提供有关添加/删除索引和统计信息的建议。我相信其他的DB也有类似的工具，无论是官方的还是第三方的。|1739776|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

There's no static answer in my opinion, this sort of thing falls under 'performance tuning'.

It could be that everything your app does is looked up by a primary key, or it could be the oposite in that queries are done over unristricted combinations of fields and any one in particular could be used at any given time.

Beyond just indexing, there's reogranizing your DB to include calculated search fields, splitting tables, etc - it's really dependant on your load shapes and query parameters, how much/what data 'really' needs to be retruend by a query.

If your entire DB is fronted by stored-procedure facades turning becomes a bit easier, as you don't have to wory about every ad-hoc query. Or you may have a deep understanding of the kind of queries that will hit your DB, and can limit the tuning to those.

For SQL Server I've found the Database Engine Tuning advisor usefull - you set up 'typical' workloads and it can make recommendations about adding/removing indexes and statistics. I'm sure other DBs have similar tools, either 'offical' or third party.

blocks|key|1739851|text|这确实是一个更多的理论问题而不是实际问题。索引对性能的影响取决于您拥有的硬件、Oracle的版本、索引类型等。昨天我听说Oracle宣布了一款由HP制造的专用存储，它的性能在使用11g数据库时应该会快10倍。对于您的情况，可以有几种解决方案:+1.拥有大量的索引(>20)，并每天(夜间)重建它们。如果表每天都有数千次更新/删除，这将特别有用。2.对表进行分区(如果这适用于您的数据模型)。3.为新的/更新的数据使用单独的表，并运行将数据组合在一起的夜间流程。这将需要更改您的应用程序逻辑。4.如果您的数据支持IOT+(索引组织表)，请切换到IOT。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739852|当然，对于这种情况，可能会有更多的解决方案。我对您的第一个建议是将DB克隆到开发环境中，并对其运行一些压力测试。|1739853|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

This really is a more theoretical questions than practical. Indexes impact on your performance depends on the hardware you have, the version of Oracle, index types, etc. Yesterday I heard Oracle announced a dedicated storage, made by HP, which is supposed to perform 10 times faster with 11g database.
As for your case, there can be several solutions:
1. Have a large amount of indexes (>20) and rebuild them daily (nightly). This would be especially useful if the table gets thousands of updates/deletes daily.
2. Partition your table (if that applies your data model).
3. Use a separate table for new/updated data, and run a nightly process which combines the data together. This would require a change in your application logic.
4. Switch to IOT (index organized table), if your data support this.

Of course there might be many more solutions for such case. My first suggestion to you, would be to clone the DB to a development environment, and run some stress testing against it.

blocks|key|1524855|text|在更新基础表时，索引会产生一定的开销。当索引用于加速查询时，它提供了一个好处。对于每个索引，您需要权衡成本和收益。在没有索引的情况下，查询的运行速度有多慢？运行速度更快有多大好处？当索引丢失时，您或您的用户能容忍缓慢的速度吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1524856|您能容忍完成更新所需的额外时间吗？|1524857|您需要比较成本和收益。这对于你的情况来说是特别的。没有神奇的索引数量可以超过“太多”的阈值。|1524858|还有存储索引所需的空间成本，但您已经说过，在您的情况下，这不是问题。考虑到磁盘空间已经变得多么便宜，在大多数情况下也是如此。|1524859|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

An index imposes a cost when the underlying table is updated. An index provides a benefit when it is used to spped up a query. For each index, you need to balance the cost against the benefit. How much slower does the query run without the index? How much of a benefit is running faster? Can you or your users tolerate the slow speed when the index is missing?

Can you tolerate the additional time it takes to complete an update? 

You need to compare costs and benefits. That's particular to your situation. There's no magic number of indexes that passes the threshold of "too many".

There's also the cost of the space needed to store the index, but you've said that in your situation that's not an issue. The same is true in most situations, given how cheap disk space has become.

blocks|key|1524927|text|在数据仓库中，拥有大量索引是很常见的。我处理过有200列的事实数据表，其中190列已建立索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1524928|尽管这会带来一些开销，但必须在上下文中理解的是，在数据仓库中，我们通常只插入一行一次，我们永远不会更新它，但是它可以参与数千个SELECT查询，这些查询可能受益于对任何列的索引。|1524929|为了获得最大的灵活性，数据仓库通常使用单列位图索引，但在高基数列上除外，其中可以使用(压缩的)+btree索引。|1524930|索引维护的开销主要与写入大量块和块拆分的开销有关，因为新行被添加到该列的现有值范围的“中间”。这可以通过分区并使新数据负载与分区方案对齐，以及通过使用直接路径插入来缓解。|1524931|为了更直接地解决您的问题，我认为一开始对显而易见的内容进行索引可能是很好的，但如果对表的查询将受益，请不要害怕添加更多的索引。|1524932|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

In data warehousing it is very common to have a high number of indexes. I have worked with fact tables having two hundred columns and 190 of them indexed.

Although there is an overhead to this it must be understood in the context that in a data warehouse we generally only insert a row once, we never update it, but it can then participate in thousands of SELECT queries which might benefit from indexing on any of the columns.

For maximum flexibility a data warehouse generally uses single column bitmap indexes except on high cardinality columns, where (compressed) btree indexes can be used.

The overhead on index maintenance is mostly associated with the expense of writing to a great many blocks and the block splits as new rows are added with values that are "in the middle" of existing value ranges for that column. This can be mitigated by partitioning and having the new data loads aligned with the partitioning scheme, and by using direct path inserts.

To address your question more directly, I think it is probably fine to index the obvious at first, but do not be afraid of adding more indexes on if the queries against the table would benefit.

blocks|key|1524981|text|其他人都给了你很好的建议。随着你的进步，我有一个额外的建议给你。在某些时候，你必须决定你最好的索引策略。最后，即使是最好的计划索引策略，也可能最终创建不会被使用的索引。让您查找未使用的索引的一种策略是监视索引使用情况。您可以执行以下操作：-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1524982|alter+index+my_index_name+monitoring+usage;|code-block|syntax|javascript|1524983|然后，您可以通过查询v$object_usage来监视该索引是否被使用。有关这方面的信息可以在Oracle®+Database+Administrator's+Guide中找到。|offset|length|1524984|请记住，如果您有一个仓库策略，即在更新表之前删除索引，然后重新创建它们，那么您将不得不再次设置索引以进行监视，并且您将丢失该索引的所有监视历史记录。|1524985|entityMap|0|LINK|mutability|MUTABLE|url|http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#sthref2563^0|0|0|1B|12|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@$I|X|J|Y|1|Z]]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

Everyone else has been giving you great advice. I have an added suggestion for you as you move forward. At some point you have to make a decision as to your best indexing strategy. In the end though, the best PLANNED indexing strategy can still end up creating indexes that don't end up getting used. One strategy that lets you find indexes that aren't used is to monitor index usage. You do this as follows:-

<pre><code>alter index my_index_name monitoring usage;
</code></pre>

You can then monitor whether the index is used or not from that point forward by querying v$object_usage. Information on this can be found in the <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#sthref2563" rel="noreferrer">Oracle® Database Administrator's Guide</a>.

Just remember that if you have a warehousing strategy of dropping indexes before updating a table, then recreating them, you will have to set the index up for monitoring again, and you'll lose any monitoring history for that index.

blocks|key|1740108|text|除了每个人都提出的要点之外，如果有更多索引，则基于成本的优化器在为SQL语句创建计划时还会产生成本，因为它需要考虑更多的组合。您可以通过正确使用绑定变量来减少这种情况，这样SQL语句就可以留在SQL缓存中。然后，Oracle可以执行软解析并重用上次找到的计划。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1740109|和往常一样，没有什么是简单的。如果有倾斜的列和直方图，那么这可能不是一个好主意。|1740110|在我们的web应用程序中，我们倾向于限制我们允许的搜索组合。否则，你将不得不测试每个组合的性能，以确保你不会有潜伏的问题，总有一天会有人发现的。我们还实现了资源限制，以防止出现问题时在应用程序的其他地方引发问题。|1740111|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

In addition to the points everyone else has raised, the Cost Based Optimizer incurs a cost when creating a plan for an SQL statement if there are more indexes because there are more combinations for it to consider. You can reduce this by correctly using bind variables so that SQL statements stay in the SQL cache. Oracle can then do a soft parse and re-use the plan it found last time.

As always, nothing is simple. If there are skewed columns and histograms involved then this can be a bad idea.

In our web applications we tend to limit the combinations of searches that we allow. Otherwise you would have to test literally every combination for performance to ensure you did not have a lurking problem that someone will find one day. We have also implemented resource limits to stop this causing issues elsewhere in the application should something go wrong.

blocks|key|1740162|text|Sql+server提供了一些很好的工具，可以让您查看哪些索引正在实际使用。本文http://www.mssqltips.com/tip.asp?tip=1239提供了一些查询，使您能够更好地了解索引的使用量，而不是更新量。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1740163|entityMap|0|LINK|mutability|MUTABLE|url|http://www.mssqltips.com/tip.asp?tip=1239^0|14|15|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Sql server gives you some good tools that let you see which indexes are actually being used.
This article, <a href="http://www.mssqltips.com/tip.asp?tip=1239" rel="nofollow noreferrer">http://www.mssqltips.com/tip.asp?tip=1239</a>, gives you some queries that let you get a better insight into how much an index is used, as opposed to how much it is updated.

blocks|key|1525191|text|它完全基于Where子句中使用的列。根据规则，我们必须在外键列上建立索引，以避免死锁。AWR报告应定期分析，以了解索引的需求。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1525192|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

It is totally based on the columns which are being used in Where Clause. 
And as the Thumb of Rule, we must have indexes on Foreign Key Columns to avoid DEADLOCKS.
AWR report should analyze periodically to understand the need of indexes.

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?

How many database indexes is too many?

Oracle数据库

数据仓库

压力测试

SQL Server

数据库

服务器

我正在处理一个具有相当大的Oracle数据库的项目(尽管我的问题同样适用于其他数据库)。我们有一个web界面，允许用户搜索几乎任何可能的字段组合。为了使这些搜索速度更快，我们为我们认为用户通常会搜索的字段和字段组合添加索引。然而，由于我们不知道我们的客户将如何使用这个软件，所以很难判断要创建哪些索引。空间不是问题；我们...

问有多少数据库索引是太多了？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有多少数据库索引是太多了？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有多少数据库索引是太多了？
EN