文章目录

  • 什么情况下需要创建索引
  • 如何选择索引类型
  • 如何选择合适的列创建索引
  • 如何更好的使用索引


什么情况下需要创建索引

  • 查询返回的结果集小
    查询返回单一记录或非常小的数据集时（例如OLTP类型查询），使用索引可以优化查询性能。
  • 压缩表
    在压缩过的追加优化表上使用索引，系统只会解压必要的行，从而提升查询性能。

如何选择索引类型

  • 选择性高的列使用B-tree索引
    例如，如果一个表有1000行数据并且一个列中有800个不同的值，则该索引的选择度为0.8，索引的选择性会比较高。唯一索引的选择度总是1.0。
  • 选择度低的列使用位图索引
    例如区分值区间在100至100000之间的列，位图索引表现最好。
  • 表数据量大，数据物理分布有一定有序性，查询条件为<、<=、=、>=或>，过滤效果较好的情况下，使用BRIN索引
    在大数据集的情况下，BRIN索引与B-Tree索引相比，占用的空间极小，性能相同。

如何选择合适的列创建索引

  • 索引在连接中用到的列
    频繁连接的列（例如外键列）上的索引能够提升连接性能，这将让查询优化器有更多可以使用的连接方式。
  • 索引在谓词中频繁使用的列
    频繁地在WHERE子句中被引用的列是索引的首选。
  • 避免在频繁更新的列上建立索引
    在一个频繁更新的列上建立索引会增加该列被更新时所需要的写操作数据量。

如何更好的使用索引

  • 避免创建重叠的索引
    在多列索引中，具有相同前导列的索引冗余。
  • 批量载入前删除索引
    当载入大量数据到一个表中，建议先删除索引并且在数据装载完成后重建这些索引，将会比更新索引更快。
  • 测试并且比较使用索引和不使用索引的查询性能
    只有被索引列的查询性能有提升时才增加索引。
  • 创建完索引，建议对表执行ANALYZE。