【MySQL】MySQL索引深入剖析

索引是用来加快SQL执行速度的，那为什么索引可以加快检索速度呢？

一、索引基础

1.1 概念

数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中数据。
剖析：索引是一种数据结构，用来加快SQL执行速度

1.2 索引类型

在MySQL中索引有这3种类型：

1. 普通索引
2. 唯一索引（主键索引也是唯一索引）
3. 全文索引

1.3 如何新建索引？

alter table test add index idx_name(name1,name2);

二、索引数据结构分析？

思考一个问题：既然索引能加快SQL执行速度，那为什么呢？
下面来推演索引的数据结构

2.1 有序数组

通过对有序数组进行二分查找，可以实现高效数据检索。但是对于更新操作，会挪动大量数据。只适合存储静态数据。

2.2 二叉查找树（BST Binary Search Tree）

二叉查找树的特点是什么？
左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。

二叉查找树既能够实现快速查找，又能够实现快速插入。但是在极端情况下，二叉查找树会退化成斜树，查找时间复杂度退化为O(n)。

2.3 平衡二叉树（AVL）

平衡二叉树的定义：左右子树深度差绝对值不能超过 1。
是什么意思呢？比如左子树的深度是 2，右子树的深度只能是 1 或者 3。
这个时候我们再按顺序插入 1、2、3、4、5、6，一定是这样，不会变成一棵“斜树”。

但是AVL每个节点只存储一个数据，而存储引擎的逻辑存储结构是以页(16K)为单位的，极大的浪费了空间。

2.4 红黑树

红黑树也是 BST 树，但是不是严格平衡的。
为什么不用红黑树？1、只有两路；2、不够平衡。

2.5 多路平衡查找树（B Tree）（分裂、合并）

这个就是我们的多路平衡查找树，叫做 B Tree（B 代表平衡）。
跟 AVL 树一样，B 树在枝节点和叶子节点存储键值、数据地址、节点引用。
它有一个特点：分叉数（路数）永远比关键字数多 1。比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点。

B Tree如何保存平衡呢？
当在已排好序的节点中间插入数据时，会进行页的分裂和合并。

从这个里面我们也能看到，在更新索引的时候会有大量的索引的结构的调整，所以解释了为什么我们不要在频繁更新的列上建索引，或者为什么不要更新主键。

节点的分裂和合并，其实就是 InnoDB 页的分裂和合并。

2.6 hash index

hash索引的时间复杂度为O(1),为什么不用hash索引呢？主要有这几个原因：

1. 没有顺序
2. 只有等值查询，不支持范围查询
3. hash冲突，采用拉链法解决，效率降低。

2.7 B+树（加强版多路平衡查找树）

B Tree 的效率已经很高了，为什么 MySQL 还要对 B Tree 进行改良，最终使用了B+Tree 呢？

总体上来说，这个 B 树的改良版本解决的问题比 B Tree 更全面。

我们来看一下 InnoDB 里面的 B+树的存储结构：

MySQL 中的 B+Tree 有几个特点：

1. 它的关键字的数量是跟路数相等的；
2. B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。
3. B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。
4. 它是根据左闭右开的区间 [ )来检索数据。

总结一下，InnoDB 中的 B+Tree 的特点：

1. 它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题是什么？（每个节点存储更多关键字；路数更多）
2. 扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵 B+Tree 拿到所有的数据）
3. B+Tree 的磁盘读写能力相对于 B Tree 来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
4. 排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）
5. 效率更加稳定（B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的）

举个例子：
假设一条记录是 1K，一个叶子节点（一页）可以存储 16 条记录。非叶子节点可以存储多少个指针？

假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为6 字节，这样一共 14 字节。非叶子节点（一页）可以存储 16384/14=1170 个这样的单元（键值+指针），代表有 1170 个指针。

树 深 度 为 2 的 时 候 ， 有 1170^2 个 叶 子 节 点 ， 可 以 存 储 的 数 据 为1170 * 1170 * 16=21902400。

在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

三、MySQL逻辑存储结构

MySQL 的存储结构分为 5 级：表空间、段、簇、页、行。

3.1 表空间 Table Space

上节课讲磁盘结构的时候讲过了，表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。分为：系统表空间、独占表空间、通用表空间、临时表空间、Undo 表空间。

3.2 段 Segment

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等，段是一个逻辑的概念。一个 ibd 文件（独立表空间文件）里面会由很多个段组成。

创建一个索引会创建两个段，一个是索引段：leaf node segment，一个是数据段：non-leaf node segment。索引段管理非叶子节点的数据。数据段管理叶子节点的数据。也就是说，一个表的段数，就是索引的个数乘以 2。

3.3 簇 Extent

一个段（Segment）又由很多的簇（也可以叫区）组成，每个区的大小是 1MB（64个连续的页）。每一个段至少会有一个簇，一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是簇。

3.4 页 Page

为了高效管理物理空间，对簇进一步细分，就得到了页。簇是由连续的（Page） 组成的空间，一个簇中有 64 个连续的页。 （1MB／16KB=64）。这些页面在物理上和逻辑上都是连续的。

跟大多数数据库一样，InnoDB 也有页的概念（也可以称为块），每个页默认 16KB。页是 InnoDB 存储引擎磁盘管理的最小单位，通过 innodb_page_size 设置。

一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB，也就是说一个表空间最多存储 64TB 的数据。

注意，文件系统中，也有页的概念。
操作系统和内存打交道，最小的单位是页 Page。文件系统的内存页通常是 4K。

3.5 行 Row

（仅供了解）
InnoDB 存储引擎是面向行的（row-oriented），也就是说数据的存放按行进行存放。

四、Innodb中的索引

索引最终还是存储在文件中。在Innodb存储引擎中，是存储在 .ibd 文件中。
在 InnoDB 里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd 文件里面。

什么叫做聚集索引（聚簇索引）？
就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。（比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引）。

在 InnoDB 里面，它组织数据的方式叫做叫做（聚集）索引组织表（clustered indexorganize table），所以主键索引是聚集索引，非主键都是非聚集索引。

如果 InnoDB 里面主键是这样存储的，那主键之外的索引，比如我们在 name 字段上面建的普通索引，又是怎么存储和检索数据的呢？

InnoDB 中，主键索引和辅助索引是有一个主次之分的。
辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。

比如我们用 name 索引查询 name= '青山'，它会在叶子节点找到主键值，也就是id=1，然后再到主键索引的叶子节点拿到数据。

为什么在辅助索引里面存储的是主键值而不是主键的磁盘地址呢？如果主键的数据类型比较大，是不是比存地址更消耗空间呢？

我们前面说到 B Tree 是怎么实现一个节点存储多个关键字，还保持平衡的呢？ 是因为有分叉和合并的操作，这个时候键值的地址会发生变化，所以在辅助索引里面不能存储地址。

另一个问题，如果一张表没有主键怎么办？

1. 如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。
2. 如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。
3. 如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

五、索引使用原则

5.1 索引相关知识

1. 列的离散度，就是列值重复度不高的列适合建索引
2. 联合索引最左匹配
3. 覆盖索引。就是查询的字段是索引值本身，避免了回表（就是又根据主键再查一遍）
4. 索引条件下推。就是在存储引擎层筛选符合条件的数据，而不是在Server层过滤

5.2 索引创建原则

1. 在用于 where 判断 order 排序和 join 的（on）字段上创建索引
2. 索引的个数不要过多。——浪费空间，更新变慢。
3. 区分度低的字段，例如性别，不要建索引。——离散度太低，导致扫描行数过多。
4. 频繁更新的值，不要作为主键或者索引。——页分裂
5. 组合索引把散列性高（区分度高）的值放在前面。
6. 创建复合索引，而不是修改单列索引。
7. 过长的字段，怎么建立索引？ 前缀索引
8. 为什么不建议用无序的值（例如身份证、UUID ）作为索引？

5.3 导致索引失效的原因

1. 索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、计算（+ - * /）
2. 隐式转换
3. like 条件中前面带%
4. 负向查询 NOT LIKE 不能 | != （<>）和 NOT IN 在某些情况下可以

注意一个 SQL 语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。其实，用不用索引，最终都是优化器说了算

 最后

对于程序员来说，要学习的知识内容、技术有太多太多，要想不被环境淘汰就只有不断提升自己，从来都是我们去适应环境，而不是环境来适应我们！

不用多说，相信大家都有一个共识：无论什么行业，最牛逼的人肯定是站在金字塔端的人。所以，想做一个牛逼的程序员，那么就要让自己站的更高，成为技术大牛并不是一朝一夕的事情，需要时间的沉淀和技术的积累。

现在竞争这么激烈，只有通过不断学习，提高自己，才能保持竞争力。

对于一些不知道学习什么，没有一个系统路线的程序员，这里给大家提供一些学习资料

需要的小伙伴，可以一键三连，点击这里获取免费领取方式！

《Java核心知识点合集（283页）》

内容涵盖：Java基础、JVM、高并发、多线程、分布式、设计模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat、数据库、云计算等

《Java中高级核心知识点合集（524页）》

《Java高级架构知识点整理》

《Docker从入门到实践》

《spring could 学习笔记》

《JVM与性能调优知识点整理》

《MySQL性能调优与架构设计解析文档》305页

《Nginx入门到实战》319页

《Java并发编程》385页

《1000道 互联网Java工程师面试题 (485页)》

需要的小伙伴，可以一键三连，点击这里获取免费领取方式！