MySQL中insert阻塞问题的分析 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: 0SET 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  AUTO_INCREMENT COMMENT '主键', `device_model` varchar(200) NOT  DEFAULT '' , `device_match` varchar(200) NOT  DEFAULT '' , `score` int(11) unsigned NOT  DEFAULT '0' , `chipset_model` varchar(200) NOT  DEFAULT '' , `manufacturer` varchar(100) NOT  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 -l5295

整个业务和数据库的数据链路如下：

业务服务器会不断发起短连接请求，整个过程中是无状态的，发起的数据写入很可能是冗余的，为了在数据库中达到唯一性，设置了这个唯一性索引，而业务的持续不断的写入，因为唯一性索引会额外有检测数据库冲突的逻辑，所以相关的SQL都会阻塞，积累起来就会发现是1/N的写入命中率。

从这一点也可以看出，很多业务对于分布式应用的理解还是有限，应用服务器水平扩展就不考虑整个链路里面的数据一致性和唯一性了，导致数据库最后成了瓶颈，况且在这个层面的ACID代价其实就很高了。

而和业务的沟通来看，他们后续会做一些修正：

1）将短连接模式修改为长连接模式

2）在业务层进行数据操作时，先进行数据探测，如果已经存在则不做后续的处理，否则写入

3）对于应用分布式架构中对于数据库唯一性校验和数据一致性方面进行更进一步的测试，从设计理念上需要做一些转变。