InnoDB 是如何存储数据的

MySQL 支持多种存储引擎,并且可以以表为粒度设置存储引擎。因为支持
事务,我们最常使用的是 InnoDB

虽然数据保存在磁盘中,但其处理是在内存中进行的。为了减少磁盘随机读取次数,
InnoDB 采用页而不是行的粒度来保存数据,即数据被分成若干页,以页为单位保存在磁盘
中。InnoDB 的页大小,一般是 16KB。

各个数据页组成一个双向链表,每个数据页中的记录按照主键顺序组成单向链表;每一个数
据页中有一个页目录,方便按照主键查询记录

  • 定位PK 15 的记录

  • 二分查找中间位 (0+6)/2 =3,#3 指向的是PK12, 12<15 , 所以得出 数据需要从#3 后继续搜索

  • 再二分 #3 和#6 中间位 (3+6)/2 =4.2 ,取整 4 ,#4对应记录是16 ,16>15 ,

  • 再从#3 指向的12 记录向下搜索3次,定位15号记录,找到PK=15 的 数据

聚簇索引和二级索引

如果数据量非常多 上面方式降低时间复杂度非常有限,为了解决这个问题,InnoDB 引入了 B+ 树

B+ 树的特点包括:

  • 最底层的节点叫作叶子节点,用来存放数据

  • 其他上层节点叫作非叶子节点,仅用来存放目录项,作为索引

  • 非叶子节点分为不同层次,通过分层来降低每一层的搜索量

  • 所有节点按照索引键大小排序,构成一个双向链表,加速范围查找

InnoDB 使用 B+ 树,既可以保存实际数据,也可以加速数据搜索,这就是聚簇索引

InnoDB 会自动使用主键(唯一定义一条记录的单个或多个字段)作为聚簇索引的索引键
(如果没有主键,就选择第一个不包含 NULL 值的唯一列)。上图方框中的数字代表了索
引键的值,对聚簇索引而言一般就是主键。

B+ 树如何实现快速查找主键:

  • 索引PK=4的数据,根节点第一记录走#2号页 , 通过#2 又可以知道再#5号页,所以#5号页是实际数据页,再通过二分查找页目录定位记录的指针

为了实现非主键字段的快速搜索,就引出了二级索引,也叫作非聚簇索引、辅助索引。二级
索引,也是利用的 B+ 树的数据结构

这次二级索引的叶子节点保存的不是实际数据,而是主键,获得主键后再去聚簇索引中获得数据行,这个过程称作回表

例子:

  • 查找b ,经过两次定位得出在#5 数据页中,查出所有主键为7和6 ,再拿着这两个主键继续使用聚簇索引进行两次回表得到完整数据。

创建二级索引的代价

维护代价

创建 N 个二级索引,就需要再创建 N 棵 B+ 树,新增数据时不仅要修改
聚簇索引,还需要修改这 N 个二级索引。

新增记录需要往页中插入数据,现有的页满了就需要新创建一个页,把现有页的部分数据移过去,这就
是页分裂;如果删除了许多数据使得页比较空闲,还需要进行页合并。页分裂和合并,都会
有 IO 代价,并且可能在操作过程中产生死锁。

空间代价

虽然二级索引不保存原始数据,但要保存索引列的数据,所以会占用更多的空间。

回表的代价

二级索引不保存原始数据,通过索引找到主键后需要再查询聚簇索引,才能得到我们要的数据

使用 SELECT * 按照 name 字段查询用户,使用 EXPLAIN查看执行计划

EXPLAIN SELECT * FROM person WHERE NAME='name1'

执行计划如下:

  • key 字段代表实际走的是哪个索引,其值是 name_score,说明走的是 name_score 这
    个索引。

  • type 字段代表了访问表的方式,其值 ref 说明是二级索引等值匹配,符合我们的查询。

把 SQL 中的 * 修改为 NAME 和 SCORE,也就是 SELECT name_score 联合索引包含的两
列:

EXPLAIN SELECT NAME,SCORE FROM person WHERE NAME='name1'

执行计划如下:

Extra 列多了一行 Using index 的提示,证明这次查询直接查的是二级索引,免去了回表。

不是所有针对索引列的查询都能用上索引

索引只能匹配列前缀
EXPLAIN SELECT * FROM person WHERE NAME LIKE '%name123' LIMIT 100

搜索 name 后缀为 name123 的用户 无法走索引,执行计划的 type=ALL 代表了全表扫描

EXPLAIN SELECT * FROM person WHERE NAME LIKE 'name123%' LIMIT 100

把百分号放到后面走前缀匹配,type=range 表示走索引扫描,key=name_score 看到实际走了 name_score 索引:

条件涉及函数操作无法走索引
EXPLAIN SELECT * FROM person WHERE LENGTH(NAME)=7

联合索引只能匹配左边的列
EXPLAIN SELECT * FROM person WHERE SCORE>45678

虽然对 name 和 score 建了联合索引,但是仅按照 score 列搜索无法走索引

尝试把搜索条件加入 name 列

EXPLAIN SELECT * FROM person WHERE SCORE>45678 AND NAME LIKE 'NAME45%'

数据库基于成本决定是否走索引
  • MySQL 选择索引,并不是按照 WHERE 条件中列的顺序进行的
  • 即便列有索引,甚至有多个可能的索引方案,MySQL 也可能不走索引。

如果需要强制走索引

EXPLAIN SELECT * FROM person FORCE INDEX(name_score) WHERE NAME >'name84059'
optimizer_trace 查看优化器生成计划的过程 选择成本
SET optimizer_trace="enabled=on";
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

最左侧列匹配

假设联合索引 KEY(class_name, student_name,subject_name)

​ 1 2 3

顺序不一致没有关系,mysql会自动优化

select * from student _score where class_name =' ' and student_name='' and subjec_name=‘’;

​ 1 2 3

select * from student _score where class_name ='' and student_name='';

​ 1 2

select * from student_score where subject_name='';

​ 3

select * from student _score where class_name ='' and subjec_name=''

​ 1 3

最左前缀匹配原则

select * from  student _score where class_name like '1%';
select * from  student _score where class_name like '%班';

范围查找原则

select  * from  student _score where class_name>'1班' and class_name<'5班';
select  * from  student _score where class_name>'1班' and class_name<'5班' and student_name>'';

等值匹配+范围匹配

select  * from  student _score where class_name ='1班' and student_name>'' and subject_name<'';

如何设计索引

先别急着设计索引 业务编码先行

where 条件, order by 条件 group by 条件 最左侧

index(a,b,c)

where a= ? and b =? ,order by a,b ,group by a

基数太小字段的没有设置索引的意义 (1,0)

尽量选择字段类型比较小的列来设计索引 减少磁盘占用

如果真的有字段很大 比如varchar(255)可以针对这个字段的前20 个字符建立索引 前缀索引

KEY my_index(name(20),age,course),注意 order by 和group by 会失效

索引套函数失效

插入和删除 页分裂 会影响性能 ,所以设计索引别太多,建议两三个联合索引就应该覆盖整张表的查询

主键尽量自增 ,少用uuid