一文读懂Mysql索引，什么是B树，什么又是B+树

1. 前言

​ MySQL：一个如雷贯耳的名词，相信大家都不陌生，也都会用，因为早在学习Javaweb的时候，我们就用到了MySQL数据库。在那个阶段，MySQL对我们来说似乎只是一个存储数据的好东西，存储时一股脑往里边塞，查询时也是盲目的全表查询。

​ 诚然，现阶段能够带来优化的技术多之又多，比如：Redis、es搜索引擎，来分担MySQL的压力，又比如Mq、Kafka等中间件，实现异步削峰。但在实际的项目中，关于mysql的优化也是十分重要的。

​ 提到mysql优化， 必然绕不开索引。而本篇文章也是以共同探究、共同进步为目标，给大家带来关于索引底层的一些原理以及实现等等问题，给有需要的朋友一个参考。希望对大家有所帮助。

2. 概括

具体谈索引之前，先简单概括下本次分享的主要内容

• 1、索引是什么
• 2、索引优劣势
• 3、索引结构
• 4、B-tree和 B+tree
• 5、索引都有哪些分类
• 6、如何使用索引，有哪些注意事项

3. 详解

3.1 索引定义

​ MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。

​ 除了数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。其实简单来说，索引就是一个排好序的数据结构。如下面的示意图所示 :

​ 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意：逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

​ 但实际上呢，我们MySQL中并不是用二叉查找树来存储，主要是因为二叉查找树，此处一个节点只能存储一条数据，而一个节点呢，在MySQL里边又对应一个磁盘块，这样我们每次读取一个磁盘块，只能获取一条数据，效率特别的低，所以我们会想到采用B树这种结构来存储。关于这一点，后续会详细讲解。

3.2 索引优劣势

• 优势

  • 加快查找和排序的速率，降低数据库的IO成本以及CPU的消耗
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

• 劣势

  • 索引实际上也是一张表，保存了主键和索引字段，并指向实体类的记录，本身需要占用空间

  • 虽然增加了查询效率，但对于增删改，每次改动表，还需要更新一下索引

    新增：需要在索引树中新增节点 
    删除：索引树中指向的记录可能会失效，意味着这棵索引树很多节点，都是失效的 
    改动：索引树中节点的指向可能需要改变
    

3.3 索引结构

索引与引擎

​ 我们需要知道的是：索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，而且也不是所有的引擎都支持所有的索引类型。大致列一下常见的索引类型：

• BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引（InnoDB 从Mysql5.6版本开始支持全文索引）

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持如下：

索引	INNODB引擎	MYISAM引擎	MEMORY引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

​ 而我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

​ 想必通过以上内容，我们会发现一个不可忽略的关键词：tree（树），关于树，我们可以简单概括为以下几种：二叉树、B树、B+树、红黑树等等。本次分享重点讲解B+树，关于其他类型的树定义以及区别，感兴趣的可以自行学习。

B+树的索引结构解释

​ 任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们不妨总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，B+树应运而生:

​ 蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（红色所示）和指针（黄色所示）如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

​ 真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项

B+树的查找过程

如上图所示，如果要查找数据项29，要经历那些过程呢？

1、首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针

2、通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针

3、通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

总结：

• 通过上面的分析，我们知道索引字段越小，数据项的数量越多，树的高度越低。这也是为什么每个数据项都要求索引字段要尽量的小的原因。并且b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。

• 3层的B+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的。如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本不敢想象。

B-tree和 B+tree 树

B-tree

​ 又称为：多路平衡查找树，B树中所有结点的孩子个数最大值称为B树的阶，用m表示。一颗m阶B树是一颗空树或者是符合一系列条件的m叉树。索引值和具体data分布在整棵树中

B树的性质

• 1、一颗m阶B树，每个节点最多有m叉，那么该节点最多有m-1个关键字(元素)
• 2、除根节点外，其它非叶子节点至少有(m/2的结果并向上取整）个孩子节点
• 3、若根节点不是叶子节点，则根节点至少有两个孩子节点。即根节点最少有1个关键字
• 4、每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它
• 5、所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。

单看理论有点抽象，假设我们现在有一颗3阶B-tree，以图为例，我们基本上一看就能明白：

总结：m阶B树，每个节点最多有m叉，那么该节点最多有m-1个关键字(元素)，同时一棵m阶(m叉)B tree满足：

每个节点最多m个孩子、孩子个数：ceil(m/2) 到 m 、关键字个数：ceil(m/2)-1 到 m-1

ceil表示向上取整，例如：ceil(2.3)=3

B树的添加算法

1，根据要插入的key的值，找到叶子结点并插入

2，判断当前结点key的个数是否小于等于m，若满足则结束，否则进行第3步

3，以结点中间的key为中心分裂成左右两部分，然后将这个中间的key发到到父结点中，这个key的左子树指向分裂后的左半部分，这个key的右子树指向分裂后的右半部分，然后将当前结点指向父结点，继续进行第3步

添加算法的关键在于，一个节点分裂成三个节点，并且父节点会向上插入

B树的删除算法

在叶子节点中直接删除关键字。如果删除后该节点的关键字个数小于m-1，则需要进行调整。

• 如果删除关键字后，其所在节点的关键字个数仍在允许的范围内（ceil(m/2)-1 到 m-1），则无需进一步处理
• 如果删除后该节点的关键字个数小于最小节点数，则需要进行调整，而调整的内容包括：
  • 借关键字：从相邻兄弟节点借一个关键字来填充空缺。
  • 合并节点：如果借不到关键字，则考虑合并节点，使得父节点的关键字个数减少
  • 递归调整：删除操作可能会影响父节点，因此可能需要递归地进行调整直到根节点
• 如果删除操作导致根节点为空（所有关键字都删除了），则更新根节点为新的根

案例

问题：从空树开始构造一颗3阶B树，依次插入的关键字为30、26、85、7、62、70。画出该B树的生成过程，最后删除关键字：62

注意：我们必须保证不破坏m阶B树的性质，然后再以下一下生成过程：

• 第一步：先创建30

• 第二步：由于是3阶B 树，最多只能2个节点，所以26和30在一起

• 第三步：进来85，就要开始分裂了，因为要保持性质4（有序），所以30作为中间上位，26保持，85去到右边。即：中间位置上位，然后左边留在旧节点，右边去到新结点

• 第四步：进来7，和26在一起

• 第五步，进来62和85在一起

• 第六步，进来70，70刚好是中间位置，上位和30在一起，然后62保持，85又去分一个新节点出来（中上、左留、右新）

• 最后删除62：删除62以后，从相邻兄弟节点借节点，26不符合30-70的排序要求，85节点借完不符合关键词数量，所以考虑合并节点，使得父节点的关键字个数减少。拿到30来填充空缺，随之26需要进行上位，最终结果如下：

B+tree

B+树的性质

​ 1、B+树有两种类型的节点：分别是内部结点（也称索引结点）和叶子结点。内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存储在叶子节点上

​ 2、内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。

​ 3、每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序连接。

​ 4、父节点存有右孩子的第一个元素的索引。

B+树的添加算法

• 若为空树，创建一个叶子结点，然后将记录插入其中，此时这个叶子结点也是根结点，插入操作结束。

• 针对叶子类型结点：根据key值找到叶子结点，向这个叶子结点插入记录。插入后，若当前结点key的个数小于等于m-1，则插入结束。否则将这个叶子结点分裂成左右两个叶子结点，左叶子结点包含前m/2个记录，右结点包含剩下的记录，将第m/2+1个记录的key进位到父结点中（父结点一定是索引类型结点），进位到父结点的key左孩子指针向左结点,右孩子指针向右结点。将当前结点的指针指向父结点，然后执行第3步。

• 索引类型结点的分裂算法和B树相同

B+树的删除算法

1，删除叶子结点中对应的key。删除后若结点的key的个数符合规范，则删除操作结束,否则执行第2步。

2，若兄弟结点key有富余（大于Math.ceil(m-1)/2 – 1），向兄弟结点借一个记录，同时用借到的key替换父结（指当前结点和兄弟结点共同的父结点）点中的key，删除结束。否则执行第3步。

3，若兄弟结点中没有富余的key,则当前结点和兄弟结点合并成一个新的叶子结点，并删除父结点中的key（父结点中的这个key两边的孩子指针就变成了一个指针，正好指向这个新的叶子结点），将当前结点指向父结点（必为索引结点），执行第4步（第4步以后的操作和B树就完全一样了，主要是为了更新索引结点）。

4，若索引结点的key的个数符合规范，则删除操作结束。否则执行第5步

5，若兄弟结点有富余，父结点key下移，兄弟结点key上移，删除结束。否则执行第6步

6，当前结点和兄弟结点及父结点下移key合并成一个新的结点。将当前结点指向父结点，重复第4步

B+树优势

• B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B 树查询时间复杂度不固定。取决于key在树中的位置
• B+树叶叶子节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。
• B+树 由于内节点无 data，每个节点能索引的范围更大更精确
• MySql索引数据结构在原B+Tree的基础上进行了优化：增加一个指向相邻叶子节点的链表指针(整体类似一个双向链表的结构)，就形成了带有顺序指针的B+Tree，提高区间访问的性能
• 从二叉查找树过渡到B+树，有一个显著的变化就是，一个节点可以存储多个数据了，相当于一个磁盘块里边可以存储多个数据，大大减少了我们的 IO次数

一个B+树中大概能存放多少条索引记录

MySQL中一个页存放的记录数量是非常大的（默认16KB），假设指针与键值忽略不计（或看做10个字节），数据占 1 kb 的空间：

• 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 16 条记录。
• 如果B+树有2层，最终能存放 1600×16=25600 条记录（因非叶子节点存放的是指针，所以能存放1600）。
• 如果B+树有3层，最多能存放 1600×1600×16=40960000 条记录。

所以由上可知，如果存储千万级别的数据，只需要三层就够了

3.4 索引原理

​ B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。从上一节中的我们可以知道B-tree每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。而在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

​ 将上一节中的B-Tree示例图优化为B+Tree，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示： 通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

​ 数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index)。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据（接下来会说明）。

3.5 索引分类

​ 在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。

假设，我们有一个表：person 主键列：id，表中有字段：age和name，并且在name上有索引。那这个表的建表语句是：

create table person( 
  id int primary key, 
  age int not null,  
  name varchar(16), 
  index (name) )engine=InnoDB; 

初始化几条数据后：

id	name	age
1	张三	10
2	李四	20
3	王五	30
4	赵六	40

通过描述我们可知，该表一共有两种索引：分别是主键（ID）以及name，可以划分为主键索引和非主键索引（辅助索引）

主键索引

​ 数据表的主键列使用的就是主键索引，且会默认创建，这也是为什么，我们还没学索引的时候，就经常听到说根据主键查会快一点，是因为主键本身就建好了索引。主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引。

如下图：

辅助索引

辅助索引又分为了如下几种（时间关系不再一一赘述）：

• 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
• 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引

辅助索引的叶子节点内容是主键的值。在InnoDB里，辅助索引也被称为二级索引。

如下图：

根据上面的索引结构，我们来讨论一个问题：基于主键索引和辅助索引的查询有什么区别？

• 如果语句是select * from person where id=2，即主键查询方式，则只需要搜索id这棵B+树；
• 如果语句是select * from person where name='张三'，即普通索引查询方式，则需要先搜索name索引树，得到id的值为1，再到id索引树搜索一次。这个过程称之为回表。

也就是说，基于辅助索引的查询需要多扫描一棵索引树。因此，我们在应用中应当尽量使用主键查询。但也有一种例外虽然是辅助索引，但是却不会触发回表，这种我们称之为：覆盖索引。

覆盖索引

我们可以思考一下，什么情况不会触发回表？

比如：我们要执行的sql如下：select id,name from person where name = '张三'; 不难发现，此时是能够命中name索引树的，而叶子节点存储了主键id，通过name索引树即可获取我们想要的查询字段，那此时就无需回表，符合覆盖索引的条件，效率较高

索引下推

所谓下推，顾名思义，其实是推迟我们的回表操作，MySQL不会轻而易举让我们去回表，因为很浪费，所以MySql 5.6之后引入了索引下推。什么意思呢？来看下边这个例子：

我们建立了一个复合索引(name，status，address)，索引中也是按这个字段来存储的，类似图中这样：

复合索引树(只存储索引列和主键用于回表)

name	status	address	id(主键)
张三1	0	1	1
张三2	1	1	2

我们执行这样一条语句：

SELECT name FROM person WHERE name like '张三%' and status ='1' ;

1. 首先我们在复合索引树上，找到了第一个以张三开头的name： 张三1
2. 此时如果使用了索引下推，那么不会立即回表(回到主键索引树搜索的过程，我们称为回表)，而是先在复合索引树判断status是否=1，此时status=0，我们直接就不回表了，直接继续找下一个以张三开头的name
3. 找到第二个：张三2，判断status=1，则根据id=2去主键索引树上找，得到所有的数据

这种先在自身索引树上判断是否满足其他的where条件，不满足则直接pass掉，不进行回表的操作，就叫做索引下推。

3.6 索引设计原则

针对表

查询频次高，且数据量多的表

针对字段

最好从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合

其他

1. 最好用唯一索引，区分度越高，使用索引的效率越高

2. 索引不是越多越好，维护也需要时间和空间代价，建议单张表索引不超过 5 个

   因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能
   

​ 比如：我们创建了三个单列索引，name，status，address ​ 当我们where中根据status和address两个字段来查询时，数据库只会选择最优的一个索引（辨识度最高的索引列），不会所有单列索引都使用

3. 使用短索引,索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

4. 利用最左前缀规则，比如有N个字段，我们不一定需要创建N个索引，可以用复合索引。也就是说，我们尽量创建复合索引，而不是单列索引

   创建复合索引:
   	CREATE INDEX idx_name_address_status ON person(name,address,status);
   
   就相当于
   	对name 创建索引
   	对name , address 创建了索引
   	对name , address, status 创建了索引
   

最左前缀

​ 所谓最左前缀，可以想象成一个爬楼梯过程，假设我们有一个复合索引：name，status，address，那这个楼梯由低到高依次顺序是：name，status，address，最左前缀，要求我们不能出现跳跃楼梯的情况，否则会导致我们的索引失效：

1. 按楼梯从低到高，无出现跳跃的情况--此时符合最左前缀原则，索引不会失效

2. 出现跳跃的情况

   • 直接第一层name都不走，都失效

   • 走了第一层，但是后续直接第三层，那么只有跳跃之前的有效，也就是只有name有效

   • 另外，这个顺序并不是由我们where中的排列顺序决定，比如：

     where name='张三' and status='1' and address='北京市' 
     where status='1' and name='张三' and address='北京市'
     

     这两个尽管where中字段的顺序不一样，第二个看起来越级了，但实际上效果是一样的。主要是因为我们MySQL有一个查询优化器，查询优化器会将SQL进行优化，选择最优的查询计划来执行。
     关于查询优化器，在这里就不再赘述，感兴趣的同学可以自行学习
     

举个栗子

假设我们有这么一个表，id为主键，没有创建索引：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB

如果要在此处建立复合索引，我们要遵循什么原则呢？

1、通过调整顺序，可以少维护一个索引

比如我们的业务需求里边，有如下两种查询方式： 根据name查询 根据name和age查询

如果我们建立索引（age,name），由于最左前缀原则，我们这个索引能实现的是根据age，根据age和name查询，并不能单纯根据name查询（因为跳跃了），为了实现我们的需求，我们还得再建立一个name索引；

而如果我们通过调整顺序，改成（name，age），就能实现我们的需求了，无需再维护一个name索引，这就是通过调整顺序，可以少维护一个索引。

2、考虑空间：短索引

比如我们的业务需求里边，有以下两种查询方式： 根据name查询 根据age查询 根据name和age查询

我们有两种方案：

1. 建立联合索引(name,age)，建立单列索引：age索引。
2. 建立联合索引(age,name)，建立单列索引：name索引。

这两种方案都能实现我们的需求，这个时候我们就要考虑空间了，name字段是比age字段大的，显然方案1所耗费的空间是更小的，所以我们更倾向于方案1。

3.7 索引使用

索引虽好，但并不是所有场景都适合：

何时建立索引

1. where中的查询字段
2. 查询中与其他表关联的字段，比如外键
3. 排序的字段
4. 统计或分组的字段

何时不要索引

1. 表中数据量很少
2. 经常改动的表
3. 频繁更新的字段
4. 数据重复度高

定义表结构（示例）

-- 无索引结构
CREATE TABLE `tuser` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 有索引结构
CREATE TABLE `tuser_index` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

初始化数据

创建存储过程并执行，初始化100w条数据

DELIMITER $$
CREATE PROCEDURE insert_data()
BEGIN
		DECLARE i INT DEFAULT 1;
    WHILE i <= 1000000 DO
        INSERT INTO tuser (name, age) VALUES 
        (CONCAT('张三',i), 1);
        SET i = i + 1;
    END WHILE;
		COMMIT;
    SET autocommit=1;
END$$
DELIMITER ;

CALL insert_data();

查询数据（无索引）

select * from tuser where name = '张三1' order by age desc;
-- 执行时间：0.26秒左右（注意，当前表只有3个字段并且数据相对较小的情况下，如果字段更多字段更大，那会更慢）

通过explain命令查看执行计划：

type：列表示关联类型或访问类型，即 MySQL 决定如何查找表中的行，查找数据行记录的大概范围，从最优到最差依次为：system > const > eq_ref > ref > range > index > ALL

rows:：MySQL 估计要读取并检测的行数，但这并不是结果集里的行数

通过演示结果我们可以看出type的值为All，代表全表扫描，要扫描所有叶子节点。key为空，表示没有命中索引，这样的sql在实际的项目当中，效率是十分低下的，所以我们一般都要进行优化

查找索引

SHOW INDEX FROM tuser;

可以看到，我们的主键字段id，系统会默认为我们创建一个名称为PRIMARY 的主键索引，并且索引的类型为BRTREE

创建索引

在主键索引的基础上再创建name和age的联合索引

CREATE INDEX idx_name_age ON tuser_index(name,age);

SHOW INDEX FROM tuser_index;

我们创建完联合索引，能看到同一个索引名是有两条字段数据的，其中seq_in_index分别是1和2，表明在联合索引中的顺序，也是我们不能跳阶使用的原因之一

查找数据（有索引）

select * from tuser_index where name = '张三1' order by age desc;
-- 执行时间：0.001秒左右（效率显著提升）

通过explain命令查看执行计划：

可以看到，type的级别为ref,并且key已经命中我们创建的联合索引，检测的行数也从100w变为了1，效率提升无数倍

删除索引

drop INDEX idx_name_age on tuser_index;

变更索引

修改索引通常是先删除旧索引，然后创建新索引

总结

​ 本次分享到这就告一段落了，再次回顾一下整篇分享的内容，其中包含索引的定义、结构、原理、如何创建、如何使用以及需要注意的地方。相对来说，更偏向理论，但是不管是什么技术，万变不离其宗，只要我们掌握了原理，那我们再去学如何使用，如何优化都会效率翻倍。

​ 通过以上内容，我们知道了，索引并不是越多越好，也不是任何场景下都适用，用的好能提升效率无数倍，用的不好甚至能导致数据库崩溃。要是展开来讲的话，还有很多很多，这里就不再一一赘述了，感兴趣的同学可以下去之后，一起再探讨沟通。

​ 最后感谢大家的聆听，谢谢大家。