数据库 | Mysql - [索引]

§1 定义

索引是高效获取数据的数据结构，是一个 有序、可以快速查询的 B+ 树

作用

• 提高查询效率
• 将 随机 IO 转化为 顺序 IO
• 避免在分组、排序时使用临时表

缺点

• 降低写操作（包括增删改）的效率，因为要维护索引
• 索引也相当于是一个表，因此需要占用空间

§2 数据结构

Mysql 中使用 B+Tree，是一种多路平衡二叉树

其他数据结构为什么不可以作为数组

• 数组
  可以做到快速查询
  但增删、变动时会带来元素位移的问题，可能涉及到大量数据的复制
• hash
  可以做到快速查询
  但 hash 本质是无序表，不能做到快速范围查询，也不能解决排序带来的问题
• 普通二叉树
  增删改查及范围、排序时都做到 $O(logN)$
  但是树不能解决 不平衡问题，极度不平衡的二叉树会退化为链表
  因此普通二叉树不稳定
• 平衡二叉树
  平衡二叉树在普通二叉树的基础上解决了平衡问题
  但查询一半的元素(叶子节点)时，IO 次数都等于树高，因此影响性能
• B Tree
  B 树每个节点可以最多保存 2 个值
  第三个值时会将当前节点变成一个平衡二叉树，并将此数的根向当前节点的父节点进位
  每个节点可以最多同时具有 3 个子节点，因此相当于一个三叉树，每次查询相对平衡二叉树少一次 IO
  如下图
  

为什么要引入 B+Tree
InnoDB 的数据页
页的设计类似于 CPU 缓存中的缓存行，是 InnoDB 进行磁盘管理的最小单位，虽然系统对磁盘的读写是以更小的 块(block) 为单位的

• 写、删除操作都是至少对一页的操作
• 查询时，也是将一页数据载入内存

查询页大小

show global status like 'Innodb_page_size'
1

Innodb 引擎下，默认一页存放 16384 (16K)

B Tree 的问题

• 每一页的有效数据量少
  一个 B Tree 的节点如下图所示
  每个节点都会携带数据或指向数据的指针，这二者都会占用空间
  因此，InnoDB 载入一块数据进内存时，会因此少载入索引信息
  
• 不适合处理范围查询
  同时，并没有一层同时存在所有的索引的信息，不利于处理范围查询，需要中序遍历

B+Tree
B+ 树每个节点可以最多保存 2 个值
节点存入第三个值时会将当前节点变成一个平衡二叉树

• 平衡二叉树的根节点 (即原节点的第二个值) 加入原节点的父节点
• 平衡二叉树的左节点是原节点的第一个值
• 平衡二叉树的右节点是原节点的第二、三个值 (局部的根节点会下沉)
  每个节点可以最多同时具有 3 个子节点

如下图所示
注意下图所示不是标准的数据结构，而是 InnoDDB 的数据页结构

B+Tree 具有下面特性

• 非叶子节点不在包含数据，只包括可以定位用的索引本身
• 叶子节点包含索引的全部数据，包括索引和指向的数据
• 所有叶子节点通过链表相连，因此获取全部数据不再需要中序遍历

B+Tree 优点

• 可以进一步压缩 IO
  并不是 B+Tree 的高度比 B-Tree 低，实际上可能会高一层用于放置所有节点信息
  而是每个数据页可以容纳更多的 B+Tree 节点，使每次 IO 可以向内存加载更多的索引信息，做到减少 IO 次数
• 叶子节点相连，适合处理范围查询

§3 分类

按索引中列和值的性质

• 主键索引：主键索引的列会被认为是主键，主键索引必是 唯一索引
• 唯一索引：索引中的值是惟一的不允许重复，但 null 值允许出现多个
• 普通索引：除主键索引、唯一索引

按索引列数量

• 单值索引：只有一个列
• 复合索引：使用多个列
  也叫多值索引、聚集索引、聚合索引

按存储位置

• 聚簇索引：索引存储位置就与数据位置重叠
• 非聚簇索引：索引需要额外的空间存储

§4 指令

创建

CREATE [UNIQUE] INDEX index_name ON table_name(column_name(length));
// 或
ALTER TABLE table_name ADD [UNIQUE] INDEX index_name ON (column_name(length));
1
2
3

删除

DROP INDEX index_name ON table_name;
1

查看

SHOW INDEX FROM table_name;
1

§5 索引失效

• 使用复合索引时不满足最左原则
• 复合索引中某列使用范围查询后，其后的列都失效
  如 index(a,b,c)，查询时 where a=1,b>2,c=3，c 失效
• like 但没有使用最左匹配时，索引失效
  如 a like ‘%eeee’ 索引失效， a like ‘eeee%’ 不失效
• 表关联时在被驱动表上使用子查询，可能索引失效
• 乱序使用索引的排序规则进行排序时，索引失效
  index (a ASC,b DESC) 时
  使用 a ASC, b DESC 排序，使用索引
  使用 a DESC, b ASC 排序，使用索引
  使用 a ASC,b ASC 排序，索引失效
• 对索引列进行操作，如计算、函数、类型转换等
• null、not null 可能造成索引失效
• 不等于(!= 或 <>)可能造成索引失效
• or 可能造成索引失效
• 优化器认为扫全表比走索引新能开销还少时
  这是因为 Mysql 底层是基于数据页存储的，每次将数据载入内存都是整页操作
  当数据很多并且很碎时，按索引进行筛选可能导致多次读取同一个数据页进入内存，而扫全表只读一次