PG中的索引失效总结2
Tags： PGPostgreSQL索引失效
简介
对于一个系统而言，稳定性、性能、安全是很重要的几点。运维的一些工作也是围绕着这些去做。对于某些时候，业务层可能会向数据库层提出种种质疑：为什么数据库这么慢？为什么数据库挂了？为什么我这么用，SQL走不了索引？诸如此类。
其实对于了解数据库和运维的大家都知道，这些使用关系型数据库的应用系统，SQL语句的好坏会直接影响系统的性能，很多系统性能很差最后发现都是因为SQL写得很烂的缘故。
有时候可能一条SQL在业务设计之初就存在问题，每次跑的时候每次都走全表扫描，耗费大量的系统资源，亦或者在业务运行到现在的期间内数据量猛增，数据量导致SQL的运行结果远远大于原来的，导致业务受影响。甚至设计的一些SQL，他本身存在过多的复杂操作，各种聚合、连接加到一起，这一条SQL跑起来可能就会造成很大的资源占用，甚至严重导致数据库的内存溢出、数据库宕机等等。或者业务层并不了解各种索引的实际原理，并不能在合适的场景选择合适的索引，这可能导致，原本SQL查询对应的想要他走索引的字段，没走索引。从而影响性能。
对所有的关系型数据库而言，优化器无疑是其中最核心的部分，因为优化器负责解析SQL，而我们又都是通过SQL来访问存储在关系型数据库中的数据的。所以优化器的好坏会直接决定该关系型数据库的强弱。
通常来说，优化器分为两种，一种是CBO，即Cost-BasedOptimizer 的缩写，直译过来就是“ 基于成本的优化器”。一种是RBO，是Rule-BasedOptimizer 的缩写，直译过来就是“基于规则的优化器”。
在得到最终的执行计划时，RBO会根据一组内置的规则，去选择执行计划，这就导致了RBO选择的执行计划可能不是最优的，不是执行时间最短的，因为他只根据对应的规则去选取执行计划。而CBO所用的判断原则为成本，CBO会从目标SQL诸多可能的执行路径中选择成本值最小的一条来作为其执行计划。在CBO模式下，由于开销都是估算值，所以精确性严重依赖于统计信息，如果统计信息越接近表的真实数据时,CBO优化器的估算值则越准确,产生的执行计划也更佳准确。但是如果统计信息和实际表数据差的很远，那么可能通过CBO得出的执行计划也可能不是最优的，这个时候就有可能因为这条错误的执行计划，引起性能问题或者相关故障。而目前主流数据库均采用CBO模式，因为相较于RBO来说，CBO还是更加倾向于得到这个对的执行计划的。
PostgreSQL数据库里也是采用的这种CBO的优化器。下面这部分，我就针对上面所说的PostgreSQL里常见的字段有索引但没有使用索引的现象，进行了几个举例，供大家参考。对于如下的几个案例的执行计划选择，其实归根到最后，都是基于CBO得出的代价最小的，或是影响了CBO的判断得到的最后的结果。
一、索引列存在多个or连接
二、数据量太小
三、选择性不好
四、查询条件模糊
五、表的一个列上有重复索引
六、优化器选项关闭了索引扫描
七、统计信息不准确
八、Hints影响执行计划
九、查询条件中使用函数
十、查询条件中有不等于运算符
一、索引列存在多个or连接
当查询条件中存在多个OR连接时，PostgreSQL需要将所有条件的结果集进行合并，而这个合并操作可能会导致索引失效。
1.模拟环境
1 2 3 4 | postgres=# create table idxidx as select * from pg_class; SELECT 445 postgres=# create index idx_11 on idxidx(oid); CREATE INDEX |
2.测试情况
一个or连接两个索引列（走索引）
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | postgres=# explain analyze select oid,relname,relnamespace from idxidx where oid =17726 or oid=17743; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on idxidx (cost=8.56..14.14 rows=2 width=72) (actual time=0.018..0.019 rows=1 loops=1) Recheck Cond: ((oid = '17726'::oid) OR (oid = '17743'::oid)) Heap Blocks: exact=1 -> BitmapOr (cost=8.56..8.56 rows=2 width=0) (actual time=0.012..0.013 rows=0 loops=1) -> Bitmap Index Scan on idx_11 (cost=0.00..4.28 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=1) Index Cond: (oid = '17726'::oid) -> Bitmap Index Scan on idx_11 (cost=0.00..4.28 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (oid = '17743'::oid) Planning Time: 0.061 ms Execution Time: 0.038 ms (10 rows) |
两个or连接三个索引列（走全表扫描）
1 2 3 4 5 6 7 8 9 | postgres=# explain analyze select oid,relname,relnamespace from idxidx where oid =17726 or oid=17765 or oid=17743; QUERY PLAN -------------------------------------------------------------------------------------------------- Seq Scan on idxidx (cost=0.00..19.79 rows=3 width=72) (actual time=0.012..0.064 rows=1 loops=1) Filter: ((oid = '17726'::oid) OR (oid = '17765'::oid) OR (oid = '17743'::oid)) Rows Removed by Filter: 444 Planning Time: 0.059 ms Execution Time: 0.079 ms (5 rows) |
要避免这种情况，可以尝试对查询条件进行重写，例如使用UNION ALL连接多个查询条件,例如如如下这种方式
1 2 3 4 5 6 7 8 9 10 11 12 13 | postgres=# explain analyze select oid,relname,relnamespace from idxidx where oid =17726 union all select oid,relname,relnamespace from idxidx where oid=17765 union all select oid,relname,relnamespace from idxidx where oid=17743; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.27..24.92 rows=3 width=72) (actual time=0.041..0.046 rows=1 loops=1) -> Index Scan using idx_11 on idxidx (cost=0.27..8.29 rows=1 width=72) (actual time=0.041..0.042 rows=1 loops=1) Index Cond: (oid = '17726'::oid) -> Index Scan using idx_11 on idxidx idxidx_1 (cost=0.27..8.29 rows=1 width=72) (actual time=0.002..0.002 rows=0 loops=1) Index Cond: (oid = '17765'::oid) -> Index Scan using idx_11 on idxidx idxidx_2 (cost=0.27..8.29 rows=1 width=72) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (oid = '17743'::oid) Planning Time: 0.169 ms Execution Time: 0.082 ms (9 rows) |
二、数据量太小
对于非常小的表或者索引，使用索引可能会比全表扫描更慢。这是因为使用索引需要进行额外的 I/O 操作，而这些操作可能比直接扫描表更慢。
1.模拟环境
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | postgres=# create table tn(id int,name varchar); CREATE TABLE postgres=# insert into tn values(1,'ysl'); INSERT 0 1 postgres=# insert into tn values(2,'ysl'); INSERT 0 1 postgres=# insert into tn values(2,'ysll'); INSERT 0 1 postgres=# insert into tn values(2,'ysll'); INSERT 0 1 postgres=# create index idx_tn on tn(id); CREATE INDEX postgres=# \d tn Table "public.tn" Column | Type | Collation | Nullable | Default --------+-------------------+-----------+----------+--------- id | integer | | | name | character varying | | | Indexes: "idx_tn" btree (id) postgres=# select * from tn; id | name ----+------ 1 | ysl 2 | ysl 2 | ysll 2 | ysll (4 rows) |
2.测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | postgres=# explain analyze select * from tn where id=2; QUERY PLAN --------------------------------------------------------------------------------------------- Seq Scan on tn (cost=0.00..1.05 rows=1 width=36) (actual time=0.007..0.007 rows=3 loops=1) Filter: (id = 2) Rows Removed by Filter: 1 Planning Time: 0.053 ms Execution Time: 0.021 ms (5 rows) postgres=# explain analyze select * from tn where id=1; QUERY PLAN --------------------------------------------------------------------------------------------- Seq Scan on tn (cost=0.00..1.05 rows=1 width=36) (actual time=0.011..0.012 rows=1 loops=1) Filter: (id = 1) Rows Removed by Filter: 3 Planning Time: 0.057 ms Execution Time: 0.026 ms (5 rows) |
三.选择性不好
如果索引列中有大量重复的数据，或者一个字段全是一个值，这个时候，索引可能并不能发挥它的作用，起到加快检索的作用，因为这个索引并不能显著地减少需要扫描的行数，所以计算的代价可能远远大于走别的执行计划的代价。
基数：数据库基数是指数据库中不同值的数量
select count(distinct column_name) from table_name;选择性：基数和总行数的比值再乘以100%就是某个列的选择性。
select count(distinct column_name) /count（column_name）* 100% from table_name;
1.模拟环境
1 2 3 4 | postgres=# create table tb_t1 as select * from pg_class; SELECT 465 postgres=# create index idx_tb_t1 on tb_t1(oid); CREATE INDEX |
2.测试
可以看到，原本oid这一列，选择性较好，分布较均匀的时候，可以正常使用到索引。而选择性不好的情况下，则
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | postgres=# explain analyze select * from tb_t1 where oid=17726; QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on tb_t1 (cost=4.29..9.86 rows=2 width=236) (actual time=0.024..0.025 rows=1 loops=1) Recheck Cond: (oid = '17726'::oid) Heap Blocks: exact=1 -> Bitmap Index Scan on idx_tb_t1 (cost=0.00..4.29 rows=2 width=0) (actual time=0.021..0.021 rows=1 loops=1) Index Cond: (oid = '17726'::oid) Planning Time: 0.220 ms Execution Time: 0.059 ms (7 rows) postgres=# update tb_t1 set oid=1; UPDATE 465 postgres=# reindex index idx_tb_t1; REINDEX postgres=# explain analyze select * from tb_t1 where oid=1; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan on tb_t1 (cost=0.00..29.81 rows=465 width=274) (actual time=0.013..0.080 rows=465 loops=1) Filter: (oid = '1'::oid) Planning Time: 0.344 ms Execution Time: 0.111 ms (4 rows) |
上边的这个例子，在我做完update后，列的基数是select count(distinct oid) from tb_t1;也就是1。而选择性是select count(distinct oid)/count(oid) 100% from tb_t1;也就是1/465 100% 选择性特别低。索引不能起到减少扫描的行数，反而在原本的基础上多了回表的动作，代价就增多了。因此CBO没有选择走这个索引的执行计划。
四、查询条件模糊
如果查询条件模糊，例如使用了不等于（<>）、LIKE等运算符或者使用了函数等，那么索引可能无法被使用。
因为正常情况下，等于（=）操作符可以直接利用B-tree或哈希索引进行查找。这是因为，这些操作符只需要在索引树中查找与给定值相等的项，就可以快速地定位到符合条件的记录。
而不等于（<>）操作符则需要查找所有不符合条件的记录，这会导致需要遍历整个索引树来找到匹配的记录，因此使用索引的成本比全表扫描更高。
LIKE操作符也可能导致不使用索引。这是因为，LIKE操作符通常需要执行模糊匹配，即查找包含你给的关键字的记录。虽然可以使用B-tree索引进行模糊匹配，但是如果模式以通配符开头（例如’%abc’），则索引将不会被使用，因为这种情况下需要遍历整个索引树来查找符合条件的记录。
这两种方式在列上有索引的时候，都是不能显著地减少需要扫描的行数。甚至加大SQL执行的代价，因此可能上边的索引不会被CBO选择为最后最优的执行计划。
1.模拟环境
1 2 3 4 | postgres=# create table tb_l1 as select * from pg_class; SELECT 465 postgres=# create index idx_tb_l1 on tb_l1(oid); CREATE INDEX |
2.测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | postgres=# explain analyze select * from tb_l1 where oid=17726; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Index Scan using idx_tb_l1 on tb_l1 (cost=0.27..8.29 rows=1 width=274) (actual time=0.029..0.030 rows=1 loops=1) Index Cond: (oid = '17726'::oid) Planning Time: 0.473 ms Execution Time: 0.083 ms (4 rows) postgres=# explain analyze select * from tb_l1 where oid<>17726; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan on tb_l1 (cost=0.00..17.81 rows=464 width=274) (actual time=0.007..0.103 rows=464 loops=1) Filter: (oid <> '17726'::oid) Rows Removed by Filter: 1 Planning Time: 0.069 ms Execution Time: 0.132 ms (5 rows) |
五、表的一个列上有重复索引
在PostgreSQL里，是允许在一列上建立多个索引的，也就是如下这种方式，是不会报错说索引重复的，这也就导致了，使用过程中表上可能存在多余的重复索引，索引不会全部被使用到，而且可能引起性能问题。
1 2 3 4 | postgres=# create index idx_tb_l1 on tb_l1(oid); CREATE INDEX postgres=# create index idx_tb_l2 on tb_l1(oid); CREATE INDEX |
1.模拟环境
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | postgres=# create table tb_l1 as select * from pg_class; SELECT 465 postgres=# create index idx_tb_l1 on tb_l1(oid); CREATE INDEX postgres=# create index idx_tb_l2 on tb_l1(oid); CREATE INDEX postgres=# \d tb_l1 Table "public.tb_l1" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- oid | oid | | | relname | name | | | relnamespace | oid | | | reltype | oid | | | reloftype | oid | | | relowner | oid | | | ... ... ... ... Indexes: "idx_tb_l1" btree (oid) "idx_tb_l2" btree (oid) |
2.测试
1 2 3 4 5 6 7 | postgres=# explain analyze select * from tb_l1 where oid=17726; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Index Scan using idx_tb_l2 on tb_l1 (cost=0.27..8.29 rows=1 width=274) (actual time=0.025..0.025 rows=1 loops=1) Index Cond: (oid = '17726'::oid) Planning Time: 0.364 ms Execution Time: 0.043 ms (4 rows) |
测试可以看到，在一个表的同一列上的两个索引其实作用是一样的，仅仅名字不一样，属于重复索引，这种情况下，就算用到索引，同一时刻也就会使用到一个索引。
使用如下的SQL可以找到数据库里的重复索引，可以定期巡检的时候进行检查，并在确认后合理优化掉重复的索引
1 2 3 4 5 6 7 | SELECT indrelid :: regclass AS table_name, array_agg(indexrelid :: regclass) AS indexes FROM pg_index GROUP BY indrelid, indkey HAVING COUNT(*) > 1; |
一个执行的结果如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | postgres=# SELECT indrelid :: regclass AS table_name, array_agg(indexrelid :: regclass) AS indexes FROM pg_index GROUP BY indrelid, indkey HAVING COUNT(*) > 1; table_name | indexes ------------+----------------------- tb_l1 | {idx_tb_l1,idx_tb_l2} t1 | {ind1,idx2} (2 rows) postgres=# \di+ idx_tb_l1 List of relations Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description --------+-----------+-------+---------+-------+-------------+---------------+-------+------------- public | idx_tb_l1 | index | xmaster | tb_l1 | permanent | btree | 32 kB | (1 row) postgres=# \di+ idx_tb_l2 List of relations Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description --------+-----------+-------+---------+-------+-------------+---------------+-------+------------- public | idx_tb_l2 | index | xmaster | tb_l1 | permanent | btree | 32 kB | (1 row) |
六、优化器选项关闭了索引扫描
PostgreSQL里有着很多的可以影响优化器的参数，例如enable_indexscan,enable_bitmapscan,enable_hashjoin,enable_sort等等，这些参数可以在session，用户，数据库级别进行设置。可以通过设置这些参数的值，来改变相关SQL执行时的执行计划。但是需要注意的是，为了个别的SQL，去盲目改变这些参数的值，往往是得不偿失的，操作的时候需要严谨并且仔细考虑，否则，这些类型的参数的改变，对于数据库的性能影响可能是巨大的。
1.模拟环境
1 2 3 4 | postgres=# create table tb_l1 as select * from pg_class; SELECT 465 postgres=# create index idx_tb_l1 on tb_l1(oid); CREATE INDEX |
2.测试
开启了对应优化器选项
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | postgres=# show enable_indexscan ; enable_indexscan ------------------ on (1 row) postgres=# show enable_bitmapscan ; enable_bitmapscan ------------------- on (1 row) postgres=# explain analyze select * from tb_l1 where oid=17721; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Index Scan using idx_tb_l2 on tb_l1 (cost=0.27..8.29 rows=1 width=274) (actual time=0.017..0.018 rows=1 loops=1) Index Cond: (oid = '17721'::oid) Planning Time: 0.088 ms Execution Time: 0.038 ms (4 rows) |
关闭对应的优化器选项，可以看到CBO受到设置的参数的影响，选择了seq scan的执行计划，而没有用到字段上的索引。
1 2 3 4 5 6 7 8 9 10 11 12 13 | postgres=# set enable_indexscan=off; SET postgres=# set enable_bitmapscan=off; SET postgres=# explain analyze select * from tb_l1 where oid=17721; QUERY PLAN -------------------------------------------------------------------------------------------------- Seq Scan on tb_l1 (cost=0.00..17.81 rows=1 width=274) (actual time=0.024..0.137 rows=1 loops=1) Filter: (oid = '17721'::oid) Rows Removed by Filter: 464 Planning Time: 0.079 ms Execution Time: 0.192 ms (5 rows) |
七、统计信息不准确
因为CBO本身是基于代价的优化器，而计算代价要根据统计信息去做计算，统计信息不准确，得到的执行计划可能不是最优，这一点不做具体的举例。
八、Hints影响执行计划
PostgreSQL数据库里有着像ORACLE里类似的Hints功能，即pg_hint_plan工具，用Hints能够改变sql语句的执行计划，hint就是优化器的一种指示。虽然功能上和效果是类似的，但是PostgreSQL和ORACLE的Hints并不完全一致的，例如全表扫描等的关键字是不同的，需要进行区分。
1、准备环境
数据库需安装pg_hint_plan插件
1 2 3 4 5 6 | postgres=# create table test_hint(id int,c varchar(100)); CREATE TABLE postgres=# insert into test_hint select i,'test'||i from generate_series(1,10000) i; INSERT 0 10000 postgres=# create index idx_test_hint_id on test_hint(id); CREATE INDEX |
2、测试
默认会走索引扫描，但是使用了hint，让其走了seqscan，没有使用到对应的字段上的索引。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | postgres=# explain analyze select * from test_hint where id=10; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_test_hint_id on test_hint (cost=0.29..8.30 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=1) Index Cond: (id = 10) Planning Time: 0.111 ms Execution Time: 0.024 ms (4 rows) postgres=# explain analyze select /*+seqscan(t) */ * from test_hint t where id=10; QUERY PLAN -------------------------------------------------------------------------------------------------------- Seq Scan on test_hint t (cost=0.00..180.00 rows=1 width=12) (actual time=0.022..2.691 rows=1 loops=1) Filter: (id = 10) Rows Removed by Filter: 9999 Planning Time: 0.311 ms Execution Time: 2.712 ms (5 rows) |
九、查询条件中使用函数
当查询条件中包含函数调用时，PostgreSQL里可能无法使用索引，因为它需要对所有数据进行计算，而不是只计算索引值。
