微信技术群：Day9884125

一 mysql架构与历史

1.1 并发控制

简单介绍mysql如何控制并发读写。如果两个进程在同一时刻对同一资源进行操作，那么数据就会破坏。一般会进行加锁解决，如果资源被其他线程锁住，那就必须等待，知道所释放下一个线程才可以访问。这种锁的方案，虽然可以解决线程安全，但是不支持并发处理，因为任意一个时刻，只有一个进程可以处理数据，这在并发下是一个问题。

1.1.1 读写锁

在多线程读取数据的时候没有这样的麻烦。因为读取数据不会修改数据，所以不会报错。但是如果某个线程在读取数据，另一个线程试图修改数据，就会导致结果是不确定的。读的客户可能报错退出，也可能读到的数据不一致。
解决这类经典问题就是并发控制。其实很简单，处理并发读或者写时，可以通过实现一个由两种类型的锁系统来解决问题。这两种类型的锁通常被称为共享锁（shared lock）和排他锁(exclusive lock)，也叫读锁(read lock)和写锁(write lock)。
读锁是共享的互不阻塞的，写锁是排他的，也就是一个写锁会阻塞其他的写锁和读锁。
在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，mysql会通过锁定防止其他用户读取同一数据。大多数时候，mysql锁的内部管理都是透明的。

1.1.2 锁粒度

一种提高共享资源并发性的方式就是让锁定对象更具有选择性。尽量只锁定需要修改的部分数据。锁定的数据量越少，则系统的并发程度越高，只要互相之间不发生冲突即可。
加锁也是需要消耗系统资源的。锁的各种操作，包括获取锁、检查锁是否已经解除、释放锁等，都会增加系统的开销。如果系统花费大量的时间来管理锁，而不是存取数据，那么系统的性能就可能受到影响。
所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡也会影响到性能。大多数商业数据库系统没有提供更多的选择，一般都是在表上加行级锁，并以各种复杂的方式实现，以便锁比较多的情况下提高更好的性能。
mysql提供了多种选择，每种存储引擎都有自己的锁策略和锁粒度。在存储引擎的设计中，锁管理是非常重要的。可以为某些特定的场景提供更好的性能。但是也会失去对其他场景的良好支持。好在mysql支持多个存储引擎。

1.1.2.1 表锁

表锁是nysql中最基本的锁策略，并且是开销最小的策略。它会锁定整张表。
尽管存储引擎可以管理自己的锁，mysql本身还是会使用各种有效的表锁来实现不同的目的。例如服务器为例如table之类的语句使用表锁，而忽略存储引擎的锁机制。

1.1.2.2 行锁

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

1.2 事务

事务就是一组原子性sql查询，或者说一个独立的工作单元。如果数据库引擎能够成功的对数据库应用该组查询的全部语句，那么执行该组查询。如果其中任何一条语句因为崩溃或其他什么原因无法执行，那么所有的语句都不会执行。也就是说，要么全部执行成功，要么全部执行失败。
可以使用start transaction语句开始一个事务，然后要么使用commit提交事务将修改的数据持久化保留，要么使用rollback撤销所有的修改。事务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表示原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)。一个运行良好的事务处理系统，必须具备这些标准特征。
这种事务处理过程中额外的安全性，也会需要数据库系统做更多的额外工作。

1.2.1 隔离级别

隔离性其实比想象的更为复杂。在sql标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改。哪些在事务内和事务间是可见的，那些不可见的。较低级别的隔离通常可以执行更高级别的并发，系统开销也更低。

1.2.1.1 四种隔离级别

未提交读(read uncommitted)
在read uncommitted级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。从性能上来说，read uncommitted不会比其他的级别好太多，但却缺乏其他级别的很多好处，除非真的有必要，在实际应用中一般很少使用。
提交读(read committed)
大多数数据库系统的默认隔离级别都是read committed(但mysql不是)read committed满足前面提到的隔离性的简单定义：一个事务开始时，只能看到已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读（nonrepeatable read）,因为两次执行同样的查询，可能会得到不一样的结果。
可重复读（repeatable read）
repeatable read解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读的问题。所谓幻读，指的是当某个事务在读取某个范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生换行。Innodb和XtraDB存储引擎通过多版本并发控制解决了幻读的问题。可重复度是mysql的默认事务隔离级别。
可串行化
serializable是最高的隔离级别。它是通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，serializable会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

1.2.2 死锁

死锁是指两个或者多个事务在同一资源上互相占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。例如，设想下面两个事务同时处理StockPrice表：
事务1

start transaction;
update StockPrice set close = 45.50 where stock_id=4 and date = '2002-05-01';
update StockPrice set close = 19.80 where stock_id=3 and date = '2002-05-02';
commit;

事务2

start transaction;
update StockPrice set close = 20.12 where stock_id=3 and date = '2002-05-02';
update StockPrice set close = 47.20 where stock_id=4 and date = '2002-05-01';
commit;

如果凑巧，两个事务都执行了第一条update语句，更新了一行数据，同时也锁定了该行数据，接着每个事务都尝试去执行第二条update语句，却发现该行已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环。除非有外部因素介入才可能解除死锁。
为了解决这种问题，数据库系统实现了各种死锁监测和死锁超时机制。越复杂的系统，比如ionnodb存储引擎，越能监测到死锁的循环依赖，并立即返回一个错误。这种解决方式很有效，否则死锁会导致出现非常慢的查询。还有一种解决方式，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好。Innodb目前处理死锁的方式是，将持有最少行级排他锁的事务进行回滚（这是比较简单的死锁回滚算法）。
锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。死锁的产生有双重原因，有些因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由于存储引擎的实现方式导致的。
死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务的系统，这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

1.2.3 事务日志

事物日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改的行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说快一些。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称为预写日志，修改数据需要写两次磁盘。
如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统奔溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。

1.2.4 mysql中的事务

mysql提供了两种事务型的存储引擎，Innodb和NDB Cluster。另外还有一些第三方存储引擎也支持事务，比较知名的包括XtraDB和PBXT。后面详细讨论它们各自的一些特点。

1.2.4.1 自动提交

mysql默认采用自动提交模式。也就是说，如果不是显式的开始一个事务，则每个查询都将被当作一个事务执行提交操作。在当前链接中，可以通过设置autocommit变量来启用或者禁用字段提交模式

-- 查询是否提交的状态
show variables like 'autocommit';

-- 修改自动提交的状态、
set autocommit=1；

1或者ON表示启用，0或者OFF表示禁用。当autocommit=0时，所有的查询都是在一个事务中，直到显式的执行commit提交或者rollback回滚，该事务结束，同时开始了另一个新的事务。修改autocommit对非事务型表，比如myisam或者内存表，不会有任何影响。对这类表来说，没有commit或者rollback的概念，也可以说是相当于一直处于autocommit启用的模式。
另外有一些命令，在执行前会强制执行commit提交当前的活动事务。如果有需要，可以检查对应版本的官方文档来确认。
mysql可以通过执行set transaction isolation level命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别。

set session transaction isolation level read committed;

mysql能够识别所有的4个ANSI隔离级别，InnoDB引擎也支持所有的隔离级别。

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

mysql服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。
如果在事务中混合使用了事务型和非事务型的表，在正常提交的情况下是不会有什么问题的。
但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要。
在非事务的表上执行事务相关操作的时候，mysql通常不会报错。有时候回滚的时候才会发出一个警告。但大多数情况下，对非事务型表的操作都不会有提醒。

1.2.6 隐式和显式锁定

InnoDB采用的是两阶段锁定协议，在事务执行过程中，随时都可以执行锁定，锁只有在执行commit或者rollback的时候才会释放。前面描述的锁定都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。
另外，InnoDB也支持通过特定的语句进行显式锁定，这些语句不属于sql规范：
mysql也支持lock tables和unlock tables语句，这是在服务器层实现的，和存储引擎无关。他们有自己的用途，但并不能替代事务处理。如果应用需要用到事务，还是应该选择事务型存储引擎。
经常发现，应用已经将表从myisam转换到InnoDB，但还是显式的使用Lock tables语句。这不但没有必要，还会严重影响性能，实际InnoDB的行级锁工作的更好。
lock tabes和事务之间互相影响的话，情况会变得非常复杂，在某些mysql版本中甚至会产生无法预料的结果。因此，本书建议，除了事务中禁用了autocommit，可以使用lock tables之外，其他任何时候都不要显式的执行lock tables，不管用什么存储引擎。

1.3 多版本并发控制

mysql的大多数事务性存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，他们一般都同时实现了多版本并发控制。不仅是mysql，包括oracle、postgresql等其他数据库系统也都实现了mvcc（多版本并发控制），但各自的实现机制不尽相同，因为mvcc没有一个统一的实现标准。
可以认为mvcc是行级锁的变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
mvcc的实现，是通过保存数据在某个时间点的快照来实现的，也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
前面说到不同存储引擎的mvcc实现是不同的，典型的有乐观并发控制和悲观并发控制。
InnoDB的mvcc，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面是repeatable read隔离级别下，mvcc具体的操作。
select
InnoDB会根据以下两个条件检查每行记录：

InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的
行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的记录，才能返回作为查询结果
Insert
InnoDB为新插入的每一行保存当前版本号作为行版本号。
delete
InnoDB为删除的每一行保存当前系统版本号作为删除标识。
update
InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。
保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。
mvcc只在repeatable read和read committed两个隔离级别下工作。其他两个隔离级别都和mvcc不兼容，因为read uncommitted总是读取最新的数据行，而不是符合当前事务版本的数据行。而serializable则会对所有读取的行都加锁。

1.4 mysql的存储引擎

本节只概括描述mysql存储引擎，具体使用请阅读相关存储引擎的官方文档。
在文件系统中，mysql将每个数据库（也可以称为schema）保存为数据目录下的一个子目录。创建表时，mysql会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。例如创建一个名为MyTable的表，mysql会在MyTable.frm文件中保存该表的定义。因为mysql使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。在windows中，大小写是不敏感的；而在unix中则是敏感的。不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在mysql服务层统一处理的。
可以使用show table status命令（在mysql5.0以后的版本中，也可以查询information_schema中对应的表）显示表的相关信息。例如，对于mysql数据库中的user表。

show table status like 'user' \G

name
表名
engine
表的存储引擎类型。在旧的版本中，该列的名字叫type，而不是engine。
row_format
行的格式。对于myisam表，可选的值为dynamic、fixed、或者compressed。dynamic的行长度的字段，如varchar或blob。fixed的行长度则是固定的，只包含可变长度的列，如char或integer。compressed的行则只在压缩表中存在。
rows
表中的行数。对于myisam和其他一些存储引擎，该值是精确的，但对于innoDB，该值是估计值。
avg_row_length
平均每行包含的字节数
data_length
表数据的大小（以字节为单位）
max_data_length
表数据的最大容量，该值和存储引擎有关。
index_length
索引的大小。（以字节为单位）
data_free
对于myisam表，表示已分配但目前没有使用的空间。这部分空间包括了之前删除的行，以及后续可以被insert利用到的空间。
auto_increment
下一个auto_increment的值。
create_time
表的创建时间
update_time
表数据的最后修改时间
check_time
使用ckeck table命令或者myisamchk工具最后一次检查表的时间。
collation
表的默认字符集和字符列排序规则。
checksum
如果启用，保存的是整个表的实时校验和
create_options
创建表时指定的其他选项

create_options
该列包含了一些其他的额外信息。对于myisam表，保存的是表在创建时带的注释。对于InnoDB表，则保存的是InnoDB表空间的剩余空间信息。如果一个是视图，则该列包含VIEW的文本字样。

1.4.1 InnoDB存储引擎

InnoDB是mysql的默认事务型引擎。它被设计用来处理大量的短期事务，短期事务大部分情况是正常提交的，很少被回滚。

1.4.2 InnoDB概述

InnoDB的数据存储在表空间（tablespace）中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是repeatable read（可重复读），并且通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB表是基于聚簇索引建立的。InnoDB的索引结果和mysql的其他的存储引擎有很大的不同，聚簇索引对主键查询有很高的性能。不过他的二级索引中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引比较多的话，主键应当尽可能的小。

1.4.3 大数据量

什么样的数据量算大？我们创建或管理的很多InnoDB数据库的数据量在3-5TB之间，或者更大，这是单台机器上的量，不是一个分片的量。这些系统运行的还不错，要做到这一点需要合理的选择硬件，做好物理设计，并为服务器的I/O瓶颈做好规划。
如果数据量继续增长到10TB以上的级别，可能就需要建立数据仓库。Infobright是mysql数据仓库最成功的解决方案。也有一些大数据库不适合Infobright，却可能适合TokuDB。

1.4.4 转换表的引擎

有很多方法可以将表的存储引擎转换成另一种引擎。每种方法都有自己的优点和缺点

1.4.4.1 alter table

将表从一个引擎修改为另一个引擎最简单的方法是使用alter table语句。下面的语句将mytable的引擎修改为InnoDB:

alter table mytable engine=InnoDB;

这个语句适用任何存储引擎。但有一个问题，需要执行很长的时间。mysql会按行将数据从原表复制到一个新的表中，在复制期间可能会消耗系统所有的I/O能力。同时原表上加读锁。所以，在繁忙的表上执行此操作要特别小心。一个替代方案是采用接下来将讨论的导出和导入的方法，手工进行表的复制。
转换表的存储引擎，会失去和原引擎相关的所有特性。例如，如果一张InnoDB表转换成myisam，然后再转换回InnoDB，原InnoDB表上所有的外键将丢失。

1.4.4.2 导入与导出

为了更好的控制转换的过程，可以使用mysqldump工具将数据导出到文件，然后修改文件中create table语句的存储引擎选项，注意同时修改表名，因为同一个数据库中不能存在相同的表名，即使他们使用的不同存储引擎。同时要注意mysqldump默认会自动在create table语句前加上drop table语句，不注意这一点可能丢失数据。

1.4.4.3 创建与查询

第三种转换技术综合了第一种方法的高效和第二种方法的安全。不需要导出整个表的数据，而是先创建一个新的存储引擎的表，然后利用Insert...select语法来导出数据：

create table innodb_table like myisam_table;
alter table innodb_table engine=InnoDB;

数据量不大的话，这样做很好。如果数据量很大，则可以考虑分批处理，针对每一段数据执行事务提交操作，以避免大事务产生过多的undo。假设有主键字段id，重复运行以下语句（最小值x和最大值y进行相应的替换）将数据导入到新表。

start transaction;
insert into innodb_table select * from myisam_table where id brtween x and y;
commit;

这样操作完以后，信标是原表的一个全量复制，原表还在，如果需要可以删除原表。如果有必要，可以在执行的过程中对原表加锁，以确保新表和原表的数据一致。
Percona Toolkit提供了一个pt-on-line-schema-change的工具（基于Facebook的在线schema变更技术），可以比较简单、方便的执行上述过程，避免手工操作可能导致的失误和繁琐。

二服务器性能分析

3.1 性能优化简介

3.2 对应用程序进行性能分析

3.3 分析mysql查询

3.4 诊断间歇性问题

3.5 其他分析工具

3.6 总结

三 Schema与数据类型优化

4.1 选择优化的数据类型

4.2 mysql schema设计中的陷阱

4.3 范式和反范式

4.4 缓存表和汇总表

4.5 加快alter table操作的速度

4.6 总结

四创建高性能的索引

5.1 索引基础

5.2 索引的优点

5.3 高性能的索引策略

5.4 索引案例学习

5.5 维护索引和表

5.6 总结

五查询性能优化

6.1 为什么查询速度会慢

6.2 慢查询基础：优化数据访问

6.3 重构查询的方式

6.4 查询执行的基础

6.5 mysql查询优化器的局限性

6.6 查询优化器的提示（hint）

6.7 优化特定类型的查询

6.8 案例学习

六 mysql高级特性

7.1 分区表

7.2 试图

7.3 外键约束

7.4 在mysql内部存储代码

7.5 游标

7.6 绑定变量

7.7 用户自定义函数

7.8 插件

7.9 字符集和校对

7.10 全文索引

7.11 分布式(XA)事务

7.12 查询缓存

7.13 总结

七优化服务器设置

确定合适配置的最短路径，最好从了解mysql内部结构和行为开始。，然后你可以使用这些知识作为如何配置mysql指南。最后，你可以将期望的配置和当前配置进行比较，并纠正任何重要和值得修改的差异。
人们经常会问：我的服务器有32GB的RAM和12个CPU核，最佳配置文件是什么？不幸的是，事情并没有那么简单。你应该根据工作负载、数据和应用程序需求来配置服务器，而不仅仅是根据硬件配置。mysql有很多可以更改但不应该更改的设置。通常更好的做法是正确的配置基本设置（在大多数情况下，只有少数设置是重要的），并将更多的时间花在schema优化、索引和查询设计上。在正确设置mysql的基本配置选项之后，从而进一步的更改中获得潜在收益通常很小。
另外修改配置的潜在缺点可能是巨大的。mysql的默认设置是有充分理由的。在不了解其影响的情况下进行更改可能会导致奔溃、卡顿或性能下降。因此，你永远不应该盲目的相信来自热门帮助网站（如mysql论坛或stack overflow）的某些人所报告的最佳配置。应该始终通过阅读相关的官方手册来检查任何更改并仔细测试。
那么该怎么办？首先应该确保InnoDB缓存池和日志文件大小等基本设置时合适的。然后，如果你想防止不希望的行为出现，应该设置一些安全选项（但请注意，这些通常不会提高性能，只会避免问题），然后保持其他的设置不变。如果遇到问题，首先要仔细诊断。
如果问题是由服务器的某个部分引起的，而该部分的行为可以通过配置选项进行纠正，那么可能需要对其进行更改。
有时还需要设置特定的配置选项，这些选项在特殊情况下可能会对性能产生重大影响。但是，这些不应该是基本服务器配置文件的一部分。应该只在发现他们解决的特定性能问题时，才设置他们。这就是为什么我们不建议通过寻找不好的方面来改进配置选项的原因。如果需要改进配置，应该会在查询响应时间中体现出来。最好从查询及其响应时间开始分析，而不是从配置选项开始。这可以节省很多时间，避免很多问题。
另一个节省时间和避免麻烦的好方法是使用默认设置，除非你明确知道不应该使用默认设置。很多默认设置都是安全的，很多人都会直接使用。这使默认设置成为测试最彻底的设置。当没必要改变这些设置而改变他们时，可能会引起意想不到的错误。

八操作系统和硬件优化

许多不同的硬件组件都会影响mysql的性能，我们看到的最常见的瓶颈是CPU耗尽。当mysql尝试并行执行太多的查询，或者少量的查询在CPU上运行太长时间时，就可能会发生cpu饱和。
I/O饱和也会发生，但比cpu耗尽的频率低的多。主要因为普遍使用了固态硬盘(SSD)
内存耗尽，但通常只在你试图将太多内存分配给mysql时才会发生

8.1 如何为mysql选择CPU

在升级当前硬件或购买新硬件时，应该考虑工作负载是否受CPU限制。可以通过检查CPU限制，但不要只查看CPU的总体负载，而是要查看最重要查询的CPU使用率和I/O之间的平衡，并注意CPU负载是否均匀。
一般来说，你的服务器要达到两个目标。
低延迟（快速响应时间）
为此需要更快的CPU，因为每个查询将只使用一个CPU。
高吞吐
如果可以同时运行多个查询，那么可以使用多个CPU为查询提供服务。
如果工作负载没有用完所有的CPU资源，mysql可以使用剩余的CPU来执行后台任务，比如清理InnoDB缓存区、执行网络操作等。然而，与执行查询相比，这些任务通常是次要的。

8.2 平衡内存和磁盘资源

配置大内存的主要原因并不是为了在内存中保存大量数据，而是为了避免磁盘I/O，因为磁盘I/O比访问内存中的数据要慢几个数量级。重要的是平衡内存和磁盘空间大小、速度、成本和其他因素，以便让工作负载获得良好的性能。

8.3 缓存、读取和写入

如果有足够的内存，可以完全避开磁盘读取操作。如果所有数据都能装入内存，那么一旦服务器的缓存预热完成，每次读取都将是一次缓存命中。在这种情况下，仍然会从内存中进行逻辑读取，但不会从磁盘中进行物理读取。然而写入是另一回事。写入可以像读取一样在内存中执行，但迟早必须被写入磁盘，才能持久保留数据。换句话说，缓存可以延迟写操作，但缓存不能像消除读操作那样消除写操作。
事实上，除了允许写操作延迟之外，缓存还允许他们以两种重要的方式组合在一起。
多次写操作，一次刷新
一个数据片段可以在内存中被多次更改，而无需每次都将新值写入磁盘。当数据最终被刷新到磁盘时，自上次物理写入以来发生的所有修改都将被持久化。例如，许多语句可以更新内存中的计数器。如果计数器被更新了100次，然后写入磁盘，则100次内存修改被合并为一次磁盘写入。
I/O合并
许多不同的数据片段可以在内存中被修改，这些修改可以被收集在一起，因此物理写可以作为单个磁盘操作执行。
这就是为什么许多事务系统使用提前写日志策略的原因，提前写日志允许在内存中更改页面，而不用将更改刷新到磁盘，这通常涉及随机I/O，速度非常慢。相反，他们将更改的记录写入顺序日志文件，这样要快的多。后台线程可以稍后将修改过的页面刷新到磁盘，这样做可以优化写操作的性能。
写操作从缓存中获益，因为可以将随机I/O转换为顺序I/O。异步（缓存）写操作通常由操作系统处理，并且是被成批处理的，因此可以更优的被刷新到磁盘。同步（无缓存）写入必须等待数据落盘。这就是为什么廉价磁盘冗余阵列（RAID）控制器的电池保护回写缓存能提升写入性能的原因

九复制

9.1 关于术语的说明

长久以来，mysql的用户对复制技术相关的术语“主库”（master）和“从库”（slave）非常熟悉。这些术语已经被“源”（source）和“副本”（replica）所代替。

9.2 复制概述

复制解决的基本问题是让一台服务器的数据与其他服务器的数据保持同步。它的实现机制可以这样概括，首先在源服务器上，任何数据修改和数据结构变更的事件都会被写入日志文件中，然后，副本服务器从源服务器上的日志文件中读取这些事件并在本地重放执行。这是一个异步处理的过程，也就是说，并不能保证副本服务器上的数据是最新的（不能保证源服务器和副本服务器的数据一致性）。复制延迟（副本数据和最新数据之间的时间差）也并没有上限。一个大的sql查询语句可能会导致副本服务器落后于源服务器几秒钟、几分钟，甚至几小时。mysql的复制基本上是向后兼容的，新版本的服务器可以作为老版本的服务器的副本，但反过来，将老版本的服务器作为新版本的服务器的副本是不可行的，因为它无法解析新版本的新特性或sql语法，而且复制使用的文件格式也可能存在差异。例如，从5.6升级到5.7，或从5.7升级到8.0，最好先对复制的设置进行测试。但对于小版本号的升级，如从5.7.34升级到5.7.35，则通常是兼容的。阅读每次版本的更新日志可以找到不同版本之间的具体变化。
通过复制可以将读操作指向副本来获得更好的读扩展性，但除非设计得当，否则并不适合通过复制来扩展写操作。在一主库多副本库的架构中，写操作会被执行多次，这时候整体系统的性能取决于写入最慢的那部分。
下面是复制比较常见的用途
数据分发
mysql的复制通常不会对带宽造成很大的压力，在后面的内容中，你将看到基于行的复制会比传统的基于语句的复制模式的带宽压力更大。你可以随意地停止或开始复制，并在不同的地理位置来分布数据备份，例如不同的数据中心。即使在不稳定的网络环境下，远程复制也可以工作。但如果为了保持很低的复制延迟，最好有一个稳定的、低延迟连接。
读流量扩展
通过mysql复制可以将读操作分布到多台服务器上，实现对读密集型应用的优化，并且实现很方便，通过简单的代码修改就能实现基本的负载均衡。对于小规模的应用，可以简单的对机器名做硬编码或使用DNS轮询（将一个机器名指向多个IP地址）。当然也可以使用更复杂的方法，例如网络负载均衡这一类的标准负载均衡解决方案，他们能够很好的将负载分配到不同的mysql服务器上。
备份
复制是一项有助于备份的有价值的技术，但副本不是备份，也不能够取代备份。
分析与报告
为报告/分析（在线分析处理，OLAP）查询使用专用的副本是一项很好的策略，可以很好的隔离此类查询产生的压力，以避免对满足外部客户需求的在线业务产生影响。复制可以很好的实现此类隔离。
高可用性和故障切换
复制有助于避免mysql成为应用程序中的单点故障，一个包含复制的设计良好的故障切换系统能够显著的缩短宕机时间。

9.3 复制如何工作

mysql是如何复制数据的。在这里，我们使用最简单的复制拓扑结构，单一源服务器和单一副本服务器。
总的来说，复制有三个步骤:

源端把数据更改记录到二进制日志中，称之为“二进制日志事件”（binary log events）。
副本将源上的日志复制到自己的中继日志中。
副本读取中级日志中的事件，将其重放到副本数据之上。

在复制框架中，读取和重放日志事件是解耦的，这就允许读取日志和重放日志异步进行，也就是说，这里的I/O线程和sql线程是可以独立运行的。

9.4 复制原理

9.4.1 选择复制格式

mysql提供了三种不同的二进制日志格式用于复制，基于语句的、基于行的和混合模式。可以通过系统参数binlog_format控制日志写入时使用哪种日志格式。
基于语句的复制是通过记录所有在源端执行的数据变更语句来实现的。当副本从中继日志读取到事件并执行时，实际上是重新执行在源端执行过的sql语句。这种格式的主要优点是简单且紧凑。一条更新了大量数据的sql语句，在二进制日志中可能仅仅需要几十字节存储。其中最大的弊端则在于会遇到某些具有“不确定性”的sql语句问题。假设有一条语句删除了一张有1000行记录的表中的100行，但没有用order by子句。如果在源和副本上，记录的排序不同，这条sql语句在源和副本上删除的100条记录就会不同，这将导致数据不一致。
基于行的复制将事件写入二进制日志，该事件包含了该行记录发生了什么改变。这听起来很简单，但是，因为这种模式的确定性，相比基于语句的方式来说，其实发生了很大的变化。使用基于行的复制，通过查看二进制日志中的事件，可以看到究竟是哪一行记录发生了什么样的改变。在基于语句的复制模式下，sql语句在执行时被解析，服务器在执行时找到所有需要变更的记录进行操作。而在基于行的复制模式下，每条被改变的记录都会作为事件被写入二进制日志，这可能会让二进制日志的大小发生巨大的增长。
“混合模式”试图结合以上两种格式的优点。在这种模式下，事件的写入，默认使用基于语句的格式，仅在需要时才切换到基于行的格式。我们说试图时因为这种模式真的非常努力，在写入每个事件时会有很多的判断条件，以确定使用哪种格式，而这也会导致二进制日志中出现不可预测的事件。我们认为二进制日志数据应该是两种模式选其一，而不应该是两种的混合。
我们建议坚持使用基于行的复制，除非某些场景下明确需要临时使用基于语句的复制。基于行的复制提供了最安全的数据复制方法。

9.4.2 全局事务标识符

在mysql5.6之前，副本必须追踪连接到源时读取的二进制日志文件和日志位置。例如，一个副本连接到上游源并从文件binlog.000002的2749位置读取数据。当副本从该二进制日志中读取事件时，它每次都会向后推进日志位点。如果就在这时，故障发生了。比如数据库崩溃了，必须从备份中重建数据。那么问题来了，在源端，如果二进制日志位点重新开始，怎么能重新将副本连接到源库？确定从哪个位点开始连接是一个非常复杂的过程。如果指向的位点太早，那么副本上就会获得重复的事件，如果指向的位点太晚，则会漏掉事件。无论使用哪种方式，都难以正确的将副本连接到源库。
为了解决这个问题，mysql新增了另一种跟踪复制位点的方法：全局事务标识符(GTID)。使用GTID，源服务器提交的每个事务都被分配成一个唯一标识符。此标识符是由server_uuid和一个递增的事务编号组成的。当事务被写入二进制日志时，GTID也随之被写入。回顾本章前面的内容可以了解到，副本将从源库读取的二进制日志事件先写入本地中继日志，再使用sql线程执行该事务，将变更应用到本地副本上。当sql线程提交事务时，它也会将GTID标记为执行完成。
为了更好地说明这一点，我们来看一个例子。假设我们的源服务器刚刚完成配置，里面还没有数据--甚至没有创建数据库。在这个源服务器上，也会生成server_uuid，假设为d9asdds-7bbe-11eb-ao43-3423af42a。我们对副本也做同样的配置，并使用适当的命令来搭建到源服务器的复制。
在源服务器上，我们需要创建一个新数据库。

create database misc;

这个事件将被写入二进制日志，这样副本也可以创建同样的数据库。在二进制日志中，我们将看到这个由GTID标识的单个事件。

d9asdds-7bbe-11eb-ao43-3423af42a: 1

当副本服务器应用此事件时，它会记录已经完成了该事务d9asdds-7bbe-11eb-ao43-3423af42a: 1。
在我们设计的示例中，假设此时在副本上停止mysql的执行。它已经提交一个事务，如果源库继续写入新的事务，那么事务列表将继续增长：2、3、4、5等。当再次启动副本服务器的时候，他就会知道已经完成了事务1.可以开始处理事务2了。
GTID解决了运行mysql复制的一个令人痛苦的问题：处理日志文件和位置。强烈建议始终按照mysql官方文档中的说明，在数据库中启用GTID。

9.4.3 崩溃后的复制安全

虽然GTID解决了日志文件和位置问题，但还有一些其他问题困扰着mysql管理员。有一些设置可以极大的改善使用复制的体验。
为了尽量降低复制中断的可能性，建议mysql的部分参数按照如下讲解内容进行配置。

innodb_flush_log_at_trx_commit=1

严格来说，这并不是一个复制相关的配置。不过这个参数可以保障每个事务日志都被同步到写磁盘。这是一个符合ACID要求的配置，将最大限度的保护你的数据--即使是在复制中也是这样的。这是因为二进制日志事件首先被提交，然后事务将被提交并写入磁盘。将此参数设置为1将增加磁盘写入操作的频次，同时确保数据的持久性。

sync_binlog=1

该变量控制mysql将二进制日志数据同步到磁盘的频率。将此值设置为1意味着在每次事务执行的时候都会把二进制日志同步写入磁盘。这可以防止在服务器崩溃时丢失事务。就像之前的配置参数一样，它也会增加磁盘写入量。

relay_log_info_repository=TABLE

以前，mysql的复制通常依赖磁盘上的文件来跟踪复制位置。这意味着，复制完成事务操作之后，还需要完成同步写入磁盘操作。如果在事务提交和同步之间发生了服务器崩溃，此时，磁盘上的文件将可能包含错误的文件和位置信息。在该配置下，该信息将被转移到mysql本身的InnoDB表中，允许复制更新同一事务中的事务和中继日志信息。这会在一个原子操作中完成，并有助于崩溃恢复。

relay_log_recovery=ON

简单的说，该参数使得副本服务器在监测到崩溃时会丢弃所有本地中继日志，并从源服务器中获取丢失的数据。这确保了在崩溃中发生的磁盘上的任何损坏或不完整的中继日志都是可恢复的。配置该参数后，不再需要配置sync_relay_log,因为在发生崩溃时，中继日志将被删除，也就无需花费额外的操作将他们同步到磁盘。

9.4.4 延迟复制

在某些场景下，在一个拓扑结构中，某些副本有一些延迟反而是有好处的。在这个策略下，可以让副本中的数据保持在线并且持续运行，但同时落后于源数据库数小时或者数天。延迟复制的配置语句是CHANGE REPLICATION SOURCE TO，配置选项为SOURCE_DELAY。
想象一下这样的场景，你正在处理大量数据，突然意外的做了一些变更。删除了一个表。从备份中恢复可能要几个小时。如果使用了延迟复制的副本，则可以找到DROP TABLE语句对应的GTID，使副本服务器的复制运行到表被删除之前的时间点，这会大大减少修复时间。
不过，这种复制结构也会带来一些复杂性。虽然延迟复制在某些数据丢失场景下非常有用，但他也给许多其他的操作带来了复杂性。如果你决定使用延迟复制，则需要考虑，在选择新的源服务器时，通常要排除延迟副本（如果故障恢复与切换操作是完全自动化的，这点则更加重要），如何监控延迟复制，以及如何处理这个特殊的副本等。这些都是引入延迟复制时应该考虑的。

9.4.5 多线程复制

在复制技术中，历史非常悠久的挑战之一就是，虽然在源上数据可以并行写入，但在副本上只能是单线程的。最新的mysql版本则提供了多线程复制能力，可以在副本端运行多个sql线程，从而加快本地中级日志的应用。
多线程复制有两种模式：DATABASE和LOGICAL_CLOCK。在DATABASE模式下，可以使用多线程更新不同的数据库。但不会有两个线程同时更新同一个数据库。如果将数据分布在mysql的多个数据库中，则可以同时并且一致的更新它们，这种模式非常有效。另一种模式LOGICAL_CLOCK允许对同一个数据库进行并行更新，只要他们都是同一个二进制日志组提交的一部分。
在大多数情况下，可以简单的通过将replica_parallel_workers设置为非零值来开启该配置，并立即看到效果。如果在单个数据库上操作，还需要将replica_parallel_type更改为LOGICAL_CLOCK。由于多线程复制还需要使用协调线程，因此管理这些线程的状态，也会带来一些额外的开销。此外，确保你的副本配置了参数replica_preserve_commit_order，这样就不会出现无序提交的问题。
有两种方法可以确定最合适的replica_parallel_workers值。一种不那么精确的方式是先停止复制，然后测试使用不同数量的线程赶上最新位置所需的时间，直到找到最佳配置。这种方式有一些缺点，因为它假设复制总是发送同样数量的数据操作语句（DML），并且它们的性能都基本相同。在实践中，这个假设很难成立。
更精确的方法是，根据实际的工作负载情况，查看每个应用程序线程的繁忙程度，然后再确定并行度。为此，我们需要启用Performance Schema的插桩和消费者表，允许它收集一些信息，然后查看实际情况。首先，我们需要启用这些视图：

update performance_schema.setup_consumers set enabled = 'YES'
where name like 'events_transactions%';

update performance_schema.setup_instruments set enabled = 'YES', timed='YES'
where name = 'transaction';

允许复制在一段时间内处理二进制事件。在理想情况下，应该在写入负载最大的时候或其他观察到出现复制延迟的时候。

use performance_schema;

这条查询将帮助你确定每个线程处理了多少个事务。正如我们从这个示例工作负载的结果中看到的，最佳配置是在3到4个线程之间，超出此范围的线程则很少被用到。

9.4.6 半同步复制

在启用半同步复制之后，源在完成每个事务提交时，都需要确保事务至少被一个副本所接收。需要确认副本已收到并成功将其写入自己的中继日志（但不一定应用到本地数据）。
由于每个事务都必须等待其他节点的响应，因此该功能会给服务器执行的每个事务都增加额外的延迟。需要根据实际情况考虑是否开启该选项。
一个值得注意的重要事情是，如果在一定时间范围内没有副本确认事务，mysql将恢复到标准的异步复制模式。这时事务并不会失败。这也说明，半同步复制不是一种防止数据丢失的方法，而是可以让你拥有更具弹性的故障切换的更大工具集的一部分。
考虑到异步复制的回退机制，我们很难找到一个好的用例来解释为什么要启用它。在一个比较合理的场景中，发生网络分区异常之后，在某个独立分区内的源服务器中就不会再写入新的数据。不幸的是，在超时后，该源将恢复为异步模式，并继续接受写入。因此，建议不要依赖该功能来保证数据完整性。

9.4.7 复制过滤器

复制过滤选项可以让副本仅复制一部分数据，不过这个功能并没有想象中那么实用。有两种复制过滤器：一种是从源上的二进制日志中过滤事件，另一种是从副本上的中继日志中过滤事件。
控制二进制日志过滤的选项是binlog_do_db和binlog_ignore_db。事实上，并不建议启用这些参数，否则，你可能会经常要向老板解释为什么数据会永久消失并且无法恢复。
在副本上，replication_选项在sql线程从中继日志中读取事件时过滤事件。你可以复制或忽略一个或多个数据库，将一个数据库重写为另一个数据库，也可以基于like语法的模式匹配来忽略表。
了解这些选项最重要的一点是，源和副本上的_do_db和*_ignore_db选项并不像你预期的那样工作。你可能会认为它们会根据对象的数据库名称来进行过滤，但实际上它们是根据当前的默认数据库进行过滤的，也就是说，如果在源上执行以下语句：

use test;
delete from sakila.film;

_do_db和_ignore_db参数将在test上过滤delete语句，而不是在sakila上。这并不是你想要的，这很可能会导致错误的语句被复制或者被忽略。_do_db和_ignore_db参数有用，但是应用场景很有限，使用时应该非常小心。如果使用这些参数，复制很容易出现不同步或失败。
binlog_do_db和binlog_ignore_db选项不仅有可能破坏复制，还会使从备份中进行时间点恢复变得不可能。在大多数情况下都不应该使用它们。
通常，复制过滤器是一颗定时炸弹。例如，假设你想阻止权限更改传递到副本，这是一个相当常见的场景。（如果你打算这么做，可能要想象是不是哪里做错了什么；应该有更好的方法可以实现你的真正目标）。系统表中复制过滤器肯定会阻止grant语句复制，但它们也会阻止调度事件、函数、存储过程等对象的复制。因为这种种不可预见的后果，所以建议谨慎使用复制过滤功能。如果需要防止特定语句被复制，一个推荐的做法是使用set sql_log_bin=0,尽管这种做法也有一定的危险性。一般来说，应该非常小心的使用复制过滤器，并且只在真正需要它们时才使用，因为它们很容易破坏复制，并会在最不方便的时候出现问题，例如在灾难恢复期间。
话虽如此，在某些特定情况下复制过滤器也是有益的。例如，你创建了多少个数据库user_1、user_2、user_3、user_4，现在，服务器上的性能压力非常大。通过恢复备份并配置好复制，可以准备将user_3和user_4的查询移动到另一台服务器上。这通常没什么问题，但你的数据库还有user_1和user_2。在某些时候，为了降低性能影响，不希望复制不需要的数据，那么可以考虑这个方案。先恢复备份，然后删除user_1和user_2。再配置复制规则以忽略user_1、user_2并完成复制设置。现在，在新服务器上仅需处理user_3、user_4的事件。一旦赶上了最新的复制位点，副本服务器就可以接收生成流量了。
过滤选项在mysql手册中有非常详细的说明，这里不再重复哪些细节。

9.5 复制切换

复制是高可用的基础。相比备份，总是保留一份持续更新的副本数据，会让灾难恢复更简单。除此之外，有些维护操作也只需要简单的重启mysql即可。
在本节我们讨论将副本切换为新的源节点的正确方法。这个操作很容易出错，而且出错后会导致数据问题和延长停机时间。我们想澄清"切换副本"和”故障切换“是同义词，它们都意味着源服务器不再接收写入，并将副本提升为新的源服务器。

9.5.1 计划内切换

切换的最常见原因是某些维护事件，包括安全补丁，内核更新，有时候甚至只是重新启动一下mysql，因为有一些配置选项更改后需要重新启动才能生效。这种类型的切换被称为计划内切换。
要成功的执行此类切换，需要完成以下步骤。

确定将哪个副本切换为新的源。这是一个包含所有数据的副本。这就是要操作的目标。
检查延时，要确保延时在秒级别。
通过设置super_read_only停止数据写入源服务器。
等待副本与目标完全同步。可以通过比较GTID来确定这一点。
在目标（需要切换为源的副本）上取消read_only设置。
将应用流量切换到目标上。
将所有副本重新指向新的源，包括刚刚被降级为副本的服务器。如果配置了GTID和AUTO_POSITION=1，这很简单

9.5.2 计划外切换

只要时间够长，每个系统都会因为软件或硬件而出现故障。当故障发生在承担写入的源服务器上时，会对用户体验产生重大影响。大多数应用程序会返回一个错误，并让用户重试。这种情况下就需要计划外切换。
因为这时候不再存在一个实时运行的源服务器了，因此这将是一个很简短的计划外切换，需要根据副本上的已有数据进行选择：

确定要切换的副本。通常会选择数据最完整的副本。这就是要切换的目标
在目标上关闭read_only设置
将应用流量切换到目标上
将所有副本重新指向新源（目标服务器），包括恢复后的原来提供服务的源服务器。在使用了GTID之后，这些操作都变的简单。注意：切换前的源服务器再次启动时，需要默认启用super_read_only。这将有助于防止任何意外的写入

9.5.3 切换时的权衡

需要说明的是，大多时候，大家对故障的第一反应就是切换。但因为很难知道切换后的目标（新的源服务器）可能丢失了多少数据，所以有时不进行故障切换可能是更好的策略。
通常。计划外切换并不是一个非常熟练的操作--也就是说，你并不会经常执行该操作。当需要执行时通常要查阅文档以确保不会错过任何步骤。在执行过程中还必须检查其他副本以验证哪个是最优的候选者。所有这些都需要时间。在某些情况下，等待服务器或mysql进程恢复可能会更快。另外，等待恢复的好处是，如果你按照ACID合规步骤进行操作，就不会丢失任何数据，并且所有副本将继续从中断的地方工作。

9.6 复制拓扑

几乎任何一个源和副本都可以配置mysql复制。复制可以有很多复杂的拓扑结构，但即使是简单的拓扑也可以非常灵活。一种拓扑可以有很多不同的用途。
巨大的灵活性也意味着可能会设计出难以维护的拓扑。在能够满足需求的情况下，我们强烈建议尽可能保持复制拓扑结构简单。话虽如此，我们推荐了两种可能的策略，它们几乎涵盖了所有用例。实际中，可能有充足的理由偏离这些，但是当你的设计变得更复杂的时候，一定问自己是否在解决正确的问题，有没有过度设计。

9.6.1 主动/被动模式

在主动被动拓扑中，应用将所有读取和写入都指向单个源服务器。此外，还维护了少量不主动服务于任何应用程序流量的被动副本。选择此的模型主要原因是，不用担心复制延迟的问题。由于所有读取都指向源服务器，因此可以防止应用程序不接受读取延迟数据的问题。
配置
在这个拓扑架构下，应该尽量让源和副本在cpu、内存等方面具有相同的配置。当需要进行切换的时候，例如，维护、软件升级、打补丁或者硬件故障等。可以从当前正在运行的源切换到其中某个副本上。因为副本上使用相同的硬件和软件配置，因此可以确保能够支持切换之前的应用流量和吞吐量。
冗余
在物理硬件环境中，推荐使用至少三台服务器的n+2冗余。如果其中一台服务器发生硬件故障，还有一台额外的副本服务器用于故障切换。如果无法在源服务器上进行备份操作，可以使用其中一个副本作为备份服务器。
在云环境中，如果数据足够少，或者可以轻松复制数据，也可以使用至少两台服务器的n+1冗余，否则还是建议n+2.如果使用了n+1架构，云服务商的动态资源调配特性会让管理操作更容易。对于打补丁等维护事件，可以按需配置第三个副本，对其执行任何必要的操作（如升级内核或应用安全更新），然后替换另一个副本。最后再进行故障切换并在前一个源上重复该过程。目的是让副本在前一个源上重复该过程。目的是让副本随时可以成为故障切换的目标。
在任何一种情况下，都可以将其中一个副本放置在地理位置较远的位置，不过需要关注复制延迟情况并确保其可用。副本应该是可恢复的，并且任何数据丢失都应符合预期的标准。
注意事项
在此模式下，实际是将读扩展绑定到单台服务器的容量上。如果达到读扩展上限，则必须演进到更复杂的拓扑（比如主动/只读池配置），否则就不得不利用分片来减少源上的读取压力。

9.6.2 主动/只读池模式

主动/只读池模式中，你将所有写入指向源服务器。根据应用程序的需要，读取则可以被发送到源服务器或只读池。只读池可以实现读取密集型应用程序的读水平扩展。在某些时候，由于源上的复制请求，水平扩展能力会受到限制。
配置
在理想情况下，至少要有一个副本（最好是两个）与源服务器具有相同的配置。同样的，当需要故障切换到其中一个副本时，该副本应该有足够的容量支撑业务的流量。
如果随着时间的推移，只读池在持续增长，则可以让副本用不同的硬件配置来优化成本。这种情况下，请尝试将流量进行加权，运行在更好的硬件配置上可以承担更多的流量。如果故障切换的目标副本上有32个cpu核，其他副本上有8个核，则可以向32核的节点发送4倍以上的流量，以确保资源被充分利用。
冗余
在只读池中的服务器数量应满足先前提出的要求，还需要至少一台服务器可以充当故障切换的目标。此外，还需要有足够的节点来支撑读流量，以及用于节点故障的小缓存区。对于读取，最有效的使用率参考指标是cpu使用率，因此，目标池中每个节点的使用率在50%-60%。随着cpu使用率的增加，节点会花费更多的时间在工作和延迟之间进行上下文切换，尝试在满足应用程序期望的延迟和使用率之间找到适当的平衡。
注意事项
在使用读取池的时候，应用程序必须对延迟读取有一定的容忍度，因为你永远无法保证在源服务器上完成的写入已被复制到副本上。可能还需要一种方法来识别那些复制延迟太大的节点，并根据需要将其踢出只读池。
只读池的大小会决定管理的复杂度，以及何时应该考虑自动化。如果只读池有16个节点，那么每次更新内核或安装安全补丁就需要执行16遍，这时如果可以自动化的完成节点剔除、安装补丁、重新启动、重新加入节点池，那么会大大降低运维操作的工作量。

9.7 复制管理和维护

在数据量很小而且写入负载一致的时候，通常不太需要经常查看复制延迟或者复制中断相关的问题。但随着数据量的增加，相关的管理和维护工作也会随之而来。

9.7.1 复制监控

复制增加了mysql监控的复杂性。虽然复制实际上同时在源和副本上工作，但大部分工作都是在副本上完成的，这也是经常发生问题的地方。比如，所有的副本都是正常运行的吗？有没有某个副本有错误？最慢的副本的复制延迟是多少？虽然是开箱即用，mysql也提供了回答这些问题所需的大部分信息，但是自动化监控过程和如何让复制更加健壮，则需要你花费更多的精力。
在配置复制监控时，需要注意以下几点。
复制同时需要源和副本上的磁盘空间。复制在源上使用二进制日志，在副本上使用中继日志。如果源上没有可用的磁盘空间，事务将无法完成并开始超时。如果在副本上发生相同的情况，mysql会通过暂停复制并等待可用的磁盘空间，相比来说这更优雅一些。所以，两边的磁盘空间都需要进行监控，以确保复制稳定运行。
监控复制的状态和错误
尽管复制是一项长期存在的功能并且非常强大，但网络问题、数据不一致和数据损坏等外部因素可能会导致其中断。因此，监控复制线程是否正常运行是非常重要的，如果复制线程设有正常运行，那么可以查看报错信息，以确定下一步应该做什么来修复错误。
延迟复制的实际延迟
之前提到了延迟复制，因此建议设置监控以确保延迟副本实际上延迟了正确的时间量。太长的延迟可能会使其使用起来更加耗时。如果延迟太短--或者更糟糕的是，根本没有延迟--可能在真的需要用到延迟副本却无法使用。

9.7.2 观测复制延迟

通常，最需要监控的就是副本与源之间的复制延迟是多少。虽然show replica status中的seconds_behind_source列理论上显示了副本的延迟，但实际上并不总是准确的，原因有多种：

副本通过将服务器的当前时间戳与二进制日志事件中记录的时间戳进行比较来计算seconds_behind_source，因此除非有正在处理的语句，否则副本甚至无法报告其延迟情况。
如果复制线程不是正在运行，副本通常会报告null。
一些错误（例如，源和副本之间的max_allowed_packet设置不匹配或网络不稳定)可能会中断复制或停止复制线程，但seconds_behind_source将报告0而不是错误。
即使复制过程正在运行，副本有时也无法计算延迟。如果发生这种情况，副本可能会报告0或null
长事务可能会导致延迟时间的波动。例如，如果有一个更新数据的事务持续运行了1小时，然后才提交，则更新将在实际发生1小时后才写入二进制日志。副本处理语句时，会显示一小时的复制延迟，然后再突然跳回延迟0秒。
要解决这些问题，只能忽略Seconds_behind_source，并使用一些可以直接观察和测量的指标来监控复制延迟。最好的解决方案是心跳记录，它需要在源上每秒更新一次时间戳。需要计算延迟时，就可以简单的在副本上用当前时间戳减去记录的心跳时间。这种方式不受刚才提到的所有问题的影响，它还有一个额外的好处--创建了一个方便的时间戳，展示副本中的数据在什么时间点时最新的。percona toolkit中包含的ptheartbeat脚本是当前最流行的复制心跳实现方案。
心跳记录还有其他好处。二进制日志中的复制心跳记录可用于许多目的，例如在灾难恢复时遇到的其他难以解决的问题。
刚刚提到的所有延迟指标都不能说明副本实际赶上源需要多长时间。这取决于许多因素，例如，副本配置是否强大以及源继续处理了多少写入。

9.7.3 确定副本的一致性

在现实中，各种情况都会导致副本于源的不一致。一些可能的原因：

意外写入副本
使用双源复制，双方都写入了数据
非确定性语句和基础语句的复制
当运行在弱持久化模式时mysql崩溃
mysql中的bug
建议遵循如下规则或配置
在副本上，始终启用super_read_only。使用read_only可以防止没有super权限的用户写入，但这不会阻止dba在没有意识到他们在副本上的情况下运行delete或alter。super_read_only设置只允许复制写入，是运行副本的最安全方式。
使用基于行的复制或确定性语句
尽管存在使用大量磁盘空间的情况，但基于行的复制是复制数据的最一致方式。这是因为它包括为每个条目更改的确切行数据。
考虑以下基于语句的复制：

delete from users where last_login_date <= NOW() limit 10;

当这个表中有1000个用户匹配where子句时会发生什么？mysql将使用表中的自然顺序，然后删除前10行。表的自然顺序在副本上可能不同，因此可能会影响不同的10行数据。后续运行的语句根据last_login_date修改或删除行可能存在也可能不存在。这可能会导致数据不一致。
编写此代码的最佳方法是使用order by，使行顺序具有确定性：

delete from users where last_login_date <= NOW() order by user_id limit 10;

使用这个语句，只要源和副本中的数据一致，就会删除相同的10行。
不要尝试同时写入复制拓扑中的多台服务器，这包括使用在两侧都支持写入的协同源或环形复制。最实用的复制拓扑是使用一个源，执行所有写入操作，以及一个或多个副本，可选择的执行读取操作。
最后强烈建议，如果遇到任何复制错误，请使用mysql官方文档中的策略。

9.7.4 复制问题和解决方案

mysql复制的简单实现使其易于配置，但也意味着有很多方法可以停止、混淆或破坏它。前面讨论了崩溃安全复制和有助于保持源和副本同步的规则。本节讨论如何发现以及如何解决甚至预防他们。

9.7.4.1 源端二进制日志损坏

如果源上的二进制日志已被损坏，那么别无选择，只能重建副本。跳过损坏的条目将跳过一些事务，这些事务在副本上将不再有机会被执行，也就会导致数据不一致。

9.7.4.2 非唯一的服务器ID

这是复制过程中可能遇到的难以琢磨的问题之一。如果你不小心配置了具有相同服务器ID的两个副本，不仔细观察的话，他们似乎可以正常工作。但如果使用诸如innotop之类的工具查看错误日志或源服务器，就会发现一些非常奇怪的情况。
在源服务器上，任何时候都只能看到两个副本中的一个。（通常，所有副本都已连接并一直在复制）。在副本上，可以在错误日志中看到频繁的判断连接和重新连接的错误消息，但没有提及与错误配置的服务器id有关。
根据mysql的版本不同，副本可能会正确但缓慢的复制，或者实际上无法正确复制--任何给定的副本都可能错过二进制日志事件甚至重复他们，从而导致重复键错误（或静默数据损坏）。由于副本之间的冲突增加了负载，还可能在源上引起问题。而且，如果副本之间的竞争足够严重，错误日志文件可能会在短时间变得巨大。
解决此问题的唯一方法是在设置副本时要小心。你可能会发现创建副本到服务器id映射的规范列表很有帮助，这样就不会忘记哪个id属于哪个副本。如果副本完全在一个子网中，则可以使用每台机器ip地址的最后一个八位字节来作为唯一id。

9.7.4.3 未配置服务器ID

如果没有配置服务器ID，mysql将显示为使用change replication source to配置了复制，但不会允许启动副本：

start replica;

如果使用change replication source to配置复制并通过show replica status验证了配置，则此错误信息尤其令人困惑。你可能会从select @@server_id中获得一个值，但这只是一个默认值。必须显示设置该值。

9.7.4.4 临时表丢失

临时表在某些场景中使用起来很方便，但不幸的是，它们与基于语句的复制不兼容。如果副本崩溃或将其关闭，则副本线程正在使用的任何临时表都会消失。当重新启动副本时，引用丢失的临时表的任何相关语句都将失败。
这种情况下最好的方法是使用基于行的复制。其次是统一命名临时表（例如，以temporary_为前缀），然后使用复制规则完全跳过复制临时表

9.7.4.5 没有复制所有变更

如果误用SET SQL_LOG_BIN=0或不了解复制过滤规则，可能会导致副本不会执行源上发生的某些变更。有时希望将其用于存档，但这通常是偶然的并且会产生不良后果。
例如，假设有一条replicate_do_db规则仅将sakila数据库复制到某个副本中。如果在源上执行以下命令，副本的数据将与源上的数据不同：

use test;
update sakila.actor ...

某些类型的语句甚至可能导致复制失败并出现错误，但这些错误却与复制没有关系。复制延迟过大

9.7.4.6 复制延迟过大

复制延迟是一个常见问题。无论如何，在设计应用程序的时候，都需要考虑复制可能会出现一些延迟。以下是一些减少复制延迟的常用方法。
多线程复制
确保使用了多线程复制，并且已经按照手册查看了如何调整各种选项以从中获得最佳效率。
使用分片技术
这看起来是一种逃避问题的方式，但使用分片技术将写入分散到多个源也是一种非常有效的策略。MySQL长期存在的经验法则是：使用副本扩展读取操作，使用分片技术扩展写入操作。
临时降低持久化要求
完美主义者不同意这种做法，但有时可能已经用尽了所有的调优技术，并且分片也不可行。如果复制延迟主要是由于写入操作导致的，则可以临时设置sync_binlog=0和innodb_flush_log_at_trx_commit=0以提高复制速度。
如果采用最后一种方法，那么要非常非常小心。最好只在副本上执行此操作，如果此副本也用于执行备份操作，则更改这些设置可能会使你无法从备份中恢复完整的数据。此外，如果副本在降低持久化要求期间崩溃，那么必须从源服务器获取数据并重新配置副本。最后，如果是手动执行此操作，很容易忘记将持久化配置改回去。请确保有良好的监控或编写了某种脚本来再次设置持久化参数。
一种可能的策略是监控SHOW REPLICA STATUS命令中的Seconds_behind_source值，当它超过某个阈值时，触发执行以下操作：

验证是否启用了super_read_only，以确保服务器是不可写副本。
更改sync_binlog和innodb_flush_log_at_trx_commit的配置以减少写操作。
定期检查SHOW REPLICA STATUS以获取Seconds_behind_source的值。
当延迟低于可接受的阈值时，将持久性相关参数恢复到正常值。

9.7.4.7 来自源服务器的超大数据包

当源服务器的max_allowed_packet大小与副本不匹配时，可能会出现另一个难以跟踪的复制问题。在这种情况下，源服务器可以记录副本认为过大的数据包，当副本检索该二进制日志事件时，它可能会遇到各种问题，其中包括无休止的错误循环、重试或中继日志中的损坏。

9.7.4.8 磁盘空间耗尽

复制确实可以用二进制日志、中继日志或临时文件占满磁盘，尤其是当源服务器执行大量LOAD DATA INFILE语句并在副本上启用log_replica_updates时。复制延迟越大，用于已从源端获取但尚未执行的中继日志占用的磁盘空间就越多。可以通过监控磁盘使用情况并设置relay_log_space参数来防止这些错误出现。

9.7.4.9 复制的限制

因为MySQL自身固有的一些限制，无论有没有出现明确的报错，MySQL复制都可能失败或不同步。有非常多的SQL函数和编程实践都无法可靠地被复制（我们在本章中也提到了很多案例）。通常，很难确保在生产代码中没有使用这些限制的内容，特别是当应用程序或团队规模很大的情况下。
另一个问题是服务器中的bug。并不是我们很消极，但MySQL服务器的许多大版本历史上都有复制方面的bug，尤其是在大版本的第一个版本中。诸如存储过程等新特性通常会导致更多问题。
对于大多数用户来说，这不是回避新功能的理由，而应该是仔细测试的理由，尤其是在升级应用程序或MySQL时。监控也很重要，你需要知道什么时候会导致问题。
MySQL复制已经很复杂了，所以应用程序越复杂，就越需要小心。但是，如果你学会了如何使用它，它就能很好地工作。十可扩展的mysql

十一高可用性

十二云端的mysql

十三应用层优化

十四备份和恢复

如果你没有提前做好备份规划，也许以后会发现已经错失了一些最佳的选择。例如，在服务器已经配置好以后，才想起应该使用LVM，以便获取文件系统的快照——但这时已经太迟了。在为备份配置系统参数时，可能没有注意到某些系统配置对性能有着重要影响。如果你没有计划做定期的恢复演练，当真的需要恢复时，就会发现并没有那么顺利。
在本章中，我们不会涵盖备份和恢复解决方案的所有部分，而是仅涉及与MySQL相关的部分。以下是我们决定不在此处包含的一些要点，但在你的整体备份和恢复策略中仍旧应该考虑涵盖：

备份存储在哪里，包括它们应该离源数据多远（在一块不同的磁盘上、一台不同的服务器上，或离线存储），以及如何将数据从源头移动到目的地。
保留策略、审计、法律要求，以及相关的条款。
存储解决方案和介质，压缩，以及增量备份。
存储的格式。
对备份的监控和报告。
存储层内置备份功能，或者其他专用设备，例如预制式文件服务器。在开始本章的介绍之前，让我们先澄清几个核心术语。首先，经常可以听到所谓的热备份、暖备份和冷备份。人们经常使用这些词来表示一个备份的影响：例如，“热备份”不需要任何的服务停机时间。问题是对这些术语的理解因人而异。有些工具虽然在名字中使用了“热备份”，但实际上并不是我们所认为的那样。我们会尽量避开这些术语，而直接说明某个特别的技术或工具对服务器的影响。
另外两个让人困惑的词是还原和恢复。在本章中它们有其特定的含义。还原意味着从备份文件中获取数据，可以将这些文件加载到MySQL里，也可以将这些文件放置到MySQL期望的路径中。恢复一般意味着当某些异常发生后对一个系统或其部分的拯救。它包括从备份中还原数据，以及使服务器完全恢复功能的所有必要步骤，例如，重启MySQL、改变配置和预热服务器的缓存等。
在很多人的观念中，恢复仅意味着修复崩溃后损坏的表。这与恢复一个完整的服务器是不同的。存储引擎的崩溃恢复要求数据和日志文件一致。要确保数据文件中只包含已经提交的事务所做的修改，恢复操作会将日志中还没有应用到数据文件的事务重新执行。这也许是恢复过程的一部分，甚至是备份的一部分。然而，这和一个意外的DROP TABLE事故后要做的事是不一样的。根据你的恢复所需要解决的问题的不同，所要采取的操作可能也会有很大不同。
最后，有两种主要类型的备份：裸文件备份和逻辑备份。裸文件备份（有时也被称为物理备份）是指文件系统中的文件副本。逻辑备份是指重建数据所需的SQL语句。

14.1 为什么要备份

下面是进行备份的几个非常重要的理由。

14.1.1 灾难恢复

灾难恢复是在下列场景下需要做的事情：硬件故障、一个不经意的Bug导致数据损坏，或者服务器及其数据由于某些原因不可获取或无法使用等。你需要准备好应付很多问题：某人偶然连错服务器执行了一个ALTER TABLE操作，机房大楼被烧毁，恶意的黑客攻击或MySQL的Bug等。尽管遭受任何一个特殊的灾难的概率都非常低，但所有的风险叠加在一起就很有可能会碰到了。

14.1.2 审计

有时候需要知道数据或Schema在过去的某个时间点是什么样的。例如，你也许被卷入一场法律诉讼，或发现了应用的一个Bug，想知道这段代码之前干了什么（有时候，仅仅依靠代码的版本控制还不够）。

14.1.3 测试

一个最简单的基于实际数据来测试的方法是，定期用最新的生产环境数据更新测试服务器。如果使用备份的方案，那就非常简单了：只要把备份文件还原到测试服务器上即可。
重新审视你的一些假设。例如，你认为共享主机供应商会提供MySQL服务器的备份？许多主机供应商根本不备份MySQL服务器，另外一些也仅仅在服务器运行时复制文件，这可能会创建一个损坏的没有用的备份。

14.2 定义恢复需求

如果一切正常，那么永远也不需要考虑恢复。但是，一旦需要恢复，只有世界上最好的备份系统是没用的，还需要一个强大的恢复系统。
不幸的是，让备份系统平滑工作比构造良好的恢复过程和工具更容易。原因如下：

备份在先。只有已经做了备份才可能恢复，因此在构建系统时，注意力自然会集中在备份上。
备份由脚本和任务自动完成。经常不经意地，我们会花些时间调优备份过程。花5分钟来对备份过程做小的调整看起来并不重要，但是你是否天天同样地重视恢复呢？
备份是日常任务，但恢复常常发生在危急情形下。
因为安全的需要。如果你正在做异地备份，可能需要对备份数据进行加密，或采取其他措施来进行保护。数据被盗用的危害人人皆知，但是有没有人想过，当没有人能对用来恢复数据的加密卷解锁，或需要从一个整块的加密文件中抽取单个文件时，数据被盗用的危害又会有多大？
只有一个人来规划、设计和实施备份。当灾难袭来时，那个人可能不在。因此需要培养几个人并有计划地让他们互为备份，这样就无须由一个不合格的人来恢复数据。
在规划备份和恢复的策略时，有两个重要的需求可以帮助思考：恢复点目标(PRO)和恢复时间目标(RTO)。你可能会注意到，这与第2章讨论的SLO非常类似。它们定义了可以容忍丢失多少数据，以及需要等待多久能将数据恢复。在定义RPO和RTO时，先尝试回答下面几类问题：
在不导致严重后果的情况下，可以容忍丢失多少数据？需要随时从故障中恢复，还是可以接受自从上次日常备份后所有的工作全部丢失？是否有法律法规的要求？
恢复需要在多长时间内完成？哪种类型的宕机是可接受的？哪种影响（例如，部分服务不可用）是应用和用户可以接受的？当那些场景出现时，又该如何持续服务？
需要恢复什么？常见的需求是恢复整个服务器、单个数据库、单个表，或仅仅是特定的事务或语句。
建议将上面这些问题的答案明确地用文档记录下来，同时还应该明确备份策略，以及备份过程。
备份误区1：复制就是备份
这是我们经常碰到的一个误区。复制不是备份，当然使用RAID阵列也不是备份。为什么这么说？可以考虑一下，如果意外地在生产库上执行了DROP DATABASE，它们是否可以帮你恢复所有的数据？RAID和复制连这个简单的测试都没法通过。它们不是备份，也不是备份的替代品。只有备份才能满足备份的要求。

14.3 mysql备份方案

备份MySQL比看起来要难。究其根本，备份仅是数据的一个副本，但是受限于应用程序的要求、MySQL的存储引擎架构，以及系统配置等因素，复制一份数据变得很困难。
在深入所有选项细节之前，先来看一下我们的建议：

在生产实践中，对于大数据库来说，裸文件备份是必需的：逻辑备份太慢并受到资源限制，从逻辑备份中恢复需要很长时间。基于快照的备份，例如Percona XtraBackup和MySQL Enterprise Backup，是最好的选择。对于较小的数据库，逻辑备份可以很好地胜任。
保留多个备份集。
定期从逻辑备份（或者裸文件备份）中抽取数据进行恢复测试。
保存二进制日志用于基于故障时间点的恢复。应该将expire_logs_days参数的值设置得足够大，至少确保可以从最近两次裸文件备份中做基于时间点的恢复，这样就可以在保持源运行且不应用任何二进制日志的情况下创建一个副本。要使备份二进制日志独立于过期设置，二进制日志需要保存在备份中足够长的时间，以便能从最近的逻辑备份中进行恢复。
完全不借助备份工具本身来监控备份和备份的过程。需要额外验证备份是否正常。
通过演练整个恢复过程来测试备份和恢复。测算恢复所需要的资源（CPU、磁盘空间、实际时间，以及网络带宽等）。
要仔细考虑安全性。如果有人能接触生产服务器，他是否也能访问备份服务器？反过来呢？
弄清楚RPO和RTO可以指导备份策略。是需要基于故障时间点的恢复能力，还是能从昨晚的备份中恢复数据但会丢失此后的所有数据也可以接受？如果需要基于故障时间点的恢复，可能要建立日常备份并保证所需要的二进制日志是有效的，这样才能从备份中还原，并通过重放二进制日志来恢复到想要的时间点。
一般说来，对数据丢失的承受力越强，备份越简单。如果你有非常苛刻的需求，比如要确保能恢复所有数据，备份就很困难。基于故障时间点的恢复也有几类。一个“宽松”的基于故障时间点的恢复需求意味着需要重建数据，直到“足够接近”问题发生的时刻。一个“硬性”的需求意味着不能容忍丢失任何一个已提交的事务，即使某些可怕的事情发生（例如，服务器着火了）。这需要特别的技术，例如将二进制日志保存在一个独立的SAN卷，或使用DRBD磁盘复制。

14.4 在线备份还是离线备份

十五 mysql用户工具

十六 mysql分支与变种

十七 mysql服务器状态

十八大文件传输

二九 explain

二十锁的调试

二十一在mysql上使用Sphinx

高性能mysql3笔记