主键在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+树这个结构，是一直存在的

如果你的表创建了主键，自然是通过你创建的主键的索引的 B+ 树来组织所有行；
如果你没有创建主键，mysql其实生成了一个隐藏的主键，按照隐藏主键构造 B+ 树来组织数据；

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

答：非主键索引构造的 B+ 树叶子结点通常存储着对应的主键索引。这种设计方式可以帮助数据库系统在使用非主键索引进行查询时快速定位到主键值，然后再通过主键值到主键索引构造的B+树上找到实际的数据记录。

具体如下：

现在有一张表：students(id int primary key, name varchar(32), class_id int);name和id都有索引，class_id 没有索引

一：当按照名字查询的时候，就是在这个name构造的B+树上，找到相对应的叶子结点，拿到id的值或者说索引；
二：然后通过id索引，回到主键索引的B+ 树上再查询一次，多查一次的过程称为“回表”；

补充：由于class_id 这一列没有创建索引，所以就需要按照 “遍历” 的方式（就是叶子结点形成的链表），一条一条记录代入条件。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/283188