本章将学习MySQL的一下几个内容
本章主要介绍MySQL的服务器架构、各种存储引起之间的主要区别，区别的重要性，回顾一下MySQL的历史背景和基准测试
MySQL逻辑架构
连接管理与安全性
优化与执行
并发控制
读写锁
锁粒度
事务
隔离级别
死锁
事务日志
MySQL中的事务
多版本并发控制
MySQL的存储引擎
InnoDB存储引擎
MyISAM存储引擎
MySQL时间线(Timeline)
MySQL的开发模式
本章简介
MySQL逻辑架构
MySQL最重要、最与众不同的特性：
-
存储引擎架构
-
查询处理(Query Processing)
-
其他任务系统(Server Task)
-
数据的存储/提取
-
处理和存储分离的设计可以在是使用是根据性能、特性，以及其他需求来选择数据存储的方式
MySQL服务器逻辑架构图
如图，把MySQL的逻辑架构分为了三层：
最上层：大多数的网络C/S工具或服务都有的类似的架构---可以理解为C/S架构
MySQL的核心服务层：---这一层包括了大多数MySQL的核心服务功能
-
查询解析
-
分析
-
优化
-
缓存
-
跨存储引擎
-
存储过程
-
触发器
-
视图等
-
存储引擎---负责MySQL中的数据存储和提取
-
服务器通过API与存储引擎进行通信
-
API屏蔽不同存储引擎之间的差异，使得这些差异对上层的查询过程透明
-
存储引擎API包含几十个底层函数，但存储引擎不回去解析SQL(InnoDB会解析外键定义)---如"开始一个事务"or"根据主键提取一行记录"
-
不同存储引擎之间不会互相通信，只是简单地响应上层服务器的请求
一、MySQL连接管理与安全性
如上图，分析客户端连接MySQL服务的C端特点：
-
每个C端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在单独的线程中执行。
-
该线程只能轮流在某个CPU核心或者CPU中运行
-
服务器会负责缓存线程，不需要为每一个新建的连接创建或者注销线程---MySQL5.5或者更新的版本提供了API，支持线程床(Thread-Pooling)插件，可以使用池中少量线程来服务大量连接
-
C端连接到MySQL服务器时服务器需要对其进行认证。基于用户名、原始主机信息、密码
-
C端连接成功服务器会继续验证该C端是否具有执行某个特定查询的权限
二、优化与执行
如上图分析MySQL核心服务功能层
-
一个查询动作MySQL会做的事情：
-
解析查询
-
创建内部数据结构(解析树)
-
对查询进行优化
-
重写查询
-
决定表的读取顺序
-
选择合适的索引
-
...
-
-
用户通过特殊的关键字提示(hint)优化器，影响它的决策过程---where子句，也可以请求优化器解释(explain)优化过程的各个因素
-
-
优化器不关心表使用什么存储引擎，但存储引起对于优化查询有影响。优化器会请求存储引擎提供容量或某个具体操作的开销信息
-
select语句在解析查询前服务器会先检查缓存(Query Cache)，如果能够在其中找到对应的查询，服务器就不必在执行查询解析、优化和执行的整个过程而是直接返回结果集。
并发控制
背景：多个查询在同一时刻修改数据，都会产生并发控制的问题。
本章讨论服务器在两个层面的并发控制：
-
服务器层
-
存储引擎层
先来介绍为什么需要并发控制。
概念一：Unix系统的email box中的mbox文件格式是很简单的。一个mbox邮箱中的所有邮件都串行在一起，彼此首尾相连。投递邮件在文件末尾附加新邮件即可---mbox的文件读取和分析模式
概念二：两个进程在同一时刻对同一个邮箱进行投递邮件邮箱的数据就会被破坏，两封邮件的内容会交叉地附加在邮箱文件的末尾。---这个时候就会通过锁(lock)来防止数据损坏。邮箱被锁住就要等到他被释放另一个用户才能对他进行投递
概念三："锁"的概念不支持并发，任意时刻只有一个进程可以修改邮箱的数据，在大容量的邮箱系统中就是个问题
把邮箱变成数据库中的表，邮件变成表中记录的数据就出现了这样的情况，当一个人在读取数据同时另一个人在修改数据，那么表反应出来的数据并不是实时的这个时候应该怎么处理？
解决上述问题的办法就是并发控制。
一、读写锁
细节如图：
大多数时候MySQL锁的内部管理都是透明的
二、锁粒度
概念：
让锁定的对象更具选择性。尽量只锁定需要修改的部分数据而不是所有的资源。
方法：
只对会修改的数据片进行精确的锁定。---锁定的数据量越少，系统的并发程度越高，相互间不发生冲突即可。
一个"锁"动作的完整过程：
-
获得锁
-
检查锁是否已经解除
-
释放锁
-
...
这些步骤会增加系统开销，系统花大量的时间来管理锁那么就会影响系统的性能。
"锁"的策略就是在锁的开销和数据的安全性之间寻求平衡。不同的MySQL存储引擎有自己的锁策略和锁粒度。---锁粒度固定在某个级别可以为特定的应用场景提供更好的性能，同时会失去对另一些应用场景的良好支持。但是MySQL支持多个存储引擎的架构就能解决这一的情况。
MySQL中的两种"锁策略"
1.表锁(table lock)
概念：
-
MySQL中最基本的锁策略
-
开销最小的策略
过程描述：
用户在对表进行写操作(插入、删除、更新等)前，需要先获得写锁，这个动作会阻塞其他用户对该表的所有读写操作(锁定整张表)。读锁之间不互相阻塞
特点：
-
特定场景中，表锁也可能有良好的性能。---read local表锁支持某些类型的并发写操作。
-
写锁比读锁有更高的优先级，(写锁请求可以插入到读锁请求前面，读写请求不会插入到写锁请求之前)
-
两层机制：
-
MySQL存储引擎自身可以管理自己的锁，会使用各种有效的表锁来实现不同的目的
-
-
服务器会为alter table之类的语句使用表锁而忽略存储引擎的锁机制
-
2.行级锁(row lock)
优点：
可以最大限度地支持并发处理(同时也带来了最大的锁开销)
特点：
行级锁只在存储引擎层实现，MySQL服务层没有实现。服务层完全不了解存储引擎中的锁实现。---所有引擎都已自己的方式实现了锁的机制
死锁
概念：
两个或多个资源在同一资源上互相占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
条件：
-
两个或多个事务占用同一资源
-
请求锁定对方占用的资源
情况：
-
多个事务试图以不同的顺序锁定资源时，就可能产生死锁。
-
多个事务同时锁定一个资源时，也会产生死锁。
真正产生死锁的原因
-
真正的数据冲突
-
存储引擎的实现方式导致的
For example
对于StockPrice表：
事务一：
start transaction;
update StockPrice set close = 45.50 where stock_id = 4 and date = '2002-05-01';
update StockPrice set close = 19.80 where stock_id = 3 and date = '2002-05-02';
commit;
事务二：
start transaction;
update StockPrice set high = 20.12 where stock_id = 3 and date = '2002-05-02';
update StockPrice set high = 47.20 where stock_id = 4 and date ='2002-05-01';
commit;
两个事务都执行了第一条update语句就更新了一行数据也锁定了该行数据。如果每个事务去执行第二行update语句语句的时候就都会等待释放锁同时又互相持有对方需要的锁，陷入死循环。---解决办法：外部因素介入，属于情况一：多个事务试图以不同顺序锁定资源产生的死锁。
存储引擎InnoDB解决死锁的方式：将持有最少行级排他锁的数据进行回滚(简单的死锁回滚算法)
锁行为和顺序和存储引擎有关
解决死锁的办法：
-
重新执行因死锁回滚的事务
事务
概念：
一组原子性的SQL查询，一个独立的工作单元
特点：
-
数据库引擎能够成功地对数据库应用该组查询地全部语句，那么就执行改组查询。
-
如果有任何一条语句因为crash或者其他原因无法执行那么所有语句都不会执行
事务内的语句要么全部执行成功要么全部执行失败
for example
银行数据库有两张表：支票表(checking)和储蓄表(savings)，现在需要从支票账户转200美元到储蓄账户需要经过的步骤：
-
检查支票账户余额高于200美元
-
从支票账户余额中减去200美元
-
在储蓄账户余额中增加200美元
上述三个步骤构成一个完整的动作，必须打包在一个事务当中，任何一个步骤失败必须回滚所有步骤
用start transaction开始一个事务，用commit提交事务将修改的数据持久保存，或者用rollback撤销所有的修改
语句：
start transaction(n.事务,交易);
select balance from checking where customer_id =...;
update checking set balance = balance - 200 where custormer_id =...;
update savings set balance = balance + 200 where custormer_id =...;
commit;
单纯知道事务依旧不能解决突发情况such as：
-
执行到第四条的时候服务器crash了
-
执行到第三条到第四条语句之间的时候另一个进程要删除支票账户的所有余额
-
...
这个时候就需要系统经过严格的ACID测试
什么是ACID？
原子性(atomicity)
概念
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
一致性(consistency)
数据库总是从一个一致性的状态转换到另一个一致性的状态。前面的原子性保证了一致性，即便遇到服务器crash或者是突然加入进程的情况也不会导致金钱的损失
隔离性(isolation)
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可以见的。---这里注意"通常来说"，后面会讲到隔离级别(isolation lever)就会知道为什么说"通常来说"了
持久性(durability)
一旦事务提交，则其所做的修改就会永久保存到数据库。系统crash数据也不会丢失。持久性也分不同的级别。
事务的ACID特征在显示中基本不可能完成。一个兼容ACID的数据库系统需要做很多复杂但可能用户并没有觉察到的工作才能确保ACID的实现。
正因为MySQL的存储引擎架构让用户可以根据业务是否需要事务处理来选择合适的引擎。
对于一些不需要事务的查询类应用，可以选择一个非事务型的存储引擎，可以获得更高的性能。
即使存储引擎不支持事务，也可以用lock tables语句为应用提供一定程度的保护。
隔离级别
四种隔离级别
-
read uncommited(未提交读)
特点：
-
事务中的修改即使没有提交对其他事务也都是可见的。
-
事务可以读取未提交的数据(脏读(dirty read))
-
read commited(提交读)
特点：
一个事务从开始直到提交之前所做的任何修改对其他事务都是不可兼得。---不可重复读(nonrepeatable read)---两次执行同样的查询就会得到不一样的结果
-
repeatable read(可重复读)---MySQL默认的事务隔离级别
特点：
-
保证了同一个事务中多次读取同样的记录结果是一致的
-
无法解决幻读(phantom read)的问题
-
幻读
-
定义：定义：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取改范围的记录时会产生幻行(phantom row)
-
-
-
serializaable(可串行的)
特点：
最高的隔离级别，通过强制事务串行执行，避免了幻读。
具体实现方法：
在读取的每一行数据上都加上锁---很少用，级别最高
总结：
1. *read uncommitted 可脏读 不可重复读 可幻读 不可加锁读*
2. *read commited 不可脏读 可重复读 可幻读 不可加锁读*
3. *repeatable read 不可脏读 不可重复读 可幻读 不可加锁读*
4. *serializable 不可脏读 不可重复读 不可幻读 可加锁读*
事务日志
概念：
使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，不用每次都将修改的数据本身持久到磁盘。
关键点：
记录的是修改行为不是修改的数据
特点：
-
事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，所以事务日志会快得多
-
事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。
我们称这样地方式为"预写式日志(Write-Ahead Logging)"，修改数据需要写两次磁盘。
-
第一次记录行为
-
第二次慢慢回刷记录数据---这个时候如果数据未写入磁盘系统crash了存储引擎重启时能够恢复这部分修改的数据。具体恢复方式视存储引擎而定
MySQL中的事务
MySQL提供的两种事务型存储引擎
-
InnoDB
-
NDB Cluster
特点：
-
自动提交(autocommit)
MySQL默认采用自动提交模式，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。---"显式地"是什么意思？
autocommit状态：
-
1或者ON表示启用
-
0或者OFF标识禁用---所有查询都是在一个事务中，结束标志：commit提交或者rollback回滚，又开始另一个新事务。
修改autocommit对非事务型的表不会有任何影响，这类表没有commit或者rollback的概念，一直处于autocommit启用的模式
在事务中混合使用存储引擎
MySQL服务层不管理事务，事务式由下层的存储引擎实现的。在同一事务中使用多种存储引擎式不可靠的。
个人理解这句话的意思是指在连表查询当中由于事务混合查询了事务型和非事务型表---因为每张表的存储引擎不一样
显式和隐式锁定
特点：
-
InnoDB采用的是两阶段锁定协议(two-pash locking protocol)，事务执行过程中随时都可以执行锁定，锁只有在执行commit或rollback的时候才会被释放，并且是同一时刻被释放。
-
InnoDB会根据隔离级别在需要的时候自动加锁
-
InnoDB支持通过特定的语句进行显示锁定，这些语句不属于SQL规范
such as
-
select ... lock in share mode
-
select ... for update
-
MySQL也支持lock tables和unlock tables语句
这是在服务层实现的，和存储引擎无关---不能代替事务处理
任何时候不要显示的执行lock tables语句，不管使用什么存储引擎
多版本并发控制
概念：
行级锁的变种，很多情况下避免了加锁操作，开销更低。
特点：
-
非阻塞的读操作
-
写操作只锁定了必要行
实现原理：
-
通过保存数据在某个时间点的快照来实现。
-
不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，事务同一时刻看到的同一张表的的数据可能不同。
典型类型
-
乐观(optimistic)并发控制
-
悲观(pessimistic)并发控制
下面以InnoDB来举例
实现原理：
InnoDB的MVCC通过在每行记录后面保留两个隐藏的列来实现的。
一个列保存了行的创建时间，一个列保存的行的过期(或删除)时间
存储是系统的版本号(System version number)
特点：
-
每开始一个新事务系统版本号都会自动递增
-
事务开始时刻系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较
下面讨论InnoDB存储引擎的一些语句的MVCC具体是如何操作的
-
select
InnoDB会根据以下两个条件检查每行记录
-
InnoDB只查找版本早于当前事务版本的数据行(要么在事务开始前已经存在，要么是事务自身插入或者修改过的)
-
行的删除版本要么未定义，要么大于当前事务版本号。---确保事务读取道德行，在事务开始之前没被删除
只有符合上诉两个条件的记录，才能返回作为查询结果
-
-
insert
-
InnoDB为新插入的每一行保存当前系统版本号作为行版本号
-
-
delete
-
InnoDB为删除的每一行保存当前系统版本号作为行删除标识
-
-
update
-
InnoDB为插入一行新纪录保存当前系统版本号作为行版本号
-
保存当前系统版本号到原来的行作为行删除标识
-
这样做的好处与不足以及兼容的隔离级别
-
保存这两个额外系统版本号是大多数读操作都可以不用加锁。
-
不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作以及额外的维护工作。
-
MVCC只在repeatable read和read committed两个隔离级别下工作。(只兼容这两个隔离级别)
MySQL的存储引擎
概念：
MySQL使用使用文件系统的目录和文件来保存数据库和表的定义
MySQL将每个数据库(schema)保存为数据目录下的一个子目录。
创建表时MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。
具体如图所示
show table status like '表名';
各字段信息：
name
表名。
Engine
表的存储引擎类型。旧版本中是type
row_format
行的格式
rows
表中的行数。MyISAM和其他一些存储引擎，该值是精确的。InnoDB中该值是估计值
Avg_row_length
平均每行包含的字节数
Data_length
表数据的大小(以字节为单位)
Max_data_length
表数据的最大容量，该值和存储引擎有关
Index_length
索引的大小(以字节为单位)
Data_free
之前删除的行，后续可以被Insert利用到的行
Auto_increment
下一个Auto_increment的值
Create_time
表的创建时间
Update_time
表数据的最后修改时间
Check_time
使用Ckeck table命令查看最后一次检查表的时间
Collation
表的默认字符集和字符列排序规则
Checksum
启用，保存整个表的实时校验和
Create_options
创建表时所指定的其他选项
Comment
额外信息。
实际情况如下图：
