本文系阅读《高性能MySQL》，Baron Schwartz等著一书中第一章 MySQL架构与历史的笔记，本章概要地描述了mysql的服务器架构、各种存储引擎之间的主要区别，以及这些区别的重要性。
mysql最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计是将查询处理（Query Processing）及其他系统任务（Server Task）和数据的存储/提取相分离。这种处理和存储
分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式。
mysql逻辑架构
mysql的逻辑架构图如下所示：
- 最上层的服务不是mysql所独有，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构，比如连接处理、授权认证、安全等
- 大多数mysql的核心服务功能都在第二层，包括查询解析、分析、优化、缓存以及所有的内置函数（如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等
- 第三层包含了存储引擎，存储引擎负责mysql中数据的存储和提取，每个存储引擎都有它的优势和劣势。服务器通过api与存储引擎进行通信，这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明
连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只会轮流在某个CPU核心或CPU核心中运行。mysql 5.5或者更新的版本提供了一个api，支持线程池插件，可以使用池中少量的线程来服务大量的连接。
并发控制
无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。本章讨论mysql在两个层面的并发控制：服务器层和存储引擎层。
读写锁
在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的所系统来解决问题，这两种类型的锁通常被称为共享锁（shared lock）和排它锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。
读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互补干扰。写锁则是排它的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样，才能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。
在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，mysql会通过锁定防止其他用户读取同一数据。大多数时候，mysql锁的内部管理都是透明的。
锁粒度
一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。
问题是加锁也需要消耗资源。锁的各种注册，包括获得锁、检查锁是否已经解除、释放锁等，都会增加系统的开销。如果系统话费大量的时间来管理锁，而不是存取数据，那么系统的性能可能会因此受到影响。
所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁（row-level lock），并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能的提供更好的性能。
表锁
表锁（table lock）是mysql中最基本的锁策略，并且是开销最小的策略。表锁非常类似于前文描述的邮箱加锁机制：它会锁定整张表。一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不可相互阻塞的。
行级锁
行级锁（row lock）可以最大程度的支持并发处理（同时也带来了最大的锁开销）。众所周知，在InnoDB和XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而mysql服务器层没有实现。
InnoDB行级锁是通过给索引上的索引项加锁来实现，在这一点上，mysql与oracle不同，oracle是通过在数据块中对相应数据行加锁来实现。mysql InnoDB的这种行级锁实现意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。
事务
事务就是一组原子性的sql查询，或者说是一个独立的工作单元。如果数据库引擎能够成功的对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。
ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability），一个运行良好的事务处理系统，必须具备这些标准特征
- 原子性，一个
事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作 - 一致性，数据库总是从一个一致性的状态转换到另外一个一致性的状态
- 隔离性，一个
事务所做的修改在最终提交之前，对其他事务是不可见的 - 持久性，一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失
隔离级别
隔离性比想象的要复杂，sql标准中定义了四种隔离级别，较低级别的隔离通常可以执行更高的并发，系统的开销也更低
- 未提交读（READ UNCOMMITTED），
事务中的修改即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这被称为脏读（Dirty Read），实际应用中很少使用未提交读 - 提交读（READ COMMITTED），一个
事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的，会造成不可重复读（non-repeatable read），因为两次执行同样的查询，可能会得到不一样的结果 - 可重复读（REPEATABLE READ），mysql的默认
事务隔离级别，解决了脏读、不可重复读的问题，保证了在同一个事务中多次读取同样记录的结果一致。但是理论上，可重复读无法解决幻读（Phantom Read）的问题，所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的纪录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。InnoDB存储引擎通过多版本并发控制解决了幻读的问题 - 可串行化（SERIALIZABLE），最高的隔离级别，它通过强制
事务串行执行，避免了幻读的问题。简单来说，可串行化会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题，在实际应用中很少使用
死锁
死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。
InnoDB目前处理死锁的方法是，将持有最少行级排它锁的事务进行回滚。
mysql中的事务
mysql提供了两种事务型的存储引擎：InnoDB和NDB Cluster，另外还有一些第三方的存储引擎也支持事务，比较知名的包括XtraDB和PBXT。
自动提交
mysql默认采用自动提交模式，如果不是显式的开始一个事务，则每个查询都被当作一个事务执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式。
mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SET AUTOCOMMIT = 1;
Query OK, 0 rows affected (0.00 sec)
1或者ON表示启用，0或者OFF表示禁用。当AUTOCOMMIT=0时，所有的查询都在一个事务中，直到显式的执行COMMIT提交或者ROLLBACK回滚，该事务结束，同时又开始了另一个新事务。
mysql可以设置隔离级别，新的隔离级别会在下一个事务开始时生效，可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别。
mysql> select @@global.tx_isolation; //查看系统级的隔离级别
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)
mysql> set global tx_isolation='READ-COMMITTED'; //修改系统级的隔离级别
Query OK, 0 rows affected (0.04 sec)
mysql> select @@global.tx_isolation; //查看系统级的隔离级别
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
1 row in set (0.00 sec)
mysql> select @@tx_isolation; //查看会话级的隔离级别
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> set session tx_isolation='read-uncommitted'; //修改会话级的隔离级别
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation; //查看会话级的隔离级别
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
mysql能够识别所有的4个ANSI隔离级别，InnoDB存储引擎也支持所有的隔离级别。
隐式和显式锁定
InnoDB采用的是两阶段锁定协议（two-phase locking protocol），在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放，前面描述的锁都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。另外，InnoDB也支持通过特定的语句进行显式锁定，这些语句不属于sql规范，应尽量避免使用，如下所示
SELECT ... LOCK IN SHARE MODE，在读取的行上设置一个共享锁，该共享锁允许其它会话读取行数据，但不允许修改。读取的是最新的行数据，如果行数据被其它还未提交的事务使用，共享锁将被阻塞直到其它事务结束SELECT ... FOR UPDATE，在读取的行上设置一个排它锁，阻止其他会话对行数据进行读、写
多版本并发控制
mysql的大多数事务型存储引擎实现的都不是简单的行级锁，基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC）。可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
不同存储引擎的MVCC实现是不同的，典型的有乐观（optimistic）并发控制/乐观锁和悲观（pessimistic）并发控制/悲观锁。
InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储并不是实际的时间值，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
MVCC只在可重复读和提交读两个隔离级别下工作，其他两个隔离级别都和MVCC不兼容，未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行，可串行化则会对所有读取的行都加锁。
悲观锁
悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去操作数据时都认为其它事务会修改数据，所以每次在操作数据时都会上锁，这样其它事务想操作这个数据就会阻塞，直到它释放锁，传统的关系型数据库就用到了这种锁机制，比如行锁、表锁、读锁、写锁等，都是在进行操作之前先上锁。
悲观锁主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于事务回滚成本的环境中。
乐观锁
乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去操作数据时都认为其它事务不会修改数据，所以不上锁，但是在更新时会判断在此期间其它事务有没有去更新此数据，可以通过版本号等方式实现。乐观锁适用于写入较少的情况，省去了锁的开销，提高了吞吐量。
mysql的存储引擎
因为mysql使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。在windows中，大小写是不敏感的；而在类unix中则是敏感的。
除非需要用到那些InnoDB不具备的特性，并且没有其他办法替代，否则都应该优先选择InnoDB引擎。