MySQL索引(高级篇)—覆盖索引

 

 📌博主主页:一个肥鲇鱼

👉策略模式之Map+函数式接口:策略模式之Map+函数式接口

👉Bean转换工具:Mapstruct使用教程

目录

什么是索引?

索引有那些种类? 

InnoDB引擎不同的索引组织结构是怎样的呢?

执行流程

什么是覆盖索引?

如何使用是覆盖索引?

执行流程

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

注意

总结


什么是索引?

        索引的本质:索引是数据结构,可以简单理解为排好序的快速查找数据结构,满足特定查找算法,这些数据结构以某种方式指向数据, 这样就可以在这些数据结构的基础上实现 高级查找算法,通俗来说类似书本的目录,这个比方虽然被用的最多但是也是最恰如其当的,在查询书本中的某个知识点不借助目录的情况下,往往都找的够呛,那么索引相较于数据库的重要性也可见一斑。

索引有那些种类? 

   索引的种类本文章只罗列出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 = '小李';

执行流程

  1. 在name索引树上找到名称为小李的节点 id为03
  2. 从id索引树上找到id为03的节点 获取所有数据
  3. 从数据中获取字段命为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 = '小李';

执行流程

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

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

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

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

注意

        此时我们要注意到type列!!!
        常见的值依次从最优到最差分别为:system > const > eq_fef > ref > range > index > all;

        一般情况下,我们要保证效率的话,要优化我们的语句至少使其达到 range 级别,如果可能的话最好优化到 ref; range 级别一般用于范围查找,所以换句话说,除了范围查找,我们其它查询语句最好是优化到 ref 级别。

总结

        覆盖索引避免了回表现象的产生,从而减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是性能优化的一种手段。

  • 8
    点赞
  • 23
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
MySQL中,聚簇索引是一种物理索引和数据存储方式,它根据主键值对数据进行重新排序,并将数据在磁盘上连续存储。聚簇索引将所有记录存放在B树的叶子节点上。每个叶子节点的数据都是完整的记录数据,包括隐藏列。聚簇索引使用主键值的大小进行记录和页的排序,页内的记录按照主键的大小排序,形成一个单向链表。存放记录的页根据主键排序成为一个双向链表。聚簇索引的优点是数据查询更快,访问到索引后可以直接访问对应的索引列数据。聚簇索引对排序查找和范围查找非常快,因为数据是紧密相连的,减少了磁盘IO操作。然而,聚簇索引的插入数据非常依赖主键的顺序,插入顺序不按照主键顺序可能导致页分裂。更新主键会导致记录移动,因此更新主键的代价较大。非聚簇索引的访问需要两次索引查找,第一次是查找到主键,第二次根据主键值去查找记录。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *3* [MySQL高级-常见索引(聚簇索引/非聚簇索引/联合索引)](https://blog.csdn.net/weixin_45108959/article/details/128064293)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] - *2* [快速理解 Mysql 回表 索引覆盖 索引下推](https://blog.csdn.net/slslslyxz/article/details/108414525)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

一个肥鲶鱼

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值