MySql索引详解

索引是数据库里的一个用于排序的数据结构，查询语句使用到索引的时候，就不需要扫描整张表去查找数据，可以提高查询的速度。但是创建和维护索引需要耗费一定的时间，同时也会影响插入的速度。

mysql主要使用B+树作为索引结构。

mysql的索引结构主要有hash索引和B+树索引两种，hash索引是基于hash表实现的，缺点很多，所以B+树索引更常用。

hash索引缺点：1.无序，则无法排序，范围查询开销大；2.key值重复率高时，出现大量哈希冲突，拉链很长，索引效率接近于遍历。

聚集索引 vs 非聚集索引

• 聚集索引的聚集是指索引关键字的顺序与表中数据记录的物理顺序一致。因为真实数据的物理顺序只能有一种，因此在一张表上最多只能创建一个聚集索引，一般用主键构建聚集索引。
• 非聚集索引的非聚集是指索引关键字的顺序与表中数据记录的物理顺序不一致。

二者优缺点对比：

聚集索引	非聚集索引
按聚集索引进行范围查询的效率高，因为索引逻辑顺序和数据的物理顺序一致，可以读写同一个磁盘块。	按非聚集索引进行范围查询的效率低，可能会频繁切换访问不同的磁盘块。
聚集索引增删数据记录效率低，因为要保持表中记录的物理顺序与索引的顺序一致，把记录插入到相应数据页后，后面的数据还要后移、分页。（如果主键是自增id，那么插入新记录的效率会比较高。）	非聚集索引的顺序与数据记录的物理顺序无关，增删数据记录的效率比较高。

MyISAM和InnoDB引擎都是以B+树作为索引结构，但二者有所区别：

• InnoDB主键索引树的叶子节点存储的是关键字和对应的数据记录，所以主键的逻辑顺序和数据记录的物理顺序相同，因此InnoDB的主键索引也是聚集索引。（InnoDB要求数据记录按照主键聚集，因此必须要有主键，不指定主键的话InnoDB会自动指定或者生成主键）
• MyISAM主键索引树的叶子节点存储的是关键字和指向对应数据记录的指针，所以主键的逻辑顺序和数据记录的物理顺序不同，因此MyISAM的主键索引是非聚集索引。

原理剖析：InnoDB与MyISAM 聚集索引与非聚集索引

InnoDB的B+树索引

InnoDB的主键索引是聚集索引，辅助索引是非聚集索引。（因为辅助索引的逻辑顺序跟对应数据的物理顺序不相同）

主键索引

InnoDB是聚集索引，其主键索引树的叶子节点存储的是主键和具体的数据记录。由下图可见，InnoDB的主键索引的非叶结点是额外创建的索引结点，而叶子结点其实就是数据表中的数据记录。

辅助索引

InnoDB的辅助索引使用非主键构建索引树，其叶子节点存储的是关键字和对应数据记录的主键值，所以通过辅助索引查找到主键值后，还需要根据这个主键值去主键索引中查找才能得到具体的数据记录，这就叫做索引回表。因此辅助索引实际上要查两次索引，而主键索引只需要查找一次索引。

MyISAM的B+树索引

MyISAM的主键索引和辅助索引都是非聚集索引。

主键索引

主键索引就是以主键来构建索引，由于MyISAM是非聚集索引，所以主键索引树的叶子节点存储的是主键和指向数据记录的指针。由下图可见，MyISAM的索引文件和数据文件是完全分开存储的。

辅助索引

一个数据表只有一个主键索引，但可以有多个辅助索引。MyISAM的辅助索引使用非主键构建索引树，其叶子节点存储的也是指针。

索引分类/类型

注意SQL语句中使用索引的部分主要是where子句，去索引上查找到符合条件的数据记录，然后再取出select子句中所需的字段。

• 普通索引：用表中普通列（任意一列）构建的索引。（不应该选择重复值过多的列作为索引）

• 唯一索引：用一列来构建索引，则列值必须唯一。用多列组合构建索引，则列值组合必须唯一。

• 主键索引：一种特殊的唯一索引，根据主键构建索引（主键已经默认值唯一）。

• 全文索引：使用倒排索引来实现，比如假设每条数据记录存储的是一篇文章，那么可以摘取其中的词语（借助分词系统）建立全文索引，然后就可以根据词语查到它出现在哪几篇文章中。

• 组合索引：用多个列组合构建的索引。如果遵循“最左前缀”的原则，那么构建三个列(a,b,c)的组合索引时，就相当于同时构建了a的单列索引，ab的组合索引和abc的组合索引。因此应该把查询最频繁的列a放最前面。（组合索引的B+树的叶子节点是有序的（a,b,c），因为多列组合索引是先按照第一列进行排序，然后在第一列属性值相同时再根据第二列属性进行排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了。）所以如果不是在前面列的基础上而是单看后面某一列，索引是失效的。

• 覆盖索引：索引本身就包含了所有需要查询的字段的值（比如范围查询），那么查完索引数拿到叶子节点（a,b,c）后，就直接可以返回索引数据，不需要索引回表。（注意InnoDB的索引是主键，他的数据记录只是直接跟在后面，并没有构建成索引，所以InnoDB只有在select查询只查主键的值的情况下才算覆盖索引）

• 稠密索引：为每个记录建立索引。

• 稀疏索引：只为部分记录建立索引，要求数据是按照这个索引有序的，这样才能查找到没有建立索引的记录。

建立索引的Tips：数据库查询一般只用到一个索引，因为比起分析多个索引树，还不如只分析一个索引树然后在符合条件的记录中顺序扫描，判断是否符合剩下的条件。因此如果经常需要同时对多个字段进行AND查询,那么建立两个单独索引不如建立一个组合索引，因为两个单独索引通常数据库只能使用其中一个，而使用组合索引因为索引本身就对应到两个字段上的，效率会有很大提高。

索引失效

并不是说建立了索引，并且查询条件使用了该索引列就一定会使用到该索引。如果优化器认为走索引还不如全表扫表的话，就会选择全表扫描。

比如

1. WHERE age+10=30，由于需要对该列所有数据进行计算，因此不会使用索引。
2. 查询只使用到组合索引中的后面的列（没有使用第一列），则索引失效。