Oracle数据库里那些索引到底是怎么帮查询提速的，讲讲原理和用法

想象一下，你有一本非常厚的电话簿，里面记录了成千上万个人的姓名、电话和地址，但所有记录都是杂乱无章地随意写上去的，让你在这本电话簿里找到“张三”的电话号码，你该怎么办？你只能从第一页开始，一页一页地翻，一行一行地找，直到找到为止，这个过程非常慢，尤其是在数据量巨大的情况下，这就是数据库没有索引时进行数据查询的处境，我们称之为“全表扫描”（Full Table Scan）。

如何加快查找速度呢？我们给这本电话簿创建一个“索引”，现实生活中，电话簿本身就是有索引的，它按照姓氏的首字母顺序排列，当你想找“张三”时，你不会从第一页开始翻，而是直接翻到“Z”开头的部分，然后在这个小得多的范围内快速找到“张”，接着在“张”姓里找到“三”，这个“按字母顺序排列”的结构,就是索引的核心思想。

在Oracle数据库中，索引的工作原理与此高度相似，它是一种独立于表数据之外的、特殊的数据库对象，它保存着表中某些列的值以及这些值所在数据行的物理地址（ROWID），你可以把索引想象成一本小册子，这本小册子只记录了关键信息（比如姓名）和对应的页码（行地址），当数据库需要根据姓名找人时，它不再去翻那本厚重的主表（电话簿），而是先查阅这本轻便的小册子（索引），快速定位到目标所在的少数几个“页码”，然后直接翻到那几页把数据拿出来，这个过程避免了扫描整张表,速度自然就大大提升了。

索引是怎么存储和工作的？

Oracle最常用的一种索引叫B树索引（B-Tree Index），你可以把它想象成一棵倒过来的、枝繁叶茂的树，这棵树是平衡的，意味着从树根到任何一个叶子节点都需要经过相同数量的步骤，这样能保证无论找哪个数据,速度都很稳定。

• 根节点和分支节点：相当于电话簿索引里的大类，A-G”、“H-N”、“O-Z”，它们不存储具体数据,只负责指引方向。
• 叶子节点：这是索引的最底层，相当于具体到每个字母下的名字列表，每个叶子节点不仅包含了你所索引的列的值（张三’），还包含了对应数据行的物理地址（ROWID）。

当执行一条查询语句，SELECT * FROM employees WHERE last_name = '张三'，并且last_name列上有索引时,Oracle会这样做：

1. 从索引的根节点开始。
2. 比较“张三”，确定它应该在哪个分支（Z”开头的分支）。
3. 沿着这个分支向下,找到对应的叶子节点。
4. 在叶子节点中快速找到“张三”这个值,并获取它的ROWID。
5. 根据ROWID,直接到表中对应的精确位置读取整行数据。

这个过程可能只需要读取3-4个数据块（索引块和表数据块），而全表扫描可能需要读取成千上万个数据块,效率差异立竿见影。

索引的常见用法和注意事项

虽然索引能极大提高查询速度，但它并非越多越好，因为索引本身也需要占用存储空间，并且在数据增、删、改时，数据库需要同时维护表和索引，这会降低数据写入的速度，这就好比你在那本电话簿里每新增一个人，不仅要加到正文里，还要在索引小册子里按顺序插入他的信息,创建索引需要权衡。

应该在哪些列上创建索引？

1. 主键列和经常作为查询条件的列（WHERE子句）：这是最经典的场景，比如经常按员工号、身份证号、订单号查询,这些列就应该是索引的首选。
2. 经常用于表连接的列（JOIN子句）：两个表通过department_id进行连接,在这个列上建立索引可以显著加快连接速度。
3. 需要保证唯一性的列：可以创建唯一索引（Unique Index）,这样数据库会自动检查数据的唯一性。

在哪些情况下索引可能无效或不适用？

1. 小表：如果表很小，只有几十上百行数据，全表扫描可能比走索引更快,因为读索引也需要开销。
2. 频繁更新的列：如果某些列经常被修改，那么维护其索引的成本会很高,可能得不偿失。
3. 查询返回了表中很大比例的数据：你的查询条件是 WHERE gender = '男'，而表中50%的人都是男性，这时，使用索引需要来回在索引块和数据块之间切换（这称为“回表”），其效率可能反而不如一次性顺序扫描整张表，Oracle的优化器很聪明,在这种情况下可能会自动放弃使用索引。
4. 对索引列使用了函数或计算：WHERE UPPER(last_name) = 'ZHANG'，因为索引是按照last_name的原始值排序的，而不是按照UPPER(last_name)排序的，所以这个查询无法有效利用索引，为了解决这个问题，可以基于函数创建“函数索引”（Function-based Index）。

总结一下

Oracle数据库中的索引就像一本书的目录，它通过创建一个排好序的、小巧的“快速指南”，帮助数据库引擎绕过繁琐的全表扫描，直接定位到所需的数据，正确使用索引是数据库查询优化的最关键手段之一，其核心原则是：为那些在查询中真正起到筛选作用的列创建索引，同时要考虑到索引带来的空间和维护成本。 在使用时，要避免那些导致索引失效的写法，让这个“加速器”真正发挥作用。

（主要原理和概念参考自Oracle官方文档中关于索引的概述以及《Oracle Database Concepts》指南中对于Schema Objects的说明，同时结合了常见的数据库性能优化实践知识。）