MySQL中insert阻塞问题的分析
这是学习笔记的第 2262 篇文章
读完需要
9
分钟速读仅需7分钟
今天收到一个业务报警，提示某个数据库实例的连接数暴涨，然后瞬间又恢复了，这种情况持续反复了几次，和开发同学沟通时，他们也希望能够得到更多的信息，比如是哪个数据库的连接数异常暴涨，我也想知道啊，但是苦于没有合适的工具和方法能够实现更细粒度的监控/统计，于是我着手开始分析这个问题。
这是一套MySQL 5.7.16的环境，事务隔离级别为RR
等我连接到这套环境的时候，show processlist的输出已经恢复了正常，查看相关的数据库日志也没有任何额外的输出，查看慢日志发现了有一部分的慢日志，提示是在insert into的语句，看起来着实蹊跷，计。
# User@Host: testdb[testdb] @ [xxxx.xx3]
# Query_time: 3.461818 Lock_time: 0.000067 Rows_sent: 0 Rows_examined: 0
SET timestamp=1597826800;
INSERT INTO `device_confignew_clientup` (`device_type`, `device_model`, `chipset_model`, `manufacturer`, `score`, `match_type`, `physic_memory`, `created_on`, `updated_on`, `is_open`) VALUES ('4', 'JEF-AN00', 'Hisilicon Kirin985', 'HUAWEI', '3000', '0', '7503', '1597826797', '1597826797', '1');
一条insert语句怎么会执行3秒多，往前继续翻，有些甚至都达到了10多秒，
在没有更多日志支撑的前提下，根据负载情况，我在主库打开了general log查看整个实例的操作明细，可以看到如下的日志信息，我截取了一段比较有代表性的日志。
首先，根据行首的id可以看到线程id增长会快，目前已经是4000万左右了，根据线程的连接情况可以看到，整个业务操作是基于短连接的形式处理的。
同时整个操作中涉及的表也很明显，是device_confignew_clientup，和慢日志里面显示的表和信息是可以互相呼应的。
表device_confignew_clientup的结构如下：
CREATE TABLE `device_confignew_clientup` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`device_model` varchar(200) NOT NULL DEFAULT '' ,
`device_match` varchar(200) NOT NULL DEFAULT '' ,
`score` int(11) unsigned NOT NULL DEFAULT '0' ,
`chipset_model` varchar(200) NOT NULL DEFAULT '' ,
`manufacturer` varchar(100) NOT NULL DEFAULT '' ,
....
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_dm_ma` (`device_model`,`manufacturer`,`chipset_model`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=50160457 DEFAULT CHARSET=utf8 ;
结合这些信息，我们似乎可以找到问题的突破口，那就是里面的那个唯一性索引。按照这个约束，主键值id是从SQL里面自增完成的，唯一性索引基于3个字段，如果仔细观察上面的日志就户发现，基于同样的列值，竟然在日志里面两个不同的客户端发起了同样的SQL.
顺着这个思路，我继续进行排查，发现问题是越来越清晰了,我基于一个字段开始梳理，发现这个编码的数据相关的Insert有5000多条，也就意味着这个业务里面存在着大量冗余的数据写入。
grep -B4 JXX-AN00 general.log |wc -l
5295
整个业务和数据库的数据链路如下：
业务服务器会不断发起短连接请求，整个过程中是无状态的，发起的数据写入很可能是冗余的，为了在数据库中达到唯一性，设置了这个唯一性索引，而业务的持续不断的写入，因为唯一性索引会额外有检测数据库冲突的逻辑，所以相关的SQL都会阻塞，积累起来就会发现是1/N的写入命中率。
从这一点也可以看出，很多业务对于分布式应用的理解还是有限，应用服务器水平扩展就不考虑整个链路里面的数据一致性和唯一性了，导致数据库最后成了瓶颈，况且在这个层面的ACID代价其实就很高了。
而和业务的沟通来看，他们后续会做一些修正：
1）将短连接模式修改为长连接模式
2）在业务层进行数据操作时，先进行数据探测，如果已经存在则不做后续的处理，否则写入
3）对于应用分布式架构中对于数据库唯一性校验和数据一致性方面进行更进一步的测试，从设计理念上需要做一些转变。
QQ群号：763628645
QQ群二维码如下， 添加请注明：姓名+地区+职位，否则不予通过
订阅我的微信公众号“杨建荣的学习笔记”，第一时间免费收到文章更新。别忘了加星标，以免错过新推送提示。
近期热文
你可能也会对以下话题感兴趣。点击链接就可以查看。
职场建议：给新人和老鸟的几点建议
对于新技术栈落地和架构思维的建议
你到底关注了哪些公众号？我做了一通分析
如何优化MySQL千万级大表，我写了6000字的解读
说说我的新书《MySQL DBA工作笔记》
使用Python分析北京积分落户数据，分析完我陷入了深思
MySQL的主键命名挺任性，就这么定了
华裔教授发现二次方程极简解法,我默默的做了下验算
回答:我不小心把公司的数据库给删了，该不该离职？
迁移到MySQL的业务架构演进实战
数据库修改密码风险高，如何保证业务持续，这几种密码双活方案可以参考
MySQL业务双活的初步设计方案
一道经典的MySQL面试题，答案出现三次反转
业务双活的数据切换思路设计（下）
业务双活的数据切换思路设计（一）
MySQL中的主键和rowid,看似简单,其实有一些使用陷阱需要注意