第一章

MySQL 的灵活性体现在很多方面，但其中最与众不同的特性是它的存储引擎架构：将查询处理以及其他系统任务和数据的存储/提取相分离。这种设计使得我们可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式。

MySQL 架构整体上分为三层：第一层处理连接、权限等；第二层包含所有核心的功能，例如，查询解析、分析、优化、缓存、所有的内置函数，存储过程、触发器、视图等等。第三层则是存储引擎。服务器通过包含几十个底层函数的存储引擎 API 来与存储引擎通信，API 可以屏蔽不同存储引擎的差异，使得对上层的查询过程透明。架构如下图：

MySQL 架构

整个 MySQL 程序包括客户端和服务端，客户端和服务端通过网络进行通信。当某个客户端和服务端建立连接后，就会在服务器进程中拥有一个线程。使用线程池可以避免频繁地创建和销毁线程。

MySQL 会将一条查询语句解析成内部数据结构——解析树，然后对其进行优化：重写查询、决定表的读取顺序、选择合适的索引等。对于 Select 语句，会用整个查询语句作为 Key，去缓存中查询处理结果。

关于并发控制。MySQL 会在两个层面来进行：服务器层、存储引擎层。最经典的解决方案就是锁机制，它通过两种类型的锁组成锁系统：共享锁（读锁）、排它锁（写锁）。锁的粒度会决定系统的并发度。一种提高共享资源的并发性的方法就是让锁更具有选择性，即只锁定需要修改的部分数据，而不是所有。在给定的资源上，锁定的数据量越少，则系统的并发程度越高。

另一方面，关于锁的操作：获得锁、检查锁是否解决、释放锁等都会增加系统的开销。因此锁策略就是在锁的开销和数据的安全性之间寻求平衡，既保证数据并发安全又能提高数据库的性能。各种数据库系统一般都是实现的行级锁。MySQL 的存储引擎可以实现自己的锁策略和锁粒度。

表锁是 MySQL 中开销最小、最基本的锁策略。尽管存储引擎可以管理自己的锁，但在执行诸如 Alter table 之类的语句时，服务器会忽略存储引擎的锁，而直接用表锁。

行级锁可以最大程度地支持并发处理，同时也带来了最大的锁开销。行级锁只在存储引擎中实现，服务器层没有实现，行级锁对服务器是透明的。

关于事务。一个运行良好的事务处理系统，必须具备：

原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）

也就是常说的 ACID 特性。一一解释：

原子性：一个事务中的所有操作，要么全部提交成功，要么全部失败回滚。 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。 隔离性：通常来说，一个事务所做的修改在提交之前，对其他事务是不可见的。之所以说通常，因为这和事务的隔离级别是相关的。 持久性：一旦事务提交，则其所做的修改会永久保存到数据库中。

在 SQL 标准中，定义了四种隔离级别，每种都规定了一个事务中所做的修改，哪些在事务内、事务间是可见的，哪些是不可见的。隔离级别越低，系统开销越小，并发越大。四种隔离级别如下：

未提交读（READ UNCOMMITTED）：一个事务可以读取其他事务未提交的修改，这也被称为“脏读”。实际中一般很少使用。

提交读（READ COMMITTED）：一个事务从开始到提交之前，所做的任何修改对其他事务是不可见的。这是大多数数据库系统的默认的事务隔离级别。这个级别也被称为不可重复读，因为在一个事务中，执行两次相同的查询，可能会得到不一样的结果。例如，第二次执行时，可以读到其他事务的提交。

可重复读（REPEATABLE READ）：同一个事务中，多次读取同样记录的结果是一致的。这是 MySQL 默认的事务隔离级别。另外，InnoDB 通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。

可串行化（SERIALIZABLE）：强制事务串行执行，解决了幻读的问题。由于需要在读取的每一行数据上都加锁，因此存在大量的超时和锁争用的问题。在实际中很少使用，只有在非常需要确保数据的一致性且可以接受没有并发的情况下，才考虑使用。

事务隔离级别

死锁是指两个或多个事务在同一个资源上相互占用，并请求锁定对方占用的资源。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁；多个事务同时锁定同一个资源时，也会产生死锁。书中给出了一个例子：

死锁的例子

当两个事务都同时执行完了第一条更新语句时，并锁定了该行。然后两个事务都去执行第二条语句，却发现已经被对方被锁定了。所以，这两个事务都在等待对方释放锁，并且同时持有对方需要的锁，陷入死循环。死锁发生后，只有部分或完全回滚其中一个事务，才能打破死锁。InnoDB 目前处理死锁的方法就是将持有最少行级排他锁的事务进行回滚。死锁的发生还和存储引擎的实现有关。

存储引擎在修改数据表时，只需要先修改表的内存拷贝，再通过事务日志将所做的修改顺序追加到磁盘上的事务日志中，而对数据表的修改可以在后台慢慢地刷回磁盘，这样提高了事务的执行效率。这种实现方式称为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

MySQL 提供了两种支持事务的存储引擎：InnoDB 和 NDB Cluster；比较知名的第三方的存储引擎，如 XtraDB 和 PBXT 也支持事务。

MySQL 默认采用自动提交模式，即如果不显示地开始一个事务，则每个查询都被当作一个事务执行提交操作。一些会导致大量数据改变的操作，如 Alter Table，Lock Tables（在服务层实现，和存储引擎无关） 等语句，在执行之前，会强制执行 commit 提交当前的活动事务。

MySQL 服务器层不管理事务，由底层的存储引擎实现事务。在同一个事务中，使用多种存储引擎是不可靠的。例如，在一个事务中混用了事务型和非事务型的表，非事务型表无法完成回滚操作，会导致数据库处于不一致的状态。

Lock Tables 和 UnLock Tables 语句是在服务器层实现的，有自己的用途，不能代替事务处理。

关于 MVCC。它是行级锁的一个变种，在很多情况下避免了锁操作，因此开销更低。因为 MVCC 并没有一个统一的实现标准，所以不同库的实现机制有所不同，但都实现了非阻塞的读操作，写操作也只是锁定必要的行。

MVCC 是通过保存数据在某个时间点的快照来实现的。不管事务执行多长时间，在一个事务中，它看到的数据都是一致的。而事务开始的时间不同，不同事务对同一张表，同一时刻看到的数据可能是不一样的。

InnoDB 实现的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现。一个保存了行的创建版本号（系统版本号，system version number），一个保存了行的过期（或删除）版本号。每开始一个事务，系统版本号会自动递增。事务开始时的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号作比较。对于不同的命令，MVCC 的实现描述如下：

命令 操作
select a. 只查找版本早于（小于或等于）当前事务版本的数据行。b. 行的删除版本未定义或者大于当前事务版本号
insert 为新插入的每一行保存当前版本号作为版本号
delete 为删除的每一行保存当前版本号作为删除标识
update 插入新的一行，保存当前系统版本号作为版本号；同时将当前系统版本号保存到原来的行作为删除标识

MVCC 只在可重复读（REPEATABLE READ）、提交读（READ COMMITTED）两个隔离级别下工作。因为 未提交读（READ UNCOMMITTED）总是读取最新的数据行，而不是符合当前事务版本的数据行；可串行化（SERIALIZABLE）则会对所有读取的行都加锁。

MVCC 多出的两列使得很多数据操作不用上锁，提升了性能；不足之处是需要额外的存储空间。

InnoDB 是 MySQL 的默认事务型存储引擎，也是最重要、使用最广泛的存储引擎。通过间隙锁使得 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入，从而防止幻读。

一般情况下，都应该使用 InnoDB 存储引擎，除非需要用到某些 InnoDB 不具备的特性。另外，不要混合使用多种存储引擎。对存储引擎的选择，可以考虑以下几个因素：事务、备份、崩溃恢复、特有的特性。当然，还要根据具体的使用场景决定使用何种存储引擎。InnoDB 对于 95% 以上的用户都是最佳选择。

三种可以转换表的存储引擎的方法：Alter Table、导出与导入、创建与查询。

第一章主要从整体层面上讲了关于 MySQL 数据库的各个方面，这也是我第一次系统地学习 MySQL，很有收获。

第四章

数据类型

选择正确的数据类型对于获得高性能至关重要，几个原则：

更小的通常更好：尽量选择能正确存储数据的最小数据类型。 简单就好：简单数据类型的操作通常需要更小的数据周期。例如，整型优于字符串，使用整型而非字符串来存储 ip。 尽量避免 NULL：NULL 使得索引、索引统计、值比较都比较复杂；需要更多的存储空间；需要特殊处理。

关于数据类型，整理了一个思维导图：

数据类型

设计表的时候有一些陷阱要避免：

  1. 太多的列。
  2. 太多的关联。单个查询最好在 12 个表以内做关联。
  3. 全能的枚举。防止过度使用枚举。
  4. 变相的枚举。
  5. 非此发明的 NULL。避免使用 NULL，但也不要走极端。

范式和反范式

在范式化的数据库中，每个事实数据都会出现并且只出现一次，相反，在反范式化的数据中，信息是冗余的，可能会存储在多个地方。

范式化的好处：

  • 范式化的更新操作通常比反范式化要快。
  • 当数据较好地范式化时，只有很少或者没有重复数据，所以只需要修改更少的数据。
  • 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
  • 很少有多余的数据意味着检索列表数据时更少需要 DISTINCT 或者 GROUP BY 语句。

范式化的缺点：

  • 范式化设计的 schema 的缺点是通常需要关联。
  • 范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

反范式的优点：

  • 数据通常在一张表中，可以很好地避免关联。
  • 单独的表也能使用更有效的索引策略。

真实世界中，经常混用范式化和反范式化设计。最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。实现上可以使用触发器更新缓存值，例如需要统计每个用户发了多少条消息，可以在 user 表中建一个 num_messages 列，每当用户发送消息时更新这个值。

缓存表和汇总表

为了提升读查询的速度，经常会需要一些额外索引，增加冗余列，甚至是创建缓存表和汇总表。这些方法虽然会增加写查询的负担，也需要额外的维护任务，但在设计高性能数据库时，这些都是常见的技巧：虽然写操作变慢了，同时还增加了读操作和写操作的开发难度，但是显著地提高了读操作的性能。

当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用。这就需要通过使用“影子表”来实现，“影子表”指的是一张在真实表“背后”创建的表。当完成了建表操作后，可以通过一个原子的重命名操作切换影子表和原表。

例如，需要重建 my_summary，可以先创建 my_summary_new，然后真充好数据，最后和真实表做切换：

影子表

在将 my_summary 这个名字分配给新建的表之前将原始的 my_summary 表重命名为 my_summary_old，就可以在下一次重建之前一直保留旧版本的数据。如果新表有问题，可以很容易地进行快速回滚操作。

加速 ALTER TABLE 操作

MySQL 的 ALTER TABLE 操作的性能对大表来说是个大问题。MySQL 执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能会花费很长时间，如果内存不足而表又很大，而且有很多索引的情况下尤其如此。

一般而言，大部分 ALTER TABLE 操作将导致 MySQL 服务中断。

对常见的场景，有两种技巧：

  • 先在一台不提供服务的机器上执行 ALTER TABLE 操作，然后和提供服务的主库进行切换。
  • 影子拷贝。用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

不是所有的 ALTER TABLE 操作都会引起表重建。例如，有两种方法可以改变或者删除一个列的默认值。一种很慢：

很慢

因为所有的 MODIFY COLUMN 操作都将导致表重建。

另一种则很快，它无需要改动表本身，只用修改 .frm 文件，因为列的默认值实际上就存在表的 .frm 中。 很快

第五章

索引是存储引擎用于快速找到记录的一种数据结构。索引优化应该是对查询性能优化的最有效的手段了。索引能够轻易地将查询性能提高几个数量级，“最优”的索引有时比一个“好的”索引性能要好两个数量级。创建一个真正“最优”的索引经常需要重写查询。

索引有很多种类型，可以为不同的场景提供更好的性能。在 MySQL 中，索引是在存储引擎层而不是服务器层实现的。

MySQL 支持 B-Tree 索引、哈希索引、空间数据索引（R-Tree）、全文索引。

B-Tree 索引使用 B-Tree 数据结构来存储数据。很多存储引擎，包括 InnoDB 使用的是 B+Tree。InnoDB 索引大致是这样工作的：

InnoDB 索引工作原理

B-Tree 对索引列是顺序组织存储的，所以很适合查找范围数据。支持：全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另一列、只访问索引的查询。

B-Tree 索引也有一些限制：

  1. 如果不是按照索引的最左列开始查找，则无法使用索引。
  2. 不能跳过索引中的列。
  3. 如果某个查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

在 MySQL 中，只有 Memory 引擎显式支持哈希索引。

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

索引的优点：

  1. 索引大大减少了服务器需要扫描的数据量。
  2. 索引可以帮助服务器避免排序和临时表。
  3. 索引可以将随机 IO 变为顺序 IO。

当然，索引也并不总是最好的工具。只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。非常小的表，大部分直接全表扫描更有效；中到大型的表，索引非常有效。特大型的表，则建立和使用索引的代价将随之增长，需要一种技术可以区分出查询需要的一组数据，如分区技术。

正确地创建和使用索引是实现高性能查询的基础。下面是一些如何高效使用索引的方式：

  • 独立的列 索引列不能是表达式的一部分，也不能是函数的参数。

  • 前缀索引和索引选择性 通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值和数据表的记录总数（#T）的比值，范围从 1/#T 到 1 之间。索引的选择性越高则查询效率越高，因为可以在查找时过滤掉更多的行。唯一索引的选择性是 1，性能最好。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于 BLOB、TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引，因为 MySQL 不允许索引这些列的完整长度。

前缀越长，选择性越高。同时又不能太长，以便节约空间。选择的诀窍就是前缀的基数应该接近于完整列的基数。

  • 多列索引

如果在 EXPLAIN 中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。

  • 选择合适的索引列顺序

  • 聚簇索引

  • 覆盖索引

如果一个索引包含（或者说是覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

第六章

查询的生命周期大致可以按照顺序来看：客户端 -> 服务器 -> 服务器解析 -> 生成执行计划 -> 执行 -> 返回结果给客户端。其中执行是最重要的阶段，包括大量为了检索数据到存储引擎的调用及调用后的数据处理，包括排序、分组等。

查询需要在这些不同的地方花费时间：网络、CPU 计算、生成统计信息和执行计划、锁等待、内存、IO 等。

如果把查询看成一个任务，由一系列子任务组成。优化查询，实际上就是要优化其子任务，要么消除一些子任务，要么减少子任务的执行次数，要么让子任务运行地更快。

查询性能低下最基本的原因是访问的数据太多，大部分性能低下的查询可以通过减少访问的数据量的方式进行优化。对于低效的查询，可以通过如下两个步骤进行分析：

  1. 确认应用程序是否在检索大量超过需要的数据。通常是访问了太多的行或列。
  2. 确认 MySQL 服务层是否在分析大量超过需要的数据行。

对于 MySQL，最简单的衡量查询开销的三个指标：响应时间、扫描的行数、返回的行数。

优化有问题的查询时，可以将查询转换一种写法让其返回一样的结果。例如：

  1. 一个复杂查询分成多个简单查询。
  2. 切分查询。

很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行。

查询执行路径

优化特定类型的查询：

  • 优化 COUNT() 查询 它有两个不同的作用：统计某个列值的数量、统计行数。注意：COUNT(*) 并不会扩展成所有的列，相反，这样的写法更清晰。

可用使用近似值、增加汇总表等来优化。

  • 优化关联查询
  • 优化子查询
  • 优化 GROUP BY 和 DISTINCT
  • 优化 LIMIT 分页

……

书中给出两个例子：使用 MySQL 构建一个队列表、计算两点之间的距离。

第十章

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台备库上，备库也可以配置成另外一台服务器的主库。

MySQL 支持两种复制方式：基于行、基于语句。两者都是通过重放二进制日志实现。

复制的用途：数据分布、负载均衡、备份、高可用性和故障切换、MySQL 升级测试。

所谓的二进制日志就是我们常说的 Binlog，复制的原理图：

复制原理

MySQL 按照事务提交的顺序而非每条语句的执行顺序来记录进进制日志，记录完日志后，主库会告诉存储引擎可以提交事务。

MySQL 配置复制的步骤：1. 在每台服务器上创建复制帐号；2. 配置主库和备库；3. 通知备库连接到主库并从主库复制数据。

基于语句的复制模式下，主库会记录那些造成数据更改的查询，当备库读取并重放这些事件时，实际上只是把主库上执行过的 SQL 再执行一遍。好处是实现简单，MySQLBinLog 工具是使用基于语句的日志的最佳工具。缺点是存在一些无法被正确复制的 SQL，例如使用 CURRENT_USER() 函数的语句等。

基于行的复制会将实际数据记录在进进制日志中，好处是可以正确地复制每一行。占用更少的 CPU，有些情况下的开销会比基于语句大。例如：

MySQL> UPDATE enormous_table SET col1 = 0;

基于行的复制更新一个黑盒，出问题时难以找到问题所在。

MySQL 能在这两种复制模式间动态切换。

设置 log_save_updates 选项可以让备库变成其他服务器的主库。

备库变主库

可以在任意个主库和备库之间建立复制，只有一个限制：每一个备库只能有一个主库。

最简单的拓扑结构是一主多备。还有主动-主动模式下的主-主复制，互为对方的主库和备库；主动-被动模式下的主-主复制；拥有备库的主-主结构；环形结构；拥有备库的环形结构。

备库会在主库上创建一个线程，并执行 binlog dump 命令，该命令会读取日志文件中的数据并将其发送给备库。因此备库足够多时，会对主库造成很大的负载。可以使用分发主库：

分发主库

复制只能扩展读操作，无法扩展写操作。

一旦配置了复制，监控和管理复制拓扑应该成为一项日常工作，不管有多少服务器。

MySQL 并不能很好地从服务器崩溃。掉电、磁盘损坏、内存或网络错误中恢复。大部分由于非正常关机后导致的复制问题都是由于没有把数据及时地刷到磁盘。

MySQL 复制是内建功能中的“瑞士军刀”，显著增加了 MySQL 的功能和可用性。

第十一章

容量可以简单地认为是处理负载的能力，从不同的角度来考虑负载很有帮助：数据量、用户量、用户活跃度、相对数据集的大小。

可扩展性是当增加资源以处理负载和增加容量时系统能够获得的投资产出率（ROI）。

当我们增加了一倍的服务器，结果增加了一倍的容量，这就是线性扩展。大部分系统并不是线性扩展，只能以比线性扩展略低的扩展系统进行扩展。

在扩展之前，可以做一些准备工作：优化性能、购买性能更强的硬件。

向上扩展比较简单，但很快会达到天花板。因此更多地考虑向外扩展。

向外扩展策略分为三个部分：复制、拆分、数据分片。

  • 复制：备库提供读。

  • 拆分：按功能拆分，不同节点执行不同的任务。

  • 数据分片：通常有一个数据库访问抽象层。如非必要，尽量不分片。目标是对那些重要并且频繁查询的数据减少分片。

  • 分区键：选择分区键的时候，尽可能选择那些能够避免跨分片查询的，但同时也要让分片足够小，以免过大的数据片导致问题。

  • 多个分片键：需要多个分区键并不意味着需要去设计两个完全冗余的数据存储。

  • 跨分片查询：可以借助汇总表来执行。

  • 分配数据、分片和节点：应该尽可能地让分片的大小比节点容量小很多，这样就可以在单个节点上存储多个分片。小分片更容易管理、便于迁移。

  • 在节点上部署分片

  • 固定分配：采用取模等方法。不灵活，修改分片策略比较难。

  • 动态分配：如采用一个表来指定 userID 被分到哪个分片。可以使用动态分配策略。

  • 混合动态分配和固定分配

  • 显式分配

  • 重新均衡分片数据
  • 生成全局唯一 ID
  • 分片工具

通过多实例扩展，每个实例监听不同的网络端口，或绑定到不同的 IP。

通过集群扩展：MySQL Cluster(NDB Cluster)、Clustrix、ScalaBase、GenieDB、Akiban。

向内扩展：处理不断增长的数据和负载最简单的办法是对不再需要的数据进行归档和清理。可以作为争取时间的短期策略。要注意：对应用的影响、要归档的行、维护数据一致性、避免数据丢失、解除归档。

保持活跃数据独立：

  1. 将表划分为几个部分，如将 users 表划分为 active_users 和 inactive_users。
  2. MySQL 分区。
  3. 基于时间的数据分区。

负载均衡的思路：在一个服务器集群中尽可能地平均负载量。

一个典型的读密集型网站负载均衡架构

如上图，包含两个负载均衡设备，一个用于 HTTP 流量，另一个用于 MySQL 访问。

负载均衡的五个常见目的：可扩展性、高效性、可用性、透明性、一致性。

在 MySQL 相关的领域里，负载均衡架构通常与数据分片及复制紧密相关。可以把负载均衡和高可用性结合在一起，部署到应用的任一层次上。

负载均衡的方法包括：直接连接、引入中间件、一主多备。

负载均衡的算法决定哪个服务器接受下一个连接，下面是一些可用的方法：随机、轮询、最少连接数、最快响应、哈希、权重。

在 MySQL 扩展策略方面，典型的应用在增长到非常庞大时，通常先从单个服务器转移到向外扩展的拥有读备库的架构，再到数据分片和/或按功能分区。