1、基础篇

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

server层

查询缓存在 8.0 被删除，要完全一样的 sql 才能触发

• 连接器：
  • TCP三次握手（MySQL基于TCP进行传输）
  • 握手成功后，验证用户名、密码
  • 验证通过后，获取并保存本次权限（连接过程中不变，新建立的连接使用最新的权限设置）
• 解析器：
  • 词法分析：构建 sql 语法树，提取表名、字段名等
  • 语法分析：是否满足 Mysql 语法
• 预处理器：
  • 检查 SQL 查询语句中的表或者字段是否存在
  • 将 select * 中的 * 符号，扩展为表上的所有列
• 优化器：
  • 确定使用哪个索引
• 执行器：是一个 while 循环，直到获取所有结果
  • 根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端

存储引擎层

• 负责数据的存储，提取

获取执行计划

执行计划：优化器优化后 sql 语句具体执行方式，不会走到执行器

通过 EXPLAIN [SQL] 获取，只能获取 DML

Mysql 存储

• 表空间字段
  • 段：有多个区组成
    • 索引段：存放索引
    • 数据段：存放数据
    • 会滚段：undo log
  • 区：多个页组成
    • 表数据较多时按照区分配，避免碎片化，同时加速 I/O
  • 页：读写最小单元，默认 16KB
  • 行：单条记录
    • Mysql 规定除了 TEXT/BLOB 这种，其他所有的列最大占用 65535 个字节（不包含隐藏字段和记录头，但是包含变长字段列表和 NULL 值列表）

行格式

• 四种
  • Redundant：废弃⚠️
  • Compact、Dynamic 和 Compressed：基本类似

单行记录：每一列最少 1 个字节，不够会填充

• 额外信息
  • 变长字段列表：varchar/TEXT/BLOB 等变长字段的实际占用长度列表
    • 没有变长字段不会有
  • NULL 值列表：每个允许为 NULL 的字段占用 1bit
    • 为 1 表示 NULL，0 表示非 NULL
    • 所有字段定义为 NOT NULL 时不会有
  • 记录头：比较多
    • delete_mask：delete 操作后标记为 1，后台的 purge 进程在记录不再被任何事务引用后将其物理删除
      • 有事务引用可能回滚，不能直接删除，且 MVCC 会用到
    • next_record：下一条记录的位置，记录间通过链表组织
• 真实数据
  • 隐藏字段：
    • row_id：没有主键和唯一索引时作为隐藏主键
    • trx_id：事务 id，表征数据由哪个事务生成，MVCC 使用
    • roll_pointer：改记录上个版本的指针，MVCC 使用
  • 真实字段：。。。

varchar(n) 中 n 的最大取值

varchar(n) 字段类型的 n 代表的是最多存储的字符数量，并不是字节大小。

• ascii：一个字符一个字节
• UTF-8:一个字符三个字节
• utf8mb4：一个字符四个字节

所以可用字符数，需要先减去变长字段字节数2个字节（一个字节最大255，两个字节65535）

• 如果允许为NULL，剩余65535-2-1=65532（一个 NULL 值列表）
• 否则剩余 65535-2 = 65533
• 如果是 ascii 字符集，那么就是字符数量
• 如果是UTF-8，需要除上3
• 如果是utf8mb4，需要除上4
• 多个字段，需要减去其他字段的长度

行溢出

65535B = 64KB，因此一行很容易超出一页，对于 TEXT 这种更容易了

Mysql 会使用溢出页存放

• Compact 行格式：溢出页和当前页都存放部分数据即下一个溢出页的地址（最常用）
• Compressed 和 Dynamic 行格式：当前页只存储指向溢出页的地址，所有数据都在溢出页

Mysql 自增

自增主键值存放位置

• MyISAM：数据文件中
• InnoDB：
  • 5.x 版本：内存中，重启会重新计算当前表中最大值作为新的自增值
  • 8.0 之后：放在 redo log 中，发生重启不会重置
• 如果插入时。自增字段
  • 为 0/null/不指定：用 AUTO_INCREMENT 填充
  • 指定了特定值：使用指定值
• 自增值修改在执行插入操作之前，如下情况出现自增值不连续
  • 1、插入失败（失败一次丢一个值）
  • 2、插入后回滚（和上面类似）
  • 3、insert ... select，每次不够了会申请两倍的 id
• 如果是给前端返回的数据，一般不建议自增（容易被其他人看到比如用户增量等信息），比较适合不返回给前端，或者内部平台使用的数据

数据类型

时间类型

类型	存储空间	日期格式	日期范围	是否带时区信息
DATETIME	5~8 字节	YYYY-MM-DD hh:mm:ss[.fraction]	1000-01-01 00:00:00～ 9999-12-31 23:59:59	否
TIMESTAMP	4~7 字节	YYYY-MM-DD hh:mm:ss[.fraction]	1970-01-01 00:00:01 ～ 2038-01-19 03:14:07	是（需要转换，耗费性能）
数值型时间戳	4 字节	全数字如 1578707612 （可读性差）	1970-01-01 00:00:01 之后的时间	否
字符串	10~19 字节	任意格式 占用空间更大 无法使用时间相关 API	任意时间	否

Decimal 和 FLOAT/DOUBLE

• DECIMAL ：定点数，存储精确小数值
• FLOAT/DOUBLE：浮点数，存储近似小数值

TEXT 和 BLOB

• BLOB 可以存放二进制对象：图片/音视频，但是一般使用 oss 存放
• TEXT 可以存放很大的字符串，如博客，一般单表存放

NULL 和 ''

• NULL：不确定的值，不同类型的字段的 NULL 不相等（带有类型信息）
  • 需要使用空间存放（NULL 字段）
  • 不能用等值判定
  • 和任何运算符比较结果都是 false，所以SELECT NULL=NULL是 false
  • 不建议使用，服务器需要对字段判空，且不会被索引存储
• ''：字符串类型，
  • 不占用空间
  • 可以用等值判定

Boolean

• 没有专门的布尔类型，用 TINYINT(1) 表示

基础概念

ER图

ER 图： Entity Relationship Diagram（实体联系图）

• 实体：如一个校园管理系统，会涉及学生、教师、课程、班级等等实体。在 ER 图中，实体使用矩形框表示。
• 属性：即某个实体拥有的属性，即字段。在 ER 图中，属性使用椭圆形表示。
• 联系：即实体与实体之间的关系，有一对一，一对多，多对多

据库三大范式是什么

• 第一范式：每个列都不可以再拆分。可以将第一范式理解为数据库表的每个“格子”都应该是单一值，而不是多个值的集合或者组合。
• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。可以将第二范式理解为消除表中的冗余数据，确保每个数据只存储在一个地方，避免数据的重复。
• 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

反范式

• 避免联合查询，将相关数据冗余放置在表中

外键

• 外键(外码)：外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键，表示关联关系。
  • 但是如果更新了一个表的主键，其他表的外键会级联更新，会带来很大的性能影响
  • 更新删除都需要考虑约束
  • 分库分表外键会失效
  • 一般会通过业务层实现

drop、delete 与 truncate 区别？

用法不同

• drop table 表名 ：删除表。
• truncate table 表名 ：清空数据库，恢复到初次创建状态。
• delete from 表名 where 列名=值：删除某一行的数据，不加 where 删除全部数据，但是不回复主键自增值。
  • 只有这个产生日志，前两个不产生日志

DML 语句和 DDL 语句区别：

• DML（Data Manipulation Language）：数据库操作语言，指对数据库中表记录的增删改查。
• DDL （Data Definition Language）：数据定义语言，对数据库库、表增删改查
• DQL（Data Query Language）：数据库查询语言，一般查询最多，所以有这个

存储引擎

	MyISAM	InnoDB
行级锁	无，只有表锁	有
事务	不支持	支持
外键	不支持	支持
异常崩溃后安全恢复	不支持	支持（redo log）
MVCC	不支持	支持
性能	读写不能并发 无行锁，性能差	性能高

触发器

六种

• Before Insert

• After Insert

• Before Update

• After Update

• Before Delete

• After Delete