PostgreSQL SQL优化 SQL不走索引的几种情况

1 背景知识

在生产环境中经常会遇到SQL优化器不用索引，可能的原因包括：

SQL 写的不好，导致无法使用索引；
SQL优化器认为索引的代价要比顺序扫描代价高。
这里我们主要讨论第二种原因出现的几种场景。

2 场景一：数据量较小的表

经常有开发问，为什么有索引而不走索引呢？因为优化器认为走索引方式太慢了！

2.1 环境准备

CREATE TABLE sma(id int);
CREATE INDEX sma_idx ON sma (id);
INSERT INTO sma VALUES(generate_series(1,100));
ANALYZE sma;

2.2 查看执行计划

执行计划是全表扫描，cost 值为 2.25 。

EXPLAIN SELECT id FROM sma WHERE id = 10;

                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on sma  (cost=0.00..2.25 rows=1 width=4)
   Filter: (id = 10)
(2 rows)

2.3 关闭全表扫描

SET enable_seqscan to off;

2.4 查看执行计划

执行计划是索引扫描，cost 值为 8.12.

EXPLAIN SELECT id FROM sma WHERE id = 10;

                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Index Only Scan using sma_id_idx on sma  (cost=0.14..8.12 rows=1 width=4)
   Index Cond: (id = 10)
(2 rows)

可以看到索引的总成本8.12要比顺序扫描2.25高了4倍左右，所以优化器选择走顺序扫描了。

在小表场景中，优化器可能有限选择顺序扫描而不是索引扫描。是由于SQL 执行时能够能在1个数据块（整个表只有一个数据块）里就获得了所需数据，相比索引扫描，在索引块的叶子节点获得对应数据块的指针后还需要去表里去扫描取得行记录，这样扫描的总块数，或者说IO次数就会多于顺序扫描所需的块数。

3 场景二：列的相关度

索引扫描需要确保读取表的IO尽可能少，才能更好的提高性能。而这就要求通过索引取得的 citid 尽可能聚集在相邻的数据块。

索引和数据的关联度称之为 PostgreSQL 列的相关性。

索引需要经历 根节点 → 分支节点 → 叶子节点，根据叶子节点取回ctid，再将ctid 排序去读取数据块。

所以 citid 的集中程度决定了所需 IO 的开销大小。

在 PostgreSQL 数据库中，可以使用 cluster 命令进行聚簇，提高列的关联度，因为索引排序和数据分布都是连续的。

3.1 列的相关性

3.1.1 如果相关性较好时

SELECT * FROM tbl WHERE col_asc BETWEEN 2 AND 4;

对于 col_asc，只需要读第一个页面即可，因为数据排序是紧凑连续的。

3.1.2 如果相关性较差时

SELECT * FROM tbl WHERE col_rand BETWEEN 2 AND 4;

则需要读取所有的页面，因为数据分布是离散的，它们可能存在不同的数据块。

3.2 查看列的相关性

统计信息 correlation字段就是表示索引列与表数据的关联度，表示列的物理顺序和逻辑顺序的相关性，相关性越高，走索引扫描的离散块扫描更少，代价越低。

CREATE  TABLE tbl (col text ,col_asc integer,col_desc integer,col_rand integer,data text);
INSERT INTO tbl valuesint;

SELECT col,col_asc,col_desc,col_rand FROM tbl;
   col    | col_asc | col_desc | col_rand 
----------+---------+----------+----------
 Tuple_1  |       1 |       12 |        3
 Tuple_2  |       2 |       11 |        8
 Tuple_3  |       3 |       10 |        5
 Tuple_4  |       4 |        9 |        9
 Tuple_5  |       5 |        8 |        7
 Tuple_6  |       6 |        7 |        2
 Tuple_7  |       7 |        6 |       10
 Tuple_8  |       8 |        5 |       11
 Tuple_9  |       9 |        4 |        4
 Tuple_10 |      10 |        3 |        1
 Tuple_11 |      11 |        2 |       12
 Tuple_12 |      12 |        1 |        6
(12 rows)

SELECT tablename,attname, correlation FROM sys_stats WHERE tablename = 'tbl';
 tablename | attname  | correlation 
-----------+----------+-------------
 tbl       | col_asc  |      1
 tbl       | col_desc |     -1
 tbl       | col_rand | 0.1258
(3 rows)

表有三个索引，其中

col_asc索引是顺序递增的
col_desc索引是倒序递减的
col_rand索引则是随机分布的

4 场景三：索引返回的结果集过大

如果使用索引后回表时。如果结果集过大（超过表数据的5%）会产生大量的离散IO，那么索引的作用就微乎其微了，这时候再用索引扫描代价明显高于顺序扫描。

如果使用索引后回表时，r如果结果集较小，则这个索引有较好的选择率，这时候索引更高效。

4.1 环境准备

CREATE TABLE big_t(id int);
CREATE INDEX ON big_t (id);
INSERT INTO big_t VALUES(generate_series(1,100000));
ANALYZE big_t ;

4.2 关闭全表扫描，查看执行计划。

SET enable_seqscan TO off;
EXPLAIN SELECT id FROM big_t WHERE id > 1;

QUERY PLAN                                     
----------
 Bitmap Heap Scan on big_t  (cost=3754.40..7139.38 rows=199998 width=4)
   Recheck Cond: (id > 1)
   ->  Bitmap Index Scan on big_t_id_idx1  (cost=0.00..3704.40 rows=199998 width=0)
         Index Cond: (id > 1)
(4 rows)

4.3 开启全表扫描，查看执行计划。

SET enable_seqscan to on;

EXPLAIN SELECT id FROM big_t WHERE id > 1;

                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on big_t  (cost=0.00..3385.00 rows=199998 width=4)
   Filter: (id > 1)
(2 rows)

上面的例子说明，当获得大量结果集时候，索引扫描的开销值为 7239，而全表扫描的开销值 3385 ，所以优化器优先选择全表扫描。

5 场景四：最左原则

test=# create table t(id int,info text);
CREATE TABLE
test=# create index on t(id,info);
CREATE INDEX
test=# insert into t select n,md5text) from generate_series(1,100000 as n;
INSERT 0 100000
test=# analyze t;
ANALYZE
test=# explain select id,info from t where id = 1 and info = 'hello';
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Index Only Scan using t_id_info_idx on t  (cost=0.42..8.44 rows=1 width=37)
   Index Cond: ((id = 1) AND (info = 'hello'::text))
(2 rows)

test=# explain select id,info from t where info = 'hello';
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on t  (cost=0.00..2084.00 rows=1 width=37)
   Filter: (info = 'hello'::text)
(2 rows)

可以看到，我创建了一个复合索引，假设是（a,b,c）的复合索引，那么索引会先按照a列排序存储，接着按照b列排序，最后是c列，假如SQL直接查询的b或者c，意味着基可能需要访问整棵索引树，这样代价太高了。

test=# set enable_seqscan to off;
SET
test=# explain select id,info from t where info = 'hello';  
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Index Only Scan using t_id_info_idx on t  (cost=0.42..3630.43 rows=1 width=37)
   Index Cond: (info = 'hello'::text)
(2 rows)

强制使用索引扫描，成本要比上面的顺序扫描高，所以对于查询条件中跳过联合索引的索引前导列的情况，优化器默认使用了顺序扫描。

6 场景五：查询条件模糊

如果查询条件使用了不等于（<>）、LIKE等运算符或者使用了函数等，那么索引可能无法被使用。

6.1 <> 操作符

等于（=）操作符可以直接利用B-tree或哈希索引进行查找。这是因为这些操作符可以在索引结构中找到对应的记录选项；

而不等于（<>）操作符则需要查找所有不符合条件的记录，这会导致需要遍历整个索引树来找到匹配的记录，带来的结果是使用索引的成本比全表扫描成本更高。

6.2 LIKE 操作符

LIKE 操作符也有可能导致无法使用索引。

例如 %abc，则索引将不会被使用，如果通配符是 abc%，则可以使用索引。原理同上。可以参考 LIKE无法使用索引文章提高LIKE 操作查询效率。

7 场景六：糟糕的统计信息

过期的、糟糕的统计信息会让优化器产生误判。

统计信息没有更新可能是autovacuum没有及时运行，这时需要做analyze手动收集统计信息，

在修改字段长度、数据类型、dml大量数据变更，也需要手工收集统计信息，否则选择率可能存在误差导致优化器估算不准，更多信息请参考影响开销值的两个因素。