Postgresql杂谈 21——宽索引和Index—Only Scans、HEAP—Only Tuples技术

       究竟什么样的索引才算是好索引（本文中的索引是指Oracle、SQLServer、Postgresql使用的基于BTree结构的普通索引，并非指Postgrsql GIN、Gist此类特色索引）？

一、针对索引数据顺序性的优化

      我们知道BTree索引的数据结构本质是一个多路平衡查找树，这种结构也就意味着，对数据的查找效率的和数据顺序紧密相关。比如对于一个多列索引（A\B\C），首先保证索引按照A排序，在A相等的前提下按照B再进行排序，接着在B相等的前提下，又按照C进行排序。这种有序性，是我们进行索引优化重要的参考依据：SQL语句的查询条件只要能够按照索引列顺序，确定一个很窄的索引片，那么就说明这个索引的查询效率很高，比如最极端的例子，我们的查询条件是A=1 AND B=2 AND C=3，那么通过这个条件我们最少可以确定只有一条索引行的索引片。所以，索引的优化原则之一可以归纳为：

• 按照索引顺序从左到右分析查询条件，如果字段在where条件中是简单查询（等值查询）则会走索引，但是一旦字段的查询条件是复杂查询（范围查询、离散查询）则索引中断。

二、针对索引索引行的优化

      另一方面，索引的本质也是存储在磁盘上的数据，只不过相对于普通的数据表行来说，索引行只是由索引列组成、比较精简的数据行。这句话告诉我们索引优化的另外一个方向：

• select语句中的投影字段，可以做到全部是索引字段的子集，这样就可以避免使用索引查询时，查询数据行。

     这就是所谓的宽索引。

三、三星索引

      在【美】Tapio Lahdenmaki 和【美】Michael Leach联合所著的《数据库索引和优化》一书中将索引分成了三个星级：

• 一星索引：where条件中所有的等值查询的列，作为了索引的最开头的列，以任意顺序都可以。比如查询条件是Where A=1 and B=2 and C between (10，100) and D<>5，在设计索引时，一星索引可以是A\B开头也可以是B\A开头。
• 二星索引：将Order by的列加入到索引中，但是不要改变建立一星索引的顺序，忽略一星索引中的列。比如order by A,C，则索引中加入C，变成A\B\C或者B\A\C，这样就避免了针对A和C再次排序，因为索引天生有序。
• 三星索引：将查询语句中剩余的列添加到索引中去，建立宽索引。列在索引中添加的顺序对查询语句的性能没有影响，但是将易变的列放到最后能够降低更新的成本。比如select D,E，而E要经常用update更新，那么最终的索引被设计成了A\B\C\D\E或者B\A\C\D\E。

四、Index—Only Scans

      在上文中提到，建立宽索引的目的是避免查询时查了索引一边，还要到表上再去查一次。这个技术在Postgresql中叫做Index—Only Scans。为了解释该技术，我们建立测试表，在实例的基础之上进行验证。首先建立test表：

stock_analysis_data=# create table test (id int primary key,content text);
CREATE TABLE

      然后插入测试数据：

stock_analysis_data=# insert into test select t.d,'content'||t.d  from generate_series(1,10000) as t(d);
INSERT 0 10000

      执行如下的查询语句，并在explain中查看：

stock_analysis_data=# explain (analyze) select id from  test where id>10 and id<30;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using test_pkey on test  (cost=0.29..8.67 rows=19 width=4) (actual time=0.028..0.044 rows=19 loops=1)
   Index Cond: ((id > 10) AND (id < 30))
   Heap Fetches: 19
 Planning Time: 0.317 ms
 Execution Time: 0.079 ms
(5 rows)

      explain中的Index Only Scan就是我们所说的Index—Only Scans技术，也就是在宽索引种只查询索引，但是实际上达到这个效果了吗？Heap Fetches: 19，说明查询到的这19条数据都是从数据库表中fetch的，看来Index Only Scan名不符实？这就需要再次引入一个概念——可见性映射表（Visibilty Map），可见性映射表记录了一个数据块是不是对全部的事务可见，只有一个数据块对所有事务可见了，使用宽索引时才会达到Index Only Scan的效果。

      为什么要这么做？道理也很简单，就是Postgresql本身更新数据是采用多版本更新的方式的，就是说更新某行数据时，会直接插入新行，而不是在原来行进行更新。或者删除某行时不是直接删除，而是在行上标记。不管是哪种场景，总会出现：有旧数据行隐藏在表中对其它事务不可见，而且索引指向旧数据行的情况。所以，即使是使用了宽索引，也需要验证索引指向数据的可见性。

       为了提高验证效率，可见性映射表的概念被提了出来，它用来记录表中的数据块是不是对所有事务可见，如果是可见的，才会做到不再去检查数据行。下面，我们来看test表中数据行对全部事务的可见性，查询pg_class表可以看到这个信息：

stock_analysis_data=# select relname,relpages,relallvisible from pg_class where relname='test';
 relname | relpages | relallvisible 
---------+----------+---------------
 test    |       55 |             0

       relallvisible为0表明表中没有数据块对所有事务可见，这就是我们建立了宽索引，仍然需要查询表中数据行的原因。要解决这一问题，需要执行vacumm命令，执行完成之后，再去查看pg_class表：

stock_analysis_data=# select relname,relpages,relallvisible from pg_class where relname='test';
 relname | relpages | relallvisible 
---------+----------+---------------
 test    |       55 |            55

       现在55个数据块对所有的事务可见，再来执行之前的查询语句：

stock_analysis_data=# explain (analyze) select id from  test where id>10 and id<30;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using test_pkey on test  (cost=0.29..4.67 rows=19 width=4) (actual time=0.008..0.024 rows=19 loops=1)
   Index Cond: ((id > 10) AND (id < 30))
   Heap Fetches: 0
 Planning Time: 0.177 ms
 Execution Time: 0.055 ms

      “Heap Fetches: 0”说明我们的优化有了效果，真正做到了Index Only Scan。

五、HEAP—Only Tuples

       在上文中，可能有朋友会有疑问：当在Postgresql中更新或者删除数据时，不会同时更新索引吗，为什么会出现索引指向旧的数据行的情况？其实，为了提高更新或者删除效率，Postgresql并不会马上去更新索引。但是为了保证用索引能够找到新的数据行，它采用了一种叫做HEAP—Only Tuples的技术，在update时，在旧的数据行建立一个指针，指向新的数据行。这样就既保证了通过索引找到了新的数据行，也提高了update的效率。

 

       HEAP—Only Tuples的技术，也称作HOT技术，它有一个限制，就是行之间的指针只能在同一个数据块中，不能跨数据块。所以如果想要使用HOT技术，就需要为数据块预留较大的空闲空间，这就要求我们在创建表时设置一个较小的填充因子：

stock_analysis_data=# create table test (id int primary key,content text) with (fillfactor=50);
CREATE TABLE

      或者修改表的填充因子：

stock_analysis_data=# alter table test set (fillfactor=50);

ALTER TABLE

六、总结

1. 进行BTree索引优化时，可以根据索引有序性的特点，将查询条件中的等值查询的列作为索引的头几个列。
2. 建立宽索引：查询条件、查询字段和排序字段全部在索引列之内，可以避免查询时再去查表，提高查询效率。
3. Postgresql使用了可见性映射表（Visibilty Map）来记录每个表的数据块是不是对所有的事务可见，如果是，则Index—Only Scans技术才会有效，使用宽索引时才会做到只查索引，不查表。
4. HEAP—Only Tuples的技术，也叫做HOT技术，是指在update时，在旧的数据行建立一个指针，指向新的数据行。这样就既保证了通过索引找到了新的数据行，也做到了在更新时不会修改索引，提高查询效率，而后Postgresql在自动vacumm或者手动vacumm会删除旧数据行并更新索引。