这是我参与更文挑战的第1天,活动详情查看:更文挑战
一、前言
数据页与数据页之间通过双向链表进行关联,数据行之前是单向链表,大致如图:
数据行之间的关联,如图:
每个数据行的头部有个数据类型,来表明当前行:
- 2 :表示最小行
- 0 :表示普通数据行
- 3 : 表示最大行
页分裂 核心目标:保证下一个数据页里的主键值都比上一个数据页里的主键值要大。
在一个表里不停的插入数据,会涉及到 页分裂 的过程:
在增加一个新的数据页的时候,实际上会把前一个数据页里主键值较大的挪动到新的数据页里来,然后把新插入的主键值较小的数据挪到上一个数据页里去,保证新数据页里的主键值一定比上一个数据页里的主键值大。
二、索引
问题1:主键的索引是如何设计的,以及如何根据主键索引查询?
假设要搜 id = 5
的数据,那如何知道这数据在哪个数据页里?
在没有索引的情况下,其实在物理层面都是全表扫描,依次扫描每个数据页内部的每个数据行。
针对主键设计一个索引,即生成一个 主键目录:把每个数据页的页号,还有数据页里最小的主键值放在一起,组成一个索引的目录。如图:
那么查询的时候,先到主键目录找到对应的数据页,再在数据页里找到对应的数据行。
问题2:索引如何用 B+
树来实现的?
在上一问的情况下,数据逐渐增多,索引也逐渐增多,从而形成索引页,如图:
Tips
:子索引页中包含对应的数据页,可以指向对应的数据页。
为了加快搜索效率,从而引入 B+
树,如图:
一颗大的 B+
树 索引数据结构里,叶子节点就是数据页本身,那么就可以称这颗 B+
树索引为聚簇索引。
在 InnoDb
存储引擎里,对数据增删改查直接对聚簇索引里的数据页进行操作。
问题3:如何对主键外的其他字段建立索引?
针对其他字段建立索引,例如
name
字段。
当插入数据的时候:
- 把完整数据插入到聚簇索引的叶子节点的数据页里去,并维护好聚簇索引
- 其他字段建立的索引为单独的
B+
树,并维护
比如给 name
字段建立一个索引,这会生成一颗 B+
树,如图:
B+
树的叶子节点是数据页,这个数据页里的数据行仅仅存放主键字段和name
字段。
当根据 name
字段搜索的时候:
SELECT * FROM table WHERE name = 'yy';
-
从
name
字段的索引B+
树里的根节点开始一层层查找,一直到叶子节点的数据页里,找到对应name
的主键值Tips
: 这找到主键值 -
那得到这行数的其他的值,那么就需要 回表。
根据上步拿到的主键值,再到聚簇索引里去查找
同样联合索引也一样,例如 name
+ age
。
问题4:插入数据如何维护不同索引的 B+
树?
如果有多个索引,那么这些索引树都需要被维护才行。
即一边维护聚簇索引,一边维护其他索引树。
问题5:联合索引查询原理 和 全值匹配规则
设计系统,一般都是设计联合索引:为了尽可能让索引数量少一些,避免磁盘占用过多,增删改性能太差。
有一张学生分数表:
基本属性有:学生、科目、分数 有主键
id
,联合索引:stu
、subject
、score
CREATE TABLE IF NOT EXISTS `stu_score` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`stu` VARCHAR(32) NOT NULL COMMENT '学生',
`subject` VARCHAR(32) NOT NULL COMMENT '科目',
`score` INT UNSIGNED NOT NULL COMMENT '分数',
`create_time` DATETIME NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
`modify_time` DATETIME NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT '更新时间',
PRIMARY KEY (`id`),
INDEX `idx_stu_subject_score` (`stu`, `subject`, `score`)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '学生分数表';
数据有:
主键 | 学生 | 科目 | 分数 |
---|---|---|---|
13 | 拉拉 | 英语 | 89 |
43 | 西西 | 英语 | 99 |
22 | 咚咚 | 英语 | 79 |
111 | 哈哈 | 英语 | 100 |
联合索引的索引树,如图:
全等值查询,就可以用上这个联合索引:
SELECT id FROM stu_score WHERE stu = '拉拉' AND subject = '英语' AND score = 100;
常见索引使用规则:
- 等值匹配:
WHERE
里的字段和字段顺序 跟 联合索引的一模一样 - 最左侧列匹配
- 最左前缀匹配原则
- 范围查找规则
- 等值匹配 + 范围匹配规则
1. 最左侧列匹配
联合索引是:KEY(stu, subject, score)
不一定必须要在 WHERE
语句里根据三个字段来查,只要根据最左侧的部分字段来查。
例如,均可:
SELECT * FROM stu_score WHERE stu;
SELECT * FROM stu_score WHERE stu AND subject;
SELECT * FROM stu_score WHERE stu AND subject AND score;
2. 最左前缀匹配原则
例如使用 like
语法来查。
这可基于索引来查找:
SELECT * FROM stu_score WHERE stu LIKE '拉%'
;
这不能基于索引来查找:
SELECT * FROM stu_score WHERE stu LIKE '%拉'
;
3. 范围查找规则
如果有范围查询,只有对联合索引里最左侧的列进行范围查询才能用到索引。
例如:
SELECT * FROM stu_score WHERE stu < '拉拉' AND stu > '西西';
;
这样就不能:
SELECT * FROM stu_score WHERE stu < '拉拉' AND stu > '西西' AND subject < '英语';
4. 等值匹配 + 范围匹配规则
例如:
SELECT * FROM stu_score WHERE stu = '拉拉' AND subject < '英语' AND score < 100 AND score > 60;
可以用 stu
在索引里准确定位到一批数据,这些数据按照 subject
顺序排列,所以 subject < '英语'
也会基于索引来查找。
但 score < 100
不能用索引。
问题6:排序如何才能用到索引
会遇到 filesort
,这是基于磁盘文件排序。
建立索引 INDEX(xx1, xx2, xx3)
,ORDER BY xx1, xx2, xx3;
, 要么都加 DESC
降序排列:ORDER BY xx1 DESC, xx2 DESC, xx3 DESC;
问题7:分组如何才能用到索引
group by
和 order by
用上的索引的原理和条件都是差不多,本质都是在 group by
和 order by
之后的字段顺序和联合索引中的从最左侧开始和字段顺序一致。
通常而言,对于 group by
后的字段,最好也按照联合索引里的最左侧的字段开始,按顺序排列开。