前沿
对于从事软件开发同学来说数据库是非常的熟悉了,其中Mysql更是霸占了大半江山,而且近几年随着各大公司都在去Oracle,从而使Mysql的使用比例进一步提升,而且在面试中,索引出现的概率也是很高的,接下来我们来聊聊Mysql中索引方面的内容。
正文
我们在使用索引,首先要知道索引的作用,为啥要用索引等一系列问题。
索引的作用
索引能够加快查询速度,建立一个恰当的索引,能够提升几十倍的查询效率。我们知道万事都有好和不好,优点和缺点、优势和劣势等正反面,索引也是,索引的优点是加快查询速度,缺点是更新数据的时候,会更新索引树、从而使提交事务时间变长,我们下面基于Innodb引擎的mysql来讨论这些问题。
索引文件会保存到.idb文件里,详细的情况大家可以看我这边文章
索引数据结构
索引以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在主键树上找到叶子节点上的整行数据。