场景:
MySQL-5.6.30, 主从架构, 只读从库的 SQL 线程卡在某一个事务两个多小时没有动过, show processlist 发现从库当时没有连接和慢查询语句;
show open TABLES where In_use>0; 发现一个表被锁定如下:
- MySQL> show open TABLES where In_use>0;
- +----------+---------------+--------+-------------+
- | Database | Table | In_use | Name_locked |
- +----------+---------------+--------+-------------+
- | cxx | t_post_xxxxxx | 1 | 0 |
- +----------+---------------+--------+-------------+
结论
从库没有线程, 说明锁定的表是从主库同步过来的语句锁定的, 应该是主库对此表的大事务操作造成.
分析
1. 通过 show slave status 确定的 position 去分析主库的 binlog, 发现生成了大量的删除 t_post_xxxxxx 的语句;
2. 查看慢查询日志, 发现 delete from t_post_xxxxxx;
3. 在主库查看 t_post_xxxxxx 表结构, 发现竟然没有主键也没有索引;
4.select count(*) from t_post_xxxxxx; 发现此表 20 多万条数据;
5. 真相大白了, 没有主键直接 delete 删除全表会生成 20 多万条 delete 语句在 binlog 中, 没有主键同步到从库需要执行 20 多万次全表扫描, 20W*20W=400 多亿, 吓人!;
6.MySQL 同步的时候, 会去利用主键来搜索需要修改的行 (或者是一些二级索引).
解决方案
1. 增加主键;
2. 跟研发沟通 delete from t_post_xxxxxx 改为 truncate table t_post_xxxxxx.
综上
由于没有统一数据库上线平台和代码审核机制, 造成一些不规范的代码以及数据库设计在生产运行. 建议上线使用规范化的数据库上线平台, 由平台自动发现数据库设计, 数据库上线脚本问题, 靠人肉上线难免会有疏漏, 自动化运维势在必行, 研发团队要使用代码规范检查工具避免不规范的代码上线.
来源: http://www.bubuko.com/infodetail-2823886.html