PostgreSql索引的使用3--多字段索引

             多字段索引就是索引中的字段不是一个，其创建的方式与单字段索引的语法一致。

如：create index idx_name on tanle_name(column_name1,column_name2);

     多字段索引只适合B-tree, GiST 和 GIN三种索引方式，并且字段是有限制的，字段个数最大为32个。这边只讨论B_tree类型。

     一个多字段索引创建后，只要在在子查询中用到了索引字段的任意子集，原则上都是会走索引的(原则上的意思是走全表扫描的成本小于走索引等情况下)。

     创建表mytest9:

  Create table mytest9(

id int,

name_1 varchar(30),

name_2 varchar(30),

name_3 varchar(30)

);

 

插入数据：

Insert into mytest9

  Select generate_series(1,10000),

'name_1'||generate_series(1,10000),'name_2'||generate_series(1,10000),

'name_3'||generate_series(1,10000);

在表中插入数据时系统会更新索引，所以当需要大量插入数据时可以先将索引删除掉，再插入数据，再重新创建索引，这是一个小技巧。

创建索引：

Create index idx_mytest9_id_name1 on mytest9 on(id,name_1,name_2);

 

最好就是执行vacuum analyze mytest9;这样会使mytest9的统计信息最新。

 

执行SQL1：

Explain analyze Select * from mytest9;

执行计划：

"QUERY PLAN"

"  Seq Scan onmytest9 (cost=10000000000.00..10000000184.00 rows=10000 width=34)"

因为没有查询条件，所以走的是全表扫描(Seq Scan就是按照顺序扫描，即全表扫描)。

 

执行SQL2：

explain select * from mytest9 where id = 10 andname_1 = 'name_11' and name_2 = 'name_21';

执行计划：

"QUERY PLAN"

"Index Scan using idx_mytest9_id_name onmytest9  (cost=0.29..8.31 rows=1width=34)"

" Index Cond: ((id = 10) AND ((name_1)::text = 'name_11'::text) AND ((name_2)::text= 'name_21'::text))"

从执行计划可以看出确实走了索引，并且是刚刚创建的idx_mytest9_id_name1索引，从SQl语句就很明显看出查询条件完全符合走索引的条件，不仅仅是字段匹配而且是全部都是等值条件。B-tree索引支持的操作符在：=,<=,>=,<,>范围内。

执行SQL3：

explain analyze select * from mytest9 where id = 10 and name_1 >= 'name_11' andname_2 < 'name_21';

执行计划：

"QUERY PLAN"

"Index Scan using idx_mytest9_id_name1 onmytest9  (cost=0.29..8.31 rows=1width=34) (actual time=0.011..0.011 rows=0 loops=1)"

" Index Cond: ((id = 10) AND ((name_1)::text >= 'name_11'::text) AND((name_2)::text < 'name_21'::text))"

"Total runtime: 0.025 ms"

从这个执行计划中可以看出cost(成本)是一样的，执行计划也是一样的，只是SQL3的子查询条件存在索引字段的范围查找，这也验证了B-tree索引支持的操作符在：=,<=,>=,<,>范围内。

需要说明的是，虽然执行计划中提到SQL的执行使用了索引扫描，但是这里不是唯一性索引，因此在执行过程中需要扫描整个索引，扫描完以后，再去原表中找到相关的数据再取出来，这里面是有两个步骤的，除非需要检索出行的字段在索引字段范围内。如：

Select id from mytest9 where id = 10 and name_1>= 'name_11' and name_2 < 'name_21';

执行SQL4：

Explain analyze select * from mytest9  where id = 10 or name_1 = 'name_11' or name_2= 'name21';

执行计划：

"QUERY PLAN"

"Seq Scan on mytest9  (cost=0.00..259.00 rows=3 width=34) (actualtime=0.022..3.095 rows=2 loops=1)"

" Filter: ((id = 10) OR ((name_1)::text = 'name_11'::text) OR((name_2)::text = 'name21'::text))"

" Rows Removed by Filter: 9998"

"Total runtime: 3.122 ms"

从执行计划中可以看出当查询条件用“or”连接后并没有走索引，而是走的全表扫描，cost=259。这中情况下在PostgreSql中不适用索引那么原因就是走索引扫描的cost比较大(相对全表扫描)。下面就强制让系统执行索引扫描。

执行SQL5：

首先执行set enable_seqscan to false;将全表扫描关闭

再执行SQl4的语句

Explain analyze select * from mytest9  where id = 10 or name_1 = 'name_11' or name_2= 'name21';

执行计划：

"QUERY PLAN"

"Bitmap Heap Scan on mytest9  (cost=650.86..661.22 rows=3 width=34) (actualtime=0.714..0.714 rows=2 loops=1)"

" Recheck Cond: ((id = 10) OR ((name_1)::text = 'name_11'::text) OR((name_2)::text = 'name21'::text))"

" ->  BitmapOr  (cost=650.86..650.86 rows=3 width=0) (actualtime=0.709..0.709 rows=0 loops=1)"

"       ->  Bitmap Index Scan onidx_mytest9_id_name1  (cost=0.00..4.29rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)"

"              Index Cond: (id = 10)"

"       ->  Bitmap Index Scan onidx_mytest9_id_name1  (cost=0.00..323.29rows=1 width=0) (actual time=0.301..0.301 rows=1 loops=1)"

"              Index Cond: ((name_1)::text ='name_11'::text)"

"       ->  Bitmap Index Scan onidx_mytest9_id_name1  (cost=0.00..323.29rows=1 width=0) (actual time=0.395..0.395 rows=0 loops=1)"

"              Index Cond: ((name_2)::text ='name21'::text)"

"Total runtime: 0.747 ms"

每一个”->”为一个节点，一般执行顺序是从上到下，从里到外的顺序执行每个节点。

从总的cost值可以看出全表扫描的成本低于索引扫描。

这里原因是每一个查询条件都要对索引进行全扫描，需要执行三次，并且扫描完后还得去原表中取数据，而全表扫描就一次扫描全表将所有事情都处理好了。在IO的处理当中，一次IO量的大小不是影响性能的关键，关键是IO吞吐的次数，次数越多性能越差。

执行SQl6：

explain analyze select * from mytest9 where id>=10 and name_1 = 'name_11' and name_2 = 'name_21';

执行计划：

"QUERY PLAN"

"Seq Scan on mytest9  (cost=0.00..259.00 rows=1 width=34) (actualtime=1.453..1.453 rows=0 loops=1)"

" Filter: ((id >= 10) AND ((name_1)::text = 'name_11'::text) AND((name_2)::text = 'name_21'::text))"

" Rows Removed by Filter: 10000"

"Total runtime: 1.470 ms"

这里也没有使用索引，顾名思义索引扫描的成本大于全表扫描，这里需要说明的是不管其他字段是否为等值条件，只要索引的前导字段非等值，那么就不会走索引，反过来如果索引的前导字段是等值条件，那么就会走索引扫描。当然也是有例外的情况，当一个表只有一个页，或者操作系统的一次IO吞吐超过表的大小的话也是不会去做索引扫描的。

执行SQl7：

Select * from mytest8 where id = 8;

Mytest8表中只有10条数据，在pg_class中对应的relpages=1

执行计划：

"QUERY PLAN"

"Seq Scan on mytest8  (cost=0.00..1.13 rows=1 width=100)"

"  Filter:(id = 1)"

因为表比较小，实际甚至小于8K，PostgreSql中最小的IO单位是8K，一个relpages为8K。此时需要关闭全表扫描才能走索引扫描。