高性能mysql第一章——架构
第一章 架构
1.1 mysql逻辑架构
mysql服务器逻辑架构图如上图所示。
第一层为连接/线程处理层。每个客户端连接mysql服务器，都会拥有一个线程。服务器会缓存线程，因此无需为每一个连接新建或释放线程。mysql5.5以上的版本还提供了线程池，可以用少量线程服务大量连接。当客户端连接到服务器时，服务器需要对其认证，根据用户名主机名密码等信息，确定客户端是否有查询/更新某个数据库内某张表的权限。
第二层为mysql的核心服务功能层。包括查询解析、分析、优化、缓存以及所有内置函数（日期、时间等），所有跨存储引擎的功能 都在这一层实现（视图、存储过程、触发器等）。
第三层包含了存储引擎。存储引擎负责数据的存储和提取，每个存储引擎都有它的优势与劣势，服务器通过API与存储引擎通信。存储引擎API包含几十个底层函数，用于执行“开始一个事务”等操作，但是不会解析sql。
1.2 并发控制
讨论mysql两个层面的并发控制：存储引擎层 与 服务器层
1.2.1 读写锁
在处理并发写或并发读写时，可以通过由两种类型的锁组成的锁系统来解决问题，这两种锁就是 共享锁（读锁） 和 排它锁（写锁）。读锁是共享的，互相不阻塞，写锁是排它的，只有一个线程能进行写操作，其他读锁和写锁都是阻塞的。
并且，写锁拥有更高的优先级。在一个锁队列中，写锁可以插到读锁的前面。
1.2.2 锁粒度
一种理想的锁方式是，尽量只锁定需要修改的资源，而不是所有资源。锁定的数据量越小，并发程度越高。
但是锁也是需要时空开销的，判断是否有锁、加锁、释放锁的操作都需要额外的开销，如果锁粒度太小，虽然并发程度高，但系统花大量资源去管理锁，而不是存取数据，也是得不偿失。
锁策略 就是在 锁的开销 和 数据安全性 之间寻求平衡。大多数据库都是行级锁 ，而mysql提供了更多锁的可能性。每种存储引擎都可以实现自己的锁策略和锁粒度。将锁粒度固定在某一级别，可以为特定的应用场景提供更好的性能，但同时也会失去对一些应用场景的支持。但好在mysql支持多个存储引擎。
表锁
表锁是mysql中最基本、开销最小的锁策略。尽管存储引擎可以设计管理自己的锁，但mysql服务器还是利用表锁来实现不同的目的。例如：会为alter table之类的语句使用表锁，而忽略存储引擎的锁机制。
行级锁
行级锁是mysql中支持并发量最大、开销最大的锁策略。行级锁只在存储引擎层实现，服务器层没有实现。并且服务器层完全不了解存储引擎层的锁实现。
1.3 事务
事务是一组原子性的操作，是一个独立执行单元。事务内的语句，要么全部执行成功，要么全部执行失败。
start transaction
sql1
sql2
sql3
commit
用start transaction开始一个事务，三条sql语句，要么全部执行成功commit上去将结果永远保留，要么roll back撤销所有修改。
事务的ACID
一个良好的事务处理系统，必须满足ACID，否则任何事情都有可能发生。比如运行到一半程序崩掉、运行时其他事务也在运行并和当前事务操作同一条数据……
原子性(atomicity): 要么全部执行成功，要么全部执行失败。
一致性(consistency): 数据库总是从一个一致性状态，转移到另一个一致性状态中去。
隔离性(isolation): 通常，一个事务在commit之前，所做的修改对其他事务而言是不可见的。
持久性(durability): 一旦事务提交，则会永久保留到数据库内。
一个数据库系统想要实现ACID，需要做许多复杂的工作，像锁粒度升级会增加系统开销一样，事务处理过程中的安全性的保证，需要更强的cpu处理能力、更大的内存和磁盘。但我们可自行选择，如果不需要事务，可以使用非事务型的存储引擎。
1.3.1 隔离级别
在sql标准中定义了四种隔离级别。每一种都规定了，在事务中的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离，意味着更好的并发性和较小的开销。
未提交读: 事务还未提交，其他事务就可见到更改。这种情况非常危险，不推荐使用。
提交读: 一个事务从开始到提交结束，其中的修改仅事务本身可见。大多数据库系统的默认隔离级别都是提交读(mysql不是)。提交读又称作不可重复读，即同一事务中两次读的结果可能是不一样的。
可重复读: 此级别保证了同一事务中多次读的结果是相同的。可重复读是mysql的默认隔离级别。但可重复读依然存在问题，即在这个事务执行时，其他事务在该范围内插入了新数据。
可串行化: 这是最高级别的隔离，会在读写的每一行数据上都加锁，可能会导致大量超时和锁争用的问题。
1.3.2 死锁
当两个或多个事物占用着自己的资源，而都在等待对方占用的资源时，会形成死锁。
start transaction
update a
update b
commit
start transaction
update b
update a
commit
如果凑巧，两个事务都执行了第一条语句，在等待第二条语句的执行，那么就会形成死锁。为了解决这种问题，数据库系统引入了大量的死锁检测和死锁超时机制。
一种是在事务开始前检测死锁的循环依赖，若有可能导致死锁，则报错。
一种是发生死锁时，设置最长等待时间，大于这个时间则放弃资源（不推荐，性能变差）。
一种是发生死锁时，将拥有最少行级写锁的事务回滚。
1.3.3 mysql中的事务
mysql提供了两种事务型存储引擎：InnoDB、NDB Cluster。
mysql5.5.5之后，InnDB为mysql的默认存储引擎。
1. 自动提交 autocommit
mysql默认采用自动提交模式。如果不是显示的开启一个事务，任何查询都会被当作一个事务。在当前连接中，可以通过设置autocommit变量开启/禁用自动模式。
当autocommit为off时，所有的查询语句都是在一个事务中，直到遇到commit或rollback命令，该事务结束，同时开启下一个事务。
有一些mysql命令，在执行之前会强行commit当前的活动事务。例如alter table、lock tables等，具体要看mysql版本。
2. 混合使用存储引擎
mysql服务器层是不管事务，事务是由下层的存储引擎层实现的。所以在同一事务中，使用多种存储引擎是不可靠的。如果在事务中，混合使用了事务型的表与非事务型的表（InnoDB表和MyISAM表），正常情况不会有什么问题，但当需要roll back时，非事务型的表是不可回滚的。
1.4 多版本并发控制
mysql多数事务型存储引擎实现的都不是简单的行级锁，而是引入了多版本并发控制(MVCC) 用于 提高并发量，但每种存储引擎的实现方式不同。
MVCC是行级锁的变种，但它在很多情况下避免了加锁的操作，因此开销更低。虽然实现机制各不相同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
以InnoDB的MVCC为例，InnoDB以 在每行记录后面保存两个隐藏的列 来实现MVCC。这两个列，一列保存 这一行的创建时间，一列保存 这一行的过期时间。但存储的并不是真正的时间，而是 系统版本号。每开始一个事务，系统版本号就会自增。事务通过自身的系统版本号与这两个隐藏的列对比，来操作数据。
在可重复度的隔离级别下，InnoDB的MVCC的具体操作如下：
select
InnoDB查找 创建系统版本号 <= 事务系统版本号的行，即在事务开始前就存在的行，或者由事务自身插入或修改的行。
同时该行的过期系统版本号要么未定义，要么 > 事务系统版本号。
insert
新插入的每一行 创建时间都是当前事务的系统版本号。
delete
删除的每一行 过期时间都是当前事务的系统版本号。
update
插入一条新数据，创建时间是当前事务的系统版本号，将原来行的过期时间置为当前事务的系统版本号。
这两个额外的系统版本号，使得大多数读操作都不用加锁。不足之处是需要额外的存储空间，要进行更多的判断以及额外的维护工作。
MVCC只在 提交读 和 可重复度 两个隔离级别下工作，因为未提交读总是能读取到最新的行，而不是符合当前事务版本的行，而串行化会对所有行加锁。
1.5 mysql存储引擎
文件系统中，mysql将每个数据库(schema)保存为数据目录下的一个子目录。创建表时，mysql会在数据库子目录下创建一个和表同名的 frm后缀文件保存表的定义。因为mysql使用文件系统的目录和文件来保存数据库和表的结构定义，大小写敏感和操作系统密切相关。
可以看到，该文件存储着表名、存储引擎、表行数、创建时间、更新时间、所用字符集等等一些基础信息。