第一章 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的实现,通过保存数据在某个时间点的快照来实现。不管需要执行多长时间,每个事物看到的数据都是一致的。根据事务开始的时间不同,每个事物对同一张表,同一时刻看到的数据可能不一样。
(懵逼中...)

  1. MySQL的储存引擎
    用InnoDB就对了!
    切换表的储存引擎语句:
    ALTER TABLE mytable ENGINE = InnoDB;

推荐阅读更多精彩内容