📌博主主页:一个肥鲇鱼
👉策略模式之Map+函数式接口:策略模式之Map+函数式接口
👉Bean转换工具:Mapstruct使用教程
目录
什么是索引?
索引的本质:索引是数据结构,可以简单理解为排好序的快速查找数据结构,满足特定查找算法,这些数据结构以某种方式指向数据, 这样就可以在这些数据结构的基础上实现 高级查找算法,
通俗来说类似书本的目录,这个比方虽然被用的最多但是也是最恰如其当的,在查询书本中的某个知识点不借助目录的情况下,往往都找的够呛,那么索引相较于数据库的重要性也可见一斑。
索引有那些种类?
索引的种类本文章只罗列出InnoDB引擎支持的索引:
- 主键索引(PRIMARY)
- 普通索引(INDEX)
- 唯一索引(UNIQUE)
- 组合索引
总体划分为两类,主键索引也被称为聚簇索引(clustered index),其余都称呼为非主键索引也被称为二级索引(secondary index)。
InnoDB引擎不同的索引组织结构是怎样的呢?
众所周知在InnoDB引用的是B+树索引模型,这里对B+树结构暂时不做过多阐述,我们对索引的种类划分为两大类主键索引和非主键索引,那么问题就在于比较两种索引的区别了。
我们建立一张学生表,其中包含字段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 = '小李';
执行流程
- 在name索引树上找到名称为小李的节点 id为03
- 从id索引树上找到id为03的节点 获取所有数据
- 从数据中获取字段命为age的值返回 12
在流程中从非主键索引树搜索回到主键索引树搜索的过程称为:回表,在本次查询中因为查询结果只存在主键索引树中,我们必须回表才能查询到结果,那么如何优化这个过程呢?带着疑点继续往下看!!!
什么是覆盖索引?
覆盖索引(covering index ,或称为索引覆盖)即从非主键索引中就能查到的记录,而不需要查询主键索引中的记录,避免了回表的产生减少了树的搜索次数,显著提升性能。 如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫 做覆盖索引
如何使用是覆盖索引?
假如现在出现的业务需求中要求根据名称获取学生的年龄,并且该搜索场景非常频繁,那么先在我们删除掉之前以字段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表明我们成功使用了覆盖索引。
注意
此时我们要注意到type列!!!
常见的值依次从最优到最差分别为:system > const > eq_fef > ref > range > index > all;
一般情况下,我们要保证效率的话,要优化我们的语句至少使其达到 range 级别,如果可能的话最好优化到 ref; range 级别一般用于范围查找,所以换句话说,除了范围查找,我们其它查询语句最好是优化到 ref 级别。
总结
覆盖索引避免了回表现象的产生,从而减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是性能优化的一种手段。