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 performance_schema.replication_applier_status_by_worker\G

2、步骤1会查出具体哪张表的问题数据，修改查询出来的异常数据

3、跳过异常步骤 set global sql_slave_skip_counter =1;

4、重新启动同步操作 start slave;

5、查询同步状态是否正常 show slave status\Gmysql连接数异常

监控脚本

监测内容：

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

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' > cspwcsdb20190111-3.txt

2、转换delete语句为insert语句

cat cspwcsdb20190111-3.txt | 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 主从同步的监控

show processlist讲解

mysql EXPLAIN分析

抓包工具fiddler分享

mysql恢复delete数据