MySQL初阶2——索引的初步理解

一、索引的概念和使用

1. 索引是什么

2. 索引的使用

2.1 查看索引

2.2 创建索引

2.3 删除索引

3. 索引使用的注意事项

二、索引的核心内容——底层的数据结构

1. 前景引入

2. 索引使用的数据结构

2.1 基础版—— B 树

2.2 升级版—— B+ 树

重点：如果查询条件是非主键且带有索引的列或者非主键且没有索引的列，执行流程如何呢？

一、索引的概念和使用

1. 索引是什么

在MySQL数据库中，索引（Index）是一种数据结构，用于加快对表中数据的检索速度。索引可以帮助数据库系统快速定位到包含指定值的行，从而加快查询操作的执行效率。

索引在MySQL中起着重要的作用，它可以大大提高检索数据的速度，特别是当处理大量数据时。下面是索引在MySQL中的一些重要特点和作用：

（1）加快查询速度：通过列上创建索引，数据库系统可以更快地定位到符合查询条件的数据，避免全表扫描，从而提高查询速度。

（2）唯一性约束：通过在列上创建唯一索引，可以确保该列的数值或字符串在整个表中是唯一的，避免数据重复。

（3）加快排序：对包含索引的列进行排序操作时，MySQL可以利用索引加快排序过程，提高排序的效率。

（4）连接优化：当多个表进行连接查询时，若连接字段上有索引，可以加快连接操作的速度。

（5）覆盖索引：如果一个查询只涉及到了索引列，那么MySQL就可以直接使用索引返回结果，而不必再去访问表中的数据，这就是覆盖索引，可以减少IO操作，提高查询效率。

尽管索引可以提高查询性能，但过多或不正确使用索引也可能导致性能下降，因此在设计表结构和索引时需要谨慎考虑。常见的索引类型包括普通索引、唯一索引、主键索引、全文索引等，根据实际需求选择合适的索引类型来优化数据库查询性能。

2. 索引的使用

2.1 查看索引

show index from 表名;

主键在MySQL中默认也会创建一个索引

当你在表中定义主键时，MySQL会自动为主键列创建一个索引。这个索引是唯一索引，它的目的是确保主键列的值是唯一的，同时也可以加速对主键列的查询操作。
由于主键索引的唯一性约束，它比普通索引更加严格。在查询中使用主键进行条件筛选时，MySQL可以直接定位到符合条件的唯一行，从而提高查询效率。
需要注意的是，虽然主键索引默认是在创建主键时自动创建的，但在某些情况下，你也可以手动定义主键索引的名称、类型或其他属性，以满足特定的需求。

2.2 创建索引

create index 索引表 on 表名(列名);

2.3 删除索引

drop index 索引名 on 表名;

3. 索引使用的注意事项

（1）删除索引，创建索引都是比较危险的操作，这些操作都会涉及到大量的IO，就可能把MySQL主机搞挂了。

（2）如何给一个已经包含大量数据的表添加索引呢？

答：部署新的数据库，用新的数据库代替旧的数据。

（3）我们一般不会使用SQL语句来实现复杂的逻辑，一般只是进行单纯的增删改查，复杂的逻辑，都是通过Java这样的业务代码来实现。

二、索引的核心内容——底层的数据结构

1. 前景引入

索引的底层数据结构是经典的面试题。

索引一定是引入了一些额外的数据结构，来增加查询的速度。默认情况下，进行条件查询操作，就是遍历表，一条一条数据都代入条件，引入索引就是要通过其他的数据结构，加快查询速度，减少遍历表的可能性。

用来加快查询的数据结构有：二叉搜索树（红黑树）、哈希表。这两种是否可以用来加快数据库的查询速度呢？

（1）哈希表：只能查询key相等的情况，无法进行大于或小于这样的范围查询。经过hans函数的映射，原来key之间的大小关系，不能反应到计算出来的hash值的大小关系，也无法决定下标的大小关系，如下：

select * from id < 100 and id >10;

（2）二叉搜索树：红黑树里面的元素是有序的，可以进行范围查询了。但是在红黑树中，找到中序遍历的下一个后继元素，这样的操作，也未必高效！具体的原因如下：

① 很有可能需要往父节点上一系列回溯，才能找到后继，这个问题也可以通过“线索化”的方式来解决，但是要付出更多的存储空间。

② 红黑树也是二叉搜索树，当元素非常多的时候，就会使树的高度变得比较高，树的高度越高，进行查询的效率就越低，高度每增加一层，比较次数就增加1。我们在回归到数据库的数据存储位置上，数据存储在硬盘上，上述的每次比较都需要进行一次硬盘IO操作了，因此红黑树不适合于大规模在硬盘上管理数据的场景。

2. 索引使用的数据结构

2.1 基础版—— B 树

索引的B树结构是一种常见的索引结构，被广泛应用于数据库系统中以加速数据检索操作。下面是索引的B树结构的主要特点：

（1）根节点：B树的根节点包含至少两个子节点，用于存储索引键和指向子树的指针。

（2）内部节点：除了根节点外，其余内部节点都至少包含m/2个子节点，其中m是B树的阶数。内部节点存储索引键值，用于指示数据所在的子树，从而帮助快速定位数据。

（3）叶子节点：B树的叶子节点是存储实际数据（或数据的引用）的地方，叶子节点之间通过指针进行连接，形成一个有序链表。叶子节点存储的数据通常按照索引键的顺序排列。

（4）0阶数：B树的阶数m决定了每个节点中最多可以包含的子节点数量。通常情况下，B树的阶数会根据存储设备的性能和数据量进行选择，以达到平衡查询性能和空间利用率的目的。

（5）平衡性：B树保持平衡，即树的所有叶子节点位于相同的深度。通过节点的分裂和合并操作来维持平衡状态，确保检索效率。

（6）搜索操作：在B树中进行搜索操作时，从根节点开始逐层查找，根据索引键值确定下一步要访问的子节点，直到找到目标数据所在的叶子节点。

（7）插入和删除：插入和删除操作会引起B树的结构变化，可能导致节点的分裂或合并。插入时，需要确保新节点插入后仍然保持平衡；删除时，需要调整节点结构以维持平衡性。

总的来说，索引的B树结构通过多路平衡查找树的特点，提供了高效的数据检索能力，适用于大型数据库系统中对数据进行快速查询和排序的需求。

因此就引入了 B 树，本质上是一棵 N 叉搜索树。每个节点上可以存储多个元素，延伸出多个子树，表示同样数量的数据，需要的结点就少了，对应的树的高度也大大降低了。

图示如下：

30,40,50,60是“一拍脑门想出来的”（随便给的），每棵子树就是对应着这些数据的中间范围。

总结：

（1）每个结点上的这些key也是有序排列，比较的时候可以直接二分查找。

（2）B树也会控制某个结点上保存的key不会太多，如果插入更多元素，使key变多了，就会使结点分裂出更多的子树出来。

（3）多个数据，都是放在一块连续的存储空间上，进行比较的手，一次硬盘 IO 就能读出整个结点，就可以直接完成上述的比较（进行多次比较，实际上只有一次磁盘IO）

其实数据库索引的数据结构的最终形态是 B+ 树，相当于 B 树的升级版！！！

2.2 升级版—— B+ 树

B+ 树同样也是N叉搜索树，B+ 树存在的前提使用了 innodb 这个存储引擎

B+树是一种常见的索引结构，也被广泛应用于数据库系统中以加速数据检索操作。与B树相比，B+树具有一些特定的结构特点：

（1）叶子节点存储数据：与B树不同，B+树的叶子节点存储了全部的关键字和对应的数据记录，而非仅存储部分关键字。这使得B+树在范围查询和顺序访问时具有更好的性能。

（2）叶子节点之间有序连接：B+树的叶子节点之间通过指针进行连接，形成一个有序链表。这样可以方便地进行范围查询操作，同时也有利于顺序遍历整个索引。

（3）内部节点不存储数据：B+树的内部节点只存储索引键值，并且不包含指向数据记录的指针。这意味着所有的数据都存储在叶子节点，从而提高了内部节点的存储密度，减少了树的高度，提高了磁盘I/O效率。

（4）阶数：B+树的阶数m决定了每个节点中最多可以包含的子节点数量。与B树类似，B+树的阶数会根据存储设备的性能和数据量进行选择。

（5）平衡性：B+树同样保持平衡，确保所有叶子节点位于相同的深度。通过节点的分裂和合并操作来维持平衡状态，以提高检索效率。

（6）搜索操作：在B+树中进行搜索操作时，从根节点开始逐层查找，根据索引键值确定下一步要访问的子节点，直到找到目标数据所在的叶子节点。

总的来说，B+树通过其特有的叶子节点存储数据、有序叶子节点连接等特点，提供了高效的范围查询和顺序访问能力，适用于需要频繁进行范围查询和排序操作的数据库系统。因此，在许多数据库管理系统中，B+树常被用作索引结构来提高数据查询的效率。

图示如下：

B+ 树相比于 B 树的优势：

（1）非常方便进行遍历和范围查询

（2）当前任何一次查询操作，最终都是要落到叶子结点完成的（非叶子结点至少指示了其索引的范围，真正的数据是落到了叶子结点处），查询任何数据，经历的硬盘IO的次数都是一样的，这个时候，查询操作消耗的时间是稳定的。

（3）由于叶子结点是数据的全集，对应的非叶子结点中都是重复出现的数据，就可以把表每一行的数据，最终都关联到叶子结点这一层，非叶子结点中只保存一个单纯的key值即可。例如：

别忘了，数据库每一行有很多列。

student(id,name,classId,gender,score……)

此时使用id这一列来做索引，这里的 B+ 树的非叶子结点，都只需要保存一个id这样的值就行了，到了叶子结点这里，每个叶子结点不光要保存id，还要把后续的name，classID等信息也保存起来。

优点：这样组织之后，非叶子结点占用的空间就比较小（非叶子结点只存id），此时非叶子结点就可以缓存到了内存中！！！（当然，这份数据必然要在硬盘上也保存一份，为了提高查询速度，就可以把这部分结构放到内存中）。内存的速度可比硬盘的速度快得多！！！

我们使用select语句进行查询时，在mysql客户端看到是一个表格结构，其实在底层不然。

实际上底层的结构就是B+ 树的结构，就会按照主键的索引（在前面已经提过，主键默认是有索引的）的这个B+ 树的叶子结点来保存每一行数据。如果一个很大的表，创建索引，就需要构造一个很大的 B+ 树，这个过程可能是非常慢的，造成大量的开销。

B+树这个结构，是一直存在的