目录
在上一篇文章SQL慢查询优化方式-CSDN博客，有提到几个概念：索引（覆盖索引、复合索引）和回表，那就需要深入了解一下MySQL的索引以及数据结构
还是以User表为例：
CREATE TABLE User (
user_id INT PRIMARY KEY,
name VARCHAR(255),
phone VARCHAR(20),
email VARCHAR(255),
role VARCHAR(50),
address VARCHAR(255),
birthday DATETIME
);一、索引结构
MySQL 中的索引是为了加速数据检索而设计的数据结构，索引可以帮助数据库系统减少查找数据所需的 I/O 操作次数，从而提高查询性能
不同的存储引擎可能支持不同类型的索引，但最常用的索引类型通常基于 B+ 树（B-Tree）
此处索引idx_userid_birthday采用的是B Tree
Hash索引：
哈希索引使用哈希算法将键值转换成固定长度的哈希码。
适用于等值查询，但对于范围查询则不太适用，因为哈希索引不保存键值的顺序
❓那为什么是B+ Tree，而不是B Tree、二叉查找树、平衡二叉树、红黑树呢
这里先不考虑Hash索引结构，那以最普通的二叉查找树开始，逐步说明各种树解决的问题以及面临的新问题，从而说明MySQL为什么选择B+树作为索引结构
1、二叉查找树(BST):树太高
特点：
- 每个节点最多有两个子节点
- 左子树中的所有节点的值小于根节点的值
- 右子树中的所有节点的值大于根节点的值
优点：插入、删除和查找的时间复杂度在平均情况下为 O(log n)
缺点：最坏情况下，如果插入的数据是递增或递减序列，树会退化成链表，时间复杂度退化为 O(n)
树太高，就会导致查询时间会变慢
2、平衡二叉树(AVL):旋转耗时
为了避免二叉查找树的过高，平衡二叉树通过旋转，使得所有节点的左右子树高度差不能超过1
🌰：树中节点的值依次为：1, 2, 3, 4, 5, 6
BTS按照递增数据插入：
1
\
2
\
3
\
4
\
5
\
6AVL按照递增顺序插入：
2
/ \
1 4
/ \
3 5
\
6可以看出
- 二叉查找树的高度为6，完全不平衡
- 平衡二叉树的高度为 3，通过旋转操作保持树的平衡，这是一个更平衡的状态
有兴趣的话，可以看下平衡二叉树的插入过程：
//插入2
1
\
2
//插入 3
2
/ \
1 3
//插入4
//此时，节点 3 的左子树高度为 1，右子树高度为 1，不需要旋转
2
/ \
1 3
\
4
//插入5
//此时，节点 4 的左子树高度为 0，右子树高度为 1，不需要旋转
//但是，节点 3 的左子树高度为 1，右子树高度为 2，需要进行旋转
//旋转前
2
/ \
1 3
\
4
\
5
//旋转后
2
/ \
1 4
/ \
3 5
//插入 6
//此时，节点 5 的左子树高度为 0，右子树高度为 1，不需要旋转
2
/ \
1 4
/ \
3 5
\
6 2
/ \
1 4
/ \
3 5
\
6从上述的插入过程，可以看出最多只需要一次旋转，但是在删除的时候会导致树失衡，，AVL需要维护从被删除节点到根节点这条路径上所有节点的平衡
缺点：由于旋转的耗时，AVL树在删除数据时效率很低
因此AVL在实际使用中并不广泛
3、红黑树:树太高
与AVL树相比，红黑树并不追求严格的平衡，而是大致的平衡：只是确保从根到叶子的最长的可能路径不多于最短的可能路径的两倍长
与AVL树相比，红黑树的查询效率会有所下降，这是因为树的平衡性变差，高度更高
但红黑树的删除效率大大提高了，因为红黑树同时引入了颜色，当插入或删除数据时，只需要进行O(1)次数的旋转以及变色就能保证基本的平衡，不需要像AVL树进行O(lgn)次数的旋转。
总的来说，红黑树的统计性能高于AVL
因此，在实际应用中，AVL树的使用相对较少，而红黑树的使用非常广泛。
例如：Java中的TreeMap使用红黑树存储排序键值对；Java8中的HashMap使用链表+红黑树解决哈希冲突问题(当冲突节点较少时，使用链表，当冲突节点较多时，使用红黑树)
对于数据在内存中的情况（如上述的TreeMap和HashMap），红黑树的表现是非常优异的
但是对于数据在磁盘等辅助存储设备中的情况（如MySQL等数据库），红黑树并不擅长，因为红黑树长得还是太高了
当数据在磁盘中时，磁盘IO会成为最大的性能瓶颈，设计的目标应该是尽量减少IO次数；而树的高度越高，增删改查所需要的IO次数也越多，会严重影响性能
4、B树：磁盘
B树也称B-树(其中-不是减号)，是为磁盘等辅存设备设计的多路平衡查找树，与二叉树相比，B树的每个非叶节点可以有多个子树
特点：
- 每个节点可以有多个子节点，每个节点可以存储多个键值对
- 所有的叶子节点都在同一层，每个节点的高度相同
- 每个节点可以存储更多的键值对，减少了树的高度
这张图乍一看，是不是挺难懂的，哈哈哈，不急……
还是以User表为例，基于userid字段的B树索引，从上图可以看出
假设B树的根节点包含键值 16 和 34，并且有三个指针 P1、P2 和 P3。这些指针指向的子树的数据范围如下：
- P1 指向的数据范围：小于 16
- P2 指向的数据范围：16 到 34
- P3 指向的数据范围：大于 34
需要查找 userId 为 29 的记录。具体步骤如下：
- 根节点访问：访问根节点，根节点包含键值 16 和 34
- 确定子节点：16< 29 <34，指向在P2指针中，遍历P2的子树，由于25< 29 <31，继续向下
- 到达叶节点：29在磁盘块8中，读入内存
可以看出经历3次磁盘IO操作后，找到userId为29的数据
但是当在叶节点中查找到29后，在叶节点中，如果该节点包含多个值，通常会从左到右依次检查这些值，直到找到目标值或确认该值不存在
B树的叶节点通常是有序的，因此可以顺序比较，直到找到匹配的值
因此B树的优缺点是：
优点：减少了磁盘 I/O 次数，因为每个节点可以存储更多的键值对。适合大规模数据存储
缺点：
- 每个节点都有key，同时也包括data，而每个页存储空间是有限的
- 节点内部的键值对仍然需要进行线性查找，效率较低
- 当存储的数据量很大的时候，会导致深度较大，增加查询时磁盘io次数，进而影响查询性能
5、B+树
B+树也是多路平衡查找树，与B树相比：
-
B树中每个节点（包括叶节点和非叶节点）都存储真实的数据，B+树中只有叶子节点存储真实的数据，非叶节点只存储键
在MySQL中，这里所说的真实数据，可能是行的全部数据（如Innodb的聚簇索引），也可能只是行的主键（如Innodb的辅助索引），或者是行所在的地址（如MyIsam的非聚簇索引），关于聚簇索引、辅助索引和非聚簇索引这些概念会在下文中解释，先做了解 -
B树中一条记录只会出现一次，不会重复出现，而B+树的键则可能重复重现——一定会在叶节点出现，也可能在非叶节点重复出现。
-
B+树的叶节点之间通过双向链表链接。
-
B树中的非叶节点，记录数比子节点个数少1；而B+树中记录数与子节点个数相同
6、总结
总结一下各种树解决的问题以及面临的新问题：
-
二叉查找树(BST)：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表；
-
平衡二叉树(AVL)：通过旋转解决了平衡的问题，但是旋转操作效率太低；
-
红黑树：通过舍弃严格的平衡和引入红黑节点，解决了AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多；
-
B树：通过将二叉树改为多路平衡查找树，解决了树过高的问题；
-
B+树：在B树的基础上，将非叶节点改造为不存储数据的纯索引节点，进一步降低了树的高度；此外将叶节点使用指针连接成链表，范围查询更加高效
二、索引类型
在 MySQL 中，主要有两种类型的索引：聚簇索引 和辅助索引。不同存储引擎对这两种索引有不同的实现方式
InnoDB 存储引擎
InnoDB 存储引擎使用 B+树 来实现索引。以下是 InnoDB 中索引的具体实现：
聚簇索引：
- 聚簇索引是 InnoDB 的默认索引类型
- 聚簇索引的叶子节点存储了行的全部数据
- 非叶子节点存储键值和指向叶子节点的指针
辅助索引：
- 辅助索引用于非主键列
- 辅助索引的叶子节点存储主键值和行的地址
- 非叶子节点存储键值和指向叶子节点的指针
MyISAM 存储引擎
MyISAM 存储引擎使用 B树 来实现索引。以下是 MyISAM 中索引的具体实现：
主键索引：
- 主键索引的叶子节点存储行的全部数据。
- 非叶子节点存储键值和指向叶子节点的指针
辅助索引：
- 辅助索引的叶子节点存储主键值和行的地址
- 非叶子节点存储键值和指向叶子节点的指针
主键索引
每个表只能有一个主键索引。
主键索引不允许重复值，并且不能为空。
ALTER TABLE User ADD PRIMARY KEY (user_id);唯一索引
保证索引的列中的值是唯一的，但允许空值的存在
🌰：为 email 列添加一个唯一索引，确保每个用户的邮箱地址是唯一的
ALTER TABLE User ADD UNIQUE INDEX idx_email (email);普通索引
最基本的索引类型，可以创建在任何数据列上
🌰：为 phone 列添加一个普通索引，以便快速查找用户信息
ALTER TABLE User ADD INDEX idx_phone (phone);多列索引（Multiple-column Index）：
也叫复合索引，在多个列上创建索引，可以提高查询性能，特别是当查询条件涉及这些列时
ALTER TABLE User ADD INDEX idx_name_role (name, role);覆盖索引
覆盖索引是指索引包含了查询所需的所有列
🌰：如果查询用户的姓名和电话，可以创建一个覆盖索引
ALTER TABLE User ADD INDEX idx_name_phone (name, phone);
SELECT name,phone
FROM User;全文索引
用于全文本搜索功能。通常用于较大的文本字段
🌰：为 name 列创建一个全文索引，以便执行全文搜索
空间索引
用于地理空间数据的索引
虽然 User 表没有这样的字段，但如果 address 字段包含地理坐标，可以考虑创建空间索引
-- 假设 address 包含地理坐标
ALTER TABLE User ADD SPATIAL INDEX idx_address (address);上文已经讲了如何对SQL进行优化：SQL慢查询优化方式-CSDN博客
那在建表的时候最好将索引考虑进去，减少对SQL优化的操作
三、哪些情况适合创建索引?
创建索引是为了提高数据库查询性能，但在实际应用中需要根据具体情况来决定是否创建索引。以下是一些适合创建索引的情况，并结合 User 表的具体示例进行说明
1、字段的数值有唯一性的限制
解释：如果某个字段具有唯一性限制（例如主键），应该创建唯一索引。
示例：假设 User 表中有 user_id 字段作为主键，可以创建唯一索引
2、频繁作为 WHERE 查询条件的字段
解释：如果某个字段经常出现在 WHERE 子句中，创建索引可以显著提高查询速度。
示例：假设 phone 和 email 字段经常用于查询，可以创建索引
CREATE INDEX idx_phone ON User(phone);
CREATE INDEX idx_email ON User(email);3、经常 GROUP BY 和 ORDER BY 的列
解释：如果某个字段经常用于 GROUP BY 或 ORDER BY 子句，创建索引可以提高这类查询的性能
示例：假设 role 字段经常用于分组或排序，可以创建索引
CREATE INDEX idx_role ON User(role);4、DISTINCT 字段需要创建索引
解释：如果某个字段经常用于 DISTINCT 查询，创建索引可以提高查询性能
示例：假设 role 字段经常用于 DISTINCT 查询，可以创建索引
CREATE INDEX idx_role ON User(role);5、多表 JOIN 连接操作时，创建索引注意事项
解释：如果某个字段用于多表连接查询，创建索引可以提高连接操作的性能
示例：假设 User 表与其他表（如 Order 表）通过 user_id 字段进行连接，可以创建索引
CREATE INDEX idx_user_id ON User(user_id);四、哪些情况不适合创建索引?
1、在where中使用不到的字段，不要设置索引
2、数据量小的表最好不要使用索引
3、有大量重复数据的列上不要建立索引
4、避免对经常更新的表创建过多的索引
5、不建议用无序的值作为索引
五、创建索引的注意事项
1、避免过度索引
索引会占用额外的存储空间，并且在插入、更新或删除数据时会增加一定的开销。因此，不要过度创建索引
2、考虑字段的选择性
如果字段的选择性不高（即大部分值都是重复的），创建索引的效果可能不佳。例如，address 字段可能有很多重复值，创建索引效果不佳
3、考虑字段的更新频率
如果字段经常被更新，创建索引可能会增加维护成本。例如，name 字段可能经常被更新，创建索引可能不合适
4、考虑字段的查询频率
对于不常查询的字段，没有必要创建索引。例如，birthday 字段可能不常用于查询，创建索引可能不合适
5、考虑索引的组合
如果查询涉及多个字段，可以考虑创建组合索引。例如，如果经常查询 User 表中的 phone 和 email 字段，可以创建组合索引
CREATE INDEX idx_phone_email ON User(phone, email);
扫一扫
2831
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
