【Mysql】InnoDb行格式、数据页结构以及索引底层原理分析

我们用JDBC 等连接MySQL 其实就是，jdbc是客户端来连接MySQL服务器。

Mysql服务器中负责对表中数据的读取和写入工作的部分是存储引擎，支持的存储引擎就是倒数第二行的innobd、memory、myisam等。
存储引擎的上一层是 处理数据的， 下一层是真的文件系统。

InnoDB

innodb_space 是可以查看 innodb具体怎么存数据的工具
我们要讲的是innodb，它是将表中的数据存储到磁盘上的存储引擎。

InnoDB存储引擎不需要一条一条的把记录从磁盘上读出来，InnoDB采取的方式是：

• 将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，
• InnoDB中页的大小一般为 16 KB
  也就是说，当需要从磁盘中读数据时每一次最少将从磁盘中读取16KB的内容到内存中，每一次最少也会把内存中的16KB内容写到磁盘中

InnoDB数据页结构

每次InnoDB拿的页即数据页，为16kb，这16kb大小的存储空间可以被划分为多个部分，示意图如下：

• 名称	中文名	占用空间	简单描述
  File Header	文件头部	38字节	页的一些通用信息
  Page Header	页面头部	56字节	数据页专有的一些信息
  Infimum + Supremum	最小记录和最大记录	26字节	两个虚拟的行记录
  User Records	用户记录	不确定	实际存储的行记录内容
  Free Space	空闲空间	不确定	页中尚未使用的空间
  Page Directory	页面目录	不确定	页中的某些记录的相对位置
  File Trailer	文件尾部	8字节	校验页是否完整

InnoDB 行格式

我们平时是以记录为单位向表中插入数据的，这些记录在磁盘上的存放方式被称为行格式。

行格式分别是Compact、Redundant、Dynamic和Compressed行格式

Compact

• 记录的真实数据:
  记录的真实数据除了我们自己定义的列的数据外,还加了隐藏列:

  列名	是否必须	占用空间	描述
  row_id	否	6字节	行id,唯一标识一条记录
  transaction_id	是	6字节	事务id
  roll_pointer	是	7字节	回滚指针

  InnoDB对主键的生成策略
  优先使用用户自定义主键作为主键,如果没定义,则选取一个unique键作为主键,如果没有定义unique键,则会为表默认添加一个row_id 作为主键.
  行溢出
  一页只能存16kb, 而一个varchar的类型的列最多就可以存65533个字节,这样就会造成一页存不了一条记录.
  在Compact和Reduntant格式中, 记录真实数据数据处只会存储该列的一部分数据,把剩余的数据分散存储在其他几个页中,记录指向这些页的地址.

• 行格式除了记录真实数据外，还会记录额外信息：

  • 变长字段长度列表
    比如我们有varchar[varchar是可变长度的,所以占用空间不确定]　，这里就会记录这个varchar对应的真实的长度

  • NULL值列表
    把这行数据里是null的都记录下来. 具体就是将每个允许存储null的列对应一个二进制位,二进制位位1时,代表该列的值位null.

  • 记录头信息
    记录头信息用于描述记录,由固定的5个字节组成,即40个二进制位,不同的位代表不同的意思:
    

Dynamic和Compressed

Dynamic和Compressed类似于行格式, 只是处理行溢出不同,他们不会在记录的真实数据处存储一部分数据,而是把所有的数据都存储到其他页面,只有在记录的真实数据处存储其他页面的地址.

Compressed行格式会采用压缩算法对页面进行压缩.

索引

B+ 树

我们把数据按下面的格式存,每个页面都按照索引列的值建立了页目录.

其实就是B+树:

上面的是模拟,InnoDB的做法:
当第一页满了以后,会把第一页复制一份,把原来的第一页作为页目录.
这样可以保证入口永远不变.

叶子结点存了所有排好序的数据

聚集索引和辅助索引

• 聚集索引

  • 按照每个表的主键构造一颗B+树，同时页节点存放的是整张表的行记录数据，每一个叶子节点都被称为数据页。
  • 聚集索引是逻辑上连续不是物理连续
  • 聚集索引对于主键的排序查找和范围查找速度非常快

• 辅助索引

  • 叶子节点并不包含记录的全部数据，每个叶子节点中的索引行中存的不是这一行数据而是主键.
    通过这个主键去找对应的行数据

真正实现的时候 会把主键也加到辅助索引的节点中,因为 辅助索引的值有可能重复,我们把主键也加进去 就不会重复了

建立索引

建立索引 其实就是选择要排序的键, 按照我们选的键 进行排序 建立新的B+树

意思是用b c d 三个列 来建立索引, 就是b的值相同 就比较c 再比较d

create index idx_t1_bcd on t1(b,c,d)

考虑索引选择性

索引的选择性 是指不重复的索引值(基数) 与表记录数的壁纸

选择性 = 基数 / 记录数

选择性的取值范围为(0, 1]，选择性越高的索引价值越大。如果选择性等于1，就代表这个列的不重复值和表记录数是一样的，那么对这个列建立索引是非常合适的，如果选择性非常小，那么就代表这个列的重复值是很多的，不适合建立索引。

考虑前缀索引

比如varchar的长度很长,我们可以选取他的前几位建立索引
比如下面是算用first_name和last_name的前3位建立索引的选择性

    SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;

前缀索引的缺点: 不能用于orderby和group by 操作,也不能用于覆盖索引

索引的代价

空间上的代价

一个索引都为对应一棵B+树，树中每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，所以一个索引也是会占用磁盘空间的。

时间上的代价

每次对表中的数据进行增删改操作时,都要去修改各个B+树索引

不可以使用索引进行排序或分组的几种情况

1. asc 和desc 混用