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

简介: 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 技巧 ~

相关文章
|
1月前
|
SQL Oracle 关系型数据库
Oracle-index索引解读
Oracle-index索引解读
85 0
|
1月前
|
存储 Oracle 关系型数据库
Oracle 12c的多重索引:数据的“多维导航仪”
【4月更文挑战第19天】Oracle 12c的多重索引提升数据查询效率,如同多维导航仪。在同一表上创建针对不同列的多个索引,加速检索过程。虽然过多索引会增加存储和维护成本,但合理选择和使用索引策略,结合位图、函数索引等高级特性,能优化查询,应对复杂场景。数据管理员应善用这些工具,根据需求进行索引管理,支持企业数据分析。
|
7月前
|
索引
Oracle-序列、索引和同义词
Oracle-序列、索引和同义词
29 0
|
1月前
|
SQL Oracle 关系型数据库
[Oracle]索引
[Oracle]索引
74 0
[Oracle]索引
|
7月前
|
存储 SQL Oracle
Oracle优化避免索引失效
Oracle优化避免索引失效
220 0
|
8月前
|
存储 Oracle 关系型数据库
9-6 Oracle 管理索引
9-6 Oracle 管理索引
|
SQL Oracle 关系型数据库
Oracle-表分析和索引分析解读
Oracle-表分析和索引分析解读
153 0