数据库监控与调优【八】—— 索引类型

索引类型

MySQL有多种索引类型，使用不同的角度，分类也有所不同。

程序猿之间沟通时，经常会聊到各种索引类型，比如你说：“这是一个组合索引”，他说“这是一个聚簇索引”，如果不了解这些术语，很容易鸡同鸭讲，不之所云。再者，面试时，也经常有变态面试官问诸如“MySQL的索引类型有哪些”的无营养问题，所以还是很有必要记录一下的。

先说结论：

从数据结构角度，可分为：

• B+树索引
• hash索引
• 空间数据索引（R-Tree索引）
• 全文索引

从功能逻辑角度，可分为：

• 普通索引
• 唯一索引
• 主键索引
• 组合索引
• 全文索引

从物理存储角度，可分为：

• 聚簇索引
• 非聚簇索引

数据结构角度

• B-Tree索引
• Hash索引
• 空间数据索引（R-Tree索引）
• 全文索引

功能逻辑角度

普通索引

普通索引是基础的索引，没有任何约束，主要用于提高查询效率。示例：

CREATE INDEX index_name ON table(column(length))                                                          

唯一索引

唯一索引就是在普通索引的基础上增加了数据唯一性的约束，索引列的值必须唯一，允许有NULL值。如果一个唯一索引同时还是个组合索引，那么表示列值的组合必须唯一。在一张数据表里可以有多个唯一索引。示例：

CREATE UNIQUE INDEX indexName ON table(column(length))                                                         

主键索引

主键索引是一种特殊的唯一索引，不允许有NULL值，并且一张表最多只有一个主键索引。

组合索引

指多个字段上创建的索引，使用组合索引时遵循最左前缀原则。示例：

CREATE index index_name CREATE table (column1, column2);                                                     

TIPS

最左前缀原则，指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1, column2, column3)，那么，如果查询条件是：

• WHERE column1 = 1、WHERE column1= 1 AND column2 = 2、WHERE column1= 1 AND column2 = 2 AND column3 = 3 都可以使用该索引；
• WHERE column1 = 2、WHERE column1 = 1 AND column3 = 3就无法匹配

全文索引

全文索引，用来检索文本中的关键字，用得很少，一般应对这种需求用Elasticsearch或者Solr之类的全文搜索引擎。

CREATE FULLTEXT INDEX ...                                                        

物理存储角度（聚簇索引、非聚簇索引）

这里的Key即辅助索引的字段值，而PK cols即辅助索引的字段值对应的主键值

主键索引的类型是聚簇索引,辅助索引的类型是都是非聚簇索引

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点不存储数据，而是指向对应数据块的指针。

InnoDB存储引擎的主键索引使用的是聚簇索引，而MyISAM存储引擎使用了非聚簇索引。

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

辅助索引（非聚簇索引）

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

• 聚簇索引：表数据和主键一起存储的，聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。而由于无法同时把数据行同时存放在两个不同的地方，所以一张表只有一个聚簇索引。
  • 聚簇索引的辅助索引：叶子节点不会保存引用的行的物理位置，而是保存行的主键值。
• 非聚簇索引：叶子节点存储的是数据块的指针。表数据和索引分开存储。查询时，先找到索引，再根据索引找到对应的数据行

聚簇索引 vs 非聚簇索引

聚簇索引优点：

• 查找效率理论上比非聚簇索引要高，但是插入、修改、删除操作的性能比非聚簇索引要低
• 范围查询方便

聚簇索引缺点：

• 插入速度严重依赖于插入顺序，因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
• 更新主键的代价很高，因为将会导致被更新的行移动。（当然一般不会更新主键）
• 聚簇索引增删改操作性能比非聚簇索引性能低

对于InnoDB：

• 主键使用聚簇索引，并且一张表有且只有1个聚簇索引。如果创建的表没有主键，则InnoDB会隐式定义一个主键来作为聚簇索引。
• 二级索引（非主键索引）叶子节点存储的是行的主键值，因此使用二级索引命中数据需要查询两次，先用二级索引搜索到主键，再用主键查找数据。

参考文档

聚集索引与非聚集索引的总结

说一下聚簇索引 & 非聚簇索引

Mysql聚簇索引和非聚簇索引原理（数据库）

MySQL中MyISAM和InnoDB对B-Tree索引不同的实现方式