MySQL架构介绍
1. MySQL逻辑架构
- 最上层服务并不是
MySQL独有，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构，比如连接处理、授权认证、安全等； - 第二层架构是
MySQL比较有意思的部分。大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等； - 第三层包含了存储引擎。存储引擎负责
MySQL中数据的存储和提取。存储引擎多种多样，服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API包含了几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析SQL(InnoDB会解析外键定义 ) ，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求。
1.1 连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。在连接建立后，服务器会验证客户端是否具有执行某个特定查询的权限。
1.2 优化与执行
MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示优化器，影响它的决策过程。也可以请求优化器解释优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准。
对于 $SELECT$ 语句，在解析查询之前，服务器会先检查查询缓存 ( $Query\ \ Cache$ )，如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。
2. 并发控制
在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySQL会通过锁定防止其他用户读取统一数据。大多数时候，MySQL的内部管理都是透明的。
所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁 ( $row-level\ \ lock$ )，并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能地提供更好的性能。而MySQL则提供了多种选择。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。在存储引擎的设计中，锁管理是个非常重要的决定。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时却会失去对另外一些应用场景的良好支持。好在MySQL支持多个存储引擎的架构，所以不需要单一的通用解决方案。
- 表锁 ( $table\ \ lock$ )：表锁是
MySQL中最基本的锁策略，并且是开销最小的策略，它会锁定整张表。一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他的读取的用户才能获得读锁，读锁之间是不相互阻塞的。尽管存储引擎可以管理自己的锁，但MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为诸如 $ALTER\ \ TABLE$ 之类的语句使用表锁，而忽略存储引擎锁机制； - 行级锁 ( $row\ \ lock$ )：行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。众所周知，在
InnoDB和XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务器层没有实现。
3. 事务
事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。
- 原子性 ( $atomicity$ )：一个事务必须被视为一个不可分割的最小工作单元；
- 一致性 ( $consistency$ )：数据库总是从一个一致性的状态转换到另外一个一致性的状态；
- 隔离性 ( $isolation$ )：一个事务所做的修改在最终提交以前，对其他事务是不可见的；
- 持久性 ( $durability$ )：一旦事务提交，其所做的修改就会永久保存到数据库中。
一个运行良好的事务处理系统，必须满足ACID特性。但在应用逻辑中，要想实现这一点很困难，甚至可以说是不可能完成的任务。一个兼容ACID的数据库系统，需要做很多复杂但可能用户并没有觉察到的工作。
3.1 隔离级别
READ UNCOMMITTED( 未提交读 )：事务中的修改，即使没有提交，对其他事务也都是可见的。事务读取未提交的数据称为脏读 ( $Dirty\ \ Read$ )；READ COMMITTED( 提交读 )：大多数数据库系统的默认隔离级别都是提交读，但MySQL不是。一个事务开始时，只能看到已经提交的事务所做的修改。这个级别有时候也叫做不可重复读 ( $nonrepeatable\ \ read$ )，因为两次执行同样的查询，可能会得到不一样的结果；REPEATABLE READ( 可重复读 )：该级别解决了脏读问题，保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，还是无法解决幻读 ( $Phantom\ \ Read$ ) 问题。幻读指的是当某个事物在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录。可重复读是MySQL的默认事务隔离级别；SERIALIZABLE( 可串行化 )：通过强制事务串行执行，可以避免幻读问题。但是由于会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁竞争问题。
3.2 死锁
为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，比如InnoDB存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。还有一种解决方式，就是当查询的时间达到等待超时的设定后放弃锁请求，这种方式通常来说不太好。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。
锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。死锁的产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，但有些完全是由于存储引擎的实现方式导致的。
3.3 事务日志
使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到硬盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志 ( $Write-Ahead\ \ Logging$ )，修改数据需要写两次磁盘。
3.4 MySQL中的事务
MySQL提供了两种事务型的存储引擎：InnoDB和NDB Cluster。另外还有一些第三方存储引擎也支持事务，比较知名的包括XtraDB和PBXT。
MySQL默认采用自动提交 ( $AUTOCOMMIT$ ) 模式，如果不是显式开始一个事务，则每个查询都被当做一个事务执行提交操作。通过$SET\ \ AUTOCOMMIT$ ，可以启用或者禁用自动提交模式。禁用之后，只有显式地执行提交或者回滚操作，当前事务才会结束。但对于非事务型的表，比如MyISAM或者内存表，不会有任何影响。另外还有一些命令，在执行之前会强制执行COMMIT提交当前的活动事务，比如 $ALTER\ \ TABLE$ 。
MySQL可以通过执行 $SET\ \ TRANSACTION\ \ ISOLATION\ \ LEVEL$ 命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。MySQL能够识别所有的 $4$ 个ANSI隔离级别，InnoDB引擎也支持所有的隔离级别。
MySQL服务器层部管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中使用多种存储引擎是不可靠的。在事务中混合使用事务型和非事务型的表，在正常提交的情况下不会有什么问题。但是当事务需要回滚时，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复。
InnoDB采用的是两阶段锁定协议 ( $two-phase\ \ locking\ \ protocol$ )。在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。
4. 多版本并发控制
MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制 ( MVCC )。可以认为MVCC是行级锁的一个变种，但是它在很多情况下都避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。不同引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制。
InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间。当然存储的不是实际时间，而是系统版本号。每开始一个新事务，系统版本号都会自动递增。在REPEATABLE READ隔离级别下，MVCC的操作方式如下：
- $SELECT$ ：
InnoDB会根据以下两个条件检查每行记录：查找版本早于当前事务版本的数据行，并保证行的删除版本要么未定义，要么大于当前事务版本号。只有符合上述条件的记录，才能返回作为查询结果； - $INSERT$ ：为新插入的每一行保存当前系统版本号作为行版本号；
- $DELETE$ ：为删除的每一行保存当前系统版本号作为行删除标识；
- $UPDATE$ ：插入一行新记录，为插入的新行保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。
MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。
5. MySQL的存储引擎
在文件系统中，MySQL将每个数据库（也可以称之为schema）保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的 $.frm$ 文件保存表的定义。可以使用 $SHOW\ \ TABLE\ \ STATUS$ 命令显示表的相关信息。
5.1 InnoDB
InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期 ( $short-lived$ ) 事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
InnoDB的数据存储在表空间 ( $tablespace$ ) 中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。在MySQL 4.1以后的版本中，InnoDB可以将每个表的数据和索引存放在单独的文件中。InnoDB也可以使用裸设备作为表空间的存储介质，但现代的文件系统使得裸设备不再是必要的选择。存在如下几种表空间：
- 系统表空间：在安装数据库的时候默认会初始化一个以 $ibdata1$ 命名的系统表空间，存储所有数据的信息以及回滚段信息，默认大小为 $10MB$ ，在高并发情况下会有性能影响，建议调整大小为 $1GB$ ；
- 独立表空间：设置参数 $innodb_-file_-per_-table=1$ ，目前
MySQL默认都是独立表空间，即每个表都有自己的表空间文件，存储对应的B+Tree、索引和插入缓冲等信息，其余信息会存储在共享表空间； - 撤销表空间：包含撤销日志，初始化的时候会创建两个默认的撤销表空间；
- 通用表空间：可以存储多个表的数据，相比独立表空间更节约元数据的内存开销；
- 临时表空间：分会话临时表空间和全局临时表空间。会话临时表空间会在第一个请求中从临时表空间分配，当会话断开时，临时表空间将被释放进入临时表空间池中；全局临时表空间用于存储用户创建的临时表的更改数据，用于回滚，在正常关闭或初始化中止时被删除，并在每次启动服务器时重新创建；
InnoDB使用MVVC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ，并且通过间隙锁 ( $next-key\ \ locking$ ) 策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB表是基于聚簇索引建立的，和MySQL的其他存储引擎有很大的不同，对主键查询有很高的性能。不过它的二级索引 ( $secondary\ \ index$ ，非主键索引 ) 中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。
InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引 ( $adaptive\ \ hash\ \ index$ )，以及能够加速插入操作的插入缓冲区 ( $insert\ \ buffer$ ) 等。
5.2 MyISAM
在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎，提供了大量的特性，包括全文索引、压缩、空间函数等，但不支持事务和行级锁，以及崩溃后的安全恢复。虽然不支持事务和崩溃后的安全恢复，但对于只读的数据，或者表比较小，可以忍受修复操作，依然可以使用MyISAM。
MyISAM将表存储在两个文件中：数据文件和索引文件，分别以 $.MYD$ 和 $.MYI$ 为扩展名。MyISAM表可以包含动态或者静态（长度固定）行。在MySQL 5.0中，如果是变长行，则默认配置只能处理 $256TB$ 的数据，这个配置可以通过修改指针长度实现。
作为MySQL最早的存储引擎之一，MyISAM有一些已经开发出来很多年的特性：
- 加锁与并发：读取时对表加共享锁，写入时对表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录；
- 修复：可以手工或者自动执行检查和修复操作，但这里说的修复和事务恢复以及崩溃恢复是不同的概念。执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的；
- 索引特性：对于
MyISAM表，即使是 $BLOB$ 和 $TEXT$ 等长字段，也可以基于前 $500$ 个字符创建索引。MyISAM也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询； - 延迟更新索引键：创建时如果指定 $DELAY_-KEY_-WRITE$ 选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区 ( $in-memory\ \ key\ \ buffer$ )，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。
如果表在创建并导入数据后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表，可以使用 $myisampack$ 对MyISAM表进行压缩。压缩表是不能进行修改的，可以极大地减少空间占用，因此也可以减少磁盘I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读。
5.3 转换表的引擎
- $ALTER\ \ TABLE$ ：将表从一个引擎修改为另一个引擎最简单的办法是使用 $ALTER\ \ TABLE\ \ xxx\ \ ENGINE$ 语句，上述语法可以适用任何存储引擎，但是需要执行很长时间，因为需要将表项复制到新表。在复制的过程中，原表上会加读锁；
- 导出与导入：可以使用 $mysqldump$ 工具将数据导出到文件，然后修改文件中的 $CREATE\ \ TABLE$ 语句的存储引擎选项，同时修改表名；
- 创建与查询：先创建一个新的存储引擎的表，然后利用 $INSERT\ \ \cdots\ \ SELECT$ 语句来导入数据。对于数据量较大的情况，可以考虑做分批处理。