Postger SQL索引

一、概述

• 使用索引可以快速访问数据哭表中的特定信息。但是索引也会增加数据库系统的日常管理负担。所以要合理的利用索引。
• PostgerSQL 提供了丰富的索引类型，除支持常规的数值类型、字符串类型的索引外，还支持时序、空间、json等类型的索引。

二、索引优缺点、分类以及设计原则

• 使用索引的优缺点
  • 优点
    • 通过创建唯一索引，可以保证数据的唯一性。
    • 提高数据记录的查询速度。
    • 加快表与表之间的连接速度。
  • 缺点
    • 需要占用额外的物理空间。
    • 如果表中的数据发生变化，则索引也需要同步进行更新，对数据库的性能有一定影响。
• 分类
  • PostgerSQL提供了B-tree、Hash、Gist、SP-Gist、GIN、BRIN等多种索引类型，每种索引类型使用不同的算法来适应不同类型的查询。默认情况使用B-tree索引。
    • B-tree
      • B-tree索引使用B-tree数据结构存储索引数据，可用于处理等值查询和范围查询，包括：大于、大于等于、小于、小于等于等运算符，以及between、in、is nul、is not nul等条件。
      • B-tree索引可用于模式匹配查询，如“字段 like ‘值%’，”但不能适用于“字段 like ‘%值’之类的后缀模糊匹配查询。
      • B-tree索引还可以用于查询结果集排序，如 order by 排序。
    • Hash索引
      • Hash索引基于哈希表实现，只能用于等值查询。Hash索引根据每一行数据的索引字段计算哈希码，并维护哈希码、记录指针对应关系。对于哈希码相同的数据来说，可以采用链表来解决冲突。哈希索引的查询速度很快。
    • GiST
      • GiST时一种平衡的树形结构访问方法，可作为一种基础模版来实现任意索引模式。B-tree索引和许多其他索引模式都可以用Gi ST索引来实现。
      • GiST索引适用于多维数据类型和集合数据类型。GiST多列索引支持在查询条件中包含索引字段的子集。
    • SP-GiST
      • SP-GiST与GiST索引类似，可作为一种基础模版来实现多种搜索方法。SP-GiST索引主要实现非平衡的基于硬盘的数据结构，如四叉树、k-d树和radix树。
    • GIN
      • G IN索引是一种通用倒排索引，可以处理包含多个键值。用它来搜索全文或者json键值的效率最高。
    • BRIN
      • BRIN表示块范围索引。BRIN索引存储连续相邻的数据块统计信息，可以大大缩小索引的占用空间。对于数据量比较大的表来说，BRIN索引比B-tree索引插入数据的速度要快，两者的范围查询效率相当。
• 设计索引的原则
  • 在经常用的查询字段上创建索引。
  • 在经常用于连接的字段上创建索引，以加快连接速度。
  • 在经常需要根据范围进行查询的列上创建索引。
  • 在经常需要排序的列上创建索引，以加快排序。
  • 不应该在查询少的列上创建索引。
  • 对经常更新的表需要避免对其建立过多的索引。
  • 不应该对数据量比较小的表创建索引。
  • 不应该在数据取值区分度很小的列创建索引，如：性别字段只有男女两个取值。