MySQL-结构+范式

MySQL整体结构

连接层

主要是指数据库连接池，会负责处理所有客户端接入的工作。

建立连接

• TCP的三次握手过程，SSL加密连接还会经过SSL多次握手过程，握手完成建立session连接
• MySQL与客户端之间采用半双工通讯机制
• 一个线程维护一个客户端连接
• MySQL默认会将连接维护八小时，在这八小时内不会销毁，除非客户端主动发送了quit指令，这时MySQL才会主动销毁连接，放到连接池中

连接池

• 因为线程在任何系统中都属于珍贵资源，频繁创建和销毁的代价比较高，当客户端主动退出连接后，MySQL只会将对应线程绑定的会话信息清空，然后将“空闲”的线程放入自身的连接池当中，以备下次客户端连接时使用。

服务层

主要包含SQL接口、解析器、优化器以及缓存缓冲区四块区域。

SQL接口

• 客户端连接传递SQL语句的入口，数据库返回数据时的出口

• 语句类型

  • DML：数据库操作语句，比如update、delete、insert等都属于这个分类。
  • DDL：数据库定义语句，比如create、alter、drop等都属于这个分类。
  • DQL：数据库查询语句，比如最常见的select就属于这个分类。
  • DCL：数据库控制语句，比如grant、revoke控制权限的语句都属于这个分类。
  • TCL：事务控制语句，例如commit、rollback、setpoint等语句属于这个分类。

• 触发器&存储过程

  • 存储过程
    • 提前编写好的一段较为常用或复杂SQL语句，然后指定一个名称存储起来，然后先经过编译、优化，完成后，这个“过程”会被嵌入到MySQL中
  • 触发器
    • 触发器则是一种特殊的存储过程
    • INSERT、UPDATE、DELETE触发执行
    • 可以通过AFTER、BEFORE语句声明触发的时机

解析器

• 验证SQL语句是否正确
• 将SQL语句解析成MySQL能看懂的机器码指令

优化器

• 生成执行计划：选择最合适的索引、join方式等，最终得到一套最优的执行计划

缓存&缓冲

• 读取缓存
• select语句的数据缓存、权限缓存、引擎缓存等信息
• 高版本(>=5.8)的MySQL中，移除了查询缓存区，毕竟命中率不高
  • 移除原因
    • ①缓存命中率低：几乎大部分SQL都无法从查询缓存中获得数据。
    • ②占用内存高：将大量查询结果放入到内存中，会占用至少几百MB的内存。
    • ③增加查询步骤：查询表之前会先查一次缓存，查询后会将结果放入缓存，额外多几步开销。
    • ④缓存维护成本不小，需要LRU算法淘汰缓存，同时每次更新/插入/删除数据时，都要清空缓存中对应的数据。
    • ⑤查询缓存是专门为MyISAM引擎设计的，而InnoDB构建的缓冲区完全具备查询缓存的作用。
    • ⑥同时项目中一般都会用Redis做业务缓存，能来到MySQL查询的语句十有八九是要走磁盘的，因此查询缓存的存在，反而弊大于利
• 写入缓冲
• 通过内存的速度来弥补磁盘速度较慢对数据库造成的性能影响
• 读取数据操作时，会先从磁盘读到数据放在缓冲区中，后续相同操作时，可以基于内存操作
• 写操作时，会先从缓冲区中查询是否有要操作的数据，如果有，则直接对内存中的数据进行操作（例如修改、删除等），对缓冲区中的数据操作完成后，会直接给客户端返回成功的信息，然后MySQL会在后台利用一种名为Checkpoint的机制，将内存中更新的数据刷写到磁盘
• 两者的区别
• 查询缓存只能给读语句使用，而缓冲读写语句都能用

存储引擎层

这里是指MySQL支持的各大存储引擎，如InnoDB、MyISAM等。

• 是MySQL数据库中与磁盘文件打交道的子系统
• 引擎负责数据的管理、库表管理、索引管理等

文件系统层

涵盖了所有的日志，以及数据、索引文件，位于系统硬盘上。

日志模块-七种常用的日志类型

• binlog二进制日志，主要记录MySQL数据库的所有写操作（增删改）。
• redo-log重做/重写日志，MySQL崩溃时，对于未落盘的操作会记录在这里面，用于重启时重新落盘（InnoDB专有的）。
• undo-logs撤销/回滚日志：记录事务开始前[修改数据]的备份，用于回滚事务。
• error-log：错误日志：记录MySQL启动、运行、停止时的错误信息。
• general-log常规日志，主要记录MySQL收到的每一个查询或SQL命令。
• slow-log：慢查询日志，主要记录执行时间较长的SQL。
• relay-log：中继日志，主要用于主从复制做数据拷贝。

数据模块

MySQL的所有数据最终都会落盘（写入到磁盘），而不同的数据在磁盘空间中，存储的格式也并不相同

常见的数据文件类型

• db.opt文件：主要记录当前数据库使用的字符集和验证规则等信息。
• .frm文件：存储表结构的元数据信息文件，每张表都会有一个这样的文件。
• .MYD文件：用于存储表中所有数据的文件（MyISAM引擎独有的）。
• .MYI文件：用于存储表中索引信息的文件（MyISAM引擎独有的）。
• .ibd文件：用于存储表数据和索引信息的文件（InnoDB引擎独有的）。
• .ibdata文件：用于存储共享表空间的数据和索引的文件（InnoDB引擎独有）。
• .ibdata1文件：这个主要是用于存储MySQL系统（自带）表数据及结构的文件。
• .ib_logfile0/.ib_logfile1文件：用于故障数据恢复时的日志文件。
• .cnf/.ini：MySQL的配置文件，Windows下是.ini，其他系统大多为.cnf。

SQL语句诞生到结束

SQL如何诞生

• 诞生于客户端
  • 开发者自己手动编写
  • ORM框架自动生成
• 读取配置文件中的连接地址、账号密码
• 当完整的SQL生成后，会先去连接池中尝试获取一个连接对象
• 连接池中有空闲连接->拿到复用
• 如果没有。判断一下当前池中的连接数是否已达到最大连接数
• 连接数已经满->当前线程则需要等待其他线程释放连接对象
• 没满->直接再创建一个新的数据库连接使用
• tcp建立连接
• 首先会验证客户端的用户名和密码是否正确：
• 如果用户名不存在或密码错误，则抛出1045的错误码及错误信息。
• 判断MySQL连接池中是否存在空闲线程：
• 存在：直接从连接池中分配一条空闲线程维护当前客户端的连接。
• 不存在：创建一条新的工作线程（映射内核线程、分配栈空间…）。
• 工作线程会先查询MySQL自身的用户权限表，获取当前登录用户的权限信息并授权。

SQL语句在数据库中是如何执行的

读SQL

• ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。
• ②SQL接口在缓存中根据哈希值检索数据，如果缓存中有则直接返回数据。
• ③缓存中未命中时会将SQL交给解析器，解析器会判断SQL语句是否正确：
• 错误：抛出1064错误码及相关的语法错误信息。
• 正确：将SQL语句交给优化器处理，进入第④步。
• ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。
• ⑤工作线程根据执行计划，调用存储引擎所提供的API获取数据。
• ⑥存储引擎根据API调用方的操作，去磁盘中检索数据（索引、表数据…）。
• ⑦发生磁盘IO后，对于磁盘中符合要求的数据逐条返回给SQL接口。
• ⑧SQL接口会对所有的结果集进行处理（剔除列、合并数据…）并返回。

写SQL

• ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。
• ②在缓存中根据哈希值检索数据，如果缓存中有则将对应表的所有缓存全部删除。
• ③经过缓存后会将SQL交给解析器，解析器会判断SQL语句是否正确：
• 错误：抛出1064错误码及相关的语法错误信息。
• 正确：将SQL语句交给优化器处理，进入第④步。
• ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。
• ⑤在执行开始之前，先记录一下undo-log日志和redo-log(prepare状态)日志。
• ⑥在缓冲区中查找是否存在当前要操作的行记录或表数据（内存中）：
• 存在：
  • ⑦直接对缓冲区中的数据进行写操作。
  • ⑧然后利用Checkpoint机制刷写到磁盘。
• 不存在：
• ⑦根据执行计划，调用存储引擎的API。
• ⑧发生磁盘IO，对磁盘中的数据做写操作。
• ⑨写操作完成后，记录bin-log日志，同时将redo-log日志中的记录改为commit状态。
• ⑩将SQL执行耗时及操作成功的结果返回给SQL接口，再由SQL接口返回给客户端。

客户端服务端连接池的区别

• 客户端连接池：减少多次创建数据库连接时，频繁出现的TCP三次握手、四次挥手、SSL握手等过程。
• 服务端连接池：减少多次创建数据库连接时，频繁创建和销毁工作线程造成的资源开销。

范式

第一范式，原子性

存储的数据具备不可再分性（每个字段的值不能再分）。

• 不满足的影响
  • 客户端语言和表之间无法很好的生成映射关系。
  • 查询到数据后，需要处理数据时，还需要对字段进行额外拆分。
  • 插入数据时，需要先拼装后才能进行写入。

第二范式，唯一性

表中的所有列，其数据都必须依赖于主键（表内每行数据必须描述同一业务属性的数据）。

• 不满足的影响，数据冗余

第三范式，独立性

表中每个非主键字段之间不能存在依赖性。

• 不满足的影响
  • 删除和修改操作，需要改动大量行数据

巴斯范式

• 主键字段独立性，联合主键字段之间不能存在依赖性。
• 也被称为3.5NF，它是第三范式的补充版

第四范式

表中字段不能存在多值依赖关系。

• 多值依赖
  • 一个表中至少需要有三个独立的字段才会出现多值依赖问题
  • 多值依赖是指表中的字段之间存在一对多的关系，也就是一个字段的具体值会由多个字段来决定

第五范式

表中字段的数据之间不能存在连接依赖关系。

域键范式

试图研究出一个库表设计时的终极完美范式。-

数据库反范式设计

为什么需要反范式设计

• 随着范式的级别越高，设计出的结构会更加精细化，表的数量随之越来越多
• 当需要数据时，只能联表查，甚至为了一个字段的数据，也需要做一次连表查询
• 问题
  • 性能开销大
  • 可能造成索引失效
  • 严重地影响整个业务系统的性能
• 指自己知道会破坏范式，但对业务带来好处大于坏处时，刻意设计出破坏范式的结构

参考文章：https://juejin.cn/column/7140138832598401054