Oracle索引知识看这一篇就足够

🏆 文章目标：本篇介绍Oracle索引知识以及案例场景

🍀 Oracle索引知识看这一篇就足够

✅ 创作者：Jay…

🎉 个人主页：Jay的个人主页

🍁 展望：若本篇讲解内容帮助到您，请帮忙点个赞吧，再点点您的小手关注下，您的支持是我继续写作的最大动力，谢谢🙏 作为回馈，对我博客内容感兴趣的小伙伴可以私聊我，我们一起学习 Oracle 和 PostgreSQL的知识，大家一起共同进步。

什么是索引

Oracle 索引是数据库中用于加快数据访问速度的一种结构。当对某个字段进行查询时，如果该字段有索引，数据库就可以直接查找到数据的位置，而无需扫描整个表。这就像在书中查找某个特定的词，如果书中有索引，你就可以直接翻到该词的页面，而无需一页一页地查找。

索引的分类

以下介绍的索引适用版本： Oracle 8i, 9i, 10g, 11g, 12c, 18c和19c等。

Oracle 提供了多种类型的索引，包括：

B-Tree 索引

B-Tree（平衡多路查找树）索引是Oracle数据库中最常用的索引类型。B-Tree索引对于高选择性的查询非常有效，这意味着查询返回的结果集占总行数的一小部分。这种索引类型也支持对索引键进行排序和范围搜索。

CREATE INDEX emp_last_name_idx ON employees (last_name);

此案例中，我们创建了一个名为emp_last_name_idx的B-Tree索引，该索引基于employees表的last_name列。

Bitmap 索引

Bitmap索引使用一种称为位图的数据结构，每个位表示一个行的存在或不存在。Bitmap索引非常适合低选择性的数据，即查询返回的结果集占总行数的很大一部分。另外，Bitmap索引在数据仓库环境中很常用，因为它可以高效地处理多个Bitmap索引之间的AND和OR操作。

CREATE BITMAP INDEX emp_gender_bidx ON employees (gender);

在此案例中，我们创建了一个名为emp_gender_bidx的Bitmap索引，该索引基于employees表的gender列。

Partitioned 索引

Partitioned索引是与表分区配合使用的。表分区可以按不同的方式划分数据，同样，索引也可以按相同或不同的方式进行分区。每个分区索引对应一个表分区。分区索引可以是B-Tree索引或Bitmap索引。

CREATE INDEX sales_date_idx ON sales (sale_date)
  LOCAL (
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2022','DD-MON-YYYY')),
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2022','DD-MON-YYYY')),
    PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2022','DD-MON-YYYY')),
    PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01-JAN-2023','DD-MON-YYYY'))
  );

在此案例中，我们创建了一个名为sales_date_idx的分区索引，该索引基于sales表的sale_date列，索引按照日期范围进行了分区。

Function-Based 索引

Function-Based索引是在某个函数的结果上建立的。这个函数可以是内置函数（例如，UPPER, LOWER）或用户定义函数。Function-Based索引对于处理复杂查询和改善特定类型的查询性能非常有用。

CREATE INDEX emp_name_upper_idx ON employees (UPPER(last_name));

在此案例中，我们创建了一个名为emp_name_upper_idx的函数索引，该索引基于employees表的last_name列的大写形式。

Reverse Key 索引

Reverse Key索引和B-Tree索引类似，只不过它将索引键的字节反转。这种索引类型主要用于减少索引键插入的热点，适用于有大量插入操作的索引。

CREATE INDEX emp_id_reverse_idx ON employees (employee_id) REVERSE;

在此案例中，我们创建了一个名为emp_id_reverse_idx的反向键索引，该索引基于employees表的employee_id列。

Text 索引

Text索引用于全文搜索，可以搜索包含某个词或短语的文档。

首先，需要创建一个文本首选项并设置其属性：

BEGIN
  CTX_DDL.CREATE_PREFERENCE('my_preference', 'BASIC_WORDLIST');
  CTX_DDL.SET_ATTRIBUTE('my_preference', 'STEM_FUZZY', 'ENGLISH');
END;

然后，可以创建文本索引：

CREATE INDEX docs_text_idx ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('WORDLIST my_preference');

在此案例中，我们创建了一个名为docs_text_idx的文本索引，该索引基于docs表的text列。

Spatial 索引

Spatial索引用于空间数据，可以搜索在某个地理区域内的数据。

在创建空间索引之前，首先需要在表上添加一个空间列：

ALTER TABLE geo_data ADD (shape SDO_GEOMETRY);

然后，可以创建空间索引：

CREATE INDEX geo_shape_sidx ON geo_data(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

在此案例中，我们创建了一个名为geo_shape_sidx的空间索引，该索引基于geo_data表的shape列。

每种索引类型都有其特定的优点和使用场景，你可以根据实际需求选择合适的索引类型.

常见的索引优化案例

正面案例

假设你有一个员工表，表中包含数百万条记录。如果你需要根据员工的姓氏查询数据，没有索引的情况下，Oracle 需要对整个表进行全表扫描，这可能需要花费很长的时间。如果你在姓氏字段上创建了索引，查询操作可以直接在索引中查找相应的数据，大大减少了查询时间。

CREATE INDEX idx_lastname ON employees (lastname);

然后你可以执行以下查询，它将利用你刚刚创建的索引：

SELECT * FROM employees WHERE lastname = 'Smith';

反面案例

假设你有一个订单表，该表的每一行都有一个状态字段，该字段表示订单的状态（如“新订单”，“处理中”，“已完成”）。如果你在此状态字段上创建了索引，可能并不能获得你预期的性能提升。

CREATE INDEX idx_status ON orders (status);

如果大部分的订单都处于“已完成”的状态，那么查询所有“已完成”的订单可能会返回表中的大部分行。在这种情况下，数据库可能决定进行全表扫描，而不是使用索引，因为全表扫描可能比查找并返回索引中的大部分行更有效。

SELECT * FROM orders WHERE status = '已完成';

因此，当你考虑在某个字段上创建索引时，需要考虑该字段的数据分布。在有大量重复值的字段上创建索引可能不会带来预期的性能提升。

索引失效的案例

有时间明明已经建立了索引，缺无法生效，那时为什么呢？这时候需要考虑是否索引失效了，如下场景介绍了失效的几种案例：

1、使用了函数或表达式：在这个例子中，我们用到了UPPER函数。索引可能不会被使用：

SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

在这个情况下，你可能需要创建一个function-based index来优化这个查询。

2、使用了不等运算符：在这个例子中，我们用到了不等运算符，所以索引可能不会被使用：

SELECT * FROM employees WHERE salary <> 5000;

3、使用了OR运算符：在这个例子中，我们用到了OR运算符。尽管department_id和job_id列都有索引，但是在这种情况下，Oracle可能无法有效地利用它们：

SELECT * FROM employees WHERE department_id = 10 OR job_id = 'SA_REP';

4、NULL值：Oracle在索引中不存储NULL值。因此，在这个例子中，尽管last_name列有索引，但是如果我们搜索NULL值，索引可能不会被使用：

SELECT * FROM employees WHERE last_name IS NULL;

5、使用了不等运算符：当在查询中使用不等号运算符时，Oracle可能无法使用索引。例如：

SELECT * FROM employees WHERE salary <> 5000;

在此查询中，Oracle无法使用salary的索引（如果存在的话），因为不等运算符无法有效利用B-tree索引。

6、使用了OR运算符：在WHERE子句中使用OR运算符可能会使索引失效，尤其是当OR运算符连接的列都有各自的索引时。例如：

SELECT * FROM employees WHERE department_id = 10 OR job_id = 'SA_REP';

在此查询中，即使department_id和job_id列都有索引，Oracle也可能无法有效地利用它们。

7、数据分布不均：如果索引列的数据分布非常不均匀，那么对于某些查询，Oracle可能会选择全表扫描而不是使用索引。

8、统计信息过时或不准确：Oracle优化器依赖于准确的统计信息来制定最有效的执行计划。如果统计信息过时或不准确，Oracle可能不会选择最佳的执行计划，这可能包括不正确的索引选择。

这些都是索引可能失效的原因，如果在执行计划中发现索引未被使用，可以检查上述情况是否存在。如果存在，可以通过调整查询、改变数据分布或更新统计信息等方式来优化。

索引的最佳实践

• 创建索引前要充分理解业务需求：理解你的查询工作负载是至关重要的，因为它将决定哪些列需要被索引，以及应该使用哪种类型的索引。
• 避免过度索引：虽然索引可以加速查询，但每个额外的索引都会消耗磁盘空间，并在插入、更新和删除操作时带来额外的开销。因此，你应该避免对不需要的列创建索引。
• 定期维护和重建索引：索引会随着时间的推移而变得碎片化，可能需要定期重建。你可以使用 Oracle 的 ANALYZE INDEX 命令或 DBMS_STATS 包来收集索引的统计信息，并确定是否需要重建索引。

关注公众号：熊猫Jay字节之旅，了解更多 AI 技巧 ~