InnoDb行格式、数据页结构、索引底层原理和如何建立索引

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: InnoDb行格式、数据页结构、索引底层原理和如何建立索引

局部性原理

在InnoDB中,数据会存储到磁盘上,在真正处理数据时需要先将数据加载到内存,表中读取某些记录时, InnoDB存储引擎不需要一条一条的把记录从磁盘上读出来,InnoDB采取的方式是:将数据划分为若干个页,以 页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小一般为 16 KB,也就是说,当需要从磁盘中读数据时 每一次最少将从磁盘中读取16KB的内容到内存中,每一次最少也会把内存中的16KB内容写到磁盘中。

InnoDB数据页结构

页是InnoDB管理存储空间的基本单位,一个页的大小默认是16KB。

SHOW GLOBAL STATUS like 'Innodb_page_size';

页结构:

image.png

名称

中文名

占用空间

简单描述

File Header

文件头部

38字节

页的一些通用信息

Page Header

页面头部

56字节

数据页专有的一些信息

Infimum + Supremum

最小记录和最大记录

26字节

两个虚拟的行记录

User Records

用户记录

不确定

实际存储的行记录内容

Free Space

空闲空间

不确定

页中尚未使用的空间

Page Directory

页面目录

不确定

页中的某些记录的相对位置

File Trailer

文件尾部

8字节

校验页是否完整

InnoDB行格式

一行记录可以以不同的格式存在InnoDB中,行格式分别是Compact、Redundant、Dynamic和Compressed行格 式。

我们可以在创建或修改表的语句中指定行格式:

CREATE TABLE 表明 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表明 ROW_FORMAT=行格式名称

COMPACT行格式

image.png

记录的额外信息

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息,这些额外信息分为3类,分别是:

  • 变长字段长度列表
  • NULL值列表
  • 记录头信息

变长字段长度列表

MySQL支持一些变长的数据类型,比如VARCHAR(M)、VARBINARY(M)、TEXT类型,BLOB类型,这些数据类型 修饰列称为变长字段,变长字段中存储多少字节的数据不是固定的,所以我们在存储真实数据的时候需要顺便把 这些数据占用的字节数也存起来。在Compact行格式中,把所有变长字段的真实数据占用的字节长度都存放在记 录的开头部位,从而形成一个变长字段长度列表。

CHAR是一种固定长度的类型,VARCHAR则是一种可变长度的类型。
VARCHAR(M),M代表最大能存多少个字符。( MySQL5.0.3以前是字节,以后就是字符)

NULL值列表

Compact行格式会把可以为NULL的列统一管理起来,存一个标记为在NULL值列表中,如果表中没有允许存储 NULL 的列,则 NULL值列表也不存在了。

  • 二进制位的值为1时,代表该列的值为NULL。
  • 二进制位的值为0时,代表该列的值不为NULL。

记录头信息

除了变长字段长度列表、NULL值列表之外,还有一个用于描述记录的记录头信息,它是由固定的5个字节组成。 5个字节也就是40个二进制位,不同的位代表不同的意思,如图:

名称

大小(单 位:bit)

描述

预留位1

1

没有使用

预留位2

1

没有使用

delete_mask

1

标记该记录是否被删除

min_rec_mask

1

B+树的每层非叶子节点中的最小记录都会添加该标记

n_owned

4

表示当前记录拥有的记录数

heap_no

13

表示当前记录在记录堆的位置信息

record_type

3

表示当前记录的类型,0表示普通记录,1表示B+树非叶子节点 记录,2表示最小记录,3表示最大记录

next_record

16

表示下一条记录的相对位置

记录的真实数据

记录的真实数据除了我们自己定义的列数据外,还会有三个隐藏列:

列名

是否必须

占用空间

描述

row_id

6字节

行ID,唯一标识一条记录

transaction_id

6字节

事务ID

roll_pointer

7字节

回滚指针

实际上这几个列的真正名称其实是:DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。

一个表没有手动定义主键,则会选取一个Unique键作为主键,如果连Unique键都没有定义的话,则会为表默 认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在 的。

行溢出数据

VARCHAR(M)类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量,如果我们使用 ascii字符集的话,一个字符就代表一个字节,我们看看VARCHAR(65535)是否可用:

mysql> CREATE TABLE varchar_size_demo(-> c VARCHAR(65535)-> ) CHARSET=ascii ROW_FORMAT=Compact;
ERROR 1118 (42000): Row size too large.
 The maximum row size for the used table type, 
not counting BLOBs, is 65535. This includes storage overhead,
 check the manual. You have to change some columns to TEXT or BLOBsmysql>

报错信息表达的意思是:MySQL对一条记录占用的最大存储空间是有限制的,除BLOB或者TEXT类型的列之外, 其他所有的列(不包括隐藏列和记录头信息)占用的字节长度加起来不能超过65535个字节。这个65535个字节 除了列本身的数据之外,还包括一些其他的数据,比如说我们为了存储一个VARCHAR(M)类型的列,其实需要占 用3部分存储空间:

  1. 真实数据
  2. 变长字段真实数据的长度
  3. NULL值标识

如果该VARCHAR类型的列没有NOT NULL属性,那最多只能存储65532个字节的数据,因为变长字段的长度占用 2个字节,NULL值标识需要占用1个字节。

mysql> CREATE TABLE varchar_size_demo(-> c VARCHAR(65532)-> ) CHARSET=ascii ROW_FORMAT=Compact;Query OK, 0 rows affected (0.02 sec)CREATE TABLE varchar_size_demo( c VARCHAR(65533) not null) CHARSET=ascii ROW_FORMAT=Compact; Query OK, 0 rows affected (0.02 sec)

记录中的数据太多产生的溢出

一个页的大小一般是16KB,也就是16384字节,而一个VARCHAR(M)类型的列就最多可以存储65533个字节,这 样就可能出现一个页存放不了一条记录。

在Compact和Reduntant行格式中,对于占用存储空间非常大的列,在记录的真实数据处只会存储该列的一部分 数据,把剩余的数据分散存储在几个其他的页中,然后记录的真实数据处用20个字节存储指向这些页的地址(当 然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数),从而可以找到剩余数据所在的页。

Dynamic 和 Compressed行格式

这两种行格式类似于COMPACT行格式,只不过在处理行溢出数据时有点儿分歧,它们不会在记录的真实数据处 存储一部分数据,而是把所有的数据都存储到其他页面中,只在记录的真实数据处存储其他页面的地址。另外, Compressed行格式会采用压缩算法对页面进行压缩。

索引的表现形式

image.png

聚簇索引

聚簇索引的特点:

1. 按主键值的大小进行记录和页的排序:

  • 数据页(叶子节点)里的记录是按照主键值从小到大排序的一个单向链表。
  • 数据页(叶子节点)之间也是是按照主键值从小到大排序的一个双向链表。
  • B+树中同一个层的页目录也是按照主键值从小到大排序的一个双向链表。

2. B+树的叶子节点存储的是完整的用户记录,就是指这个记录中存储了所有列的值(包括隐藏列)。

具有这两种特性的B+树称为聚簇索引,所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引 并不需要我们在MySQL语句中显式的使用INDEX语句去创建。InnoDB存储引擎会自动的为我们创建聚簇索引。 在InnoDB存储引擎中,聚簇索引就是数据的存储方式(所有的用户记录都存储在了叶子节点),也就是所谓的索 引即数据,数据即索引。

二级索引(复制索引)

聚簇索引只能在搜索条件是主键值时才能发挥作用,因为B+树中的数据都是按照主键进行排序的。当我们想以别 的列作为搜索条件时我们可以多建几棵B+树,不同的B+树中的数据采用不同的排序规则。

二级索引与聚簇索引有几处不同:

  1. 按指定的索引列的值来进行排序
  2. 叶子节点存储的不是完整的用户记录,而只是索引列+主键。
  3. 目录项记录中不是主键+页号,变成了索引列+页号。
  4. 在对二级索引进行查找数据时,需要根据主键值去聚簇索引中再查找一遍完整的用户记录,这个过程叫做
    回表

联合索引

以多个列的大小为排序规则建立的B+树称为联合索引,本质上也是一个二级索引。

目录项纪录的唯一性

我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的 目录项记录的内容实际上是由三个部分构成的:

  • 索引列的值
  • 主键值
  • 页号

B+树索引总结

  1. 每个索引都对应一棵B+树。用户记录都存储在B+树的叶子节点,所有目录记录都存储在非叶子节点。
  2. InnoDB存储引擎会自动为主键(如果没有它会自动帮我们添加)建立聚簇索引,聚簇索引的叶子节点包含完整的用户记录。
  3. 可以为指定的列建立二级索引,二级索引的叶子节点包含的用户记录由索引列 + 主键组成,所以如果想通过二级索引来查找完整的用户记录的话,需要通过回表操作,也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
  4. B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表,而且每个页内的记录(不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话,则页面和记录先按照联合索引前边的列排序,如果该列值相同,再按照联合索引后边的列排序。
  5. 通过索引查找记录是从B+树的根节点开始,一层一层向下搜索。由于每个页面都按照索引列的值建立了页目录,所以在这些页面中的查找非常快。

如何建立索引

考虑索引选择性

索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数的比值:

选择性 = 基数 / 记录数

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

考虑前缀索引

用列的前缀代替整个列作为索引key,当前缀长度合适时,可以做到既使得前缀索引的选择性接近全列索引,同 时因为索引key变短而减少了索引文件的大小和维护开销。

使用mysql官网提供的示例数据库:https://dev.mysql.com/doc/employee/en/employees-installation.html
github地址:https://github.com/datacharmer/test_db.git

employees表只有一个索引,那么如果我们想按名字搜索一个人,就只能全表扫描了:

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND
last_name='Anido';

那么可以建立索引,看下 两个索引的选择性:

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
 - - 0.0042SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees; 
-- 0.9313

显然选择性太低,选择性很好,但是first_name和last_name加起来长度为30,有没有兼顾长度和选择性的办法? 可以考虑用first_name和last_name的前几个字符建立索引,例如,看看其选择性:

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

选择性还不错,但离0.9313还是有点距离,那么把last_name前缀加到4:

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

这时选择性已经很理想了,而这个索引的长度只有18,比短了接近一半,建立前缀索引的方式为:

ALTER TABLE employees.employees ADD INDEX `first_name_last_name4` (first_name,
last_name(4));

前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于ORDER BY和GROUP BY操作,也不能用于覆盖索 引。

总结

  • 索引列的类型尽量小
  • 利用索引字符串值的前缀
  • 主键自增
  • 定位并删除表中的重复和冗余索引
  • 尽量使用覆盖索引进行查询,避免回表带来的性能损耗。

在mysql中,类型转换只能是字符串转成数字,如下:

  • 如果字母’a‘,字母全部直接转成0
  • 如果是数字’1‘,数字转成1

切记,不要让mysql进行强制类型转换

面试题:

  • 表数据结构本身就是按B+Tree组织的一个索引结构文件
  • 聚簇索引叶子节点包含了完整的数据记录
  • 为什么InnoDB表必须有索引,并且推荐整型的自增主键
  • 为什么非主键索引存储的是主键值(一致性和节省存储空间)
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
存储 关系型数据库 MySQL
索引大战:探秘InnoDB数据库中B树和Hash索引的优劣
索引大战:探秘InnoDB数据库中B树和Hash索引的优劣
23 0
|
26天前
|
存储 缓存 关系型数据库
✅InnoDB为什么使用B+树实现索引?
InnoDB使用B+树实现索引,因其具备平衡特性、所有数据存储在叶子节点利于范围查询和排序,且非叶子节点仅含索引关键字,能存储更多数据,减少IO操作并优化缓存利用率。B+树与哈希索引的区别在于,后者适用于等值查询但不支持范围查询和排序,且磁盘访问效率较低。相比于红黑树和B树,B+树更适合数据库索引需求。
|
3天前
|
存储 关系型数据库 MySQL
【MySQL技术内幕】5.1-InnoDB存储引擎索引概述
【MySQL技术内幕】5.1-InnoDB存储引擎索引概述
11 0
|
3天前
|
存储 算法 关系型数据库
【MySQL技术内幕】4.4-InnoDB数据页结构
【MySQL技术内幕】4.4-InnoDB数据页结构
11 1
|
3天前
|
存储 SQL 关系型数据库
【MySQL技术内幕】4.2-InnoDB行记录格式
【MySQL技术内幕】4.2-InnoDB行记录格式
10 0
|
3天前
|
存储 关系型数据库 MySQL
【MySQL技术内幕】4.2-InnoDB逻辑存储结构
【MySQL技术内幕】4.2-InnoDB逻辑存储结构
8 0
|
10天前
|
缓存 关系型数据库 MySQL
MySQL数据库——InnoDB引擎-架构-内存结构(Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer)
MySQL数据库——InnoDB引擎-架构-内存结构(Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer)
27 3
|
10天前
|
存储 关系型数据库 MySQL
MySQL数据库——InnoDB引擎-逻辑存储结构(表空间、段、区、页、行)
MySQL数据库——InnoDB引擎-逻辑存储结构(表空间、段、区、页、行)
27 7
|
11天前
|
存储 关系型数据库 MySQL
第七章InnoDB数据存储结构
第七章InnoDB数据存储结构
8 0
|
28天前
|
SQL 算法 关系型数据库
✅Innodb加索引,这个时候会锁表吗?
MySQL 5.6 引入 Online DDL 技术,允许在创建或删除 InnoDB 索引时不阻塞其他会话,减少了锁定和性能影响。不同 DDL 操作支持不同方式,如 COPY、INSTANT 和 INPLACE,具体见官方文档。虽然 Online DDL 可减少阻塞,但可能在索引构建期间仍有锁定,建议在非高峰时段执行并做好测试和规划。MySQL 5.6 之前的 DDL 操作会导致表锁定,而 Online DDL 旨在减少这种阻塞,但并非所有 DDL 语句都适用。了解各种算法如 COPY、INPLACE 和 INSTANT,以及它们的工作原理,有助于优化 DDL 操作。