oracle oracle索引如何选择和优化？

How to choose and optimize oracle indexes?

提问by guigui42

I would like to know if there are general rules for creating an index or not. How do I choose which fields I should include in this index or when not to include them?

我想知道是否有创建索引的一般规则。我如何选择应在此索引中包含哪些字段或何时不包含它们？

I know its always depends on the environment and the amount of data, but I was wondering if we could make some globally accepted rules about making indexes in Oracle.

我知道它总是取决于环境和数据量，但我想知道我们是否可以制定一些全球公认的关于在 Oracle 中制作索引的规则。

回答by David Aldridge

The Oracle documentation has an excellent set of considerations for indexing choices: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004

Oracle 文档为索引选择提供了一组出色的注意事项：http: //download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004

Quoting:

引用：

• Consider indexing keys that are used frequently in WHERE clauses.

• Consider indexing keys that are used frequently to join tables in SQL statements. For more information on optimizing joins, see the section "Using Hash Clusters for Performance".

• Choose index keys that have high selectivity. The selectivity of an index is the percentage of rows in a table having the same value for the indexed key. An index's selectivity is optimal if few rows have the same value. Note: Oracle automatically creates indexes, or uses existing indexes, on the keys and expressions of unique and primary keys that you define with integrity constraints. Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.

• Do not use standard B-tree indexes on keys or expressions with few distinct values. Such keys or expressions usually have poor selectivity and therefore do not optimize performance unless the frequently selected key values appear less frequently than the other key values. You can use bitmap indexes effectively in such cases, unless the index is modified frequently, as in a high concurrency OLTP application.

• Do not index columns that are modified frequently. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.

• Do not index keys that appear only in WHERE clauses with functions or operators. A WHERE clause that uses a function, other than MIN or MAX, or an operator with an indexed key does not make available the access path that uses the index except with function-based indexes.

• Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table.

• When choosing to index a key, consider whether the performance gain for queries is worth the performance loss for INSERTs, UPDATEs, and DELETEs and the use of the space required to store the index. You might want to experiment by comparing the processing times of the SQL statements with and without indexes. You can measure processing time with the SQL trace facility.

• 考虑索引 WHERE 子句中经常使用的键。

• 考虑对经常用于连接 SQL 语句中的表的索引键。有关优化连接的更多信息，请参阅“使用哈希集群提高性能”一节。

• 选择具有高选择性的索引键。索引的选择性是表中具有相同索引键值的行的百分比。如果少数行具有相同的值，则索引的选择性是最佳的。注意：Oracle 会在您使用完整性约束定义的唯一键和主键的键和表达式上自动创建索引或使用现有索引。如果数据分布有偏差，因此一两个值的出现频率远低于其他值，那么索引低选择性列会很有帮助。

• 不要在具有很少不同值的键或表达式上使用标准 B 树索引。此类键或表达式通常具有较差的选择性，因此不会优化性能，除非频繁选择的键值出现的频率低于其他键值。在这种情况下，您可以有效地使用位图索引，除非经常修改索引，如在高并发 OLTP 应用程序中。

• 不要索引经常修改的列。修改索引列的 UPDATE 语句以及修改索引表的 INSERT 和 DELETE 语句比没有索引时花费的时间更长。此类 SQL 语句必须修改索引中的数据以及表中的数据。它们还会生成额外的撤消和重做。

• 不要索引仅出现在带有函数或运算符的 WHERE 子句中的键。使用除 MIN 或 MAX 以外的函数的 WHERE 子句或具有索引键的运算符不会使使用索引的访问路径可用，除非使用基于函数的索引。

• 在大量并发 INSERT、UPDATE 和 DELETE 语句访问父表和子表的情况下，考虑索引参照完整性约束的外键。这样的索引允许对父表进行 UPDATE 和 DELETE 操作，而无需共享锁定子表。

• 选择索引键时，请考虑查询的性能增益是否值得插入、更新和删除的性能损失以及存储索引所需空间的使用。您可能希望通过比较带索引和不带索引的 SQL 语句的处理时间来进行试验。您可以使用 SQL 跟踪工具测量处理时间。

回答by Tony Andrews

There are some things you should always index:

有些事情你应该总是索引：

• Primary Keys - these are given an index automatically (unless you specify a suitable existing index for Oracle to use)
• Unique Keys - these are given an index automatically (ditto)
• Foreign Keys - these are notautomatically indexed, but you should add one to avoid performance issues when the constraints are checked

• 主键 - 这些被自动赋予一个索引（除非您指定一个合适的现有索引供 Oracle 使用）
• 唯一键 - 这些被自动赋予一个索引（同上）
• 外键 - 这些不会自动索引，但您应该添加一个以避免检查约束时出现性能问题

After that, look for other columns that are frequentlyused to filter queries: a typical example is people's surnames.

之后，寻找其他经常用于过滤查询的列：一个典型的例子是人们的姓氏。

回答by DCookie

From the 10g Oracle Database Application Developers Guide - Fundamentals, Chapter 5:

来自 10g Oracle Database Application Developers Guide - Fundamentals，第 5 章：

In general, you should create an index on a column in any of the following situations:

通常，您应该在以下任何一种情况下对列创建索引：

• The column is queried frequently.
• A referential integrity constraint exists on the column.
• A UNIQUE key integrity constraint exists on the column.

• 该列经常被查询。
• 列上存在参照完整性约束。
• 列上存在唯一键完整性约束。

Use the following guidelines for determining when to create an index:

使用以下准则来确定何时创建索引：

• Create an index if you frequently want to retrieve less than about 15% of the rows in a large table. This threshold percentage varies greatly, however, according to the relative speed of a table scan and how clustered the row data is about the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.
• Index columns that are used for joins to improve join performance.
• Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key; see Chapter 6, "Maintaining Data Integrity in Application Development" for more information.
• Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large.

• 如果您经常希望检索大表中不到 15% 的行，请创建索引。但是，根据表扫描的相对速度以及行数据与索引键的聚集程度，此阈值百分比变化很大。表扫描越快，百分比越低；行数据越聚集，百分比越高。
• 用于连接以提高连接性能的索引列。
• 主键和唯一键自动具有索引，但您可能希望在外键上创建索引；有关详细信息，请参阅第 6 章“在应用程序开发中维护数据完整性”。
• 小表不需要索引；如果查询花费的时间太长，则表可能从小到大。

Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are good candidates for indexing:

一些列是索引的有力候选者。具有以下一项或多项特征的列很适合建立索引：

• Values are unique in the column, or there are few duplicates.
• There is a wide range of values (good for regular indexes).
• There is a small range of values (good for bitmap indexes).

• The column contains many nulls, but queries often select all rows having a value. In this case, a comparison that matches all the non-null values, such as:

  WHERE COL_X >= -9.99 *power(10,125) is preferable to WHERE COL_X IS NOT NULL

  This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).

• 列中的值是唯一的，或者很少有重复项。
• 值范围很广（适用于常规索引）。
• 值范围很小（适用于位图索引）。

• 该列包含许多空值，但查询通常选择所有具有值的行。在这种情况下，匹配所有非空值的比较，例如：

  WHERE COL_X >= -9.99 *power(10,125) 优于 WHERE COL_X IS NOT NULL

  这是因为第一个使用 COL_X 上的索引（假设 COL_X 是一个数字列）。

Columns with the following characteristics are less suitable for indexing:

具有以下特征的列不太适合索引：

• There are many nulls in the column and you do not search on the non-null values.

• 列中有许多空值，您无需搜索非空值。

回答by DCookie

Wow, that's just such a huge topic, it's hard to answer in this format. I srtongly recommend this book.

哇，这真是一个巨大的话题，很难以这种格式回答。我强烈推荐这本书。

Relational Database Index Design and the Optimizers by Tapio Lahdenmaki

Tapio Lahdenmaki 的关系数据库索引设计和优化器

You don't just use indexes to make table access faster, sometimes you make indexes to avoid table access altogether. Something not mentioned yet but vital.

您不仅使用索引来加快表访问速度，有时您还创建索引来完全避免表访问。一些尚未提及但至关重要的事情。

There's a whole science to this if you really want to make your database perform maximally.

如果你真的想让你的数据库发挥最大的作用，这有一个完整的科学。

Ah, one specific optimization to Oracle is building reverse key indexes. If you have a PK index of a monoatomically increasing value, like a sequence, and you have highly concurrent inserts and don't plan to range scan that column then make it a reverse key index.

啊，对 Oracle 的一项特定优化是构建反向键索引。如果您有一个单原子递增值的 PK 索引，例如一个序列，并且您有高度并发的插入并且不打算对该列进行范围扫描，则将其设为反向键索引。

See how specific these optimizations can be?

看看这些优化有多具体？

回答by Adam Davis

Look into Database Normalization - you'll find a lot of good, industry standard rules about what keys should exist, how databases should be related, and hints on indexes.

查看数据库规范化 - 您会发现很多关于应该存在哪些键、数据库应该如何关联以及索引提示的良好的行业标准规则。

-Adam

-亚当

回答by jim

Usually one puts the ID columns up front and those usually identify the rows uniquely. A combination of columns can also do the same thing. As an example using cars... tags or license plates are unique and qualify for an index. They (the tags column) can qualify for the primary key. The owners name can qualify for an index if you are going to search on name. make of car really shouldn't get an index in the beginning as it's not going to vary too much. Indexes don't help if the data in the column doesn't vary too much.

通常将 ID 列放在前面，这些列通常唯一地标识行。列的组合也可以做同样的事情。以汽车为例...标签或车牌是唯一的，有资格作为索引。它们（标签列）可以作为主键。如果您要搜索姓名，则所有者姓名可以有资格获得索引。汽车品牌一开始真的不应该得到一个指数，因为它不会变化太大。如果列中的数据变化不大，则索引无济于事。

Take a look at the SQL - what are the where clauses looking at. Those may need an index.

看一看 SQL - where 子句在看什么。那些可能需要一个索引。

Measure. What is the issue - pages/queries taking too long ? what's being used for the queries. Create an index on those columns.

措施。问题是什么 - 页面/查询花费的时间太长？用于查询的内容。在这些列上创建索引。

Caveats: indexes need time for updates and space.

注意事项：索引需要时间和空间进行更新。

and sometimes full table scans are quicker than an index. small tables can be scanned quicker than getting the index and then hitting the table. Look at your joins.

有时全表扫描比索引更快。与获取索引然后点击表相比，可以更快地扫描小表。看看你的加入。