MySQL架构概述
MySQL 最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理（Query Processing）及其他系统任务（Server Task）和数据的存储/提取相分离。本文概要地描述了MySQL的服务器架构以及各存储引擎之间的主要区别。
MySQL逻辑架构
下图展示了MySQL的逻辑架构图。
MySQL的逻辑架构分为三层。
第一层: 最上层的服务并不是MySQL所独有，大多数基于网络的客户端/服务器的工具或者服务都有类似架构。比如连接处理、授权认证、安全等。
第二层: 大多数MySQL的核心服务都在，包括查询、分析、优化、缓存以及所有的内置函数，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图。
第三层: 包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API与存储引擎进行通信，这些接口屏蔽了存储引擎间的差异性，使得这些差异对上层的查询过程透明。存储引擎不会去解析SQL（除InnoDB会解析外键之外），不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求。
连接管理与安全性
每一个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行。服务器会缓存线程，因此不需要为每个新建的连接创建和销毁线程。
但客户端连接到MySQL服务器时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。
优化与执行
MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。
优化器不关心表使用的哪种存储引擎，但存储引擎对于优化查询是有影响的。
对于SELECT语句，在查询解析之前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析
并发控制
读写锁
- 共享锁 读锁 非阻塞
- 排他锁 写锁 阻塞
写锁比读锁有更高的优先级，可以插入到读锁队列的前面
锁粒度
锁策略：在锁的开销和数据的安全性之间寻求平衡
- 表锁 开销最小
- 行锁 最大并发度，最大锁开销
事务
事务是一组原子性的 SQL 查询，或者说一个独立的工作单元。事务内的语句要么全部执行成功，要么全部执行失败。
- START TRANSACTION 开始事务
- COMMIT 提交事务
- ROLLBACK 撤销事务
事务的特征ACID
- atomicity 原子性 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。
- consistency 一致性 数据库总是从一个一致性的状态转换到另外一个一致性的状态。
- isolation 隔离性 通常来说，一个事务所做的修改在最终提交前，对其它事务是不可见的。
- durability 持久性 一旦事务提交，则其所做的修改就会永久保存到数据库中
隔离级别
SQL标准定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。
- READ UNCOMMITED（未提交读）
事务中的修改即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这被称为脏读（Dirty Read）。
- READ COMMIT（提交读）
一个事务从开始到提交之前，所做的任何修改对其它事务都是不可见的。这个级别有时候也叫不可重复读（nonrepeateble read），因为两次执行相同的查询，可能会得到不一样的结果。
- REPEATABLE READ（可重复读）
解决了脏读和不可重复读，理论上，无法解决幻读的问题。
幻读（Phantom Read），是指当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取范围的记录时，会产生幻行。
InnoDB 和 XtraDB 存储引擎通过多版本并发控制解决了幻读的问题。
可重复读是 MySQL 的默认隔离级别
- SERIALIZABLE（可串行化）
SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行，避免前面说的幻读的问题。
死锁
死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定一个资源时，也会产生死锁。
InnoDB 目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。
死锁的产生有双重因素：有些是真正的数据冲突，有些是由于存储引擎的实现方式导致的。死锁发生后，只有部分或者完全回滚其中一个事务才能打破死锁。大多数情况下，只需重新执行因死锁回滚的事务即可。
事务日志
事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到硬盘。事务日志采用的是追加的方式，事务日志持久后，内存中被修改的数据在后台可以慢慢刷回到磁盘。通常称为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。
如果数据的修改已经记录到日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。
MySQL中的事务
MySQL 中提供了两种事务型的存储引擎：InnoDB 和 NDB Cluster。另外还有一些第三方的存储引擎，比如 XtraDB 和 PBXT。
MySQL 默认采用自动提交（AUTOCOMMIT）模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当做一个事务执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式。还有一些命令，在执行前强制执行 COMMIT 提交当前的活动事务。如A LTER TABLE、LOCK TABLES 等。MySQL 可以通过执行 SET TRANSACTION LEVEL 命令来设置隔离级别。MySQL能够识别所有的4个ANSI隔离级别，InnoDB 引擎也支持所有的隔离级别。
MySQL 服务器层不管理事务，事务是由下层的存储引擎实现的。所有在同一个事务中，使用多种存储引擎是不可靠的。
InnoDB 采用的两阶段锁定协议（two-phase locking protocol）。在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK 的时候才会释放，并且所有的锁是在同一时刻被释放。InnoDB 会根据隔离级别在需要的时候自动加锁。InnoDB 也支持通过特定的语句进行显示锁定，如SET … LOCK IN SHARE MODE等。
多版本并发控制（MVCC）
可以认为MVCC是行级锁的一个变种，但是在很多情况下避免了加锁操作，开销更低。
MVCC的实现，是通过保存数据在某一个时间点的快照来实现的。不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始时间的不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
不同存储引擎的 MVCC 实现不同，典型的有乐观并发控制和悲观并发控制。InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。存储的不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。保存这两个额外的系统版本号，使大多数读操作都可不用加锁。不足之处是每行记录都需要额外的存储空间。MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作。
存储引擎
InnoDB
MySQL默认的事务型引擎。
InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。
默认REPEATABLE READ，通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB 是基于聚簇索引建立的，对之间查询有很高的性能。它的二级索引（非主键索引）中必须包含主键列，如果主键列很大的话，其它所有索引都会很大。
从磁盘读取数据时采用可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index)，以及能够加速插入操作的插入缓冲区（insert buffer）等。
参见 官方手册《InnoDB事务模型和锁》
InnoDB可以通过一些机制和工具支持真正的热备份，Oracle的MySQL Enterprise Backup、Percona的XtraBackup可以做到这一点。MySQL的其它存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入。
MyISAM
MyISAM 支持全文本索引、压缩、空间函数（GIS）等。不支持事务和行级锁，崩溃后无法安全恢复。
MyISAM 将表存在两个文件中：数据文件（.MYD）索引文件（.MYI）。如果表是变长行，则默认配置只能处理256TB的数据。
特性
加锁与并发
对整张表而不是行加锁。但是在表有读取查询的同时，也可以往表中插入新的记录（并发插入，concurrent insert）
修复
CHECK TABLE mytabl 检查表的错误。
REPAIR TABLE mytable 进行修复。
当MySQL服务器关闭，可以用myisamchk命令行工具进行检查和修复。
索引
即使是 BLOB 和 TEXT 等长字段，也可以基于其前 500个 字符创建索引。MyISAM也支持全文本索引，是一种基于分词创建的索引，可以支持复杂的查询。
延迟更新索引键（Delay Key Write）
在创建 MyISAM 表的时候，如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区（in-memory key buffer），只有在清理键缓存区或者关闭表的时候才会将对应的索引块写入到磁盘，极大地提升了写入性能。
压缩表
如果表在创建并导入数据后，不会再进行修改操作，那么这样的或许适合采用MyISAM压缩表。可以使用 myisampack 对 MyISAM 表进行压缩。压缩表能极大减少磁盘空间占用和磁盘 I/O。
MySQL内建的其它存储引擎
Archive、Blackhole、CVS、Frederated、Memory、NDB 集群引擎
总结
MySQL 拥有分层的架构，上层是服务器层的服务和查询执行引擎，下层则是存储引擎。
MySQL 最初基于 ISAM 构建（后来被 MyISAM 取代），其后陆续添加了更多的存储引擎和事务支持。从 MySQL 5.5 起，InnoDB 成为默认的存储引擎。InnoDB 对绝大多数用户来说都是最佳选择。