1 MySql基础介绍
1 mysql逻辑架构
mysql逻辑架构图：
Mysql服务器、存储引擎 是两个独立的组件，彼此通过api交互
- 第一层：连接处理、授权认证、安全管理
- 第二层：核心服务功能
- 查询解析、分析、优化、缓存以及所有的内置函数(日期、时间、数学、加密函数等）
- 跨存储引擎的功能：存储过程、触发器、视图等。
- 第三层：存储引擎，负责MySQL中数据的存储和提取。
- 服务器通过API与存储引擎进行通信。
- 存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信，仅响应上层服务器的请求。
1.1 连接管理与安全性
- 服务器为每个客户端单独开辟一个线程（或线程池（少量线程）以应对大量连接），处理来自该客户端的所有连接。
- 认证方式：
- 用户名+密码+主机信息(ip 端口)
- 安全套接字SSL （后续章节详解 todo）
- 连接权限校验
1.2 优化与执行
优化器工作内容：
- 先解析查询，并创建解析树，再优化，如：重写查询、决定表的读取顺序、选择合适的索引等。使用【优化器解释explain】来查看其优化内容
- 优化器并不关心表使用什么存储引擎，但存储引擎对于优化查询有影响：优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。
- 对于SELECT语句，在解析查询之前，服务器会先检查查询缓存，如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。
2 并发控制
Mysql有两个层面的并发控制：服务器层和存储引擎层
2.1 锁粒度
- 每个存储引擎都可以实现自己的锁策略和锁粒度。
- 两种基本锁粒度：表锁、行级锁
2.2 表锁
- Mysql最基本的锁策略，也是开销最小的策略。它会锁定整张表。对表进行写操作前，需先获得写锁。读写互斥，读读不互斥
- 写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面(反之读锁则不能插入到写锁的前面)。
- 尽管存储引擎可以管理自己的锁，MySQL服务器还是会使用各种有效的表锁来实现不同
的目的。例如，Mysql服务器会为诸如ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。
2.3 行级锁
- 只在存储引擎层实现，而MySQL服务器层没有实现。
- 并发性好，但锁开销大
3 事务
3.1 数据库事务四特性
事务是指一组逻辑操作，它们要么一起成功，要么一起失败。
ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）
- 原子性：事务是一个不可分割的最小单元，事务中的操作要么都发生，要么都不发生。
- 一致性：如果事务执行之前数据库是一个完整的状态，那么事务结束后（无论事务是否执行成功）数据库仍然是一个完整的状态。(DB中所有的数据都符合DB的约束规范）
- 隔离性：多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离。
- 持久性：事务一旦被提交，它对数据库的影响是永久性的
3.2 隔离级别
SQL标准定义了四种隔离级别：
| 隔离级别 | 说明 | 问题 |
|---|---|---|
| 读未提交（Read Uncommitted） | 事务A可读取事务B未提交的数据 | 引发 脏读 |
| 读已提交（Read Committed） | 事务A只能读取其它已提交事务的数据； | 引发 不可重复读 |
| 可重复读（Repeatable Read） | 保证同一事务中多次读取同样的记录的结果是一致的；Mysql默认事务隔离级别 | 解决了脏读；但引发 幻读 |
| 可串行化（Serializable） | 强制事务串行执行，在读的每一行数据行上加锁 | 大量的超时和锁竞争 |
-
脏读：一个事务读取了另外一个未提交事务数据
-
不可重复读取：在当前事务中，读取了另一事务提交的更新或者删除的数据。异常情形：同一事务先后执行相同的select语句时可能看到不一样的结果
-
幻读：当事务A在读取某个范围内的记录时,事务B又在该范围内插入了新的记录，当事务A再次读取该范围的记录时，会产生幻行（读取到新插入的记录）；多版本控制(MVVC)解决幻读
总结：
3.3 死锁
数据库系统实现了多种锁检测和死锁超时机制：
- 方式一：当检测到死锁的循环依赖，立即返回一个错误。
- 方式二：当查询的时间达到锁等待超时的设定值后，放弃锁请求
- InnoDB处理死锁的方法：将持有最少行级排他锁的事务进行回滚
3.4 事务日志
事务日志的目的是提交事务效率。
- 首先数据库的数据可以被缓存到内存
- 修改表数据时，会先修改内存中的数据，再把该修改行为以追加的方式记录到事务日志
- 事务日志保存到磁盘后，后台线程稍后把修改的数据刷新到磁盘
- 它是一种预写日志策略（write ahead logger），修改数据需要写两次磁盘
3.5 MySql中的事务
一、自动提交
默认采用自动提交模式（auto commit）
- 自动提交模式：如果不是显式地开启一个事务，那么每一条sql指令都会当做一个事务来执行。
- 非自动提交模式：所有sql指令都在一个事务中，直到显式地执行COMMIT提交或者ROLLBACK回滚，该事务结束，同时又开始了新的事务。
二、在事务中混合使用存储引擎
MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中,使用多种存储引擎是不可靠的。
三、隐式和显式锁定
todo
4 多版本并发控制MVCC
目标：减少不必要的锁操作
实现原理：
一、两个前提：
- 每个事务开始之前，会以递增方式生成一个系统版本号。以下简称：VNum
- 在每一行记录增加两个隐藏列：一个保存行的创建时间、一个保存行的删除时间。这两个时间概念用系统版本号来代替
二、对于不同的sql指令，InnoDB执行不同的操作：
INSERT： 为新插入的每一行保存VNum作为行版本号。
DELETE：为删除的每一行保存VNum作为行删除标识。
UPDATE：为插入一行新记录，保存VNum作为行版本号，同时保存VNum到原来的行作为行删除标识。
SELECT：
InnoDB会根据以下两个条件检查每行记录:
- 只查找版本早于当前事务版本的数据行(也就是，行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
- 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的记录，才能返回作为查询结果。
温馨提示：数据库的删除、更新，跟浅义上的理解不一样：1、delete并非把数据从磁盘删除；update它是先插入后删除。在后续学习笔记会对此进行更深入讲解。
5 MySql常用存储引擎
5.1 InnoDB
- InnoDB采用MVCC来支持高并发，实现了四个标准的隔离级别，默认级别：REPEATABLE READ (可重复读)
- 通过间隙锁(next-key locking)策略防止幻读的出现 （后续章节详解 todo)
- InnoDB的表是基于聚族索引建立 （后续章节详解 todo)
- 存储格式是平台独立，可以将数据和索引文件跨平台复制。
- 其它优化：可预测性预读、自适应hash索引、插入缓冲区等 （后续章节详解 todo)
- 支持真正热备份 （后续章节详解 todo)
5.2 MyISAM
不支持：事务、行级锁、奔溃后安全恢复
适用于：表比较小、读多写少的场景
特性：
一、加锁与并发
对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。在表有读取查询的同时，也可以往表中插入新的记录(并发插人)
二、奔溃修复
崩溃后无法安全恢复
三、索引特性
支持全文索引：对于MyISAM表，即使是BL0B和TEXT等长字段，也可以基于其前500个字符创建索引
6 InnoDB如何使用MVCC解决幻读
后续章节详解 todo
可参照：InnoDB事务模型和锁定
