通信方式

mysql服务器和客户端的通信方式有以下几种:

  1. tcp/ip
  2. 共享内存(windows下同一台机器) 服务端启动: –shared-memory 客户端启动:–protocol=memory
  3. 命名管道 服务器启动–enable-named-pipe 客户端启动 –protocol=pipe
  4. unix域套接字文件 –protocol=socket (同一台unix机器)

连接

  1. 客户端连接服务器的时候,服务器会给每个进来的客户端创建一个线程,连接断开之后服务器不会立即销毁这个线程,而是将这个线程缓存起来
  2. 但是为每个客户端连接维护一个线程,但是维护太多线程会影响服务器的性能;
  3. 客户端和服务端建立连接之后,服务器会等待客户端发送sql请求过来,即sql脚本
  4. 服务器默认连接客户端的数量是151
  5. 通过show processlist命令查看command为sleep的这行数据可以查询空闲连接,客户端如果太长时间没动静,连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的,默认值是 8 小时。

解析优化

当服务器收到客户端传过来的文本请求之后,就会进行一系列的解析和优化
查询缓存:
  客户端发送select请求的时候会缓存,等下个客户端发送相同查询请求的时候优先使用缓存同时会监控表中的字段,当缓存对应的表中的字段发生更新操作时会及时清除缓存,mysql8中已经废弃掉了,因为要维护一定的缓存空间
语法解析:
查询优化:
执行sql:

储存引擎(表级)

区别/引擎 MYISAM INNODB
数据缓存 n y
外键支持 n y
锁粒度 表级 行级
MVCC n y
储存限制 256TB 64TB
事务 n y

字符集

字符集可供修改级别

  1. 服务器级别 charset_utf8_server
  2. 数据库级别 charset_utf8_databases
  3. 表级别 创建/修改表的时候
  4. 列级别 创建/修改列的时候

注意:

  • 字符集具有传递性,如果在创建表或者列的时候没有指定字符集,则会根据可供修改级别依次继承
  • mysql默认从客户端传到服务器的数据编码是character_set_client,然后会传递给服务器,将字符串按照character_set_client进行解码然后转换为charset_set_connection,服务器处理之后再讲连接编码转换为charset_set_results;
  • 在mysql中设置:SET NAMES 字符集名;则会将三个的字符集跟新建的这个字符集同步或者再客户端启动的时候在mysql的配置文件中default-character-set=utf8
  • 我们不止可以指定字符集还可以指定字符的比较规则,但是在mysql中默认修改字符集或者比较规则其中一条,另一个也会跟着变

扩展

内存:

  • 内存本身是通过电容来实现二进制位的存储,每一个位的电容都非常小,只不过内存制造的非常密集所以可以保存很多位
  • 内存的构造中是通过地址线和数据线连接到二维的矩阵单元中实现读写操作的
  • 内存在读写和存储过程中都离不开电。

磁盘:

可抽象理解为老式留声机,老式留声机播放唱片的时候,唱针就会读取凹槽记录的信息,然后传递到喇叭中;磁盘也是类似的但是磁盘同时会有读写两个针头,在读写数据的时候,读写头会去定位数据所在扇区的地址,这个定位是传动臂控制角度和磁盘的旋转共同决定的,所以在不断读写的过程中,传动臂会不断调整角度,我们通常所说的读写瓶颈就是不断定位的这个过程。

INNODB

  • 数据库储存数据是一个持久化的过程,从扩展可知持久化数据必须是储存在磁盘之中的,而磁盘读写是很低效的。
  • innodb使用方式是:将数据分页,以页作为磁盘和内存之间交互的基本单位,每个页的大小一般为16kb

InnoDB强大的功能

  1. 崩溃恢复;无论是硬件还是软件层面出现问题,在重启mysql服务之后,均会恢复到崩溃之前的一次提交
  2. 强大的缓存池;各种类型的数据和索引都会在访问时缓存在主内存中,专用数据库服务器通常会将高达80%的物理内存分配给缓存池
  3. 校验和机制,保证数据的完整;该机制会先算出数据的校验和比如数据长度等信息,在传输的时候先发送校验和,在发送真实数据,然后计算发送过来的校验和,如果二者不相等则认为是脏数据
  4. 自适应哈希索引;当频繁的访问某一行数据的时候,innodb有个自适应哈希索引功能,会将这行数据的访问优化成好像访问hash表一样
  5. 自带储存引擎监控( INFORMATION_SCHEMA )和性能监控(Performance Schema)

数据页结构

名称 占用空间大小(字节) 描述
File Header 38 文件头部,数据页的通用信息,记录了该页的类型,页号,上个页的页号,下个页的页号;每个数据页组成一个双向链表结构
当前数据页的类型,以及所属表空间等信息
Page Header 56 数据页头部,数据页的专有信息,基本都是储存记录的状态信息
Infimum + Supremum 26 最小和最大记录,和行记录头信息的heap_no字段相关联,真实记录中heap_no是从2开始的,而0和1分别代表最小和最大记录
User Records 待定 用户记录,初始化的时候没有该模块,当我们每次插入一条记录,会从Free Space中分配相应的内存空间存储,
Free Space 待定 空闲空间,当该模块的内存被吸收完的时候就不存在该模块,且会申请新的页储存记录
Page Directory 待定 页面目录,
1.将所有未标记删除的记录分组
2.将每组的最后一条记录的地址偏移量(槽)提出来放在页的尾部
分组规则:对于最小记录所在的分组只能有 1 条记录,最大记录所在的分组拥有的记录条数只能在 1到8 条之间,剩下的分组中记录的条数范围只能在是 4到8 条之间
File Trailer 8 文件尾部,校验页是否完整;这个部分由8个字节组成,前4个字节代表页的校验和,后4个字节代表页面被最后修改时对应的日志序列位置(LSN);
在进行数据同步之前首先会将数据的校验和同步到内存中去,当数据全部同步过来之后再计算校验和如果计算结果和刚开始同步过来的相同则数据完整

COMPACT行格式

组成结构

变长字段长度列表:fa-minus: null值列表:fa-minus: 记录头信息:fa-minus:记录真实数据

变长字段长度列表

  对于所有的varxxx(M)和xxxblob类型,因为储存类型不是固定的,为了能让mysql服务器便于管理,即了解存在服务器所有数据占用的内存,所以这些类型的数据真实占用的字节数都会被记录在相应数据行的头部,且每个字段的真实字节长度在列表中是按顺序逆序存放,即字段

1
2
3
4
5
create table test(
a1 varchar(len1),
a2 varchar(len2),
a3 varchar(len3)
);

在变长字段列表中存放的顺序就是len3,len2,len1;且列表只储存值为非NULL的。
  我们将每个字符类型所占用的最大字节数记录为W,建表时申明的字段类型长度记录为M,即前面的len1,len2,真实数据储存的字节数记录为L,则当MxW>255且L>127,变长字段长度用两个字节表示否则用一个字节表示。

null值列表

列表只会统计字段为null的值,如果字段中没有null值则该列表不存在且该列表只能用整数个字节表示,如果不够整数字节则在高位补0。

记录头信息

名称 大小(bit) 描述
预留位1 1
预留位2 1
delete_mask 1 该记录是否被删除,1:删除 0:未删除;删除一条记录在数据页中并不会真的清除,所有被删除的记录会组成一个垃圾链表,
如果有新的记录插入表中,可能会把删除记录所占用的空间覆盖掉
min_rec_mask 1 B+数每层非叶子节点中的最小记录标记,该属性只会出现在索引页中
n_owned 4 表示当前记录拥有的记录数
heap_no 13 表示当前记录在记录堆的位置信息
record_type 3 表示当前记录的类型,0表示普通记录,1表示B+树非叶节点记录,2表示最小记录,3表示最大记录
next_record 16 表示下一条记录的相对位置,每两条记录两两之间的关系类似于链表,上一个节点中记录下各节点的位置

记录真实数据

在记录真实数据列表中除了我们存放的真实数据之外还有几个冗余的隐藏列

  1. DB_ROW_ID:这个隐藏列跟mysql的主键生成有关,如果用户定义了主键就是用用户定义的,反之查看表中是否存在非空unique修饰的字段,
    如果有则使用这个字段作为隐藏的行id,如果没有则自动生成。 
    服务器会维护一个全局变量,每当新增数据的时候会将其值赋值给当前插入记录的ROW_ID,并将全局变量+1
    每当这个变量的值为256的倍数时,就会将该变量的值刷新到系统表空间的页号为7的页面中一个Max Row Id属性处
  2. DB_TRX_ID:事务id,生成方法跟ROW_ID同理,只不过保存在页号为5的页面中,属性为Max Trx Id
  3. DB_ROLL_PTR:回滚指针,指向当前插入记录的日志所在页面(类型为FIL_PAGE_UNDO_LOG)
    每次对某条聚簇索引记录进行改动时,都会把旧的版本写入到undo日志中,然后这个隐藏列就相当于一个指针,可以通过它来找到该记录修改前的信息。

行溢出数据

  根据字符集的不同,每行所能储存的最大数据量也是不同的,即(2^16-1)/W=max(M);但是真实的大小是比计算出来的这个max(M)要小;因为其中还包含了一些标识等信息。
  因为innodb是通过数据页在内存和磁盘之间交换数据的每个数据页大小为16kb即16384个字节,所以当数据量超过这个数量的时候mysql会选择分页储存,在每个行的记录真实数据处除了存放真实数据之外还会留出20个字节储存指向另一部分数据页的地址

行记录和数据页的关系

  了解了每条数据记录的组成结构,可以知道行格式是对每一条记录的作为最小单元的拆解;我们知道每条记录是按顺序存储在数据页中的,因为每条记录行格式记录了下条记录所储存的位置,所以每组记录中都是以单链表的形式首尾相连的,具体记录的储存和分组规则可以查看数据页机构中Page Directory的描述。


Q1:知识有点琐碎和平时了解到的联系不起来怎么搞?
A1:那是因为平时了解的过于浅显,如果现在难以理解,不妨先混个眼熟,到时候在过来瞧瞧
Q2:有个问题,我们在创建库或者表的时候选字符集通常是utf8但是还有个utf8mb64,能解释下吗?
A1:其实utf8是utf8mb3的别名,我们常见的字符通常可以用1-3个字节表示,utf8的最大字节数就是3,但是当我们需要存一些图片表情之类的可以使用utf8mb4字符,之所以默认使用utf8mb3是因为,字节最大长度对性能有一定的影响(具体可以看下行格式中计算字节数用到的地方)。