叶问【转自知数堂微信公众号】

转自

《叶问》是知数堂新设计的互动栏目，不定期给大家提供技术知识小贴士，形式不限，或提问、或讨论均可，并在当天发布答案，让大家轻轻松松利用碎片时间就可以学到最实用的知识点。

知数堂 - 最靠谱、最有品质的培训品牌 http://www.3wedu.net/

 叶问专辑 https://mp.weixin.qq.com/mp/homepage?__biz=MzI1OTU2MDA4NQ%3D%3D&hid=15&sn=8a530aa309c1fe6e4d99b3a0d49a9695

2018年6月10日，周日

MySQL主从复制什么原因会造成不一致，如何预防及解决？

一、导致主从不一致的原因主要有： 

1. 人为原因导致从库与主库数据不一致（从库写入）

2. 主从复制过程中，主库异常宕机

3. 设置了ignore/do/rewrite等replication等规则

4. binlog非row格式

5. 异步复制本身不保证，半同步存在提交读的问题，增强半同步起来比较完美。 但对于异常重启（Replication Crash Safe），从库写数据（GTID）的防范，还需要策略来保证。

6. 从库中断很久，binlog应用不连续，监控并及时修复主从

7. 从库启用了诸如存储过程，从库禁用存储过程等

8. 数据库大小版本/分支版本导致数据不一致？，主从版本统一

9. 备份的时候没有指定参数 例如mysqldump --master-data=2 等

10. 主从sql_mode 不一致

11. 一主二从环境，二从的server id一致

12. MySQL自增列 主从不一致

13. 主从信息保存在文件里面，文件本身的刷新是非事务的，导致从库重启后开始执行点大于实际执行点

14. 采用5.6的after_commit方式半同步，主库当机可能会引起主从不一致，要看binlog是否传到了从库

15. 启用增强半同步了（5.7的after_sync方式），但是从库延迟超时自动切换成异步复制

二、预防和解决的方案有：

1. master:innodb_flush_log_at_trx_commit=1&sync_binlog=1

2. slave:master_info_repository="TABLE"&relay_log_info_repository="TABLE"&relay_log_recovery=1

3. 设置从库库为只读模式

4. 可以使用5.7增强半同步避免数据丢失等

5. binlog row格式

6. 必须引定期的数据校验机制

7. 当使用延迟复制的时候，此时主从数据也是不一致的（计划内），但在切换中，不要把延迟从提升为主库哦~

8. mha在主从切换的过程中，因主库系统宕机，可能造成主从不一致（mha本身机制导致这个问题）

2018年6月11日，周一

你为什么会决定进行分库分表，分库分表过程中遇到什么难题，如何解决的？

一、为什么决定进行分库分表？

1. 根据业务类型，和业务容量的评估，来选择和判断是否使用分库分表

2. 当前数据库本事具有的能力，压力的评估

3. 数据库的物理隔离，例如减少锁的争用、资源的消耗和隔离等

4. 热点表较多,并且数据量大，可能会导致锁争抢，性能下降

5. 数据库的高并发，数据库的读写压力过大，可能会导致数据库或系统宕机

6. 数据库（MySQL5.7以下）连接数过高，会增加系统压力

7. 单表数据量大，如SQL使用不当，会导致io随机读写比例高。查询慢（大表上的B+树太大，扫描太慢，甚至可能需要4层B+树）

8. 备份和恢复时间比较长

二、都遇到什么问题?

1. 全局pk（主键和唯一索引）的冲突检测不准确，全局的自增主键支持不够好

2. 分片键的选择。如没有选择好，可能会影响SQL执行效率

3. 分布式事务，中间价产品对分布式事务的支持力度

4. 对于开发来说，需要进行业务的拆分

5. 对于开发来说，部分SQL不兼容则需要代码重构，工作量的评估

6. 对于开发来说，跨库join，跨库查询

 

三、如何解决?

1. 使用全局分号器。或者使用全局唯一id，（应用生成顺序唯一int类型做为全局主键）

2. 应用层来判断唯一索引

3. 配合应用选择合适的分片键，并加上索引

4. 配合应用，配合开发，对不兼容SQL的进行整改

2018年6月12日，周二

MySQL高可用架构应该考虑什么? 你认为应该如何设计？

一、MySQL高可用架构应该考虑什么？

1. 对业务的了解，需要考虑业务对数据库一致性要求的敏感程度，切换过程中是否有事务会丢失

2. 对于基础设施的了解，需要了解基础设施的高可用的架构。例如 单网线，单电源等情况 

3. 对于数据库故障时间掌握，业务方最多能容忍时间范围，因为高可用切换导致的应用不可用时间

4. 需要了解主流的高可用的优缺点：例如 MHA/PXC/MGR 等。

5. 考虑多IDC多副本分布，支持IDC级别节点全部掉线后，业务可以切到另一个机房

 

二、你认为应该如何设计? 

1. 基础层 和基础运维部门配合，了解和避免网络/ 硬盘/ 电源等是否会出现单点故障 

2. 应用层 和应用开发同学配合，在关键业务中记录SQL日志，可以做到即使切换，出现丢事务的情况，也可以通过手工补的方式保证数据一致性，例如：交易型的业务引入状态机，事务状态，应对数据库切换后事务重做 

3. 业务层 了解自己的应用，根据不同的应用制定合理的高可用策略。 

4. 单机多实例 环境及基于虚拟机或容器的设计不能分布在同一台物理机上。 

5. 最终大招 在数据库不可用 ，可以把已提及的事务先存储到队列或者其他位置，等数据库恢复，重新应用

2018年6月13日，周三

MySQL备份，使用xtrabackup备份全实例数据时，会造成锁等待吗？那么如果使用mysqldump进行备份呢？

一、xtrabackup和mysqldump会造成锁等待吗？ 

1. xtrabackup会，它在备份时会产生短暂的全局读锁FTWL(flush table with read lock)，用于拷贝frm/MYD/MYI等文件，以及记录binlog信息。如果MyISAM表的数据量非常大，则拷贝时间就越长，加锁的时间也越长

2. mysqldump有可能会。如果只是添加 --single-transacton 选项用于保证备份数据一致性，这时就不会产生FTWL锁了。但通常我们为了让备份文件和binlog保持一致，通常也会设置 --master-data 选项用于获得当前binlog信息，这种情况也会短暂加锁

3. 数据量特别大的话，建议优先用 xtrabackup，提高备份/恢复速度。而如果数据量不是太大或者想备份单表，则建议用mysqldump了，方便逻辑恢复。各有利弊，注意其适用场景

 

二、xtrabackup冷知识

1. 基于MySQL 5.6版本开发的xtrabackup，会在备份过程中生成内部通信文件 suspend file，用于 xtrabackup 和 innobackupex 的通信，备份结束后文件删除，默认文件位置 /tmp/xtrabackup_suspended 

2. 如果在备份过程中，修改了 /tmp 的访问权限或该文件的权限，则两个程序间直接不能通信，会造成 xtrabackup hang 住，正在备份的表不能正常释放锁，会造成锁等待，此时需要强制 kill 掉 xtrabackup 进程

2018年6月15日，周五

MySQL 5.7开始支持JSON，那还有必要使用MongoDB存JSON吗？请列出你的观点/理由。

一、观点A：支持MySQL存储JSON

1.MongoDB不支持事务，而MySQL支持事务

2.MySQL相对MongoDB而言，MySQL的稳定性要优于MongoDB

3.MySQL支持多种存储引擎

二、观点B:支持MongoDB存储JSON 

1.从性能的角度考虑，对于JSON读写效率MongoDB要优于MySQL

2.MongoDB相对MySQL而言，MongoDB的扩展性要优于MySQL

3.MongoDB支持更多的JSON函数

三、总结

1.如果应用程序无事务要求，存储数据表结构复杂并且经常被修改， 例如游戏中装备等场景用MongoDB比较适合

2.如果应用程序有事务要求，存储数据的"表"之间相互有关联，例如有订单系统等场景用MySQL比较适合

3.整体来看相对看好MySQL的JSON功能，在未来官方的努力下MySQL的JSON功能有机会反超MongoDB

2018年6月17日，周日

当数据被误删除/误操作后造成数据丢失。你尝试过用什么手段来挽救数据/损失？

一、前提 
1.当数据被误删除/误操作后，第一时间要关闭数据库。业务方需要紧急挂停机公告，避免数据二次污染，用于保护数据的一致性

2.BINLOG格式为ROW格式，不讨论其他格式的BINLOG

二、数据被误操作(update/delete/drop)造成数据丢失，可以用哪些手段来恢复？ 

1.BINLOG恢复：可以使用逆向解析BINLOG工具来恢复。例如：binlog2SQL等

2.延迟从库: 可以通过解除延迟从库，并指定BINLOG结束位置点，可以实现数据恢复

三、数据被误删除(rm/物理文件损坏)造成数据丢失，可以用哪些手段来恢复？ 

1.如果有备份，可以通过备份恢复 mysqldump/xtrabackup + binlog 来实现全量+增量恢复

2.如果无备份但是有从库，可以通过主从切换，提升从库为主库，从而实现数据恢复

3.如果无备份并且无从库，但MySQL没有重启，可以通过拷贝/proc/$pid/fd中的文件，来进行尝试恢复

4.如果无备份并且无从库，但MySQL有重启，可以通过extundelete或undrop-for-innodb来恢复

2018年6月19日，周二

MySQL 5.7的复制架构,在有异步复制、半同步、增强半同步、MGR等的生产中，该如何选择？

一、生产环境中： 

几种复制场景都有存在的价值。下面分别描述一下： 

1. 从成熟度上来选择，推荐：异步复制(GTID+ROW)

2. 从数据安全及更高性能上选择：增强半同步 （在这个结构下也可以把innodb_flush_log_trx_commit调整到非1， 从而获得更好的性能)

3. 对于主从切换控制觉的不好管理，又对数据一致性要求特别高的场景，可以使用MGR

二、理由：

1. 异步复制，相对来讲非常成熟，对于环境运维也比较容易上手 

2. 增强半同步复制，可以安全的保证数据传输到从库上，对于单节点的配置上不用要求太严格，特别从库上也可以更宽松一点，而且在一致性和性能有较高的提升，但对运维上有一定的要求

3. MGR组复制。相对增强半同步复制，MGR更能确保数据的一致性，事务的提交，必须经过组内大多数节点（n/2+1）决议并通过，才能得以提交。MGR架构对运维难度要更高，不过它也更完美

总的来讲，从技术实现上来看：MGR> 增强半同步>异步复制。

未来可能见到更多的MGR在生产中使用，对于MySQL的运维的要求也会更上一层楼。

2018年6月20日，周三

为什么说pt-osc可能会引起主从延迟，有什么好办法解决或规避吗？

• 若复制中binlog使用row格式，对大表使用pt-osc把数据从旧表拷贝到临时表，期间会产生大量的binlog，从而导致延时

• pt-osc在搬数据过程中insert...select是有行锁的，会降低事务并行度；且pt-osc搬数据过程中生成的binlog不是并行的，所以在slave不能并行回放

• 可以通过设定参数 --chunk-size、--chunk-time控制每次拷贝数据大小，也可以设定--max-log、check-interval、check-slave-lag等参数控制主从复制延迟程度（但这样可能会造成pt-osc工作耗时太久，需要自行权衡）

2018年6月21日，周四

你遇到过哪些原因造成MySQL异步复制延迟？

1. master上多为并发事务，salve上则多为单线程回放（MySQL 5.7起，支持真正的并行回放，有所缓解）

2. 异步复制，本来就是有一定延迟的（否则也不叫做异步了，介意的话可以改成半同步复制）

3. slave机器一般性能比master更弱（这是很常见的误区，其实slave对机 器性能要求并不低）

4. 有时为了节省机器资源，会在slave上运行多个实例

5. 表结构设计不合理，尤其是在MySQL 5.6之前没主键，几乎会造成所有更新都全表扫描一遍，效率非常低

6. slave上运行大量只读低效率的SQL

7. 大量大事务，也会造成slave无法并行回放 

8. 业务设计缺陷，或网络延迟等导致延迟

2018年6月22日，周五

MySQL每天产生了多大容量的binlog，用SQL语句能查到吗？

首先，这是个假设性命题（又一个钓鱼题）。 
这个需求完全可以通过系统层命令，配合MySQL中的“FLUSH BINARY LOGS”快速完成。 
运行SHOW MASTER/BINARY LOGS命令能查看全部binlog列表，但没办法区别哪些是当天内生成的。

2018年6月23日，周六

用什么方法可以防止误删数据？

以下几个措施可以防止误删数据，如下： 

1. 生产环境中，业务代码尽量不明文保存数据库连接账号密码信息

2. 重要的DML、DDL通过平台型工具自动实施，减少人工操作

3. 部署延迟复制从库，万一误删除时用于数据回档，且从库设置为read-only

4. 确认备份制度及时有效

5. 启用SQL审计功能，养成良好SQL习惯

6. 启用 sql_safe_updates 选项，不允许没 WHERE 条件的更新/删除

7. 将系统层的rm改为mv

8. 线上不进行物理删除，改为逻辑删除（将row data标记为不可用）

9. 启用堡垒机，屏蔽高危SQL

10. 降低数据库中普通账号的权限级别

11. 务必开启binlog

2018年6月24日，周日

MySQL 8.0相对于5.7的复制改进，都有哪些呢？

宋利兵老师：《MySQL 8.0相对于5.7的复制改进》的公开课也讨论了这个命题，简单概括主要有两部分：

一、普通复制功能改进 

1. 新增WRITESET并行复制模式，提高并行度，降低延迟 

2. 在多源复制中，可在线动态修改每个channel的filter rule，并且能在P_S中查看/监控 

3. Binary Log中存储更多元数据，并支持毫秒级别的延迟监控 

4. 对JSON Documents的复制效率更高了 

5. 支持DDL Crashsafe 

6. 增加caching_sha2_password安全策略，提高复制安全性

二、MGR功能改进:

1. 支持设置节点权重，且权重最大的在线节点将被选举为主 

2. 每个节点中存储更多的状态信息，如版本、角色等 

3. 可根据从节点的事务状态，自动化流控 

4. 离开集群的服务器自动被设置为read only，避免被误操作更新数据 

5. 可监控MGR的内存使用情况

2018年6月25日，周一

跑truncate table，4亿条数据会不会造成长时间锁表呢?有什么更好的方法吗?

最好是create新表，然后交叉rename对调，再drop/truncate table或其他方式清除数据。 

一、可操作步骤： 

1. 创建新的 tmp 表，正式表与tmp表表名交换（注意在一个SQL里完成，并锁表） 

2. 对 tmp 表创建硬链接 ln tmp.ibd tmp.ibd.hdlk 

3. mysql中删除表tmp（truncate / drop 都行）

4. 然后找个业务不繁忙的时间删除数据文件或者用coreutils 的truncate慢慢搞 

二、关于truncate table，官档解释：

Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements 
When a table is truncated, it is dropped and re-created in a new .ibd file, and the freed space is returned to the operating system

2018年6月26日，周二

明明有个索引“感觉”应该被选中，EXPLAIN时在possible_keys也有它，但最后没被选中，可能的原因有哪些？ 

一、执行计划如下：

desc select * from t1 where c2 >= 2; 
key: NULL 
key_len: NULL 
rows: 14 
filtered: 92.86 
Extra: Using where

二、可能的原因如下： 

1. 隐式转换

2. 表碎片，因为表的碎片率过高

3. 根据索引读取到的数据在整个表中的数据占比超过30%

4. 统计信息没有及时更新

三、上述执行计划的结果是：

预计扫描的行数为14行，filtered（是指返回结果的行占需要读到的行的百分比）的值为92%。

当前执行计划中filtered值92% 说明根据索引查询获取的结果占整张表的92%，在MySQL中根据索引查询的结果占整张表的数据30%则不会走索，所以不会走索引。 
另外，也有可能是表的碎片率过高或隐式转换导致的。

2018年6月27日，周三

主从复制线程均正常（为Yes，也没报错），Master的binlog已到binlog.000100，但slave上看到Master_Log_File却只到binlog.000090，可能的原因有哪些?

首先要注意，这是Master_Log_File IO线程延迟，并不是Relay_Master_Log_File SQL线程延迟。

一、可能的原因如下： 

1. 由于sync_relay_log值过低，导致Slave频繁刷新relay_log文件，使 Slave的硬盘资源消耗过高，所以导致SlaveIO Thread很慢。 

2. Master/Slave压力过大导致Slave IO Thread不能及时响应， 无法及时获得Master的event。 

3. 网络丢包严重。小包可以连接并且保持连接不断，但是大包就无法发送。可能是Master和Slave关于TCP MTU值设置不一致导致。 

4. Master和Slave网络链接已经断开。但slave_net_timeout值等于0（表示完全禁用心跳）或者slave_net_timeout和Slave_heartbeat_period非常大（表示检测主从心跳的时间）。 

5. Master的binlog非常大，io线程的file很长时间都在读同一个。 

二、总结 
本次案例是在主库进行压力测试，在压力测试的过程中，因为Master本身的压力就很大Master来不及把binlog发送给Slave。所以表面上看起来没有延迟，但实际上已经产生了延迟。

。
2018年7月4日，周三

如何优化Linux操作系统用于MySQL环境？

 

一、初级玩法 

1. 在BIOS及内核层面关闭NUMA 

2. 在BIOS层面将CPU、内存均设置最大性能模式 

3. 在BIOS层面关闭CPU节能模式 

4. 修改IO Scheduler为deadline 或 noop 

5. 使用xfs文件系统，挂载选项noatime、nodiratime、nobarrier 

6. 在内核层面设置vm.swappiness<=5，vm.dirty_ratio<=10， vm.dirty_background_rati<=5 

7. 在内核层面修改用户可最大打开文件数和线程数为65535 

8. 禁用SWAP分区

二、高端玩法

1. 使用最新稳定Linux发行版 

2. 升级各个硬件设备到最新稳定firmware版本 

3. 使用SSD时，开启TRIM功能，并且可以的话文件系统block size和SSD对齐 

4. 当磁盘I/O存在瓶颈时，除了常规因素外，还需要关注中断不均衡的可能性

2018年7月5日，周四

MySQL 8.0 InnoDB哪些新特性你最期待，为什么？

1. 数据字典全部采用InnoDB引擎存储，支持DDL原子性、crash safe，metadata管理更完善 

2. 快速在线加新列（腾讯互娱DBA团队贡献） 
3. 并行redo log，并提升redo log的I/O性能 
4. 新增倒序索引 
5. 增强CBO特性 
6. 消除了buffer pool mutex（Percona的贡献） 
7. 自增ID持久化 
8. 行锁增加SKIP LOCKED和NOWAIT特性选项 
9. 新增事务CATS特性，大大提升事务性能（Michigan大学贡献） 
10. memcached plugin增强 
11. 增强JSON性能、功能 
12. 新增智能选项 innodb_dedicated_server

2018年7月10日，周二

 MySQL hang的原因有哪些？ 

1. MySQL使用资源过高导致服务器太累扛不住。例如CPU、内存、 I/O等开销。 

2. 磁盘无可用空间。 

3. MySQL频繁的创建和销毁连接。 

4. MySQL使用的最大文件打开数和连接数，超过了操作系统的限制。 

5. MySQL的锁不能有效的释放。例如持有行锁或者表锁，造成了MDL等待。 

6. MySQL的bug导致的。 

导致MySQL hang住的原因有很多，不局限于上述因素，还需要机智的你来挖掘。

2018年7月12日，周四

专访王晓伟老师，MySQL数据导入数据仓库（Hadoop）有哪几种方式？ 

1. 传统方式，采用mysqldump等工具将数据文件上传至HDFS 

2. 使用Sqoop Kettle等ETL工具，将数据表对应导入Hive的数据表 

3. 使用kafka+flume方案，将mysql binlog通过流式采集的方式导入Hadoop 

4. 设计实现Hive的快照表、增量表、全量表，实现MySQL到Hive数据的增量导入，并支持分库分表等特性。