MySQL的索引（优缺点，二叉树，红黑数，B+Tree）【详解】

1.什么是索引

索引：是用于提高查询数据性能的一种数据结构。实际开发中，对数据的读写操作大概是10：1

• 把索引理解为目录。

• 构建索引的过程，就是构建目录的过程

• 构建索引的过程中会对数据进行排序

2.索引的优缺点

优点：

• 可以极大的提升查询数据的速度

• 可以帮助进行数据排序

缺点：

• 索引也会占用存储空间

• 索引会影响增、删、改的性能

3.什么字段适合加索引

适合添加索引的情况：

1. 经常作为查询条件的字段

2. 用于和其它表关联的字段：外键字段

3. 经常用来排序的字段：构建索引时，MySQL会对数据进行排序

4. 值的重复率低的字段：比如身份证号字段适合加索引；性别字段不适合加索引

不适合添加索引的情况：

1. 值的重复率高的字段：比如性别

2. 频繁更新的字段

3. 值过大的字段：比如有一个字段类型 varchar(60000)

4. 值无序的字段：值变化时，索引需要重新排序

4. 索引的数据结构

数据结构可视化地址：https://www.cs.usfca.edu/~galles/visualization/

好处：

• 大部分情况下，查找数据的速度，会比全表扫描要快

问题：

• 二叉树没有自平衡的效果，极端情况可能变成单链表。之后查找数据又变成全表扫描了

• 一个节点最多只能有2个子节点，如果数据特别多，树的层级会非常深，导致IO次数多，影响查询速度

5.平衡二叉树-红黑树组织索引值

好处：

• 这种算法会进行树的染色与旋转，保证树的平衡：左右子树的高度差，最大是2倍，不可能形成单链表

缺点：

• 一个节点最多有2个子节点，如果数据量特别大，树的层级就会非常深。查找数据时IO的次数就会多，查找的性能就会降低

6.B-Tree

特点：

• 每个节点允许有多个子节点

• 能够通过分裂和自旋，尽可能的减少树的层级，实现树的平衡

好处：

• 树的层级更浅，IO次数就更少

• 不会退化成单链表

缺点：

• 查询速度不稳定。有些数据在根节点上，有些数据在叶子节点上，每个节点上都有数据

• 树的层级还不够浅。MySQL的每个节点16KB，一个节点里存储的有索引值还有对应的数据，那么下级节点数量就少，导致树的层级不够浅

7.B+Tree组织索引值

B+Tree组织索引值的结构：

• 每个节点，空间大小是以“页”为单位。默认一页16KB

• 节点存储的内容：

  • 非叶子节点只存储：索引值和下级节点的地址，不保存数据

  • 叶子节点才会存储：索引值和每一行的数据

  目的是：让非叶子节点可以有更多的子节点，从而降低树的深度

• 所有的叶子节点之间，构建成双向链表：方便进行范围查询

• 所有的数据都已经帮我们做好了排序

面试问题到：如果一张表大概1000W条数据，MySQL里索引树的深度有多少？大概3~4层，                               如果叶子节点在内存里，不需要加载，IO加载次数大概2-3层

这里图片的非叶子节点是3个开始，项目里是根据数据进行的算法分析

8. 索引语法

1.语法

• 创建索引：create index 索引名 on 表名(字段,字段,..)

• 查询索引：show indexes from 表名 或者 show index from 表名

• 删除索引：drop index 索引名 on 表名

2.示例

#给tb_emp表里name字段添加索引
create index idx_name on tb_emp(name);
#查询一张表有哪些索引
show indexes from tb_emp;
#删除索引
drop index idx_name on tb_emp;

演示图：