MySQL经典案例分析_mysql异常案例分析
问题背景
mysql主从同步
1、结构：主库业务数据增删改查，从库是负责报表的查询
2、问题：主从异常导致报表数据不准确，上线期间，出现多次，发现时同步已经中断很长时间。
3、解决：脚本⾃动监控+短信提醒，及时发现异常，⼈⼯⼲预恢复。
mysql查询效率
问题：系统上线运⾏后，随着数据的不断增长，可能⼀些语句执⾏会越来越慢。分销系统
解决⽅向：借助mysql EXPLAIN 排查sql语句。进⾏针对性修改。
mysql连接数增长异常
1、问题：4⽉底多次出现知识库mysql连接被占满，导致系统故障。
不是瞬间，隔⼏天会出现⼀次。
2、排查：死锁、连接未关闭(接⼝平台groovy脚本)、show processlist
3、解决：根据排查，优化代码及jdbc连接数配置，解决故障。
代码执⾏效率
排查：借助Fiddler(http协议调试代理⼯具)，查执⾏慢的代码。
解决：优化代码结构，解决问题。
mysql主从同步监控脚本建设全包
监测内容：
1、检查主机IP及端⼝是否⼀致
2、检查主从mysql-bin⽂件是否⼀致
3、检查主从⽇志⽂件位置Position是否⼀致
4、检查slave_IO_running和slave_sql_running两个线程是否启动【重要】
异常短信发送：使⽤curl -d 参数 请求连接 (内容及号码)
⼈⼯⼲预
1、通过sql查询导致同步异常的数据 select * from plication_applier_status_by_worker\G
2、步骤1会查出具体哪张表的问题数据，修改查询出来的异常数据
3、跳过异常步骤 set global sql_slave_skip_counter =1;
4、重新启动同步操作 start slave;
5、查询同步状态是否正常 show slave status\Gmysql连接数异常
监控脚本
监测内容：
sqlyog的快捷键
1、查询mysql进程，select * from infomation_schema.processlist
Id: 就是这个线程的唯⼀标识\User: 启动线程的⽤户\Host: 记录了发送请求的客户端的 IP 和 端⼝号\DB: 数据库名称\Command: 是指此刻该线程正在执⾏的命令\Time: 表⽰该线程处于当前状态的时间(秒)\State: 线程的状态\Info: 记录线程执⾏的语句。默认只显⽰前100个字符.
2、查询锁表信息：show open tables where in_use> 0;
3、当连接数超出阈值时，短信提醒，并且抓取1和2的实时结果，定位问题连接。
排查优化
排查：
1、select * from infomation_schema.processlist及锁表信息查询出耗时进程，包含3个：A、sleep进程；
B、km_search_log；
C、km_task_sheet 及sys_task_type 。
2、排查A：检查jdbc配置，db.druid.timeBetweenEvictionRunsMillis=60000，db.druid.initialSize=5，60秒关闭空闲连接，B语句耗时长、C语句执⾏间隔短于执⾏时长，造成⼀直在创建新的连接，初始化连接⽐较⼩，sleep连接不能回收。
优化--现在正常情况是在30-40之间
1、调整初始化连接⾄30，减少连接的新建次数，空出回收空闲连接的时间。
2、梳理登录业务，屏蔽km_search_log查询，效果明显
3、反馈信息提醒业务(km_task_sheet 及sys_task_type)，原20秒执⾏⼀次，调整频率为两分钟，减少语句进程⼀直叠加。mysql查询效率-EXPLAIN
EXPLAIN查询结果字段解释
id ：编号，id值越⼤执⾏优先级越⾼，id值相同则从上往下执⾏，id为null最后执⾏。
select_type ：查询类型
table ：表名
partitions ：分区
type ：类型，type常见类型从最优到最差：system > const > eq_ref > ref > range > index > ALL，
possible_keys ：预测⽤到的索引
key ：实际使⽤的索引，如果没有为null。根据这个字段判断是否需要增加索引。
key_len ：实际使⽤索引的长度，
ref ：表之间的引⽤
rows ：通过索引查询到的数据量
filtered ：指返回结果的⾏占需要读到的⾏(rows列的值)的百分⽐。
Extra ：额外的信息
1、Using index
2、Using where
3、Using where Using index
4、NULL
计算机and运算怎么算5、Using index condition
6、Using temporary表⽰MySql需要创建⼀张临时表来处理查询。⼀般需要优化mysql查询效率-案例分析
1、关联表字段类型不⼀致，导致索引失效
描述：原因是his_phs_send的phs_id和csp_contact的call_id字段类型不⼀致，导致索引失效
排查过程：
EXPLAIN 查看语句分析结果，d表Extra提⽰未使⽤索引，key值使⽤索引项为空，type为ALL，效率最慢，数据超600W。缩减语句⾄his_phs_send和csp_contact关联，索引还未执⾏，定位⾄d.call_id未⾛索引，排查原因，phs_id为bigint类型，call_id为varchar类型，修改phs_id为varchar，语句执⾏时间由9秒多减少⾄0.3秒。
2、减少关联出来的数据量
3、对应字段增加索引
4、减少语句中的⼦查询
多个⼦查询拼接的sql语句，索引不会⽣效代码执⾏效率-Fiddler
mysql恢复delete数据
1、⽇志查delete删除语句
mysqlbinlog --no-defaults --database=cspwcsdb --start-datetime="2019-01-11 08:20:09" --stop-datetime="2019-01-11 10:00:50" --base64-output=decode-rows -v -v mysql-bin.000022 | sed -n '/### DELETE FROM /,/COMMIT/p' >
mysql将时间戳转换成日期
2、转换delete语句为insert语句
| sed -n '/###/p' | sed 's/### //g;s//*./,/g;s/DELETE FROM/INSERT
INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.),/\1;/g' | sed 's/@[1-9]=//g' > t1.sql
3、替换；和其余@10之后的@序号，⽣成需要执⾏的insert语句
备注：此⽅式只恢复过少量数据，⼤批量数据没有使⽤过。当时⽣成的⽂件未保存，⼤家可以测试下，看下⽣成的⽂件内容。
总结
本次分享主要涵盖了mysql同步异常监控、恢复、sql性能优化、代码优化⼯具及delete数据恢复，在现场实施使⽤率较⾼。
mysql面试题 增删改查
执⾏效率的提升，直接提升⽤户感知。mysql 主从同步的监控
show processlist讲解
mysql EXPLAIN分析
抓包⼯具fiddler分享
mysql恢复delete数据

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。