🎺 索引

1. B+ 树

1) 数据结构

特点：

• N 叉树
  • 非叶子结点存放 key
  • 叶子节点：
    • 主键索引存放该主键下所有数据
    • 非主键索引存放主键的值
  • 叶子节点
    • 通过双向链表连接
    • 顺序 & 逆序查找快

MySQL 中节点个数分析

• 一个节点为一页 | 页的倍数
  • 假设为 一页，每页 default = 16KB
  • 假设 一个节点 key = 8B，维护前后指针 = 8B
  • 则一个节点大小为 16B
  • 除去 一页除去行记录的 其他固定数据所占字节数
  • 所以粗略估计 不到 1000 个节点最合适

2) 常见的树

种类	B+ 树	B 树
非叶子结点是否存放数据	×	√
树高	矮	高
叶子节点是否有链表连接	√	×
范围查询	更快	较慢，每次都是独立的二分查找
父节点是否在子节点中	√	×

3) 常见索引

种类	优点	缺点	使用场景
B+ 树	范围查询快，等值查询较快	-	支持等值 & 范围查询，且增删频繁的场景
Hash	等值查询快	不支持范围查询	K V 结构非关系型数据库
数组	等值 & 范围查询都快	增删成本高	静态数据

---

2. MySQL 索引

1) Hash 索引

2) B+ 树索引

① 主键索引

特点

• 键值唯一
• 非空
• 一张表只能有一个主键
• 若不声明主键
  • InnoDB 查找表中是否存在唯一索引 & 设置非空，若存在，则该字段为主键
  • 创建一个 6字节的自增主键

② 唯一索引

特点

• 值唯一
• 可为空值
• 可建立多个唯一索引
• 目的
  • 保证数据唯一性
• 唯一索引失效？
  • 添加唯一索引的字段为 null 时，唯一性约束失效
  • 因此，唯一索引的列，值不能为 null

添加唯一索引

③ 普通索引

特点

• 值可重复
• 可为空值
• 可建立多个
• 目的
  • 提高查找效率

添加普通索引

④ 前缀索引

特点

• 作用对象
  • 字符串类型数据
• 目的
  • 降低空间占用

3) 聚簇索引 VS 非聚簇索引

① 聚簇索引

• 特点
  • 索引结构 & 数据一起存放
• 对象
  • MySQL 主键索引
• 优点
  • 查询快
• 缺点
  • 最好是有序数据
  • 更新时，还需要更新索引数据，因为存放了所有列的数据

② 非聚簇索引

• 特点
  • 索引结构 & 数据分开存放
• 对象
  • 二级索引
• 优点
  • 更新时，如果不是主键，则不需要更新该索引
• 缺点
  • 最好是有序数据
  • 回表查询时降低查询效率

3) 全文索引

作用对象

• 文本类型 CHAR,VARCHAR,TEXT 支持的存储引擎
• MYISAM & MySQL(5.6) 原理
• 倒排索引

4) 空间数据索引

• 支持 OpenGIS 几何数据模型
• MYISAM & MySQL(5.7)

---

3. 索引优化

1) 独立的列 VS 联合索引

• 遵循最左前缀匹配原则
• 把频繁使用的列、区分度高的列放在前面

2) 前缀索引

• 只取前几个字符，实现索引占用空间小且快的效果
• MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY ，而且也无法使用前缀索引做覆盖扫描，前缀索引也有可能增加扫描行数
• 设置命令：alter table muran add index idx_name(announce_name(10));

3) 索引下推

• 版本
  • MySQL 5.6 & MYISAM & MySQL partition
• 功能
  • 在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少 回表 次数，同时也减少了返回到 Server 层的数据量，IO 次数减少
• 作用对象
  • 二级索引，主键索引自己就会做数据的判断，而且也不需要回表，因此只适用于二级索引
• 命令
  • 5.6默认开启，关闭指令：SET optimizer_switch = 'index_condition_pushdown=off';

4) 索引覆盖

• 要查询的字段正好为索引字段，不需要 回表 查询

5) 前缀索引

• 降低索引的占用空间，提高索引查询效率
• 无法利用前缀索引做 order by group by，也无法作为 覆盖索引

查看索引区分度

---

4. 优缺点 & 使用条件

1) 优点

• 提高查找效率，从而降低 IO 成本
• 降低排序成本，从而降低 CPU 消耗

2) 缺点

• 占用磁盘空间
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

3) 哪些场景下不适合索引？

1. 数据量少
2. 区分度不高
3. 频繁更新的字段

---

5. 索引失效

• 对索引列添加函数 [MySQL 8.0 开始，可以将添加了某个函数的索引整体作为索引保存]
• 对等号左边的索引添加表达式，右边无所谓
• 调用隐式函数转换 [MySQL 遇到字符串 & 数字比较时，自动将字符串转换为数字]
• 进行左模糊查询
• 遇到 not in | or & 存在非索引列
• 终止到范围查询
• 联合索引最左匹配原则
• 优化器优化

隐式转换问题:

• 字符串转换为数值类型时，非数字开头的字符串会转化为0，
• 以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果