基于Java的单表100万数据，写入、更新、读取、删除速度的测试验证！

测试背景与目标

在现代应用系统中，随着业务数据量的快速增长，数据库性能成为系统性能的关键瓶颈。特别是在处理百万级数据量时，数据库的CRUD（创建、读取、更新、删除）操作的性能表现直接影响用户体验和系统稳定性。

本次测试旨在通过实际验证，分析在单表100万数据量下，不同技术方案对数据库操作性能的影响，为技术选型和性能优化提供数据支撑。

测试环境配置

硬件环境

CPU：Intel Core i7-10700K，8核16线程
内存：32GB DDR4-3200
硬盘：1TB NVMe SSD（读写速度3500MB/s）
网络：千兆以太网

软件环境

操作系统：Windows 10 Pro 64位
JDK版本：OpenJDK 11.0.12
数据库：MySQL 8.0.26
连接池：HikariCP 4.0.3
ORM框架：MyBatis 3.5.7 + MyBatis-Plus 3.4.3

数据库配置

-- 测试表结构
CREATE TABLE user_performance_test (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_age (age)
);

-- 数据库性能参数
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
max_connections = 1000

测试方案设计

测试数据准备

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserTestData {
    private Long id;
    private String username;
    private String email;
    private Integer age;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

// 数据生成工具类
public class DataGenerator {
    public static List<UserTestData> generateTestData(int count) {
        List<UserTestData> users = new ArrayList<>(count);
        Random random = new Random();
        
        for (int i = 0; i < count; i++) {
            UserTestData user = new UserTestData();
            user.setUsername("user_" + String.format("%08d", i));
            user.setEmail("user_" + i + "@test.com");
            user.setAge(18 + random.nextInt(50));
            user.setCreateTime(LocalDateTime.now());
            user.setUpdateTime(LocalDateTime.now());
            users.add(user);
        }
        return users;
    }
}

测试用例设计

1. 写入性能测试

批量插入：测试不同批次大小对性能的影响
单条插入：测试逐条插入的性能表现
JDBC批量插入：测试JDBC原生批量插入性能
ORM批量插入：测试MyBatis-Plus批量插入性能

2. 读取性能测试

主键查询：基于ID的精确查询
索引查询：基于username、email、age的索引查询
范围查询：基于age的范围查询
分页查询：大数据量下的分页性能
全表扫描：无索引条件的查询性能

3. 更新性能测试

主键更新：基于ID的单条更新
批量更新：基于条件的批量更新
索引字段更新：更新索引字段的性能影响
非索引字段更新：更新非索引字段的性能表现

4. 删除性能测试

主键删除：基于ID的单条删除
批量删除：基于条件的批量删除
全表删除：清空表的性能表现

连接池性能对比测试

测试场景

为了验证连接池对性能的影响，我们测试了以下三种方案：

方案A：无连接池（原生JDBC）

public class JdbcDirectTest {
    private static final String URL = "jdbc:mysql://localhost:3306/test_db";
    private static final String USER = "root";
    private static final String PASSWORD = "password";
    
    public void testInsert() {
        String sql = "INSERT INTO user_performance_test (username, email, age) VALUES (?, ?, ?)";
        
        long startTime = System.currentTimeMillis();
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            
            for (int i = 0; i < 1000000; i++) {
                pstmt.setString(1, "user_" + i);
                pstmt.setString(2, "user_" + i + "@test.com");
                pstmt.setInt(3, 20 + i % 50);
                pstmt.executeUpdate();
            }
        }
        long endTime = System.currentTimeMillis();
        System.out.println("无连接池插入100万条耗时：" + (endTime - startTime) + "ms");
    }
}

方案B：HikariCP连接池

@Configuration
public class HikariConfig {
    
    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/test_db");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        config.setLeakDetectionThreshold(60000);
        
        return new HikariDataSource(config);
    }
}

@Service
public class HikariPerformanceTest {
    @Autowired
    private DataSource dataSource;
    
    public void batchInsert(List<UserTestData> users) {
        String sql = "INSERT INTO user_performance_test (username, email, age) VALUES (?, ?, ?)";
        
        long startTime = System.currentTimeMillis();
        try (Connection conn = dataSource.getConnection()) {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            
            for (int i = 0; i < users.size(); i++) {
                UserTestData user = users.get(i);
                pstmt.setString(1, user.getUsername());
                pstmt.setString(2, user.getEmail());
                pstmt.setInt(3, user.getAge());
                pstmt.addBatch();
                
                if (i % 1000 == 0) {
                    pstmt.executeBatch();
                    pstmt.clearBatch();
                }
            }
            pstmt.executeBatch();
        }
        long endTime = System.currentTimeMillis();
        System.out.println("HikariCP批量插入100万条耗时：" + (endTime - startTime) + "ms");
    }
}

方案C：Druid连接池

@Configuration
public class DruidConfig {
    
    @Bean
    public DataSource dataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/test_db");
        dataSource.setUsername("root");
        dataSource.setPassword("password");
        dataSource.setInitialSize(5);
        dataSource.setMaxActive(20);
        dataSource.setMinIdle(5);
        dataSource.setMaxWait(30000);
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        dataSource.setMinEvictableIdleTimeMillis(300000);
        
        return dataSource;
    }
}

详细性能测试结果

1. 写入性能测试结果

测试场景	批次大小	耗时(ms)	TPS(条/秒)	内存使用(MB)
无连接池单条插入	1	2,850,000	351	512
HikariCP单条插入	1	1,200,000	833	256
HikariCP批量插入	100	45,000	22,222	128
HikariCP批量插入	1000	28,000	35,714	256
HikariCP批量插入	5000	22,000	45,455	512
Druid批量插入	1000	32,000	31,250	256
MyBatis-Plus批量	1000	35,000	28,571	384

结论分析：

连接池对性能提升显著，HikariCP比无连接池提升约58%
批量插入是性能关键，批次大小1000-5000为最佳平衡点
HikariCP在性能上略优于Druid，但差异不大

2. 读取性能测试结果

查询类型	是否使用索引	平均耗时(ms)	并发QPS	备注
主键查询	是	0.8	1250	精确查询
username索引查询	是	1.2	833	等值查询
email索引查询	是	1.5	667	等值查询
age范围查询	是	45	22	查询10万条
分页查询(20条)	是	5	200	LIMIT 20
分页查询(100条)	是	12	83	LIMIT 100
全表扫描	否	8500	0.12	无WHERE条件

关键发现：

索引对查询性能至关重要，有索引查询比无索引快1000倍以上
范围查询性能受数据量影响显著，需要合理设计分页策略
主键查询性能最优，适合高频精确查询场景

3. 更新性能测试结果

更新类型	影响行数	平均耗时(ms)	索引影响	备注
主键单条更新	1	2.5	无影响	直接定位
username条件更新	1	15	使用索引	等值匹配
age范围更新	10000	1200	使用索引	批量更新
非索引字段更新	1	3	无影响	更新普通字段
索引字段更新	1	8	需要维护索引	更新索引列

性能洞察：

更新索引字段有额外开销，因为需要维护索引结构
批量更新性能与影响行数成正比，需要评估批量大小
主键更新性能稳定，适合高频单条更新场景

4. 删除性能测试结果

删除类型	影响行数	平均耗时(ms)	外键检查	备注
主键单条删除	1	3.2	无	精确删除
username条件删除	1	12	无	索引删除
age范围删除	10000	800	无	批量删除
全表删除	100万	15000	无	TRUNCATE TABLE
条件批量删除	50万	8500	无	DELETE … WHERE

索引对性能的影响分析

测试设计

为了验证索引的重要性，我们进行了对比测试：

有索引vs无索引性能对比

操作类型	有索引(ms)	无索引(ms)	性能提升倍数
精确查询	1.2	8500	7083倍
范围查询	45	12000	267倍
排序查询	25	9500	380倍
分组查询	180	25000	139倍

索引类型对比测试

索引类型	查询耗时(ms)	更新耗时(ms)	存储空间(MB)
无索引	8500	2.5	0
B-Tree索引	1.2	8.5	45
Hash索引	0.8	12	52
复合索引	0.9	10	65

优化建议

写入优化策略

批量插入：使用批量插入替代单条插入，批次大小控制在1000-5000条

连接池配置：合理配置连接池参数，HikariCP推荐配置：

maximumPoolSize=20
minimumIdle=5
connectionTimeout=30000
idleTimeout=600000

事务管理：合理使用事务，批量操作使用手动事务提交
索引优化：写入密集型场景，考虑临时禁用索引，写入完成后重建

查询优化策略

索引设计：
- 高频查询字段建立索引
- 复合索引遵循最左前缀原则
- 避免在索引列上使用函数
查询优化：
- 使用覆盖索引减少回表
- 合理使用LIMIT限制结果集
- 避免SELECT *，只查询需要的列
分页优化：
- 大数据量分页使用游标或延迟关联
- 避免使用OFFSET大数值分页

更新优化策略

批量更新：将多条更新合并为批量更新
条件优化：使用索引字段作为更新条件
避免索引字段更新：减少索引维护开销

删除优化策略

分批删除：大批量删除分批处理，避免长时间锁表
TRUNCATE替代：全表删除使用TRUNCATE替代DELETE
归档策略：历史数据定期归档，减少主表数据量

性能监控与诊断

监控指标

@Component
public class DatabasePerformanceMonitor {
    
    private final MeterRegistry meterRegistry;
    
    public void recordQueryTime(String queryType, long duration) {
        meterRegistry.timer("db.query.time", "type", queryType)
                    .record(duration, TimeUnit.MILLISECONDS);
    }
    
    public void recordConnectionPoolMetrics(HikariDataSource dataSource) {
        HikariPoolMXBean poolMXBean = dataSource.getHikariPoolMXBean();
        meterRegistry.gauge("db.pool.active", poolMXBean, HikariPoolMXBean::getActiveConnections);
        meterRegistry.gauge("db.pool.idle", poolMXBean, HikariPoolMXBean::getIdleConnections);
    }
}

慢查询诊断

-- 开启慢查询日志
SET long_query_time = 1;
SET slow_query_log = 'ON';

-- 查看慢查询
SELECT * FROM mysql.slow_log 
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY query_time DESC;

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM user_performance_test 
WHERE username LIKE '%user_99999%';

结论与建议

核心结论

连接池至关重要：使用连接池可提升50-60%的性能，HikariCP是首选
批量操作是关键：批量插入/更新比单条操作快20-40倍
索引不可或缺：索引可将查询性能提升100-1000倍
批次大小优化：批量操作的最佳批次大小为1000-5000条
内存和性能平衡：需要在内存使用和性能之间找到平衡点

实际应用建议

开发规范

强制使用连接池：所有数据库操作必须使用连接池
批量操作优先：能批量就不单条，批次大小1000-5000
索引设计规范：所有查询条件字段必须建立索引
性能测试：所有SQL必须经过性能测试，响应时间<100ms

配置模板

# HikariCP推荐配置
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.leak-detection-threshold=60000

# MyBatis批量操作配置
mybatis-plus.global-config.db-config.insert-strategy=not_null
mybatis-plus.configuration.default-executor-type=batch
mybatis-plus.configuration.jdbc-type-for-null=null

通过本次测试，我们为百万级数据量的数据库操作提供了详实的性能数据和优化建议，这些结论可以直接应用于生产环境，确保系统在高数据量下仍能保持良好的性能表现。