pgsql常用索引简写

news/2024/5/26 20:31:03/文章来源:https://blog.csdn.net/liu289747235/article/details/136654509

文章来源:互联网博客文章,后续有时间再来细化整理。

在数据库查询中,合理的使用索引,可以极大提升数据库查询效率,充分利用系统资源。这个随着数据量的增加得到提升,越大越明显,也和业务线有关,越是读多写少的业务体现越明显。

索引优点:

  • 唯一索引保证唯一性
  • 加快数据的检索速度(单表查询、联合查询及分组排序等等)

索引缺点:

  • 创建索引和维护索引要耗费时间(创建、更新、删除都需要维护)
  • 索引需要占物理空间(物理空间包含内存和磁盘,这个看索引大小)

1 BTREE索引

CREATE INDEX默认使用BTREE索引,适合按照顺序存储的数据进行比较查询和范围查询。

查询优化器会优先考虑使用BTREE索引:

  • <,<=,=,>,>=
  • 以及这些操作的组合,比如between and,也可以使用BTREE。
  • 在索引列上的IS NULL 或者IS NOT NULL也可以使用BTREE。
  • BTREE索引也可以用于模糊查询,但是仅限字符串开头是常量的情况下,比如 name LIKE ‘Jason%’,或者name ~ ’^Jason’。但是name LIKE ‘%Jason’是不能用的。
  • Min/Max聚集操作也可使用BTREE索引。
  • 其实在merge join以及order by中,可以通过使用BTREE索引的有序性来减少sort带来的代价
create index on t1(id);

2 Hash索引

Hash索引是通过比较hash值来查找定位,如果hash索引列的数据重复度比较高,容易产生严重的hash冲突,从而降低查询效率,因此这种情况下,不适合hash索引。

CREATE INDEX idx_name ON table_name USING HASH (column_name);

3 GiST索引

不是独立的索引类型,是一种架构或者索引模板,是一棵平衡二叉树。适用于多维数据类型和集合数据类型。

适合业务:

  • 几何类型,支持位置搜索(包含、相交、在上下左右等),按距离排序。
  • 范围类型,支持位置搜索(包含、相交、在左右等)。
  • IP类型,支持位置搜索(包含、相交、在左右等)。
  • 空间类型(PostGIS),支持位置搜索(包含、相交、在上下左右等),按距离排序。
  • 标量类型,支持按距离排序。

相比Btree缺点:

  • GiST跟Btree索引相比,索引创建耗时较长,占用空间也比较大。

相比Btree有点:

  • BTREE组合索引(a, b),如果where条件中只有b,则无法使用索引。此时,GiST可以解决这种情况。
create index idx_t3_gist on t3 using gist(a,b);

条件分析: 


root=# explain select * from t3 where b = '2022-11-18 17:50:29.245683';QUERY PLAN
-------------------------------------------------------------------------------Index Scan using idx_t3_gist on t3  (cost=0.28..8.30 rows=1 width=49)Index Cond: (b = '2022-11-18 17:50:29.245683'::timestamp without time zone)
(2 rows)

4 SP-GiST索引

和GiST类似,但是是一棵不平衡树,支持多维和海量数据,把空间分割成互不相交的部分。SP-GiST适用于空间可以递归分割成不相交区域的结构,包括四叉树、k-D树和基数树。

create index on sites using spgist(url);

5 GIN索引

gin是倒排索引(es中字段默认会创建一个倒排索引),是一个存储对(key,list[])集合的索引结构,其中key是一个键值,而list[]是一组出现过key的位置。如(‘hello’,’14:2 23:4’)中,表示hello在14:2和23:4这两个位置出现过。

gin使用:

  • 单值稀疏数据搜索
  • 多列任意搜索,当用户的需求是按照任意列进行搜索时,gin支持多列展开单独建立索引域。从这边可以看出gin和btree都适用联合索引,两者的区分就是,看索引是否是任意的,如果第一个索引列是必有的可以选择btree,相反选择gin。
5.1 前后模糊索引- pg_trgm

对于前后都需要模糊的字段需要用到pg_trgm索引,需要注意的是,数据库的lc_type不能为‘C’,可以通过命令 \l+ database_name 来查看。需要提前创建扩展:

CREATE EXTENSION btree_gin;
CREATE EXTENSION pg_trgm

索引创建:

CREATE INDEX idx_vehiclestructured_plateno_like ON viid_vehicle.vehiclestructured USING GIN (plateno GIN_TRGM_OPS)
5.2 pg_trgm原理

pg_trgm使用时将字符串的前端添加2个空格,末端添加一个空格,之后每三个连续的字符串作为一个TOKEN进行拆分,对TOKEN建立GIN倒排索引。

查看字符串的原理:

SELECT SHOW_TRGM('viid');
结果:show_trgm         
-----------------------------{"  v"," vi","id ",iid,vii}
5.3  物理结构

逻辑结构

GIN索引在逻辑上可以看成一个relation,该relation有两种结构:

  • 只索引基表的一列
keyvalue
Key1Posting list( or posting tree)
Key2Posting list( or posting tree)
  • 索引基表的多列(复合、多列索引)
column_idkeyvalue
Column1 numKey1Posting list( or posting tree)
Column2 numKey1Posting list( or posting tree)
Column3 numKey1Posting list( or posting tree)
.........

这种结构,对于基表中不同列的相同的key,在GIN索引中也会当作不同的key来处理。

GIN索引在物理存储上包含如下内容:

  1. Entry:GIN索引中的一个元素,可以认为是一个词位,也可以理解为一个key

  2. Entry tree:在Entry上构建的B树

  3. posting list:一个Entry出现的物理位置(heap ctid, 堆表行号)的链表

  4. posting tree:在一个Entry出现的物理位置链表(heap ctid, 堆表行号)上构建的B树,所以posting tree的KEY是ctid,而entry tree的KEY是被索引的列的值

  5. pending list:索引元组的临时存储链表,用于fastupdate模式的插入操作
    从上面可以看出GIN索引主要由Entry tree和posting tree(or posting list)组成,其中Entry tree是GIN索引的主结构树,posting tree是辅助树。

entry tree类似于b+tree,而posting tree则类似于b-tree(平衡树)。

另外,不管entry tree还是posting tree,它们都是按KEY有序组织的。

总结:组合索引是为每一个字段创建一个entry tree,当key对应的value数据较少则用链表形式,当达到一定数量则采用B树(平衡树)的模式,这个倒是像极了Java8 HashMap的内部数据部分结构

5.4 pg_trgm适用场景
  1. 有前缀的模糊查询,例如a%,至少需要提供1个字符。

  2. 有后缀的模糊查询,例如%ab,至少需要提供2个字符。

  3. 前后模糊查询,例如%abc%,至少需要提供3个字符。

5.5 查询流程

这个我没有找到相关博客文章的说明,但是根据上面对gin索引结构的说明,人工智能的回答可信度应该是有的。注意:下面是AI回答,作为思考考虑就行,下面介绍不保证正确。

6 brin

Brin索引是块级索引,它不是以行号为单位记录索引明细,而是记录每个数据块或者每段连续的数据块的统计信息。因此brin索引空间占用特别小,对数据写入、更新、删除的影响很小。

    Brin索引适合时序数据(timestamp类型),在时间或序列字段创建索引,进行等值、范围查询时效果好;

以及对存储空间比较严格的场景。

CREATE INDEX idx_vehiclestructured_plateno_like ON viid_vehicle.vehiclestructured USING BRIN(plateno);

Brin的优点

  • 顺序扫描会很快,它是索引顺序扫描的一种改进,如果键值的顺序和存储中块的组织顺序相同,则针对大表的统计型SQL性能会大幅提升。
  • 创建索引的速度非常快。
  • 索引占用的空间很小。

Brin的缺点:

  • Brin在很大程度上依赖于数据相邻性(在磁盘上附近发现相似的数据)。如果我们的数据非常的混乱,则Brin索引查询重叠的条目可能性就非常高。一旦我们的Brin索引开始重叠,就将匹配更多的记录,并且导致需要从源表中读取多个块范围,以找到我们要查找的记录。

适合:

  • 主要适用于类似时序数据之类的,有着天然的顺序,而且都是添加写的场景。比如有序时间这类的。

推荐文章:

PgSQL · 应用案例 · GIN索引在任意组合查询中的应用-阿里云开发者社区 (aliyun.com)

GIN索引 - foreast - 博客园 (cnblogs.com)

PostgreSQL GIN索引实现原理-阿里云开发者社区 (aliyun.com)

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.luyixian.cn/news_show_1006733.aspx

如若内容造成侵权/违法违规/事实不符,请联系dt猫网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

进程等待详解

一、进程等待的作用 我们都知道&#xff0c;当子进程已经结束而父进程还在执行时&#xff0c;子进程会变成僵尸进程&#xff0c;造成内存泄漏等问题&#xff0c;如下&#xff1a; #include <stdio.h> #include <stdlib.h> #include <stdbool.h> #include &l…

2024/3/11打卡分巧克力(第8届蓝桥杯省赛)——二分

题目 儿童节那天有 K 位小朋友到小明家做客。 小明拿出了珍藏的巧克力招待小朋友们。 小明一共有 N 块巧克力&#xff0c;其中第 i 块是 HiWi 的方格组成的长方形。 为了公平起见&#xff0c;小明需要从这 N 块巧克力中切出 K 块巧克力分给小朋友们。 切出的巧克力需要满足&…

面试题:Redis 为什么读写性能高?

Redis 的速度⾮常快&#xff0c;单机的 Redis 就可以⽀撑每秒十几万的并发&#xff0c;性能是 MySQL 的⼏⼗倍。速度快的原因主要有⼏点&#xff1a; 基于内存的数据存储 Redis 将数据存储在内存当中&#xff0c;使得数据的读写操作避开了磁盘 I/O。而内存的访问速度远超硬盘&a…

数据处理分类、数据仓库产生原因

个人看书学习心得及日常复习思考记录&#xff0c;个人随笔。 数据处理分类 操作型数据处理&#xff08;基础&#xff09; 操作型数据处理主要完成数据的收集、整理、存储、查询和增删改操作等&#xff0c;主要由一般工作人员和基层管理人员完成。 联机事务处理系统&#xff…

opengl日记27-opengl报错ERROR::SHADER::PROGRAM::LINKING_FAILED

Author: wencoo Blog&#xff1a;https://wencoo.blog.csdn.net/ Date: 14/03/2024 Email: jianwen056aliyun.com Wechat&#xff1a;wencoo824 QQ&#xff1a;1419440391 Details:文章目录 目录正文 或 背景 报错信息ERROR::SHADER::PROGRAM::LINKING_FAILED解决方法情况1情况…

SpringController返回值和异常自动包装

今天遇到一个需求&#xff0c;在不改动原系统代码的情况下。将Controller的返回值和异常包装到一个统一的返回对象中去。 例如原系统的接口 public String myIp(ApiIgnore HttpServletRequest request);返回的只是一个IP字符串"0:0:0:0:0:0:0:1"&#xff0c;目前接口…

PandasAI—让AI做数据分析

安装 pip install pandasai !pip install --upgrade pandas pandasai 导入依赖项 import pandas as pdfrom pandasai import PandasAIfrom pandasai.llm.openai import OpenAI使用pandas创建一个数据框 df pd.DataFrame({"country": ["United States",…

如何解决ChatGPT消息发不出问题,GPT消息无法发出去,没有响应的问题

前言 今天工作到一半&#xff0c;登陆ChatGPT想咨询一些代码上的问题&#xff0c;结果发现发不了消息了。 ChatGPT 无法发送消息&#xff0c;但是能查看历史的对话。不过首先可以先打开官方的网站&#xff1a;https://status.openai.com/ 。 查看当前Open AI的状态&#xff0…

运放失调电压及其影响

运放失调电压及其影响 在运放的应用中&#xff0c;我们经常会遇到一个重要的性能指标——失调电压。本文将介绍失调电压的定义、优劣范围&#xff0c;并提供一些应对失调电压的方法。 定义 在运放开环使用时&#xff0c;加载在两个输入端之间的直流电压使得放大器直流输出电…

基于斑翠鸟优化算法(Pied Kingfisher Optimizer ,PKO)的无人机三维路径规划(MATLAB)

一、无人机路径规划模型介绍 二、算法介绍 斑翠鸟优化算法(Pied Kingfisher Optimizer ,PKO),是由Abdelazim Hussien于2024年提出的一种基于群体的新型元启发式算法,它从自然界中观察到的斑翠鸟独特的狩猎行为和共生关系中汲取灵感。PKO 算法围绕三个不同的阶段构建:栖息…

利用Java实现数据矩阵的可视化

1. 引言 在进行工程开发时&#xff0c;通常需要在窗口的某个区域将有效数据形象化地呈现出来&#xff0c;例如&#xff1a;对于某一区域的高程数据以伪色彩的方式呈现出高度的变化&#xff0c;这就需要解决利用Java进行数据呈现的问题。本文将建立新工程开始&#xff0c;逐步地…

VScode(Python)使用ssh远程开发(Linux系统树莓派)时,配置falke8和yapf总结避坑!最详细,一步到位!

写在前面&#xff1a;在Windows系统下使用VScode时可以很舒服的使用flake8和yapf&#xff0c;但是在ssh远程开发树莓派时&#xff0c;我却用不了&#xff0c;总是出现问题。当时我就开始了漫长的探索求知之路。中间也请教过许多大佬&#xff0c;但是他们就讲“能用不就行了&…

LabVIEW电磁阀特性测控系统

LabVIEW电磁阀特性测控系统 电磁阀作为自动化工程中的重要组成部分&#xff0c;其性能直接影响系统的稳定性和可靠性。设计一种基于LabVIEW的电磁阀特性测控系统&#xff0c;通过高精度数据采集和智能化控制技术&#xff0c;实现电磁阀流阻、响应时间及脉冲特性的准确测量和分…

【MySQL性能优化】- 一文了解MVCC机制

MySQL理解MVCC &#x1f604;生命不息&#xff0c;写作不止 &#x1f525; 继续踏上学习之路&#xff0c;学之分享笔记 &#x1f44a; 总有一天我也能像各位大佬一样 &#x1f3c6; 博客首页 怒放吧德德 To记录领地 &#x1f31d;分享学习心得&#xff0c;欢迎指正&#xff…

docker部署springboot jar包项目

docker部署springboot jar包项目 前提&#xff0c;服务器环境是docker环境&#xff0c;如果服务器没有安装docker&#xff0c;可以先安装docker环境。 各个环境安装docker&#xff1a; Ubuntu上安装Docker&#xff1a; ubuntu离线安装docker: CentOS7离线安装Docker&#xff1…

应急响应实战笔记03权限维持篇(6)

0x00 前言 在渗透测试中&#xff0c;有三个非常经典的渗透测试框架----Metasploit、Empire、Cobalt Strike。 那么&#xff0c;通过漏洞获取到目标主机权限后&#xff0c;如何利用框架获得持久性权限呢&#xff1f; 0x01 MSF权限维持 使用MSF维持权限的前提是先获得一个met…

开关电源的线性调整率是什么?怎么检测线性调整率?

开关电源线性调整率 开关电源线性调整率是指输入电压在额定范围内变化时&#xff0c;开关电源输出电压随之变化的比率。线性调整率对开关电源的电压稳定性有着重要影响&#xff0c;通常开关电源的线性调整率在1%~5%之间。线性调整率越小&#xff0c;说明电压越稳定&#xff1b;…

Maven编译:Failed to execute goal……Fatal error compiling

问题描述 mvn编译报错 Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.10.1:compile (default-compile) on project postion-report-service: Fatal error compiling 解决方法 pom.xml 中java.version配置为11 检查Maven中的jre是否配置正确

1.下载安装ESP32开发环境ESP-IDE

ESP32简介 ESP32介绍 说到ESP32&#xff0c;首先ESP32不是一个芯片&#xff0c;ESP32是一个系列芯片&#xff0c; 是乐鑫自主研发的一系列芯片微控制器。它主要的功能就是支持WiFi和蓝牙&#xff0c; ESP32指的是ESP32裸芯片。但是&#xff0c;“ESP32”一词通常指ESP32系列芯…

11.用AI运行AI

文章目录 Godmode使用示例概念Recursive Reprompting and Revision(Re3)Language Models as Zero-Shot PlannersHuggingGPTLanguage models can solve computer tasks 部分截图来自原课程视频《2023李宏毅最新生成式AI教程》&#xff0c;B站自行搜索。 用AI运行AI&#xff0c;顾…