MOP 系列|MOP 三种主流数据库索引简介

MOP 不用多说了，我这里指的就是 MySQL、Oracle、PostgreSQL 三种目前最主流的数据库，MOP 系列打算更新 MOP 三种数据库的索引知识、高可用架构及常用 SQL 语句等等，今天打算介绍一下这三种数据库的索引基础知识。

Oracle 索引类型

B 树索引

索引组织表

•索引组织表(IOT)在一个B树索引结构中存储表行的全部内容。使用索引组织表，能缩短 具有精确匹配和主键范围搜索的查询时间。

•B 树索引内部结构：B 树索引有两种类型的块:用于搜索的分支块和用于存储键值的叶块。B 树索引的上层分支块包含指向低层索引块的索引数据。 在索引扫描中，数据库使用语句指定的索引列值遍历索引来检索一行。如果数据库扫描索引寻找一个值，那么它将在 n 个 I/ o 中找到这个值，其中 n 是 B 树索引的高度。这是 Oracle 数据库索引背后的基本原则。

唯一索引

•唯一索引是组成索引的列上没有任何重复值的索引，如果尝试子啊包含重复值的表上创建唯一索引则会报错。当创建唯一约束时会自动创建唯一索引。

反向键索引

•反向键索引是一种 B 树索引，它在保持列顺序的同时物理地反转每个索引键的字节。对于平衡有大量顺序插入的索引的 IO 是非常有用的。 例如，如果索引键为 20，并且该键以十六进制形式存储的两个字节在标准 b 树索引中为 C1,15，则反向键索引将字节存储为15,C1。

键压缩索引

•键压缩索引有助于减少前导列经常重复的组合索引的存储和IO要求。

降序索引

•在升序索引中，Oracle数据库按升序存储数据。默认情况下，字符数据按照值的每个字节中包含的二进制值、数字数据从小到大、日期从早到晚排序。

位图索引

在位图索引中，数据库为每个索引键存储一个位图。在传统的 b 树索引中，一个索引条目指向单行。在位图索引中，每个索引键存储指向多行的指针。

位图索引主要是为数据仓库或查询以特别方式引用许多列的环境而设计的。可能需要位图索引的情况包括: 索引列的基数较低，也就是说，与表的行数相比，不同值的数量很少。 被索引的表要么是只读的，要么不受DML语句的重大修改。

位图连接索引

位图连接索引是两个或多个表连接的位图索引。 对于表列中的每个值，索引存储索引表中对应行的行号。相比之下，标准位图索引是在单个表上创建的。

位图连接索引是通过提前执行限制来减少必须连接的数据量的有效方法。对于位图连接索引何时有用的示例，假设用户经常查询具有特定工作类型的员工数量。典型的查询如下所示:

从概念上讲，idx_bm_employees 是工作的索引。如下查询所示的 SQL 查询中的 title 列(包括示例输出)。索引中的 job_title 键指向 employees 表中的行。查询会计人数可以使用索引来避免访问雇员和工作表，因为索引本身包含所请求的信息。

函数索引

function-based index 基于函数的索引计算涉及一个或多个列的函数或表达式的值，并将其存储在索引中。基于函数的索引既可以是 B 树索引，也可以是位图索引。

虚拟列索引

一种代替基于函数的索引的方法是在表中添加一个虚拟列,然后为虚拟列创建索引（11g 以上）。

虚拟索引

通过关键字 Nosegment 子句可以指示 Oracle 创建永远不会被使用的索引，并且不会将任何去分配给它的索引。

如果我们想要创建一个大表的非常大的索引，但是我们也不确定优化器是否会用到它，那么就可以通过 nosegment 来创建索引进行测试，如果该索引有用，再删除该索引使用不带 nosegment 关键字的命令重新创建。

不可见索引

invisible 不可见索引是被优化器忽略的索引，但是对于表数据的插入、更新或删除时，数据库也会维护索引结构，除非在会话或系统级别显式地将 OPTIMIZER_USE_INVISIBLE_INDEXES 初始化参数设置为 TRUE。

不可用索引

Unusable 当您使索引不可用时，优化器会忽略它，DML 也不会维护它。当您使分区索引的一个分区不可用时，该索引的其他分区仍然有效。在使用不可用的索引或索引分区之前，必须重建或删除并重建它。

全局分区索引

全局分区索引是一个 B 树索引，它独立于创建它的基础表进行分区。单个索引分区可以指向任何或所有表分区，而在本地分区索引中，索引分区和表分区之间存在一对一奇偶校验。

本地分区索引

在本地分区索引中，索引在与其表相同的列上进行分区，具有相同的分区数量和相同的分区边界。 每个索引分区只与基础表的一个分区相关联，因此索引分区中的所有键仅引用存储在单个表分区中的行。通过这种方式，数据库自动将索引分区与其关联的表分区同步，使每个表-索引对独立。

域索引

application domain index 应用程序域索引是特定于应用程序的自定义索引。 扩展索引可以: 在自定义的复杂数据类型(如文档、空间数据、图像和视频剪辑)上容纳索引(参见) 利用专门的索引技术

B 树聚簇索引

B 树索引是聚簇表键上定义的索引。B 树聚簇索引将一个聚簇键与一个数据库块地址相 关联。该索引类型与聚簇表一同使用。

散列聚簇索引

类似地，散列聚簇索引也用于聚簇表，散列聚簇索引与 B 树聚簇索引的差异是，前者使用散列函数取代了索引键。

索引其他操作

重命名索引

删除索引

•不能仅删除与已启用的UNIQUE键或PRIMARY键约束关联的索引。要删除约束关联的索引，必须禁用或删除约束本身。如果删除一个表，所有关联的索引都会自动删除。

重建索引

•您可以选择在线重建索引。联机重新构建使您能够在重新构建的同时更新基表。

改变索引存储特征

•使用Alter index语句修改任何索引的存储参数，包括数据库创建的用于强制执行主键和唯一键完整性约束的存储参数。 例如，下面的语句改变了emp_name索引:

• 对于实现完整性约束的索引，可以通过发出包含 ENABLE 子句的 USING INDEX 子句的 ALTER TABLE 语句来调整存储参数。例如，下面的语句改变了在表 emp上创建的索引的存储选项，以强制执行主键约束:

MySQL 索引类型

索引原理

MySQL默认存储引擎 innodb 只显式支持 B-Tree( 从技术上来说是B+Tree)索引，对于频繁访问的表，innodb 会透明建立自适应 hash 索引，即在B树索引基础上建立hash索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。B+树中的B代表平衡（balance），而不是二叉（binary），因为 B+ 树是从最早的平衡二叉树演化而来的。二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。

MySQL 默认 innodb 存储引擎就是使用B+树来实现索引结构的。由于内节点(非叶子节点)不存储 data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO 效率更高。

非唯一索引

索引值可以出现多次（默认索引类型）

唯一索引

索引值必须唯一或为NULL

主键

表的主键表示在最重要的查询中使用的列或列集。它有一个关联索引，用于快速查询性能。值必须唯一，并且不能包含 NULL。

全文索引

只有 InnoDB 和 MyISAM 存储引擎支持 FULLTEXT 索引，并且只支持 CHAR、VARCHA R和 TEXT 列。索引总是在整个列上进行，不支持列前缀索引。

空间索引

MySQL允许在NOT NULL的几何值列上创建空间索引。优化器检查索引列的SRID属性，以确定要使用哪个空间参考系统(SRS)进行比较，并使用适合于SRS的计算

创建索引的 SQL 语句

函数索引

对表中的列执行表达式或函数计算后的结果构成索引

降序索引

MySQL支持降序索引:索引定义中的DESC不再被忽略，而是导致键值按降序存储。以前，索引可以按相反的顺序扫描，但会降低性能。降序索引可以按正向顺序扫描，这样效率更高。降序索引还使优化器可以在最有效的扫描顺序混合了某些列的升序和其他列的降序时使用多列索引。

•考虑下面的表定义，它包含两个列和四个两列索引定义，用于列上升序和降序索引的各种组合:

复合索引

MySQL可以创建复合索引(即多个列上的索引)。一个索引最多可以包含16列。注意多列索引的最左边前缀匹配原则。

如果索引存在于 (col1, col2, col3)， 只有前两个查询使用索引。第三和第四 查询确实涉及索引列，但不使用索引 执行查找，因为 (col2) 和 (col2, col3) 不是最左边的前缀 (col1, col2, col3)。

不可见索引

MySQL 支持不可见索引;也就是说，优化器不使用的索引。该特性适用于主键以外的索引(显式或隐式)。

查看索引

PostgreSQL 索引类型

PostgreSQL 提供了丰富的索引类型，除支持常规的数值类型、字符串类型数据的索引外，还支持时序、空间、JSON等类型数据的索引。PostgreSQL 提供了 B-tree、Hash、GiST、SP-GiST、GIN、BRIN 等多种索引类型，每种索引类型使用不同的算法来适应不同类型的查询。在默认情况下，创建的索引类型为 B-tree 索引。在索引类型名后面加上关键字 USING，可以选择其他的索引类型，例如，创建一个 HASH 索引：

在创建索引的时候 PostgreSQL 会锁定表以防止写入，然后对表做全表扫描，从而完成创建索引的操作。在此过程中，其他用户仍然可以读取表，但是插入、更新、删除等操作将一直被阻塞，直到索引创建完毕。如果这张表是更新较频繁且比较大的表，那么创建索引可能需要几十分钟，甚至数个小时，这段时间内都不能做任何插入、删除、更新操作，这在大多数的在线数据库中都是不可接受的。鉴于此，PostgreSQL 支持在不长时间阻塞更新的情况下建立创建索引，这是通过在 CREATE INDEX 中加 CONCURRENTLY 选项来实现的。当该选项被启用时，PostgreSQL 会执行表的两次扫描，因此该方法需要更长的时间来建索引。尽管如此，该选项也是很有用的。

修改索引的语法：

1、B-tree 索引

B-tree 索引使用 B-tree 数据结构来存储索引数据，可用于处理等值查询和范围查询，包括＜、＜=、=、＞=、＞等运算符，以及BETWEEN、IN、IS NULL、IS NOT NULL等条件。B-tree 还可以用于查询结果集排序，如 order by 排序。

B-Tree 索引结构参考自德哥 https://github.com/digoal/blog/blob/master/201605/20160528_01.md

PostgreSQL 的 B-Tree索引页分为几种类别：

meta page root page # btpo_flags=2 branch page # btpo_flags=0 leaf page # btpo_flags=1

如果即是 leaf 又是 root 则 btpo_flags=3。

其中 meta page 和 root page 是必须有的，meta page 需要一个页来存储，表示指向 root page 的 page id。随着记录数的增加，一个 root page 可能存不下所有的 heap item，就会有 leaf page，甚至 branch page，甚至多层的 branch page。 一共有几层 branch 和 leaf，就用 btree page 元数据的 level 来表示。

2、Hash 索引

HASH 索引存储一个由索引列计算出的 32 位的 hash code 值。因此，Hash 索引只能处理简单等值比较。每当索引列涉及到等值操作符的比较时，查询规划器将会使用 Hash 索引。

3、GiST 索引

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

例如几何类型检索

4、SP-GiST 索引

SP-GiST 是 “Space-Partitioned GiST” 的缩写，即空间分区 GiST 索引。它是 从PostgreSQL9.2 版本开始提供的一种新索引类型，和 GiST 相似，SP-GiST 索引为支持多种搜索提供了一种基础结构。SP-GiST 允许实现众多不同的非平衡的基于磁盘的数据结构，例如四叉树、k-d 树和 radix 树。主要是通过一些新的索引算法来提高 GiST 索引在某种情况下的性能。

例如 范围类型搜索

5、GIN 索引

GIN 索引是“倒排索引”，它适合于包含多个组成值的数据值，例如数组。倒排索引中为每一个组成值都包含一个单独的项，它可以高效地处理测试指定组成值是否存在的查询。

6、BRIN 索引

BRIN 索引（块范围索引的缩写）存储有关存放在一个表的连续物理块范围上的值摘要信息。因此，那些值和table中物理行存放顺序相关性更好的列更高效。与 GiST、SP-GiST 和 GIN 相似，BRIN 可以支持很多种不同的索引策略，并且可以与一个 BRIN 索引配合使用的特定操作符取决于索引策略。

BRIN索引是块级索引，它不同于B-tree等索引。BRIN索引在记录数据时，以数据块或每段连续的数据块为单位记录信息，而不是以行号为单位记录索引明细。如果块的边界范围很大，或者说块与块之间的重叠度很高，那么BRIN索引的过滤性就很差。因此，BRIN索引仅适合用于检索存储位置与取值线性相关性很强的字段。如时序数据，在时间或序列字段创建BRIN索引，进行等值、范围查询时效果很 Nice。

当然一个索引可以定义在表的多个列上，这样的索引称之为多列索引，CREATE INDEX idx_test_cc ON test2 (c1, c2);目前，只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引。是否可以有多个关键列与INCLUDE列是否可以被添加到索引中无关。索引最多可以有 32 列，包括 INCLUDE 列。

7、表达式索引

一个索引列并不一定是底层表的一个列，也可以是从表的一列或多列计算而来的一个函数或 者标量表达式。和 Oracle 数据库一样，PostgreSQL 也支持函数索引。实际上，PostgreSQL 索引的键除了可以是一个函数外，还可以是从一个或多个字段计算出来的标量表达式。表达式上的索引并不是在索引查找时进行表达式的计算，而是在插入或更新数据行时进行计算，因此在插入或更新时，表达式上的索引会慢一些。

例子 如果我们经常进行如下的查询：

那么值得创建一个这样的索引：

如果我们经常进行如下的查询：

那么需要创建一个大小写转换的索引：

8、部分索引

一个部分索引是建立在表的一个子集上，而该子集则由一个条件表达式（被称为部分索引 的谓词）定义。而索引中只包含那些符合该谓词的表行的项。部分索引是一种专门的特性， 但在很多种情况下它们也很有用。

9、全文索引

PostgreSQL 内置了全文检索功能，但内置的功能只能检索英文。PostgreSQL 全文检索的搜索过程实际上使用一个 tsvector 和 tsquery 进行匹配，tsvector 代表了文档，而 tsquery 代表了检索条件，匹配的运算符是“@@”。

当然还有 zhparser、rum 等索引插件可以用于全文检索，由于个人能力有限，这里就不介绍了，等以后有时间学习了再介绍。

参考链接

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~