§1 定义
索引是高效获取数据的数据结构,是一个 有序、可以快速查询的 B+ 树
作用
- 提高查询效率
- 将 随机 IO 转化为 顺序 IO
- 避免在分组、排序时使用临时表
缺点
- 降低写操作(包括增删改)的效率,因为要维护索引
- 索引也相当于是一个表,因此需要占用空间
§2 数据结构
Mysql 中使用 B+Tree,是一种多路平衡二叉树
其他数据结构为什么不可以作为数组
- 数组
可以做到快速查询
但增删、变动时会带来元素位移的问题,可能涉及到大量数据的复制 - hash
可以做到快速查询
但 hash 本质是无序表,不能做到快速范围查询,也不能解决排序带来的问题 - 普通二叉树
增删改查及范围、排序时都做到 O ( l o g N ) O(log N) O(logN)
但是树不能解决 不平衡问题,极度不平衡的二叉树会退化为链表
因此普通二叉树不稳定 - 平衡二叉树
平衡二叉树在普通二叉树的基础上解决了平衡问题
但查询一半的元素(叶子节点)时,IO 次数都等于树高,因此影响性能 - B Tree
B 树每个节点可以最多保存 2 个值
第三个值时会将当前节点变成一个平衡二叉树,并将此数的根向当前节点的父节点进位
每个节点可以最多同时具有 3 个子节点,因此相当于一个三叉树,每次查询相对平衡二叉树少一次 IO
如下图
为什么要引入 B+Tree
InnoDB 的数据页
页的设计类似于 CPU 缓存中的缓存行,是 InnoDB 进行磁盘管理的最小单位,虽然系统对磁盘的读写是以更小的 块(block) 为单位的
- 写、删除操作都是至少对一页的操作
- 查询时,也是将一页数据载入内存
查询页大小
show global status like 'Innodb_page_size'
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));
删除
DROP INDEX index_name ON table_name;
查看
SHOW INDEX FROM table_name;
§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 底层是基于数据页存储的,每次将数据载入内存都是整页操作
当数据很多并且很碎时,按索引进行筛选可能导致多次读取同一个数据页进入内存,而扫全表只读一次