1.两类索引
MySQL 中的索引有很多中不同的分类方式,可以按照数据结构分,可以按照逻辑角度分,也可以按照物理存储分,其中,按照物理存储方式,可以分为聚簇索引和非聚簇索引。
主键索引,其实就是聚簇索引(Clustered Index);主键索引之外,其他的都称之为非主键索引,非主键索引也被称为二级索引(Secondary Index),或者叫作辅助索引。
对于主键索引和非主键索引,使用的数据结构都是 B+Tree,唯一的区别在于叶子结点中存储的内容不同:
- 主键索引的叶子结点存储的是一行完整的数据。
- 非主键索引的叶子结点存储的则是主键值。叶子结点不包含行记录的全部数据;非主键的叶子结点中,除了用来排序的key还包含一个bookmark;该书签存储了聚集索引的key。
这就是两者最大的区别。
查询的时候:
- 如果是通过主键索引来查询数据,例如
select * from user where id=100
,那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。 - 如果是通过非主键索引来查询数据,例如
select * from user where username='javaboy'
,那么此时需要先搜索 username 这一列索引的 B+Tree,搜索完成后得到主键的值,然后再去搜索主键索引的 B+Tree,就可以获取到一行完整的数据。(对username创建普通索引,由于是select*查询的是全部的数据,就会在叶子节点再次走可key的b+tree)
对于第二种查询方式而言,一共搜索了两棵 B+Tree,第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree,这个过程就是所谓的回表。
从上面的分析中我们也能看出,通过非主键索引查询要扫描两棵 B+Tree,而通过主键索引查询只需要扫描一棵 B+Tree,所以如果条件允许,还是建议在查询中优先选择通过主键索引进行搜索。
2.建立一张学生表,其中包含字段id设置主键索引、name设置普通索引、age(无处理),并向数据库中插入4条数据:("小赵", 10)("小王", 11)("小李", 12)("小陈", 13)
create table `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '名称',
`age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年龄',
primary key (`id`),
KEY `I_name` (`name`)
) ENGINE=InnoDB;
INSERT INTO student (name, age) VALUES("小赵", 10),("小王", 11),("小李", 12),("小陈", 13);
每一个索引在 InnoDB
里面对应一棵B+树,那么此时就存着两棵B+树。
可以发现区别在与叶子节点中,主键索引存储了整行数据,而非主键索引中存储的值为主键id, 在我们执行如下sql后
SELECT age FROM student WHERE name = '小李';
sql执行流程为:
- 在
name
索引树上找到名称为小李的节点id
为 03 - 从
id
索引树上找到id
为 03的节点 获取所有数据 - 从数据中获取字段命为
age
的值返回 12
在流程中从非主键索引树搜索回到主键索引树搜索的过程称为:回表,在本次查询中因为查询结果只存在主键索引树中,我们必须回表才能查询到结果,那么如何优化这个过程呢?引入正文覆盖索引
2.覆盖索引
就是把单列的非主键 索引 修改为 多字段 的联合索引, 在一棵索引数上。 就找到了想要的数据, 不需要去主键索引树上,再检索一遍 这个现象,称之为 索引覆盖.
覆盖索引(covering index ,或称为索引覆盖)即从非主键索引中就能查到的记录,而不需要查询主键索引中的记录,避免了回表的产生减少了树的搜索次数,显著提升性能。
- 如何使用是覆盖索引?
之前我们已经建立了表student
,那么现在出现的业务需求中要求根据名称获取学生的年龄,并且该搜索场景非常频繁,那么先在我们删除掉之前以字段name
建立的普通索引,以name
和age
两个字段建立联合索引,sql命令与建立后的索引树结构如下。
# 删除之前的非主键索引
alter table student drop index I_name;
# 添加非主键索引
alter table student add index I_name_age(name, age);
那在我们再次执行如下sql后:
select age from student where name = '小李';
流程为:
- 在
name
,age
联合索引树上找到名称为小李的节点- 此时节点索引(非主键索引)里包含信息
age
直接返回 12
- 如何确定数据库成功使用了覆盖索引呢?
当发起一个索引覆盖查询时,在explain
的extra
列可以看到using index
的信息:
这里我们很清楚的看到Extra
中using index
表明我们成功使用了覆盖索引。
覆盖索引避免了回表现象的产生,从而减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是性能优化的一种手段。
3.索引下推
在新版本中,在存储引擎中就会过滤掉一部分数据
老版本中,是在存储引擎中先取出来再在server中进行过滤
询表中“名字第一个字是张,性别男,年龄为10岁的所有记录”。
select * from tuser where name like '张%' and age=10 and ismale=1;
该语句在搜索索引树的时候,只能匹配到名字第一个字是‘张’的记录(即记录ID3),接下来是怎么处理的呢?
当然就是从ID3
开始,逐个回表,到主键索引上找出相应的记录,再比对age
和ismale
这两个字段的值是否符合。
但是!MySQL 5.6引入了索引下推优化,可以在索引遍历过程中,对索引中包含的字段先做判断,过滤掉不符合条件的记录,减少回表字数。
下面图1、图2分别展示这两种情况。
图 1 中,在 (name,age)
索引里面我特意去掉了 age
的值,这个过程 InnoDB 并不会去看 age 的值,只是按顺序把“name
第一个字是’张
’”的记录一条条取出来回表。因此,需要回表 4 次。
图 2 跟图 1 的区别是,InnoDB 在 (name,age)
索引内部就判断了 age 是否等于 10,对于不等于 10 的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID4、ID5 这两条记录回表取数据判断,就只需要回表 2 次。
如果没有索引下推优化(或称ICP优化
),当进行索引查询时,首先根据索引来查找记录,然后再根据where条件来过滤记录;在支持ICP优化后,MySQL会在取出索引的同时,判断是否可以进行where条件过滤再进行索引查询,也就是说提前执行where
的部分过滤操作,在某些场景下,可以大大减少回表次数,从而提升整体性能。