目录
想把MySQL有变更的数据分发到其他异构数据库,都有什么可选方案
MySQL什么情况下DML操作不会记录binlog?
MySQL中ANALYZE TABLE的作用是?生产上操作会有什么风险?
MySQL生产中上亿数据大表怎么在线增加一个字段并设定默认值?
MySQL表中有自增列id,但是表中的id列数据不连续,产生的可能原因是什么?
如何定位造成MySQL慢的罪魁祸首慢SQL及分析优化思路?
MySQL中InnoDB表自增主键如何从0开始?
MySQL常见的主从复制错误有哪些?如何处理?
Redis中如何发现并优化生产环境的big key?
一、想把MySQL有变更的数据分发到其他异构数据库,都有什么可选方案
(一)触发器方式(不推荐)
触发器处理的方式,在表上建立触发器,将增删改动作捕捉到增量表
(二)基于时间戳
表上如果有更新时间/插入时间的时间戳,可以根据时间戳捕捉增量数据
(三)基于binlog方式(推荐)
MySQL目前比较好也是目前业务用的比较多的增量方案都是基于binlog实现
1、在MySQL提交的DML操作正常情况都会记录到binlog(也是主从复制的基础),首先上游通过解析binlog获取到增量日志
2、将解析到的更新事件存入消息队列,如kafka/MQ等
3、根据下游数据库种类,分别用不同的消费组件进行队列消费,拼接成SQL进行数据更新
(四)业界典型的方案
开源方案
1、阿里巴巴开源组件canal+kafka+自写消费组件
2、阿里巴巴开源组件otter(支持MySQL到MySQL的数据同步)
3、阿里巴巴开源迁移工具yugong(支持Oracle->Oracle/Oracle->MySQL的全量/增量迁移)
4、linkedin开源组件databus
5、美团点评开源组件puma
商业方案
各云厂商提供的DTS方案
二、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无法写入
三、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才是正道
四、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、第三方工具加列同样可能造成主从延迟,因此注意控制速率
五、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以前修改自增值重启后会丢失,务必注意一下
六、如何定位造成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及对应的执行计划
(三)执行计划需要关注的部分可以参考
(四)慢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、防范于未然,不要等到问题严重了才来做优化
七、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支持持久化,妈妈再也不用担心自增值丢啦
八、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部分补全
九、Redis中如何发现并优化生产环境的big key?
Redis中的大key一直是重点需要优化的对象,big key既占用比较多的内存,也可能占用比较多的网卡资源,造成redis阻塞,因此我们需要找到这些big key进行优化
(一)寻找big key
通常来说找到redis中的big key有如下几种方法
1、redis-cli自带--bigkeys,例如:redis-cli -h -a --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
(二)优化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上等方式优化
有任何问题都可以加微信讨论,欢迎沟通~~ 互相进步!
微信:lvqingshan_