MySQL--这样一优化系统整体性能立马提升--其乐融融的IT技术小站

环境：Spring5.3.23

1. 案例代码

先看下示例代码：

static class PersonService {
  @Resource
  private JdbcTemplate jdbcTemplate;
  /**
   * 这里的代码，先执行了更新操作，然后执行查询操作。
   * 业务代码非常的简单
   */
  @Transactional
  public void operator() {
    // 1
    int res = this.jdbcTemplate.update("update p_user t set t.address='akf' where t.id = 9000000") ;
    System.out.printf("更新: %d 条数据%n", res) ;
    // 2
    List> users = this.jdbcTemplate.queryForObject("select * from p_user x where x.username='h4F7i4B4'", (rs, rowNum) -> {
      List> datas = new ArrayList<>() ;
      while (rs.next()) {
        Map obj = new HashMap<>() ;
        obj.put("id", rs.getObject(1)) ;
        obj.put("username", rs.getObject(2)) ;
        obj.put("email", rs.getObject(3)) ;
        obj.put("password", rs.getObject(4)) ;
        obj.put("address", rs.getObject(5)) ;
        obj.put("age", rs.getObject(6)) ;
        datas.add(obj) ;
      }
      return datas ;
    }) ;
    System.out.println(users) ;
  }
}

先思考上面代码在什么样的场景下可以做相应的优化？

2. 环境准备

本地环境我准备了p_user表数据量2000W。

图片

数据库索引情况：

图片

这里除了主键，没有任何其它的索引。

这里不建立任何二级索引是为了模拟查询慢的场景。

执行上面的查询SQL：

图片

执行时间5s；感觉还好。

3. 代码分析

在上面的代码中2个关键的数据库操作分别是更新与查询，这两个查询是没有任何关联的，相关独立；而在整个方法上是添加了@Transaction注解，整个方法的执行都是在一个事务中执行。那么这里的查询如果非常的慢是不是会对当前的修改记录或者是整个p_user表造成影响呢？

结合上面的数据表情况，当前表是没有任何索引的（除主键）。如果这里的查询比较慢，会发生什么情况呢？

首先你要知道MySQL中update语句会上什么锁？

回顾MySQL锁：

MySQL的UPDATE语句默认会带上一个写锁（Write Lock）。在MySQL中，写锁会阻塞其他事务对同一行的读取和写入操作，以确保数据的一致性和完整性。

当执行UPDATE语句时，MySQL会在相关的行上获取写锁。这样，其他事务无法修改或删除这些行，直到写锁被释放。这有助于防止在并发操作中发生数据冲突或不一致的情况。

还有点要注意mysql的锁机制是基于事务的，所以通常我们需要在一个事务中进行操作。随着事务的提交或回滚进行锁的释放

知道了update语句默认会带上写锁，那么这里的update锁的是id等于9000000的数据。前面提到了，只要事务提交或者回滚后锁才会被释放。

那如果这里我们接下来的查询比较慢那是不是我们这个锁的释放时间就会变长，其它事务将会被阻塞。一旦发生了阻塞我们系统的整体性能可能会受到影响。这里的update语句只会对id为9000000的数据上锁，如果咱们的更新语句是范围的或者条件是没有索引的那很可能就成了表锁，那这时候系统的性能会变的非常糟糕。也就是我们对这条id为9000000的数据要锁至少5s时间。

该如何优化上面的代码呢？

4. 代码优化

通过上面的分析，由于先执行了update语句，然后执行查询语句，如果查询比较慢那么我们的update语句形成的写锁（行锁，间隙锁或者表锁）时间会变长，对系统的整体性能会造成影响。所以这里我们只需要将查询和修改操作顺序进行下调整即可。

@Transactional
public void operator() {
  // 1
  List> users = this.jdbcTemplate.queryForObject("select * from p_user x where x.username='h4F7i4B4'", (rs, rowNum) -> {
    List> datas = new ArrayList<>() ;
    while (rs.next()) {
      Map obj = new HashMap<>() ;
      obj.put("id", rs.getObject(1)) ;
      // ...
      datas.add(obj) ;
    }
    return datas ;
  }) ;
  System.out.println(users) ;
  // 2
  int res = this.jdbcTemplate.update("update p_user t set t.address='akf' where t.id = 9000000") ;
  System.out.printf("更新: %d 条数据%n", res) ;
}

通过上面的优化，虽然该接口自身的性能并没有提升，但是在该接口中update语句形成的锁时间将大大的减少（在这里查询语句是没有锁的），如果同一时刻存在其它事务修改当前的数据不至于被阻塞太长时间，那其它接口的性能整体不就提高了么。

在上面的代码中首先是2个操作互不相干，其实完全可以把不需要事务的操作放到其它方法中(注意事务失效问题)。

static class PersonService {
  @Resource
  private JdbcTemplate jdbcTemplate;
  @Resource
  private PersonService ps ;


  public void query() {
    ps.update() ;
    List> users = this.jdbcTemplate.queryForObject("select * from p_user x where x.username='h4F7i4B4'", (rs, rowNum) -> {
      List> datas = new ArrayList<>() ;
      while (rs.next()) {
        Map obj = new HashMap<>() ;
        obj.put("id", rs.getObject(1)) ;
        // ...
        datas.add(obj) ;
      }
      return datas ;
    }) ;
    System.out.println(users) ;
  }
  
  @Transactional
  public void update() {
    int res = this.jdbcTemplate.update("update p_user t set t.address='akf' where t.id = 9000000") ;
    System.out.printf("更新: %d 条数据%n", res) ;
  }
}

上面代码己注入自己，解决在非事务方法中调用事务方法二导致事务失效。当然也可以通过AopContext来解决(不推荐)，也可以把事务方法放到其它类中都可以解决。

完毕！！！

1. 案例代码

2. 环境准备

3. 代码分析

4. 代码优化

相关推荐