深入理解四种数据库索引类型（- 唯一索引/非唯一索引 - 主键索引（主索引） - 聚集索引/非聚集索引 - 组合索引）

深入理解四种数据库索引类型（- 唯一索引/非唯一索引 - 主键索引（主索引） - 聚集索引/非聚集索引 - 组合索引）

• 唯一索引/非唯一索引
• 主键索引（主索引）
• 聚集索引/非聚集索引
• 组合索引

唯一索引/非唯一索引

唯一索引

1.唯一索引是在表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中不可以重复。

非唯一索引

2.非唯一索引是在表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中可以重复，不要求唯一。

主键索引（主索引）

3.主键索引（主索引）是唯一索引的特定类型。表中创建主键时自动创建的索引 。一个表只能建立一个主索引。

聚集索引/非聚集索引

4.聚集索引（聚簇索引），表中记录的物理顺序与键值的索引顺序相同。一个表只能有一个聚集索引。

扩展：聚集索引和非聚集索引的区别？分别在什么情况下使用？

聚集索引和非聚集索引的根本区别是表中记录的物理顺序和索引的排列顺序是否一致。

聚集索引的表中记录的物理顺序与索引的排列顺序一致

优点是查询速度快，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理的紧跟其后。

缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效。

建议使用聚集索引的场合为：
A.某列包含了小数目的不同值。
B.排序和范围查找。

非聚集索引的记录的物理顺序和索引的顺序不一致

其他方面的区别：
1.聚集索引和非聚集索引都采用了 B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用中的记录在叶子层包含一个指向表数据页中的指针的方式。聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点。

2.非聚集索引添加记录时，不会引起数据顺序的重组。

看上去聚簇索引的效率明显要低于非聚簇索引， 因为每次使用辅助索引检索都要经过两次 B+树查找， 这不是多此一举吗？ 聚簇索引的优势在哪？

1.由于行数据和叶子节点存储在一起， 这样主键和行数据是一起被载入内存的， 找到叶子节点就可以立刻将行数据返回了， 如果按照主键 Id 来组织数据， 获得数据更快。

2.辅助索引使用主键作为"指针", 而不是使用地址值作为指针的好处是， 减少了当出现行移动或者数据页分裂时,辅助索引的维护工作， InnoDB 在移动行时无须更新辅助索引中的这个"指针"。 也就是说行的位置会随着数据库里数据的修改而发生变化， 使用聚簇索引就可以保证不管这个主键 B+树的节点如何变化， 辅助索引树都不受影响。

建议使用非聚集索引的场合为：
a.此列包含了大数目的不同值；
b.频繁更新的列

5.组合索引（联合索引）

基于多个字段而创建的索引就称为组合索引。

创建索引
create index idx1 on table1(col1,col2,col3)
查询
select * from table1 where col1= A and col2= B and col3 = C

组合索引查询的各种场景
组合索引 Index (A, B, C)

下面条件可以用上该组合索引查询：
 A>5
 A=5 AND B>6
 A=5 AND B=6 AND C=7
 A=5 AND B=6 AND C IN (2, 3)

下面条件将不能用上组合索引查询：
 B>5 ——查询条件不包含组合索引首列字段
 B=6 AND C=7 ——理由同上

下面条件将能用上部分组合索引查询（重要！ ！ ！ ！ ） ：
 A>5 AND B=2 ——当范围查询使用第一列， 查询条件仅仅能使
用第一列
 A=5 AND B>6 AND C=2 ——范围查询使用第二列， 查询条件仅仅能使用
前二列
 A=5 AND B IN (2, 3) AND C=2 ——理由同上

组合索引排序的各种场景:

兹有组合索引 Index(A,B)。
 下面条件可以用上组合索引排序：
 ORDER BY A——首列排序
 A=5 ORDER BY B——第一列过滤后第二列排序
 ORDER BY A DESC, B DESC——注意， 此时两列以相同顺序排序
 A>5 ORDER BY A——数据检索和排序都在第一列

下面条件不能用上组合索引排序：
 ORDER BY B ——排序在索引的第二列
 A>5 ORDER BY B ——范围查询在第一列， 排序在第二列
 A IN(1,2) ORDER BY B ——理由同上
 ORDER BY A ASC, B DESC ——注意， 此时两列以不同顺序排序

alter table users add index lname_fname_age(lname,fname,age);

创建了 lname_fname_age 多列索引,相当于创建了(lname)单列索引，
(lname,fname)联合索引以及(lname,fname,age)联合索引。

举例说明：上面给出一个多列索引(username,password,last_login)，当
三 列 在 where 中 出 现 的 顺 序 如 (username,password,last_login) 、
(username,password)、(username)才能用到索引，如下面几个顺序
(password,last_login) 、 (passwrod) 、 (last_login)--- 这 三 者 不 从
username 开始，(username,last_login)---断层，少了 password，都无
法利用到索引。因为 B+tree 多列索引保存的顺序是按照索引创建的顺序，
检索索引时按照此顺序检索。

作者：六尺帐篷
链接：https://www.jianshu.com/p/f49ee80c4956
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。