MySQL基础篇

基础篇

说明：

此类文章是为小林coding的图解MySQL，所简写，目的在于大家更快抓到小林文章的重点
本文全部由我简化，但是其中有部分引用小林的文章内容
希望大家掌握精髓，构建知识体系和知识框架

执行一条select 语句期间发生了什么？

MySQL 的架构共分为两层：Server 层和存储引擎层

Server 层负责建立连接、分析和执行 SQL
存储引擎层负责数据的存储和提取

1、连接器
连接mysql服务，先经过TCP三次握手，服务正常，建立连接
校验客户端的用户名和密码
连接器获取用户权限

show processlist;——查看有多少个客户端连接了服务
最大空闲时间—— show variables like ‘wait_timeout’;——8小时
kill connection + id ——手动杀死空闲连接
MySQL 服务支持的最大连接数——151——show variables like 'max_connections';
推荐使用长连接，为了避免占用内存问题，两种解决方式
1、定期断开长连接
2、客户端主动重置连接

2、查询缓存

解析SQL语句第一个字段，是select，去Query Cache查缓存数据，key-value方式存储
8版本删除了，因为太鸡肋了，原因：一旦有更新操作这个表的server层查询缓存就清空了

3、解析器解析SQL

解析器
1、词法分析，识别关键字，构造SQL语法树，方便后续获取SQL 类型啦，id的啦，字段名的啦，表名的啦，where条件的啦

2、语法分析，判断是否满足语法规则
tip:解析器不会查表和字段存不存在

4、执行SQL

• prepare 阶段，也就是预处理阶段；

查表和字段存不存在
将 select * 中的 * 符号，扩展为表上的所有列；

• optimize 阶段，也就是优化阶段；

优化器：制定执行计划，例如选择哪个索引

• execute 阶段，也就是执行阶段；

执行器：从存储引擎读取记录，返回给客户端

MySQL 一行记录是怎么存储的？

MySQL的NULL值怎么存放的？

MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。

NULL值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。

varchar(n) 实际占用数据的大小？

MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小。

varchar(n) 中 n 最大取值为多少？

一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。

如果一张表只有一个 varchar(n) 字段，且允许为 NULL，字符集为 ascii。varchar(n) 中 n 最大取值为 65532。

计算公式：65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 = 65535 - 2 - 1 = 65532。

如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

行溢出后，MySQL 是怎么处理的

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。

Compact 行格式针对行溢出的处理是这样的：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

Compressed 和 Dynamic 这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。

mysql数据存放在`表名.ibd`
SHOW VARIABLES LIKE 'datadir';
/var/lib/mysql/

db.opt # 存放默认字符集和字符校验规则
表名.frm # 表结构，元数据信息
表名.ibd # 表数据，也叫独占表空间文件

# 表的文件结构
表：
段：数据段，索引段，回滚段
区：1MB，64个页一个区，B+树组织数据，双向链表
页：16KB,读写单位，数据页，undo页，溢出页等
行：有不同的行格式

索引段：存放 B + 树的非叶子节点的区的集合
数据段：存放 B + 树的叶子节点的区的集合
回滚段：存放的是回滚数据的区的集合，MVCC 利用了回滚段实现了多版本查询数据

# 行格式
Redundant、5.1Compact、5.7Dynamic、Compressed
Redundant #  5.0 版本后退休了，非紧凑型
Compact #
额外信息：变长字段长度列表-NULL值列表-记录头信息，
真实数据：列值
变长字段的真实数据占用的字节数会按照列的顺序逆序存放

NULL 是不会存放在行格式中记录的真实数据部分里的，以「变长字段长度列表」里不需要保存值为 NULL 的变长字段的长度

为什么「变长字段长度列表」的信息要按照逆序存放？
「记录头信息」向左读就是记录头信息，向右读就是真实数据，比较方便

当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了
所以「变长字段长度列表」只出现在数据表有变长字段的时候

Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中，降低空间浪费
NULL 值列表也不是必须的。
当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。
设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）

存储字段类型为 varchar(n) 的数据时，其实分成了三个部分来存储：

• 真实数据
• 真实数据占用的字节数
• NULL 标识，如果不允许为NULL，这部分不需要

一个变长字段长度为1——或2，看n是否<=255

varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。（这是对于acill字符集，对于其他字符集例如utf8,一个字符三个字节，n最大=65532/3）

多字段的话：所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

当发生行溢出时，溢出的数据会存放到「溢出页」中
真实数据处用 20 字节存储指向溢出页的地址
如果是Dynamic，只存储 20 个字节的指针来指向溢出页，实际的数据都存储在溢出页中