聚集索引 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 索引）

• 从物理存储角度
  • 聚集索引 (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 : 非聚集索引

  表中有四条记录：

两个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、何时使用聚集索引和非聚集索引

聚集索引 VS 非聚集索引

原文：https://www.cnblogs.com/sebastian-tyd/p/13580127.html