INSERT INTO ... ON DUPLICATE KEY... 引起数据库CPU飙高问题排查_insert into on duplicate key性能问题

一、问题复现

1、创建表

CREATE TABLE `tb_user_basic_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_domain_id` bigint(20) NOT NULL COMMENT '账号domain id',
  `isolate_domain_id` bigint(20) NOT NULL COMMENT '数据隔离 domain id',
  `wid` bigint(20) NOT NULL COMMENT '用户id',
  `nickname` varchar(128) DEFAULT NULL COMMENT '用户昵称',
  `avatar_url` varchar(400) DEFAULT NULL COMMENT '用户图像',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(4) NOT NULL COMMENT '是否删除，0=否，1=是',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_wid_userdoamin_isolatedomain` (`wid`,`user_domain_id`,`isolate_domain_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT

2、并发调用on duplicate key的代码

public class DuplicateKeyMain {
 
    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.load(DuplicateKeyMain.class.getClassLoader().getResourceAsStream("druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
 
        ExecutorService executorService = Executors.newFixedThreadPool(20);
        int sum = IntStream.range(0, 1000000).peek(i -> executorService.execute(() -> doInsertOnDuplicateKey(jdbcTemplate, i))).sum();
 
        System.out.println(sum);
    }
 
 
    private static void doInsertOnDuplicateKey(JdbcTemplate jdbcTemplate, int index) {
        long start = new Date().getTime();
        jdbcTemplate.execute("INSERT INTO `tb_user_basic_info`( " +
                "`user_domain_id`, `isolate_domain_id`, `wid`, `nickname`, `avatar_url`, `is_deleted`) " +
                "VALUES (1, 1, 1, '2', '3', 0) ON DUPLICATE KEY UPDATE `nickname` = 'num:" + index + "'");
        long end = new Date().getTime();
        System.out.println("cost: " + (end - start));
    }
 
 
}

3、问题复现

运行一段时间后，使用top命令查看进程的cpu使用率，可以发现mysql进程的CPU使用率已经到了67.7% 

二、原因分析

使用show engine innodb status查看存储引擎状态，可以发现有大量事务在等待一个行锁，即所需要修改的那条记录上的锁。

初步可以推断，是由于INSERT INTO ... ON DUPLICATE KEY...会获取行锁，行锁大量冲突导致数据库CPU利用率飙高。

通过”select * from innodb_trx“查询语句也印证了这一点。下图一共有20个连接，19个连接在等待那一个行锁。

那么另一个问题又来了，什么锁冲突会引起数据库CPU飙高？

根据mysql的官方文档MySQL :: MySQL 5.7 Reference Manual :: 14.16.2.2 InnoDB Lock and Lock-Wait Information中写道：

InnoDB会给行锁维护一个锁队列。当后面的事务B希望获取行锁(独占锁)，而行锁还被前面的事务A所占有时，InnoDB会把事务B的锁请求加入到锁队列中。只有当事务A提交或者回滚后，事务B才能获得行锁。

因此，在很多请求获取同一个锁的情况下，这些额外的操作导致了额外的CPU消耗，导致了CPU利用率高。

三、补充

根据以上结论，我们可以推测，如果把 INSERT INTO ... ON DUPLICATE KEY...改为UPDATE...，应该也是一样的效果，一样会因为锁冲突而导致CPU利用率上升。我们将语句改为UPDATE语句后，果然服务器的CPU上升了。

参考文章：

Mysql 并发引起的死锁问题（INSERT ... ON DUPLICATE KEY UPDATE 死锁）_xiaolyuh123的博客-CSDN博客_mysql防死锁大量数据更新

MySQL :: MySQL 5.7 Reference Manual :: 14.8.9 Configuring Spin Lock Polling