高性能mysql读书笔记（三）

第10章 复制

MySQL内建的复制功能是构建基于MySQL的大规模,高性能应用的基础.同时也是高可用性,可扩展性,灾难恢复,备份及数据仓库等工作的基础

概述

1. 解决问题: 让一台服务器的数据与其他服务器保持同步.主库可以同步到多台备库,备库本身也可以配置为另一台服务器的主库
2. 复制原理: 通过在主库上记录二进制日志,在备库重放日志的方式实现异步的数据复制
3. 复制方式: 基于行的复制和基于语句的复制
4. 向后兼容: 新版本只能作为老版本的备库,反之不行

用途

1. 数据分布: 在不同地理位置分布数据备份,可以随意停止或开始复制.基于行比基于语句带宽压力更大
2. 负载均衡: 将读操作分布到多个服务器上
3. 备份: 复制是备份的一项有意义的技术补充
4. 高可用性和故障切换: 避免单点失败
5. MySQL升级测试: 一种普遍做法是使用一个更高版本的MySQL作为备库保证实例升级前查询能够在备库按照预期执行

过程

1. 主库把数据更改记录到二进制日志(Binary Log)
2. 备库将主库上的日志复制到自己的中继日志(Relay Log)
3. 备库读取中继日志中的事件,将其重放到备库数据上
4. 局限: 主库上并发运行的查询在备库只能串行化执行,因为只有一个sql线程重放中继日志事件,这是很多工作负载的性能瓶颈

复制配置

1. 在每台服务器上创建复制账号: 需要REPLICATION SLAVE权限
2. 配置主库和备库: 每个服务器的ID需要唯一不能冲突
3. 通知备库连接到主库并从主库复制数据
4. CHANGE MASTER TO: 指定备库连接的主库设置
5. SHOW SLAVE STATUS: 检查复制是否正确执行
6. START SLAVE: 开始复制
7. SHOW PROCESSLIST: 查看复制线程,IO线程(发送或获取日志),SQL线程(重放日志)
8. 推荐配置: 开启sync_binlog

从另一个服务器开始复制

问题: 主库已经运行一段时间,用一台新安装的备库与之同步 保持同步条件:

1. 某个时间点的主库的数据快照
2. 主库当前的二进制日志文件,和获得数据快照时在该二进制日志文件中的偏移量.通过这两个可以确定二进制日志的位置
3. 从快照时间到现在的二进制日志

克隆备库方法:

1. 冷备份: 关闭主库,复制数据.主库重启后会使用新的二进制文件,在备库指向这个文件的起始处
2. 热备份:如果只有MyISAM,可以通过mysqlhotcopy或rsync来复制数据
3. 如果只包含InnoDB: 可以使用mysqldump转储主库数据并加载到备库,然后设置相应的二进制日志坐标
4. 使用快照或备份: 使用主库的快照或者备份初始化备库,然后指定二进制日志坐标
5. 使用Percona Xtrabackup: 备份时不阻塞服务器操作,可以在不影响主库情况下设置备库
6. 使用另外的备库: 实质就是把另外的备库当成主库进行数据克隆

复制的原理

基于语句的复制

1. 主库会记录那些造成数据更改的查询
2. MySQL5.0之前只支持基于语句的复制
3. 对于函数,存储过程和触发器在基于语句的复制模式可能存在问题
4. 更新必须是串行,需要更多的锁

基于行的复制

1. 将实际的数据记录在二进制日志
2. 能够更高效复制数据
3. 基于行的复制事件格式,对人不可读,可以使用mysqlbinlog
4. 很难进行时间点恢复
5. 有些操作,如全表更新(update)复制开销会很大

复制拓扑

基本原则

1. 一个MySQL备库实例只能有一个主库
2. 每个备库必须有一个唯一的服务器id
3. 一个主库可以有多个备库
4. 如果打开log_slave_update一个备库可以把其主库上的数据变化传播到其他备库

一主多备

1. 适用于少量写和大量读,可以把读分摊到多个备库上
2. 当作待用的主库
3. 放到远程数据中心,用作灾难恢复
4. 作为备份,培训,开发或测试服务器

双主复制

1. 个数据库互为主库和备库
2. 容易造成数据不同步
3. 通常并不建议使用这种模式

主动被动的双主模式

1. 类似双主复制,把其中一台配置为只读
2. 类似于创建一个热备份
3. 可以用作执行读操作,备份,离线维护及升级

有备库的双主模式

1. 双主模式下,各自有备库

主库,分发主库和备库

1. 问题: 备库足够多时会对主库造成很大的负载
2. 方案: 将其中部分备库当成主库,分发给更多的备库
3. 通过分发主库,可以对二进制日志事件执行过滤和重写规则

复制管理和维护

1. 监控复制: SHOW MASTER STATUS查看主库状态, SHOW BINLOG EVENTS查看复制事件
2. 测量备库延迟: 可以使用Percona Toolkit里的pt-hearbeat
3. 确定主备是否一致
4. 备库换主库: 难点在于获取新主库合适的二进制日志位置
5. 备库提升为主库分为计划内提升和计划外提升

计划内提升

1. 停止向老的主库写入
2. 备库赶上主库
3. 备库设置为主库
4. 将备库和写操作指向新主库,然后开启主库的写入

计划外提升

当主库崩溃时,需要提升一台备库替代

1. 确定最新的备库
2. 让所有备库执行完从崩溃前主库获得的中继日志,如果未完成则更换主库,会丢失原先的日志事件
3. 重新完成主备的配置

复制的问题和解决方案

数据损坏或丢失

1. 主库意外关闭: 主库开启sync_binlog避免事件丢失,使用Percona Toolkit中的pt-table-checksum检查主备一致性
2. 备库意外关闭: 重启后观察MySQL错误日志,想方法获取备库指向主库的日志偏移量
3. 主库上的二进制日志损坏: 跳过所有损坏的事件,手动找到一个完好的事件开始
4. 备库上的中继日志损坏: MySQL5.5后能在崩溃后自动重新获取中继日志
5. 二进制日志于InnoDB事务日志不同步: 除非备库中继日志有保存,否则自求多福

其他

1. 如果使用myisam,在关闭Mysql前需要确保已经运行了stop slave,否则在服务器关闭时会kill所有正在运行的查询.
2. 如果是事务型,失败的更新会在主库上回滚而且不会记录到二进制日志
3. 避免混用事务和非事务: 如果备库发生死锁而主库没有,事务型会回滚而非事务型则不会造成不同步
4. 主库和备库使用不同存储引擎容易导致问题
5. 不唯一和未定义备库服务器id
6. 避免在主库上创建备库上没有的表,因为复制可能中断
7. 基于语句复制时,主库上没有安全使用临时表的方法.丢失临时表: 备库崩溃时,任何复制线程拥有的临时表都会丢失,重启备库后所有依赖临时表的语句都会失败
8. InnoDB加锁读引起的锁争用: 将大命令拆成小命令可以有效减少锁竞争
9. 过大的复制延迟: 定位执行慢的语句,改善机器配置
10. 其他: 查看官网手册

复制高级特性

1. 半同步复制: 当提交事务,客户端收到查询结束反馈前必须保证二进制日志已经传输到至少一台备库上,主库将事务提交到磁盘上之后会增加一些延迟
2. 复制心跳: 保证备库一直与主库相联系,如果出现断开的网络连接,备库会注意到丢失的心跳数据

其他复制技术

1. Percona XtraDB Cluster的同步复制
2. Tungsten

第11章 可扩展的MySQL

可扩展性: 通过增加资源提升容量的能力

考虑负载

容量可以简单地认为是处理负载的能力,考虑负载可从以下几个角度

1. 数据量: 很多应用从不物理删除任何数据,应用所积累的数据量是可扩展的普遍挑战
2. 用户量: 更多的用户意味着更多的事务,更多的复杂查询
3. 用户活跃度
4. 相关数据集的大小

规划可扩展性

1. 估算需要承担的负载到底有多少
2. 大致正确地估计日程表
3. 应用的功能完成多少
4. 预期的最大负载是多少
5. 如果依赖系统的每个部分分担负载,某个部分失效时会发生什么

向上扩展(垂直扩展)

1. 单台服务器增加各种高性能硬件
2. 烧钱有效的方法
3. 不应该无限制向上扩展

向外扩展

1. 策略: 复制,拆分,数据分片
2. 按功能拆分: 常见做法,根据功能将应用部署在不同服务器,并使用专用的数据库服务器

数据分片

数据分片是目前扩展大型MySQL最通用且最成功的方法

1. 应用设计初期考虑到,后期实现就比较容易,否则很难将应用从单一数据存储转换为分片架构
2. 文中举例: 通过用户id来对文章和评论进行分片,而将用户的信息保留在单个节点上
3. 数据库访问抽象层,降低应用和分片数据之间通信的复杂度
4. 如非必要尽量不分片
5. 数据分片最大的挑战就是查找和获取数据
6. 类似于表分区,选择分区键和数据分片方式是关键,具体请细查

通过集群扩展

1. 可以使用集群或数据库分布式技术根据场景适当解决一些问题
2. 书中提到技术: NDB Cluster, Clustrix等技术

向内扩展

1. 对不再需要的数据进行归档和清理
2. 需要考虑对应用的影响
3. 需要考虑数据逻辑的一致性,例如清理A表历史数据时需要考虑所有关联数据的处理
4. 冷热数据分离

负载均衡

目的

1. 可扩展性: 如读写分离时从备库读数据
2. 高效性: 把更多工作分配给更好的机器
3. 可用性: 使用时刻保持可用的服务器
4. 透明性: 客户端无需知道服务器
5. 一致性: 如果应用是有状态的,负载均衡器就应该将相关的查询指向同一个服务器

直接连接

复制上的读写分离

1. 基于查询分离: 将不能容忍脏数据的查询分配到主库,其他分配到备库
2. 基于脏数据分离: 让应用检查复制延迟,许多报表类应用使用这个策略
3. 基于会话分离: 可以在会话层做一个标记,如果用户修改了数据,则一段时间内总是指向主库
4. 基于版本分离: 给用户的操作增加版本号,检查版本号决定从主库还是备库读取数据

修改DNS名

1. 通过变更DNS名指定的服务器实现
2. 缺点很多,不建议

转移IP地址

1. 在服务器之间转移虚拟地址
2. 给服务器分配固定的ip地址,为每个逻辑上的服务使用一个虚拟ip地址

引入中间件

1. 负载均衡器,如HAproxy
2. 负载均衡算法: 随机, 轮询,最少连接数,最快响应,哈希,权重
3. 服务器池中增加或移除服务器: 在配置连接池中的服务器时,要保证有足够多未使用的容量

第12章 高可用性

1. 高可用性意味着更少的宕机时间

宕机原因

1. 磁盘空间不足
2. 糟糕的sql或者服务器bug引起
3. 糟糕的表和索引设计
4. 复制问题通常由于主备数据不一致导致

高可用性实现

1. 衡量指标: 平均失效时间(MTBF), 平均恢复时间(MTTR)
2. 避免问题: 适当的配置,监控和规范
3. 保证在宕机时能快速恢复,系统制造冗余,具备故障转移能力

避免单点失效

1. 通过增加冗余避免
2. 共享存储或磁盘复制,如果服务器挂了,备用服务器可以挂载相同的文件系统执行需要的恢复操作
3. MySQL同步复制

故障转移和故障恢复

1. 提升备库或切换角色
2. 虚拟IP地址或IP接管: 当MySQL实例失效时可以将IP地址转移到另一台MySQL服务器上
3. 使用中间件解决方案

第13章 云端的MySQL

（略）

第14章 应用层优化

Web服务器问题

Apache不适合用作通用Web服务器（既处理动态脚本也处理静态文件）：Apache对于静态文件的请求存在资源浪费，进程会复用，如果前一次处理的是动态语言脚本的请求，在请求结束后并不会释放所有的内存给操作系统，这样会造成一个占用内存很多的进程来为一个很小的请求服务的情况。同样的，这些被复用的进程也可能会保持大量MySQL连接，从而浪费MySQL资源。 总之，不要使用Apache来做静态内容服务，或者至少和动态服务使用不同的Apache实例。

使用缓存代理服务（Squid、Varnish），防止所有请求到达Web服务器。

打开gzip压缩。

不要为用于长距离连接的Apache配置启用Keep-Alive选项，因为这会使得重量级的Apache进程存活很长时间。

缓存

在实践中发现从Nginx的内存中获取内容比从缓存代理磁盘中获取数据要快。

（讲的太乱，也有一点过时，没有介绍redis，略）

第15章 备份与恢复

设计MySQL备份方案考虑点

1. 在线备份还是离线备份
2. 逻辑备份还是物理备份
3. 非显著数据: 如二进制日志和InnoDB事务日志
4. 代码: 存储过程,触发器
5. 服务器配置和复制配置
6. 外部配置,管理脚本
7. 增量备份和差异备份
8. 存储引擎和数据一致性

备份数据方式

1. 文件系统中或SAN快照中直接复制数据文件
2. Percona XtraBackup 做热备份

InnoDB崩溃恢复

1. 二级索引损坏: 使用OPTIMIZE TABLE修复损坏的二级索引,此外可以通过构建一个新表重建受影响的索引
2. 聚簇索引损坏: innodb_force_recovery导出表
3. 损坏系统结构: 系统结构包括事务日志等,可能需要做整个数据库的导出和还原,因为InnoDB内部绝大部分的工作可能受影响

第16章 MySQL用户工具

工欲善其事,必先利其器

接口工具

1. MySQL Workbench: 一站式的工具
2. SQLyog: 可视化工具之一

命令行工具集

1. Percona Toolkit
2. MySQL Workbench 工具集

SQL实用集

1. common_schema
2. MySQL Forge

监测工具

1. Nagios
2. Zabbix: 同时支持监控和指标收集的完整系统
3. Zenoss: Python写的
4. Hyperic HQ: 基于Java

Innotop命令行监控

主要包括以下功能

1. 事务列表
2. 当前运行的查询
3. 当前锁和锁等待列表
4. 服务器状态和变量汇总信息
5. InnoDB内部信息
6. 复制监控

附录A MySQL分支与变种

（略）

附录B MySQL服务器状态

PERFORMANCE_SCHEMA库、INFORMATION_SCHEMA库、SHOW命令。 （详略）

附录C 大文件传输

（略）

附录D EXPLAIN

EXPLAIN命令用于查看查询优化器选择的查询计划。被标记了EXPLAIN的查询会返回关于执行计划中每一步的信息，而不是执行它。(实际上，如果查询在from子句中包括子查询，那么MySQL实际上会执行子查询) EXPLAIN只是一个近似结果。

EXPLAIN的结果

id 标识SELECT所属的行，如果在语句中没有子查询或者联合查询，那么只会有一行（因为只有1个SELECT），且id值为1.

select_type

1. SIMPLE 意味着该查询不包含子查询和UNION，如果查询有任何复杂的子部分，则最外层部分标记为PRIMARY（id为1的查询）。
2. SUBQUERY 包含在SELECT列表中的子查询（即不是位于FROM子句中的查询）；
3. DERIVED 包含在FROM子句中的子查询；
4. UNION 在UNION查询中的第二个和随后的SELECT被标记为UNION；
5. UNION RESULT 用来从UNION的匿名临时表检索结果的SELECT；

table 表示正在访问哪个表（包括匿名临时表，比如derived1），可以在这一列从上往下观察MySQL的关联优化器为查询选择的关联顺序。

type 访问类型，决定如何查找表中的行，从最差到最优排列如下：

1. ALL 全表扫描；
2. index 按索引次序全表扫描，主要优点是避免了排序，缺点是当随机访问时开销非常大；如果在Extra列中有Using index，说明MySQL正在使用覆盖索引，即只扫描索引的数据。
3. range 有限制的索引扫描（带有between或where >等条件的查询）； 注意，当使用IN()、OR()来查询时，虽然也显示范围扫描，但是其实是相当不同的访问类型，在性能上有重要的差异。
4. ref 索引查找，返回所有匹配某个值的行，这个值可能是一个常数或者来自多表查询前一个表里的结果值；
5. eq_ref 也是索引查找，且MySQL知道最多只返回一条符合条件的记录（使用主键或者唯一性索引查询），MySQL对于这类访问优化的非常好；
6. const、system 当MySQL能对查询的某部分进行优化并将其转换为一个常量时，它就会使用这些访问类型；
7. NULL 意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引；

possible_keys 显示查询可以使用哪些索引。

key 显示MySQL决定采用哪个索引来优化对表的访问。

key_len 显示MySQL在索引里使用的字节数。

ref 显示之前的表在key列记录的索引中查找值所用的列或常量。

rows MySQL估计为了找到所需的行而要读取的行数。

filtered 在使用EXPLAIN EXTENED时才会出现，查询结果记录数占总记录数的百分比。

Extra

1. Using index：表示将使用覆盖索引；
2. Using where：意味着MySQL服务器将在存储引擎检索后再进行过滤；
3. Using temporary：意味着MySQL在对查询结果排序时会使用一个临时表；
4. Using filesort：意味着MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行；
5. Range checked for each record …：意味着没有好用的索引；

附录E 锁的调试

（略）

在MySQL上使用Sphinx

免费的开源的全文搜索引擎

参考

https://segmentfault.com/a/1190000011722724

https://blog.csdn.net/yxz8102/article/details/107391026

https://juejin.im/post/6844904061582442509

https://blog.csdn.net/xifeijian/category_1430276.html