深入剖析Mysql索引原理

深入剖析Mysql索引原理

数据库索引是什么？

数据库索引，是数据库管理系统（DBMS）中一个有序的数据结构，以协助快速查询、更新数据库表中的数据。

索引数据结构选择与演变

数组

特点：数组是一块连续的存储空间。通过下标查询很快，时间复杂度为O(1)

存在的问题：插入耗时比较长

链表

特点：不连续的存储空间，插入快，但查询较慢

存在的问题：查询好使较长

二叉查找树

特点：左子树的节点小于父节点的值；右子树的节点大于父节点的值。

缺点：当顺序插入时，二叉树会退变为链表，导致查询效率低下

平衡二叉树（AVL树）

由于上面二叉树会存在退化成链表的情况，所以就出现了平衡二叉树。

特点

保证左右子树深度差绝对值不超过1

平衡策略

平衡二叉树(AVL树)是通过左旋或者右旋来保证树的平衡

左旋

右旋

存储索引示意图

存在问题

每个节点最多只有两个分叉，当我们数据量很大时(几十万，上千万)，这棵树的层次会很高，而每一次查找都会伴随着一次磁盘的IO

B树

特点

1. 每个根节点至少2个子女
2. 每个非根节点所包含关键字个数 j满足：
   ⌈m/2⌉−1<=j<=m−1⌈m/2⌉−1<=j<=m−1
3. 除根节点外的所有结点（不包括叶子结点）的度数正好是关键字总数加1，子树个数k：
   ⌈m/2⌉<=k<=m⌈m/2⌉<=k<=m
4. 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
5. 叶子结点位于同一层

平衡策略

存储索引示意图

B+树

特点

• 只有叶子节点才存储数据

• 每个叶子节点使用双向链表相连

存储索引示意图

索引结构

非聚簇(集)结构（Myisam）

Myisam主键索引：有2个文件，一个是索引文件，一个是数据文件。

通过索引查找数据的时候，是先通过索引找到存储数据的地址，再通过这个地址到数据文件取出数据。

Myisam的普通索引与主键索引在硬盘上面的存储方式是一样的

聚簇(集)结构（InnoDB）

InnoDB主键索引：索引和数据是在同一个文件中（即表索引与表数据在一起）

InnoDB聚集索引与普通索引的差异？

InnoDB聚集索引的叶子节点存储行记录，因此，InnoDB必须要有，且只有一个聚集索引：

1. 如果表定义了PK，则PK就是聚集索引；
2. 如果表没有定义PK，则第一个NOT NULL UNIQUE作为聚集索引；
3. 否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

InnoDB普通索引的叶子节点存储主键值

联合索引：由多个字段组成的索引

如果经常要用到多个字段的多条件查询，可以考虑建立联合索引，一般是除了第一个字段外的其他字段不经常用于条件筛选情况，比如两个字段，如果经常使用a条件或者a+b条件区查询，而很少单独使用b条件查询，那么可以建立a,b的联合索引；如果a和b都要分别经常单独的被用作查询条件，那还是建立多个单列索引。

最左匹配原则（叶子节点存储的数据是什么？？？）

假设我们按照a、b、c的顺序创建的联合索引，就像我们显示生活中的一座桥，从桥的这头到桥的那头，必须经过a、b、c者三个点，其中缺的字段可以想象成桥上有个缺口，不能通过，所以只能走桥的一部分。

什么是回表？

执行流程：

1. 先扫描name索引树，找到主键值id=1.
2. 再扫描主键索引，找到对应行。

如上图所示就是”回表查询“，先定位主键值，再通过主键值定位行记录，性能上较直接查询索引树定位行记录更慢。

覆盖索引

什么是索引覆盖？

1. 只需要在一颗索引树上就可以获取sql所需所有的列数据，不需要回表，相比回表速度要更快。
2. explain输出结果extra字段为Using index时，触发了索引覆盖。

如何实现索引覆盖？

实现方案：将被查询的字段建立到联合索引中

对于上面的例子，因为我们对name字段建立了普通索引，且基于name的索引叶子节点右主键id值，因此满足了在一颗索引树上获取sql所需的所有列数据这一条件，通过观察extra也可发现是Using Index无需回表。ps:本文实验基于8.0版本innodb

select id, name from user where name = 'Qingshan'

观察第二个例子，因为sex并没有被建立到联合索引中，且在name索引树上也无法直接获取，因此只能通过回表查询，两次扫描索引树，效果更低。

explain select id,name,sex from user where name='Qingshan'

争对第二个例子，我们将sex建立到联合索引中去。

ALTER TABLE `test`.`user` 
DROP INDEX `name`,
ADD INDEX `idx_name_sex`(`name`, `sex`);

再次执行查询，可以看到extra已经变为Using index了,命中了索引覆盖无需回表。

使用索引覆盖的场景

1. count查询优化

2. 列查询回表优化（上面第二个例子）

3. 分页查询：可以建立联合索引解决，针对下例可以建立（name,sex）覆盖索引

   select id,name,sex ... order by name limit 500,100;
   

创建索引的原则

• 较频繁作为查询条件的字段创建索引（例如：where、join、order by)
• 索引个数不要过多
• 散列度低的字段不要创建索引 （例如：性别） 散列度公式：count(distinct(column_name)):count(*)
• 随机无序或频繁更新的值，不适合创建索引
• 尽量扩展索引，不要新建索引。比如表中已有a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可
• 定义有外键的数据列一定要创建索引
• 对于定义为text、image和bit的数据类型的列不要建立索引

索引失效

• 索引列上使用函数、表达式、运算符
• 出现类型隐式转换
• like 条件字符前面带%（最左前缀）
• 负向查询 <> != NOT IN(不一定)