MySQL索引优化实战 Java实现亿级数据快速查询解决方案

最新推荐文章于 2025-12-03 14:39:35 发布

原创最新推荐文章于 2025-12-03 14:39:35 发布 · 202 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #java #数据库

MySQL索引优化实战

选择合适的索引类型
对于亿级数据表，B-Tree索引适合范围查询和精确匹配，HASH索引仅适用于等值查询。联合索引需遵循最左前缀原则，避免冗余索引。

示例：创建联合索引

ALTER TABLE large_table ADD INDEX idx_combined (column1, column2, column3);

避免索引失效场景
不要在索引列上使用函数或运算，如WHERE YEAR(create_time)=2023会导致索引失效。使用覆盖索引减少回表操作，通过EXPLAIN检查执行计划。

Java实现高效查询方案

分页查询优化
传统LIMIT offset, size在亿级数据下性能差，改用延迟关联或游标分页。

// 游标分页示例（基于ID）
String sql = "SELECT * FROM large_table WHERE id > ? ORDER BY id LIMIT 1000";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setLong(1, lastId);

批量处理与连接池配置
使用batchUpdate减少网络开销，合理设置连接池参数（如HikariCP）：

HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(20);
config.setConnectionTimeout(30000);

读写分离与分库分表

读写分离
通过主从复制分散查询压力，Java应用可通过中间件（如ShardingSphere）或注解@Transactional(readOnly=true)路由到从库。

水平分表策略
按时间或哈希分片，例如按用户ID取模分表：

-- 分表路由逻辑
SELECT * FROM order_${user_id % 10} WHERE user_id = 12345;

缓存层集成

多级缓存架构
高频查询结果存入Redis，本地缓存（如Caffeine）减少远程调用。

// Redis缓存示例
String cacheKey = "user:" + userId;
String data = redisTemplate.opsForValue().get(cacheKey);
if (data == null) {
    data = dbQuery(userId);
    redisTemplate.opsForValue().set(cacheKey, data, 1, TimeUnit.HOURS);
}

缓存一致性处理
更新数据库后删除缓存，或采用消息队列异步更新。

监控与持续调优

慢查询日志分析
启用MySQL慢查询日志并定期分析：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

JVM参数优化
调整GC策略减少停顿，例如G1垃圾回收器：

-XX:+UseG1GC -Xms4g -Xmx4g