人大金仓基于KingbseES的索引基础理论

沉舟侧畔千帆过_

于 2023-09-20 22:32:32 发布

阅读量263

点赞数

文章标签：金仓数据库 kingbase 数据库人大金仓 DBA

本文链接：https://blog.csdn.net/arthemis_14/article/details/133104707

版权

关键字：

Btree、Hash、GiST、GIN、BRIN、SP-GiST

1.什么是索引？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。索引主要是为了加快数据的查询速度与减少系统的响应时间。但是当对表进行更新时，会降低更新表的速度，因为要对表和索引同时更新。

2.使用索引的优点与缺点

优点：

通过创建唯一索引，可以保证数据的唯一性。
提高数据记录的查询速度。
加快表与表之间的连接速度()(小括号)必选语法项目，必须键入小括号。

缺点：

需要占用额外的物理存储空间。
如果表中的数据有变化，则索引也需要同步进行更新，对数据库性能有一定的影响。

3.索引的使用场景与设计原则

3.1 索引的使用场景：

例如汉语字典的目录页，可以按照拼音、笔画、偏旁部首等排序的目录快速查找到需要的字。

3.2 索引的设计原则：

应该创建索引的列：

在经常用于查询的字段上创建索引，加快搜索速度
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。
在经常使用连接的列上
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
在经常需要根据范围进行查询的列上创建索引

不该创建索引的列：

对于那些在查询中很少使用或者参考的列不应该创建索引。因为若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据或者重复值多的列也不应该创建索引。这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引并不能明显加快检索速度。
对经常更新的表需要避免对其建立过多的索引
当该列的修改性能要求远远高于检索性能时，不应该创建索引。修改性能和检索性能是互相矛盾的。

4.常见的索引类型

4.1 Btree索引

Btree索引是最常用的索引类型，当不指定创建的索引类型时，默认选择Btree索引。Btree索引使用Btree数据结构来存储索引数据，可用于处理等值查询和范围查询，包括<、<=、=、>=、>等运算符，以及BETWEEN、IN、IS NULL、IS NOT NULL等条件；Btree索引还可以用于查询结果集排序，如ORDER BY排序。

BTREE索引结构来源于TREE型结构，树形结构可以让增删改查的平均时间复杂度都为O(log(n))。但是使用TREE型结构时，如果需要索引的数据很多，树的层数就会很高，数据量大时查询还是非常慢，因此引入了BTREE索引结构。BTREE的特点就是不再是二叉搜索，而是N叉搜索，树的高度会大大降低。

B树的特征：

关键字集合分布在整棵树中
任何一个关键字出现且只出现在一个节点中
搜索有可能在非叶子节点结束
其搜索性能等价于在关键字全集内做一次二分查找
自动层次控制

4.2 Hash索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，Hash索引基于哈希表实现，只能用于等值查询,特别是索引列的值非常长的等值查询。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个比较小的值，不同键值的行计算出的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引的限制：

哈希索引只包含哈希值和行指针，不存储字段值。
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引只支持等值比较查询。技巧：Hash索引在有限制条件（需要制定一个确定的值而不是一个范围）的情况下非常有用。

4.3 GiST索引

GiST(Generalized Search Tree)是通用搜索树的缩写，与B树类似，也是一种平衡搜索树。它在系统中相当于一个模板，几乎可以实现任意索引模式。

GiST多用于例如地理数据、文本文档、图像等数据类型，它允许定义一些规则将任意类型的数据分布在一颗平衡树上。
可以建立一种可扩展的索引结构，包括数据类型和查询谓词的扩展
GiST是一棵平衡树，即从根节点到叶子节点的经过的节点数相同，节点中包含被索引的数据行。索引项形式为（p,ptr），p是搜索的谓词。在叶子节点中，ptr为指向数据库某元祖的指针；在非叶子节点中，ptr为指向子树节点的指针。

4.4 SP-GiST索引

与GiST索引类似，可作为一种基础模板来实现多种搜索方法。SP-GiST适用于可以递归的将空间分割为不相交区域的结构，例如四叉树、K维树等。SP-GiST的思想是将值域分割成不重叠的子域，每个子域也依次可以分割，这样的划分导致了树的不平衡。

4.5 GIN索引

是一种通用倒排索引，可以处理包含多个键值（如数组）。用它搜索全文或JSON键值效率很高。

提到倒排索引，就需要先了解正排索引，正排索引是以文档的ID作为关键字，表中记录文档中所有出现的字的信息。在建立索引的时候，索引是基于文档建立的，那么后续如果有新的文档加入，就直接添加一个新的索引块，但是在查找的时候，需要对一个文档后面所带的所有文字信息进行一遍扫描，然后才能确定查找值的位置。

而在倒排索引中，就是将正排索引每一行文档+文字的形式给颠倒过来，采用一种类似于标签的形式，以文字+文档的顺序来进行索引，即以关键字来进行索引，关键字所对应的记录表项记录了出现这个字或词的所有文档，因为字或词的对应的文档数量在动态变化，所以维护起来比较困难，但是查询时可以一次通过关键字查询到所有的文档，效率较高。

4.6 BRIN索引

BRIN表示块范围索引。BRIN索引存储连续相邻的数据块统计信息，可以大大缩小索引占用空间。

BRIN索引是一种有损的索引，它相对于B树索引，极大的减少了所占用空间以及查询响应的速度，因为BRIN存储的时元祖数据，以及相关数据的页面信息，例如B树可能存储页面中每个数据的信息，而BRIN索引可能就之存储页面的最大值与最小值，当需要查找的时候，就判断查找值是否在该阈值内，大大的缩小了查找的范围，提高了速率。

5.创建索引的语法

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
[ WHERE predicate ]

参数说明：

UNIQUE：创建唯一索引
CONCURRENTLY ：并行创建索引，在构建索引时不阻塞写操作
Method：索引方法，默认是Btree索引
COLLATE collation：索引的排序规则
ASC：升序排序（默认）
DESC：下降排序
NULLS FIRST：空值排序在非空值前面。当指定DESC时这是默认行为
NULLS LAST：空值排序在非空值后面。当没有指定DESC时这是默认行为

6.常用的索引方法

6.1多列索引（复合索引）

对于多列联合查询的场景来说，可以建一个多列索引。只有Btree、GiST、GIN、BRIN类型的索引支持建多列索引；一个多列索引最多支持32个字段；Btree类型的多列索引，支持从左向右的顺序匹配各个索引字段。

执行本次测试案例时，需提前创建Student2表，其中只定义sno与sage，且都为int型；

建表：create table Student2 (sno int, sage int);

循环插入sno与sage的数值，范围设定为8-10000；PLSQL语句如下：

test=# \set SQLTERM /
test=# declare
test-# type student_type is table of integer;
test-# l_student_snos student_type :=student_type();
test-# begin
test-# for i in 8..10000 loop
test-# l_student_snos.extend;
test-# l_student_snos(l_student_snos.last) := i;
test-# end loop;
test-# forall i in 1..l_student_snos.count
test-# insert into Student2 (sno, sage)
test-# values (l_student_snos(i), l_student_snos(i)-7);
test-# commit;
test-# end;
test-# /

退出PLSQL，通过sql语句查询Student表，随机截取结果如下。

案例1：创建多列索引（a,b）形式,查询条件where a=1 and b=2形式,查看执行计划索引生效。

语句：

CREATE INDEX stu_index ON Student2 USING BTREE(sno,sage);
EXPLAIN ANALYZE SELECT * FROM Student2 WHERE sno=9981 and sage=9981;

案例2：查询条件where a=1 形式，查看执行计划索引生效

语句：EXPLAIN ANALYZE SELECT * FROM Student WHERE sno=9981;

案例3：查询条件where b=2形式，查看执行计划索引不生效

语句：EXPLAIN ANALYZE SELECT * FROM Student WHERE sage=9981;

案例4：查询条件where a=1 or b=2形式，查看执行计划索引不生效

语句： EXPLAIN ANALYZE SELECT * FROM Student2 WHERE sno=9981 and sage=9981;

6.2唯一索引

唯一索引：唯一索引可以保证某个字段值是唯一的，也可以保证多个字段组合值是唯一的，其中字段中的null值被认为是不同的值。
唯一索引是Btree类型的索引，对于表中有唯一性约束或主键约束的字段（或字段组合）来说，KES会自动创建相应的唯一索引，不需要手动再创建唯一索引。

6.3表达式索引

表达式索引可以使用一个函数或表达式的计算结果作为索引的字段。被索引的函数或表达式要用圆括号括起来。表达式索引适用于查询速度远比插入更新速度要求高的场景，不适用于插入更新频繁的情况。

语句：CREATE INDEX ano_power_idx ON Student2 (power(sno,2));

EXPLAIN ANALYZE SELECT * FROM Student2 WHERE power(sno,2)=998001;

更多信息，参见https://help.kingbase.com.cn/v8/index.html

沉舟侧畔千帆过_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
人大金仓基于KingbseES的索引基础理论

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。索引主要是为了加快数据的查询速度与减少系统的响应时间。但是当对表进行更新时，会降低更新表的速度，因为要对表和索引同时更新。
复制链接

扫一扫