PostgreSQL之INDEX 索引详解

2024-12-03 00:54:25

之前总结了PostgreSQL的序列相关知识，今天总结下索引。

我们都知道，数据库索引最主要的作用是可以提高检索数据的速度，但是索引也不是越多越好。因为索引会增加数据库的存储空间，查询数据是要花较多的时间。

1、创建索引

SQL语句如下：

CREATE INDEX idx_commodity
 ON commodity  //表名
 USING btree   //用B树实现
 (commodity_id); //作用的具体列

2、删除索引

DROP index idx_commodity;

3、增加索引的优势：

创建索引可以大大提高系统的性能。

第一，最主要的原因是可以大大加快数据的检索速度；

第二，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

4、增加索引的劣势：

第一，创建索引和维护索引要花费时间，且随着数据量的增加时间也会增加；

第二，索引会占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间；'

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

5、索引的选择

一般来说，应该在这些列上创建索引：

第一、在经常需要搜索的列上，可以加快搜索的速度；

第二、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

第三、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

第四、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

第五、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

第六、在WHERE子句的列上面创建索引，加快条件的判断速度。

一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

补充：PostgreSQL索引分类及使用

1.索引方式

PostgreSQL数据库支持单列index,多列复合 index, 部分index, 唯一index, 表达式index,隐含 index, 和并发index。

2.索引方法

PostgreSQL 支持 B-tree, hash, GiST, and GIN index methods。

3.索引使用范围

1).B-tree

B-tree可以有效使用当一个查询包含等号(=)和范围操作符 (<, <=, >, >=, BETWEEN, and IN)。

2).hash

一个等号操作符(=)，不适合范围操作符。

3).GiST

适用于自定义复杂类型,包括rtree_gist, btree_gist, intarray,tsearch, ltree 和 cube。

4).GIN

GIN比GiST占用多三倍多空间，适合复杂like，例如like ‘%ABC12%'。

4.索引使用注意事项

1).当一个表有很多行时，对一个表列进行索引是很重要的。

2).当检索数据时，应该选择一个好的备选列作为索引，外键，或者取最大最小值的键，列的选择性对索引有效性很重要。

3).为了更好的性能要移除不使用的索引，为了清除无法利用的行每隔一月重建所有索引。

4).如果有非常大量的数据，使用表分区索引。

5）当列中包含NULL值时，可以考虑建立一个不包含NULL的条件索引。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

PostgreSql 重建索引的操作

PostgreSql数据库的重建索引时通过REINDEX命令来实现的,如reindexindex_name: 其语法是: REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]; 下面解释下说明情况下需要: 1.当由于软件bug或者硬件原因导致的索引不再可用,索引的数据不再可用: 2.当索引包含许多空的或者近似于空的页,这个在b-tree索引会发生.Reindex会腾出空间释放哪些无用的页(页就是存放数据的一个单位,类似于bloc
PostgreSql 的hash_code函数的用法说明

PostgreSql 实现的hash_code 函数与java hash_code方法一致 CREATE FUNCTION hash_code(text) RETURNS integer LANGUAGE plpgsql AS $$ DECLARE i integer := 0; DECLARE h bigint := 0; BEGIN FOR i IN 1..length($1) LOOP h = (h * 31 + ascii(substring($1, i, 1))) & 42949672
Postgresql 查询表引用或被引用的外键操作

今天更新两个SQL.是用来查询PG中,主表被子表引用的外键,或子表引用了哪个主表的主键. 废话不多说,直接上实验! CentOS 7 + PG 10 创建两个实验表,test01为主表,test02为子表,test02引用test01中的id列. test=# create table test01( test(# id int primary key, test(# col1 varchar(20) test(# ); CREATE TABLE test=# create table test
postgresql 索引之 hash的使用详解

os: ubuntu 16.04 postgresql: 9.6.8 ip 规划 192.168.56.102 node2 postgresql help create index postgres=# \h create index Command: CREATE INDEX Description: define a new index Syntax: CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON
PostgreSQL Sequence序列的使用详解

PostgreSQL是一种关系型数据库,和Oracle.MySQL一样被广泛使用.平时工作主要使用的是PostgreSQL,所以有必要对其相关知识做一下总结和掌握,先总结下序列. 一. Sequence序列 Sequence是一种自动增加的数字序列,一般作为行或者表的唯一标识,用作代理主键. 1.Sequence的创建例子:创建一个seq_commodity,最小值为1,最大值为9223372036854775807,从1开始,增量的步长为1,缓存为1的循环排序Sequence. SQL语句如
PostgreSQL之INDEX 索引详解

之前总结了PostgreSQL的序列相关知识,今天总结下索引. 我们都知道,数据库索引最主要的作用是可以提高检索数据的速度,但是索引也不是越多越好.因为索引会增加数据库的存储空间,查询数据是要花较多的时间. 1.创建索引 SQL语句如下: CREATE INDEX idx_commodity ON commodity //表名 USING btree //用B树实现 (commodity_id); //作用的具体列 2.删除索引 DROP index idx_commodity; 3.增加索引的
InnoDB的关键特性-插入缓存,两次写,自适应hash索引详解

InnoDB存储引擎的关键特性包括插入缓冲.两次写(double write).自适应哈希索引(adaptive hash index).这些特性为InnoDB存储引擎带来了更好的性能和更高的可靠性. 插入缓冲插入缓冲是InnoDB存储引擎关键特性中最令人激动的.不过,这个名字可能会让人认为插入缓冲是缓冲池中的一个部分.其实不然,InnoDB缓冲池中有Insert Buffer信息固然不错,但是Insert Buffer和数据页一样,也是物理页的一个组成部分. 主键是行唯一的标识符,在应用程序
对Pandas MultiIndex(多重索引)详解

创建多重索引 In [16]: df = pd.DataFrame(np.random.randn(3, 8), index=['A', 'B', 'C'], columns=index) In [17]: df Out[17]: first bar baz foo qux \ second one two one two one two one A 0.895717 0.805244 -1.206412 2.565646 1.431256 1.340309 -1.170299 B 0.4108
MySQL数据库之索引详解

目录一.MySQL索引简介二.MySQL五种类型索引详解 (一)普通索引 (二)唯一性索引 (三)主键索引 (四)复合索引 (五)全文索引三.MySQL索引使用原则总结今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL索引相关内容. 一.MySQL索引简介索引是MySQL数据库为了加快数据查询的速度,给表中的某一个或者是某几个列添加的一种"目录".MySQL的索引是一个特殊的文件,但是InnoDB类型引擎(关于MySQL的引擎我们会在今后的文章中进行讲解)的表的
MySQL索引详解及演进过程及面试题延伸

目录 1索引的概念 1.1定义 1.2类型 1.3作用 2索引的数据结构B+树的演进过程 2.1问题 2.2问题 2.3问题:怎么建目录呢?给每一个页都建一个目录吗? 2.4索引树.页的分裂与合并 2.5根据我们刚才推演的,延申出几个面试题 3什么是二级索引树 3.1那么二级索引树怎么排序? 3.2索引桥的概念是什么呢(最左匹配原则)? 3.3回表.覆盖索引.索引下推 3.4延申几个面试题: 3.5二级索引树的总结 4主键索引与二级索引的区别 1索引的概念 1.1定义索引在关系型数据库中,是一
PostgreSQL copy 命令教程详解

报文介绍PostgreSQL copy 命令,通过示例展示把查询结果导出到csv文件,导入数据文件至postgresql. 1. copy命令介绍 copy命令用于在postgreSql表和标准文件系统直接传输数据.copy命令让PostgreSQL 服务器直接读写文件,因此文件必须让PostgreSQL 用户能够访问到.该命令使用的文件是数据库服务器直接读写的文件,不是客户端应用的文件,因此必须位于服务器本地或被直接访问的文件,而不是客户端位置. copy to 命令拷贝表内容至文件,也可以拷
Python NumPy教程之索引详解

目录为什么我们需要 NumPy 使用索引数组进行索引索引类型基本切片和索引高级索引 NumPy 或 Numeric Python 是一个用于计算同质 n 维数组的包.在 numpy 维度中称为轴. 为什么我们需要 NumPy 出现了一个问题,当 python 列表已经存在时,为什么我们需要 NumPy.答案是我们不能直接对两个列表的所有元素执行操作.例如,我们不能直接将两个列表相乘,我们必须逐个元素地进行.这就是 NumPy 发挥作用的地方. 示例 #1: # 演示需要 NumPy 的
PostgreSql JDBC事务操作方法详解

目录 JDBC事务相关方法简介禁用自动提交模式提交事务回滚事务 PostgreSQL JDBC 事务示例 JDBC事务相关方法简介本文将借助示例,简单讲解下JDBC操作Pg事务的流程. 首先来简单讲解下事务的定义:为了确保两个(多个)数据库操作都生效,或者两个操作都不发生,可以使用事务.根据定义,事务是作为单个单元执行的一组语句.换句话说,要么所有语句都成功执行,要么没有执行. 禁用自动提交模式当建立与PostgreSQL数据库的连接时,它处于自动提交模式.这意味着每个SQL语句都被视
PostgreSQL教程(八)：索引详解

一.索引的类型: PostgreSQL提供了多种索引类型:B-Tree.Hash.GiST和GIN,由于它们使用了不同的算法,因此每种索引类型都有其适合的查询类型,缺省时,CREATE INDEX命令将创建B-Tree索引. 1. B-Tree: 复制代码代码如下: CREATE TABLE test1 ( id integer, content varchar ); CREATE INDEX test1_id_index
postgreSQL的crud操作详解

postgreSQL学习笔记欢迎大家指出问题! 版本从6.0开始支持SQL解释器安装一路下一步. 1.创建数据库 //命令行操作 createdb database_name; CREATE DATABASE database_name; psql -l //展示所有数据库 psql database_name //进入数据库 2.删除数据库 dropdb database; DROP DATABASE database_name; 3.创建表 create tabl