四种索引类型解析 · PHP知识总结

> 索引是对数据库表中的一列或者多列的数据进行排序的一种结构，使用索引可快速访问数据表中的特定信息。 > pgsql提供了B-tree、Hash、Gin等索引类型，每种索引类型因采用不同的算法而适用于不同的查询场景。在未指定索引类型时，CREATE INDEX命令将创建B-tree 索引。 **基本术语：** * 元组 - tuple，指的是行(包括heap tuple, index tuple等，都可以叫元组)。 * tid, ctid - 指行号(block\_number, item pointer)，包括HEAP表的BLOCK NUMBER，以及在BLOCK中指向行的item pointer。 * 键值 - 指索引结构中的KEY * 基表 - 创建索引的表 **索引的扫描方式：** 1. **Indexscan：** 根据查询索引找到匹配记录的[ctid]()，再通过ctid去查堆表把相应的数据读出来 2. **Bitmapscan：** 通过索引找到匹配记录的ctid集合，在内存中建一个bitmap对ctid做集合运算和排序等，最后再根据bitmap中的数据文件把对应数据读取出来。 3. **Seq Scan：** 顺序扫描也叫做全表扫描，数据库的一种扫描方式，跟索引没啥关系，当语句是这种扫描方式时，说明该查询语句并没有走索引。 4. **Index Only Scan：** 这种索引方式说明索引字段中包含了所有返回字段，对vm文件中全为可见的数据块，不查堆表直接返回索引中的值 > [VM文件](http://docs.linchunyu.top/2280082#span_stylefontsize15pxVMspan_154)是可见性映射文件，如果存在vm文件，则表示该数据块没有需要清理的行，即该表做了vacuum操作。[vaccum命令](http://docs.linchunyu.top/2280082#span_stylefontsize15pxvacuumspan_198)时用来对表进行操作，回收已经删除元祖占据的存储空间 **索引分类：** * **从索引的类型上区分：** * 唯一索引：唯一索引不允许任何重复的值插入到表。格式为：`CREATE UNIQUE INDEX index_name on table_name (column_name)` * 单列索引：只基于表的一个列上创建的索引。格式为： `CREATE INDEX index_name ON table_name (column_name);` * 组合索引：基于表的多列上创建的索引。格式为：`CREATE INDEX index_name ON table_name (column1_name, column2_name)` * 表达式索引：字段进行某种运算之后的结果创建索引、格式为：`CREATE INDEX index_name ON table_name(expression|column_name)` * **从索引的实现方法上区分：** * B-tree：创建索引时未指定类型时，默认创建B-tree索引 * Hash：此索引类型只能处理简单的等值比较 * Gin：通用倒排索引，可以与pg_trgm扩展配合使用以实现字符串匹配的全文检索，即全模糊搜索 * Gist：通用的搜索树(Generalized Search Tree)。它是一种平衡树结构的访问方法，在系统中作为一个基本模版，可以使用它实现任意索引模式。B-trees, R-trees和许多其它的索引模式都可以用GiST实现 <table> <thead> <th width="10%">索引类型</th> <th width="20%">说明</th> <th width="20%" >适用场景</th> <th>特性</th> </thead> <tbody> <tr> <td>B-tree</td> <td>默认类型。使用btree索引，where条件必须包含第一个索引列</td> <td>B-tree索引适合用于存储排序的数据，并支持=，>,>=，<,<=，以及between，in，is null等操作符和以字符串开头的模糊查询(前模糊)</td> <td>1、在PostgreSQL13中，Btree索引新增了Deduplication特性，使得PG数据库有了新的方式去处理重复的索引键值，这大大减小了btree索引所占用的空间，提升了索引扫描的性能，deduplication涉及到底层索引存储结构的变更。在13版本之前，每一个重复的数据都会占用索引的一个叶子元组leaf，这些重复的key值在索引页面中重复存储，带来很大的空间浪费。通过deduplicate_items 参数开启（默认开启）<br>2、在PostgreSQL当前支持的索引类型中，只有B-tree可以产生排序的输出，当ORDER BY与LIMIT n组合：显式排序将必须处理所有数据以识别前n行，但如果存在与ORDER BY匹配的索引，则可以直接检索前n行，而不扫描其余部分。升序默认null值放在最后，可以使用NULLS FIRST和/或NULLS LAST选项来进行调整。<br>3、支持多列索引，最多可以指定32列，使用最左匹配原则</td> </tr> <tr> <td>Hash</td> <td>此索引类型只能处理简单的等值比较</td> <td>= 操作符</td> <td>1、由于hash函数没有特定的排序规则，所以一般的hash索引只支持等值查<br>2、hash索引也不会处理null值，所以不会标记null值<br>3、hash索引不存储索引键，只存储hash码，所以不会 index-only扫描，也不支持多列创建hash索引<br>4、从10版本开始，可以通过pageinspect插件查看hash索引的内部情况 `create extension pageinspect`</td> </tr> <tr> <td>Gin</td> <td>通用倒排索引，是一个存储对(key, posting list)集合的索引结构，其中key是一个键值，而posting list 是一组出现过key的位置</td> <td>1、多值类型(数组、全文检索、枚举、网络地址类型)：包含、相交<br>2、JSON类型<br> 3、普通类型(通过btree_gin 插件支持)：与B-Tree类似<br>4、字符串(通过pg_trgm 插件支持)：模糊查询、相似查询<br>5、多列：任意列组合查询</td> <td>1、在表中的每一个属性，在建立索引时，都可能会被解析为多个键值<br>2、通过这种索引结构可以快速的查找到包含指定关键字的元组，因此GIN索引特别适用于多值类型的元素搜索，比如支持全文搜索，数组中元素的搜索<br>3、Gin索引创建耗时比btree索引长约10倍，对频繁更新的表字段不建议使用</td> </tr> <tr> <td>Gist</d> <td>通用搜索树，一种平衡树结构的访问方法,在系统中作为一个基本模版,可以使用它实现任意索引模式。B-trees, R-trees和许多其它的索引模式都可以用GiST实现</td> <td>1、空间类型：方位（上、下、左、右），空间关系（相交、包含），空间距离排序(KNN)<br>2、范围数据：=, &&, , >, -|-, &<br>3、普通类型：与B-Tree类似，增加空间类型类似操作符<br>4、数组类型<br>5、多列：任意列组合查询</td> <td>1、可通过btree_gist扩展，支持多种数据类型的 B 树等效功能<br>2、可通过cube扩展，支持多维立方体的索引<br>3、可通过hstore扩展，支持存储键值对模块的索引<br>4、可通过intarray扩展，支持一维 int4 值数组的 RD 树的索引<br>5、可通过ltree扩展，支持树状结构的索引<br>6、可通过pg_trgm扩展，支持全文检索，模糊搜索<br>7、可通过seg扩展，支持“float ranges”的索引</td> </tr> </tbody> </table> **Gin索引和Gist索引的区别：** 1. GIN索引查找比GiST索引更快、更精准 2. GIN索引的建立和更新比GIST索引耗时更长、占用空间更大 3. GIN索引对静态数据是最好的，因为查找速度很快。对于动态数据， GiST索引更新比较快。具体而言，GiST索引非常适合动态数据