ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL:INDEX

PostgreSQL:INDEX

原创 PostgreSQL 作者:Ryan_Bai 时间:2020-12-14 18:07:16 0 删除 编辑

索引是加速搜索引擎检索数据的一种特殊表查询。简单地说,索引是一个指向表中数据的指针。

分类

  • B-tree:最常用的索引,B-tree 索引适合处理等值查询和范围查询

  • Hash:只能处理简单的等只查询

  • GiST:不是一种单独的索引类型,而是一种架构,可以在这种架构上实现很多不同的索引策略。GiST 索引定义的特定操作符可以用于特定索引策略。PostgreSQL 的标准发布中包含了用于二维几何数据类型的 GiST 操作符类。比如,一个图形包含另一个图形的操作符“@>”,一个图形在另一个图形的左边没有重叠的操作符“<<”,等等

  • SP-GiST:SP-GiST 是“space-partitioned GiST”的缩写,即空间分区 GiST 索引。主要是通过一些新的索引算法提高 GiST 索引再某个情况下的性能。

  • GIN:反转索引,它可以处理包含多个键的值,如数组等。与 GiST 类似,GIN 支持用户定义的索引策略,可通过定义 GIN 索引的特定操作符类型实现不同的功能。比如,它支持包含操作符“@>”、被包含操作符“<@”、相等操作符“=”、重叠操作符“&&”,等等。

CREATE

语法

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]
  • CONCURRENTLY:并发创建索引。当该选项被使用时,PostgreSQL 会执行表的两次扫描,因此该方法需要更长一些的时间来创建索引

单列索引

单列索引是一个只基于表的一个列上创建的索引,基本语法如下:

CREATE INDEX index_name ON table_name (column_name);

组合索引

组合索引是基于表的多列上创建的索引,基本语法如下:

CREATE INDEX index_name ON table_name (column1_name, column2_name);

不管是单列索引还是组合索引,该索引必须是在 WHERE 子句的过滤条件中使用非常频繁的列。

唯一索引

使用唯一索引不仅是为了性能,同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下:

CREATE UNIQUE INDEX index_name ON table_name (column_name);

局部索引

局部索引是在表的子集上构建的索引;子集由一个条件表达式上定义。索引只包含满足条件的行。基础语法如下:

CREATE INDEX index_name ON table_name (conditional_expression);

隐式索引

隐式索引是在创建对象时,由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

查看

  1. \d:查看表下的索引

    testdb=# \d company
                      Table "public.company"
     Column  |     Type      | Collation | Nullable | Default 
    ---------+---------------+-----------+----------+---------
     id      | integer       |           | not null | 
     name    | text          |           | not null | 
     age     | integer       |           | not null | 
     address | character(50) |           |          | 
     salary  | real          |           |          | 
    Indexes:
        "company_pkey" PRIMARY KEY, btree (id)
        "salary_index" btree (salary)
  2. \di:查看所有索引

    runoobdb=# \di
                        List of relations
     Schema |      Name       | Type  |  Owner   |   Table    
    --------+-----------------+-------+----------+------------
     public | company_pkey    | index | postgres | company
     public | department_pkey | index | postgres | department
     public | salary_index    | index | postgres | company
    (3 rows)
  3. SELECT

    select * from pg_indexes where tablename ='table_name';

删除索引

DROP INDEX index_name;

修改索引

ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name
ALTER INDEX name SET ( storage_parameter = value [, ... ] )
ALTER INDEX name RESET ( storage_parameter [, ... ] )

准则

  • 创建

    • 主键自动建立唯一索引;

    • 频繁作为查询条件的字段应该创建索引;

    • 查询中与其他表有关联的字段,例如外键关系;

    • 高并发的情况下一般选择复合索引;

    • 查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找);

    • 查询中统计或者分组的字段;

    • 存储空间固定的字段更适合选作索引的关键字,例:与 text 类型的字段相比, char 类型的字段较为适合选作索引关键字

  • 不创建

    • 索引不应该使用在较小的表上。

    • 索引不应该使用在有频繁的大批量的更新或插入操作的表上。

    • 索引不应该使用在含有大量的 NULL 值的列上。

    • 索引不应该使用在频繁操作的列上。

    • 经常增删改的表,不需要创建索引;

    • 表中包含大量重复数据,不需要创建索引,例如性别字段,只有男女,不适合建立索引;

常见失效原因

  • 没有查询条件,或者查询条件没有建立索引

  • 在查询条件上没有使用引导列

  • 查询的数量是大表的大部分,应该是 30% 以上

  • 使用内部函数导致索引失效,例:

    select * from test where round(id)=1
  • 表记录较少

  • 隐式转换导致索引失效,例如:表的字段 tu_mdn 定义为 varchar2(20),但在查询时把该字段作为 number 类型以 where 条件传给数据库:

    • 错误的例子:

      select * from test where tu_mdn=13333333333;
    • 正确的例子:

      select * from test where tu_mdn='13333333333';
  • 索引列进行运算导致索引失效,运算包括(+,-,*,/,! 等),例:

    select * from test where round(id)=1
  • like "%_" 百分号在前

  • 单独引用复合索引里非第一位置的索引列

  • B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走

  • 并发创建索引过程中,如果被强行取消,可能会留下一个无效的索引,这个索引仍然会导致更新变慢。如果所创建的是一个唯一索引,这个无效的索引还会导致插入重复值失败。手工删除重建即可修复

在线重新创建索引

CREATE INDEX CONCURRENTLY ON <index_name> USING btree(id);

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31490526/viewspace-2742169/,如需转载,请注明出处,否则将追究法律责任。

上一篇: PostgreSQL:表空间
请登录后发表评论 登录
全部评论
Oracle ACE Associate; OCMU 用户组成员; Oracle 10g OCE、OCA、OCP; Oracle 11g OCP、OCM; MySQL 5.6 OCP; Oracle 11g OCP讲师; PostgreSQL PGCE 获得者;

注册时间:2017-09-18

  • 博文量
    226
  • 访问量
    261363