MySQL 覆盖索引详解

3A网络
• 阅读 275

1. 什么是索引?

索引(在 MySQL 中也叫“键key”)是存储引擎快速找到记录的一种数据结构,通俗来说类似书本的目录,这个比方虽然被用的最多但是也是最恰如其当的,在查询书本中的某个知识点不借助目录的情况下,往往都找的够呛,那么索引相较于数据库的重要性也可见一斑。

2. 索引的有哪些种类?

索引的种类这里只罗列出InnoDB支持的索引:主键索引(PRIMARY),普通索引(INDEX),唯一索引(UNIQUE),组合索引,总体划分为两类,主键索引也被称为聚簇索引(clustered index),其余都称呼为非主键索引也被称为二级索引(secondary index)。

3. 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);

这里我们设置了主键为自增,那么此时数据库里数据为

MySQL 覆盖索引详解

每一个索引在 InnoDB 里面对应一棵B+树,那么此时就存着两棵B+树。

MySQL 覆盖索引详解

可以发现区别在与叶子节点中,主键索引存储了整行数据,而非主键索引中存储的值为主键id, 在我们执行如下sql后

SELECT age FROM student WHERE name = '小李';

流程为:

  1. 在name索引树上找到名称为小李的节点 id为03
  2. 从id索引树上找到id为03的节点 获取所有数据
  3. 从数据中获取字段命为age的值返回 12

在流程中从非主键索引树搜索回到主键索引树搜索的过程称为:回表,在本次查询中因为查询结果只存在主键索引树中,我们必须回表才能查询到结果,那么如何优化这个过程呢?引入正文覆盖索引

4. 什么是覆盖索引?

覆盖索引(covering index ,或称为索引覆盖)即从非主键索引中就能查到的记录,而不需要查询主键索引中的记录,避免了回表的产生减少了树的搜索次数,显著提升性能。

5. 如何使用是覆盖索引?

之前我们已经建立了表student,那么现在出现的业务需求中要求根据名称获取学生的年龄,并且该搜索场景非常频繁,那么先在我们删除掉之前以字段name建立的普通索引,以name和age两个字段建立联合索引,sql命令与建立后的索引树结构如下

ALTER TABLE student DROP INDEX I_name;
ALTER TABLE student ADD INDEX I_name_age(name, age);

MySQL 覆盖索引详解

那在我们再次执行如下sql后

SELECT age FROM student WHERE name = '小李';

流程为:

  1. 在name,age联合索引树上找到名称为小李的节点
  2. 此时节点索引里包含信息age 直接返回 12

6. 如何确定数据库成功使用了覆盖索引呢?

当发起一个索引覆盖查询时,在explain的extra列可以看到using index的信息

MySQL 覆盖索引详解

这里我们很清楚的看到Extra中Using index表明我们成功使用了覆盖索引

总结:覆盖索引避免了回表现象的产生,从而减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是性能优化的一种手段,文章有不当之处,欢迎指正,以上环境都是部署在3A服务器上的,感兴趣的朋友可以自己部署尝试下

点赞
收藏
评论区
推荐文章
Wesley13 Wesley13
2年前
SQL优化看这一篇就够了
MySQL索引1\.定义索引是帮助MySQL高效获取数据的数据结构。索引内部存在一个键值和对应数据的物理地址,当数据很多的时候,索引文件会很大,所以一般以文件的形式存储于磁盘中,后缀名为.myi。2\.常用索引类型聚集索引次要索引覆盖索引
Wesley13 Wesley13
2年前
mysql 知识2
mysql知识2存储引擎1.InnoDB和MYISAM存储引擎的区别?索引1.什么是索引?你知道Mysql有哪些索引?分别介绍一下2.索引的优缺点?那些情况适合建索引那些情况不适合建索引?事务1.什么是事务,它有哪些特性?说一说事务的隔离级别,分别解决了什么问题?
Wesley13 Wesley13
2年前
mysql(索引)
MySQL索引MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的
Wesley13 Wesley13
2年前
mysql中的回表查询与索引覆盖
了解一下MySQL中的回表查询与索引覆盖。回表查询要说回表查询,先要从InnoDB的索引实现说起。InnoDB有两大类索引,一类是聚集索引(ClusteredIndex),一类是普通索引(SecondaryIndex)。InnoDB的聚集索引InnoDB聚集索引的叶子节点存储行记录,因此InnoDB必须要有且只有一个聚集索引。
Wesley13 Wesley13
2年前
mysql面试题
MySQL面试索引相关1.什么是索引?索引是一种数据结构,可以帮助我们快速的进行数据的查找.1.索引是个什么样的数据结构呢?索引的数据结构和具体存储引擎的实现有关,在MySQL中使用较多的索引有Hash索引,B树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B树索引.
Wesley13 Wesley13
2年前
MYSQL 索引类型
  在MYSQL中,索引是在引擎层中而不是服务器层实现的。所以并没有统一的索引标准:不同存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同(1)BTree索引    如果没有特别指明类型的话,那么就代指为BTree引擎,它使用BTree数据结构来
Wesley13 Wesley13
2年前
MySQL索引背后的数据结构及算法原理
摘要本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文
Wesley13 Wesley13
2年前
MySQL的存储引擎InnoDB选择了B+ 树
     我们知道数据的存储和检索是两个很重要的功能,当我们的数据量大了,怎么能快速的检索数据呢,答案是使用索引,可索引具体的技术实现有很多,选择哪一种呢,我就以mysql为例记录下它为什么选择了B树作为索引的实现方式。1. 索引简介  索引是一种用于快速查询行的数据结构,就像一本书的目录就是一个索引,如果想在一本书中找
Wesley13 Wesley13
2年前
MySQL 什么是索引?
_该文为《MySQL实战45讲》的学习笔记,感谢查看,如有错误,欢迎指正_<br<fontcolorblue一、索引简介</font<fontcolorred索引就类似书本的目录,作用就是方便我们更加快速的查找到想要的数据。</font索引的实现方式比较多,常见的有哈希表,有序数组,
Stella981 Stella981
2年前
B+Tree索引为什么可以支持千万级别数据量的查找——讲讲mysql索引的底层数据结构
MySQL索引底层数据结构索引是存储引擎快速找到记录的一种数据结构一、有索引与没索引的差距先来看一张图:!(https://static.oschina.net/uploads/img/202010/14150116_INlJ.png)左边是没有索引的情况,右边是作为