《高性能MySql》读书笔记（一）

MySql逻辑架构

MsSql的服务器是基于C/S架构的，逻辑架构大致上可以分为三层。

第一层是客户端，每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行。当客户端 连接到MySQL服务器时，服务器需要对其进行认证。一且客户端连接成功， 服务器会继续验证该客户端是否具有执行某个特定查询的权限（例如， 是否允许客户端对 world 数据库的 Country 表执行 SELECT 语句）。

第二层是MySQL的核心服务功能，包括查询解析、分析、优化、缓存以及所有的内置函数（例如， 日期、 时间、 数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。MySQL 会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示(hint) 优化器，影响它的决策过程。也可以请求优化器解释 (explain) 优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便千用户重构查询和 schema、修改相关配置，使应用尽可能高效运行。优化 器会请求存储引擎提供容量或某个具体操作的开销信息， 以及表数据的统计信息等。对于SELECT 语句，在解析查询之前，服务器会先检查查询缓存(Query Cache)，如果能够在其中找到对应的查询， 服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。但是在MySql5.7以后的版本中已经移除了该项功能，可能太过鸡肋。

第三层包含了存储引擎。服务器通过API与存储引擎进行通信。存储引擎API包含儿十个底层函数，用千执行诸如”开始一个事务”或者 ”根据主键提取一行记录” 等操作。

并发控制

表锁

表锁是MySQL中最基本的锁策略， 并且是开销最小的策略，它会锁定整张表。一个用户在对表进行写操作（插人、 删除、 更新等）前， 需要先获得写锁， 这会阻塞其他用户对该表的所有读写操作。 只有没有写锁时， 其他读取的用户才能获得读锁， 读锁之间是不相互阻塞的。另外，写锁也比读锁有更高的优先级，写锁可以插入到锁队列中读锁的前面， 反之读锁则不能插入到写锁的前面。

行级锁

行级锁可以最大程度的支持并发处理（最大的锁开销）。InnoDB 和 XtraDB ,以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，MySQL 服务层没有实现。

事务

事务就是一组原子性的SQL查询，事务内的语句， 要么全部执行成功， 要么全部执行失败。

事务的ACID特性

特性	解释
原子性(atomicity)	一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功， 要么全部失败回滚，对千一个事务来说， 不可能只执行其中的一部分操作，这就是事务的原子性。
一致性(consistency)	数据库总是从一个一致性的状态转换到另外一个一致性的状态。
隔离性(isolation)	一个事务所做的修改在最终提交之前，对其他事务都是不可见的。
持久性(durability)	一旦事务提交，则其所做的修改就会永久保存到数据库中。

事务的隔离级别

隔离级别	解释
READ UNCOMMITTED (未提交读）	在 READ UNCOMMITTED 级别，事务中的修改，即使没有提交，对其他事务也都是可见 的。事务可以读取未提交的数据，这也被称为脏读(Dirty Read)。从性能上来说，READ UNCOMMITTED 不会比其他的级别好太多，但却缺乏 其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。
READ COMMITTED (读取已提交）	大多数数据库系统的默认级别都是read commited，一个事务开始时只能看见已经提交的事务所做的修改，也称为不可重复读。
REPEATABLE READ (可重复读）	解决了脏读问题，保证了在 同一个事务中多次读取同样的记录的结果是一样的。可重复度级别无法解决幻读问题。InnoDB 和XtraDB 存储引擎通过多版本并发控制(MVCC )解决了幻读问题。
ERIALIZABLE (可串行化）	最高的隔离级别。它通过强制事务串行执行，避免了幻读问题。serializable 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁竞争的问题。实际开发中很少用。

MySQL 可以通过执行 SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL 默认采用自动提交 (AUTOCOMMIT) 模式。 也就是说， 如果不是显式地开始个事务， 则每个查询都被当作一个事务执行提交操作。

事务日志

事务日志可以帮助提高事务的效率。 使用事务日志， 存储引擎在修改表的数据时只需要修改其内存拷贝， 再把该修改行为记录到持久在硬盘上的事务日志中， 而不用每次都将修改的数据本身持久到磁盘。 事务日志持久以后， 内存中被修改的数据在后台可以慢慢地刷回到磁盘。

多版本并发控制（MVCC）

可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。 虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要 的行。
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。 也就是说，不管需要执 行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

InnoDB的MVCC, 是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号(system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，井且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

MVCC只在 REPEATABLE READ 和 READ COMMITTED两个隔离级别下工作。其他两个隔离 级别都和MVCC不兼容 , 因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

MySql存储引擎

lnnoDB存储引擎

InnoDB是MySql默认的存储引擎，支持事务操作，采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别 是REPEATABLE READ (可重复读），并且通过间隙锁(next-key locking)策略防止幻读的出现。。
InnoDB表是基于聚簇索引建立的。
InnoDB的锁是行级锁。
InnoDB支持在线热备份。

MyISAM 存储引擎

MyISAM相较于InnoDB，并不支持事务操作，MyISAM对整张表加锁， 而不是针对行。 读取时会对需要读到的所有表加共享锁， 写入时则对表加排他锁。但是在表有读取查询的同时， 也可以往表中插入新的记录。
InnoDB的锁是表锁。

除非需要用到某些InnoDB不具备的特性， 并且没有其他办法可以替代， 否则都应该优先选择InnoDB引擎。
如果不在乎可扩展能力和井发能力， 也不在乎崩溃后的数 据丢失问题， 却对InnoDB的空间占用过多比较敏感， 这种场合下选择MylSAM就比较合适。例如日志型数据。
除非万不得已， 否则建议不要混合使用多种存储引擎， 否则可能带来一系列复杂的问题，以及一些潜在的bug和边界问题。
不要轻易相信"MyISAM比InnoDB快” 之类的经验之谈，这个结论往 往不是绝对的。在很 多我们已知的场景中，InnoDB 的速度都可以让MyISAM望尘莫及， 尤其是使用到聚簇索引，或者需要访问的数据都可以放入内存的应用。