001 mysql架构

一.MySQL历史与分支

1.历史

1979年    Unireg
1996年    MySQL1.0
1996年    MySQL3.11.1
1999年    MySQLAB公司成立
2000年    MyISAM存储引擎
2001年    集成InnoDB引擎
2003年    MySQL5.0，支持视图，存储过程等
2008年    Sun以10亿美金收购MySQLAB，MySQL进入Sun时代
2008年    MySQL5.1，提供了分区，事件管理，支持基于行的复制，修复了大量bug
2009年    Oracle以74亿美金收购Sun，MySQL进入Oracle时代，第三方引擎InnoDB2005年被Oracle收购
2010年    MySQL5.5InnoDB成为默认存储引擎
2011年    MySQL5.6
2013年    MySQL5.7
2016年    MySQL8.0

2.分支

MaxDB
MariaDB：文档数据库
Drizzle
Percona Server：高负载优化，提供了非常有用的性能诊断工具
InnoSQL：姜承尧

二.MySQL架构逻辑

1.mysql架构图

• a.最上层的服务：客户端，服务器等。
• b.第二层：mysql服务层。包含mysql大部分功能，查询解析，分析，优化，缓存以及内置函数（时间，日期，数学，加密等），所有跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。
• c.第三层：存储引擎，存储引擎负责mysql数据的存储和提取（也就是写和读），服务器通过API和存储引擎进行通信，API屏蔽了不同存储引擎之间的差异。

2.连接管理和安全性

• a.每个客户端连接都在服务器进程中有一个线程，这个连接的查询只会在这个线程中执行。
• b.服务器会负责缓存线程，因此就不需要为每一个新建的连接创建或销毁线程，线程池。
• c.客户端连接到服务器时，会进行验证（用户名，主机，密码），验证成功后，还会进行数据库权限的认证

3.优化与执行

• a.mysql会解析查询，创建内部数据结构（解析树），进行各种优化
• b.查询语句执行之前，会先查询缓存，如果有缓存，则直接从缓存里返回结果

三.并发控制

1.读写锁

• a.读锁是非阻塞的，写锁是阻塞的。

2.锁粒度

• a.表级锁：最基本的锁策略，开销最小，当对数据库表进行插入，删除，更新时，首先要获取写锁，然后会将整张表锁住，这时既不能写，也不能读。只有写锁释放，才能获取读锁，读锁之间不阻塞。
• b.行级锁：最大程度地支持了并发处理，但是同时也带来了最大的开销。行级锁只在存储引擎层实现，而mysql服务器层不实现，所有的存储引擎都以自己的方式显式地实现了锁。

3.乐观锁

乐观锁假设数据一般情况下不会造成冲突，所以在数据提交的时候才会检测冲突，如果发生冲突，则返回错误的信息，让用户决定如何去做。
实现方式：

• a.在表中添加一个数字类型version字段。当读取数据时，将version字段的值一起读出，数据每更新一次，version加一。当我们提交数据的时候，判断读出的version的值和数据库表中记录当前的版本比较，如果值相等，则可以更新，否则认为是过期数据。下面用一张图来说明：
• b.与第一种方式差不多，只不过version变成时间戳字段。

4.悲观锁

• a.查询记录时，会将当前记录锁住。

四.事务

事务是一组原子性的SQL查询，是最小的工作单元。事务内的语句要么全部执行成功，要么全部不执行。
ACID表示：

• 原子性（Atomicity）：事务是最小工作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：数据库总是从一个一致性状态转变到另一个一致性状态。
• 隔离性（Isolation）：一个事务所做的修改在最终提交之前，对于其它事务是不可见的。
• 持久性（Durability）：一旦事务提交成功，数据就会永久保存到数据库中。

1.隔离级别

查看数据库当前隔离级别：前面是全局，后面的当前会话

• a.read uncommitted（读未提交，也叫脏读）：事务之间是可见的。两个并发的事务，事务A读取了事务B未提交的数据，称之为脏读。 例子：小王今天发工资，老板转账4000到小王银行卡上，但是事务还没提交，小王一查，银行卡里有4000，赶紧去取钱，这是老板发现账务有问题，就回滚了事务，此时小王取钱时发现余额不足。
• b.read committed（读已提交，也叫不可重复读）：解决了脏读。但是无法解决不可重复读：两个并发的事务，事务A还没结束，事务B就先提交了数据，当事务A再次读取时，结果可能不一样。 例子：小王拿着银行卡去消费，查询时卡里有4000元，当小王刚准备付账时，他媳妇通过转账，将4000元全部转走，并且在小王支付之前提交了事务，此时小王支付时，发现余额不足，这就是不可重复读。
• c.repeatable read（可重复读）：解决了不可重复读（行级锁）。但是无法解决幻读：两个并发的事务，当事务A读取某个范围内的数据时，另个一事务又在该范围内插入了新数据，当之前的事务再次读取时，会出现幻行。mysql的默认隔离级别（InnoDB通过MVCC，也就是多版本并发控制解决了幻读问题）。 例子：比如张老师正在向电脑中录学生的成绩，而王老师正在查询并打印（查询并打印在同一事务）70分到90分之间的学生，在事务内查到了10个人，此时张老师刚好录了一个80分成绩的学生，并且立马提交了，此时王老师准备打印成绩而再次查询时，发现多了一个人，就好产生了幻觉一样。
• d.serializable（可串行化）：强制事务串行执行，也就是在读取的每一行上都加锁。可以解决脏读，不可重复读，幻读问题，但是开销非常大。

2.死锁

死锁是指多个事务在同一资源相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。死锁发生后，只有部分或者完全回滚其中一个事务，才能打破死锁，大多数情况下只需要重新执行因死锁回滚的事务即可。
如果事务1和2都执行了第一条语句，同时也锁定了更新的行，此时再执行第二条语句，但是发现该行已经被对方锁定，然后两个事务都等待对方释放锁，同时两个事务又持有对方需要的锁，这是就会陷入死循环。
解决方式：

• a.InnoDB将持有最少行级写锁的事物进行回滚，这也是比较简单的死锁回滚算法。
• b.当查询的时间达到锁等待超时的设定后放弃锁请求。

3.事务日志

• a.使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久化硬盘上的事务日志中，而不用每次都将修改的数据持久化到硬盘上。
• b.因为事务采用的追加的方式，因此写日志的操作是磁盘上一小块区域的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁盘，所以速度会很多。
• c.事务日志持久化之后，内存中被修改的数据在后台慢慢被刷回磁盘中。因此修改数据要写两次磁盘。
• d.mysql会最大程度的使用缓存机制来提高数据库的访问效率，即使数据库崩溃，内存中的数据来不及写入磁盘而丢失了，这时数据库重启的时候会用事务日志来恢复数据。

4.mysql事务

• a.自动提交：mysql默认采用自动提交模式（AUTOCOMMIT）。也就是说，每一条查询都被当做一个事务提交，ON表示打开，OFF表示关闭。
• 
• b.还有一些命令，会在执行之前强行提交当前的活动事务，例如数据定义语言（DDL），ALTER TABLE等等。

五.存储引擎

在文件系统中，MySQL将每个数据库（也可以称之为schema）保存为数据目录下的一个子目录。

InnoDB

• a.支持事务，支持行级锁，支持非锁定读，外键，主要应用于联机事务处理OLTP，5.5.8版本开始是默认引擎

• b.将数据放在一个逻辑的表空间中，将表数据（包含索引）存放在ibd文件中，表结构存放在frm文件中，db.opt用来记录该库的默认字符集编码和字符集排序规则。例如新建了一个数据库test，再新建表t和m，则datadir目录：

• c.通过MVCC（多版本并发控制）来实现高并发，并且实现了SQL标准四个隔离级别，默认是REPEATABLE（可重复读），通过Next-key解决了幻读，还提供了插入缓冲（insert buffer），二次写（double write），自适应哈希索引（adaptive hash index），预读（read ahead）等高性能的功能。

• d.采用聚集索引方式，每张表都是按主键顺序存储的，如果没有显示主键，InnoDB会选一个unique列来当主键，如果没有unique列，则InnoDB会生成一个隐藏RowID来当主键。

MyISAM

• 不支持事务，支持表级锁，支持全文索引，主要应用OLAP，只缓存索引文件。

Memory

• 所有数据都放在内存中，使用哈希索引