MySQL常问考点总结

范式

• 第一范式（1NF）：数据库表中的每个属性都是原子、不可再分的。
• 第二范式（2NF）：第二范式要求满足第一范式的基础上，非主键属性完全依赖于主键。也就是说，表中的非主键属性必须完全依赖于主键，而不能部分依赖于主键，即不能依赖于组合键的一部分。
  下表就不符合2NF，学生姓名和课程名会重复出现。

StudentID	CourseID	StudentName	CourseName	Grade
1	101	Alice	Math	A
1	102	Alice	Science	B
2	101	Bob	Math	B
2	103	Bob	History	A
3	102	Charlie	Science	C

• 第三范式（3NF）：第三范式要求满足第二范式的基础上，消除表中的传递依赖。传递依赖是指非主键属性通过其他非主键属性传递依赖于主键。
  下表不符合3NF。部门名和部门名传递依赖于部门ID ，而不是主键(员工ID)

EmployeeID	EmployeeName	DepartmentID	DepartmentName	DepartmentManager
1	Alice	101	Sales	John
2	Bob	102	Marketing	Kate
3	Charlie	101	Sales	John
4	David	103	Finance	Mary
5	Emma	102	Marketing	Kate

• 还有其他范式如BCNF（Boyce-Codd范式）、第四范式（4NF）和第五范式（5NF）。这些范式的目标是进一步减少冗余和数据异常，但随着范式的提高，设计和查询复杂度也会增加。
• 范式设计需要在满足业务需求和性能要求之间进行权衡。在某些情况下，可以通过反范式化来优化性能，即有意地引入一定程度的冗余数据来减少查询的复杂性和提高性能。

事务

事务是一系列数据库操作的逻辑单元。

• ACID:

  • 原子性（Atomicity）：全部成功或全部回滚。事务执行期间的所有操作记录到日志中，失败时可以使用日志进行回滚操作。
  • 一致性（Consistency）：事务的执行不会破坏数据库的完整性约束。设计数据库时定义约束、触发器等以保证数据一致性。
  • 隔离性（Isolation）：并发执行的事务之间应该相互隔离，一个事务的中间结果对其他事务是不可见的。使用**锁和MVCC(多版本并发控制)**等。MVCC可以使每个事务只看到自己的视图。
  • 持久性（Durability）：事务一旦提交，对数据库的改变应该是永久性的，即使发生系统故障也不会丢失。使用日志记录事务的操作信息，即使故障重启也能恢复。

• 事务隔离级别

  • 读未提交（Read Uncommitted）：最低的隔离级别，允许一个事务读取另一个事务未提交的数据，可能导致脏读、不可重复读和幻读问题。
  • 读已提交（Read Committed）：要求一个事务只能读取另一个事务已提交的数据，解决了脏读问题，但可能会出现不可重复读和幻读问题。
  • 可重复读（Repeatable Read）：要求一个事务在整个过程中看到的数据保持一致，解决了不可重复读问题，但可能会出现幻读问题。
  • 串行化（Serializable）：最高的隔离级别，要求事务串行执行，避免了脏读、不可重复读和幻读问题。

• 事务隔离性问题

  • 脏读：事务B对数据进行了修改，但是又回滚了。事务A读了数据并正常执行结束，那么数据是有误、脏的。
  • 不可重复读：事务A读取数据，事务B修改或删除该数据并执行结束，事务A再读取时，两次数据读取不一致
  • 幻读：通常发生在范围查询时，不可重复读是因为实现可重复读时是对查询范围内的每个数据进行锁定，但是其他事务新增或删除(与不可重复读的区别)的数据没有锁定，当前事务仍然可以查询到查询范围内其他事务新插入的数据，那么事务A读、事务B增删、事务A读，事务A两次读取会不一致。

  隔离级别的增加在增加安全的同时，也增加系统开销和并发性降低，在实际应用中需要根据业务需求和性能要求来选择适当的隔离级别。

• 数据库的锁

  • 读锁：可以多个事务同时读。但是只要有一个读，其他人就不能获取写锁。
  • 写锁：只要有一个事务持有写锁，其他事务就不能获取读锁和写锁。
  • 意向锁
  • 行锁：事务只锁定访问的行。
  • 表锁 ：事务锁定整张表，性能比行锁好，但是更容易锁冲突和阻塞。
    • 行锁细粒度更高，锁管理开销较大。而表锁粒度较粗，容易发生阻塞。选择时需要考虑并发读和并发写的多少、业务需要的并发控制细粒度。
      数据库根据并发控制算法和事务隔离级别来管理锁机制，常见的并发控制算法包括两段锁协议、多版本并发控制（MVCC）等。
  • 页级锁

• 两段锁协议
  指所有的事务必须分两个阶段对数据项加锁和解锁

  • 加锁：在对任何数据进行读操作之前要申请并获得S锁（共享锁），在进行写操作之前要申请并获得X锁（排它锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
  • 解锁：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

• MVCC
  维护多个版本以实现并发控制。
  每个数据行关联一个版本号和时间戳。

  • 读操作：每个事务有一个全局唯一时间戳，无法读取时间戳晚于他自己的时间戳的数据版本。
  • 写操作：创建一个新的数据版本，并为他分配一个新的时间戳。旧版本的数据仍可以被旧版本事务读取。在该事务提交后，该新版本才可以对新事务可见。
  • 垃圾回收：旧版本数据会根据垃圾回收算法，即时回收空间。

• 日志：

  • redo log：物理操作日志，如页号xxx，偏移量yyy写入了’zzz’数据。可以崩溃恢复时重新执行未提交的事务或恢复已提交事务的修改。用于保证持久性。
  • undo log：逻辑操作日志，记录反操作用于回滚。比如进行了INSERT操作，那么就记录一条DELETE操作，以便在回滚事务或进行崩溃恢复时撤销已提交的事务修改。保证一致性、原子性。回滚到特定的版本即MVCC。能实现事务的隔离性。
  • bin log:记录所有的修改(写)操作，不记录读操作。用于数据恢复和数据复制。
  • 慢查询日志：记录了执行时间超过预设阈值的查询语句。可以找到潜在的性能瓶颈并进行优化。
  • 其他的还有什么查询日志、错误日志啥的
  • 在事务执行修改操作之前，MySQL会将修改操作记录到Redo Log中，以确保事务的持久性。
    在事务提交之前，MySQL会将修改操作记录到Undo Log中，以便在回滚事务或崩溃恢复时撤销已提交事务的修改。
    在事务提交后，MySQL将Redo Log中的修改操作应用到数据库中的数据页，确保数据的持久性。
    在事务回滚或崩溃恢复时，MySQL根据Undo Log中的信息撤销或重做事务的修改，恢复数据库到一致状态。

数据库的备份和恢复机制

全量备份(全部数据)和增量备份(新写的数据)

数据库扩容

• 垂直硬件扩容：增加CPU、内存、磁盘
• 水平扩展：集群、负载均衡(一致性hash)
• 数据分片：将数据分散存储(垂直拆分和水平拆分)，每个节点只存储部分数据
• 数据库复制：创建冗余副本

底层数据结构和索引

一般使用InnoDB作为存储引擎，ACID、MVCC

B+树

• 多叉树，而且修改操作要服务于降低层数的目的，减少IO
• 只有叶节点存放数据

存储引擎相关

• 每种索引都是一棵B+树，分为主键索引(聚簇索引)、非主键索引(二级索引)
• 一般来说要先把二级索引扫描完得到数据对应的ID，然后拿ID去主键索引上找
  • 回到主键上找的操作叫做回表。要回表的原因是：由于查询结果所需要的数据只在主键索引上有，所以不得不回表。
  • 覆盖索引：如果二级索引上已经存了要查的数据(联合索引、冗余索引)，那么就查完二级索引就直接返回数据，不需要回表
• 最左前缀：索引上搜索时，会从左到右看索引(联合索引)的内容，所以最左的字段最好是能快速命中的，比如就要将经常LIKE的数据放在索引靠右的位置，让左边索引先精准命中，然后缩小范围去LIKE
• 索引下推：如果二级索引是个联合索引 ( id , age )，那么在有where age=10的条件时，本来MySQL 5.6之前是一个一个拿着id去回表，然后再检查主键索引上的age字段。MySQL 5.6开始，因为二级索引有age字段，所以会age的判断操作放在二级索引上进行，少了回表。

2PC协议

分布式事务协议
下面是2PC协议的工作流程：

1. 准备阶段（Prepare Phase）：

   1.1 协调者（Coordinator）向所有参与者（Participants）发送事务的准备请求。

1.2 参与者执行事务的操作，并将操作的结果记录在本地的事务日志中。
1.3 参与者向协调者发送“准备就绪”（Ready）或“中止”（Abort）的响应。
1.4 如果所有参与者都发送了“准备就绪”响应，协调者进入下一个阶段。否则，协调者发送“中止”消息，终止事务。
2. 提交阶段（Commit Phase）：
2.1 协调者向所有参与者发送“提交”（Commit）请求。
2.2 参与者收到“提交”请求后，将事务的操作结果应用到持久化存储中。
2.3 参与者向协调者发送“完成”（Done）的响应。
2.4 协调者收到所有参与者的“完成”响应后，事务被提交，协调者发送“提交完成”（Commit Done）消息给所有参与者。

如果在任何阶段发生故障或参与者无法完成操作，2PC协议具有回滚机制来确保事务的一致性。

2PC简单，可以实现强一致性
缺点:

• 同步阻塞：在2PC的准备阶段和提交阶段，所有参与者都需要等待协调者的指令，这会导致同步阻塞，影响性能和可伸缩性。
• 单点故障：协调者是2PC协议的中心节点，一旦协调者发生故障，整个协议无法继续执行。
• 阻塞等待：如果有一个参与者无法正常响应或发生故障，其他参与者可能会一直阻塞等待，造成资源浪费和延迟。

MySQL的分布式部署

通过日志复制实现异步数据同步

分布式集群

• 主从复制、读写分离：主节点接收写操作，将操作记录到binlog，从节点复制主节点的binlog保持数据一致性。从节点处理只读操作。可以实现高可用、可伸缩(增加从节点分担读压力)、数据备份。

MySQL的安全机制

• 访问控制
  • 用户认证、密码加密
  • 管理员分配权限，权限包括数据库级别、表级别、列级别
  • 安全连接：支持SSL/TLS协议，加密网络连接
• 审计
  • 日志记录：错误日志、查询日志
  • 支持审计插件：记录和监控数据库操作
• 数据加密
• 定期备份

数据库连接池和连接管理

连接管理涉及连接的创建、获取、释放和销毁等操作，它负责确保连接的可用性和有效性，并管理连接的生命周期。通过重复利用连接，避免了频繁创建和销毁连接的开销，提高了数据库访问的效率。