MySQL优化班13期课后作业,20180927

概念题:
1、简述 MySQL 主从复制原理,以及异步、半同步、增强半同步复制的区别
2、简述 Xtrabackup 工作原理,以及如何进行一致性备份
3、简述 mysqldump 工作原理,以及如何进行一致性备份
4、简述造成 MySQL 主从数据不一致的原因及解决办法
5、简述 MySQL 主从复制延迟的原因及解决办法
6、简述 double write 的工作机制
7、MySQL 半同步的实现及原理
8、简述 MySQL MVCC 的工作机制
9、简述对 innodb_flush_log_at_trx_commit 的理解
10、简述 UPDATE t SET c1 = c1 + 2 WHERE id = 10; 的执行过程
11、什么情况下,可能用不到索引

实操题:
12、有个 MySQL 5.5 实例,共约 500G,如何安全升到 5.7 版本
13、有 10 个实例,每个约 200G,共约 1.5T,请设计合适的备份策略
14、对约 200G 的表进行 DDL,如何操作更安全稳妥、影响最小
15、当频繁出现 unauthenticated user 时,可能是什么原因引起的,如何解决
16、列举几个通用的 InnoDB 存储引擎优化思路、方案

开放题:
17、分库分表架构设计中,都应该考虑哪些关键因素
18、MySQL 数据库负载高的排查和解决办法
19、你以前遇到过印象最深刻的 MySQL 故障、优化案例,你是如何处理的
20、你刚入职新公司成为 DBA,请问你打算从哪些方面快速上手
21、MySQL 有哪些高可用架构,你会选择哪一个,为什么
 
补充(可能会问到的高频问题):
22、pt-osc 和 gh-ost 的原理
23、并行复制的原理
24、请用 Python写一个读取文件的脚本
25、Redis 内存采用的是什么
26、Redis 主从和持久化是怎么做的
27、LSN 指的是什么
28、Redis 的高可用你是怎么做的
29、ACID 特性及各事务隔离级别的特点、问题
30、乐观锁、悲观锁的含义
已邀请:

iamkuboy

赞同来自:

1、
1228077-20171222172528412-149389594.png

  • 主从复制原理

  1. 主从复制由 master、slave 角色组成,线程有 master 中的 dump thread,slave 的 I/O thread 和 SQL thread。
  2. 主从复制结构部署后,在 master 上进行的增删改会由 dump thread 写入到 binlog 中,并通知 slave;
  3. I/O thread 从 master 读取 binlog 内容,转存到 slave 的 relay log 文件中;
  4. SQL thread 会持续读取 relay log,解析其中的 SQL,在 slave上不断执行。

 
  • 异步复制

  1. 在 master 上进行增删改时,不会询问 slave 的状态、也不会等待 slave 是否追平 SQL,而是不断的写入、生成 binlog。
  2. master 上可以并发写入,而 SQL thread 单线程、只能串行执行,是复制的瓶颈点。
  3. 当 master 写入了大事务、slave 性能差等情况,会频繁的出现主从延迟的问题,从而导致主从读数不一致。

 
  • 半同步复制

  1. 半同步复制在 5.5 版本开始支持,是通过安装插件来开启的。
  2. 特点:单工通信(dump thread 收 ACK/发 binlog)
  3. 流程为:Client Commit --> MySQL SQL Parse --> Storage Involve --> Write Binary Log --> Storage Commit --> Waiting Slave dump --> Return to Client --> Commit OK!
  4. 原理是在 master 进行增删改后,用户发出 commit 时,将修改写入 binlog,并确保至少一个 slave 转存了 relay log 后,master dump thread 接收到了 slave 的 ACK 时,master 才执行执行 commit 操作,返回客户端提交成功。
  5. 如果 master 未接收到某个 slave 返回的 ACK 时,会进行等待,等待超过 rpl_semi_sync_master_timeout 时,会关闭同步复制(rpl_semi_sync_master_enabled=OFF),此时转为异步复制。
  6. 半同步复制在一定程度的解决了主从读数不一致的问题,但是事务处理性能降低较为严重,而且在转为异步复制时还是会出现主从读数不一致的问题。

 
  • 增强半同步复制

  1. 增强半同步复制是在 5.7 版本推出的新特性,增加了参数 rpl_semi_sync_master_wait_point,并将 5.6 版本的方式设为 after_commit,5.7 版本为 after_sync。
  2. 双工通信:master 新增了独立的 ACK 响应线程,接收 slave 的 ACK,加快了半同步的效率。(dump thread 发 binlog,ACK thread 收 ACK)
  3. 流程为:Client Commit --> MySQL SQL Parse --> Storage Involve --> Write Binary Log --> Waiting Slave dump --> Storage Commit --> Return to Client --> Commit OK!
  4. 与半同步复制的区别在于先发起 binlog dump 再发起 commit,并且由独立 ACK 线程接收 ACK,加快了主从复制的效率,使得 5.7 增强半同步的效率基本与异步复制相当。
  5. 存在的问题:在 commit 阶段 master 挂了,slave 切为 new master,old master 启动后会比 new master 多一个事务。

 
 
  • 总结:异步复制、半同步复制、增强半同步复制都不等确保主从数据完全一致,需要定期做主从数据一致性校验。通常使用工具 pt-table-checksum 和 pt-table-sync,但由于该工具使用的是 crc32 算法、非 MD5,也存在极低概率的数据一致性校验出错的情况。

iamkuboy

赞同来自:

2、
  • 备份原理

流备份过程图.png

  1. 开启 redo log 拷贝线程,从最新的 checkpoint 开始顺序拷贝 redo 日志。
  2. 开启 ibd 文件拷贝线程,拷贝 ibdata1、innodb 表数据。
  3. ibd 文件拷贝结束,发出 FTWRL 命令,获取一致性时间点。
  4. 备份非 innodb 表(系统表) 和 frm 文件。
  5. 由于此时没有新事务提交,等待 redo log 拷贝完成。
  6. 最新的 redo log 拷贝完成后,意味着此时的 innodb 表和非 innodb 表数据都是最新的。
  7. 获取 binlog 一致性时间点,此时数据库的状态是一致的。
  8. 释放锁,备份结束。

 
  • 恢复原理

恢复过程.png

  1. xtrabackup 利用了 MySQL 的 InnoDB Crash Recovery 机制。
  2. InnoDB 读取 redo、对比文件的 LSN,判断 redo 中需要继续 commit 和 rollback 的事务,并刷写数据文件,是数据文件处于一致的状态。
  3. 拷贝回 datadir,完成备份恢复。

iamkuboy

赞同来自:

3、
mysqldump_备份_InnoDB_表.jpg

  • 执行一致性备份

  1. InnoDB 表:mysqldump --single-transaction --master-data=2 --flush-logs
  2. MyISAM 表:mysqldump --lock-all-tables  ——  执行过程中实例只读,直到备份结束。

 
  • 备份 InnoDB 表原理

  1. flush tables;  —— 关闭是所有已打开的表,防止因长查询或大事务导致无法关闭,不能获取 FTWRL 锁。
  2. flush tables with read lock;  ——  加全局读锁、关闭所有已打开的表、阻止 commit 操作,获得实例一致性时间点。
  3. set session transaction isolation level repeatable read;  ——  设置隔离级别为 RR,确保备份事务中的任意时间点读取的数据都是一致的。
  4. start transaction /*!40100 with consistency snapshot*/;  ——  开启一致性快照事务,需追加 --single-transaction
  5. SELECT @@GLOBAL.GTID_EXECUTED;   并  show master status;  ——  获取实例的 GTID、file、pos 信息,用于主从复制,需追加 --master-data 参数。
  6. unlock tables;  ——  释放 FTWRL 锁。
  7. select * from t;  ——  备份选中的表。只能保证 InnoDB 表一致性,其他引擎表不能保证一致性
  8. commit;  ——  备份结束,提交事务。

 
  • mysqldump 默认启用了 --lock-tables,所以会导致在 (7) 对所有表持有读锁: lock table tb read local,所以所有的 update、delete 语句会被阻塞。但是 select 语句和 insert 语句不会被阻塞。

iamkuboy

赞同来自:

4、
  • 主从数据不一致的原因

  1. 发生复制错误
  2. 发生主从切换
  3. 重做主从结构
  4. 备份恢复以后
  5. 增强半同步 master crash 后再次启动(可能产生临界事务)

 
  • 解决办法

  1. 使用 pt-table-checksum 进行主从数据一致性校验
  2. 使用 pt-table-sync 执行主从数据修复

  • 注意:工具使用 的是 crc32 校验算法,不如 MD5 精确,但是运算速度快,极低概率会出现校验结果不准确的情况。

iamkuboy

赞同来自:

5、
  • 主从复制延迟原因

  1. master 的写是多线程、多事务并发的写入。
  2. SQL thread 是单线程,并且只能串行执行。它是主从复制的瓶颈点,也是优化的关键。

 
  • 解决办法 —— 一切为了提高 SQL thread 执行效率:

  1. 提高从库机器的配置(CPU、SSD、PCI-E SSD)。
  2. master 开启 binlog group commit。
  3. slave 开启并行复制(Enhanced Multi-Threaded Slave)。
  4. slave 开启 writeset。
  5. 表结构设计必有主键,而且主键要短小。
  6. 应用程序使用适当的 cache,减小数据库的压力。

iamkuboy

赞同来自:

6、
  • double write 的设计原因

  1. InnoDB 存储引擎在 crash 时写 page 时有 partial write 的问题
  2. redo log 记录的是逻辑操作,不是物理块操作,无法修复 partial write 问题

double_write.png

  • double write 的工作机制

  1. 在 mem 和 ibdata1 中各分配 2个 1M 的空间(连续的 128个 page,2个 extend)
  2. 在 flush page 时,首先将 page copy 到 mem 中的 double write buffer 中
  3. 再将 D W buffer 中的 page 分 2次写到 ibdata1 中的 double write (extend)(连续存储,顺序写,性能高),每次写入 1M
  4.  将 D W buffer 中的 page 写入数据文件中(离散写,效率相对低)
  5. 对 ibdata1 中的 double write (extend) 标记为可覆盖的
  6. 如果此时 crash,再次启动 mysqld 时,crash recovery 阶段可以从 ibdata1 中的 double write (extend) 中读取 page,刷入数据文件,再继续应用 redo。

iamkuboy

赞同来自:

7、
  • MySQL 半同步的实现及原理

  1. 半同步复制在 5.5 版本开始支持,是通过安装插件来开启的。
  2. 特点:单工通信(dump thread 收 ACK/发 binlog)
  3. 流程为:Client Commit --> MySQL SQL Parse --> Storage Involve --> Write Binary Log --> Storage Commit --> Waiting Slave dump --> Return to Client --> Commit OK!
  4. 原理是在 master 进行增删改后,用户发出 commit 时,将修改写入 binlog,并确保至少一个 slave 转存了 relay log 后,master dump thread 接收到了 slave 的 ACK 时,master 才执行执行 commit 操作,返回客户端提交成功。
  5. 如果 master 未接收到某个 slave 返回的 ACK 时,会进行等待,等待超过 rpl_semi_sync_master_timeout 时,会关闭同步复制(rpl_semi_sync_master_enabled=OFF),此时转为异步复制。
  6. 半同步复制在一定程度的解决了主从读数不一致的问题,但是事务处理性能降低较为严重,而且在转为异步复制时还是会出现主从读数不一致的问题。

iamkuboy

赞同来自:

8、
  • MVCC

  1. MVCC 是多版本并发控制(Multi-Version Concurrency Control)的简称。
  2. MVCC 配合 undo 使 InnoDB 存储引擎能够支持可重复读。
  3. InnoDB 会将 DML 修改的行的旧数据(前镜像)存储在 undo 中。
  4. MVCC 使得数据库读不会对数据加锁,普通的 SELECT 请求不会加锁,提高了数据库的并发处理能力。
  5. 借助MVCC,数据库可以实现 RC、RR 等隔离级别,用户可以查看当前数据的前一个或者前几个历史版本。保证了 ACID 中的I特性(隔离性)。

 
  • MySQL MVCC 的工作机制

  1. InnoDB 表是 IOT 表,每个表有 3 个隐藏列:DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR,后 2个分别存储事务 ID 和回滚指针。
    64.jpg
  2. 回滚指针指向上一个事务 ID 对应的前镜像在 undo 中的位置,当插入一条新数据时,记录上对应的回滚段指针为 NULL。
    640.jpg
  3. update 时先用 x 锁锁定该行,再把该行数据 copy 到 undo 中,修改该行数据,填写事务 ID,修改回滚指针指向 undo 前镜像位置,记录 redo(包括对 undo 的操作)。
  4. session2 查询返回的未修改数据就是从这个 undo 中返回的。
  5. MySQL就是根据记录上的回滚段指针及事务 ID 判断记录是否可见,如果不可见继续按照DB_ROLL_PTR 继续回溯查找。

iamkuboy

赞同来自:

9、
  • innodb_flush_log_at_trx_commit = N(设置 redo log flush 方式)

  1. N = 0 :事务提交时,依靠 InnoDB 的 master 线程每秒执行 1次 flush log file。因此如果 MySQL crash,那么就会丢失 1秒的事务。
  2. N = 1:事务提交时,每个事务执行 1次 redo log buffer flush log file,并且立即刷新(fsync())。注意:因为 OS 的“延迟写”特性,此时的刷入只是写到了 OS 的 cache 中,因此执行 fsync 操作才能保证一定持久化到了硬盘中。
  3. N = 2:事务提交时,会将 redo log buffer flush log file,但是不会立即进行 fsync() 操作,而是等待 OS 调用 fsync()。此时若 OS crash 而未及时 fsync(),也可能会丢失一部分事务。

 
  • sync_binlog =  N(设置 binlog flush 方式)

  1. N > 0:向 binlog 写入 N条 SQL 或 N个事务后,就把 binlog 的数据刷新到磁盘上。
  2. N = 0:不主动 flush binlog 的数据到磁盘上,而是由操作系统决定。

 
  • 可以看到,只有 N = 1才能真正地保证事务的持久性,但是由于刷新操作 fsync() 是阻塞的,直到完成后才返回,我们知道写磁盘的速度是很慢的,因此 MySQL 的性能会明显地下降。如果不在乎事务丢失,0和2能获得更高的性能。
  • 所以主库建议设置双1,从库建议设置 2、0。

iamkuboy

赞同来自:

10、
  • 不懂,求解答

iamkuboy

赞同来自:

11、
  • 索引为何不可用

  1. 通过索引扫描的记录数超过 20%~30%,可能会变为全表扫描
  2. 联合索引中,第 1个索引列使用 range scan(这时只能用到部分索引)
  3. 联合索引中,第 1个查询条件不是最左索引列
  4. 模糊查询条件列,最左以通配符 % 开始
  5. 堆表使用 hash 索引时,使用 range scan 或者 order by
  6. 多表 join 时,排序字段不属于驱动表,无法利用索引完成排序
  7. 两个独立索引,其中一个用于检索,一个用于排序(只能用到一个索引)
  8. join 查询时,join 列数据类型不一致,也会导致索引不可用

要回复问题请先登录注册