postgresql—-索引失效详解数据库

什么是索引失效?如果where过滤条件设置不合理,即使索引存在,且where过滤条件中包含索引列,也会导致全表扫描,索引不起作用。什么条件下会导致索引失效呢?

1.任何计算、函数、类型转换

2.!=

3.NOT,相当于使用函数

4.模糊查询通配符在开头

5.索引字段在表中占比较高

6.多字段btree索引查询条件不包含第一列

7.多字段索引查询条件使用OR(有时也会走索引扫描,但查询效率不高)

 

测试表

test=# /timing  
Timing is on. 
test=# create table tbl_index(a bigint,b timestamp without time zone ,c varchar(12)); 
CREATE TABLE 
Time: 147.366 ms 
test=# insert into tbl_index select generate_series(1,10000000),clock_timestamp()::timestamp without time zone,'bit me'; 
INSERT 0 10000000 
Time: 30982.723 ms

 

1.任何计算、函数、类型转换

crtest=# create index idx_tbl_index_a on tbl_index (a); 
CREATE INDEX 
Time: 19634.874 ms 
test=#  
test=# explain analyze select * from tbl_index where a = 1; 
QUERY PLAN                                                           
------------------------------------------------------------------------------------------------------------------------------ 
Index Scan using idx_tbl_index_a on tbl_index  (cost=0.43..8.45 rows=1 width=23) (actual time=59.844..59.850 rows=1 loops=1) 
Index Cond: (a = 1) 
Planning time: 22.788 ms 
Execution time: 60.011 ms 
(4 rows) 
Time: 84.865 ms 
test=# explain analyze select * from tbl_index where a + 1 = 1; 
QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------- 
Gather  (cost=1000.00..84399.00 rows=50000 width=23) (actual time=7678.109..7678.109 rows=0 loops=1) 
Workers Planned: 2 
Workers Launched: 2 
->  Parallel Seq Scan on tbl_index  (cost=0.00..78607.33 rows=20833 width=23) (actual time=7350.047..7350.047 rows=0 loops=3) 
Filter: ((a + 1) = 1) 
Rows Removed by Filter: 3333333 
Planning time: 0.112 ms 
Execution time: 7688.615 ms 
(8 rows) 
Time: 7780.024 ms 
test=# explain analyze select * from tbl_index where power(a,2) = 1; 
QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------- 
Gather  (cost=1000.00..94815.67 rows=50000 width=23) (actual time=47.516..6902.399 rows=1 loops=1) 
Workers Planned: 2 
Workers Launched: 2 
->  Parallel Seq Scan on tbl_index  (cost=0.00..89024.00 rows=20833 width=23) (actual time=4607.894..6892.174 rows=0 loops=3) 
Filter: (power((a)::double precision, '2'::double precision) = '1'::double precision) 
Rows Removed by Filter: 3333333 
Planning time: 13.564 ms 
Execution time: 6904.232 ms 
(8 rows) 
Time: 7051.482 ms 
test=#  
test=# explain analyze select * from tbl_index where a::varchar = '1'; 
QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------- 
Gather  (cost=1000.00..94815.67 rows=50000 width=23) (actual time=1.239..6689.272 rows=1 loops=1) 
Workers Planned: 2 
Workers Launched: 2 
->  Parallel Seq Scan on tbl_index  (cost=0.00..89024.00 rows=20833 width=23) (actual time=4449.890..6679.233 rows=0 loops=3) 
Filter: (((a)::character varying)::text = '1'::text) 
Rows Removed by Filter: 3333333 
Planning time: 1.029 ms 
Execution time: 6692.329 ms 
(8 rows) 
Time: 6723.530 ms

 

在表tbl_index.a字段创建btree索引,使用a=1索引生效,但是下面的例子运算、函数、类型转换却导致索引失效了。

where a + 1 = 1

where power(a,2) = 1

where a::varchar = ‘1’

如何解决呢?可参考前面的表达式索引解决:

create index idx_tbl_index_a on tbl_index ((a+1));

create index idx_tbl_index_a on tbl_index ((power(a,2)));

create index idx_tbl_index_a on tbl_index ((a::varchar));

 

2.!=

test=# explain analyze select * from tbl_index where a != 1; 
QUERY PLAN                                                         
------------------------------------------------------------------------------------------------------------------------- 
Seq Scan on tbl_index  (cost=0.00..140899.00 rows=9999999 width=23) (actual time=0.049..11004.864 rows=9999999 loops=1) 
Filter: (a <> 1) 
Rows Removed by Filter: 1 
Planning time: 0.206 ms 
Execution time: 11585.859 ms 
(5 rows) 
Time: 11587.146 ms

 

3.NOT,相当于使用函数

test=# explain analyze select * from tbl_index where a is null; 
QUERY PLAN                                                           
------------------------------------------------------------------------------------------------------------------------------ 
Index Scan using idx_tbl_index_a on tbl_index  (cost=0.43..4.45 rows=1 width=23) (actual time=30.092..30.092 rows=0 loops=1) 
Index Cond: (a IS NULL) 
Planning time: 33.783 ms 
Execution time: 41.838 ms 
(4 rows) 
Time: 102.544 ms 
test=# explain analyze select * from tbl_index where a is not null; 
QUERY PLAN                                                         
-------------------------------------------------------------------------------------------------------------------------- 
Seq Scan on tbl_index  (cost=0.00..115899.00 rows=10000000 width=23) (actual time=3.062..6309.908 rows=10000000 loops=1) 
Filter: (a IS NOT NULL) 
Planning time: 0.099 ms 
Execution time: 6877.566 ms 
(4 rows) 
Time: 6878.156 ms

以上比较可知where a is null索引生效,但是where a is not null导致索引生效。类似导致索引失效的还有NOT IN,NOT LIKE等,但是NOT EXISTS不会导致索引失效。下面的例子可以看到tbl_index表仍进行索引扫描,但是性能仍有限制,使用NOT IN虽然索引失效,但性能比NOT EXISTS要高。这个和我之前的认识有些出入,之前测试发现NOT EXISTS比NOT IN性能高,看来情况不同,性能也是不一定的。

test=# explain analyze select * from tbl_index where a not in (select a from tbl_test ); 
QUERY PLAN                                                         
------------------------------------------------------------------------------------------------------------------------- 
Seq Scan on tbl_index  (cost=14.50..140913.50 rows=5000000 width=23) (actual time=1.393..3717.312 rows=9999000 loops=1) 
Filter: (NOT (hashed SubPlan 1)) 
Rows Removed by Filter: 1000 
SubPlan 1 
->  Seq Scan on tbl_test  (cost=0.00..12.00 rows=1000 width=8) (actual time=0.038..0.236 rows=1000 loops=1) 
Planning time: 0.134 ms 
Execution time: 4147.857 ms 
(7 rows) 
Time: 4148.615 ms 
test=# explain analyze select * from tbl_index where not exists (select null from tbl_test where tbl_test.a = tbl_index.a); 
QUERY PLAN                                                     
------------------------------------------------------------------------------------------------------------------------------------- 
-------------------- 
Merge Anti Join  (cost=62.45..218187.26 rows=9999000 width=23) (actual time=1.698..16909.581 rows=9999000 loops=1) 
Merge Cond: (tbl_index.a = tbl_test.a) 
->  Index Scan using idx_tbl_index_a on tbl_index  (cost=0.43..193110.43 rows=10000000 width=23) (actual time=0.035..14781.400 row 
s=10000000 loops=1) 
->  Sort  (cost=61.83..64.33 rows=1000 width=8) (actual time=0.390..0.659 rows=1000 loops=1) 
Sort Key: tbl_test.a 
Sort Method: quicksort  Memory: 71kB 
->  Seq Scan on tbl_test  (cost=0.00..12.00 rows=1000 width=8) (actual time=0.038..0.194 rows=1000 loops=1) 
Planning time: 0.339 ms 
Execution time: 17530.472 ms 
(9 rows) 
Time: 17594.258 ms

 

4.模糊查询通配符在开头

test=# explain analyze select * from tbl_index where c like 'bit%'; 
QUERY PLAN                                                         
-------------------------------------------------------------------------------------------------------------------------- 
Seq Scan on tbl_index  (cost=0.00..140899.00 rows=10000000 width=23) (actual time=0.099..1685.317 rows=10000000 loops=1) 
Filter: ((c)::text ~~ 'bit%'::text) 
Planning time: 55.373 ms 
Execution time: 2104.863 ms 
(4 rows) 
Time: 2164.464 ms 
test=# explain analyze select * from tbl_index where c like '%me'; 
QUERY PLAN                                                          
--------------------------------------------------------------------------------------------------------------------------- 
Seq Scan on tbl_index  (cost=0.00..140899.00 rows=10000000 width=23) (actual time=20.172..5507.741 rows=10000000 loops=1) 
Filter: ((c)::text ~~ '%me'::text) 
Planning time: 65.603 ms 
Execution time: 6007.367 ms 
(4 rows)

 

5.索引字段在表中占比较高

test=# insert into tbl_index values (10000001,'2015-05-23 00:00:00','haha'); 
INSERT 0 1 
Time: 88.226 ms 
test=# explain analyze select * from tbl_index where c = 'bit me'; 
QUERY PLAN                                                         
-------------------------------------------------------------------------------------------------------------------------- 
Seq Scan on tbl_index  (cost=0.00..140899.00 rows=10000000 width=23) (actual time=0.051..6758.236 rows=10000000 loops=1) 
Filter: ((c)::text = 'bit me'::text) 
Rows Removed by Filter: 1 
Planning time: 0.128 ms 
Execution time: 7237.900 ms 
(5 rows) 
Time: 7238.685 ms 
test=# explain analyze select * from tbl_index where c = 'haha'; 
QUERY PLAN                                                          
---------------------------------------------------------------------------------------------------------------------------- 
Index Scan using idx_tbl_index_c on tbl_index  (cost=0.43..4.45 rows=1 width=23) (actual time=0.063..0.065 rows=1 loops=1) 
Index Cond: ((c)::text = 'haha'::text) 
Planning time: 0.219 ms 
Execution time: 2.869 ms 
(4 rows) 
Time: 4.942 ms

 

test=# drop index idx_tbl_index_a; 
DROP INDEX 
Time: 134.873 ms 
test=# drop index idx_tbl_index_c; 
DROP INDEX 
Time: 173.572 ms

 

6.多字段btree索引查询条件不包含第一列

test=# explain analyze select * from tbl_index where a = 10000001 and c = 'haha'; 
QUERY PLAN                                                            
-------------------------------------------------------------------------------------------------------------------------------- 
Index Scan using idx_tbl_index_a_c on tbl_index  (cost=0.43..6.20 rows=1 width=23) (actual time=23.254..23.257 rows=1 loops=1) 
Index Cond: ((a = 10000001) AND ((c)::text = 'haha'::text)) 
Planning time: 36.050 ms 
Execution time: 35.710 ms 
(4 rows) 
Time: 78.816 ms 
test=# explain analyze select * from tbl_index where  c = 'haha'; 
QUERY PLAN                                                           
----------------------------------------------------------------------------------------------------------------------------- 
Gather  (cost=1000.00..68982.44 rows=1 width=23) (actual time=7869.579..7890.974 rows=1 loops=1) 
Workers Planned: 2 
Workers Launched: 2 
->  Parallel Seq Scan on tbl_index  (cost=0.00..67982.34 rows=0 width=23) (actual time=7468.480..7468.480 rows=0 loops=3) 
Filter: ((c)::text = 'haha'::text) 
Rows Removed by Filter: 3333333 
Planning time: 0.130 ms 
Execution time: 7891.137 ms 
(8 rows) 
Time: 7891.937 ms 
test=# explain analyze select * from tbl_index where a = 10000001; 
QUERY PLAN                                                           
------------------------------------------------------------------------------------------------------------------------------ 
Index Scan using idx_tbl_index_a_c on tbl_index  (cost=0.43..8.45 rows=1 width=23) (actual time=0.154..0.156 rows=1 loops=1) 
Index Cond: (a = 10000001) 
Planning time: 0.257 ms 
Execution time: 0.206 ms 
(4 rows) 
Time: 1.119 ms

 

7.多字段索引查询条件使用OR

test=# explain analyze select * from tbl_index where a = 10000001 or c = 'haha'; 
QUERY PLAN                                                           
----------------------------------------------------------------------------------------------------------------------------- 
Gather  (cost=1000.00..79399.11 rows=1 width=23) (actual time=7321.821..7323.593 rows=1 loops=1) 
Workers Planned: 2 
Workers Launched: 2 
->  Parallel Seq Scan on tbl_index  (cost=0.00..78399.01 rows=0 width=23) (actual time=7307.413..7307.413 rows=0 loops=3) 
Filter: ((a = 10000001) OR ((c)::text = 'haha'::text)) 
Rows Removed by Filter: 3333333 
Planning time: 0.163 ms 
Execution time: 7324.821 ms 
(8 rows) 
Time: 7325.532 ms 
test=# explain analyze select * from tbl_index where a = 10000001 and c = 'haha'; 
QUERY PLAN                                                           
------------------------------------------------------------------------------------------------------------------------------ 
Index Scan using idx_tbl_index_a_c on tbl_index  (cost=0.43..6.20 rows=1 width=23) (actual time=0.040..0.041 rows=1 loops=1) 
Index Cond: ((a = 10000001) AND ((c)::text = 'haha'::text)) 
Planning time: 0.165 ms 
Execution time: 0.093 ms 
(4 rows) 
Time: 32.904 ms

 

原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/4867.html

(0)
上一篇 2021年7月16日
下一篇 2021年7月16日

相关推荐

发表回复

登录后才能评论