这是通读《高性能MySQL》（第三版）的第一篇笔记，这本书真的是一本不可多得的好书，光是第一章就解决了一些我一直以来的疑惑，估计学完会有和不小的收获。

第一章是MySQL的架构与历史，本章收获：

  • 认识MySQL的架构；
  • 认识“锁”；
  • 系统认识事务的原理；
  • 认识MySQL的存储引擎；
  • 如何选择合适的存储引擎等。

其中，InnoDB存储引擎和MyISAM引擎的区别以及如何选择是我之前一直还未了解但是必须了解的问题，通过这章内容也有了一定的认识。（笔记只记下来觉得非常有用的一些知识点作为回顾整理再者方便以后查阅，详情还是看原书。）

1.1 MySQL逻辑架构

MySQL的服务器逻辑架构图：

1.2 并发控制

读写锁

在处理并发读或者是写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两个类型的锁通常被称为共享锁（shared lock）和排他锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。

读锁是共享的，或者说是相互不阻塞的。多个用户在同一时刻可以同时读取同一个资源，而互不干扰。

写锁则是排他的，也就是说一个写锁会阻塞其它的写锁和读锁，这是出于安全策略的考虑，只有这样，才能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。

锁策略

锁策略：在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。

两种最重要的锁策略：

表锁（table lock）
表锁是MySQL中最基本的锁策略，并且是开销最小的策略。表锁锁定整张表。

行级锁（row lock）
行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。InnoDB和XtraDB（用来代替InnoDB的InnoDB增强版），以及一些其他的存储引擎中实现了行级锁。行级锁只在存储引擎层实现。

1.3 事务

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或者其他原因无法执行，那么所有的语句都不会执行。事务内的语句，要么全部执行成功，要么全部执行失败。

事务SQL例子：

START TRANSACTION;

SELECT balance FROM checking WHERE customer_id=10233276;

UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;

UPDATE saving SET balance = balance + 200.00 WHERE customer_id = 10233276;

COMMIT;

一个运行良好的事务处理系统，必须具备ACID特征。

ACID

ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。

  • 原子性
    整个事务中的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务来说，不可能只执行其中一部分操作，这就是事务的原子性。

  • 一致性
    数据库总是从一个一致性的状态转换到另一个一致性的状态。一致性确保了事务执行到一半时系统崩溃，但是事务最终没有提交，所以数据中所做的修改也不会保存的数据库中。

  • 隔离性
    通常来说（隔离级别），一个事务所做的修改在最终提交之前，对其他事务是不可见的。

  • 持久性
    一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

一个实现了ACID的数据库，相比没有实现ACID的数据库，通常会需要更强的CPU处理能力、更大的内存和更多的磁盘空间。用户可以根据业务是否需要事务处理，来选择合适的存储引擎。对于一些不需要事务的查询类应用，选择一个非事务型的存储引擎，可以获得更高的性能。即使存储引擎不支持事务，也可以通过LOCK TABLES语句为应用提供一定程度的保护，这些选择用户都可以自主决定。

隔离级别

四种隔离级别：

  • READ UNCOMMITTED（未提交读）
    事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。

  • READ COMMITTED（提交读）
    一个事务开始时，只能”看见“已经提交的事务所做的修改。换句话说，一个 事务从开始直到提交前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重读读。

  • REPEATABLE READ（可重复读）
    解决了脏读的问题。保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读的问题。幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。

  • SERIALIZALE（可串行化）
    最高隔离级别。会在读取的每一行数据都加上锁，所以可能导致大量的超时和锁争用的问题。只有在非常需要确保数据的一致性而且可以接受没有并发控制的情况下，才考虑采用该级别。

死锁

死锁是指两个或者多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务视图以不同的的顺序锁定资源时，就可能会产生死锁。

两个对方都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环。除非有外部因素介入才可能解除死锁。

为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。InnoDB目前处理死锁的方法是，将持有的最少行级排他锁的事务进行回滚（这是比较简单的死锁回滚方法）。

死锁的产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由于存储引擎的实现方式导致的。

MySQL中的事务

MySQL的两种事务型存储引擎：InnoDB和NDB Cluster。

自动提交（AUTOCOMMIT）

MySQL默认采用自动提交模式。如果不是显式地开始一个事务，每一个查询都被当作一个事务执行提交操作。在当前链接中可以设置AUTOCOMMIT变量来启用或者禁用自动提交模式：

mysql>SHOW VARIABLES LIKE 'AUTOCOMMIT';

mysql>SET AUTOCOMMIT = 1;

1或者ON表示启用，0或者OFF表示禁用。当AUTOCOMMIT=0时，所有的查询都是在一个事务中，直到显式地执行COMMMIT或者ROLLBACK回滚，该事务结束，同时又开始了另一个新事务。

修改AUTOCOMMIT对非事务型的表，比如MyISAM或者内存表，不会有任何影响。对这类表来说，没有COMMMIT或者ROLLBACK的概念，也就是说可以相当于一直处于AUTOCOMMIT启用的模式。

MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别：

mysql>SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

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

MySQL服务层不管理事务，事务是由下层的存储引擎实现的。

如果事务中混合使用了事务型和非事务型的表，在正常提交的情况下不会有什么问题。

但是如果事务需要回滚，非事务型的表上的表更就无法撤销。

为每张表选择合适的存储引擎非常重要。

隐式和显式锁定

隐式锁定：存储引擎自动进行的锁定

显式锁定：通过LOCK TABLES等语句进行锁定

建议：除了事务中禁用了AUTOCOMMIT，可以使用LOCK TABLES之外，其他任何时候都不要显式地执行LOCK TABLES，不管使用的是什么存储引擎。

1.4 多版本并发控制（MVCC）

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。

InnoDB的MVCC，是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号。保存这两个额外的系统版本号，使大多数读操作都可以不用加锁。

MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。

1.5 MySQL的存储引擎

InnoDB存储引擎

InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询设计的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

InnoDB的表是基于聚簇索引建立的。InnoDB的索引结构和MySQL其他存储引擎有很大的不同，聚簇索引对主键查询有很高的性能。

作为事务型的存储引擎，InnoDB通过一些机制和工具支持真正的热备份。

MySQL的其他存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

对于只读的数据，或者表比较小、可以忍受修复操作，则依然可以继续使用MyISAM（但请不要默认使用MyISAM，而是应当默认使用InnoDB）。

MyISAM特性

加锁与并发：对整张表加锁，而不是针对行。在表有读取查询时，也可以往表中插入新的记录（并发插入）。

修复（和事务恢复以及崩溃恢复不是一个概念）：手工或者自动执行检查和修复操作。通过CHECK TABLE mytable检查表的错误，如果有错误可以通过执行REPAIR TABLE mytable进行修复。

索引特性：即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建索引。MyISAM也支持全文索引。

延迟更新索引键（Delayed Key Write）：创建表的时候如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。

MyISAM压缩表

可以使用myisampack对MyISAM表进行压缩。压缩表可以极大地减少磁盘空间占用，也可以减少磁盘I/O，从而提升查询性能。

MyISAM性能

MyISAM最典型的问题还是表锁的问题，如果你发现所有的查询都查询都长期处于“Locked”
状态，那么毫无疑问表锁就是罪魁祸首。

MySQL内建的其他存储引擎

  • Archive引擎：不是一个事务型引擎，而是一个针对告诉插入和压缩做了优化的简单引擎。
  • Blackhole引擎
  • CSV引擎：可以将Excel等电子表格软件中的数据存储为CSV文件，然后复制到MySQL数据目录下，就能在MySQL中打开使用。
  • Memory引擎：所有数据保存在内存中，不需要进行磁盘I/O。Memory是表级锁，所以并发写入性能较低。
  • Merge引擎
  • NDB集群引擎：MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群（MySQL Cluster）。

第三方存储引擎

Percona的XtraDB存储引擎是基于InnoDB引擎的一个改进版本。

选择合适的引擎

除非需要用到某些InnoDB不具备的特性，并且没有其他的办法可以替代，否则都应该优先选择InnoDB引擎。

例如：如果要用到全文索引，建议优先考虑InnoDB+Sphinx的组合，而不是使用支持全文索引的MyISAM。

除非万不得已，否则建议不要混合使用多种存储引擎，否则可能带来一列复杂的问题，以及一些潜在的bug和边界问题。至少，混合存储对一致性备份和服务器参数配置都带来了一些困难。

选择引擎时需要考虑的因素：事务、备份、奔溃恢复、特有的特性

不要轻易相信“MyISAM比InnoDB快”之类的经验之谈，这个结论往往不是绝对的。

如果涉及订单处理，那么支持事务就是必要选项。

转换表的引擎

1. ALTER TABLE

mysql> ALTER TABLE mytable ENGINE = InnoDB;

缺点：需要执行很长时间；复制期间消耗系统所有的I/O能力；原表上会加上读锁。

2. 导入与导出

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

3. 创建与查询（CREATE 和 SELECT）

综合了前两种方法的高效和安全。创建一个新的存储引擎的表，然后利用INSERT···SELECT语法来导数据：

mysql>CREATE TABLE innno_table LIKE myisam_table;

mysql>ALTER TABLE innodb_table ENGINE=InnoDB;

mysql>INSERT INTO innodb_table SELECT * FROM myisam_table;

数据量很大时，可以用事务分批处理：

mysql> START TRANSACTION;

mysql> INSERT INTO innodb_table SELECT * FROM myisam_table

-> WHERE id BETWEEN x AND y;

mysql> COMMIT;

如果有必要，可以在执行的过程中对原表加锁，以确保新表和原表的数据一致。