《叶问》是知数堂新设计的互动栏目，不定期给大家提供技术知识小贴士，形式不限，或提问、或讨论均可，并在当天发布答案，让大家轻轻松松利用碎片时间就可以学到最实用的知识点。
2019年07月02日，周二
想把MySQL有变更的数据分发到其他异构数据库，都有什么可选方案
一、触发器方式（不推荐）
1、触发器处理的方式，在表上建立触发器，将增删改动作捕捉到增量表二、基于时间戳
1、表上如果有更新时间/插入时间的时间戳，可以根据时间戳捕捉增量数据三、基于binlog方式（推荐）
MySQL目前比较好也是目前业务用的比较多的增量方案都是基于binlog实现
1、在MySQL提交的DML操作正常情况都会记录到binlog（也是主从复制的基础），首先上游通过解析binlog获取到增量日志
2、将解析到的更新事件存入消息队列，如kafka/MQ等
3、根据下游数据库种类，分别用不同的消费组件进行队列消费，拼接成SQL进行数据更新四、业界典型的方案
4.1开源方案
1、阿里巴巴开源组件canal+kafka+自写消费组件
2、阿里巴巴开源组件otter(支持MySQL到MySQL的数据同步)
2、阿里巴巴开源迁移工具yugong（支持Oracle->Oracle/Oracle->MySQL的全量/增量迁移）
3、linkedin开源组件databus
4、美团点评开源组件puma4.2商业方案
1、各云厂商提供的DTS方案
2019年07月04日，周四
MySQL什么情况下DML操作不会记录binlog？
以下情况可能会造成DML不会记录binlog：
1、并未开启binlog
2、在执行DML之前set sql_log_bin=0;
3、DML操作提交后，未完成binlog写入，此时MySQL crash，MySQL重新启动后，会回滚该操作
4、DML操作提交后，由于sync_binlog配置为非0，MySQL服务器down机导致binlog并未持久化
5、主库提交事务，同步到从库，但从库并未开启log_slave_updates，也无法记录binlog
6、DML操作在temporary表（非memory引擎）上，由于是会话级的数据变更，并不记录binlog
7、update/delete等DML并没有达到数据更新
8、其他原因如：binlog文件权限、文件句柄、inode耗尽、磁盘空间满等原因导致binlog无法写入
2019年07月09日，周二
MySQL中ANALYZE TABLE的作用是？生产上操作会有什么风险？
一、ANALYZE TABLE的作用
1、ANALYZE TABLE 会统计索引分布信息
2、对于 MyISAM 表，相当于执行了一次 myisamchk --analyze
3、支持 InnoDB、NDB、MyISAM 等存储引擎，但不支持视图（view）
4、执行 ANALYZE TABLE 时，会对表加上读锁（read lock）
5、该操作会记录binlog
二、生产上操作的风险
1、ANALYZE TABLE的需要扫描的page代价粗略估算公式：sample_pages * 索引数 * 表分区数
2、因此，索引数量较多，或者表分区数量较多时，执行ANALYZE TABLE可能会比较费时，要自己评估代价，并默认只在负载低谷时执行
3、特别提醒，如果某个表上当前有慢SQL，此时该表又执行ANALYZE TABLE，则该表后续的查询均会处于waiting for table flush的状态，严重的话会影响业务，因此执行前必须先检查有无慢查询
备注：上面多次提到MyISAM，仅是本次总结所需，并不推荐大家使用MyISAM引擎，使用InnoDB才是正道
2019年07月11日，周四
MySQL生产中上亿数据大表怎么在线增加一个字段并设定默认值？
一、MySQL8.0版本
如果满足以下条件：
1、不是压缩表
2、不是data dictionary tablespace
3、不是全文索引表
4、不是临时表
5、新增列在最后位置
满足以上条件，可以通过instant方式完成快速加列，即修改metadata信息，代价非常小，秒级完成
二、MySQL5.6、MySQL5.7
1、可以这么说，虽然支持inplace（不阻塞同时在跑的DML），但还需要rebuild表，代价可能较大并且耗时可能会非常长，也会造成非常严重的主从延迟
2、因此生产环境还是推荐使用pt-osc/gh-ost等第三方工具进行在线加列，
3、需要注意的是pt-osc会建触发器，如果原先表上本身就有触发器就不能使用了
4、另外需要注意的是通过第三方工具加列时需要考虑到空间占用问题（影子表及加列过程中产生的binlog）
5、第三方工具加列同样可能造成主从延迟，因此注意控制速率
详细ONLINE DDL见：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
2019年07月16日，周二
MySQL表中有自增列id，但是表中的id列数据不连续，产生的可能原因是什么？
可能的情况有如下几种：
1、虽然表定义了自增主键，但是插入时从全局ID生成器获取ID，导致每个表分配到的ID并不是连续的
2、表中数据进行过删除
3、session A插入了数据，获取了自增id为10，session B也插入了数据，但session A回滚了，导致id=10的空洞
4、设置了innodb_autoinc_lock_mode=2，高并发或批量插入的情况下导致自增ID不连续
5、手动调整过AUTO_INCREMENT，导致中间有空洞
6、设置步长非1，导致每次自增并不是加1
PS:8.0以前修改自增值重启后会丢失，务必注意一下
2019年07月18日，周四
如何定位造成MySQL慢的罪魁祸首慢SQL及分析优化思路？
一、首先需要打开慢查询
1、设置slow_query_log = 1打开慢查询
2、通过设置long_query_time调整慢查询SQL的阈值
3、通过设置min_examined_row_limit记录慢查询的最小扫描行数
二、其次关注慢查询记录中的重要关注点
1、Query_time：慢查询的实际查询时间
2、Rows_sent：发送给客户端的行数
3、Rows_examined：扫描的行数
4、最后需要关注具体的sql及对应的执行计划
三、执行计划需要关注的部分可以参考
《叶问》第13期《MySQL常用的SQL调优手段或工具有哪些》
四、慢SQL分析需要的元素
1、表结构信息：show create table
2、数据量信息：select count(*) from table/show table status like 'xxx';/information_schema.tables
3、索引统计信息：show index from
4、执行计划：EXPALIN select * from t
五、主要的优化手段有如下：
1、SQL语句的改写，优化不良语句
2、符合业务场景需求的索引建立
3、表结构上对列属性、表结构的调整
4、MySQL参数的调整
5、和开发应用"撕"性能
六、最后有一些注意点：
1、实战优化很难一步到位，模拟测试环境很重要（不要随便在生产环境测试）
2、业务至上，数据至上，而后才是优化
3、衡量得失，索引也有维护成本，不是越多越好
4、没有对比就没有成就感，记得留存"胖胖"的SQL，形成优化报告
5、防范于未然，不要等到问题严重了才来做优化
2019年07月23日，周二
MySQL中InnoDB表自增主键如何从0开始？
1、实际上，MySQL无法真正设置InnoDB表AUTO_INCREMENT=0
2、但是我们可以人为修改SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
3、指定自增列插入0值，如insert into t(id,name) values(0,'zhangsan');
另外有几点说明：
1、尽管MySQL允许设置SQL_MODE为NO_AUTO_VALUE_ON_ZERO并插入0值，但是还是不建议这样做，例如主从SQL_MODE不一致可能造成数据不一致
2、8.0以后AUTO_INCREMENT支持持久化，妈妈再也不用担心自增值丢啦
2019年07月25日，周四
MySQL常见的主从复制错误有哪些？如何处理？
一、1032错误
1032错误即从库比主库少数据，分为UPDATE场景和DELETE场景
UPDATE场景处理思路是：
1、主要处理思路是将丢失的数据在从库补上
2、找到sql_thread停止位置(Relay_Master_Log_File + Exec_Master_Log_Pos)
3、去主库解析对应位置binlog，获取到UPDATE相关的数据
4、在slave上插入数据
5、重启sql_thread
DELETE场景处理思路：
1、主要的处理思路就是跳过该事务
2、非GTID环境下通过设置sql_slave_skip_counter=1跳过该事务
3、GTID环境下通过注入空事务方式跳过该事务，简要步骤如下：
3.1、set gtid_next='xxx';
3.2、begin;commit;
3.3、set gtid_next='AUTOMATIC';
3.4、详细可参考：http://imysql.com/2014/07/31/mysql-faq-exception-replication-with-gtid.shtml
4、重启sql_thread
二、1062错误
1062错误即从库比主库多数据（主键冲突情况）
处理思路比较简单，即删除对应的主键冲突数据即可：
1、删除从库上对应的数据（根据主键）
2、重启sql_thread
三、1236错误
1236错误即从库需要开始同步的binlog位置已经不存在主库上，该错误出现分几种情况
1、GTID从库需要开始复制的GTID要比主库上已经purged GTID位置要小
2、GTID从库GTID_SET比主库上缺失部分（如主库有多个GTID_SET，而从库只有一个）
3、非GTID环境下，从库去拉binlog的时间找不到对应的binlog（即从库当前的binlog比主库最旧的binlog之间存在缺失）
那么对应的处理方式是：
1、对于1、3两种情况，建议是直接通过备份的方式直接重新做主从
2、对于第2种情况，可以通过set global gtid_purged='xxx'的方式将差异的GTID_SET部分补全
2019年07月30日，周二
Redis中如何发现并优化生产环境的big key？
Redis中的大key一直是重点需要优化的对象，big key既占用比较多的内存，也可能占用比较多的网卡资源，造成redis阻塞，因此我们需要找到这些big key进行优化
一、寻找big key
通常来说找到redis中的big key有如下几种方法
1、redis-cli自带--bigkeys，例如：redis-cli -h <hostip> -a <password> --bigkeys
2、获取生产Redis的rdb文件，通过rdbtools分析rdb生成csv文件，再导入MySQL或其他数据库中进行分析统计，根据size_in_bytes统计bigkey
3、通过python脚本，迭代scan key，每次scan 1000，对扫描出来的key进行类型判断，例如：string长度大于10K，list长度大于10240认为是big bigkeys
4、其他第三方工具，例如：redis-rdb-cli
地址：https://github.com/leonchen83/redis-rdb-cli
二、优化big key
1、优化big key的原则就是string减少字符串长度，list、hash、set、zset等减少成员数
2、以hash类型举例来说，对于field过多的场景，可以根据field进行hash取模，生成一个新的key，例如原来的
hash_key:{filed1:value, filed2:value, filed3:value ...}，可以hash取模后形成如下key:value形式
hash_key:mod1:{filed1:value}
hash_key:mod2:{filed2:value}
hash_key:mod3:{filed3:value}
...
取模后，将原先单个key分成多个key，每个key filed个数为原先的1/N
3、string类型的big key，如文章正文，建议不要存入redis，用文档型数据库MongoDB代替或者直接缓存到CDN上等方式优化
扫一扫
408
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
