高性能MySQL 学习笔记 第一章 MySQL架构与历史

第一章 MySQL架构与历史

1.1 MySQL逻辑架构

MySQL服务器逻辑架构图

最上层，大多数基于网络的客户端/服务器的工具或服务都由类似的架构。如 连接处理、授权认证、安全等。
第二层架构是MySQL的核心服务功能，包括查询解析、分析、优化、缓存以及所有的内置函数（如 日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
第三层包含存储引擎。负责数据的存储和提取。服务器通过API与存储引擎进行通信。不同存储引擎之间不会相互通信，只是简单的响应上层服务器的请求。

1.1.1 连接管理与安全性

当客户端（应用）连接到MySQL服务器时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。

1.1.2 优化与执行

MySQL会解析查询，并创建内部数据结构（解析树），后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示（hint）优化器，影响他的决策过程。也可请求优化器解释（explain）优化过程的各个因素，实用化可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行。

1.2 并发控制

MySQL在两个层面的并发控制：服务器层与存储引擎层。
MySQL如何控制并发读写。

1.2.1 读写锁

处理并发读或写时，可以通过实现一个有两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁（shared lock）和排它锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。
读锁是共享的，或者说是互相不阻塞的。多个用户在同一时刻可以同时读取同一个资源，而互不干扰。写锁是排他的，即是说一个写锁会阻塞其他的写锁和读锁。

1.2.2 锁粒度

一种提高共享资源并发性的方式就是让锁定对象更有选择性。
锁策略就是在锁的开销和数据的安全性之间寻求平衡，这种平衡会影响性能。

表锁（table lock）
最基本的锁策略，且是开销最小的策略。会锁定整张表。一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。
写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面。
存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同目的。如，服务器会为alter table之类的语句使用表锁，而忽略存储引擎的锁机制。

行级锁（row lock）
行级锁可以最大程度的支持并发处理（同时带来最大的锁开销）。行级锁只在存储引擎层实现，MySQL服务器层没有实现。

1.3 事务

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。事务内的语句，要么全部执行成功，要么全部执行失败。
银行应用是解释事务必要性的一个经典例子。假设一个银行的数据库有两张表：支票（checking）表和储蓄（savings）表。现从 用户Jane支票账户转移200元到储蓄账户，至少需要三个步骤：
1.检查支票账户余额高于200元
2.从支票账户余额减去200元
3. 在储蓄账户余额增加200元
上述操作必须打包在一个事务中。任一步骤失败，必须回滚所有的步骤。
可以用 start transaction 语句开始一个事务，然后要么使用 commit 提交事务将修改的数据持久保留，要么使用rollback 撤销所有的修改。事务SQL样本如下：

1  start transaction;
2  select balance from checking where customer_id = 10233244;
3  update checking set balance = balance - 200.00 where customer_id = 10233244;
4  update savings set balance = balance + 200.00 where customer_id = 10233244;
5  commit;

一个运行良好的事务处理系统，必须具备这些标准特征。ACID：原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。
原子性：一个事务必须被视为一个不可分割的最小工作单元，对于一个事务来说，不可能只执行其中一部分操作，这就是事务的原子性。
一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性：一旦事务提交，其所做的修改就会永久保存在数据库中。

1.3.1 隔离级别

SQL标准中定义了四种隔离级别。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

read uncommitted（未提交读）
事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，也被称为脏读（dirty read）。实际应用很少。

read committed（提交读）
一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。nonrepeatable read

repeatable read（可重复读）
解决了脏读的问题。保证了在同一个事务中多次读取同样记录的结果是一致的。但理论上，可重复读隔离级别还是无法解决另外一个幻读（phantom read）的问题。幻读，指当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新记录，当之前的事务再次读取该范围的记录时，会产生幻行（phantom row）。
可重复读是MySQL的默认事务隔离级别。

serializable（可串行化）
最高的隔离级别。通过强制事务串行执行，避免了前面说的幻读的问题。serializable会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。只有在非常需要确保数据的一致性且可以接受没有并发的情况下，才考虑采用。实际很少用到。

ANSI SQL 隔离级别

隔离级别         脏读可能性  不可重复读可能性 幻读可能性  加锁读
read uncommitted  Y           Y                Y          N
read committed    N           Y                Y          N
repeatable read   N           N                Y          N
serializable      N           N                N          Y

1.3.2 死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。
InnoDB处理死锁的方法是，将持有最少行级排他锁的事务进行回滚（这是相对较简单的死锁回滚算法）。
锁的行为和顺序是和存储引擎相关的。
死锁的产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，有些则完全是由于存储引擎的实现方式导致的。
死锁发生后，只有部分或完全回滚其中一个事务，才能打破死锁。对于事务型的系统，这是无法避免的。
mostly，只需重新执行因死锁回滚的事务即可。

1.3.3 事务日志

可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头。
事务日志持久以后，内存中被修改的数据在后台可以慢慢刷回到磁盘。目前大多数存储引擎都是这样实现的，通常称之为预写式日志（write-ahead logging），修改数据需要写两次磁盘。
若数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能自动恢复这部分修改的数据。

1.3.4 MySQL中的事务

提供两种事务型的存储引擎：InnodB和NDB Cluster。

自动提交（AUTOCOMMIT）
MySQL默认采用自动提交（autocommit）模式。若不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。可通过设置autocommit变量来启用或禁用自动提交模式：

1或者ON表示启用，0或OFF表示禁用。当autocommit=0时，所有的查询都是在一个事务中，直到显示地执行 commit 提交或者 rollback 回滚，该事务结束，同时又开始另一个新事务。修改autocommit对非事务型的表，不会有任何影响。
有些命令，在执行之前会强制执行commit提交当前的活动事务。

MySQL可通过执行 set transaction isolation level命令来设置隔离级别。新的隔离级别会在下一个事务开始时生效。可在配置文件中设置整个数据库的隔离级别，也可指该表当前会话的隔离级别：

在事务中混合使用存储引擎
事务由下层的存储引擎实现。同一个事务中，使用多种存储引擎是不可靠的。

隐式和显式锁定
InnoDB采用的是两阶段锁定协议（two-phase locking protocol）。
事务执行过程，随时都可以执行锁定，锁只有在执行 commit 或 rollback 时才会释放，且所有锁都是在同一时刻被释放。这都是隐式锁定，InnoDB会根据隔离级别在需要时自动加锁。
InnoDB也支持通过特定语句进行显式锁定：

• select … lock in share mode
• select … for update

MySQL也支持 lock tables 和 unlock tables 语句，在服务器层实现。不能代替事务处理。
除事务中禁用了 autocommit ，可以使用 lock tables 之外，其他任何时候都不要显式地执行 lock tables。

1.4 多版本并发控制

MySQL多数事务型存储引擎一般都同时实现了多版本并发控制（MVCC）。可以认为MVCC是行级锁的一个变种，但很多情况下避免了加锁操作，开销更低。大都实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能不一样。
不同存储引擎的MVCC实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。

1.5 MySQL的存储引擎

…