MySQL索引底层数据结构与算法、存储引擎

索引

        索引是帮助MySQL高效获取数据的排好序的数据结构

索引的优缺点

优点:

1. 通过创建唯一索引可以保证数据库表中每一行数据的唯一性；
2. 可以给所有的 MySQL 列类型设置索引；
3. 可以大大加快数据的查询速度，这是使用索引最主要的原因；
4. 在实现数据的参考完整性方面可以加速表与表之间的连接；
5. 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。

缺点:

1. 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加；
2. 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间；
3. 如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸；
4. 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

二叉树

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列、以提高查找效率

1. 二叉树的组成：每个节点包含一个值和指向左右子节点的指针。
2. 节点分类：根节点是树的顶部节点，没有父节点；叶节点是没有子节点的节点；非叶节点是至少有一个子节点的节点。
3. 遍历方式：

• 前序遍历（Preorder Traversal）：根-左-右
• 中序遍历（Inorder Traversal）：左-根-右
• 后序遍历（Postorder Traversal）：左-右-根
• 层序遍历（Level Order Traversal）：按层从上到下、从左到右的顺序遍历

如果数据是递增的，叶子单边增长，树会越来越高，如果查询最后一个数据，就需要全表扫描，效率极低

根节点是二叉树中第一个添加的数据，它是整个树的起点，小于根节点的值放在左子节点，大于根节点的值放在右子节点

红黑树

红黑树（Red-Black Tree）是一种自平衡的二叉搜索树，它在插入和删除操作时能够保持树的平衡。

其时间复杂度为 O(log n)，其中 n 是树中节点的数量。

二叉搜索树性质：红黑树是一棵二叉搜索树，其中每个节点的左子树的关键字都小于节点自身的关键字，右子树的关键字都大于节点自身的关键字。

节点颜色属性：每个节点被标记为红色或黑色。根节点为黑色。红色节点的两个子节点都是黑色。

黑色平衡性质：从根节点到每个叶子节点的路径上，黑色节点的数量相同。这保证了最长路径不超过最短路径的两倍，维持了树的平衡。

自平衡操作：在插入和删除节点时，通过对节点进行着色和旋转操作，保持了红黑树的平衡性和性质。

缺点：当数据量大的时候，红黑树的树高会非常大，查询时需要多次磁盘IO，查询效率并不高。

频繁的插入和删除操作，红黑树的平衡调整可能较为频繁，导致性能下降。

红黑树的查询效率是在平衡的情况下得到的。如果红黑树不平衡，比如出现连续的插入或删除操作，可能会导致树的高度增加，进而影响查询效率。

B-Tree

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列
• B树增加数据时会自动平衡
• 由于B树的平衡性和有序性，根据关键字的大小关系，可以快速定位到目标数据所在的节点

特点：

自平衡：B-Tree在每次插入或删除操作后会自动进行平衡操作，以保持树的平衡状态。这意味着树的高度保持相对较小，从而提供了更快的搜索性能。

多路搜索：B-Tree的每个节点可以包含多个子节点，这使得B-Tree能够同时搜索多个子节点，从而减少了搜索的时间复杂度。

有序性：B-Tree的每个节点内的键值是有序存储的，这使得在B-Tree中进行范围查询更加高效。

磁盘友好：B-Tree的设计考虑了磁盘访问的效率，它的节点大小通常与磁盘块大小相匹配，以减少磁盘访问次数

B+Tree（B-Tree变种）

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引；
• 叶子节点包含所有索引字段；
• 叶子节点用指针连接，提高区间访问的性能；

B+树是B树的一种变种，具有相似的特性和自平衡性；

B+树的非叶子节点仅存储关键字和子节点的指针，所有的数据存储在叶子节点上，并通过链表连接；

叶子节点按照关键字的顺序排列，形成有序链表，这使得B+树在范围查询和顺序访问时非常高效；

查询操作通过在非叶子节点进行二分查找定位到叶子节点，然后在叶子节点中进行关键字的查找操作；

插入和删除操作涉及节点的分裂和合并，以保持树的平衡性

#mysql一页的大小约为16KB
show global status like 'Innodb_page_size';

一排的叶子节点称为一个数据页，mysql中一页的大小默认约为16KB；以索引为bigint类型为例，一个索引占8个字节，InnoDB指针占6个字节，则一个数据页大概可以存储1170【16KB/(8+6)Byte】条索引，下一页同理。叶子节点的索引加数据一般不会超过1KB，所以叶子节点一页大概可以存放16条索引加数据。所以当树高为3时，B+Tree大概可以存储2千万+的数据【1170*1170*16=21902400】，所以在此情况下，查询任意数据最多只需要访问磁盘IO h次(h是树的高度)。而B-Tree若要存放两千多万数据大概需要六七层高，故效率比B+Tree低

为什么Mysql使用B+树而不是B树？

MySQL的数据查询通常是针对存储在磁盘上的数据进行的。然而，与内存相比，磁盘的每次IO操作耗时较长。

在B+树中，非叶子节点仅存储索引而不存储实际数据。这使得非叶子节点能够容纳更多的索引信息，从而提高查询效率。

数据项只存储在叶子节点上，并通过指针进行链接，形成一个有序的叶子节点链表。通过这种方式，当进行查询时，数据库可以首先从根节点开始，根据索引逐层定位，避免不必要的磁盘IO操作。

通过减少树的高度，B+树减少了查询过程中需要进行的IO操作次数，从而显著提高了查询的效率。通过充分利用非叶子节点存储更多的索引，B+树能够有效地组织和管理大量的数据，并提供高效的数据访问方式。

Hash

是一种基于哈希函数和树结构的数据组织方式，键值对的集合（key，value）

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+ 树索引更高效
• 仅能满足 “=”，“IN”，不支持范围查询(不使用Hash原因)
• hash冲突问题（哈希冲突（Hash Collision）是指不同的关键字经过哈希函数计算后得到相同的哈希值，导致它们在哈希表中存储位置相同的情况）

通过哈希计算得到Hash值，根据Hash值，找到对应的节点，可以快速定位到对应的存储位置。

存储引擎

MyISAM索引文件和数据文件是分离的(非聚集)

InnoDB索引实现(聚集)

• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引-叶节点包含了完整的数据记录
• 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
• 为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

InnoDB、MyISAM

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL都默认封装成事务，自动提交，尽量把多条SQL放在begin和commit之间，组成一个事务； 
2. InnoDB支持外键，而MyISAM不支持；
3. InnoDB是聚集索引，表数据文件本身就是按B+树组织的一个索引结构文件；
4. 聚集索引-叶节点包含了完整的数据记录；
5. 建议InnoDB表必须建主键（提高效率），推荐使用整型的自增主键（效率更高），不键主键并且找不到能键主键列的情况下InnoDB表会把隐藏列Row_id作为主键，而Myisam可以没有主键；
6. 非主键索引结构叶子节点存储的是主键值 (一致性和节省存储空间)；
7. 非主键索引查询需要两次查询，先查询到非主键索引，然后回表再通过主键查询到数据。
8. InnoDB不保存表的具体行数，而MyISAM用一个变量保存了整个表的行数；
9. MyISAM是非聚集索引，使用B+树作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和非主键索引是独立的；
10. InnoDB的B+树主键索引的叶子节点就是数据文件，非主键索引的叶子节点是主键的值；MyISAM的B+树主键索引和非主键索引的叶子节点都是数据文件的地址指针（查询效率InnoDB比MyISAM高的原因）；
11. Innodb存储文件有frm、ibd，而Myisam是frm、myd、myi

         Innodb：frm是表定义文件，ibd是数据文件（mysql8.0版本把frm文件放到了ibd文件里）

         Myisam：frm是表定义文件，myd是数据文件，myi是索引文件