MySQL索引详解

数据库索引，是数据库管理系统中的一个有序的数据结构，能帮助我们更快的查询、更新表中的数据。

存储结构

Hash表

哈希表是键值对的集合，通过键可以快速取出对应的值；

哈希表的核心是哈希算法（也叫散列算法），通过哈希算法，算出key对应的index，再通过index找到对应的value。

但是哈希表会出现哈希冲突问题，如果不同的key算出的index相同，就会出现哈希冲突；我们通常使用链地址法解决，也就是将哈希冲突的value放在链表中。

为了减少哈希冲突，哈希表就需要将数据尽可能均匀的分布在哈希值集合中。

B树

B树也叫B-树，全称为多路平衡查找树，而B+树是B树的变体。

B树的所有结点既存放键也存放数据，但是所有的叶子节点都是相互独立的；

B+树只有叶子节点存放键和数据，其他节点只存放键，所有叶子节点都有一条引用链指向相邻的节点。

在MySQL中，MyISAM引擎和InnoDB引擎默认都是使用 B+Tree 作为索引结构，但是两者的实现方式略有不同：

在MyISAM引擎中，叶子节点的数据区域存放的是数据记录的地址；

而在InnoDB引擎中，其数据文件本身就是索引文件。

索引类型

主键索引

数据表中主键使用的索引就是主键索引；一张数据表只能有，且主键不能为空，不能重复。

在InnoDB引擎中，如果当前表没有指定主键，InnoDB引擎会先检查表中是否有唯一索引且不为空的字段，如果有则将此字段设置为默认主键，否则会自动创建一个6比特的自增主键。

二级索引

二级索引又叫辅助索引，在二级索引的叶子节点上存放的数据是主键，所以通过主键索引+二级索引定位到数据。

普通索引

ALTER TABLE table_name ADD INDEX index_name(column_name);

普通索引的作用就是增加查询速度，一张表中允许存在多个普通索引，并允许重复数据和空值；

唯一索引

ALTER TABLE table_name ADD UNIQUE INDEX index_name(column_name);

唯一索引是一种约束索引，一张表中允许存在多个唯一索引，被索引的列不能出现重复的数据，但是允许空值；

前缀索引

ALTER TABLE table_name ADD INDEX index_name(column_name(sub));

前缀索引只能实用于字符串类型的字段，前缀索引会取文本的前几个字符创建索引，所以会比普通索引占用的空间更少；

全文索引

ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_name);

全文索引主要是为了检索大文本字段的关键字，5.6之前只有MyISAM引擎支持，5.6之后InnoDB引擎也支持了。

联合索引

使用表中的多个字段创建索引，也叫组合索引或复合索引；

最左前缀匹配原则

在使用联合索引时，可以将区分度高的字段放在左侧；MySQL 会根据字段顺序，从左到右分别匹配联合索引中的字段，知道出现范围查询为止，比如<、>、between和like左通配符。

聚簇索引与非聚簇索引

聚簇索引

聚簇索引的索引结构和数据存放在一个文件中，比如主键索引就属于聚簇索引。

优点：

查询数据的速度很快，根据主键查询到对应的叶子节点之后，就能直接拿到数据。

缺点：

更新的代价很大，由于聚簇索引基于B+Tree实现，再插入数据时，为了保证叶子节点的有序性，需要进行排序，非自增主键排序时代价较大；

如果更新了主键的，需要对叶子节点进行移动，叶子节点里面存了数据，导致移动的代价很大。

非聚簇索引

聚簇索引的索引结构和数据分开存在两个文件中，比如二级索引就属于聚簇索引。

优点：

相比于聚簇索引更新数据的代价较小。

缺点：

需要回表查询数据，当查询到索引对应的主键或数据指针后，需要回表查询数据。

索引覆盖

如果一个索引叶子节点内拥有（覆盖）了所有需要查询的数据，那么此时就不需要回表，这就是索引覆盖。

索引下推

索引下推（Index Condition Pushdown，简称ICP）是MySQL 5.6实现的优化，将原本由服务层负责的数据筛选工作交给引擎层完成，从而减少回表的次数。

未使用索引下推：

使用了索引下推：

索引失效

使用!=、<、>；

查询的类型不一致；

使用函数；

使用运算符；

使用or；

使用前通配符模糊查询。