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

前 言

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

Oracle 索引类型

B 树索引

索引组织表

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

create table prod_sku (prod_sku_id number,sku varchar2(256),
constraint prod_sku_pk primary key(prod_sku_id,sku))
organization index;


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

唯一索引

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

create unique index idx_cust_unq on cust(last_name,first_name);


反向键索引

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

create index idx_cust_id_rev on cust(cust_id) reverse;


键压缩索引

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

create index idx_cust_comp on cust(last_name,first_name) compress 2;


降序索引

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

create index idx_cust_iddesc on cust(cust_id desc);


位图索引

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

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

create table f_sales(sales_amt number,d_date_id number,d_product_id number,d_customer_id number);
create bitmap index bit_idx_f_sales_id on f_sales(d_date_id);


位图连接索引

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

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

SELECT COUNT(*) FROM employees, jobs
WHERE employees.job_id = jobs.job_id
AND jobs.job_title = 'Accountant';

CREATE BITMAP INDEX idx_bm_employees ON employees (jobs.job_title)
FROM employees, jobs
WHERE employees.job_id = jobs.job_id;


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

函数索引

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

CREATE INDEX idx_cust_valid ON customers ( CASE cust_valid WHEN 'A' THEN 'A' END ); CREATE INDEX idx_emp_total_sal ON employees (12 * salary * commission_pct, salary, commission_pct);


虚拟列索引

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

create table inv(inv_id number,inv_count number, inv_status generated always as ( case when invcount 100 then 'OKAY end) );

create index idx_inv_stas on inv(inv_status);


虚拟索引

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

alter session set "_use_no_segment_indexes"=true;
create index idx_cust_id on cust(cust_id) nosegment;


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

不可见索引

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

CREATE INDEX idx_emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k) INVISIBLE;
ALTER INDEX idx_emp_ename VISIBLE;
ALTER INDEX idx_emp_ename INVISIBLE;
SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_EMP_ENAME';


不可用索引

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

ALTER INDEX idx_emp_email UNUSABLE;

SELECT SEGMENT_NAME, BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('IDX_EMP_EMAIL');
no rows selected


全局分区索引

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

CREATE INDEX cust_last_name_ix ON customers (cust_last_name) GLOBAL PARTITION BY HASH (cust_last_name) PARTITIONS 4;


本地分区索引

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

CREATE INDEX hash_sales_idx ON hash_sales(time_id) LOCAL;


域索引

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

B 树聚簇索引

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

散列聚簇索引

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

索引其他操作

重命名索引

ALTER INDEX index_name RENAME TO new_name;


删除索引

DROP INDEX idx_emp_ename;


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

重建索引

ALTER INDEX idx_emp_name REBUILD;


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

ALTER INDEX idx_emp_name REBUILD ONLINE;


改变索引存储特征

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

ALTER INDEX idx_emp_ename STORAGE (NEXT 40);


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

ALTER TABLE emp ENABLE PRIMARY KEY USING INDEX;


参考链接

https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/indexes-and-index-organized-tables.html#GUID-ACA0308E-5F01-4236-81D3-D0CDE5CB6695

https://www.postgresql.org/files/documentation/pdf/16/postgresql-16-A4.pdf


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

❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————