第一章 MySQL架构与历史

1. MySQL逻辑架构

image.png

如上图所示：
第一层是连接处理、授权认证、安全等等；
第二层是查询解析、分析、优化、缓存以及所有的内置函数（如日期、时间、数学函数），所有跨储存引擎的功能都在这一层实现如储存过程、触发器、视图等；
第三层是储存引擎，负责数据的储存和提取；
就像OOP设计思想一样，第二层声明了一堆接口，第三层的储存引擎实现了第二层的接口，例如“开始一个事务”或者“根据主键提取一行记录”等操作，当然不同储存引擎的实现是不太一样的。

1.1 连接管理与安全性

服务器进程中拥有一个线程池来处理客户端的连接，可以使用少量的线程服务大量的连接；当客户端连接进来时，服务器需要对账户密码、原始主机信息校验，连接成功后，会做查询的权限进行检查；

1.2 优化与执行

（1）查询缓存，如果能找到缓存，则直接返回；
（2）解析查询，并创建解析树进行各种优化（重新查询、决定表的读取顺序、选择合适的索引，用户可以通过特殊的关键字提示优化器来影响它的决策过程）
（3）调用储存引擎进行数据提取；

2. 并发控制

MySQL数据库用锁来解决并发读写的问题

2.1 读写锁

写锁（排他锁）：获取写锁后，其他写、读操作都必须等待；
读锁（共享锁）：资源共享，获得读锁后，其他客户依然可以获得读锁，但是不能获得写锁；
读、写锁是排斥的，如果获得了读锁，那其他客户就不能获得写锁，相反如果获得了写锁，那其他客户就不能获得读锁；

2.2 锁粒度

表锁（table lock）：整个表加锁，最基本的锁策略，开销最小（如ALTER TABLE）；
行锁（row lock）：只对操作的行加锁，最大限度支持并发处理，但是锁的开销也很大，只在储存引擎层实现行锁；

3. 事务

事务的四个特性（ACID）：
A原子性（atomicity）：整个事务中的操作，要么成功，要么失败；
I隔离性（isolation）：一个事务在提交前，在其他事务中是不可见的；
D持久性（durability）：一旦事务提交，所有操作就永久保存到数据库中；
C一致性（consistency）：应用系统从一个正确的状态到另一个正确的状态；
ACID就是事务能够通过AID来保证这个C的过程
C是目的，AID都是手段

3.1 隔离级别

假如有一行数据A=20，两个客户端a，b都在对A进行事务操作

• READ UNCOMMITTED（未提交读）
  a获取了A的写锁，执行A=10，释放写锁，然后去做其他耗时的操作；
  b此时不获取任何锁，读到A=10；
  a发现应用异常，所以回滚了；
  此时b读到A=10是不对的，出现脏读；
• READ COMMITED（读提交）
  a获取了A的读锁，读到A=20，释放读锁，然后去做其他耗时的操作；
  b此时获取A的写锁，执行A=30，然后提交事务；
  a做完了耗时操作，再次获取读锁，读到A=30，虽然读到的值是正确的，但是出现不可重复读；
• REPEATABLE READ（可重复读）（MySQL默认隔离级别）
  a获取了A的读锁，读到A=20，不释放读锁，然后去做其他耗时的操作；
  b此时获取A的写锁失败；
  a做完了耗时操作，再次读取A，读到A=20，读到的值是正确的，解决了不可重复读的问题；
  然后a获取了所有值等于20的行读锁，想做个统计，读到A=20，不释放读锁，然后去做其他耗时的操作；
  b此时插入一条值为20的记录，B=20，提交事务；
  此时a再次读取所有值等于20的行记录，发现多了B这行，出现幻读；
• SERIALIZABLE（可串行化）
  所有的读写操作都按顺序执行，万无一失，完美！（狗头）

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITED	会	会	会
READ COMMITED	不会	会	会
REPEATABLE READ	不会	不会	会
SERIALIZABLE	不会	不会	不会

3.2 死锁

死锁是指两个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象；
假如有a，b两个事务，
a 开始事务
update t set price=20 where id=1;
update t set price=30 where id=2;
a提交事务

b 开始事务
update t set price=50 where id=2;
update t set price=60 where id=1;
b提交事务

当a和b同时执行，a获取id=1的写锁；b获取id=2的写锁；
两个事务都执行完第一行语句ab都没释放，这时候，a想要获取id=2的写锁，b想要获取id=1的写锁，然而发现改行已经被对方锁定，然后都在等待对方释放锁，同时又持有对方需要的锁，陷入死循环；必须要有第三方介入；

解决方案：死锁检测和死锁超时机制；
InnoDB目前处理死锁方法是，将持有最少行级排他锁的事务进行回滚

3.3 事务日志

WAL（Write-Ahead Logging）：预写式日志，当事务修改数据并提交事务，先写日志，此日志使用追加的方式，所以速度非常快，写完日志然后刷新内存，等CPU有空闲的时候，再把内存的数据刷到磁盘做持久化；此时如果系统崩溃重启，内存的数据尚未写到磁盘，数据库服务器会加载日志恢复这部分数据，具体的恢复方式则视储存引擎而定。

3.4 MySQL中的事务

MySQL中的事务默认是自动提交的（AUTOCOMMIT），也就是说，如果不显式的开始一个事务，则每个查询都被当做一个事务执行提交操作。
连接中设置AUTOCOMMIT的语句：SET AUTOCOMMIT = 1，所有的查询都是在一个事务中，直到显式的COMMIT提交或者ROLLBACK回滚；
在事务中混合使用储存引擎是会有问题的！

4. 多版本并发控制（MVCC）

MySQL的大多数事务型储存引擎实现的都不是简单的行级锁，不同的储存引擎的MVCC实现是不同的，有乐观并发控制，悲观并发控制。
MVCC的实现，通过保存数据在某个时间点的快照来实现。不管需要执行多长时间，每个事物看到的数据都是一致的。根据事务开始的时间不同，每个事物对同一张表，同一时刻看到的数据可能不一样。
（懵逼中...)

5. MySQL的储存引擎
   用InnoDB就对了！
   切换表的储存引擎语句：
   ALTER TABLE mytable ENGINE = InnoDB；