高性能MySQL--第一章 MySQL架构与历史

1.MySQL最重要的不同是它的存储引擎架构，将查询处理（Query Processing）以及其他系统任务（Server Task）和数据的存储/提取相互分离。

MySQL的核心服务在第二层，包括查询解析、分析、优化、缓存以及所有的内置函数（日期、时间、数学、加密函数），跨存储引擎的功能在这一层实现：存储过程、触发器、视图。

读锁-共享锁，相互不阻塞；写锁-排他锁，阻塞其他的写锁和读锁。表锁，锁定整张表，如ALERT TABLE语句；行级锁，最大成都支持并发处理。

事务是一组原子性的SQL查询，一个独立的工作单元。事务内的语句，或全部执行成功，或全部执行失败。START TRACTION语句开始一个事务，COMMIT提交事务将修改数据持久保存，ROLLBACK撤销所有修改。ACID，原子性，一致性，隔离性，持久性。

实现了ACID的数据库会比没有实现的需要更强的CPU处理能力、更大的内存和更多的磁盘空间。根据业务选择合适的存储引擎。LOCK TABLES语句可以为应用提供一定程度的保护。

隔离级别，较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

读未提交，事务中的修改，即使未提交，对其他事务也是可见的。事务可以读取未提交的数据--脏读（Dirty Read）。实际应用很少用。

读已提交，多数数据库系统默认隔离级别，事务提交前，所做的修改其他事务不可见。两次执行同样的查询结果不一样，导致不可重复读。

可重复读（MySQL默认隔离级别），同一个事务中多次读取同样的记录的结果是一样的。解决了脏读问题。幻读（一个事务读取某范围内的记录时，另一个事务在该范围内插入了新记录，之前的事务再次读取该范围时会产生幻行）未解决。多版本并发控制（MVCC）解决幻读问题。InnoDB的默认级别，InnoDB通过间隙锁（next-key locking）策略防止出现幻读，间隙锁不仅锁定查询的行，还会对索引中的间隙进行锁定，防止幻影行的插入。

可串行化（SERIALIZABLE），最高隔离级别，强制事务串行执行，避免了幻读。读取的每一行加锁，可能导致大量的耗时和锁竞争问题。要保证数据高度一致且接受没有并发的情况下才会使用此级别。

死锁，多个事务在同一资源上互相占用，并请求对方占用的资源，相互等待，都不释放，除非有外部介入才能解除死锁。

InnoDB处理死锁：将持有最少行级排他锁的事务进行回滚。

部分或完全回滚其中一个事务才能打破死锁。

事务日志，提高事务效率。存储引擎修改表数据时，只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。采用追加的方式，写日志的操作是磁盘上一小块区域内的顺序I/O，不像随机I/O需要频繁移动磁盘头，所以快很多。事务日志持久后，内存中被修改的数据可以在后台慢慢刷回到磁盘。预写式日志，修改数据需要写两次磁盘（第一次写到持久行事务日志中，第二次根据事务日志写到数据库里）。

事务型存储引擎：InnoDB和NDB Cluster。XtraDB、PBXT

mysql默认自动提交模式。

MVCC多版本并发控制，在很多情况下避免了加锁操作，开销更低。通过保存数据在某个时间点的快照来实现多版本并发控制。

InnoDB的MVCC，通过在每行记录后面保存两个隐藏的列来实现MVCC。一列保存来行的创建时间，一列保存行的过期时间（删除时间）。存储的是系统版本号。每开始一个新的事务，系统版本号会自动递增。事务开始时刻的版本号是事务的版本号。MVCC只在可重复读和读已提交两个隔离级别下工作。

MySQL存储引擎：

InnoDB存储引擎，MySQL默认事务型引擎，最广泛使用的存储引擎。用来处理大量的短期（short-lived）事务（正常提交，很少回滚）。数据存储在tablespace中，每个表的数据和索引放在单独的文件中。InnoDB采用MVCC来支持高并发，实现了四个标准的隔离级别。基于聚簇索引建立，从磁盘读取数据时有可预测预读（自动在内存中创建hash索引来加速读操作的自适应哈希索引adaptive hash index，能加速插入操作的插入缓存区insert buffer），支持热备份。

MyISAM存储引擎，MySQL5.1之前的默认引擎。支持全文索引、压缩、空间函数（GIS）。不支持事务和行级锁，崩溃后无法安全恢复。将标存储在两个文件里（数据文件和索引文件），可以包含动态或静态（长度固定）行。对整张表加锁，不是行锁。可以手工或自动执行检查和修复操作，但kennel导致一些数据丢失。延迟更新索引键。压缩表支持索引，但是只读的。

日志型应用，适合使用MyISAM或Archive存储引擎，因为它们开销低，而且插入速度非常快。

修改存储引擎的方法：（1）ALTER TABLE [表名] ENGINE=InnoDB;（2）mysqldump导出修改文件中CREATE TABLE语句的存储引擎选项，然后再导入；（3）CREATE和SELECT，先创建一个新存储引擎的表，然后用INSERT ... SELECT语法导数据。