如何选择和优化oracle索引?[闭门]

Oracle文档在索引选择方面有一套非常好的注意事项: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/designing-and-developing-for-performance.html#GUID-99A7FD1B-CEFD-4E91-9486-2CBBFC2B7A1D

引述:

• 考虑在WHERE子句中频繁使用的索引键。

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

• 选择具有高选择性的索引键。索引的选择性是表中具有相同索引键值的行的百分比。如果有几行具有相同的值,则索引的选择性是最佳的。注意:Oracle会自动对使用完整性约束定义的唯一键和主键的键和表达式创建索引或使用现有索引。 如果数据分布发生倾斜,使一个或两个值的出现频率远远低于其他值,则索引低选择性列可能会有所帮助。

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

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

• 考虑在大量并发插入、更新和删除语句访问父表和子表的情况下索引引用完整性约束的外键。这样的索引允许更新和删除父表,而无需共享锁定子表。

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

您应该始终为以下内容编制索引:

• 主键-自动为它们提供索引(除非您指定了适合Oracle使用的现有索引)
• 唯一键-自动为这些键提供索引(同上)
• 外键-这些是 自动索引,但您应该添加一个,以避免在检查约束时出现性能问题

在这之后,寻找其他列 用于过滤查询:一个典型的例子是人们的姓氏。

来自10g Oracle数据库应用程序开发人员指南-基础知识,第5章:

• 列上存在唯一的键完整性约束。

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

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

• 值在列中是唯一的,或者重复项很少。
• 值的范围很广(适用于常规索引)。

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

  比 其中COL_X不为空

具有以下特征的列不太适合编制索引:

• 列中有许多空值,您不搜索非空值。

哇,这真是个大话题,很难用这种形式回答。我极力推荐这个 book .

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

如果你真的想让你的数据库发挥最大的性能,这是一门完整的科学。

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

看看这些优化有多具体?

研究数据库规范化——您将发现许多关于应该存在哪些键、数据库应该如何关联以及索引提示的良好的行业标准规则。

-亚当

通常会将ID列放在前面,这些列通常唯一地标识行。列的组合也可以做同样的事情。以汽车为例。。。标签或车牌是唯一的,符合索引要求。它们(标记列)可以符合主键的条件。如果要搜索名称,则所有者名称可以符合索引的条件。“汽车品牌”一开始不应该得到一个指数,因为它不会有太大的变化。如果列中的数据变化不大,索引就没有帮助。

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

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

有时,全表扫描比索引更快。扫描小表比获取索引然后点击表更快。看看你的连接。