MYSQL复习——第五章：索引与算法

• 索引分类
  • 底层实现角度 / 数据结构角度 / 物理角度
    • R树索引
    • 全文索引
    • 哈希索引
    • B树索引
      • ICP优化（提前在存储引擎层过滤where）
      • MRR优化（回表查询优化）
      • 非聚集索引（辅助索引）和 聚集索引
      • B+树与B树区别
  • 逻辑角度
    • 主键索引
    • 唯一索引
    • 普通索引
• 索引使用场景
• Cardinality值 / Selectivity索引选择性
• 补充

索引分类

按照数据结构：B树索引；Hash索引；全文索引

按照物理存储（主要指B树索引）：聚簇索引（聚集索引）；非聚集索引（辅助索引）

按照逻辑：主键索引；唯一索引；单列索引；复合索引

5.1 数据结构角度

• R树索引
  • 空间树索引，用于索引地理坐标
  • Innodb中不存在，PostgreSQL中存在
• 全文索引
  • 作用：用于查找数据库中的任意内容。
  • 实现方式：倒排索引，在Auxiliary Table(辅助表)中存储了单词和文档映射，有两种表现形式：
    • 1. inverted file index: {单词，单词所在文档ID}  eg. {old:1, 4} 表示old出现在文档1和文档4中
    • 2. full inverted index: {单词，(单词所在文档ID，文档中位置)}  eg.{old:(1:6), (2:1)} 表示old出现在文档1 的第6个单词
  • 优缺点：效率很低，一般用第三方工具（比如ES）代替
  • 早期只有Mysaim支持全文索引，5.6及之后Innodb也支持全文索引
• 哈希索引
  • 作用：映射到哈希散列桶进行查找，用于自适应哈希索引
  • 优缺点
    • 优点：很快，但只适用于 =, !=, in, not in等简单操作
    • 缺点：对于范围查找（大于，小于）无能为力
• B树索引（Innodb索引）
  • 存储内容
    • 非数据页的索引页（非叶子结点）存放：key + 数据页的偏移量；
    • 数据页的索引页存放（叶子节点）：key + 整行的数据记录
  • 备注：Innodb中虽然称作B树索引，这里的B树是指 B+ 树（关于 B+树 与 B树的区别见下面）
  • 特点：高扇出性，一般高度在2-4层。分为聚集和非聚集索引
  • 主键（聚集）索引 和 非主键（非聚集）索引

聚集索引（也称 主键索引，聚簇索引）：

• 表中数据按照主键顺序存放，存放的格式为： 主键key + 该主键对应那一行的所有数据

非聚集索引（也称 非聚簇索引，辅助索引，二级索引Secondary Index）：

• 除了主键索引（聚集索引）之外的索引都是非聚集索引
• 特点就是叶子节点不会存放所有数据，存放格式为： 该辅助索引 + 那一行辅助索引对应的主键key
• 因此如果索引没有覆盖，需要回表查询

例子：见下图

• MRR（Multi-Range Read）优化（回表查询优化）
  • 解决的问题：查询范围辅助索引时，如果没有覆盖索引需要回表查询。在MySQL5.6后，在查询前根据主键进行排序。将随机IO变成顺序IO。
  • 原理：在内存中将第一次返回的辅助索引按照主键索引进行排序
  • 实践：explain结果中,extra项会显示Using MRR
• ICP（Index Condition Pushdown）优化
  • 在MySQL5.6后，当使用ICP优化后，Extra会看到Using index condition提示。
  • 解决的问题：原先根据索引查找记录，在内存中再根据WHERE过滤。优化后MYSQL在取出索引的同时进行WHERE过滤，将这一步骤放在了存储引擎层。     

5.2 逻辑角度

主键索引：Innodb默认索引，不能重复，不能为Null；如果没主键，自动创建6个字节的主键

唯一索引：不能重复，允许为Null，并且可以有多个Null（唯一索引约束仅适用于非NULL值，确保这些值是唯一的）

• 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
• 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引（包含复合索引）: 基本的索引类型，没有唯一性的限制，允许为NULL值

• 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

• 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建复合索引

全文索引： 是目前搜索引擎使用的一种关键技术。

• 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

Innodb如何维护索引

语句：Alter table...或者CREATE/ DROP INDEX

维护方法：

对于主键索引：创建新表结构，复制原表到新表，删除原表，把新表改名成原表

对于辅助索引：加S锁

5.3 索引使用场景

• 应该加索引的场景
  • 经常出现在where之后的属性
  • 经常用于 order by 和 groupBy 的属性
  • join表时的on语句后面的属性
  • 经常用于 sum, count, avg 等聚合操作的属性
• 不应该加索引的场景
  • 多样性较小的属性（比如 gender）
  • 数据很少的表
  • 频繁更新 / 删除的表或属性
  • 字段太长的属性（索引太占空间）

5.4 Cardinality值

对取值范围广的字段加索引更合适，Cardinality值是数据库预估的范围值（该字段有多少个不同的value，多样性越多说明效果越好）。

数据库通过随机采样的方式预估该值。优化器会根据这个值来判断是否使用这个索引

这里有个公式：Index Selectivity = Cardinality / #T

• Index Selectivity：索引选择性，这个值越大约好，取值范围为(0, 1]
• Cardinality：该字段有多少个不同的value
• #T：表记录数

5.5 补充

（1）索引优化参考文档：CodingLabs - MySQL索引背后的数据结构及算法原理

（2）关于索引中的Null值及存储方式

• 聚簇索引：本身不允许为Null，所以不用考虑
• 非聚簇索引：Null被视为小于所有其他非Null值，包含Null的索引会在B+树最前面部分聚集。如果获取is null的数据，会从B+树最左边开始遍历，直到找到记录不是Null结束