一、MySQL逻辑架构
前言：为了充分发挥MySQL的性能并顺利地使用，就必须理解其设计
MySQL最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理及其他系统任务和数据的存储/提取相分离。所以这种处理和存储分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式
1、MySQL的逻辑架构图
1.1、连接管理与安全性
1.2、优化与执行
2、并发控制
2.1、读写锁
共享锁（读锁）、排他锁（写锁）
2.2、锁粒度
表锁、行级锁
3、事务
3.1、 ACID
3.2、隔离级别
未提交读（脏读） 事务可以读取未提交的数据，性能不比其他级别好太多，如非必要一般不使用
提交读（不可重复读） 一个事务从开始直到提交之前，所作的任何修改对其他事务都是不可见的
可重复读（MySQL的默认事务隔离级别） 解决了脏读问题，保证了在同一个事务中多次读取同样记录的结果是一致的。但可重复读会出现幻读问题，当一个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，这时当之前的事务再去读取该范围的记录时，就产生了幻行
可串行化（最高隔离级别） 强制事务串行执行，避免了幻读的问题，在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别
3.3、死锁
死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象
为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。InnoDB目前处理死锁的方式是，将持有最少行级排他锁的事务进行回滚
锁的行为和顺序是和存储引擎相关的。死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。所以应用程序在设计时必须考虑如何处理死锁，大多数情况下只需重新执行因死锁回滚的事务即可。
3.4、事务日志
事务日志可以帮助提高事务的效率。
使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为 记录到 持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。
事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多
在事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。通常称为预写式日志，所有其实修改数据需要写两次磁盘（一次是事务日志持久化到磁盘，一次是修改数据刷回到磁盘）
3.5、MySQL中的事务
MySQL提供了两种事务型的存储引擎：InnoDB和NDB Cluster。另外还有一些第三方存储引擎也支持事务，比较知名的包括XtraDB和PBXT
MySQL默认采用自动提交模式。如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。
4、多版本并发控制
MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC）
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。因为事实上根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的
不同存储引擎的MVCC实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。
悲观锁的理解：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。悲观锁的实现，往往依靠底层提供的锁机制；悲观锁会导致其它所有需要锁的线程挂起，等待持有锁的线程释放锁。
乐观锁的理解：假设不会发生并发冲突，每次不加锁而是假设没有冲突而去完成某项操作，只在提交操作时检查是否违反数据完整性。缺点是不能解决脏读的问题，所以通常乐观锁与提交读的隔离等级共同使用。
InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间），实际上存储的不是实际的时间值，而是系统版本号。每开始一个新事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
以可重复读（REPEATABLE READ）隔离级别为例，解析MVCC具体如何操作：
SELECT
InnoDB会根据以下两个条件检查每行记录：
1、只查找版本早于当前事务版本的数据行（行的系统版本号小于或等于事务的系统版本号），确保事务读取的行，要么是在事务开始前已经存在，要么是事务自身插入或者修改过
2、行的删除版本要么未定义，要么大于当前事务版本号，确保事务读取到的行，在事务开始之前未被删除。
只有符合这两个条件的记录，才能返回作为查询结果
INSERT
为新插入的每一行保存当前系统版本号作为行版本号
DELETE
为删除的每一行保存当前系统版本号作为行删除标识
UPDATE
为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识
优缺点：保存两个额外系统版本号，使大多数读操作都可以不用加锁。使读数据操作很简单，性能很好。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作
MVCC只在可重复读和提交读两个隔离级别下工作。因为未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行。而可串行化则会对所有读取的行都加锁。
5、MySQL的存储引擎
在文件系统中，MySQL将每个数据库保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。因为MySQL使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。
不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在MySQL服务层统一处理。
5.1、InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。
InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。
InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。
InnoDB采用MVCC来支持高并发，并实现了四个标准的隔离等级。其默认级别是可重复读，并且通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB表是基于聚簇索引建立的。聚簇索引对主键查询有很高的性能，不过它的二级索引（非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。
InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。
作为事务型的存储引擎，InnoDB通过一些机制和工具支持真正的热备份，MySQL的其他存储引擎不支持热备份。
5.2、MyISAM存储引擎
在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。提供大量的特性，包括全文索引、压缩、空间函数（GIS）等，但不支持事务和行级锁，一个毫无疑问的缺陷就是崩溃后无法安全恢复。
MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。但最典型的性能问题还是表锁问题，如果所有的查询都长期处于锁定状态，那么查询的速度无疑是很慢的。
5.3、MySQL内建的其他存储引擎
Archive引擎，是一个针对高速插入和压缩做了优化的简单引擎。只支持INSERT和SELECT操作，会缓存所有的写并利用zlib对插入的行进行压缩，所以比MyISAM表的磁盘I/O更少。每次SELECT查询都需要执行全表扫描，适合日志和数据采集类应用或者在一些需要更快速的INSERT操作的场合下使用。支持行级锁和专用的缓冲区，所以可以实现高并发的插入。
CSV引擎，可以将普通的CSV文件（逗号分割值的文件）作为MySQL的表来处理，但不支持索引。将数据存储为CSV文件，然后复制到MySQL数据目录下，就能在MySQL中打开使用。将数据写入到一个CSV引擎表，其他外部程序也能从表的数据文件中读取CSV格式的数据。因此CSV引擎可以作为一种数据交换的机制，非常有用。
Memory引擎，如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。比MyISAM表快一个数量级，因为数据保存在内存中，不需要进行磁盘I/O。Memory表的结构在重启以后还会保留，但数据会丢失。应用场景为用于查找或者映射表、缓存周期性聚合数据的结果、用于保存数据分析中产生的中间数据。支持Hash索引，因此查找操作非常快。是表级锁，因此并发写入性能较低。不支持BLOB或TEXT类型的列，并且每行的长度固定，即使指定VARCHAR列，实际存储也会转成CHAR，可能导致部分内存的浪费。如果MySQL在执行查询中需要使用临时表保存中间结果，内部使用的临时表就是Memory表。如果中间结果超出Memory表的限制，或者含有BLOB或TEXT字段，则临时表会转换成MyISAM表。
5.4、第三方存储引擎
OLTP类引擎
XtraDB存储引擎是基于InnoDB引擎的一个改进版本，已经包含在Percona Server和MariaDB中，它的改进主要集中在性能、可测量性和操作灵活性方面。
面向列的存储引擎
MySQL默认是面向行的，每一行的数据是一起存储的，服务器的查询也是以行为单位处理的。而在大数据量处理时，面向列的方式可能效率更高。如果不需要整行的数据，可以传输更少的数据，如果每一行都单独存储，那么压缩的效率也会更高。
Infobright是最有名的面向列的存储引擎，是为数据分析和数据仓库应用设计的。
5.5、选择合适的引擎
5.6、转换表的引擎
将表的存储引擎转换成另外的一种引擎有很多方法，下面讲述其中的三种方法：
1、ALTER TABLE
ALTER TABLE mytable ENGINE = InnoDB;
优点：可以适用任何存储引擎
缺点：执行时间长，MySQL会按行将数据从原表复制到一张新的表中，期间可能会消耗系统所有的I/O能力，所以替代方案采用导出与导入，手工进行表的复制
注意：如果转换表的存储引擎，将会失去和原引擎相关的所有特性。
2、导出与导入
使用工具将数据导出到文件，然后修改文件中CREATE TABLE 语句的存储引擎选项，注意同时修改表名。注意在CREATE TABLE 语句前的DROP TABLE语句，不注意可能会导致数据丢失。
3、创建与查询（CREATE和SELECT）
综合第一种的高效和第二种的安全，不需要导出整个表的数据，先创建一个新的存储引擎表，然后利用INSERT...SELECT语法来导数据：
CREATE TABLE innodb_table LIKE myisam_table;
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;
如果数据量不大，这样做工作得很好。如果数据量大，可以考虑分批处理，针对每一段数据执行事务提交操作。
假设有主键字段id，重复运行以下语句（最小值x和最大值y进行相应的替换）将数据导入到新表：
START TRANSACTION;
INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
COMMIT;
新表是原表的一个全量复制，原表还在。如果需要可以删除原表，如果有必要，可以在执行的过程中对原表加锁，以确保新表和原表的数据一致。