《高性能MySQL》&《MySQL技术内幕 InnoDB存储引擎》笔记

第一章 MySQL架构与历史

MySQL的架构

从上图可以看出，MySQL数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。需要注意的是，存储引擎是基于表的，而不是数据库的（即同一个数据库中的不同表可以有不同的存储引擎）。

MySQL是一个单进程多线程架构的数据库。

连接MySQL

连接MySQL是一个连接进程和MySQL数据库实例进行通信。从程序设计的角度来说，本质上是进程通信。

连接MySQL的方式有：TCP/IP套接字、命名管道和共享内存、UNIX域套接字。

InnoDB与MyISAM存储引擎对比

InnoDB支持事务、外键、行锁；支持非锁定读，即默认读取操作不会产生锁。
InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为REPEATABLE级别。
提供了插入缓冲，二次写，自适应哈希索引，预读等高性能和高可用的功能。
对于表中数据的存储，InnoDB存储引擎采用了聚集的方式，因此每张表数据的存储都是按照主键的顺序进行存放（这种表称为“索引组织表”）。

MyISAM（发音：my-z[ei]m）不支持事务、表锁设计，支持全文索引（InnoDB已经支持）。

MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择InnoDB表。当你的数据库主要以查询为主，相比较而言更新和写 入比较少，并且业务方面数据完整性要求不那么严格，就选择MyISAM表。

第二章 InnoDB存储引擎概述

内存

缓冲池

在数据库系统中，由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

（注：上图中左上角的日志缓冲应该为重做日志缓冲）
需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘。

重做日志缓冲

重做日志缓冲一般不需要设置的很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。默认为8MB.

系统在以下三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中：

• Master Thread每一秒将重做日志缓冲刷新到重做日志文件；
• 每个事务提交时会将重做日志缓冲刷新到重做日志文件；
• 当重做日志缓冲池剩余空间小与1/2时，重做日志缓冲刷新到重做日志文件。

Checkpoint技术

为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当由于宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中D（Durability 持久性）的要求。

Checkpoint技术是用来解决以下几个问题：

• 缩短数据库的恢复时间；
• 缓冲池不够用时，将脏页刷新到磁盘；
• 重做日志不可用时，刷新脏页。

当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复。这样就大大缩短了恢复的时间。

此外，当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷新回磁盘。

重做日志出现不可用的情况是因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的。重做日志可以被重用的部分是指这部分重做日志已经不再需要，即当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。若此时这部分重做日志还需要使用，那么必须强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

对于InnoDB存储引擎而言，其是通过LSN（Log Sequence Number）来标记版本的。而LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。

第三章 文件

日志文件

错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。MySQL DBA 在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录了一些警告信息或正确的信息。

慢查询日志

可以在MySQL启动时设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。该值默认为10秒。

查询日志

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。

二进制日志

二进制日志（binary log）记录了对MySQL数据库执行更改的所有操作。

二进制日志文件默认未开启。手动开启后会使系统性能下降大概1%.

但考虑到可以使用复制（replication）和point-in-time的恢复，这些性能损失绝对是可以且应该被接受的。

重做日志文件

在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。这两个文件就是重做日志文件，或者事务日志。

重做日志的目的：万一实例或者介质失败，重做日志文件就能派上用场。例如，数据库由于所在主机掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。

每个InnoDB存储引擎至少有一个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0、ib_logfile1。InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，当重做日志文件2也被写满时，会再被切换到重做日志文件1中。

重做日志与二进制日志的区别：

1. 二进制日志会记录所有与mysql数据库有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志，而InnoDB存储引擎的重做日志只记录有关其本身的事务日志，

2. 记录的内容不同，不管你将二进制日志文件记录的格式设为哪一种，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志；而InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况；

3. 写入的时间也不同，二进制日志文件是在事务提交前进行提交，即只写磁盘一次，不论这时该事务多大；而在事务进行的过程中，不断有重做日志条目被写入重做日志文件中。

第四章 之一 表

索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table）。在InnoDB存储引擎表中，每张表都有个主键，如果在创建表时没有显式定义主键，则InnoDB存储引擎会按如下方式选择或创建主键：

• 首先判断表中是否存在非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键；
• 如果不符合上述条件，InnoDB存储引擎会自动创建一个6字节大小的指针；

对于其他的一些数据库，如Microsoft SQL Server数据库，其中一种称为堆表的表类型，即行数据的存储按照插入的顺序存放。堆表的特性决定了堆表上的索引都是非聚集的。

需要牢记的是，B+树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页。数据库把页载入到内存，然后通过Page Directory再进行二叉查找。只不过二叉查找的时间复杂度很低，同时在内存中的查找很快，因此通常忽略这部分查找所用的时间。

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间(tablespace)。表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也称为（block），InnoDB存储引擎的逻辑存储结构大致如图：

VARCHAR

• MySQL数据库的VARCHAR类型可以存放65535字节数据（除去别的开销，实际最大可以存放65532字节）；
• VARCHAR(N)中的N是指字符数；
• 此外，此处65535长度是指所有VARCHAR列的长度总和，如果列的长度总和超出这个长度，依然无法创建，如：

CREATE TABLE test (
    a VARCHAR(22000),
    b VARCHAR(22000),
    c VARCHAR(22000)
) CHARSET = latin1

分区表

分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，都可独自处理，也可以作为一个更大对象的一部分进行处理。

当前MySQL数据库支持以下几种类型的分区：

• RANGE分区：行数据基于属于一个给定连续区间的列值放入分区；
• LIST分区：和RANGE类似，只是LIST分区里面是离散的值；
• HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数；
• KEY分区：根据MySQL数据库提供的（即内置的）哈希函数进行分区。

分区和性能

数据库应用分为两类：一类是OLTP（在线事务处理），如Blog，电子商务，网络游戏等；另一类是OLAP（在线分析处理），如数据仓库，数据集市。在一个实际的应用环境中，可能既有OLTP的应用，也有OLAP的应用。如网络游戏中，玩家的操作的游戏数据库应用就是OLTP的，但是游戏厂商可能需要对游戏产生的日志进行分析，通过分析得到的结果来更好地服务于游戏，预测玩家的行为等，而这却是OLAP的应用。

对于OLAP的应用，分区的确可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表，其中有一个时间戳属性列。用户的查询需要从这张表中获取一年的数据。如果按时间戳进行分区，则只需要扫描相应的分区即可。

然而对于OLTP的应用，分区应该非常小心。在这种应用下，通常不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录即可。而根据B+树索引的原理可知，对于一张大表，一般的B+树需要2~3次的磁盘IO。因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。

例如：很多开发团队会认为含有1000W行的表是一张非常大的表，所以他们往往会采用分区，如对主键做10个HASH的分区，这样每个分区就只有100W的数据了，因此查询应该变快了，如SELECT * FROM TABLE WHERE PK=@pk。但是有没有考虑过这样一种情况：100W和1000W行的数据本身构成的B+树的层次都是一样的可能都是2层。那么上述走主键分区的索引并不会带来性能的提高。如果1000W的B+树高度是3,100W的B+树的高度是2，那么上述按主键分区的索引可以避免1次IO，从而提高查询效率。这没问题，但是这张表只有主键索引，没有任何其他的列需要查询的，如果还有类似如下的SQL语句：SELECT * FROM TABLE WHERE KEY = @key,这时对于KEY的查询需要扫描所有的10个分区，即使每个分区的查询开销为2次IO，则一共需要20次IO。而对于原来单表的设计，对于KEY的查询只需要2~3次IO。

这里，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。

——没有全局的索引，所以才需要遍历每个分区的索引。

第四章 之二 Schema与数据类型优化

选择优化的数据类型

• 更小的通常更好；更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少；
• 简单就好；例如，整形比字符串操作代价更低；实用内建类型而不是字符串来存储日期和时间；用整形存储IP地址等；
• 尽量避免NULL；如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL 的列使得索引、索引统计和值比较都更复杂。尽管把可为NULL的列改为NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该尽量避免设计成可为NULL的列；

字符串类型

VARCHAR 和 CHAR

VARCHAR是最常见的字符串类型。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，MyISAM会将行拆成不同的片段存储；InnoDB则需要分裂页来使行可以放进页内。

下面这些情况使用VARCHAR是合适的：字符串的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。

CHAR适合存储很短的字符串，或者所有值都接近同一个长度，如密码的MD5值。对于经常变更的数据，CHAR也比VARCHAR更好，因为CHAR不容易产生碎片（行间碎片？）。

慷慨是不明智的

使用VARCHAR(5)和VARCHAR(200)存储"hello"的空间开销是一样的。那么使用更短的列有什么优势吗？

事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或其他操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。

所以最好的策略是只分配真正需要的空间。

BLOB 和 TEXT

BLOB和TEXT都是为存储很大的数据而设计的数据类型，分别采用二进制和字符方式存储。

与其他类型不同，MySQL把每个BLOB和TEXT值当做一个独立的对象去处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length个字节而不是整个字符串做排序。同样的，MySQL也不能将BLOB或TEXT列全部长度的字符串进行索引。

选择表示符（identifier）

整数类型通常是标识列的最佳选择，因为它们很快并且可以使用AUTO_INCREMENT。
如果可能，应该避免使用字符串类型作为标识列，因为它们很耗空间，并且比数字类型慢。
对于完全随机的字符串也需要多加注意，例如MD5(),SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢：

• 因为插入值会随机的写入到索引的不同位置，所以使得INSERT语句更慢。这会导致叶分裂、磁盘随机访问。
• SELECT语句会变的更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
• 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的局部性原理失效。

第五章 创建高性能的索引 & 索引与算法

B+树索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在2~4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO。

数据库中的B+树索引可以分为聚集索引和辅助索引。聚集索引的叶子结点存放的是一整行记录，而辅助索引叶子结点存放的是主键值。

许多数据库的文档这样告诉读者：聚集索引按照顺序物理地存储数据。但是试想一下，如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常之高。所以，聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表连接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。（《MySQL技术内幕 InnoDB存储引擎》）

InnoDB只聚集在同一个页面中数据，包含相邻键值的页面可能相距甚远。（高性能MySQL）

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。

索引的类型

B-Tree索引

B+树，所有叶子节点在同一层，每一个叶子节点包含指向下一个叶子结点的指针。

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。

其中，索引对多个值进行排序的顺序是与定义索引时列的顺序一致的。

B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

• 全字匹配：和索引中的所有列进行匹配，如查找姓名为Cuba Allen、出生于1960-01-01的人；
• 匹配最左前缀：即只使用索引的第一列，如查找所有姓为Allen的人；
• 匹配列前缀：匹配某一列的值的开头部分，如查找所有以J开头的姓的人。这里只使用了索引的第一列；
• 匹配范围值：如查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列；
• 精确匹配某一列并范围匹配另一列：如查找所有姓为Allen，并且名字是字母K开头的人。即第一列全匹配，第二列范围匹配；
• 只访问索引的查询：覆盖索引；

如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人。类似的，也无法查找姓以某个字母结尾的人。

不能跳过索引中的列。也就是说，上述索引无法用于查找姓为Smith并且在某个特定日期出生的人。

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如查询WHERE 姓='Smith' AND 名 LIKE 'J%' AND 出生日期='1976-12-23'，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件。（如果范围查询列值的数量有限，那么可以使用多个等于条件来代替范围条件）

到这里读者应该可以明白，前面提到的索引列的顺序是多么重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时保持指向数据行的指针。

在MySQL中，只有Memory引起显示支持哈希索引。

• 哈希索引数据并不是按照索引数据顺序存储的，所以无法用于排序；
• 哈希索引页不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；
• 哈希索引只支持等值比较查询，不支持任何范围查询；

InnoDB引擎有个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。这是一个完全自动的、内部的行为。

索引的优点

最常见的B-Tree索引，按照顺序存储数据，所以可以用来做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成查询。据此特性，总结下来索引有如下三大优点：

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

评价一个索引是否适合某个查询的“三星系统”：

• 索引将相关的记录放到一起则获得一星；
• 索引中的数据顺序和查找中的排列顺序一致则获得二星；
• 索引中的列包含了查询需要的全部列则获得三星；

高性能的索引策略

独立的列

索引列不能是表达式的一部分，也不能是函数的参数，否则不会使用索引。

如：SELECT actor_id FROM actor WHERE actor_id + 1 = 5

SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。但有时候这样做还不够，还可以做些什么呢？
通常可以索引开始的部分字符，这样可以大大节约空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为可以在查询时过滤掉更多的行。唯一索引的选择性是1.

诀窍在与既要选择足够长的前缀以保证较高的选择性，同时又不能太长，以便节约空间。

多列索引

很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序建立多列索引。

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列（在没有ORDER BY 或 GROUP BY的情况下）。
例如，在超市的销售记录表中：SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584，很自然的customer_id的选择性更高些，所以多列索引的顺序应该是(customer_id, staff_id)。

这样做有一个地方需要注意，查询的结果非常依赖与选定的具体值。例如，一个应用通常都有一个特殊的管理员账号，系统中所有其他用户都是这个用户的好友，所以系统通常通过它向网站的所有其他用户发送状态和其他消息。这个账号巨大的好友列表很容易导致网站出现服务器性能问题。

这实际上是一个非常典型的问题。任何的异常用户，不仅仅是那些用于管理应用的设计糟糕的账号会有同样的问题；那些拥有大量好友、图片、状态、收藏的用户，也会有前面提到的系统账号同样的问题。

从这个小案例可以看到经验法则和推论在多数情况下是有用的，但要注意不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引在同一个结构中保存了B-Tree索引和数据行。

在InnoDB中，聚簇索引“就是”表。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

InnoDB只能通过主键聚集索引！
如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

MySQL中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

聚簇索引有一些重要的优点：

• 可以把相关的数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件可能都会导致一次磁盘I/O。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。

同时，聚簇索引也有一些缺点：

• 聚簇索引最大限度提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖插入顺序。按照主键的顺序插入式加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致移动行的时候，可能面临叶分裂的问题。
• 二级索引访问需要两次索引查找，而不是一次。

MyISAM/InnoDB的主键索引和二级索引

MyISAM的主键索引和（所有其他的）二级索引的叶子节点中保存的都是指向行的物理位置的指针。

InnoDB的主键索引的叶子结点是数据行；（所有其他的）二级索引的叶子节点中保存的是主键值。
这样的策略减少了当出现行移动或数据页分裂时二级索引的维护工作。使用主键值当做指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无需更新二级索引中的这个“指针”。

如果正在使用的InnoDB表没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就可以使用索引来直接获取列的数据，这样就不再需要读取数据行。我们称这样的索引为覆盖索引。

例如，表inventory有一个多列索引（store_id, film_id），MySQL如果只需要访问这两列，就可以使用这个索引做覆盖索引，如SELECT store_id, film_id FROM inventory.

利用索引扫描来做排序

只有当索引的列顺序和ORDER BY子句的列顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求。

有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。

例如，索引：UNIQUE KEY idx（rental_date, inventory_id, customer_id）

下面这个查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：

WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id DESC;

下面这个查询也没问题，因为ORDER BY使用的两列就是索引的最左前缀：

WHERE rental_date > '2005-05-25' ORDER BY rental_date , inventory_id;

下面是一些不能使用索引做排序的查询：

• 下面这个查询使用了两种不同的排序方向，但是索引列都是正序排序的：
  • WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;
• 下面这个查询的ORDER BY子句中引用了一个不在索引中的列：
  • WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;
• 下面这个查询的WHERE和ORDER BY中的列无法组合成索引的最左前缀：
  • WHERE rental_date = '2005-05-25' ORDER BY customer_id ;
• 下面这个查询在索引列的第一列上是范围条件，所以MySQL无法使用索引的其余列：
  • WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;

冗余和重复索引

重复索引是指在相同的列上按相同的顺序创建相同类型的索引。如：

CREATE TABLE test (
    ID INT NOT NULL PRIMARY KEY,
    A INT NOT NULL,
    B INT NOT NULL,
    UNIQUE(ID),
    INDEX(ID)
) ENGINE=InnoDB;

事实上，MySQL的主键约束和唯一约束都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。

冗余索引和重复索引有一些不同。如果创建了索引（A, B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引，索引（A, B）也可以当做索引（A）来使用。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引（如扩展索引（A）为（A，B））。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变大太大，从而影响其他使用该索引的查询的性能。

一般来说，增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。

第六章 锁

开发多用户、数据库驱动的应用时，最大的一个难点是：一方面要最大程度地利用数据库的并发访问，另一方面还要确保每个用户能以一致的方式读取和修改数据。为此就有了锁的机制，同时这也是数据库系统区别于文件系统的一个关键特性。

锁机制用于管理对共享资源的并发访问。

InnoDB存储引擎中的锁

锁的类型

InnoDB存储引擎实现了如下两种标准的行级锁：

• 共享锁（S Lock），允许事务读一行数据；
• 排它锁（X Lock），允许事务删除或更新一行数据。

此外，InnoDB存储引擎支持多粒度锁定，这种锁允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁（Intention Lock）。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。

例如，如果需要对记录r上X锁，那么分别需要对数据库A、表、页上意向锁IX，最后对记录r上X锁。若其中任何一个步骤导致等待，那么该操作需要等待粗粒度锁的完成。举例来说，在对记录r加X锁之前，已经有事务对表1进行了S锁，而当前事务需要对表1上IX锁，由于不兼容，所以该事务需要等待表锁操作的完成。

一致性非锁定读

一致性的非锁定读（consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制的方式来读取当前时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这是读取操作不会因此去等待行上锁的释放。相反的，InnoDB存储引擎会去读取行的一个快照数据。

快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

可以看到，非锁定读机制极大地提高了数据库的并发性。在InnoDB存储引擎的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。

一个行记录可能有不止一个快照数据，一般称这种技术为多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。

在READ COMMITTED事务隔离级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据（违反了事务ACID中的I的特性，即隔离性）。而在REPEATABLE READ事务隔离级别下，一致性非锁定读总是读取事务开始时的行数据版本。

一致性锁定读

在某些情况下，用户需要显式的对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT的只读操作。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读操作：

• SELECT ... FOR UPDATE（X锁）
• SELECT ... LOCK IN SHARE MODE（S锁）

锁的算法

行锁的3种算法

InnoDB存储引擎有3种行锁的算法，分别是：

• Record Lock：单个行记录上的锁；
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身；
• Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身；

InnoDB对于行的查询都是采用这种Next-Key Lock锁定算法。

但查询的索引含有唯一属性时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住记录本身，而不是范围。如SELECT * FROM t WHERE pk = 5 FOR UPDATE；

解决Phantom Problem

在默认的事务隔离级别下，即REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking机制来避免Phantom Problem（幻读）。

Phantom Problem是指在同一事务下，连续两次执行相同的SQL语句可能会导致不同的结果，第二次的SQL语句可能会返回之前不存在的行（重点在记录数不一样）。

如下SQL语句：SELECT * FROM t WHERE pk > 2 FOR UPDATE，第一次返回a=5这条记录；若这时另一个事务插入了4这个值，那么第二次执行时将返回4和5.这与第一次得到的结果不同，违反了事务的隔离性，即当前事务能够看到其他事务的结果。

InnoDB存储引擎采用Next-Key Locking的算法避免Phantom Problem。对于上述SQL语句，其锁住的不是5这个单值，而是对(2, +∞)这个范围加了X锁。因此任何对于这个范围的插入都是不被允许的，从而避免Phantom Problem。

锁问题

脏读

所谓脏数据是指事务对缓冲池中行记录的修改，并且还没有被提交。

脏读指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。

脏读在生产环境中并不常见。脏读发生的条件是需要事务的隔离级别为READ UNCOMMITTED，而目前绝大多数的数据库都至少设置成READ COMMITTED。

不可重复读

不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问该数据集合，并做了一些DML操作。这样，由于第二个事务的修改，第一个事务两次读到的数据可能是不一样的，这种情况称为不可重复读。

不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据。

一般来说，不可重复读的问题是可接受的，因为其读到的数据是已经提交的，本身不会带来很大的问题。因此，很多数据库厂商，如Oracle、Microsoft SQL Server将其数据库事务的默认隔离级别设置为READ COMMITTED，在这种隔离级别下允许不可重复读的现象。

幻读

一个事务按相同的查询条件查询之前检索过的数据，确发现检索出来的结果集条数变多或者减少（由其他事务插入、删除的），类似产生幻觉。

注意到，Repeatable Read（可重复读）隔离级别仍然避免不了幻读。

By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 13.6.8.5, “Avoidingthe Phantom Problem Using Next-Key Locking”).

13.2.8.5. Avoiding the PhantomProblem Using Next-Key Locking
To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking.
You can use next-key locking to implement a uniqueness check in your application:If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the success or of your row during the read prevents anyone mean while inserting a duplicate for your row. Thus, the next-key locking enables you to “lock” the nonexistence of something in your table.

我的理解是说，InnoDB提供了next-key locks，但需要应用程序自己去（手动）加锁。manual里提供一个例子：

SELECT * FROM child WHERE id> 100 FOR UPDATE;

这样，InnoDB会给id大于100的行（假如child表里有一行id为102），以及100-102，102+的gap都加上锁。
可以使用show engine innodb status来查看是否给表加上了锁。

在InnoDB存储引擎中，通过使用Next-Key Locking算法来避免不可重复读的问题。
——避免了不可重复读，即实现了可重复读，亦即实现了事务的隔离性。

——避免了不可重复读，即实现了可重复读，亦即实现了事务的隔离性。

——避免了不可重复读，即实现了可重复读，亦即实现了事务的隔离性。

第七章 事务

事务（Transaction）是数据库区别于文件系统的重要特性之一。

事务概述

理论上说，事务有着极其严格的定义，它必须同时满足四个特性，即通常所说的事务的ACID特性。值得注意的是，虽然理论上定义了严格的事务要求，但是数据库厂商出于各种目的，并没有严格去满足事务的ACID标准。如Oracle数据库，其默认的事务隔离级别是READ COMMITTED，不满足隔离性的要求。对于InnoDB存储引擎而言，其默认的事务隔离级别是READ REPEATABLE，完全遵循和满足事务的ACID特性。

A（Atomicity），原子性

原子性指整个数据库事务是不可分割的工作单位。事务中的所有数据库操作要么全部成功，要么全部撤销。

C（Consistency）一致性

一致性指事务将数据库从一种一致的状态转变为下一种一致的状态。在事务开始之前和结束之后，数据库的完整性约束没有破坏。

I（Isolation）隔离性

隔离性还有其他的称呼，如并发控制、可串行化、锁等。

事务的隔离性要求，事务提交前对其他事务不可见。通常这使用锁来实现。

D（Durability），持久性

事务一旦提交，其结果就是永久性的。即使发生宕机等事故，数据库也能将数据恢复。

事务的实现

事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。

redo和undo都可以视为一种恢复操作，redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。因此两者记录的内容不同，redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。

redo

重做日志用来实现事务的持久性（所以关于上面原子性的实现，有待商榷）。其由两部分组成：一是内存中的重做日志缓冲，其是易失的；二是重做日志文件，其是持久的。

InnoDB是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化。

为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，为了确保日志写入磁盘（因为这里有一个文件系统缓存），必须进行一次fsync操作。由于fsync的效率取决于磁盘的性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。

InnoDB存储引擎允许用户手工设置非持久性的情况发生，以此提高数据库的性能。即当事务提交时，日志不写入重做日志文件，而是等待一个时间周期后再执行fsync操作。这可以显著提高数据库的性能，但是当数据库发生宕机时，由于部分日志文件未写入磁盘，因此会丢失最后一段时间的事务。

LSN

LSN是Log Sequence Number的缩写，其代表的是日志序列号。在InnoDB存储引擎中，LSN占用8字节，并且单调递增。LSN代表的含义有：

• 重做日志写入的总量
• checkpoint的位置
• 页的版本

LSN表示事务写入重做日志的字节总量。例如，当前重做日志的LSN是1000，事务T1写入了100字节的重做日志，LSN就变成1100，又有事务T2写入200字节的重做日志，那么LSN变成：1300。可见LSN记录的是重做日志的总量，其单位是字节。

每个页的头部也有一个LSN，记录的是该页最后刷新时LSN的大小。重做日志记录的是每个页的物理更改日志，因此页中的LSN用来判断是否需要进行恢复操作。例如：页的LSN为10000，数据库启动时，写入重做日志的LSN为13000，表明该事务已经提交，数据库需要恢复；重做日志中的LSN小于页中的LSN，不需要进行重做，因为页中的LSN表示已经刷新到该位置。

恢复

InnoDB存储引擎在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作。

undo

重做日志记录了事务的行为，可以很好的通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要undo。如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。

除了回滚操作，undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过读取之前的行版本信息，以此实现非锁定读。**

最后也是最为重要的一点是，undo log也会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。

purge

purge用于最终完成delete和update操作。这样设计是因为InnoDB存储引擎支持MVCC，所以记录不能在事务提交时立即进行处理。这时其他事务可能正在引用该行，故InnoDB存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过purge来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的delete操作。

group commit

若事务为非只读事务，则每次事务提交时需要进行一次fsync操作，以此保证重做日志都已经写入了磁盘。然而磁盘的fsync性能是有限的，为了提高效率，当前数据库都提供了group commit功能，即一次fsync可以刷新确保多个事务日志被写入文件。

（实现用了队列和流水线）

备份与恢复

备份类型

根据备份的方法不同：

• Hot Backup（热备）：在数据库运行中直接备份
• Cold Backup（冷备）：在数据库停止的情况下备份
• Warm Backup（温备）：同样是在数据库运行中进行，但会对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性

根据备份后文件的内容：

• 逻辑备份：备份出的文件内容是可读的，一般是文本文件，内容一般是由一条条SQL语句，或者是表内实际数据组成。这类方法的好处是可以观察导出的文件的内容，一般适用于数据库的升级、迁移等工作。但其缺点是恢复所需要的时间往往较长。
• 裸文件备份：复制数据库的物理文件。这类备份的恢复时间往往较逻辑备份短很多。

按照备份数据库的内容来分：

• 完全备份：对数据库进行一个完整的备份
• 增量备份：在上次完全备份的基础上，对于更改的数据进行备份
• 日志备份：对MySQL数据库二进制日志的备份，通过对一个完全备份进行二进制日志的重做来完成数据库的point-in-time的恢复工作。

MySQL数据库复制（replication）的原理就是异步实时地将二进制日志传送并应用到从（slave/standby）数据库。

但是对于真正的增量备份来说，只需要记录当前每页最后的检查点的LSN，如果大于之前全备时的LSN，则备份该页，否则不用备份，这大大加快了备份的速度和恢复的时间。

快照备份

通过写时复制技术来创建快照。当创建一个快照时，仅复制原始卷中数据的元数据，并不会有数据的物理操作，因此快照的创建过程是非常快的。当快照创建完成，原始卷上有写操作时，快照会跟踪原始卷块的改变，将要改变的数据在改变之前复制到快照预留的空间里，因此这个原理的实现叫做写时复制。而对于快照的读取操作，如果读取的数据块是创建快照后没有修改过的，那么会将读操作直接重定向到原始卷上；如果要读取的是已经修改过的块，则将读取保存在快照中该块在修改之前的数据。因此，采用写时复制机制保证了读取快照时得到的数据与快照创建时一致。

B区块被修改了，因此历史数据放入了快照区域。读取快照数据时，A、C、D块还是从原有卷中读取，而B块就需要从快照读取了。

复制（replication）

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。

MySQL内建的复制功能是构建基于MySQL的大规模、高性能应用的基础，这类应用使用所谓的“水平扩展”的架构。我们可以通过为服务器配置一个或多个备库的方式来进行数据同步。复制功能不仅有利于构建高性能的应用，同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。事实上，可扩展性和高可用性通常是相关联的话题。

复制（replication）是MySQL数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication的工作分为以下3个步骤：

1. 主服务器（master）将数据更改记录到二进制日志中
2. 从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。
3. 从服务器重做中继日志的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

复制+快照的备份架构

复制可以用来作为备份，但其功能不仅限于备份，其主要功能如下：

• 数据分布。由于MySQL数据库提供的复制并不需要很大的带宽，因此可以在不同的数据中心之间实现数据的拷贝。
• 读取的负载均衡。通过建立多个从服务器，可将读取平均地分布到这些从服务器中，从而减少主服务器的压力。一般可以通过DNS的Round-Robin和Linux的LVS功能实现负载平衡。
• 数据库备份。复制对备份很有帮助，但是从服务器不是备份，不能完全代替备份。
• 高可用性和故障转移。通过复制建立的从服务器有助于故障转移，减少故障的停机时间和恢复时间。

可见，复制的设计目的不是简简单单用来备份的，并且只用复制来进行备份是远远不够的。

假设当前应用采用了主从式的复制架构，从服务器用来作为备份，一个不太有经验的DBA执行了误操作，如DROP DATABASE或者DROP TABLE，这时从服务器也跟着运行了，那这时如何从服务器进行恢复呢？一种比较好的方法是通过对从服务器上的数据库所在的分区做快照，以此来避免复制对误操作的处理能力。当主服务器上发生误操作时，只需要恢复从服务器上的快照，然后再根据二进制日志执行point-in-time的恢复即可。因此，快照+复制的备份架构如下图所示：

发送复制事件到其他备库

log_slave_updates选项可以让备库变成其他服务器的主库。在设置该选项后，MySQL会将其执行过的事件记录到它自己的二进制日志中。

为什么要指定服务器ID，难道MySQL在不知道复制命令来源的情况下不能执行吗？为什么MySQL要在意服务器ID是全局唯一的。问题的答案在于MySQL在复制过程中如何防止无限循环。当复制SQL线程读中继日志时，会丢弃事件中记录的服务器ID和该服务器ID相同的事件，从而打破了复制过程中的无限循环。在某些复制拓扑结构下打破无限循环非常重要，例如主-主复制结构。

复制拓扑

几个基本原则：

1. 一个备库只能有一个主库
2. 每个备库必须有一个唯一的服务器ID
3. 一个主库可以有多个备库
4. 如果打开了log_slave_updates选项，一个备库可以把其主库上的数据变化传播到其他备库

一主库多备库

这是最简单的拓扑结构。在有少量写和大量读时，这种配置是非常有用的。可以把读分摊到多个备库上，直到备库给主库造成了太大的负担，或者主备之间的带宽成为瓶颈为止。

尽管这是非常简单的拓扑结构，但它非常灵活，能满足多种需求：

• 为不同的角色使用不同的备库（如添加不同的索引或使用不同的存储引擎）
• 把一台备库当做待用的主库
• 将一台备库放到远程数据中心，用作灾难恢复
• 使用其中一个备库，作为备份、培训、开发或者测试使用服务器

这种结构流行的原因是它避免了很多其他拓扑结构的复杂性。例如在同一个逻辑点停止所有备库的复制，它们正在读取的是主库上同一个日志文件的相同物理位置。这是个很好的物理特性，可以减轻管理员的许多工作，例如把备库提升为主库。

主动-主动模式下的主-主复制

主-主复制（也叫双主复制或双向复制）包含两台服务器，每一个都被配置成对方的主库和备库，换句话说，它们是一对主库。这样，任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

主动-主动模式下的主-主复制有一些应用场景，但通常用于特殊的目的。一个可能的应用场景是两个处于不同地理位置的办公室，并且都需要一份可写的数据拷贝。

这种配置最大的问题是如何解决冲突，两个可写的互主服务器导致的问题非常多。例如两台服务器同时修改一行记录，或同时在两台服务器上向一个包含AUTO_INCREMENT列的表里插入数据。

总的来说，允许向两台服务器上写入所带来的麻烦远远大于其带来的好处，但下面描述的主动-被动模式则会非常有用。

主动-被动模式下的主-主复制

这和上面的主要区别在于其中一台服务器是只读的被动服务器。

这种方式使得反复切换主动和被动服务器非常方便,因为服务器是配置是对称的。这使得故障转移和故障恢复很容易。它也可以让你在不关闭服务器的情况下执行维护,优化表,升级操作系统（或者应用程序、硬件等）或其他任务。

例如，执行ALTER TABLE操作可能会锁住整个表，阻塞对表的读和写，则可能会花费很长时间并导致服务中断。然而在主-主配置下，可以先停止主动服务器上的备库复制线程（这样就不会在被动服务器上执行任何更新），然后在被动服务器上执行ALTER操作，交换角色，最后在先前的主动服务器上启动复制线程。这个服务器将会读取中继日志并执行相同的ALTER语句。这可能花费很长时间，但不要紧，因为该服务器没有为任何活跃查询提供服务。

拥有备库的主-主结构

这种配置的优点是增加了冗余，对于不同地理位置的复制拓扑，能够消除站点单点失效的问题。你也可以像平常一样，将读查询分配到备库上。

主库、分发主库以及备库

当备库足够多时，会对主库造成很大的负担。因此，如果需要多个备库，一个好办法是从主库移除负载并使用分发主库。分发主库也是一个备库，它的唯一目的就是提取和提供主库的二进制日志。多个备库连接到分发主库，这使原来的主库摆脱了负担。为了避免在分发主库上做实际的查询，可以将它的表修改为blackhole存储引擎。

树或金字塔形（或称级联复制架构）

如果正在将主库复制到大量的备库中，不管是把数据分发到不同的地方，还是提供更高的读性能，使用金字塔结构都能够更好地管理。

这样设计的好处是减轻了主库的负担，就像前面提到的分发主库一样。它的缺点是中间层出现任何错误都会影响到多个服务器。如果每个备库和主库直接相连就不会出现这样的问题。同样，中间层次越多，处理故障会更困难、更复杂。

定制的复制方案

选择性复制

为了利用访问局部性原理，并将需要读的工作集驻留在内存中，可以复制少量数据到备库中。如果每个备库只拥有主库的一部分数据，并且将读分配给备库，就可以更好的利用备库的内存。并且每个备库也只有主库一部分的写入负载，这样主库的能力更强并能保证备库延迟。

这个方案有点类似于下面我们会讨论到的水平数据划分，但它的优势在于主库包含了所有的数据集，这意味着无须为了一条查询去访问多个服务器。如果读操作无法在备库上找到数据，还可以通过主库来查询。即使不能从备库上读取所有数据，也可以移除大量的主库读负担。

最简单的方法是在主库上将数据划分到不同的数据库里，然后将每个数据库复制到不同的备库上。

分离功能

许多应用都混合了在线事务处理（OLTP）和在线分析处理（OLAP），OLTP查询比较短并且是事务型的，OLAP查询则通常很大，也很慢，并且不要求绝对最新的数据。这两种查询给服务器带来的负担完全不同，因此它们需要不同的配置，甚至使用不同的存储引擎或者硬件。

一个常见的办法是将OLTP的数据复制到专门为OLAP工作准备的备库上。这些备库可以有不同的硬件、配置、索引或者不同的存储引擎。

复制和容量规划

写操作通常是复制的瓶颈，并且很难通过复制来扩展写操作。当计划为系统增加复制容量时，需要确保进行了正确的计算，否则很容易犯一些复制相关的错误。

例如，假设工作负载为20%的写以及80%的读，服务器支持每秒1000次查询，那么应该增加多少备库才能处理当前两倍的负载，并将所有的读查询分配给备库？四倍呢？

看上去应该增加两个备库并将1600次读操作平分给它们，但不要忘记，主库的写操作同样会在备库上执行。400次写操作，只剩600次读操作，所以需要三台备库。

四倍负载时，将有800次写入，这时备库只有200次读每秒，就需要16台备库来处理3200次读查询。

这远远不是线性扩展，查询数量增加4倍，却需要增加17倍的服务器。这说明当为单台主库增加备库时，将很快达到投入远高于回报的地步。

复制只能扩展读操作，无法扩展写操作。对数据进行分片是唯一可以扩展写操作的方法。

测量备库延迟

一个比较普遍的问题是如何监控备库落后主库的延迟有多大（SHOW SLAVE STATUS输出的Seconds_behind_master由于各种原因/缺陷几乎不可用）。

最好的解决方案是使用heartbeat record（心跳记录），这是一个在主库上会每秒更新一次的时间戳。为了计算延时，可以直接用备库当前的时间戳减去心跳记录的值。

MySQL二进制日志转储线程并没有通过轮询的方式从主库请求事件，而是由主库来通知备库新的事件，因为前者低效且缓慢。从主库读取一个二进制日志事件是一个阻塞型网络调用，当主库记录事件后，马上就开始发送。因此可以说，只要复制线程被唤醒并且能够通过网络传输数据，事件就会很快到达备库。

可扩展的MySQL

什么是可扩展性

人们常常把诸如“可扩展性”、“高可用性”以及性能等术语在一些非正式场合用作同义词，但事实上它们是完全不同的。

性能：响应时间
可扩展性：当增加资源以处理负载和增加容量时系统能够获得的投资产出率

Scale Up：向上扩展/垂直扩展，购买更多强悍的硬件，以增加已有服务器的性能；
Scale Out：向外扩展/水平扩展，将任务分配到多台计算机上；

向外扩展/水平扩展

最简单也最常见的向外扩展的方法是通过复制将数据分发到多个服务器上，然后将备库用于读查询。这种技术对于以读为主的应用很有效。它也有一些缺点，例如重复缓存。

另外一个比较常见的向外扩展方法是将工作负载分布到多个“节点”。

在MySQL架构中，一个节点（node）就是一个功能部件。如果没有规划冗余和高可用性，那么一个节点可能就是一台服务器。如果设计的是能够故障转移的冗余系统，那么一个节点通常可能是下面的某一种：

• 一个主-被模式下的主-主复制双机结构
• 一个组库和多个备库
• 一个主动服务器，并使用分布式复制块设备（DRBD）作为备用服务器
• 一个基于存储区域网络（SAN）的“集群”

1，按功能拆分

按功能拆分，或者说按职责拆分，意味着不同的节点执行不同的任务。将独立的服务器或节点分配给不同的应用，这样每个节点只包含它的特定应用所需要的数据。

例如，在门户网站，可以浏览网站新闻、论坛，寻求支持和访问知识库等，这些不同功能区域的数据可以放到专用的MySQL服务器中。

归根结底，还是不能通过功能划分来无限地进行扩展，因为如果一个功能区域被捆绑到单个MySQL节点，就只能进行垂直扩展。其中的一个应用或者功能区域最终增长到非常庞大时，都会迫使你去寻求一个不同的策略。如果进行了太多的功能划分，以后就很难采用更具扩展性的设计了。

2，数据分片

在目前用于扩展大型MySQL应用的方案中，数据分片是最通用且最成功的的方法。它把数据分割成一小片，或者说一小块，然后存储到不同的节点中。

数据分片在和某些类型的按功能划分联合使用时非常有用。大多数分片系统也有一些“全局的”数据不会被分片（例如城市列表或者登录数据）。全局数据一般存储在单个节点上，并且通常保存在类似memcached这样的缓存里。

事实上，大多数应用只会对需要的数据做分片——通常是那些将会增长得非常庞大的数据。假设正在构建的博客服务，预计会有1000万用户，这时候就无需对注册用户进行分片，因为完全可以将所有的用户（或者其中的活跃用户）放到内存中。假如用户数达到5亿，那么就可能需要对用户数据分片。用户产生的内容、例如发表的文章和评论，几乎肯定需要进行数据分片，因为这些数据非常庞大，而且会越来越多。

分片技术和大多数应用的最初设计有着显著的差异，并且很难将应用从单一数据存储转换为分片架构。如果在应用设计初期就已经预计到分片，那实现起来就容易得多。

许多一开始没有建立分片架构的应用都会碰到规模扩大的情形。例如，可以使用复制来扩展博客服务的读查询，直到它不再奏效。然后可以把服务器划分为三个部分：用户信息、文章，以及评论。可以将这些数据放到不同的服务器上（按功能划分）。

最后，可以通过用户ID来对文章和评论进行分片，而将用户信息保留在单个节点上。

如果事先知道应用会扩大到很大的规模，并且清楚按功能划分的局限性，就可以跳过中间步骤，直接从单个节点升级为分片数据存储。

为什么选择数据分片存储？

因为如果想扩展写容量，就必须切分数据。如果只有单台主库，那么不管有多少备库，写容量都是无法扩展的。对于上述缺点而言，数据分片是我们的首选解决方案。

3，选择分区键

一个好的分区键常常是数据库中一个非常重要的实体的主键。这些键值决定了分片单元。例如，如果通过用户ID或客户端ID来分割数据，分片单元就是用户或者客户端。

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

4，多个分区键

许多应用拥有多个分区键，换句话说，应用需要从不同的角度看到有效且连贯的数据视图。这意味着某些数据在系统内至少需要存储两份。

例如，需要将博客应用的数据按照用户ID和文章ID进行分片，因为这两者都是应用查询数据时使用比较普遍的方式。试想一下这种情形：频繁的读取某个用户的所有文章，以及某个文章的所有评论。如果按照用户分片就无法找到某篇文章的所有评论（需要遍历所有分片查询），而按文章分片则无法找到某个用户的所有文章。

需要多个分区键并不意味着需要去设计两个完全冗余的数据存储。
例如，假设为用户数据和书籍数据都设计了分片数据存储。而评论同时拥有用户ID和评论ID，这样就跨越了两个分片的边界。实际上却无需冗余存储两份评论数据。替代方案是，将评论和用户数据一起存储，然后把每个评论的标题和ID与书籍数据存储在一起。这样在渲染大多数关于某本书的评论的视图时无须同时访问用户和书籍数据存储，如果需要显示完整的评论内容，可以再从用户数据存储中获得。

5，跨分片查询

大多数分片应用多少都有一些查询需要对多个分片的数据进行聚合或关联操作。例如，一个读书俱乐部网站要显示最受欢迎或最活跃的用户，就必须访问每一个分片。如何让这类查询很好的执行，是实现数据分片的架构中最困难的部分。虽然从应用的角度来看，这是一条查询，但实际上需要拆分成多条并行执行的查询，每个分片上执行一条。

普遍的做法是使用C或Java编写一个辅助应用来执行查询并聚合结果集。也可以借助汇总表来实现。

跨分片查询并不是分片面临的唯一难题。维护数据一致性同样困难。外键无法在分片间工作，因此需要由应用来检查参照一致性，或者只在分片内使用外键，因为分片的内部一致性可能是最重要的。还可以使用XA事务，但由于开销太大，现实中使用很少。

6，分配数据、分片和节点

应尽可能的让分片的大小比节点容量小很多，这样就可以在单个节点上存储多个分片。

保持分片足够小更容易管理。这将使数据的备份和恢复更加容易，如果表很小，那么像更改表结构这样的操作会更加容易。例如，有一个100GB的表，你可以直接存储，也可以将其划分成100个1GB的分片，并存储在单个节点上。现在假如要向表上增加一个索引，在单个100GB的表上的执行时间会比100个1GB分片上执行的总时间更长，因为1GB的分片更容易全部加载到内存中。并且在执行ALTER TABLE时还会导致数据不可用，阻塞1GB的数据比阻塞100GB的数据要好得多。

小一点的分片也便于转移。这有助于重新分配容量，平衡各个节点的分片。

8，固定分配

将数据分配到分片中有两种主要的方法：固定分配和动态分配。两种方法都需要一个分区函数，使用行的分区键作为输入，返回存储该行的分片。

固定分配使用的分区函数仅仅依赖于分区键的值。哈希函数和取模运算就是很好的例子。

9，动态分配

假设有一个表，包括用户ID和分片ID：

CREATE TABLE user_to_shard (
    user_id INT NOT NULL,
    shard_id INT NOT NULL,
    PRIMARY KEY (user_id)
);

这个表本身就是分区函数。

负载均衡

负载均衡的基本思路很简单：在一个服务器集群中尽可能地平均负载量。通常的做法是在服务器前端设置一个负载均衡器。然后负载均衡器负责将请求路由到最空闲的服务器。

在与MySQL相关的领域里，负载均衡架构通常和数据分片及复制紧密相关。例如，可以在MySQL Cluster集群的多个SQL节点上做负载均衡，也可以在多个数据中心间做负载均衡，其中每个数据中心又可以使用数据分片架构，每个节点实际上是拥有多个备库的主-主复制对结构，这里又可以做负载均衡。

复制上的读写分离

MySQL复制产生了多个数据副本，你可以选择在备库还是主库上执行查询。由于备库复制是异步的，因此主要的难点是如何处理备库上的脏数据（主库已修改，而备库没有来得及同步更新的数据）。

如果不太关心数据是否是脏的，可以使用备库，而对需要即时数据的请求则使用主库。我们将这称为读写分离。

常见的读写分离方法如下：

• 基于查询分离
  • 将所有不能容忍脏数据的读和写查询分配到主库或主动服务器上，其他的读查询分配到备库或被动服务器上。（然而只有很少的查询能够容忍脏数据）
• 基于脏数据分离
  • 让应用检查复制延迟，以确定备库数据是否太旧
• 基于会话分离
  • 判断用户是否修改了数据。用户不需要看到其他用户的最新数据，但需要看到自己的更新。可以在会话层设置一个标志位，标记是否做了更新，若是则将该用户的查询在一段时间内总是指向主库。这是我们通常推荐的策略，因为它是在简单和有效之间的一种很好的妥协。如果有足够的想象力，可以把基于会话的分离方法和复制延迟监控结合起来。如果用户在10秒前更新了数据，而所有备库的延迟在5秒内，就可以安全地从备库中读取数据。
• 基于版本分离
  • 这和基于会话的分离方法相似：你可以跟踪对象的版本号或时间戳，通过比较从备库读取的对象版本或时间戳来判断数据是否足够新。例如，用户发表了一篇文章后，可以更新用户的版本，这样就会从主库去读取数据了。

大多数读写分离解决方案都需要监控复制延迟来决策读查询的分配。