MySQL 5.7与Wicket 8.6应用并行执行多查询优化求助

你现在遇到的核心问题，大概率是数据库连接池被长查询耗尽，再加上MySQL本身的配置限制，导致其他请求要么拿不到连接，要么MySQL的资源被长查询占满，整个应用直接卡壳。虽然是只读查询，但连接池太小的话，长查询会一直占着一个连接，其他请求只能排队等它释放；而且MySQL的会话、IO资源也会被长查询占用，进一步拖慢其他请求的执行。

你的连接池目前只开了5个最大连接，这对于有长查询的场景来说太小了，先调整这些关键参数：

• 增大最大连接数：根据你的应用并发量来，比如调到15-20（注意别超过MySQL默认max_connections（151）的70%，15是很安全的取值）：

  <property name="maximumPoolSize" value="15" />
  

• 调整连接存活时间：你现在的maxLifetime是90秒，长查询如果超过这个时间，连接会被强制关闭反而容易出问题。把它改成30分钟（1800000毫秒），空闲超时可以保持90秒或者改成5分钟：

  <property name="maxLifetime" value="1800000" />
  <property name="idleTimeout" value="300000" />
  

• 缩短连接获取超时：当前90秒的连接超时太长了，改成10秒，避免请求傻等连接：

  <property name="connectionTimeout" value="10000" />
  

• 开启连接泄漏检测：加个leakDetectionThreshold参数，设为30秒，帮你排查有没有连接没正确释放的情况：

  <property name="leakDetectionThreshold" value="30000" />
  

你的MySQL配置有几个可以针对性调整的点，尤其是针对只读长查询和并发请求：

• 关掉查询缓存：MySQL 5.7的查询缓存本身有锁问题，当有查询写缓存时，其他查询会被阻塞，而且复杂查询的缓存效率极低，直接关掉：

  query_cache_type = 0
  query_cache_size = 0
  

• 给InnoDB加内存缓存：你用的是InnoDB，最核心的参数就是innodb_buffer_pool_size，建议设为服务器内存的50%-70%（比如服务器有8G内存，就设4G），让更多数据放内存里，减少磁盘IO：

  innodb_buffer_pool_size = 4G
  

• 提升IO并发能力：增大InnoDB的读写IO线程数，比如设为8，让MySQL能同时处理更多IO请求：

  innodb_read_io_threads = 8
  innodb_write_io_threads = 8
  

• 更换临时表引擎：别用MyISAM当临时表引擎了，改成InnoDB，它的并发处理能力更好：

  internal_tmp_disk_storage_engine=InnoDB
  

• 增大MySQL最大连接数：如果你调大了连接池的最大连接数，同步把MySQL的max_connections改成200，避免MySQL连接耗尽：

  max_connections = 200
  

再好的配置也架不住低效的查询，这部分也得同步优化：

• 添加合适的索引：用EXPLAIN分析你的长查询，看看是不是存在全表扫描，把WHERE、JOIN用到的字段加上合适的索引，能大幅缩短查询时间。
• 分页查询：如果结果集很大，别一次性查出来，改成分页返回，减少单次查询的资源占用。
• 异步执行长查询：在Wicket里把长查询放到异步线程里跑，比如用AbstractAjaxTimerBehavior或者Spring的@Async注解，让用户请求不用一直等着，后台跑完了再通知用户，这样不会占着连接池的连接太久。
• 使用只读事务：执行长查询的时候开启只读事务，MySQL会对这类事务做优化，减少锁的开销：

  @Transactional(readOnly = true)
  public List<YourEntity> runLongQuery(Date startDate, Date endDate) {
      // 你的长查询逻辑
  }
  

• 监控连接池状态：HikariCP支持JMX监控，或者用Spring Boot Actuator（如果用了Spring Boot），查看连接数、空闲数、等待连接的请求数，确认连接池是不是真的够用。
• 监控MySQL状态：用SHOW PROCESSLIST查看长查询的执行状态，有没有锁等待；用SHOW ENGINE INNODB STATUS查看InnoDB的运行情况，排查潜在瓶颈。

内容的提问来源于stack exchange，提问作者Felix H