关于java:图解-MySQL-索引清晰易懂写得太好了

作者：shuaibing90 \
起源：https://www.xysycx.cn/article…

索引是辅助存储引擎高效获取数据的一种数据结构。

很多人形象的说索引就是数据的目录，便于存储引擎疾速的定位数据。

咱们常常从以下几个方面对索引进行分类

从 「数据结构的角度」 对索引进行分类

B+tree
Hash
Full-texts 索引

从 「物理存储的角度」 对索引进行分类

聚簇索引
二级索引(辅助索引)

从 「索引字段个性角度」 分类

主键索引
惟一索引
一般索引
前缀索引

从 「组成索引的字段个数角度」 分类

单列索引
联结索引（复合索引）

下表是 MySQL 常见的存储引擎 InnoDB，MyISAM 和 Memory 别离反对的索引类型

在理论应用中，InnoDB 作为 MySQL 建表时默认的存储引擎

对上表进行横向查看能够理解到，B+tree 是 MySQL 中被存储引擎采纳最多的索引类型。

这里浅尝辄止的谈一下 B+tree 与 Hash 和红黑树的区别。另外，MySQL 系列面试题和答案全副整顿好了，微信搜寻Java 技术栈，在后盾发送：面试，能够在线浏览。

1970 年，R.Bayer 和 E.Mccreight 提出了一种实用于外查找的均衡多叉树——B- 树，磁盘管理系统中的目录治理，以及数据库系统中的索引组织少数采纳 B-Tree 这种数据结构。– 数据结构 C 语言版第二版严蔚敏

B+tree 是 B-Tree 的一个变种。（哦，对了，B-tree 念 B 树，它不叫 B 减树。。。）

B+tree 只在叶子节点存储数据，而 B-tree 非叶子节点也存储数据，对此处有疑难的能够到上面的连贯本人插入数据测试一番。

B-tree : https://www.cs.usfca.edu/~galles/visualization/BTree.html
B+tree : https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

因而，B+tree 单个节点的数量更小，在雷同的磁盘 IO 下能查问更多的节点。

另外 B+tree 叶子节点采纳单链表链接适宜 MySQL 中常见的基于范畴的程序检索场景，而 B-tree 无奈做到这一点。

对于有 N 个叶子节点的 B+tree，搜寻复杂度为 「O(logdN) ,d 是指 degree 是指 B+tree 的度」，示意节点容许的最大子节点个数为 d 个，在理论的使用中 d 值是大于 100 的，即便数据达到千万级别时候 B+tree 的高度仍然维持在 3-4 左右，保障了 3-4 次磁盘 I/O 就能查到指标数据。

从上图中能够看出红黑树是二叉树，节点的子节点个数最多为 2 个，意味着其搜寻复杂度为 「O(logN)」，比 B+ 树高出不少，因而红黑树检索到指标数据所需经理的磁盘 I/O 次数更多。

范畴查问是 MySQL 数据库中常见的场景，而 Hash 表不适宜做范畴查问，Hash 表更适宜做等值查问，另外 Hash 表还存在 Hash 函数抉择和 Hash 值抵触等问题。

因为这些起因，B+tree 索引要比 Hash 表索引有更广的实用场景。

MySQL 中的两种罕用存储引擎对索引的解决形式差异较大。

首先看一下 InnoDB 存储引擎中的索引，InnoDB 表的索引依照叶子节点存储的是否为残缺表数据分为聚簇索引和二级索引。

全表数据就是存储在聚簇索引中的。

聚簇索引以外的其它索引叫做二级索引。

上面结合实际的例子介绍下这两类索引。

create table workers
 (id    int(11)     not null auto_increment comment '员工工号',
     name  varchar(16) not null comment '员工名字',
     sales int(11) default null comment '员工销售业绩',
     primary key (id)
 ) engine InnoDB
   AUTO_INCREMENT = 10
   default charset = utf8;

 insert into workers(id, name, sales)
 values (1, '江南', 12744);
 insert into workers(id, name, sales)
 values (3, '今何在', 14082);
 insert into workers(id, name, sales)
 values (7, '路明非', 14738);
 insert into workers(id, name, sales)
 values (8, '吕归尘', 7087);
 insert into workers(id, name, sales)
 values (11, '姬野', 8565);
 insert into workers(id, name, sales)
 values (15, '凯撒', 8501);
 insert into workers(id, name, sales)
 values (20, '绘梨衣', 7890);

咱们当初本人的测试数据库中创立一个蕴含销售员信息的测试表 workers

蕴含 id(主键),name,sales 三个字段，指定表的存储引擎为 InnoDB。

而后插入 8 条数据

这个例子当中，workers 表的聚簇索引建设在字段 id 上

为了精确模仿，咱们先把主键 id 插入 b+tree 失去下图

而后在此图根底上，我画出了高清版。

从图中能够看到，聚簇索引的每个叶子节点存储了一行残缺的表数据，叶子节点间采纳单向链表按 id 列递增连贯，能够不便的进行程序检索。

InnoDB 表要求必须有聚簇索引，默认在主键字段上建设聚簇索引，在没有主键字段的状况下，表的第一个 NOT NULL 的惟一索引将被建设为聚簇索引，在前两者都没有的状况下，InnoDB 将主动生成一个隐式自增 id 列并在此列上创立聚簇索引。

接着来看二级索引。

还以方才的 workers 表为例

咱们在 name 字段上增加二级索引 index_name

alter table workers add index index_name(name);

同样咱们画出了二级索引 index_name 的 B+tree 示意图

图中能够看出二级索引的叶子节点并不存储一行残缺的表数据，而是存储了聚簇索引所在列的值，也就是 workers 表中的 id 列的值。

这两张示意图中 B+tree 的度设置为了 3，这也次要是为了不便演示。

理论的 B+tree 索引中，树的度通常会大于 100。

说了聚簇索引和二级索引必定要提到「回表查问」。

因为二级索引的叶子节点不存储残缺的表数据，所以当通过二级索引查问到聚簇索引的列值后，还须要回到局促索引也就是表数据自身进一步获取数据。

比如说咱们要在 workers 表中查问名叫吕归尘的人

select * from workers where name='吕归尘';

这条 SQL 通过 name=’ 吕归尘 ’ 的条件

在二级索引 index_name 中查问到主键 id=8，接着带着 id=8 这个条件

进一步回到聚簇索引查问当前能力获取到残缺的数据，很显然回表须要额定的 B+tree 搜寻过程，必然增大查问耗时。

须要留神的是通过二级索引查问时，回表不是必须的过程，当 Query 的所有字段在二级索引中就能找到时，就不须要回表，MySQL 称此时的二级索引为笼罩索引或称触发了 「索引笼罩」。

select id,name from workers where name='吕归尘';

这句 SQL 只查问了 id，和 name，二级索引就曾经蕴含了 Query 所以须要的所有字段，就无需回表查问。

explain select id,name from workers where name='吕归尘';

应用 explain 查看此条 SQL 的执行打算执行打算的 Extra 字段中呈现了 Using where;Using index 表明查问触发了索引 index_name 的索引笼罩，且对索引做了 where 筛选，这里不须要回表。

上面做比照，查问一下没有索引的

explain select id,name,sales from workers where name='吕归尘';

Extra 为 Using Index Condition 示意会先条件过滤索引，过滤完索引后找到所有合乎索引条件的数据行，随后用 WHERE 子句中的其余条件去过滤这些数据行。Index Condition Pushdown (ICP)是 MySQL 5.6 以上版本中的新个性，是一种在 存储引擎层 应用索引过滤数据的一种优化形式。ICP 开启时的执行打算含有 Using index condition 标示，示意优化器应用了 ICP 对数据拜访进行优化。

如果你对此感兴趣去查阅对应的官网文档和技术博客。

这次咱们简化来了解，不思考 ICP 对数据拜访的优化，当敞开 ICP 时，Index 仅仅是 data access 的一种拜访形式，存储引擎通过索引回表获取的数据会传递到 MySQL Server 层进行 WHERE 条件过滤。

Extra 为 Using where 只是揭示咱们 MySQL 将用 where 子句来过滤后果集。这个个别产生在 MySQL 服务器，而不是存储引擎层。个别产生在不能走索引扫描的状况下或者走索引扫描，然而有些查问条件不在索引当中的状况下。

这里表明没有触发索引笼罩，进行回表查问。

说完了 InnoDB 的索引，接下来咱们来看 MyISAM 的索引

以 MyISAM 存储引擎存储的表不存在聚簇索引。

MyISAM 表中的主键索引和非主键索引的构造是一样的，从上图中咱们能够看到

他们的叶子节点是不存储表数据的，节点中寄存的是表数据的地址，所以 MyISAM 表能够没有主键。

MyISAM 表的数据和索引是离开的，是独自寄存的。

MyISAM 表中的主键索引和非主键索引的区别仅在于主键索引 B+tree 上的 key 必须合乎主键的限度，

非主键索引 B+tree 上的 key 只有合乎相应字段的个性就能够了。

「主键索引」

建设在主键字段上的索引
一张表最多只有一个主键索引
索引列值不容许为 null
通常在创立表的时候一起创立

「惟一索引」

建设在 UNIQUE 字段上的索引就是惟一索引
一张表能够有多个惟一索引，索引列值容许为 null

咱们演示创立索引

create table persons
 (id   int(11) not null auto_increment comment '主键 id',
     eno  int(11) comment '工号',
     eid  int(11) comment '身份证号',
     veid int(11) comment '虚构身份证号',
     name varchar(16) comment '名字',
     primary key (id) comment '主键索引',
     UNIQUE key (eno) comment 'eno 惟一索引',
     UNIQUE key (eid) comment 'eid 惟一索引'
 ) engine = InnoDB
   auto_increment = 1000
   default charset = utf8;
 alter table persons
     add unique index index_veid (veid) comment 'veid 惟一索引';

通过 show index from persons; 命令咱们看到曾经胜利创立了三个惟一索引。

主键索引和惟一索引对字段的要求是要求字段为主键或 unique 字段，

而那些建设在一般字段上的索引叫做一般索引，既不要求字段为主键也不要求字段为 unique。

前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个 bytes 建设的索引，而不是在整个字段上建索引。

例如，能够对 persons 表中的 name(varchar(16))字段中 name 的前 5 个字符建设索引。

create index index_name on persons (name(5)) comment '前缀索引';
show index from persons;

前缀索引能够建设在类型为

char
varchar
binary
varbinary

的列上，能够大大减少索引占用的存储空间，也能晋升索引的查问效率。

建设在单个列上的索引为单列索引
- 上文演示的都是单列索引
建设在多列上的称为联结索引（复合索引）

演示一下联结索引 create index index_id_name on workers(id,name) comment '组合索引'; 这条语句在咱们演示表 workers 中建设 id，name 这两个字段的联结索引。借助 show index 命令查看索引的详细信息操作后后果如下：

尽管详细信息当中列出了两条对于联结索引的条目，但并不示意联结索引是建设了多个索引，联结索引是一个索引构造，这两个条目示意的是组合索引中字段的具体信息，按建设索引时的书写程序排序。

同样咱们来看下联结索引的 B+tree 示意图

从图中看到组合索引的非叶子节点保留了两个字段的值作为 B+tree 的 key 值，当 B+tree 上插入数据时，先按字段 id 比拟，在 id 雷同的状况下按 name 字段比拟。

近期热文举荐：

1.1,000+ 道 Java 面试题及答案整顿(2021 最新版)

2. 别在再满屏的 if/ else 了，试试策略模式，真香！！

3. 卧槽！Java 中的 xx ≠ null 是什么新语法？

4.Spring Boot 2.5 重磅公布，光明模式太炸了！

5.《Java 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！

关于java:图解-MySQL-索引清晰易懂写得太好了

什么是索引？

索引的分类

数据结构角度看索引

B+tree 和 B-tree

B+tree 和红黑树

B+tree 索引与 Hash 表

物理存储角度看索引

InnoDB 的索引

MyISAM 的索引

索引字段个性角度看索引

一般索引

前缀索引

索引列的个数角度看索引