面试之Mysql索引总结

前沿

对于从事软件开发同学来说数据库是非常的熟悉了，其中Mysql更是霸占了大半江山，而且近几年随着各大公司都在去Oracle，从而使Mysql的使用比例进一步提升，而且在面试中，索引出现的概率也是很高的，接下来我们来聊聊Mysql中索引方面的内容。

正文

我们在使用索引，首先要知道索引的作用，为啥要用索引等一系列问题。

索引的作用

索引能够加快查询速度，建立一个恰当的索引，能够提升几十倍的查询效率。我们知道万事都有好和不好，优点和缺点、优势和劣势等正反面，索引也是，索引的优点是加快查询速度，缺点是更新数据的时候，会更新索引树、从而使提交事务时间变长，我们下面基于Innodb引擎的mysql来讨论这些问题。

索引文件会保存到.idb文件里，详细的情况大家可以看我这边文章 

Mysql底层存储原理

索引数据结构

索引以B+树的形式保存在磁盘或者被加载到内存中，关于B+、B、B-、红黑树等，我们这次先不细聊，等以后有机会我们详细聊聊，他们有啥区别。B+树的优点是能够大幅度降低树的高度，从而提升查询速度，而且非叶子节点保存的是索引的值，主键索引树叶子节点保存的才是整条数据，而且数据直接是排好序的，直接通过指针进行关联，当在范围查询的时候，这点很重要，这也是Hash搜索引擎所不具备的优势，而非主键索引叶子节点保存的是主键ID，从而引入另外一个问题，回表操作，我们下面再想说。

索引分类

mysql索引分为几类：普通索引、联合索引、唯一索引、主键索引、覆盖索引、聚簇索引、非聚簇索引。

索引创建

创建一张学生表student：

create table t_student
(
    id       int auto_increment   primary key,
    stu_no   varchar(10) default '' not null comment '学生编号',
    stu_name varchar(10) default '' not null comment '学生姓名',
    stu_age  int         default 0  null comment '年龄',
    stu_sex  tinyint     default 0  null comment '0- 男 1- 女',
    constraint t_student_id_uindex   unique (id)
)comment '学生表';

创建普通索引：

alter table t_student add index idx_stu_no(stu_no) comment '学生编号普通索引';

创建联合索引：

alter table t_student add index idx_stu_no_name(stu_no,stu_name) comment '学生编号与名称联合索引';

或者使用

create index idx_stu_no_name on t_student (stu_no, stu_name)  comment '学生编号与名称联合索引';

可以看到创建普通索引与联合索引在索引语句上没啥区别，只是多一个stu_name，都用到的关键字index；

唯一索引

alter table t_student add unique index uniq_stu_no(stu_no) comment '学生编号唯一索引';

唯一索引用到了关键字unique index，而且唯一索引所在的列不能有null值、索引值是唯一的。

主键索引可以看作是一种特殊的唯一索引，唯一索引在一张表中可以创建多个，但是主键索引一张表只能有一个，一般是id。

一般在建表的时候一起加上，通过下面sql语句单独创建，也可以没有，如果不指定某一个索引为主键索引，系统会从已经创建的唯一索引中选择第一个作为主键索引，如果系统中没有唯一索引，那么系统会创建一列作为唯一索引，但是这列是隐藏的。

alter table t_student add primary key id(id) comment '学生编号唯一索引';

覆盖索引：

覆盖索引不是一类索引，而是在查询的情况下，所指定查询列的字段刚好是索引的列，无需进行回表，咱们举个例子：

我们创建一个

alter table t_student add index idx_stu_no_name(stu_no,stu_name) comment '学生编号与学生名称'；

联合索引，那么当我们的查询语句是

 select stu_no,stu_name where stu_no ='NO_100002' and stu_name='李四';

那么这种情况下会走覆盖索引，无需二次查询，直接返回。

索引特性

我们创建索引的主要目的，就像上面说的，是为了提高查询速度，那么如何才能提高速度的？那么就需要了解索引的特性。

索引遵循最左匹配原则，啥是最左匹配原则？还是用上面的例子进行说明，前提条件，stu表存在 idx_stu_no_name联合索引，大家看下面俩个查询语句：

语句1：

select * where  stu_name='李四';

语句2：

select * where stu_no ='NO_100002';

大家猜猜语句1、2哪个会用到索引，或者是那个不会用到索引？？

答案语句2会使用到上面的索引，而语句1不会使用的，这就是最左匹配原则，系统根据where条件去匹配索引树，因为联合索引是stu_no 与stu_name ，索引树在构造的时候，按照字段的值进行排序，按照按照stu_no排序，stu_no相同，再按照stu_name排序，所以这个时候去匹配索引的值得时候，语句1的where条件是stu_name ，处在索引字段的第二个位置，系统无法根据第二个值来判断条件值的顺序，从而也无法根据索引树快速的找到叶子节点的值，也就是语句1没有走索引。

语句2 where条件是stu_no，在索引字段的第一位（最左边），所以根据B+数的从上到下的查询方式，可以快速定位到匹配的叶子节点，可以走索引。

我们在举个例子，假如我们有一个三个字段（a、b、c）组成的联合索引，那么看下面的sql语句：

语句1：

select * from t_student where a = ‘xx’ and b = ‘xx’ and c = ‘xx’;

语句2：

 select * from t_student where a = ‘xx’ and b = ‘xx’;

语句3：

select * from t_student where a= ‘xx’ and c = ‘xx’;

语句4：

select * from t_student where a == ‘xx’;

语句5：

select * from t_student where b = ‘xx’;

语句6：

select * from t_student where b = ‘xx’ and c = ‘xx’;

语句7：

select * from t_student where c = ‘xx’;

哪些语句可以走索引呢？

语句1：可以走索引，这个毫无疑问；

语句2：可以走索引，只能用到字段 a 、b

语句3：可以走索引，但是只能用到字段a，根据最左匹配原则，字段c无法走索引，因为没有字段b，无法判断c的顺序。

语句4：可以走索引，但是只能用字段a。

语句5：根据最左匹配原则，无法走索引，因为无法判断b的顺序。

语句6：没有走索引，与语句5同理。

语句7：没有走索引，同理。

有人说，当我们通过a,b,c 列创建一个联合索引的时候，系统自动创建3个索引，分别是（a）、（a,b）、（a.b.c），我是不赞成这种说法，我理解的是只会创建一个联合索引，但是该联合索引的作用相当于上述3个索引，而不是说系统真正创建了3个索引，这点很好验证，我们通过explain + sql语句 就可以验证，这里就不验证了。

回表

先看一条查询语句：前提条件在a所在的列上创建索引

select * from t_student where a == ‘xx’;

我们根据条件a去匹配索引树a，找到其叶子节点，叶子节点保存的时候主键ID，但是这个时候我们查询的字段是*，根据主键ID再次查询主键索引树，匹配到叶子节点，该叶子节点保存的时候才是整条数据，这就叫做回表。通俗来讲，先根据非主键索引树找到叶子节点上的主键ID，再根据主键ID在主键树上找到叶子节点上的整行数据。