文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

postgresql——索引

2024-04-02 19:55

关注

postgresql的索引


postgresql提供的索引类型有:B-tree、hash、gist和gin。大多情况下,B-tree索引比较常用,用户可以使用create index命令创建一个B-tree索引。


1、B-tree索引:

   B-tree适合处理那些能够按顺序存储的数据,比如对于一些字段涉及使用:< ,<= ,= ,>= 或 >操作符之一进行比较的时候,可以建立一个索引。

也可以使用B-tree索引搜索来实现与这些运算符的组合相同的构造,如BETWEEN和IN。此外,索引列上的IS NULL或IS NOT NULL条件可以与B-tree索引一起使用。

  对于涉及模式匹配运算符LIKE的查询,优化器还可以使用B-tree索引,如果模式是常量,并且锚定到字符串的开头,例如col LIKE 'foo%'或 col?'^ foo',但不能是col LIKE'%bar'。但是,如果您的数据库不使用C语言环境,则需要使用特殊的运算符类创建索引,以支持对模式匹配查询的索引;见下文第11.9节。也可以对 ILIKE和?*使用B-tree索引,但只有当模式以非字母字符(即不受大小写转换影响的字符)开始时才可以。


2、hash索引:

  hash索引只能处理简单的等于比较。当一个索引的列涉及使用=操作符进行比较的时候,查询规划器会考虑使用hash索引。

Hash索引操作目前不记录WAL-log,所以如果有没有写入的更改,Hash索引可能需要在数据库崩溃后用REINDEX重建。此外,在初始基本备份之后,不会通过流式或基于文件的复制来复制Hash索引的更改,因此它们对随后使用它们的查询给出错误的答案。由于这些原因,目前不鼓励使用Hash索引。


3、gist索引:

   gist索引不是单独一种索引类型,而是一种架构,可以在这种架构上实现很多不同的索引策略。因此,可以使用gist索引的特定操作符类型高度依赖于索引策略(操作符类 )

GiST索引不是一种单一的索引,而是可以实现许多不同索引策略的基础设施。因此,可以使用GiST索引的特定运算符根据索引策略(运算符类)而变化。


4、GIN索引

   GIN索引是反转索引,可以处理包含多个键的值(比如数组)。与gist类似,gin支持用户定义的索引策略,可以使用GIN索引的特定操作符类型根据索引策略的不同而不同 。



索引的设计原则:

①:索引并非越多越好。如果一个表中有大量的索引,那么不仅会占用大量磁盘空间,还会影响:insert、delete、update等语句的性能,因为更改表中的数据时,索引也会进行调整和更新。

②:避免对经常更新的表进行过多索引,并且索引中的列要尽可能少。对经常用于查询的字段应该创建索引,但要避免添加不必要的字段。

③:数据量小的表最好不要使用索引。数据较少时,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。

④:在条件表达式中经常用到的不同值较多的列上建立索引,在不同值少的列上不要建立索引。

⑤:当唯一性是某种数据本身的特征时,指定唯一索引。使用唯一索引能够确保定义的列的数据完整性,提高查询速度。

⑥:在频繁进行排序或分组(进行group by或order by操作)的列上建立索引。如果待排序的列有多个,可以在这些列上建立组合索引。



---常见操作:(注意:默认创建的是B-tree索引)


基本语法:

create [unique |fulltext |spatial]  index  index_name  on  table_name  (col_name[length],....)  [ ASC | DESC ]


1、创建普通索引:B-tree索引

create index idx_contacts_name on contacts(name);


--创建唯一索引:

create  unique index idx_emp  on  emp(id);


--创建组合索引:

create  index  idx_emp  on  emp(id,name); 



2、数组索引

create index idx_contacts_phone on contacts using gin(phone);


注:phone在contacts表中是一个数组类型


3、降序索引

create index idx_contacts_name on contacts(name desc);


4、指定存储参数

create index idx_contacts_name on contacts(name) with(fillfactor=50);


注:fillfactor是常用的存储参数


5、指定空值排在前面

create index idx_contacts_name on contacts(name desc nulls first);


6、避免创建索引的长时间阻塞,可以在index关键字后面增加concurrently关键字,可以减少索引的阻塞时间

create index concurrently idx_contacts_name on contacts(name desc);


注意,重建索引时不支持concurrently ,可以新建一个索引,然后删除旧索引,另外并发索引被强制取消,可能会留下无效索引,这个索引将会导致更新变慢,如果是唯一索引,还会导致插入重复值失败。


7、修改索引


索引重命名: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(storeage_parameter[,...])


8、删除索引

drop index if exists idx_emp;


8、cascade会把索引和依赖索引的对象全部删除


阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯