mysql索引&索引数据结构

mysql索引

Mysql数据库中的常见索引结构有多种，常用Hash，B-树，B+树等数据结构来进行数据存储。树的深度加深一层，意味着多一次查询，对于数据库磁盘而言，就是多一次IO操作，导致查询效率低下。

什么是索引

索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

简单类比一下，数据库如同书籍，索引如同书籍目录，假如我们需要从书籍查找与 xx 相关的内容，我们可以直接从目录中查找，定位到 xx 内容所在页面，如果目录中没有 xx 相关字符或者没有设置目录（索引），那只能逐字逐页阅读文本查找，效率可想而知。

为什么使用索引

  • 快速查找匹配where子句中的行
  • 如果可以在多个索引中选择，mysql通常会使用找到最少行的索引
  • 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
  • 当有表连接的时候，从其他表检索行数据
  • 查找特定索引列的min和max的值
  • 如果排序或者分组时可用索引的最左前缀完成的，则对表进行排序和分组
  • 在某些情况下，可以优化查询以检索数据值而无需查找数据行

总结： 使用最主要的原因是使用提高效率

索引的优缺点

优点：

  • 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 索引可以帮助服务器避免排序和创建临时表【B+树已经排序过的】
  • 索引可以将随机IO变成顺序IO，减少IO次数
  • 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
  • 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  • 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
  • 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
  • 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
  • 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  • 对于非常小的表，大部分情况下简单的全表扫描更高效；

创建索引准则

  • 应该创建索引的列：
    • 在经常需要搜索的列上，可以加快搜索的速度
    • 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
    • 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度[少用]
    • 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
    • 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
    • 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
  • 不该创建索引的列：
    • 对于那些在查询中很少使用或者参考的列不应该创建索引。若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
    • 对于那些只有很少数据值或者重复值多的列也不应该增加索引。【数据去重后的数据比趋于1,则索引效果越好】
      这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
    • 对于那些定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。
    • 当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）

索引的分类

  • 主键索引(唯一且非空)【数据库默认建立的索引是给唯一键建立的】
  • 唯一索引(唯一可为空)
  • 普通索引(普通字段的索引)
  • 全文索引(一般是varchar,char,text类型建立的，但很少用)
  • 组合索引(多个字的建立的索引)

MySQL 的索引有两种分类方式：逻辑分类和物理分类。

逻辑分类

有多种逻辑划分的方式，比如按功能划分，按组成索引的列数划分等

  1. 按功能划分
  • 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
 ALTER TABLE TableName ADD PRIMARY KEY(column_list);
  • 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD UNIQUE (column_list);
  • 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
CREATE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));
  • 全文索引：它查找的是文本中的关键词，主要用于全文检索。（篇幅较长，下文有独立主题说明）
  1. 按列数划分
  • 单例索引：一个索引只包含一个列，一个表可以有多个单例索引。
  • 组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

物理分类

分为聚簇索引和非聚簇索引（有时也称辅助索引或二级索引）

聚簇是为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。

聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。

非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

聚簇索引优点：

  • 聚簇索引对于主键的排序查找和范围查找速度非常快
  • 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

聚簇索引缺点：

  • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）

  • 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

  • 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

Mysql中key 、primary key 、unique key 与index区别

key 与 index 含义

  • key具有两层含义：1.约束（约束和规范数据库的结构完整性）2.索引
  • index：索引

key 种类

  • key：等价普通索引 key 键名 (列)

  • primary key: 【1. 约束作用（constraint），主键约束（unique，not null，一表一主键，唯一标识记录），规范存储主键和强调唯一性; 2.为这个key建立主键索引 】

  • unique key:【1. 约束作用（constraint），unique约束（保证列或列集合提供了唯一性；2.为这个key建立一个唯一索引】

  • foreign key：【1.约束作用（constraint），外键约束，规范数据的引用完整性 ；2.为这个key建立一个普通索引】

练习

建立个user表：

mysql> create table user(
    -> id int auto_increment,
    -> username varchar(100) not null,
    -> user_id int(8) primary key,
    -> depart_no int not null,
    -> corp varchar(100),
    -> phone char(11),
    -> key auto_id (id),
    -> unique key phone (phone),
    -> index username_depart_corp (username,depart_no,corp),
    -> constraint fk_user_depart foreign key(depart_no) references depart(id);
    -> )engine=innodb charset=utf8;

auto_increment修饰的字段需要是一个候选键，需要用key指定，否则报错。我们看下表的结构:

image-20211210111945092

查看表索引：

在这里插入图片描述

可见key也会生成索引，【key类型：PRI 主键约束；UNI 唯一约束；MUL 可以重复】

在这里插入图片描述

如果一个Key有多个约束，将显示约束优先级最高的， PRI>UNI>MUL

索引数据结构

MySQL中常用的索引结构（索引底层的数据结构）有：B-TREE ，B+TREE ，HASH 等，而树是数据结构中的重中之重，有一般二叉树、完全二叉树、满二叉树、线索二叉树、霍夫曼树、二叉排序树、平衡二叉树、红黑树、B树。

二叉树

定义：二叉排序树（Binary Sort Tree），又称二叉查找树（Binary Search Tree），也称二叉搜索树。二叉排序树或者是一棵空树

二叉树图

image-20211208160651240

特性：

  • 所有非叶子结点至多拥有两个子节点（Left和Right）；

  • 所有结点存储一个关键字；

  • 非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；

二叉搜索树的搜索，从根结点开始，如果查询的关键字与结点关键字相等，则该结点为查询的结点，如果查询关键字比结点关键字小，则进入左子树，反之则进入右子树；如果左子树为空或者右子树为空，则返回查找不到响应的关键字；
如果二叉搜索树的所有叶子结点的左右子树的树木保持一个平衡即左右子树个数大致相等的话，其搜索则更接近与二分查找；但是它相比连续内存空的二分查找的优点是：改变二叉搜索树的结构（添加或者删除）不需要大段的移动数据，甚至通常都是常数开销；

产生问题：

当一个二叉树经历多次删除操作后，就会出现树不平衡的状态，下图

image-20211208161626836

右边也是一个搜索二叉树，只不过不在平衡了，他的搜索功能也变成了线性的，同样的关键字可能导致不同的树结构索引，所以，在使用搜索二叉树时，还要考虑尽可能让B树保持左图的结构，避免和右图类似，这也有事所谓的平衡问题了

实际使用的二叉搜索树都是在原二叉搜索树的基础上加上平衡算法，即平衡二叉树；如何保持B树节点分布均匀的平衡算法就是平衡二叉树的关键所在，平衡算法是一种在二叉搜索树的插入和删除结点时的一种策略。即：在插入或删除的同时保持二叉搜索树的平衡。

B-树

B-树,这里的 B 表示 balance( 平衡的意思),B-树是一种多路自平衡的搜索树（B树是一颗多路平衡查找树）它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点

三阶B树图

image-20211208162517630

磁盘读取数据是以盘块(block)为基本单位的。

B-树就是B树，多路搜索树，树高一层意味着多一次的磁盘I/O

image-20211208162651906

B树是一种多路搜索树，一棵m阶的B树满足下列条件：

  • 树中每个结点至少有M个孩子
  • 根结点的子节点数为[2,M）
  • 除根结点以外的非叶子结点的儿子数为[M/2, M]；
  • 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
  • 非叶子结点的关键字个数 = 指向子节点的指针个数-1；
  • 非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
  • 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
  • 所有叶子结点位于同一层；

image-20211209094340477

B树的特征:

  • 关键字集合分布在整颗树中；

  • 任何一个关键字出现且只出现在一个结点中；

  • 搜索有可能在非叶子结点结束；

  • 其搜索性能等价于在关键字全集内做一次二分查找；

  • 自动层次控制；

    B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的子结点；重复，直到所对应的子指针为空，或已经是叶子结点；

B+ 树

B+树是B-树的变体，也是一种多路搜索树，特性和B-类似，不同在于：

  1. 所有关键字存储在叶子节点出现,内部节点(非叶子节点并不存储真正的 data)
  2. 为所有叶子结点增加了一个链指针

B+简图：

image-20211209095701418

因为内节点并不存储 data，所以一般B+树的叶节点和内节点大小不同，而B-树的每个节点大小一般是相同的，为一页

为了增加 区间访问性，一般会对B+树做一些优化。如下图带顺序访问的B+树。

image-20211209100440379

B-树和B+树的区别

  1. B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。

B-树：

image-20211209101510294

从上图可以看出，key 为 50 的节点就在第一层，B-树只需要一次磁盘 IO 即可完成查找。所以说B-树的查询最好时间复杂度是 O(1)。

B+树：

image-20211209101620289

由于B+树所有的 data 域都在根节点，所以查询 key 为 50的节点必须从根节点索引到叶节点，时间复杂度固定为 O(log n)。

小结：B树的由于每个节点都有key和data，所以查询的时候可能不需要O(logn)的复杂度，甚至最好的情况是O(1)就可以找到数据，而B+树由于只有叶子节点保存了data，所以必须经历O(logn)复杂度才能找到数据

  1. B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。

image-20211209102353728

空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问。利用磁盘预读原理提前将这些数据读入内存，减少了磁盘 IO 的次数。由于B+树的叶子节点的数据都是使用链表连接起来的，而且他们在磁盘里是顺序存储的，所以当读到某个值的时候，磁盘预读原理就会提前把这些数据都读进内存，使得范围查询和排序都很快。

  1. B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确

由于B-树节点内部每个 key 都带着 data 域，而B+树节点只存储 key 的副本，真实的 key 和 data 域都在叶子节点存储。前面说过磁盘是分 block 的，一次磁盘 IO 会读取若干个 block，具体和操作系统有关，那么由于磁盘 IO 数据大小是固定的，在一次 IO 中，单个元素越小，量就越大。这就意味着B+树单次磁盘 IO 的信息量大于B-树，从这点来看B+树相对B-树磁盘 IO 次数少。

由于B树的节点都存了key和data，而B+树只有叶子节点存data，非叶子节点都只是索引值，没有实际的数据，这就时B+树在一次IO里面，能读出的索引值更多。从而减少查询时候需要的IO次数！

image-20211209111607641

B-树仅有 2 个 key，而B+树有 3 个 key

总结：

  1. B+ 树的磁盘读写代价更低：B+ 树的数据都集中在叶子节点，分支节点 只负责指针（索引）；B 树的分支节点既有指针也有数据 。这将导致B+ 树的层高会小于B 树的层高，也就是说B+ 树平均的Io次数会小于B 树。
  2. B+ 树的查询效率更加稳定：B+ 树的数据都存放在叶子节点，故任何关键字的查找必须走一条从根节点到叶子节点的路径。所有关键字的查询路径相同，每个数据查询效率相当。
  3. B+树更便于遍历：由于B+树的数据都存储在叶子结点中，分支结点均为索引，遍历只需要扫描一遍叶子节点即可；B树因为其分支结点同样存储着数据，要找到具体的数据，需要进行一次中序遍历按序来搜索。
  4. B+树更擅长范围查询：B+树叶子节点存放数据，数据是按顺序放置的双向链表。B树范围查询只能中序遍历。
  5. B+ 树占用内存空间小：B+ 树索引节点没有数据，比较小。在内存有限的情况下，相比于B树索引可以加载更多B+ 树索引。

B树由来

B-树是专门为外部存储器设计的，如磁盘，它对于读取和写入大块数据有良好的性能，所以一般被用在文件系统及数据库中。

首先看看为什么会出现B-树这类数据结构：

传统用来搜索的平衡二叉树有很多，如 AVL 树，红黑树等。这些树在一般情况下查询性能非常好，但当数据非常大的时候它们就无能为力了。原因当数据量非常大时，内存不够用，大部分数据只能存放在磁盘上，只有需要的数据才加载到内存中。一般而言内存访问的时间约为 50 ns，而磁盘在 10 ms 左右。速度相差了近 5 个数量级，磁盘读取时间远远超过了数据在内存中比较的时间。这说明程序大部分时间会阻塞在磁盘 IO 上。那么我们如何提高程序性能？减少磁盘 IO 次数，像 AVL 树，红黑树这类平衡二叉树从设计上无法“迎合”磁盘。

image-20211209135228391

上图是一颗简单的平衡二叉树，平衡二叉树是通过旋转来保持平衡的，而旋转是对整棵树的操作，若部分加载到内存中则无法完成旋转操作。其次平衡二叉树的高度相对较大为 log n（底数为2），这样逻辑上很近的节点实际物理上可能非常远，无法很好的利用磁盘预读（局部性原理），所以这类平衡二叉树在数据库和文件系统上的选择就被 pass 了。【空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问】

然后从减少磁盘IO的角度来看看B-树的设计：

索引的效率依赖与磁盘 IO 的次数，快速索引需要有效的减少磁盘 IO 次数，如何快速索引呢？索引的原理其实是不断的缩小查找范围，快速定位，就如我们平时用字典查单词一样，先找首字母缩小范围，再第二个字母等等。平衡二叉树是每次将范围分割为两个区间。为了更快，B-树每次将范围分割为多个区间，区间越多，定位数据越快越精确。那么如果节点为区间范围，每个节点就较大了。所以新建节点时，直接申请页大小的空间（磁盘存储单位是按 block 分的，一般为 512 Byte。磁盘 IO 一次读取若干个 block，我们称为一页，具体大小和操作系统有关，一般为 4 k，8 k或 16 k），计算机内存分配是按页对齐的，这样就实现了一个节点只需要一次 IO

image-20211209135911506

上图是一棵简化的B-树，多叉的好处非常明显，有效的降低了B-树的高度，为底数很大的 log n，底数大小与节点的子节点数目有关，一般一棵B-树的高度在 3 层左右。层数低，每个节点区确定的范围更精确，范围缩小的速度越快（比二叉树深层次的搜索肯定快很多）。一个节点需要进行一次 IO，那么总 IO 的次数就缩减为了 log n 次。B-树的每个节点是 n 个有序的序列(a1,a2,a3…an)，并将该节点的子节点分割成 n+1 个区间来进行索引(X1< a1, a2 < X2 < a3, … , an+1 < Xn < anXn+1 > an)。

小结：

B树的每个节点，都是存多个值的，不像二叉树那样，一个节点就一个值，B树把每个节点都给了一点的范围区间，区间更多的情况下，搜索也就更快了，比如：有1-100个数，二叉树一次只能分两个范围，0-50和51-100，而B树，分成4个范围 1-25， 25-50，51-75，76-100一次就能筛选走四分之三的数据。所以作为多叉树的B树是更快的

Hash

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。Memory存储引擎使用Hash

image-20211209142151986

范围查询

Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。也不支持任何范围查询。

由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，存储是哈希计算之后的顺序，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。

B+树和哈希结构区别

  • 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；这有个前提，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

  • 如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

  • 哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

  • 哈希索引也不支持多列联合索引的最左匹配规则；

  • B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

InnoDB B+Tree结构来存储索引

InnoDB使用B+Tree数据结构存储索引，根据索引物理结构可将索引划分为聚簇索引和非聚簇索引（也可称辅助索引或二级索引）。一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引。

B+树 叶子节点包含数据表中行记录就是聚簇索引（索引和数据是一块的）:

image-20211209151946280

B+树 叶子节点没包含数据表中行记录就是非聚簇索引（索引和数据是分开的）,下图：

image-20211209152105900

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。

如果数据库只按这样的方式存储，那么如何查找数据就成为一个问题？
因为我们不知道要查找的数据存在哪个页中，也不可能把所有的页遍历一遍，那样太慢了。

于是人们想到了用B+ 树的方式组织这些数据，下图以InnoDB为例：

image-20211209152334898

  • pointer往往是6个字节，指明对应key值的页面位置信息。

  • key一般为索引主键，如果为单字段 bigint 类型，则为8字节。如此可计算一个页大概可以存放16 * 1024/（6+8）=1170行数据。

  • 假设一行数据1k，那么2层B+ 树（第一层索引，第二层叶子节点 存数据）就可以存储1170 * 16 = 18 720行；三层则可以存储1170 * 1170 * 16=21902400行。

MyISAM B+Tree结构来存储索引

MyISAM也使用B+Tree数据结构存储索引，但都是非聚簇索引。

以下是MyISAM主键索引存储图:

image-20211209152536784

可见，索引和数据是分开的 索引的data部分只是索引的地址值。其实上文也提到过，.MYI就是MyISAM表的索引文件，MYD是MyISAM表的数据文件。

image-20211209152620699

索引规则

回表

name字段是普通索引，从name列的B+树找到主键，再从主键的B+树找到最终的数据，这就是回表。(主键索引的叶子节点保存的是列的所有数据，但是普通所有的叶子结点保存的是对应的主键ID）

总结：通过普通索引B+树确定主键值，再到主键索引树，查找到具体数据，即为回表

例如：

select * from use where name='sun' # name已经创建有普通索引

首先会通过name这个非主键索引找到sun对应的主键Id=2,然后通过id=2在主键索引中找到整个行数据，并返回，这个就是回表。

image-20211210113645225

覆盖索引

在非主键索引上可以查询到所需要的字段，不需要回表再次查询就叫覆盖索引。

例如：

select id,name from user where name ="1"

只查询id和name值，id的值在第一步非主键索引就已经有了，就不需要根据ID到主键索引中查询行数据了，没有回表操作。

最左匹配原则

组合索引中 先匹配左边，再继续向后匹配；比如user表中有name+age组成的联合索引，select * from user where name="纪先生" and age = 18 就符合最左匹配，可以用的索引。而select * from user where age = 18就不符合，用不到这个索引。

总结：组合索引顾头不顾未，想要使用索引，必须从组合索引的第一个字段开始。

例如：

select * from user where name="是非" and age = 18;

select * from user where age = 18;
#  由于最左匹配原则：只需要建立一个组合索引age+name即可
#  第一个sql在执行过程中，mysql 底层优化器会把age和name调换执行位置，从而提高查询效率

select * from user where name="纪先生" and age = 18;

select * from user where name= "纪先生";

# 建立name+age和age索引，或者建立age+name和name索引,看着两个都可以。
# 其实name+age和age更好，因为索引也是需要持久化存储的，占用磁盘空间，读取的时候也是占用内存的，
# name+age和age+name这两个占用是一样的，但是name和age单独比较，
# 肯定age占用空间更少，name更长（索引越大，IO次数可能更多）

索引下推

组合索引中尽量利用索引信息，来尽可能的减少回表的次数

name+age的组合索引如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据，然后回表根据ID查询对于的数据行，之后在筛选出符合age条件的数据。索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID，然后回表根据ID查询对应行数据，明显会减少数据的条数

相关Mysql文章系列

mysql索引&索引数据结构

mysql 函数 & 自定义函数

mysql常用命令&架构&引擎

mysql事务

mysql日志

大佬博客:MySQL体系构架、存储引擎和索引结构

一文搞懂MySQL索引（清晰明了)

参考:

深入学习二叉树(四) 二叉排序树[https://www.jianshu.com/p/bbe133625c73]
B树和B+树的区别[https://blog.csdn.net/a519640026/article/details/106940115]
深入了解MySQL中的索引（用处、分类、匹配方式）[https://www.php.cn/mysql-tutorials-482605.html]
posted @ 2021-12-10 15:58  Mr*宇晨  阅读(457)  评论(0)  编辑  收藏  举报