一、索引基础：为什么说它是数据库的 “搜索引擎”？

**

（一）索引核心价值：5 分钟看懂底层逻辑

在数据库的庞大世界里，索引就如同一个智能的搜索引擎，默默发挥着至关重大的作用 。从本质上讲，索引是一种精心设计的数据结构，它巧妙地建立起列值与数据行之间的紧密映射关系 。这就好比在一座巨大的图书馆中，索引是那本详细的图书目录，通过它，我们可以迅速找到所需书籍，而无需逐本翻阅整个图书馆。

当我们在数据库中进行查询操作时，如果没有索引，数据库往往需要进行全表扫描，就像在茫茫书海中盲目寻找一本书，效率极其低下。但有了索引，情况就大不一样了。以一个拥有百万级数据的订单表为例，假设我们要查询某个特定订单号的订单信息，如果没有索引，数据库可能需要扫描 20 万行数据，这一过程可能需要耗费 200ms 的时间。但如果为订单号字段建立了索引，数据库便能直接定位到目标数据，查询时间可以大幅缩短至 5ms，性能提升高达 40 倍 ！

索引之所以能如此高效，关键在于它能够让数据库跳过全表扫描这一耗时的过程，直接快速定位到我们需要的数据。具体来说，它在加速 WHERE 查询方面表现卓越。当我们使用 WHERE 子句来筛选特定条件的数据时，索引可以迅速缩小搜索范围，快速找到符合条件的数据行。在优化 JOIN 连接时，索引也发挥着重大作用，它能够加快表与表之间的连接速度，提高查询效率。对于排序和分组操作，索引同样能够提升其效率，让数据的处理更加快速和精准。

不过，就像任何事物都有两面性一样，索引虽然强劲，但也并非越多越好。过度使用索引会带来一些负面影响，其中最明显的就是会导致 DML（数据操作语言，如 INSERT、UPDATE、DELETE）操作变慢。这是由于在进行 DML 操作时，数据库不仅要更新数据本身，还需要同时更新相关的索引，以确保索引与数据的一致性。索引越多，更新索引的开销就越大，从而导致 DML 操作的性能下降。所以，在使用索引时，我们需要在查询性能和数据更新性能之间找到一个平衡点，根据实际的业务需求和数据特点来合理创建和使用索引。

（二）必知的 3 大索引类型及适用场景

1. B 树索引（默认首选）：B 树索引是 Oracle 数据库中默认的首选索引类型，它就像一棵精心构建的平衡树，每个节点都存储着关键的索引信息。这种索引适用于高基数列，也就是那些列值具有较高唯一性的列，列如主键和唯一键。在实际应用中，它支持多种查询操作，包括等值查询（使用 “=” 操作符）、范围查询（如 “>”“<”“BETWEEN” 等操作符）以及排序操作。

例如，在一个用户表中，如果我们常常需要根据手机号来查询用户信息，那么为手机号字段建立 B 树索引就是一个超级明智的选择。这样，当我们执行查询语句 “SELECT * FROM users WHERE phone_number = '13800138000'” 时，数据库可以利用 B 树索引迅速定位到对应的用户记录，大大提高查询效率。又如在订单表中，若我们需要按照创建时间的范围来筛选订单，如 “SELECT * FROM orders WHERE create_time BETWEEN '2023 – 01 – 01' AND '2023 – 12 – 31'”，B 树索引同样能发挥作用，快速筛选出符合条件的订单数据。

2. 位图索引（低基数神器）：位图索引是处理低基数列的神器，所谓低基数列，就是那些列值重复度较高的列，像性别、状态码等字段就属于这一类。位图索引的工作原理独特而高效，它通过二进制位映射来快速过滤数据。

举个例子，假设有一个日志表，其中有一个日志级别字段，取值只有 “ERROR”“INFO”“DEBUG” 这几种。如果我们要统计不同日志级别的日志数量，如 “SELECT log_level, COUNT (*) FROM logs GROUP BY log_level”，为日志级别字段建立位图索引后，查询效率会得到显著提升，相比没有索引的情况，查询时间可能会缩短 30%。这是由于位图索引将每个唯一值映射为一个位向量，通过简单的位运算就能快速定位符合条件的行。

但需要注意的是，位图索引在高并发更新场景下要慎用。由于位图索引在更新时需要锁定整个位图，这可能会导致严重的锁竞争问题，从而影响系统的并发性能。列如在一个高并发的电商订单系统中，如果对订单状态字段（低基数列）使用位图索引，当大量订单状态同时更新时，就可能出现锁冲突，导致系统性能大幅下降。

3. 函数索引（突破常规查询）：函数索引为我们解决了一种特殊的查询难题，即当索引列被函数包裹时，常规索引无法发挥作用的问题。它的原理是对列值进行函数处理后再建立索引，从而实现对经过函数处理的数据的快速查询。

例如，在一个员工表中，如果我们常常需要进行不区分大小写的模糊查询员工姓名，如 “SELECT * FROM employees WHERE UPPER (name) LIKE '% JOHN%'”，直接对 name 字段建立常规索引是无法满足需求的。但如果我们为 UPPER (name) 建立函数索引，数据库就能利用这个索引快速找到符合条件的员工记录。再列如，在处理涉及日期的查询时，如果我们需要对不同时区的订单时间进行统一查询，如 “SELECT * FROM orders WHERE TO_DATE (order_time, 'YYYY – MM – DD HH24:MI:SS', 'TZ') BETWEEN TO_DATE ('2023 – 01 – 01 00:00:00', 'YYYY – MM – DD HH24:MI:SS', 'TZ') AND TO_DATE ('2023 – 12 – 31 23:59:59', 'YYYY – MM – DD HH24:MI:SS', 'TZ')”，为 TO_DATE (order_time) 建立函数索引可以大大优化查询性能，让跨时区查询变得更加高效。

二、索引创建实战：4 大核心原则 + 避坑指南

（一）建索引前必问的 3 个灵魂问题

在创建索引之前，有三个至关重大的问题需要我们深入思考，它们就像三把精准的尺子，协助我们衡量是否真的需要为某列创建索引，避免盲目操作带来的性能和资源浪费。

1. 该列是否高频出目前查询条件中？

索引的主要作用是加速查询，所以仅在那些频繁出目前查询条件中的列上创建索引才是明智之举。具体来说，就是在 WHERE、JOIN、ORDER BY 等子句中常常被使用的列。例如，在一个电商订单系统中，订单表常常需要根据用户 ID 查询订单信息，那么为用户 ID 字段创建索引就可以大大提高查询效率。相反，如果某个字段极少在查询中出现，列如一些用于记录历史备注的字段，为其创建索引只会白白浪费宝贵的存储空间和系统资源。

2. 数据更新频率如何？

数据更新频率是一个不容忽视的关键因素。对于那些高频更新的表，列如交易流水表，每秒钟可能会有大量的新记录插入和旧记录更新，在这样的表中添加组合索引时必须格外谨慎。由于组合索引包含多个列，每新增一条数据，索引就需要同步更新多个键值，这一过程会带来额外的开销。据实际测试和经验总结，在高频更新的表中添加组合索引，可能会导致写入性能下降 20%-30% ，严重影响系统的实时数据处理能力。

3. 选择性是否达标？

选择性是衡量一个列是否适合创建索引的重大指标，它反映了列中不同值的分布情况。我们可以通过执行 “SELECT COUNT (DISTINCT 列名)/COUNT (*) FROM 表名；” 这条 SQL 语句来计算选择性。一般来说，选择性的值越接近 1，说明该列的不同值越多，也就越适合创建索引。例如，在一个员工表中，员工 ID 字段的选择性几乎为 1，由于每个员工都有唯一的 ID，为这样的字段创建索引能够极大地提高查询效率。一般，当选择性大于 0.1 时，创建索引是比较合适的。但如果选择性低于 0.05，列如性别列，只有 “男” 和 “女” 两个值，在这样的列上创建索引可能并不划算，由于全表扫描可能反而更快，由于索引的维护开销可能会超过其带来的查询加速收益。

（二）创建索引的最佳实践

1. 单列索引：精准定位单个条件

单列索引是最基础、最常见的索引类型，它针对单个列创建，就像一把精准的 “狙击枪”，专门用于快速定位基于单个条件的查询。当我们的查询条件主要依赖于某一个列时，单列索引能够发挥出强劲的威力。列如在一个学生表中，如果我们常常需要根据学生的学号来查询学生的详细信息，如 “SELECT * FROM students WHERE student_id = '2023001'”，为 student_id 字段创建单列索引后，数据库可以直接利用索引快速定位到对应的学生记录，大大提高查询效率。单列索引的创建语法超级简单，使用 “CREATE INDEX index_name ON table_name (column_name);” 语句即可完成创建，其中 index_name 是我们为索引取的名字，table_name 是要创建索引的表名，column_name 就是我们要创建索引的列名。

2. 组合索引：覆盖多条件查询的 “黄金搭档”

组合索引，也称为复合索引，是针对多个列创建的索引，它就像一把 “万能钥匙”，能够同时优化多个条件的查询，是解决复杂查询场景的 “黄金搭档”。在使用组合索引时，遵循 “最左匹配原则” 至关重大。这意味着在查询条件中，必须从组合索引的最左边的列开始，按照索引定义的顺序依次使用列，才能充分利用索引的优势。例如，在一个订单表中，我们常常需要根据用户 ID、订单状态和创建时间进行查询，那么可以创建一个按 (用户 ID, 订单状态，创建时间) 顺序的组合索引。这样，当我们执行查询语句 “SELECT * FROM orders WHERE user_id = 123 AND order_status = ' 已支付 ' AND create_time BETWEEN '2023 – 01 – 01' AND '2023 – 12 – 31'” 时，数据库可以利用组合索引快速定位到符合条件的订单记录。同时，这个组合索引还可以优化仅使用用户 ID 和订单状态的查询，如 “SELECT * FROM orders WHERE user_id = 123 AND order_status = ' 已支付 '”，以及按照创建时间排序的查询，如 “SELECT * FROM orders ORDER BY create_time”。创建组合索引的语法为 “CREATE INDEX index_name ON table_name (column1, column2, …);”，其中 column1, column2, … 就是我们要创建索引的多个列。

3. 分区索引：大表优化的关键武器

对于拥有千万级以上数据的大表来说，分区索引是提升性能的关键武器。分区索引能够将索引数据分散到对应分区，大大提升查询的并行度。其中，本地分区索引（LOCAL）是一种超级有效的方式，它将索引与表的分区紧密关联，每个分区都有自己独立的索引。例如，有一个按月份分区的销售表，每个月的数据存储在一个独立的分区中。如果我们为这个销售表创建本地分区索引，当查询特定月份的数据时，如 “SELECT * FROM sales WHERE sale_date BETWEEN '2023 – 05 – 01' AND '2023 – 05 – 31'”，数据库仅需扫描 5 月份对应的分区索引，而无需扫描整个索引，这使得查询速度得到大幅提升，根据实际案例，速度一般可以提升 50% 左右。创建分区索引时，需要在创建索引语句中指定分区相关的参数，例如 “CREATE INDEX index_name ON table_name (column_name) LOCAL;”，这样就创建了一个本地分区索引。

（三）新手常犯的 3 类建索引错误

1. 过度索引：空间与性能的双重杀手

新手在使用索引时最容易犯的错误之一就是过度索引，即创建了过多不必要的索引。这就好比在一个小房间里堆满了各种工具，虽然看起来应有尽有，但真正使用时却很难快速找到需要的工具，而且还占用了大量空间。在数据库中，过度索引会带来空间和性能的双重负面影响。例如，在一个仅有 10 个值的 “用户类型” 列创建 B 树索引，由于该列的值超级有限，索引文件的大小可能会超过表数据本身，造成存储空间的极大浪费。而且，每次对表进行更新操作时，数据库都需要维护这些多余的索引，这会导致写入延迟增加，列如可能会增加 15ms 的延迟，严重影响系统的实时性和响应速度。

2. 忽略表达式索引

当查询条件中包含函数时，如 “WHERE LOWER (name)='john'”，如果没有创建函数索引，数据库将无法利用常规索引进行快速查询，只能进行全表扫描，这会大大降低查询效率。正确的做法是创建函数索引，针对这种情况，我们可以使用 “CREATE INDEX index_name ON table_name (LOWER (name));” 语句来创建函数索引，这样在执行上述查询时，数据库就能利用函数索引快速定位到符合条件的数据，避免全表扫描带来的性能损耗。

3. 主键索引外的重复建设

在 Oracle 数据库中，当我们为表定义主键时，数据库会自动为主键创建唯一索引，以确保主键的唯一性和数据的完整性。不过，有些新手可能会忽略这一点，在主键已经有索引的情况下，又重复创建一样的索引，这无疑是一种资源的浪费。据统计，这种重复建设可能会浪费 30% 以上的索引存储空间，而且在数据更新时，还会增加不必要的索引维护开销，降低系统性能。所以，在创建索引时，必定要清楚数据库的默认机制，避免主键索引的重复建设 。

三、索引优化：让查询性能再提升 40% 的进阶技巧

（一）动态监控：3 个必查的核心指标

1. 索引命中率：索引命中率是衡量索引使用效率的关键指标，它反映了在查询过程中，索引被实际使用的频率。通过查询 V$OBJECT_USAGE 视图，我们可以清晰地了解每个索引的使用情况。具体查询语句如下：

SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'your_index_name';

在这个视图中，USED 列表明索引是否被使用，如果值为 NO，说明该索引自监控以来从未被使用过。为了确保索引的有效性，我们可以设置一个监控周期，列如连续 7 天监控索引的使用情况。如果某个索引在连续 7 天内都未被使用，那么它很可能是多余的，我们就可以思考将其删除，以减少不必要的索引维护开销，释放宝贵的存储空间。

2. 碎片率：索引碎片率是影响查询性能的另一个重大因素。当索引中存在大量碎片时，数据库在读取索引数据时需要读取更多的物理块，这会增加 I/O 操作的次数，从而降低查询效率。一般来说，当 FREELISTS（空闲列表）的值大于 10 时，就说明索引可能存在较高的碎片率。例如，我们可以使用以下语句来分析索引的碎片情况：

SELECT * FROM INDEX_STATS WHERE INDEX_NAME = 'your_index_name';

在返回的结果中，FREELISTS 字段的值可以协助我们判断索引的碎片程度。对于碎片率较高的索引，我们可以通过定期重建索引来降低碎片率，提高查询性能。重建索引的语句如下：

ALTER INDEX your_index_name REBUILD;

重建索引会重新组织索引的数据结构，将碎片整理合并，从而提高索引的访问效率。

3. 统计信息时效性：统计信息对于数据库的查询优化至关重大，它能够协助优化器选择最优的执行计划。不过，随着数据的不断更新，统计信息可能会逐渐过时，这就会导致优化器做出错误的决策，选择不合理的执行计划，从而影响查询性能。为了确保统计信息的时效性，我们可以每周执行一次
DBMS_STATS.GATHER_TABLE_STATS 存储过程来更新统计信息。具体的执行语句如下：

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(

ownname => 'your_schema_name',

tabname => 'your_table_name',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO'

);

END;

这个存储过程会自动收集表中所有列的统计信息，并根据数据量的大小自动选择合适的采样率，以确保统计信息的准确性。通过定期更新统计信息，我们可以让优化器更加准确地评估查询成本，从而选择最优的执行计划，提高查询性能 。

（二）性能调优的 5 个实操技巧

1. 覆盖索引：覆盖索引是一种超级有效的优化策略，它能够显著减少查询过程中的回表操作，从而提高查询效率。所谓覆盖索引，就是将查询所需的所有列都包含在索引中，这样在查询时，数据库就可以直接从索引中获取到所需的数据，而无需再通过索引定位到表中的数据行，从而避免了回表操作带来的额外开销。

例如，有一个用户表，我们常常需要查询用户的姓名和邮箱，查询语句如下：

SELECT 姓名, 邮箱 FROM 用户表 WHERE 部门='技术部';

如果我们为部门、姓名和邮箱这三个列创建一个组合索引，即：

CREATE INDEX idx_user_info ON 用户表 (部门, 姓名, 邮箱);

那么这个索引就可以成为覆盖索引。当执行上述查询时，数据库可以直接从索引中获取到姓名和邮箱这两个列的数据，而无需再访问用户表，从而大大提高了查询效率。根据实际测试，使用覆盖索引后，查询效率可以提升 60% 左右 。

2. 反向键索引：反向键索引是一种特殊的索引类型，它主要用于解决自增主键在高并发插入时可能出现的索引块争用问题。在传统的 B 树索引中，自增主键的值是按照顺序存储的，这就导致在高并发插入时，新插入的数据会聚焦在索引的同一叶块上，从而造成索引块争用，降低插入性能。

而反向键索引则通过 REVERSE 参数，将索引值的字节顺序进行反转，从而打散索引值的分布。例如，对于自增主键 employee_id，正常的索引值可能是 1、2、3、4……，而在反向键索引中，这些值可能会被存储为 1000000、2000000、3000000、4000000……（这里只是为了说明原理，实际存储的字节顺序反转更为复杂）。这样，在高并发插入时，新插入的数据就会分散到不同的索引叶块上，避免了热点问题，提高了插入性能。创建反向键索引的语句如下：

CREATE INDEX idx_employee_id ON employee (employee_id) REVERSE;

3. 无日志模式快速建索引：在对历史数据进行初始化索引时，一般数据量会超级大，如果按照常规方式创建索引，会生成大量的 redo 日志，这不仅会占用大量的磁盘空间，还会降低建索引的速度。为了提高建索引的效率，我们可以使用 NOLOGGING 选项，以减少 redo 日志的生成。

例如，要为一个包含千万级数据的历史订单表创建索引，可以使用以下语句：

CREATE INDEX idx_order_history ON order_history (order_id) NOLOGGING;

使用这种方式建索引，速度一般可以提升 3 倍左右。但需要特别注意的是，由于使用 NOLOGGING 选项创建索引时不会生成完整的 redo 日志，所以在创建索引之后，必定要确保数据已经进行了备份，以防止在发生故障时数据丢失 。

4. 并行创建加速大表索引：对于大表来说，创建索引往往是一个超级耗时的操作。为了加快大表索引的创建速度，我们可以利用多核 CPU 的优势，采用并行创建索引的方式。通过在创建索引语句中使用 PARALLEL 参数，我们可以指定并发度，即同时参与创建索引的进程数量。

例如，要为一个大表创建索引，并指定使用 4 个进程并行创建，可以使用以下语句：

CREATE INDEX idx_big_table ON big_table (column1) PARALLEL 4;

这样，数据库会启动 4 个并行进程同时进行索引创建操作，大大缩短了建索引的时间。需要注意的是，并行创建索引会消耗更多的系统资源，所以在实际使用时，需要根据系统的硬件配置和当前的负载情况合理调整并发度，以确保系统的稳定性和性能 。

5. 索引失效排查三板斧：在数据库的日常运行中，索引失效是一个常见的问题，它会导致查询性能急剧下降。为了及时发现和解决索引失效问题，我们可以采用以下 “三板斧” 排查方法：

• 检查是否存在数据类型隐式转换：当查询条件中的数据类型与索引列的数据类型不匹配时，可能会发生隐式转换，这会导致索引失效。例如，当我们执行查询语句 “WHERE 订单号 = 123” 时，如果订单号字段在数据库中实际是 VARCHAR 类型，而我们没有将 123 转换为字符串形式，数据库就会进行隐式转换，从而使索引无法生效。正确的做法是将查询语句修改为 “WHERE 订单号 ='123'”，显式地将值转换为与索引列一样的数据类型。

• 确认条件是否满足最左匹配：对于组合索引，必须满足最左匹配原则，索引才能生效。也就是说，在查询条件中，要从组合索引的最左边的列开始，按照索引定义的顺序依次使用列。例如，有一个组合索引为（用户 ID, 订单状态，创建时间），如果我们的查询语句是 “SELECT * FROM orders WHERE 订单状态 =' 已支付 ' AND 创建时间 BETWEEN '2023 – 01 – 01' AND '2023 – 12 – 31'”，由于没有使用组合索引最左边的用户 ID 列，所以这个索引无法生效。只有当查询条件包含用户 ID 列，并且按照索引定义的顺序使用其他列时，索引才能发挥作用。

• 查看执行计划（EXPLAIN PLAN）：执行计划是数据库执行查询语句时的详细步骤和策略，通过查看执行计划，我们可以清楚地了解查询是否走了索引扫描，还是进行了全表扫描。在 Oracle 中，我们可以使用 EXPLAIN PLAN 命令来查看执行计划。例如，对于查询语句 “SELECT * FROM employees WHERE employee_id = 100;”，我们可以先执行 “EXPLAIN PLAN FOR SELECT * FROM employees WHERE employee_id = 100;”，然后再通过 “SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);” 来查看详细的执行计划。如果执行计划显示使用了全表扫描（如 “TABLE ACCESS FULL”），而我们期望使用索引扫描，那么就说明索引可能存在问题，需要进一步排查 。

四、生产环境避坑：5 类高频问题解决方案

（一）写入性能下降？先查这 2 点

1. 是否存在索引阻塞？

在高并发写入场景中，索引阻塞是一个常见的问题，它会严重影响写入性能。其中，位图索引在并发更新时特别容易产生锁冲突。这是由于位图索引采用了一种特殊的存储结构，它通过位图来表明索引列的值，每一位对应一个数据行。当进行并发更新时，如果多个事务同时修改一样索引列的值，就会导致锁冲突，由于位图索引的锁粒度较大，会锁定整个位图。

例如，在一个电商订单系统中，订单状态字段是一个低基数列，可能只有 “已下单”“已支付”“已发货”“已完成” 等几个状态。如果对这个订单状态字段使用位图索引，当大量订单同时更新状态时，就会出现严重的锁冲突。列如，在某一促销活动期间，每秒有数百个订单同时更新为 “已支付” 状态，由于位图索引的锁冲突，这些更新操作可能会相互等待，导致写入延迟大幅增加，从正常情况下的几毫秒增加到几百毫秒，严重影响系统的响应速度和吞吐量。

为了解决这个问题，我们可以思考改用 B 树索引。B 树索引的锁粒度较小，它是基于数据行进行锁定的，因此在高并发更新场景中能够更好地处理并发操作，减少锁冲突的发生。或者，我们也可以优化事务逻辑，尽量减少对低基数列的频繁修改。列如，可以将多个状态更新操作合并为一个批量操作，减少事务的数量，从而降低锁冲突的概率 。

2. 索引是否过多？

索引虽然能够提升查询性能，但过多的索引会对 DML 操作产生负面影响，导致写入性能下降。当单表索引超过 5 个时，我们就需要高度警惕，认真评估每个索引的必要性。过多的索引会增加数据更新时的开销，由于在执行 INSERT、UPDATE、DELETE 等操作时，数据库不仅要更新数据本身，还需要同时更新相关的索引，以确保索引与数据的一致性。

例如，在一个用户信息表中，如果为用户 ID、姓名、邮箱、手机号、地址、注册时间等多个字段都创建了索引，当插入一条新的用户记录时，数据库需要同时更新这多个索引，这会大大增加写入操作的耗时。根据实际测试和经验总结，当索引过多时，DML 操作耗时可能会增加 25% 以上 。

为了避免这种情况，我们需要定期清理那些仅用于单个特殊查询的索引。这些索引在大多数情况下并不会被使用，但却会占用宝贵的系统资源，增加数据更新的开销。我们可以通过查询数据库的相关视图，如 DBA_INDEXES 视图，来了解每个索引的使用情况，找出那些长时间未被使用的索引，并将其删除。例如，可以使用以下查询语句来筛选出 30 天内未被使用的索引：

SELECT index_name, table_name, last_analyzed

FROM dba_indexes

WHERE last_analyzed u003c SYSDATE – 30;

通过删除这些冗余索引，我们可以有效地降低 DML 操作的耗时，提高写入性能，同时释放宝贵的存储空间，让数据库系统更加高效地运行 。

（二）查询未走索引？按这个流程排查

1. 统计信息是否更新？

统计信息对于数据库的查询优化至关重大，它是优化器选择执行计划的重大依据。不过，随着数据的不断更新，统计信息可能会逐渐过时，这就会导致优化器做出错误的决策，选择不合理的执行计划，从而使查询未走索引。

例如，有一个员工表，原本员工数量为 1000 人，其中部门为 “技术部” 的员工有 100 人。在这种情况下，优化器根据统计信息认为通过全表扫描查询 “技术部” 员工的效率较高，由于全表扫描的成本相对较低。但后来，“技术部” 员工数量增加到了 500 人，而统计信息却没有及时更新，此时优化器依旧可能选择全表扫描，而不是使用索引扫描，这就导致查询效率低下。

为了解决这个问题，我们可以执行 “ANALYZE TABLE 表名 COMPUTE STATISTICS;” 语句来强制更新统计信息。这条语句会重新收集表中数据的统计信息，包括数据的分布情况、列的基数等，从而让优化器能够做出更准确的决策。例如，对于员工表，我们可以执行 “ANALYZE TABLE employees COMPUTE STATISTICS;”，这样优化器在后续查询时，就能根据最新的统计信息选择最优的执行计划，确保查询能够正确使用索引，提高查询效率 。

2. 条件是否被函数 “包裹”？

当查询条件中的列被函数 “包裹” 时，常规索引一般无法发挥作用，由于索引是基于列的原始值建立的，而函数操作会改变列的值。例如，在地址表中，如果我们使用 “WHERE SUBSTR (地址，1, 2)=‘北京’” 这样的查询条件，由于地址列被 SUBSTR 函数处理，数据库无法直接使用地址列上的常规索引进行快速查询，只能进行全表扫描，这会大大降低查询效率。

为了解决这个问题，我们可以创建函数索引。函数索引是针对经过函数处理后的列值建立的索引，它能够让数据库利用索引快速定位到符合条件的数据。对于上述例子，我们可以使用 “CREATE INDEX idx_address_sub ON 表名 (SUBSTR (地址，1, 2));” 语句来创建函数索引。这样，当执行 “WHERE SUBSTR (地址，1, 2)=‘北京’” 查询时，数据库就可以利用这个函数索引快速找到地址以 “北京” 开头的记录，避免全表扫描，提高查询性能 。

3. 是否存在大量 NULL 值？

B 树索引默认不存储 NULL 值，这是由于 NULL 值在比较和排序时存在特殊的规则，会增加索引的复杂性。当查询条件包含 IS NULL 时，如果使用 B 树索引，数据库无法直接从索引中获取到 NULL 值的记录，需要进行额外的处理，这可能会导致索引失效。

例如，在一个学生成绩表中，有一个成绩字段，部分学生的成绩可能为 NULL。如果我们执行 “SELECT * FROM student_scores WHERE score IS NULL” 查询，使用 B 树索引的话，数据库可能无法利用索引快速定位到成绩为 NULL 的记录，只能进行全表扫描。

为了解决这个问题，我们可以根据具体情况进行单独处理。一种方法是为 NULL 值创建一个特殊的标识，列如将 NULL 值替换为一个特定的数值，如 – 1（前提是该数值在正常成绩范围内不会出现），然后对这个特殊标识进行索引。或者，我们可以改用位图索引，位图索引可以存储 NULL 值，并且在位图中为 NULL 值分配一个特定的位，这样在查询 IS NULL 条件时，能够快速定位到对应的记录 。

（三）索引占用空间过大？3 招优化

1. 键压缩（COMPRESS）：键压缩是一种超级有效的减少索引空间占用的方法，它主要针对组合索引。在组合索引中，往往存在一些重复的前缀键，这些重复的键值会占用大量的存储空间。键压缩通过对这些重复的前缀键进行压缩存储，能够显著减少索引文件的大小。

例如，在一个地址表中，我们创建了一个（省，市，区）的组合索引。在实际数据中，可能有许多记录的省份是一样的，如 “广东省”。如果不进行键压缩，每个记录在索引中都要完整存储 “广东省” 这个前缀键，这会造成大量的空间浪费。而使用键压缩后，一样的前缀键只需要存储一次，后续记录中一样的前缀键可以通过引用的方式来表明，从而大大减少了索引的存储空间。根据实际测试，对这样的地址表索引进行键压缩后，空间一般可以减少 40% 左右 。

在创建索引时，我们可以使用 “CREATE INDEX index_name ON table_name (column1, column2, column3) COMPRESS;” 语句来启用键压缩功能。这里的 index_name 是索引名，table_name 是表名，column1, column2, column3 是组合索引的列，COMPRESS 表明启用键压缩 。

2. 指定专用表空间：将索引与表数据分离存储在专用表空间中，并且将这个专用表空间设置在高速存储设备上（如 SSD），是一种提升 I/O 性能和优化索引空间利用的有效策略。这样做有两个主要好处：一是可以提升 I/O 性能，由于高速存储设备的读写速度更快，能够加快索引的访问速度，从而提高查询性能；二是可以避免索引与表数据竞争空间，使数据库的存储结构更加清晰和合理。

例如，在一个大型电商数据库中，订单表的数据量超级大，索引也占用了大量空间。如果将索引和表数据存储在同一个普通硬盘上，在高并发查询时，I/O 竞争会超级激烈，导致查询性能下降。但如果我们将索引存储在 SSD 上的专用表空间中，就可以大大减少 I/O 等待时间，提高查询响应速度。同时，将索引和表数据分开存储，也便于进行管理和维护，列如可以对索引表空间进行单独的备份和恢复操作 。

在创建索引时，我们可以使用 “CREATE INDEX index_name ON table_name (column_name) TABLESPACE index_tablespace;” 语句来指定索引存储的表空间，其中 index_tablespace 就是我们创建的专用索引表空间 。

3. 定期删除无效索引：随着数据库的不断发展和业务需求的变化，有些索引可能会逐渐变得不再使用，但它们依旧占用着宝贵的存储空间。通过 DBA_INDEXES 视图，我们可以筛选出那些长时间未被使用的索引，从而定期删除这些无效索引，释放冗余空间。

例如，我们可以使用以下查询语句来筛选出 LAST_ANALYZED 超过 30 天未使用的索引：

SELECT index_name, table_name, last_analyzed

FROM dba_indexes

WHERE last_analyzed u003c SYSDATE – 30;

这条语句会查询出 30 天内没有被分析使用过的索引信息，包括索引名、所属表名和最后分析时间。通过分析这些信息，我们可以确定哪些索引是无效的，并使用 “DROP INDEX index_name;” 语句将其删除。根据实际经验，定期删除无效索引平均可以节省 20%-30% 的索引存储，有效优化了数据库的存储空间利用 。

五、总结：打造高性能数据库的黄金法则

索引是数据库优化的核心工具，但绝非 “一刀切”—— 需结合业务场景选择类型，在建索引前评估查询与更新的平衡，定期监控并优化索引状态。记住：合理的索引设计是提升数据库性能的关键，它能让查询速度大幅提升，为数据库的长期稳定运行奠定基础。

你在实际开发中遇到过哪些索引难题？欢迎在评论区分享你的经验，一起探讨数据库优化的更多技巧！