Spring Data JPA性能骤降排查：Spring Boot 2迁移后插入变慢

Hey Nick, let's break down this massive performance gap step by step. Looking at your configs and stats, there are several key areas to focus on that are likely causing the slowdown:

1. Fix MySQL Batch Statement Rewriting (Critical!) #

Your new stats show 19 JDBC batches but execution time is still through the roof—this is almost certainly because MySQL isn't actually batching your inserts under the hood. By default, MySQL ignores batch requests unless you enable statement rewriting.

Add this parameter to your spring.datasource.url:

spring.datasource.url=jdbc:mysql://<your-host>:<your-port>/<your-db>?rewriteBatchedStatements=true&useSSL=false&serverTimezone=UTC

This tells the MySQL driver to convert multiple single insert statements into a single batch insert like INSERT INTO category VALUES (...), (...), (...), which drastically reduces round-trips to the database.

2. Adjust Hikari Connection Pool Settings #

Your old C3P0 config had a max pool size of 100, but your new Hikari setup only allows 10 connections. With batch operations, insufficient connections can lead to bottlenecks waiting for available database connections.

Update your Hikari config:

spring.datasource.hikari.maximum-pool-size=30
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.minimum-idle=5

Start with 30 connections and adjust based on your database's capacity—this should eliminate connection wait times.

3. Optimize Hibernate Flush Behavior #

Your new stats show 3 flushes refreshing 2823 entities vs. the old setup's 1 flush for 941 entities. This means Hibernate is flushing the session multiple times during the saveAll() call, which adds massive overhead.

Fix this by:

• Ensuring your service layer method is wrapped in a single transaction with @Transactional (make sure it's the Spring org.springframework.transaction.annotation.Transactional annotation, not JPA's).
• Setting Hibernate's flush mode to only flush on commit:

  spring.jpa.properties.hibernate.flush.mode=COMMIT
  

This will force Hibernate to delay all flushing until the transaction commits, matching your old manual transaction behavior.

4. Verify Batch Configuration Completeness #

You have hibernate.jdbc.batch_size=50 and order_inserts=true, but add one more property to ensure batch processing works for all entity types:

spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true

This enables batch processing for entities that use versioning (even though your entity doesn't, it's a safe catch-all for edge cases).

5. Check Dialect Compatibility #

You're using MySQLDialect—if you're running MySQL 8.0+, switch to the newer dialect which includes better optimizations for modern MySQL versions:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

6. Validate SQL Output #

Double-check the actual SQL being generated (you have show_sql=true enabled). After adding rewriteBatchedStatements=true, you should see a single insert statement with multiple value sets instead of 942 individual insert statements. If you still see single inserts, your batch configuration isn't taking effect.

Start with the MySQL rewrite parameter and connection pool adjustment—these are the most common fixes for this exact scenario. Once you apply those, re-run your test and compare the stats again; you should see a huge drop in execution time.

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