数据库系列-PostgreSQL之索引

前言

索引对于我们查找数据有很重要的意义，在大多数情况下，我们寄希望于能够使用索引来提高查询效率。但是有时候，我们却发现某些SQL却并不会使用索引。所以，搞清楚索引是怎么会，是如何被使用的很重要。

索引是什么

索引是表的有组织的一个或多个列值的列表。
索引的思想在于，如果用户只需要表中数据的一个子集，来快速判断哪些行符合要求，而不用每一行的数据都进行判断。从而减少执行查询所需要查看的数据量。

合理创建索引

不是所有的情况字段都适合创建索引。

1. 索引是需要占用存储空间的。如果一个表只有2个字段，而字段A长度是1，字段B长度是9。单独针对B字段创建索引，意味着需要额外的90%的空间来存放B字段索引！近乎等同于全表的存储空间。这意味着磁盘和维护成本比较高。需要谨慎，除非对查询性能有非常大的提升。
2. 索引占用的存储空间越大，意味着将该索引树读取到内存中的成本越大，消耗越大。但读取成本超过一定阈值时，它所带来的效率提升就弥补不了它的损耗。

查询效率与索引

不使用索引的情况

1. 缓存效应。
   当数据库将表中的所有数据缓存到内存中（可能前面的查询导致），后面的查询可能就直接从缓存中查找数据了。而不会到磁盘中获取索引数据。
2. 要查找的数据量足够大
   当查找的数据量与全表数据量相差不多的情况下，会直接走全表扫描。
3. 统计信息
   统计信息，是为了给执行引擎提供数据参考来决定采用什么样的查找方式，提高查找效率。
   例如：如果统计信息中发现某表的数据量非常少，那么就有可能直接走全表扫描。
   所以错误的统计信息，对于查询优化非常致命。
   可以使用Vaccum Analyze手动统计。
4. 需要查找的数据上没有可用索引。
5. 索引带来的性能提升无法弥补其带来的性能损耗。
   数据库从文件中读取索引到内存，也是需要成本的。如果索引不再被认为有足够的选择性来弥补使用它所带来的损耗，那么执行引擎也不会使用索引。

   选择性，是指值是否足够分散，过滤时能够筛选很多数据，提高效率。如果选择性比较低，意味着数据比较集中，查找的数据量比较大，就会走全表扫描。

在PG的执行计划中，如果您发现带有Seq Scan字样，则表明它是整个表进行顺序扫描。而不是走索引。

联合索引

对于PG而言，不推荐(field,field)这种方式来创建联合索引，因为这种方式只能提高正好使用相同的域组合的查询效率。在PG中，可以单独对列进行创建索引。PG可以非常高效地通过位图索引扫描方式组合多个单列索引。

与联合索引相关的查询
覆盖索引，是指查询的列只包含索引列的情况。这种场景就可以只访问索引就可以返回所有数据了。

总结

1. 索引的创建和维护，是需要代价的。
2. 索引的存储成本取决于索引列占行数据的占比。
3. 索引对于查询效率的提升与要查找的数据量有着密切关系。
4. 表的统计信息对执行计划有着至关重要的意义。
5. PG可以很高效地组织多个单列索引来实现联合索引的效果。

PS：参考《PostgreSQL 9X之巅》