- 数据库 - 索引 index

---

InnoDB支持的：B树索引、哈希索引、集群索引、聚簇索引、覆盖索引；

什么是索引

1、什么是索引：
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可以快速访问数据库表中的特定的信息；

通俗的讲，数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录；
它是一个独立的表，一条索引记录要包含的基本信息有键值和逻辑指针；

• 键值：定义索引时指定的所有字段的值；
• 逻辑指针：指向数据页或另一个索引页；

2、为什么要使用索引：
使用索引可以优化查询，加快访问速度，减少磁盘IO；但是不能优化DML操作；

不使用索引时，数据库只能一行一行扫描所有记录，然后判断该记录是否满足查询条件；
（1）使用索引可以避免全表扫描；多数查询可以只扫描少量索引页及数据页，而不是遍历所有数据页；
（2）对于非聚集索引，有些查询甚至可以不访问数据页，就能获取到查询的数据；
如果SQL语句仅访问被索引的列，那么数据库只需从索引中读取数据，而不用读取表；
如果该语句同时还要访问非索引列，那么Oracle会使用rowid来查找表中的行；
（3）聚集索引可以避免数据插入操作集中于表的最后一个数据页；
（4）一些情况下，索引还可用于避免排序操作；

3、使用索引的不足：
（1）创建索引和维护索引要耗费时间，这个时间随着数据量的增加而增加；
（2）索引是一个独立的数据表，需要占用物理空间，建立聚簇索引占用的空间更大；
（3）对表中的数据进行增加、修改、删除时，索引也要动态维护；这就导致了数据库更新数据的性能下降了；

4、什么时候使用索引：
（1）表经常进行select查询操作；在经常需要搜索的列上创建索引；
（2）列名经常在where子句或连接条件中出现；
（3）表记录很多，记录的内容分布范围很广，离散度高的列，返回的行数小于5%；
（4）更新键值代价低，不是频繁更新的；
（5）使用逻辑AND/OR效率高；

---

三星系统

1、索引将相关的记录放到一起；
2、索引中的数据顺序和查找中的排列顺序一致；
3、索引中的列包含看查询中需要的全部列；

---

存储索引的数据结构

在MySQL中，索引时存储在引擎层的而不是服务器层实现的，所以不同存储引擎支持不同的索引，多个存储引擎支持同一个索引，其底层实现也可能不同；

B-树索引

B+树【InnoDB使用的是B+树；】

B-树索引 使用B树数据结构来存储数据；

B树的每个节点都是一个索引页；每个节点上存放m-1个key和m个指向子节点的指针，每个key是一条索引记录，

B-Tree索引所有的值都是按顺序存储的，并且每一个叶子到根的距离相同；

B树索引能加快访问数据的速度，因为存储引擎不用再进行全表扫描来获取需要的数据，而是从索引根节点开始搜索，根节点中存放了索引的数据和指向子节点的指针，若根节点页中没有找到需要的索引数据，则根据指针向下层子节点页中继续查找；通过比较要查找的值和节点页的值，可以找到合适的指针进入下层子节点页；

指针实际上定义了子节点页中值的上下限；

叶子结点的指针指向被索引的数据，而不是其他节点页；

create table people(
	last_name  varchar(50)     not null,
	first_name  varchar(50)     not null,
	dbo    	   date            not null,
	gender     enum('m', 'f')  not null,
	key(last_name, first_name, dbo)         // 定义复合索引
);

索引对多个值进行排序的顺序是依据创建表时定义索引时列的顺序；

例如：在一个节点页中存储的两个key中存储的姓、名都相同，则根据出生日期进行排序；

B树索引适用于全键值、键值范围、键最左前缀查找；

• 全键值匹配：指的是和索引中所有列进行匹配；
  例如：可以指定姓、名、出生日期；
• 键值范围匹配：可以指定姓在某一范围之间；
• 键最左前缀匹配：可以只使用索引的第一列进行匹配；
  例如：根据姓来查找索引值；
• 键最左列前缀匹配：只使用索引第一列的一部分进行匹配；
  例如：姓为xyz时，可以只指定x；
• 精确匹配第一列，范围匹配第二列：

B-树索引限制：
1、如果不是按照索引最左列开始查找，则无法使用索引：无法查找指定名的的值；

2、不能跳过索引中的列：只指定姓、出生日期，不指定中间的名列，那么就使用指定的索引的第一列；

3、若查询中第一列使用范围查找，第二列使用全值查找，则只能使用第一列；

SO，定义索引时指定的列的顺序很重要！优化性能时，可以使用相同的列但顺序不同的索引来满足不同类型的需求；

哈希索引

哈希索引是基于哈希表实现的，只有精确匹配索引的所有列的查询才有效；对索引表的每一行数据，存储引擎都会对所有的索引列计算一个哈希值，不同键值的行计算出来的哈希值不一样，但是也会有冲突存在；哈希索引会将所有的哈希值存储在索引中，同时在哈希表中保存指向每个数据行的指针；

有的存储引擎支持非唯一哈希索引：如果有多列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中；

哈希索引的数据结构：每个节点存储索引行的哈希值和指向数据表中行记录的指针；哈希值是顺序存储的，但是行记录指针不是有序的；

使用哈希索引存储时，只支持精确匹配索引的所有列，查找记录时，根据指定的索引列的值计算哈希值，然后根据哈希值查找对应的记录指针，最后根据指针指向的行记录的值，比较是否匹配查询条件，以确保就是要查找的行；

哈希索引的限制：
1、哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行；

2、哈希索引数据不是按照索引值顺序存储的，所以无法用于排序；

3、哈希索引不支持部分索引列匹配查找，因为哈希索引存储的哈希值是使用索引列的全部内容计算出来的；

4、哈希索引只支持的等值比较查询：=、in()、<=>，不支持范围查询：where age > 20;

5、访问哈希索引的数据的速度快，除非哈希冲突多，因为出现哈希冲突时，存储引擎需要遍历链表中存储的所有行指针，逐一对比行记录的值是否符合查询条件；

6、若哈希冲突很多的话，维护索引的代价就大；比如删除一行时，需要遍历对应哈希值的链表中的每个行指针；

在使用哈希索引进行查询时，必须在where子句中包含常量值：select id from url where url_crc=CRC32("http://www.mysql.com") and url="http://www.mysql.com";
根据url_crc哈希值查找行指针，若出现哈希冲突，就根据url的值，与链表中存储的所有行指针指向的行记录值进行对比，找出对应的行记录；

位图索引 (bitmap) (oracle)

空间数据索引 (R-Tree)

全文索引

---

索引分类

聚集索引与非聚集索引

一般索引(oracle)

索引键值可重复；

唯一索引

唯一索引：不允许任意两行具有相同索引值的索引；唯一索引的值不重复，但是可以有一个null；

注意：

• 若为已经存在的表的某列创建唯一索引，如果该列中有重复的值，那么不能在该列上创建索引；
• 若创建表时指定索引列，那么在向表中添加记录时，索引列的值不能重复；也就是说若将学生表的name列设置为索引列，那么添加记录时，添加已经存在的name的记录会失败；
• 创建表时，若为某字段设置了唯一性约束，那么数据库会隐式的为该列创建唯一索引，索引的名称就是列的名称；
• 可以为某一字段设置两个唯一索引，只要索引名字不同即可；
  create unique index name1 on student(name);
create unique index name2 on student(name);

主键索引

主键索引：数据库为表定义主键时将会自动创建主键索引，主键索引是唯一索引的特定类型；该索引要求主键中每个值都唯一，但是不能为空；在查询中使用主键索引时，还允许对数据的快速访问；

复合索引

包含多个字段的索引，称为复合索引;

索引最多可以包含31个字段，索引记录最大长度为600B;

聚集索引 (Clustered)

聚集索引也是一种数据存储方式，InnoDB的聚集索引是在在同一个结构中保存了B-树索引和数据行；

聚集索引：表中数据按照索引的顺序来存储的；对于聚集索引，叶子结点也就是数据结点；叶子节点存储了真是的数据行，不再有另外的数据页；非叶子结点只存储索引列的值；叶子结点存储所有列的值；

• 一张表中只能创建一个聚集索引，因为真实数据的物理顺序只能有一种；如果存在聚集索引，就不能再指定CLUSTERED 关键字；
• 如果一张表没有聚集索引，那么它就被称为“堆集”(heap)，所有的新行都被添加到表的末尾位置；
• InnoDB将主键作为聚集索引，如果没有显示定义主键，那么就会选择一个非空的唯一索引代替，如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚集索引；

（1）聚集索引查询操作：
如上图，在名字字段建立聚集索引，当需要根据此字段查找特定记录时，会从此索引的根结点开始查找，根结点没有，就根据指针查找下一个子节点，知道找到或返回查找失败；

例如：查找“green”，在索引页1001中没有，但是它介于[Bennet,Karsen]，据此找到了索引页1007，在该页中，Green介于[Greane, Hunter]间，据此找到叶子结点1133，在此页中找到了目标数据行，该行存储了所有字段的值，不只是索引字段的值；

此次查询的IO操作包括3个索引页的插叙（最后一个其实是在数据页中查询），这里的查询可能是从磁盘读取，或只从缓存中读取的，如果此表访问频率比较高，那么索引树中较高层的索引可能是从缓存中读取的，所以真正的IO可能小于上面的情况；

（2）聚集索引插入操作：
最简单的情况下，插入操作根据索引找到对应的数据页，然后通过挪动已有记录为新数据腾出空间，最后插入数据；

若数据页已经满了，则需要拆分数据页；（页拆分比较耗费资源，所以数据库系统中会有相应的机制尽量减少页拆分的次数，通常是为每页预留空间）

（3）聚集索引删除操作：
删除操作将导致下方的数据行向上移动以填充空白记录造成的空白；如果删除的行是数据页的最后一行，那么该页将会被回收，相应的索引页中的记录将被删除；

数据的删除操作，可能会导致索引页中仅有一条记录，这时，该记录可能会被移到临近的索引页中，原索引页会被回收，这就是“索引合并”；

非聚集索引 (Non-clustered)

非聚集索引：表数据的存储顺序与索引的顺序无关；对于非聚集索引，叶子结点存储的是索引字段的值和指向数据页数据行的指针，而不是存储所有字段的值；该层紧邻数据页，其行数与数据表行数一致；

• 一个表中只能有一个聚集索引，但是表中的每一列都可以有自己的非聚集索引；

（1）非聚集索引查询操作：
先查找索引页，找打叶子结点之后，根据叶子结点中记录存储的指针查找对应的数据页，在数据页中找到需要的数据行；

（2）非聚集索引插入操作：
若一张表中包含非聚集索引，但是没有聚集索引，那么新数据将被插入到最后一个数据页中，然后更新非聚集索引；

若包含聚集索引，那么就会根据聚集索引查找数据的插入位置，然后更新聚集索引和非聚集索引；

（3）非聚集索引删除操作：
如果在删除命令的Where子句中包含的列上，建有非聚集索引，那么该非聚集索引将被用于查找数据行的位置，数据删除之后，位于索引叶子上的对应记录也将被删除。如果该表上有其它非聚集索引，则它们叶子结点上的相应数据也要删除。

如果删除的数据是该数所页中的唯一一条，则该页也被回收，同时需要更新各个索引树上的指针。

由于没有自动的合并功能，如果应用程序中有频繁的随机删除操作，最后可能导致表包含多个数据页，但每个页中只有少量数据；

覆盖索引

覆盖索引：非聚集索引的叶子节点包含所有数据行中的索引列值，使用这些节点就可以返回查询所需要的真正的数据，而不需要访问数据页，这种情况就称为索引覆盖；

如果在多个字段上创建了一个符合的非聚集索引，并且查询中所需要的select字段，以及where、order by、group by、having子句中所涉及到的字段都包含在索引列中，那么只所搜索引页就能查到所需数据，而不用再访问数据页了；

在索引覆盖的情况下，包含两种扫描：匹配索引扫描、非匹配索引扫描；

索引覆盖要求索引列要包含查询中设计的所有字段，另外，若where子句中的查询条件符合“全键值”、“键前缀查找”等的要求，就可以使用匹配索引扫描，否则只能使用非匹配索引扫描；非匹配索引扫描将扫描索引树上的所有叶子结点；

聚集索引与非聚集索引的区别

• 一个表中只能有一个聚集索引，但是可以有 多个非聚集索引；
• 聚集索引中索引的顺序与数据表中行的物理顺序相同，非聚集索引中索引顺序与表中行记录顺序无关；
• 聚集索引中节点存储的是完整的行记录数据，不再有数据表，非聚集索引中叶子结点中存储的是索引列的值和执行数据表中对应行的指针；
• 聚集索引插入数据时，先根据索引找到插入位置再插入，非聚集索引直接在最后一个数据页中插入，然后更新索引表；
• 聚集索引数据访问速度更快；

各种索引特点：

• 唯一索引字段值不重复，可以有一个为null；主键索引字段值不重复，不能有null；
• 一个表只能有一个聚集索引，但是可以有多个非聚集索引、唯一索引；
• 聚集索引一定是唯一索引，唯一索引不一定是聚集索引；
• 唯一索引有助于定位信息，但是主键索引性能更好；

---

创建 / 修改 / 删除 索引

MySQL

unique：唯一索引；
clustered：聚集索引；
noclustered：非聚集索引；

索引名字要唯一，一般格式是“表名_字段1名_字段2名”

• 为已存在的表创建索引：
  create unique/clustered/noclustered index indexName on tableName(列名1 asc, 列名2 desc, ...);
• 修该表：alter table tableName add unique indexName(列的列表);
• 创建表时指定索引：create table tableName([...], unique uniqueName (列的列表));

修改索引名字：alter index oldIndexName rename to newIndexName;

删除索引：drop index indexName;

Oracle

查看索引建在哪表、列：
select * from user_indexes;
select * from user_ind_columns;

---

索引碎片问题 (oracle)

一张表上线使用很久之后，期间由于对基表做DML操作，导致索引表块的自动更改操作，尤其是基表的delete操作，会引起index表的index_entries的逻辑删除，而只有当一个索引块中的所有index_entry都被删除了，这个索引块才会被删除；索引对基表的delete、insert操作都会产生索引碎片问题；时间越长，索引碎片就会越来越大，查询时间会越来越长、效率越来越低；

在Oracle里面，可以通过查看index_stats视图，当发生以下三种情形之一时，就说明索引碎片该整理了：
（1）Height >= 4；
（2）pct_used < 50%；
（3）del_lf_rows / lf_rows > 0.2；

1、使用非管理员账号创建表t，在表t的id列创建索引，并向表中insert一百万条记录：

create table t(id int);
create index ind_1 on t(id);
begin for i in 1..1000000 loop insert into t values(i); if mod(i, 100) = 0 then commit; end if;end loop;end;

2、查看索引建立在哪个列：select * from user_ind_columns;

3、分析索引使用情况：analyze index ind_1 validate structure;
（注意：每次查看index_stats视图之前都要先分析分析一次，执行分析操作之后，才会把分析的结果插入到index_stats视图中，才能查看到最新的索引使用情况）

4、查看索引使用情况：select name, height, pct_used, del_lf_rows/lf_rows from index_stats;

5、整理索引碎片(在线分析整理)：alter index ind_1 rebuild [online] [ts_test];
注：以前整理索引碎片，要先停服务，把表锁住，然后把数据导入到另外一张新表，然后再整理；现在可以在线整理索引，不需要停服务；

6、若出现ORA-00439: feature not enabled: Online Index Build , Time: 0.000000s，select * from v$option where parameter = 'Online Index Build'; false表示没有开启在线整理索引碎片的功能，或者Oracle版本不支持；

7、若没开启在线整理索引碎片功能，那就不在线：alter index ind_1 rebuild；

8、先分析，再查看；