MySQL高性能学习——（二）MySQL事务和存储引擎概览

一、 事务

事务就是一组原子性的SQL查询，一个独立的工作单元。如果数据库引擎能够成功对数据库应用全部sql语句，那么就执行该组查询，若有任何一条sql因为崩溃或者其他原因无法执行，那么所有的语句都不会执行。
可以用START TRANSACTION语句开始一个事务，然后要么用COMMIT提交事务，要么使用ROLLBACK撤销回滚所有的修改。

START TRANSACTION;
SELECT balance From checking WHERE customer_id = 1;
UPDATE checking SET balance = balance -200 where customer_id = 1;
COMMIT;

一个运行良好的事务处理系统，必须通过严格的ACID测试。ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。

原子性：
一个事务必须被视为一个不可分割的最小单元。
一致性：
数据库总是从一个一致性的状态转换到另外一个一致性的状态。
隔离性：
一个事务所做的修改在最终提交之前，对其他事务不可见。
持久性：
一旦事务提交，其作出的修改会永久保存到数据库中。

1. 隔离级别

隔离性：在SQL标准中定义了四种隔离级别：

READ UNCOMMITTED （未提交读）

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

READ COMMITTED（提交读）

大多数数据库默认隔离级别都是 READ COMMITTED（mysql不是）。一个事务从开始直到提交之前，所做的任何操作对其他事务都是不可见的，这个级别也叫做不可重复读。

REPEATABLE READ （可重复读）

mysql默认事务隔离级别。这一级别解决了脏读的问题，该级别保证了在同一事务中多次读取同样的记录结果是一致的。但是理论上无法解决幻读问题。（幻读：当某个事务在读取某范围内记录时，另一个事务又在该范围插入了新的记录，当第一个事务再次读取记录时，就会产生幻行。InnoDB和XtraDB存储引擎通过多版本并发控制解决了幻读的问题）。

SERIALIZABLE （可串行化）

最高隔离级别：通过强制事务串行执行，避免了前面的幻读问题。这一级别会在读取的每一行数据都加锁，会导致大量的超时和锁争用的问题。实际中很少用到。

2. 死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
产生死锁的原因：数据冲突或由于存储引擎的实现方式导致的。
死锁发生后，只有部分或者完全回滚其中一个事务，才能打破死锁。

3. 事务日志

使用事务日志，存储引擎在修改表数据时只需要修改内存靠背，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将数据本身持久到磁盘。

事务日志采用追加方式，写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘多个地方移动磁头，所以采用事务日志的方式相对要快得多。事务日志持久后，内存中被修改的数据在后台可以慢慢刷回磁盘。目前大多存储引擎都是这样实现的，称之为预写式日志，修改数据需要写两次磁盘。

4. MySQL中的事务

MySQL提供了两种事务型存储引擎：InnoDB和NDB Cluster。还有第三方存储引擎也支持事务：XtraDB和PBXT。

AUTOCOMMIT 自动提交

MySQL默认采用自动提交模式，即每个查询都被当做一个事务执行提交操作。可以通过设置AUTOCOMMIT启用或禁用自动提交模式。

在事务中混合使用存储引擎

由于MySQL服务器层不管理事务，只由存储引擎实现，因此同一事务使用多种存储引擎会引发一系列问题。
比如：在事务中混合使用了事务型和非事务型的表，若事务需要回滚，非事务型的表将无法撤销，事务最终结果也将无法确定。

隐式和显式锁定

InnoDB采用两段锁定协议，在事务执行过程中，随时可以执行锁定，而锁只在COMMIT和ROLLBACK的时候才会释放，并且所有的锁都在同一时刻被释放。InnoDB会根据隔离级别在需要的时候自动加锁。

5. 多版本并发控制（MVCC）

MySQL的大多数事务型存储引擎一般都同时实现了多版本并发控制（MVCC）。

可以将MVCC看做是行级锁的一个变种，它的实现是通过保存数据在某个时间点的快照来实现的。下面用InnoDB简化版行为说明MVCC如何工作：

InnoDB的MVCC在每行记录后面保存两个隐藏列，一个保存了行的创建时间、一个保存行的过期时间（删除时间）。存储的不是实际时间是系统的版本号，每开始一个新事物，系统版本号都会自动递增，事物开始时刻的系统版本号会作为事物的版本号，用来和查询到每行记录的版本号进行比较。

在REPEATABLE READ隔离级别下：

SELECT 
	InnoDB为新插入的每一行保存当前系统版本号为行版本号
DELETE 
	InnoDB为删除的每一行保存当前系统版本号为行删除标识
UPDATE
	InnoDB为插入一行新记录，保存当前系统版本号为行版本号，同时保存当前系统版本号到原来的行作为行删除标识		

优点： 读数据操作简单，性能好，保证只会读到复合标准的行，不需要加锁。
不足： 每行记录都要有额外的存储空间，需要做更多检查和维护工作。

同时，MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。（READ UNCOMMITTED总是读取最新数据行；SERIALIZABLE会对所有读取行加锁。）

二、 MySQL的存储引擎

在文件系统中，MySQL将每个数据库（schema）保存为数据目录下的子目录。创建表时，会在子目录下创建一个和表同名的frm文件保存表的定义。

InnoDB概览

InnoDB是MySQL的默认事务型引擎，也是最重要最广泛的存储引擎。它用来处理大量短期事务，它的性能和自动崩溃回复特性使它在非事务型存储的需求中也很流行。

• InnoDB数据存储在表空间中。（表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成）
• InnoDB采用MVCC来支持高并发，并实现了四个标准隔离级别。（默认级别为REPEATABLE READ，通过间隙锁 next-key locking策略来防止幻读。）
• InnoDB表基于聚簇索引建立的，聚簇索引对主键查询有很高的性能，不过它的耳机索引中必须包含主键列。
• InnoDB内部很多优化，包括：从磁盘读取数据时采用可预测性预读，能自动在内存中创建hash索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。

如果使用InnoDB引擎，一定要阅读官方手册中的“InnoDB事务模型和锁”一节。

MyISAM引擎

MySQL5.1之前，默认的存储引擎是MyISAM，它提供了大量特性，包括全文索引、压缩、空间函数（GIS）等，但是它不支持事务和行级锁，崩溃后无法安全恢复。

• MyISAM会将表存在数据文件和索引文件中，分别以 .MYD 和 .MYI为扩展名。
• 特性：加锁与并发，MyISAM对整张表加锁，读取时加共享锁，写入时加排他锁。
• 特性：修复：MyISAM可以手工或自动执行检查和修复操作，但这可能导致一些数据丢失、且操作很慢。
• 特性：索引：即使是BLOB和TEXT等长字段，也可基于前500个字符创建索引，支持全文索引。
• 特性：延迟更新索引键：创建表时，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会卸刀内存中的键缓冲区，只有在清理键缓冲区或关闭表时才将对应索引块写入磁盘。
• MyISAM压缩表：压缩表可以极大减少磁盘空间占用，提升查询性能，支持索引，但是只有只读表适用，压缩后的表不能修改，除非解压修改后再压缩。

Archive引擎

只支持INSERT和 SELECT操作。

Blackhole引擎

没有实现任何存储机制，不做任何保存。但服务器会记录表日志，用于复制数据到备库，或只简单记录到日志。

CSV引擎

将普通的CSV文件（逗号分割值文件）作为表来处理，不支持索引。可以在数据库运行时拷入或拷出文件。可将Excel扥光电子表格软件中的数据存储为CSV文件，然后复制到MySQL数据目录下。

Federated引擎

Federated是访问其他MySQL服务器的代理，会创建一个到远程MYSQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或发送需要的数据。

Memory引擎

如果需要可以快速访问数据，且这些数据不会被修改，重启以后丢失也没关系，那么可以使用Memory。要比MyISAM表快一个数量级。如果MySQL在执行查询中需要使用临时表来保存中间结果，内部使用的临时表就是Memory表。如果中间结果过大或含有BLOB或TEXT字段，那么临时表会转换成MyISAM表。

Merge引擎

MyISAM引擎的一个变种，是由多个MyISAM表合并而来的虚拟表。可用于日志或数据仓库类应用。

NDB集群引擎

作为SQL和NDB原生协议之间的接口。

1. 转换表的引擎

（1）ALTER TABLE

最简单的转换引擎方法：

mysql> ALTER TABLE mytable ENGINE = InnoDB;

上述语法可以使用于任何存储引擎，但是需要执行很长时间。MySQL会按行将数据从原表复制到一张新的表中，期间可能会消耗系统所有的I/O能力，同事原表上会加上读锁。如果转换表引擎，将会失去和原引擎相关的所有特性。

（2）导出和导入

使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项，要同时修改表名。

（3）创建和查询

这种转换结合了第一种的高效和第二种的安全，先创建一个新的存储引擎的表，然后利用INSERT、SELECT语法来导出数据。

mysql> CREATE TABLE innodb_table LIKE myisam_table;
mysql> ALERT TABLE innodb_table ENGINE=InnoDB;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;