MySQL 索引汇总1. MySQL 索引**** 索引（index）是帮助MySQL高效获取数据的数据结构（有序） M

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

1. MySQL 索引****

索引（index）是帮助MySQL高效获取数据的数据结构（有序）

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

①普通索引：普通索引允许被索引的数据列包含重复的值

②唯一索引：索引列的值必须唯一，但允许有空值

③单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

④复合索引：即一个索引包含多个列

①对查询频次较高，且数据量比较大的表建立索引

②组合索引挑选最常用、过滤效果最好的列的组合

③使用唯一索引，区分度越高，使用索引的效率越高

④使用短索引，构成索引的字段总长度比较短，提升索引访问的I/O效率

⑤尽可能建前缀索引，比如密码就适合建立前缀索引，因为密码几乎各不相同，前缀的标识度高，能缩短索引长度

MySQL5.6引入了索引下推优化，默认是开启的

例子：user表中（a,b,c）构成一个索引。 select * from user where a='23' and b like '%eqw%' and c like 'dasd'。

解释：

如果没有索引下推原则，则MySQL会通过a='23' 先查询出一个对应的数据。然后返回到MySQL服务端。MySQL服务端再基于两个like模糊查询来校验and查询出的数据是否符合条件。这个过程就设计到回表操作。

如果使用了索引下推技术，则MySQL会首先返回返回条件a='23'的数据的索引，然后根据模糊查询的条件来校验索引行数据是否符合条件，如果符合条件，则直接根据索引来定位对应的数据，如果不符合直接reject掉。因此，有了索引下推优化，可以在有like条件的情况下，减少回表的次数。

①hash索引适合等值查询，但是无法进行范围查询。

②hash索引没办法利用索引完成排序。

③hash索引不支持多列联合索引的最左匹配规则。

④如果有大量重复健值得情况下，hash索引的效率会很低，因为哈希碰撞问题。

聚簇索引与非聚簇索引是从文件存储的角度进行划分：

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据（InnoDB引入），在一张表上最多只能创建一个聚集索引，默认是主键(可以多列)

非聚簇索引：将数据和索引进行分开存储（MyISAM都是非聚簇索引）

对于主键索引和非主键索引，使用的数据结构都是 B+Tree，唯一的区别在于叶子结点中存储的内容不同：

主键索引的叶子结点存储的是一行完整的数据。

非主键索引的叶子结点存储的则是主键值。

当我们需要查询的时候：

如果是通过主键索引来查询数据，例如 select * from user where id=100，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。

如果是通过非主键索引来查询数据，例如 select * from user where username='javaboy'，那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。

对于第二种查询方式而言，一共搜索了两棵 B+Tree，第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree，这个过程就是所谓的回表。

不一定

如果查询的列本身就存在于索引中，那么即使使用二级索引，一样也是不需要回表的。

举个例子，我有如下一张表：

uname 和 address 字段组成了一个复合索引，那么此时，索引树的叶子节点中除了保存主键值，也保存了 address 和uname 的值。

查询语句 select uname, address from user where username='javaboy' 这里的返回结果在二级索引上就能提供，所以无需回表查询