聚集索引 VS 非聚集索引
索引：关系型数据库中给数据库表中一列或多列的值排序后的存储结构。
1、MySQL索引类型：
-
从数据结构角度
-
B+ 树索引 (O(log(n)))
-
在 MyISAM 中
- 主键和其他的并没有太大区别，不过和 InnoDB 不太一样的地方是在 MyISAM 里 leaf node 里存放的不是主键信息，而是指向数据文件里的对应数据行的信息
- MyISAM 的 B+ Tree 的叶子节点上的 data 并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的 key 一定得是唯一的，这里的索引都是非聚集索引
- MyISAM 还采用压缩机制存储索引，比如，第一个索引为 "her"， 第二个索引为 "here"，那么第二个索引会被存储为 "3,e"，这样的缺点是同一个节点中的索引只能采用顺序查找
-
在 InnoDB 中，有两种形态
- primary key 形态，其 leaf node 里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据
- secondary index 形态，其 leaf node 和普通的 BTREE 差不多，只是还存放了指向主键的信息
-
-
Hash 索引
- 仅能满足 "=","IN",<=>""查询，不能使用范围查询（由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值得过滤，不能用于基于范围得过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系并不能保证和 Hash 运算前完全一样）
- Hash 索引无法被用来避免数据的排序操作（由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且 Hash 值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算）
- Hash 索引不能利用部分索引键查询（对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键值合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或者几个索引键进行查询的时候，Hash 索引也无法被利用）
- Hash 索引在任何时候都不能避免表扫描（Hash 索引是通过 Hash 运算之后，将 Hash 运算结果的 Hash 值和所对应的行指针信息存放在一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果）
- Hash 索引遇到大量 Hash 值相等的情况后性能并不一定会比 B-Tree 索引高（对于选择性较低的索引，如果创建 Hash 索引，那么将会存在大量记录指针信息与同一个 Hash 值相关联，这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下）
-
FULLTEXT 索引
- 主要用来查找文本中的关键字，分词；而不是直接与索引中的值相比较。Full-text 索引和其他索引大不相同，它更像是一个搜索引擎，而不是简单的 where 语句的条件匹配，Full-text 索引配合 match against 操作使用，而不是一般的 where 语句加 like
- R-Tree 索引（空间索引，用于对 GIS 数据类型创建 SPATIAL 索引）
-
|
索引 |
MyISAM引擎 |
InnoDB引擎 |
Memory引擎 |
|
B-Tree |
支持 |
支持 |
支持 |
|
Hash |
不支持 |
不支持 |
支持 |
|
R-Tree |
支持 |
不支持 |
不支持 |
|
Full-text |
支持 |
现支持 |
不支持 |
-
从物理存储角度
-
聚集索引 (clustered index)
- 该索引中键值的逻辑顺序决定了表中相应行的物理顺序；即只要索引是相邻的，那么对应的数据一定也是相邻的存放在磁盘上的
-
非聚集索引 (non-clustered index)
- 该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同；记录的物理顺序和逻辑顺序没有必然的联系
-
-
从逻辑角度
-
普通索引（单列索引）
- 最基本的索引，没有任何限制，MyISAM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引
-
唯一索引或非唯一索引
- 与普通索引类似，不同的是：索引列的值必须唯一，但允许有空值（注意和主键不同）；如果是组合索引，则列值的组合必须唯一，创建方式与普通索引类似
-
主键索引
- 特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在建表的时候同时创建主键索引
- 组合索引（最左前缀）
-
全文索引
-
备注：每个表只能有一个聚集索引，因为一个表中的记录只能以一种物理顺序存放；但是，一个表可以有不止一个非聚集索引。
2、索引区别
2.1、聚集索引（clustered index）
也叫聚簇索引。该索引中键值的逻辑顺序决定了表中相应行的物理顺序
数据行的物理顺序和列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能有一个聚集索引，聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增 id 充当聚集索引）
聚集索引确定表中数据的物理顺序。聚集索引类似电话簿，后者按照姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能有一个聚集索引；但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。
2.2、非聚集索引（nonclustered index）
该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以哟弄个有多个普通索引
3、回表查询和索引覆盖
3.1、回表查询
聚集索引与非聚集索引差异
-
InnoDB聚集索引的叶子节点存储行记录，InnoDB必须要有，且只有一个聚集索引
所以PK查询非常快，直接定位行记录
- 如果表定义了PK，则PK就是聚集索引
- 如果表没有定义PK，则第一个not Null unique列是聚集索引
-
否则，InnoDB会创建一个隐藏的row-id作为聚集索引
-
InnoDB非聚集索引的叶子节点存储主键值
注意：不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针
Demo:
假设：有table表
table( id PK, name KEY, sex, flag);
id : 聚集索引，name : 非聚集索引
表中有四条记录：
|
id |
name |
sex |
flag |
|
1 |
shenjian |
m |
A |
|
3 |
zhangsan |
m |
A |
|
5 |
lisi |
m |
A |
|
9 |
wangwu |
f |
B |
两个B+树索引分别如上图：
- id为PK，聚集索引，叶子节点存储行记录
- name为KEY，普通索引，叶子节点存储PK值，即id值
从普通索引无法直接定位行记录，那普通索引的查询过程
是怎么样的呢？
通常情况下，需要扫描两遍索引树
例：select * from table where name = 'lisi';
如粉色路径所示，需要扫描两遍所引述：
- 先通过普通索引定位到主键值id=5
- 再通过聚集索引定位到行记录
这就是所谓的回表查询，先定位主键值，再定位行记录；性能较扫一遍索引树更低。
3.2、索引覆盖
查询的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
MySQL官网，类似的说法出现在explain/desc查询计划优化章节，即explain/desc的输出结果Extra字段为Using index时，能够触发索引覆盖。
不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一颗索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
如何实现索引覆盖？
常见的方法：将被查询的字段，建立到联合索引中
1、能够命中 name 索引，索引叶子节点存储了主键 id，通过 name 的索引树即可获取 id 和 name，无需回表，符合索引覆盖，效率较高。
2、显示使用 name 索引，索引叶子节点存储了主键 id，但是 sex 字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过 id 值扫描聚集索引获取 sex 字段值，效率会降低。
3、如果把 (name) 单列索引升级为联合索引 (name, sex)就不同了，可以看到都能命中索引，无需回表。
4、何时使用聚集索引和非聚集索引
|
动作描述 |
使用聚集索引 |
使用非聚集索引 |
|
列经常被分组排序 |
使用 |
使用 |
|
返回某范围内的数据 |
使用 |
不使用 |
|
一个或极少不同值 |
不使用 |
不使用 |
|
小数目的不同值 |
使用 |
不使用 |
|
大数目的不同值 |
不使用 |
使用 |
|
频繁更新的列 |
不使用 |
使用 |
|
外键列 |
使用 |
使用 |
|
主键列 |
使用 |
使用 |
|
频繁修改索引列 |
不使用 |
使用 |
