【Oracle学习笔记】4.索引（Index）

1.基础概念理解

• 索引定义与作用
  索引就像是数据库表数据的 “目录”，它是一种数据结构，能大幅加快数据库从表中检索数据的速度。数据库表可能包含大量数据行，如果没有索引，执行查询时数据库通常需要逐行扫描整个表来查找符合条件的数据，这就像在一本没有目录的书中找特定内容，效率很低。而索引通过特定的数据结构，让数据库可以直接定位到包含所需数据的行，极大地减少了检索时间。例如，在一个记录了数百万条交易记录的 transactions 表中，若经常要查询某个特定客户 ID 的交易记录，在 customer_id 列上创建索引后，数据库就能像通过目录找书的内容一样，快速定位到相关记录，而不用逐行扫描数百万条记录。
• 与其他数据库概念的关联
  索引紧密关联着表和列。它基于表中的列来创建，是对表数据的一种辅助结构。每个索引都与特定的表相关联，并且依赖于表中数据的存储和组织方式。同时，索引和约束存在联系。例如，唯一约束通常通过唯一索引来实现，以保证列值的唯一性。但两者侧重点不同，约束主要强调数据完整性规则，而索引更关注查询性能提升。例如，在 employees 表中，employee_id 列可以同时设置为主键约束（保证唯一性和非空），并自动创建唯一索引，既确保了员工 ID 的唯一性，又能加速基于 employee_id 的查询。

2.索引类型学习

• B - 树索引
  B - 树索引是 Oracle 数据库中最常用的索引类型。它以一种平衡树结构存储数据，这种结构保证了树的高度相对稳定，从而确保查询性能的一致性。在 B - 树索引中，数据按照索引键值有序存储。对于等值查询（如 SELECT * FROM employees WHERE employee_id = 123;），数据库可以快速定位到对应的数据行，因为 B - 树结构使得查找过程类似二分查找，效率很高。对于范围查询（如 SELECT * FROM employees WHERE salary > 5000;），B - 树索引也能有效工作，它可以从根节点开始，沿着树的分支快速定位到满足条件的起始数据行，然后按顺序检索后续符合条件的行。例如，在一个存储产品信息的 products 表中，对 product_id 列创建 B - 树索引后，无论是查询单个产品（等值查询），还是查询价格在某个范围内的产品（范围查询），都能快速获取结果。
• 位图索引
  位图索引适用于低基数列，即列中不同值的数量相对较少的情况。它为列中的每个不同值创建一个位图，位图中的每一位对应表中的一行数据。如果某行数据包含该值，则对应位被设置为 1，否则为 0。当进行查询时，数据库通过对位图进行逻辑运算（如与、或运算）来快速定位符合条件的数据行。例如，在一个员工信息表中，gender 列只有 “男” 和 “女” 两个值，非常适合创建位图索引。当查询所有男性员工（SELECT * FROM employees WHERE gender = '男';）时，数据库只需对位图中对应 “男” 值的位图进行简单处理，就能快速找到所有符合条件的行。而且，在位图索引用于多条件组合查询时优势明显，比如查询 “男性且部门为销售部” 的员工，数据库可以对 “男性” 和 “销售部” 对应的位图进行 “与” 运算，迅速得出结果。
• 函数索引
  函数索引是基于函数或表达式创建的索引。当查询条件涉及对列进行函数运算时，函数索引能显著提升查询性能。例如，在 employees 表中，如果经常需要查询姓氏大写形式的员工记录（如 SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';），在 UPPER(last_name) 表达式上创建函数索引后，数据库在执行此类查询时就能直接利用索引快速定位数据，而不需要对每一行数据实时计算 UPPER(last_name)。这在需要频繁进行特定函数运算查询的场景下，大大提高了查询效率。但需要注意的是，函数索引的维护成本相对较高，因为每次表数据更新时，相关的函数索引都可能需要重新计算和更新。
• 反向键索引
  反向键索引主要用于处理单调递增的键值，如序列生成的 ID。在常规的 B - 树索引中，当键值单调递增时，新插入的数据会集中在索引树的右侧，可能导致索引块的争用问题，影响并发性能。反向键索引将索引键值的字节顺序反转存储，使得插入操作能更均匀地分布在索引结构中，减少索引块争用。例如，在一个使用序列生成订单 ID 的 orders 表中，如果使用常规 B - 树索引，随着订单不断增加，新订单 ID 对应的索引插入操作可能都集中在索引树的同一区域，造成该区域的竞争。而创建反向键索引后，插入操作会分散到索引结构的不同部分，提高并发插入的性能。不过，反向键索引不适合范围查询，因为键值的顺序被反转了，范围查询时无法像 B - 树索引那样按顺序快速定位数据。

3.创建与管理索引

• 创建索引语法
  在 Oracle 中，使用 CREATE INDEX 语句创建索引，语法如下：

-- 创建普通索引
CREATE INDEX idx_column_name ON table_name (column_name);
-- 创建唯一索引，确保列值唯一
CREATE UNIQUE INDEX idx_unique_column ON table_name (unique_column);
-- 创建基于函数的索引
CREATE INDEX idx_function ON table_name (function(column_name));

例如，要在 customers 表的 email 列创建普通索引，可以使用：

CREATE INDEX idx_customer_email ON customers (email);

若要确保 customer_id 列值唯一并创建索引：

CREATE UNIQUE INDEX idx_customer_id ON customers (customer_id);

如果经常按客户姓名大写形式查询，在 UPPER(customer_name) 上创建函数索引：

CREATE INDEX idx_customer_name_upper ON customers (UPPER(customer_name));

• 索引维护操作
  索引在使用过程中可能会出现性能下降等问题，需要进行维护。使用 ALTER INDEX 语句进行索引重建，当索引出现碎片过多、性能不佳等情况时，重建索引可以优化其结构，提升性能。例如：

ALTER INDEX idx_column_name REBUILD;

还可以使用 ALTER INDEX 进行索引重命名，如将 idx_old_name 重命名为idx_new_name：

ALTER INDEX idx_old_name RENAME TO idx_new_name;

• 删除索引
  当索引不再需要时，使用 DROP INDEX 语句删除，以释放存储资源并避免对数据库性能产生不必要的影响。例如，删除 idx_column_name 索引：

DROP INDEX idx_column_name;

4.性能影响与优化

• 索引对查询性能的影响
  索引对查询性能的提升非常显著。通过实际测试可以明显看到差异，例如在一个包含 10 万条记录的 orders 表中，假设要查询订单金额大于 1000 的订单记录。在未创建索引前，执行查询可能需要较长时间，因为数据库需要全表扫描。而在 order_amount 列创建 B - 树索引后，查询执行时间会大幅缩短。不同类型的索引对不同查询场景的提升效果不同，B - 树索引在等值查询和范围查询中都表现出色；位图索引在多条件低基数列组合查询时优势明显；函数索引则针对涉及函数运算的查询。合理选择和使用索引能极大提高数据库的查询效率。
• 索引对 DML 操作的影响
  虽然索引能加速查询，但对数据插入（INSERT）、更新（UPDATE）和删除（DELETE）操作会产生负面影响。因为每次执行 DML 操作时，数据库不仅要更新表中的数据，还要同步更新相关的索引结构。例如，在一个频繁插入新员工记录的 employees 表中，如果创建了过多不必要的索引，每次插入操作时，除了将新员工数据插入表中，还需要更新多个索引，这会显著增加插入操作的时间。同样，更新和删除操作也会因为索引的存在而变慢。因此，在设计索引时需要平衡查询和 DML 操作的需求，避免过度索引。
• 索引优化策略
  为了优化索引性能，首先要避免创建过多不必要的索引，只针对经常用于查询条件的列创建索引。例如，如果某个列很少在查询中使用，就没必要为其创建索引。对经常用于连接操作的列创建索引，可以提高表连接的效率。例如，在 orders 表和 customers 表通过 customer_id 进行连接查询时，在 orders 表和 customers 表的 customer_id 列上都创建索引能加速连接操作。定期使用数据库提供的工具（如 Oracle 的 SQL Tuning Advisor）分析索引的使用情况，对于那些很少使用的索引，可以考虑删除，以减少存储和维护成本。

5.实践与案例分析

5.1假设我们正在开发一个在线商城系统，该系统包含以下几个核心表：

● products（商品表）：存储商品的详细信息。
● customers（顾客表）：记录顾客的相关信息。
● orders（订单表）：记录订单的详细信息，包括下单顾客、购买商品等。

a.products 表索引设计

-- 创建 products 表
CREATE TABLE products (product_id NUMBER PRIMARY KEY,product_name VARCHAR2(200) NOT NULL,price NUMBER,category_id NUMBER,stock_quantity NUMBER
);-- 假设经常根据商品名称搜索商品，在 product_name 列创建 B - 树索引
CREATE INDEX idx_product_name ON products (product_name);-- 如果经常按价格范围筛选商品，在 price 列创建 B - 树索引
CREATE INDEX idx_product_price ON products (price);-- 若经常根据商品类别查询商品，在 category_id 列创建 B - 树索引
CREATE INDEX idx_product_category ON products (category_id);

b.customers 表索引设计

-- 创建 customers 表
CREATE TABLE customers (customer_id NUMBER PRIMARY KEY,customer_name VARCHAR2(100) NOT NULL,email VARCHAR2(100) UNIQUE,phone_number VARCHAR2(20)
);-- 若经常根据顾客姓名查找顾客信息，在 customer_name 列创建 B - 树索引
CREATE INDEX idx_customer_name ON customers (customer_name);-- 如果通过邮箱查找顾客很频繁，在 email 列创建唯一索引（既保证唯一性，又加速查询）
CREATE UNIQUE INDEX idx_customer_email ON customers (email);

c.orders 表索引设计

-- 创建 orders 表
CREATE TABLE orders (order_id NUMBER PRIMARY KEY,customer_id NUMBER,order_date DATE,total_amount NUMBER,order_status VARCHAR2(50),FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);-- 若经常按订单日期范围查询订单，在 order_date 列创建 B - 树索引
CREATE INDEX idx_order_date ON orders (order_date);-- 如果常根据顾客 ID 查询该顾客的所有订单，在 customer_id 列创建 B - 树索引
CREATE INDEX idx_order_customer_id ON orders (customer_id);

在这个在线商城系统中，通过以上索引设计，不同业务场景下的查询性能得到了显著提升。例如，当顾客在前端搜索商品名称时，基于 idx_product_name 索引，系统能快速定位到相关商品；在统计某段时间内的订单时，idx_order_date 索引能加速查询。

5.2以一个知名的开源论坛系统为例，该系统包含 posts（帖子表）、users（用户表）和 comments（评论表）。

a.posts 表索引设计

CREATE TABLE posts (post_id NUMBER PRIMARY KEY,user_id NUMBER,post_title VARCHAR2(200) NOT NULL,post_content CLOB,post_date DATE,category_id NUMBER,view_count NUMBER,FOREIGN KEY (user_id) REFERENCES users(user_id),FOREIGN KEY (category_id) REFERENCES categories(category_id)
);-- 为了快速根据帖子标题搜索帖子，创建 B - 树索引
CREATE INDEX idx_post_title ON posts (post_title);-- 由于经常需要查询某个用户发表的所有帖子，在 user_id 列创建 B - 树索引
CREATE INDEX idx_post_user_id ON posts (user_id);-- 考虑到按类别浏览帖子的场景，在 category_id 列创建 B - 树索引
CREATE INDEX idx_post_category_id ON posts (category_id);-- 对于热门帖子的查询（按浏览量排序），在 view_count 列创建 B - 树索引
CREATE INDEX idx_post_view_count ON posts (view_count);

b.comments 表索引设计

CREATE TABLE comments (comment_id NUMBER PRIMARY KEY,post_id NUMBER,user_id NUMBER,comment_content VARCHAR2(500),comment_date DATE,FOREIGN KEY (post_id) REFERENCES posts(post_id),FOREIGN KEY (user_id) REFERENCES users(user_id)
);-- 若经常需要查询某篇帖子的所有评论，在 post_id 列创建 B - 树索引
CREATE INDEX idx_comment_post_id ON comments (post_id);-- 若要快速定位某个用户发表的评论，在 user_id 列创建 B - 树索引
CREATE INDEX idx_comment_user_id ON comments (user_id);

在这个论坛系统中，通过这样的索引设计，能够高效地支持各种常见的查询操作。例如，用户搜索特定标题的帖子时，idx_post_title 索引发挥作用；在显示某篇帖子的所有评论时，idx_comment_post_id 索引加速了数据的检索。这种设计充分考虑了业务场景，合理选择索引类型和索引列，为系统的高性能运行提供了保障。

5.3错误处理与调试示例

假设在上述在线商城系统的开发过程中，我们遇到了一个问题：在执行某个复杂查询时，查询性能并没有因为索引而得到提升，反而比没有索引时更慢。
a.问题排查步骤
i.查看查询执行计划：
在 Oracle 中，可以使用 EXPLAIN PLAN FOR 语句查看查询执行计划，例如：

EXPLAIN PLAN FOR
SELECT *
FROM products p
JOIN orders o ON p.product_id = o.product_id
WHERE p.product_name LIKE '%keyword%' AND o.order_date BETWEEN '2023 - 01 - 01' AND '2023 - 12 - 31';

然后通过以下语句查看执行计划详情：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

通过分析执行计划，我们发现数据库并没有使用我们创建的 idx_product_name 和 idx_order_date 索引，而是进行了全表扫描。
ii.检查索引状态：

-- 检查 idx_product_name 索引状态
SELECT index_name, status
FROM user_indexes
WHERE index_name = 'IDX_PRODUCT_NAME';-- 检查 idx_order_date 索引状态
SELECT index_name, status
FROM user_indexes
WHERE index_name = 'IDX_ORDER_DATE';

确认索引状态为 VALID，说明索引本身没有损坏。

iii.分析索引适用性：
发现问题出在 LIKE '%keyword%' 这种查询方式上，因为这种模糊查询无法有效地利用 B - 树索引。
b.解决方案
i.调整查询方式：
如果业务允许，尽量避免使用 LIKE '%keyword%' 这种无法利用索引的查询方式。例如，可以改为 LIKE 'keyword%'，这样在 product_name 列上的 B - 树索引就能发挥作用。
ii.考虑其他索引类型或技术：
如果必须使用 LIKE '%keyword%'，可以考虑使用 Oracle 的全文索引（Oracle Text），它更适合这种复杂的文本搜索场景。
通过上述错误处理与调试过程，我们能够解决因索引使用不当导致的查询性能问题，进一步优化数据库的性能。