1. 为什么使用索引
2. 索引及其优缺点
2.1 索引概述
2.2 优点
2.3 缺点
3. InnoDB中索引的推演
3.1 索引之前的查找
3.2 设计索引
1. 一个简单的索引设计方案
2. InnoDB中的索引方案
1 迭代1次:目录项纪录的页
2 迭代2次:多个目录项纪录的页
从图中可以看出,我们插入了一条主键值为320的用户记录之后需要两个新的数据页:
- 为存储该用户记录而新生成了 页31 。
- 因为原先存储目录项记录的 页30的容量已满 (我们前边假设只能存储4条目录项记录),所以不得不需要一个新的 页32 来存放 页31 对应的目录项。
现在因为存储目录项记录的页不止一个,所以如果我们想根据主键值查找一条用户记录大致需要3个步骤,以查找主键值为 20 的记录为例:
- 确定 目录项记录页我们现在的存储目录项记录的页有两个,即 页30 和 页32 ,又因为页30表示的目录项的主键值的范围是 [1, 320) ,页32表示的目录项的主键值不小于 320 ,所以主键值为 20 的记录对应的目录项记录在 页30 中。
- 通过目录项记录页 确定用户记录真实所在的页 。
在一个存储 目录项记录 的页中通过主键值定位一条目录项记录的方式说过了。 - 在真实存储用户记录的页中定位到具体的记录。
3 迭代3次:目录项记录页的目录页
④ B+Tree
一个B+树的节点其实可以分成好多层,规定最下边的那层,也就是存放我们用户记录的那层为第 0 层,之后依次往上加。之前我们做了一个非常极端的假设:存放用户记录的页 最多存放3条记录 ,存放目录项记录的页 最多存放4条记录 。其实真实环境中一个页存放的记录数量是非常大的,假设所有存放用户记录的叶子节点代表的数据页可以存放 100条用户记录 ,所有存放目录项记录的内节点代表的数据页可以存放 1000条目录项记录 ,那么:
- 如果B+树只有1层,也就是只有1个用于存放用户记录的节点,最多能存放 100 条记录。
- 如果B+树有2层,最多能存放 1000×100=10,0000 条记录。
- 如果B+树有3层,最多能存放 1000×1000×100=1,0000,0000 条记录。
- 如果B+树有4层,最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。相当多的记录!!!
你的表里能存放 100000000000 条记录吗?所以一般情况下,我们 用到的B+树都不会超过4层 ,那我们通过主键值去查找某条记录最多只需要做4个页面内的查找(查找3个目录项页和一个用户记录页),又因为在每个页面内有所谓的 Page Directory (页目录),所以在页面内也可以通过 二分法 实现快速定位记录。
3.3 常见索引概念
索引按照物理实现方式,索引可以分为 2 种:聚簇(聚集)和非聚簇(非聚集)索引。我们也把非聚集索引称为二级索引或者辅助索引。
1. 聚簇索引
特点:
- 使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:
- 页内 的记录是按照主键的大小顺序排成一个 单向链表 。
- 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
- 存放 目录项记录的页 分为不同的层次,在同一层次中的页也是根据页中目录项记录的主键
大小顺序排成一个 双向链表 。
- B+树的 叶子节点 存储的是完整的用户记录。
- 所谓完整的用户记录,就是指这个记录中存储了所有列的值(包括隐藏列)。
优点:
- 数据访问更快 ,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快
- 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
- 按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以 节省了大量的io操作 。
缺点:
- 插入速度严重依赖于插入顺序 ,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
- 更新主键的代价很高 ,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新二级索引访问需要两次索引查找 ,第一次找到主键值,第二次根据主键值找到行数据
2. 二级索引(辅助索引、非聚簇索引)
概念:回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值,所以如果我们想根据c2列的值查找到完整的用户记录的话,仍然需要到 聚簇索引 中再查一遍,这个过程称为 回表 。也就是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树!
问题:为什么我们还需要一次 回表 操作呢?直接把完整的用户记录放到叶子节点不OK吗?
3. 联合索引
我们也可以同时以多个列的大小作为排序规则,也就是同时为多个列建立索引,比方说我们想让B+树按照 c2和c3列 的大小进行排序,这个包含两层含义:
- 先把各个记录和页按照c2列进行排序。
- 在记录的c2列相同的情况下,采用c3列进行排序
注意一点,以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ,本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的,不同点如下:
建立 联合索引 只会建立如上图一样的1棵B+树。
为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。
3.4 InnoDB的B+树索引的注意事项
####1. 根页面位置万年不动
2. 内节点中目录项记录的唯一性
3. 一个页面最少存储2条记录
4. MyISAM中的索引方案
4.2 MyISAM索引的原理
4.3 MyISAM 与 InnoDB对比
MyISAM的索引方式都是“非聚簇”的,与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区
别:
① 在InnoDB存储引擎中,我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录,而在MyISAM 中却需要进行一次 回表 操作,意味着MyISAM中建立的索引相当于全部都是 二级索引 。
② InnoDB的数据文件本身就是索引文件,而MyISAM索引文件和数据文件是 分离的 ,索引文件仅保存数据记录的地址。
③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ,而MyISAM索引记录的是 地址 。换句话说,
InnoDB的所有非聚簇索引都引用主键作为data域。
④ MyISAM的回表操作是十分 快速 的,因为是拿着地址偏移量直接到文件中取数据的,反观InnoDB是通过获取主键之后再去聚簇索引里找记录,虽然说也不慢,但还是比不上直接用地址去访问。
⑤ InnoDB要求表 必须有主键 ( MyISAM可以没有 )。如果没有显式指定,则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整型。
5. 索引的代价
6. MySQL数据结构选择的合理性
6.1 全表遍历
这里都懒得说了。
6.2 Hash结构
6.3 二叉搜索树
6.4 AVL树
6.5 B-Tree
6.6 B+Tree
6.7 R树