MySQL学习

发表于 分类于 评论数： 阅读次数： 本文字数： 8.1k 阅读时长 ≈ 29 分钟

——摘录自《MySQL是怎样运行的》

1、字符集和比较规则

1.1、字符集简介

字符集是人们抽象出来一个字符集的概念来描述某个字符范围的编码规则，简单的来说就是在某个范围的字符建立与二进制数据的映射关系（编码规则）。

1.2、比较规则简介

对某个字符集中比较大小的规则。比如字符’a‘的编码为0x01,字符’b‘的编码为0x02，所以’a’小于’b’。这种简单的比较规则也可以称为二进制比较规则。但在很多场合下，英文字符都是不区分大小写额，也就是’a’和‘A’是相等的。此时就不可以简单粗暴地使用二进制比较规则，此时可以这样指定比较规则：

  • 将两个大小写不同的字符都转为大写或者小写。
  • 再比较这两个字符对应的二进制数据。

1.3、常见的字符集

  • ASCII字符集（1字节）：共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于该字符集总共才128个字符，所以可以使用一个字节来进行编码。
    • ‘L’ ->01001100(十六进制0x4C，十进制76)
    • ‘M’ ->01001101(十六进制0x4D，十进制77)
  • ISO 8859-1字符集：共收录256个字符，它在ASCII字符集的基础上又扩充了128个西欧常用字符（包括德法两国的字母）。ISO 8859-1字符集也可以使用一个字节来进行编码。
  • GB2312字符集（1-2字节）：共收录6763个汉字，其中一级汉字3755个，二级汉字3008个；同时，GB 2312收录了包括拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母在内的682个全角字符。整个字符集分成94个区，每区有94个位。这种字符集兼容ASCII字符集，在编码方式有些特别之处：如果字符在ASCII字符集中，则采用一字节编码；否则采用两字节编码
    • GB2312，又称为GB0，由中国国家标准总局发布，1981年5月1日实施
    • GB2312标准共收录6763个汉字，其中一级汉字3755个，二级汉字3008个
    • GB2312是一种区位码。分为94个区(01-94)，每区94个字符(01-94)
    • 01-09区为特殊符号
    • 10-15区没有编码
    • 16-55区为一级汉字，按拼音排序，共3755个
    • 56-87区为二级汉字，按部首／笔画排序，共3008个
    • 88-94区没有编码
    • GB2312只是编码表，在计算机中通常都是用"EUC-CN"表示法，即在每个区位加上0xA0来表示。区和位分别占用一个字节。
  • GBK字符集：在收录的字符范围上对GB2312字符集进行了扩充，编码方式兼容GB2312字符集。
  • UTF-8字符集：编码规则：
    • 一个US-ASCIl字符只需1字节编码（Unicode范围由U+0000~U+007F）。
    • 带有变音符号的拉丁文、希腊文、西里尔字母、亚美尼亚语、希伯来文、阿拉伯文、叙利亚文等字母则需要2字节编码（Unicode范围由U+0080~U+07FF）。
    • 其他语言的字符（包括中日韩文字、东南亚文字、中东文字等）包含了大部分常用字，使用3字节编码。
    • 其他极少使用的语言字符使用4字节编码。

1.4、MySQL中的utf8和utf8mb4

  • utf8mb3：“阉割”过的utf-8字符集，使用1~3字节表示字符
  • utf8mb4：正宗的utf-8字符集，使用1~4字节表示字符

在mysql中，utf8是utf8mb3的别名，如果需要存储特殊的字符，可以使用4字符编码的utf8mb4

1.5、MySQL中的字符集和比较规则

查看当前mysql支持的字符集
1
show charset like 'utf8%';

image-20220511172235587

Charset:字符集名称
Default_collation:默认的比较规则
Maxlen:最大长度,utf8字符长度为1-3所以MaxLen为3 utf8mb4字符长度1-4所以Maxlen为4

查看当前mysql中支持的比较规则
1
show collation like 'utf8%';

image-20220511172428267

Default:是否是当前字符集默认的比较规则
Complied:是否将字符集编译到了server中
Sortlen:与比较字符串在内存中需要的内存量有关

utf8_str1_str2
str1代表着比较规则所使用的的语言,比如 utf8_polish_xx表示波兰语的比较规则
str2代表着如何比较.
通常情况下我们会使用的是utf8_general_ci与utf8_bin,前者代表着不区分大小写,后者代表着区分大小写

字符集与比较规则的分类

按照应用级别可以分为服务器级别 数据库级别 表级别 列级别

服务器级别

字符集

1
2
show variables like 'character_set_server';
默认: utf8

比较规则

1
2
show variables like 'collation_server';
默认: utf8_general_ci

数据库级别

1
2
3
4
show variables like 'character_set_database';
默认: utf8
show variables like 'collation_database';
默认:utf8_general_ci

创建数据库时指定:

1
create database xxx [character set  '字符集名' collate '比较规则名称']

表级别

表级别没有专门变量表示
创建数据表时指定:

1
create table xxx (xxx) [character set 字符集名 collate 比较规则名称]

列级别

1
2
3
create table xxx (
id int [character set 字符集名称] [collate 比较规则名称]
) [character set  字符集名 collate 比较规则名称]

如果列级别没有设置字符集与比较规则,则使用表的,如果表没有设置,则使用数据库的,如果数据库没有设置则使用服务器的.

mysql通信中的字符集与比较规则

image-20220511173659500
image-20220511173739019

character_set_client(服务端收到请求)

此变量session级
当我们用客户端与服务端通信时,character_set_client指的是,服务端以什么字符集去理解客户端发送过来的请求,默认是当前操作系统字符集.在当前seesion中,此变量为utf8,如果设置成gbk,会导致select '我'会乱码,因为服务端会把从服务端收到的以utf8编码的’我’使用gbk解码,所以会乱码,.

character_set_connection(服务端处理请求)

此变量session级
当服务器将’我’通过character_set_client正确解码后,会按照character_set_connection进行编码,然后按照collation_connection比较规则进行比较,但是如果查询中有列比如select '我'=name from aaa此时会优先采用name列的字符集与比较规则进行判断

character_set_results(服务端响应请求)

此变量session级
服务器将要响应的数据以什么格式进行编码,比如select name from aaa name是以utf8编码的,character_set_results是gbk编码,那么会把查询的结果以gbk编码对客户端响应

客户端接收请求

客户端接收响应的请求后,会以什么编码的方式显示出来,默认就是当前系统的字符集

2、InnoDB记录存储结构

2.1、InnoDB页简介

页是Innodb中磁盘和内存交互的基本单位，也是Innodb管理存储空间的基本单位，默认大小为16KB。

2.2、InnoDB行格式

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。InnoDB存储引擎设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式。

2.3、行记录格式的分类和介绍

在早期的InnoDB版本中，由于文件格式只有一种，因此不需要为此文件格式命名。随着InnoDB引擎的发展，开发出了不兼容早期版本的新文件格式，用于支持新的功能。为了在升级和降级情况下帮助管理系统的兼容性，以及运行不同的MySQL版本，InnoDB开始使用命名的文件格式。

MySQL怎么从二进制内容看InnoDB行格式

在msyql 5.7.9及以后版本，默认行格式由innodb_default_row_format变量决定，它的默认值是dynamic

2.4、行格式

COMPACT

MySQL怎么从二进制内容看InnoDB行格式

变长字段列表

MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型，我们也可以把拥有这些数据类型的列称为变长字段，变长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。如果该可变字段允许存储的最大字节数（M×W）超过255字节并且真实存储的字节数（L）超过127字节，则使用2个字节记录，否则使用1个字节记录。

问题一：那么为什么用128作为分界线呢？ 一个字节可以最多表示255，但是MySQL设计长度表示时，为了区分是否是一个字节表示长度，规定，如果最高位为1，那么就是两个字节表示长度，否则就是一个字节。例如，01111111，这个就代表长度为127，而如果长度是128，就需要两个字节，就是10000000 10000000，首个字节的最高位为1，那么这就是两个字节表示长度的开头，第二个字节可以用所有位表示长度，并且需要注意的是，MySQL采取Little Endian的计数方式，低位在前，高位在后，所以129就是10000001 10000000。同时，这种标识方式，最大长度就是 2^15-1=32767，也就是32KB。

问题二：如果两个字节也不够表示的长度，该怎么办？ innoDB页大小默认为16KB，对于一些占用字节数非常多的字段，比方说某个字段长度大于了16KB，那么如果该记录在单个页面中无法存储时，InnoDB会把一部分数据存放到所谓的溢出页中，在变长字段长度列表处只存储留在本页面中的长度，所以使用两个字节也可以存放下来。这个溢出页机制参考后面的数据溢出。

NULL值列表

表中的某些列可能存储NULL值，如果把这些NULL值都放到记录的真实数据中存储会很占地方，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表。每个允许存储NULL的列对应一个二进制位，二进制位的值为1时，代表该列的值为NULL。二进制位的值为0时，代表该列的值不为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 表示下一条记录的相对位置

隐藏列

记录的真实数据除了我们自己定义的列的数据以外，MySQL会为每个记录默认的添加一些列（也称为隐藏列），包括：

  • DB_ROW_ID(row_id)：非必须，6字节，表示行ID，唯一标识一条记录
  • DB_TRX_ID：必须，6字节，表示事务ID
  • DB_ROLL_PTR：必须，7字节，表示回滚指针

InnoDB表对主键的生成策略是：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique 键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。

DB_TRX_ID（也可以称为trx_id） 和DB_ROLL_PTR（也可以称为roll_ptr） 这两个列是必有的，但是row_id是可选的（在没有自定义主键以及Unique 键的情况下才会添加该列）。

其他的行格式和Compact行格式差别不大。

Redundant行格式

Redundant行格式是MySQL5.0之前用的一种行格式，不予深究。

Dynamic行格式

MySQL5.7的默认行格式就是Dynamic，Dynamic行格式和Compact行格式挺像，只不过在处理行溢出数据时有所不同。

Compressed行格式

Compressed行格式在Dynamic行格式的基础上会采用压缩算法对页面进行压缩，以节省空间。以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度数据能够进行有效的存储（减少40%，但对CPU要求更高）。

数据溢出

如果我们定义一个表，表中只有一个VARCHAR字段，如下：

1
CREATE TABLE test_varchar( c VARCHAR(60000))

然后往这个字段插入60000个字符，会发生什么？前边说过，MySQL中磁盘和内存交互的基本单位是页，也就是说MySQL是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。而一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节，这样就可能造成一个页存放不了一条记录的情况。

在Compact和Redundant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的该列的前768个字节的数据，然后把剩余的数据分散存储在几个其他的页中，记录的真实数据处用20个字节（768字节后20个字节）存储指向这些页的地址。这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。

Dynamic和Compressed行格式，不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

3、InnoDB的数据页

3.1 概念&分类

概念

InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB，查看命令如下（单位是字节）：

1
2
3
4
5
6
7
mysql> show status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

可以把「页」理解为一个容器，这个容器是用来存储「记录」的。

分类

值得注意的是，InnoDB 中的「页」并非只有一种，比如有存放 Insert Buffer 的页、存放 undo log 的页、存放数据的页等等。其中我们最关注的还是存放我们表数据的页，又称「索引页」，或者数据页。

下面分析和介绍数据页。

3.2 数据页结构

概述

InnoDB 数据页的结构如图所示：

img

它由七部分构成，简介如下：

img

记录插入过程

在数据页中，当记录为空时，User Records 是不存在的。随着记录的一条条插入，会不断从 Free Space 开辟空间分配给记录，如图所示：

img

页结构分析

记录头信息

为便于描述记录是如何在页中存储的，这里先贴一下前文提到的记录头信息：

img

它们的各个部分说明如下：

  • 前两个预留位：暂无用处（各占 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 位，保存下一条记录的相对位置

  • Infimum + Supremum

这部分存储的是固定的两条记录，分别为数据页中的「最小记录」和「最大记录」，如图所示：

img

PS: 由于这两条记录不是我们自定义的，因此不存储在 User Records 空间。

Free Space

空闲空间，数据页中尚未使用的部分。

User Records

用户记录，这里就是存储我们记录的地方。

上面的「最小记录」和「最大记录」是如何跟我们的记录关联起来的呢？

这时候 next_record 的作用就体现出来了。这两条记录和我们自定义的记录之间是通过 next_record 关联起来的，自定义的记录之间也是通过 next_record 关联起来的，如图所示：

img

即，「最小记录」的 next_record 指向第一条数据记录，最后一条数据记录的 next_record 指向「最大记录」。数据记录之间是按照主键的顺序从小到大排序的。

PS: 此处的 (1,100,‘aaaa’), (2,200,‘bbbb’) 等几条内容是自定义的数据。
记录中的蓝色部分为额外信息，橙色部分为记录数据。

Page Directory

上面是以 4 条记录举例的，如果要查找其中一条，即使遍历也不会很耗时。但是，如果记录数量一直增加，比如到一千条、一万条甚至更多（假设一个数据页能存的下），我们如何去查找其中的一条记录呢？这样再去遍历岂不是很笨？

就像我们从一本书里找某些内容，当只有几页的时候很容易就能找到；但如果有好几百页，总不能一页一页去翻吧？

我们通常是先查找目录，找到在哪一页，然后直接翻到那一页。页目录的作用跟书的目录差不多。

由于记录之间是按主键排序的，可以把它们从小到大分成一个个的「组」，每组包含很少的几条记录，如图所示：

img

分组规则大致如下：

  • 「最小记录」自成一组
  • 包含「最大记录」的组一般为 1~8 条记录
  • 其它记录一般是 4~8 条分为一组

分组之后，把每组中最大的那条记录的地址偏移量提出来，按顺序存储起来，这些地址偏移量称为槽（Slot），而这些槽就组成了页目录（Page Directory）。就像是给一本书做了目录。

目录有了，怎么使用呢？

此时如果要查找一条记录，步骤大致如下：

1、由于页目录中的槽是有序的，因此可以用「二分法」快速定位到一个槽；

2、找到该槽所在分组中主键值最小的记录；

3、通过 next_record 遍历组中的记录。

这样就比遍历所有的记录快得多了吧！

Page Header

页头部，主要记录数据页的一些信息，比如本页存了多少条记录、页目录中有多少个槽等等。

File Header

页头部存的是一个数据页的概要信息，是一个页专有的，而文件头存的是各种页通用的信息，比如页的类型是什么、页的编号是多少、上一页的页号是多少、下一页的页号是多少等等。

既然有上一页、下一页的定义，说明页与页之间其实是互相连接的，它们之间就像一个双向链表（比如 Java 的 LinkedList）那样，如图所示：

img

File Trailer

文件尾，主要用于校验一个页是不是完整的。

4、B+树索引

InnoDB存储引擎的是一颗B+树，完整的用户记录都存储在B+树第0层的叶子节点；其他层次的 节点都属于内节点，内节点中存储的是目录项记录。

InnoDB的索引分为两种。

  • 聚簇索引：以主键值的大小作为页和记录的排序规则，在叶子节点处存储的记录包含了表中所有的列。
  • 二级索引：以索引列的大小作为页和记录的排序规则，在叶子节点处存储的记录内容是索引列+主键。

InnoDB存储引擎的B+树根节点自创建之日就不再移动。

在二级索引的B+树内节点中，目录项记录由索引列的值，主键值和页号组成。

一个数据页至少可以容纳2条记录。

MyISAM存储引擎的数据和索引分开存储，这种存储引擎的索引全部都是二级索引，在叶子节点处存储的是列+行号。

5、B+树索引的使用

MySQL中B+树索引的应用场景大全