拨开慢SQL迷雾｜连载02：一文掌握索引优化精髓，何时建、如何建、怎么避坑

磐维（PanWeiDB/openGauss）SQL 优化实战系列：
《拨开慢SQL迷雾｜连载01：一文吃透执行计划基础，开启SQL调优之路》

导语

索引是数据库查询优化的"银弹"，但绝非越多越好。在磐维（PanWeiDB/openGauss）中，一个设计合理的索引可以将查询从14 毫秒优化到 0.2 毫秒；而一个设计不当的索引，不仅无法提速，还会拖慢 DML（INSERT/UPDATE/DELETE）性能，甚至因函数或隐式转换导致优化器直接弃用。

本文结合 TPCH 与示例库的真实案例，从何时建、如何建、怎么避坑三个维度，系统讲解磐维数据库中的索引优化实战。

一、何时建索引

建索引不能凭感觉，需要结合业务逻辑、数据分布、执行计划三方面综合判断。

1.1 根据业务逻辑判断

熟悉业务的同学可以直接根据查询特征创建索引：

• WHERE 条件列：高频出现在过滤条件中的列
• JOIN 关联列：表连接条件中的外键或关联字段
• ORDER BY / GROUP BY 列：需要排序或分组的列

1.2 根据数据分布判断

如果不确定某列是否适合建索引，可以先查看该列的数据离散度。离散度越高（不同值越多），索引的选择性越好。

以 TPCH 的 orders 表为例：

-- 查看 orders 表按日期的分布
select o_orderdate, count(*) from orders 
group by o_orderdate order by 2 limit 10;

结果：

      o_orderdate   | count
    ----------------+-------
     1994-02-13     |  1131
     1998-06-30     |  1140
     1997-11-22     |  1144
     ...

再查看总行数：

select count(*) from orders;
-- count: 3000000

分析：o_orderdate 每天约 1000 多条，离散度很高。如果业务经常按日期范围查询，该列非常适合建立索引。

1.3 根据执行计划判断（最可靠）

当执行计划中出现 Seq Scan 且 Rows Removed by Filter 极大时，说明数据库扫描了大量数据却只返回极少行，这是建索引的最强信号。

案例：查询 t_objects 表中 OBJECT_NAME = 'EMP' 的记录。

EXPLAIN ANALYZE SELECT * FROM T_OBJECTS WHERE OBJECT_NAME = 'EMP';

执行计划：

                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Seq Scan on t_objects  (cost=0.00..2995.21 rows=2 width=194) (actual time=12.545..14.751 rows=1 loops=1)
   Filter: ((object_name)::text = 'EMP'::text)
+   Rows Removed by Filter: 86656
 Total runtime: 14.813 ms
(4 rows)

关键信号：

• Rows Removed by Filter: 86656：扫描了 8.6 万行，仅返回 1 行
• 耗时 14.8 ms，对于单条精确查询来说过慢

结论：object_name 列急需索引。

二、如何建索引

判断需要索引后，接下来要确定索引类型（单列/复合）并验证效果。

2.1 单列索引：精准打击高频过滤列

针对上一节的 t_objects 案例，创建单列索引：

CREATE INDEX t_objects_n10 on t_objects(object_name);

创建后再次执行：

EXPLAIN ANALYZE SELECT * FROM T_OBJECTS WHERE OBJECT_NAME = 'EMP';

新执行计划：

                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_objects  (cost=4.27..12.10 rows=2 width=194) (actual time=0.166..0.166 rows=1 loops=1)
   Recheck Cond: ((object_name)::text = 'EMP'::text)
   Heap Blocks: exact=1
+   ->  Bitmap Index Scan on t_objects_n10  (cost=0.00..4.26 rows=2 width=0) (actual time=0.161..0.161 rows=1 loops=1)
         Index Cond: ((object_name)::text = 'EMP'::text)
 Total runtime: 0.203 ms
(6 rows)

效果对比：

2.2 复合索引：应对多条件组合查询

当查询条件涉及多列，且这些列经常同时出现时，复合索引（多列索引）比多个单列索引更高效。

案例：TPCH Q3 优化。

业务 SQL：

select
    l_orderkey,
    sum(l_extendedprice * (1 - l_discount)) as revenue,
    o_orderdate,
    o_shippriority
from
    customer,
    orders,
    lineitem
where
    c_mktsegment = 'BUILDING'
    and c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate < date '1995-03-15'
    and l_shipdate > date '1995-03-15'
group by
    l_orderkey,
    o_orderdate,
    o_shippriority
order by
    revenue desc,
    o_orderdate
limit 10;

优化前执行计划（Hash Join）：

Hash Join (rows=150615)
  ->  Seq Scan on lineitem (rows=16168962)
        Filter: (l_shipdate > '1995-03-15 00:00:00'::oradate)
        Rows Removed by Filter: 13830833
  ->  Hash Join (customer 与 orders，返回 732138 行)

问题分析：原执行计划lineitem 表返回 1600 万行与结果集做 Hash Join，需要扫描大量数据。实际上关联后仅返回 15 万行，如果先通过 l_orderkey 和 l_shipdate 过滤，再循环关联，效率更高。

优化方案：

1. 创建复合索引：

CREATE INDEX LINEITEM_N3 ON LINEITEM(L_ORDERKEY, L_SHIPDATE);

2. 使用 Hint 强制 lineitem 走索引和 NestLoop：

select /*+ indexscan(lineitem lineitem_n3) */
       l_orderkey, sum(...) ...

优化后执行计划（Nested Loop）：

    Nested Loop
       ->  Hash Join  (customer 与 orders，返回 732138 行)
       ->  Index Scan using lineitem_n3 on lineitem   (rows=150615)
             Index Cond: ((l_orderkey = orders.o_orderkey) AND (l_shipdate > ...))

优化效果：

• 原耗时：23194 ms
• 加索引并配合 Hint 后：11572 ms
• lineitem 从 Seq Scan 变为 Index Scan using lineitem_n3

复合索引设计原则：

• 等值查询列放前面（如 L_ORDERKEY）
• 范围查询列放后面（如 L_SHIPDATE）
• 遵循"最左前缀"原则，确保索引能被充分利用

2.3 验证索引是否被使用

建索引后务必通过 EXPLAIN ANALYZE 验证优化器是否选择了索引。若未选择，可能是：

1. 索引列上存在函数或隐式转换（见 [3.1 过滤列上使用函数，索引直接失效](#3.1 坑一：过滤列上使用函数，索引直接失效)）
2. 表数据量太小，优化器认为 Seq Scan 更优
3. 统计信息过期，需执行 ANALYZE 表名 更新

三、怎么避坑

索引建了却用不上，是生产环境最常见的性能陷阱。以下三大避坑指南均来自真实案例。

3.1 坑一：过滤列上使用函数，索引直接失效

原理：在索引列上使用函数或表达式时，优化器无法通过索引树定位数据，只能退化为全表扫描。

反例：

EXPLAIN ANALYZE SELECT * FROM orders 
WHERE to_char(o_orderdate,'yyyymmdd') = '19980702';

执行计划：

+ Seq Scan on orders  (cost=0.00..285867.26 rows=37501 width=111)
                    (actual time=9.952..7099.479 rows=3128 loops=1)
-   Filter: (to_char((o_orderdate)::timestamp without time zone,
            'yyyymmdd'::text) = '19980702'::text)
   Rows Removed by Filter: 7496872
Total runtime: 7100.821 ms

问题：to_char() 函数包裹了 o_orderdate 列，即使该列有索引也无法使用。

正例：

EXPLAIN ANALYZE SELECT * FROM orders 
WHERE o_orderdate = to_date('1998-07-02','yyyy-mm-dd');

执行计划：

+ Seq Scan on orders  (cost=0.00..267116.55 rows=3028 width=111) 
                     (actual time=3.316..2266.638 rows=3128 loops=1)
-   Filter: (o_orderdate = '1998-07-02 00:00:00'::oradate)
   Rows Removed by Filter: 7496872
 Total runtime: 2267.741 ms

结果：耗时降至 2267 ms。若该列建有索引，后者可直接命中。

避坑原则：不要把函数套在索引列上。如果需要格式化，应在参数侧（右侧）处理，保持列的"纯净"。

3.2 坑二：隐式类型转换，优化器放弃索引

当查询参数的类型与列类型不一致时，数据库会进行隐式类型转换，这会导致索引失效。

案例：orders_bak 表的 o_custkey 为 varchar 类型。

create table orders_bak as
select o_orderkey,to_char(o_custkey) as o_custkey from orders limit 10000;
create index orders_bak_n1 on orders_bak(o_custkey);
-- 传入数字，触发隐式转换
EXPLAIN ANALYZE SELECT * FROM orders_bak WHERE o_custkey = 40598;

执行计划：

Seq Scan on orders_bak  (cost=0.00..205.00 rows=1 width=10)
                        (actual time=0.025..9.985 rows=1 loops=1)
+   Filter: ((o_custkey)::bigint = 40598)
   Rows Removed by Filter: 9999
Total runtime: 10.116 ms

问题：o_custkey 是 varchar，但传入了数字 40598，数据库隐式转换为 bigint，导致索引 orders_bak_n1 无法使用。

修正：

-- 传入字符串，类型一致
EXPLAIN ANALYZE SELECT * FROM orders_bak WHERE o_custkey = '40598';

执行计划：

[Bypass]
+ Index Scan using orders_bak_n1 on orders_bak
  (cost=0.00..8.27 rows=1 width=10)
  (actual time=6.024..6.025 rows=1 loops=1)
   Index Cond: ((o_custkey)::text = '40598'::text)
Total runtime: 6.107 ms

避坑原则：查询参数类型必须与列定义类型严格一致。设计表时应统一类型，查询时确保传参类型匹配。

3.3 坑三：关联列类型不一致，JOIN 无法利用索引

关联查询中，如果关联字段类型不一致，不仅会导致隐式转换，还会使 JOIN 无法利用索引，被迫走 Hash Join + 全表扫描。

案例：customer.c_custkey 为 bigint，orders_bak.o_custkey 为 text。

-- 类型不一致，隐式转换
EXPLAIN SELECT * FROM customer c
JOIN orders_bak o ON o.o_custkey = c.c_custkey
WHERE c.c_name = 'Customer#000000130';

执行计划：

Hash Join  (cost=10961.01..11141.64 rows=50 width=169)
+   Hash Cond: ((o.o_custkey)::bigint = c.c_custkey)
   ->  Seq Scan on orders_bak o
   ->  Hash
         ->  Seq Scan on customer c
               Filter: ((c_name)::text = 'Customer#000000130'::text)

问题：o_custkey 被隐式转换为 bigint，两表均无法使用索引，只能走 Hash Join + Seq Scan。

修正：显式转换，保持类型一致

EXPLAIN select * FROM customer c
JOIN orders_bak o ON o.o_custkey = c.c_custkey::text
where c.c_name = 'Customer#000000130';

新执行计划：

Nested Loop
   ->  Seq Scan on customer c
         Filter: ((c_name)::text = 'Customer#000000130'::text)
   ->  Index Scan using orders_bak_n1 on orders_bak o
         Index Cond: ((o_custkey)::text = (c.c_custkey)::text)

避坑原则：表设计阶段就应保证关联字段类型一致。若已存在不一致，应在 SQL 中显式转换（如 ::text），确保 JOIN 条件两侧类型相同，从而让索引生效。

总结

索引是数据库优化的基石，但只有"建对、用好"才能发挥价值。下一篇将深入多表关联场景，讲解如何通过 Hint 控制 NestLoop、HashJoin 与 MergeJoin 的连接方式。