您的位置：首页 > 数据库 > 数据库其它  >  InnoDB主键索引树和二级索引树的场景分析

InnoDB主键索引树和二级索引树的场景分析

 更新时间：2022年3月11日 13:52  点击：1085 作者：BugMaker-shen

我们这里讨论InnoDB存储引擎，数据和索引存储在同一个文件student.ibd

场景1：主键索引树

uid是主键，其他字段没有添加任何索引

select * from student;

如果是这样查询，这表示整表搜索，从左到右遍历叶子节点链表，从小到大访问

select * from student where uid<5;

如果是这样查询，这表示范围查询，就直接在有序链表中遍历搜索就可以了，直到遍历到第一个不小于5的key结束遍历

select * from student where uid=5;

如果是这样查询，这表示等值查询，在索引树上进行二分查找即可

由于name没有索引，于是做整表搜索

select * from student where name='linfeng';

场景2：二级索引树

uid是主键，以name创建了普通索引（二级索引）

以name为索引构建的索引树，称为辅助索引树，也叫做二级索引树。key是辅助索引字段name的值，然后还有外加uid主键的值

在辅助索引树上，key是辅助索引的值，也就是name；data数据值是所在记录行的主键值（PRIMARY KEY），也就是uid（并不是表的一行数据）

分析语句1：

select name from student where name='linfeng';

因为过滤字段是name且 只select了name一个字段，name有索引，索引树上直接就有，所以从name的二级索引树上去等值匹配linfeng

分析语句2：

select uid,name from student where name='linfeng';

这种情况select的是name和uid，而这些在二级索引树上也是直接就有，所以搜索二级索引树就完事了。

分析语句3：

select * from student where name='linfeng';

这种情况下就涉及到回表了，这是一个很重要的概念。由于name字段有索引，所以我们会到name字段构建的二级索引树上去查找。但二级索引树没有linfeng这个人所有的信息，所以完整的查询过程应该是这样的：

  • 用linfeng到二级索引树上进行匹配，拿到二级索引树上存储的uid
  • 然后拿着这个uid去主索引树上去匹配，最后拿到linfeng的所有信息（回表）


而这个回表意味着更多的磁盘I/O，会影响效率，如果业务只需要uid、name，就别写select *了，这样可以避免回表

分析语句4：

我们删除name的索引后执行以下语句

select * from student where age=20 order by name;

没有用到索引，还使用外部排序了。此外我们还看到using filesort，这时需要优化了。

我们的过滤条件是age，先给age添加索引，看看行不行

可以看到，age命中索引了，查询age所在的索引树。由于我们写的是select *，依然存在回表。还有using filesort，因为使用age=20查询到的结果是多个，然而name此时是没有顺序的，所以还需要再进行外部排序。

那能不能通过给name加载索引来解决问题呢？

不能，因为一次SQL执行只能用到1个索引，搜索了这个字段的索引树就不会再去搜索另一个字段的索引树了，因为加载索引是要耗费磁盘I/O的，查找多个索引树就太慢了！

分析：既然索引树上只能存自己建立的索引字段以及主键，那我们把需要查询的字段都设置成索引不就好了？

解决方法：我们可以在二级索引树上的key：age+name，形成联合索引，先按age排序，age相同了，再按name排序

再次select *

这时候就使用到联合索引了，而且没有using filesort，这次是这样查询的：

先用age=20在辅助索引树上查找，如果数据足够会找到多个结果，这个结果就是已经排好序的，不需要再using filesort

我们现在直接用第二个字段name作为过滤条件

我们看到这里没有用到索引，因为我们用(age,name)创建索引，是先按age排序，再按name排序。如果我们只用name作为过滤条件，这就没有办法使用索引匹配了，因为是优先用age排序。

所以我们经常说，多列索引一定要使用到第1个字段，这样才能用到索引！

在建立(age,name)联合索引的情况下，以下操作不回表（到二级索引树上搜索，再去主索引树上搜索）：

  • select age
  • select age, name
  • select uid,age,name

以下操作要回表

  • select *
  • select age,name,sex

到此这篇关于InnoDB主键索引树和二级索引树的文章就介绍到这了,更多相关InnoDB索引树内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞！

原文出处：https://blog.csdn.net/qq_42500831/article/details/123396865

[!--infotagslink--]

相关文章

  • 深入谈谈MySQL中的自增主键

    这篇文章主要给大家介绍了关于MySQL中自增主键的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧...2021-02-05

  • MySQL的InnoDB引擎入门学习教程

    MySQL发展到今天，InnoDB引擎已经作为绝对的主力，除了像大数据量分析等比较特殊领域需求外，它适用于众多场景。然而，仍有不少开发者还在“执迷不悟”的使用MyISAM引擎，觉得对InnoDB无法把握好，还是MyISAM简单省事，还能支持快...2015-11-24

  • 简单了解mysql InnoDB MyISAM相关区别

    这篇文章主要介绍了简单了解mysql InnoDB MyISAM相关区别,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下...2020-09-05

  • MySQL禁用InnoDB引擎的方法

    一、确定版本查看MySQL版本复制代码 代码如下:mysql -V或者可以登录MySQL使用select version();或status;命令查看二、开始工作关闭MySQL复制代码 代码如下:service mysql stop如果上面的命令无法关闭MySQL，则使用kill...2014-05-31

  • Mysql主键UUID和自增主键的区别及优劣分析

    这篇文章主要介绍了Mysql主键UUID和自增主键的区别及优劣分析，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04

  • 获取DataList控件的主键和索引实用图解

    一是在DataList控件添加一个DataKeyField，以便获取到它的主键值，另外还添加了两个铵钮及一个Label标答，用来显示选择结果，真正将来你也许用不上标签，因为获取到结果之后，就可以进行你想的要事情了...2021-09-22

  • MySQL InnoDB ReplicaSet(副本集)简单介绍

    这篇文章主要介绍了MySQL InnoDB ReplicaSet(副本集)的相关资料，帮助大家更好的理解和学习使用MySQL，感兴趣的朋友可以了解下...2021-04-23

  • MySQL 处理插入过程中的主键唯一键重复值的解决方法

    本篇文章主要介绍在插入数据到表中遇到键重复避免插入重复值的处理方法，主要涉及到IGNORE,ON DUPLICATE KEY UPDATE,REPLACE的相关知识，感兴趣的朋友一起学习吧...2016-04-18

  • MySQL提示The InnoDB feature is disabled需要开启InnoDB的解决方法

    这篇文章主要介绍了MySQL提示The InnoDB feature is disabled需要开启InnoDB的解决方法,简单分析了MySQL数据库开启InnoDB引擎的实现技巧,需要的朋友可以参考下...2016-01-15

  • mysql innodb的监控（系统层，数据库层）

    这篇文章主要介绍了mysql innodb的监控（系统层，数据库层）的相关资料,需要的朋友可以参考下...2017-04-26

  • MySQL InnoDB表空间加密示例详解

    这篇文章主要给大家介绍了关于MySQL InnoDB表空间加密的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用MySQL具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧...2020-08-17

  • MySQL存储引擎简介及MyISAM和InnoDB的区别

    MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改 STORAGE_ENGINE 配置变量，能够方便地更改MySQL服务器的默认存储引擎。 InnoDB：用于事务处理应用程序，具有众...2014-05-31

  • 详解MySQL InnoDB存储引擎的内存管理

    这篇文章主要介绍了详解MySQL InnoDB存储引擎的内存管理，帮助大家更好的理解和学习使用MySQL数据库，感兴趣的朋友可以了解下...2021-04-08

  • MySQL数据库innodb启动失败无法重启的解决方法

    这篇文章给大家分享了MySQL数据库innodb启动失败无法重启的解决方法，通过总结自己遇到的问题分享给大家，让遇到同样问题的朋友们可以尽快解决，下面来一起看看吧。...2016-10-02

  • 详解MySQL自增主键的实现

    现在大部分的软件开发都离不开数据库。而mysql也是经常会用到的一个数据库。mysql数据库中有一个主键生成规则，就是自增。也是我们经常会用到的。本文就来介绍一下...2021-09-06

  • mysql主键id的生成方式(自增、唯一不规则)

    本文主要介绍了mysql主键id的生成方式，主要包括两种生成方式，文中通过代码示例介绍的非常详细，感兴趣的可以了解一下...2021-09-06

  • InnoDb 体系架构和特性详解 (Innodb存储引擎读书笔记总结)

    下面小编就为大家带来一篇InnoDb 体系架构和特性详解 (Innodb存储引擎读书笔记总结)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧...2017-04-03

  • MySQL事务数据库(InnoDB类型)的安装方法

    MySQL数据库分二种类型，一种是传统的数据表格式，一种是支持事务处理的数据表格式(InnoDB,BDB,其中以InnoDB为主),下面我介绍一下关于MySQL事务处理数据库的安装及使用方...2016-11-25

  • MyBatis-Plus Sequence主键的实现

    这篇文章主要介绍了MyBatis-Plus Sequence主键的实现，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧...2020-12-24

  • 浅析MySQL 主键使用数字还是uuid查询快

    在实际开发中可能会生成uuid作为主键那么疑问来了，到底MySQL 主键使用数字还是uuid查询快呢？下面小编给大家带来实例代码给大家详细介绍，感兴趣的朋友跟随小编一起看看吧...2021-08-09