数据库优化：索引与读写分离
数据库优化：索引与读写分离
索引优化策略
合理的索引设计是提升数据库查询性能的关键，需要根据查询模式和数据特征进行优化。
-- 复合索引设计
-- 遵循最左前缀原则
CREATE INDEX idx_user_orders ON orders(user_id, order_date, status);
-- 覆盖索引（避免回表）
CREATE INDEX idx_covering ON orders(user_id, order_date, total_amount, status);
-- 部分索引（只索引需要的数据）
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
-- 表达式索引
CREATE INDEX idx_email_lower ON users(LOWER(email));
// 索引管理服务
@Component
public class IndexManager {
private final JdbcTemplate jdbcTemplate;
private final MetricsExporter metrics;
// 分析索引使用情况
public List<IndexUsage> analyzeIndexUsage(String schema) {
String sql = """
SELECT
schemaname,
tablename,
indexname,
idx_scan as scans,
idx_tup_read as tuples_read,
idx_tup_fetch as tuples_fetched,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE schemaname = ?
ORDER BY idx_scan DESC
""";
return jdbcTemplate.query(sql, new Object[]{schema},
(rs, rowNum) -> IndexUsage.builder()
.schema(rs.getString("schemaname"))
.table(rs.getString("tablename"))
.index(rs.getString("indexname"))
.scans(rs.getLong("scans"))
.tuplesRead(rs.getLong("tuples_read"))
.tuplesFetched(rs.getLong("tuples_fetched"))
.size(rs.getString("index_size"))
.build());
}
// 识别未使用的索引
public List<String> findUnusedIndexes(String schema) {
String sql = """
SELECT indexname
FROM pg_stat_user_indexes
WHERE schemaname = ?
AND idx_scan = 0
AND indexrelid NOT IN (
SELECT conindid FROM pg_constraint WHERE contype = 'p'
)
""";
return jdbcTemplate.queryForList(sql, String.class, schema);
}
// 识别缺失索引
public List<MissingIndex> findMissingIndexes(String schema) {
String sql = """
SELECT
relname as table_name,
seq_scan,
seq_tup_read,
idx_scan,
CASE WHEN seq_scan > 0
THEN seq_tup_read / seq_scan
ELSE 0 END as avg_rows_per_scan
FROM pg_stat_user_tables
WHERE schemaname = ?
AND seq_scan > 100
AND seq_tup_read > 10000
AND idx_scan = 0
ORDER BY seq_tup_read DESC
""";
return jdbcTemplate.query(sql, new Object[]{schema},
(rs, rowNum) -> MissingIndex.builder()
.table(rs.getString("table_name"))
.seqScans(rs.getLong("seq_scan"))
.tuplesScanned(rs.getLong("seq_tup_read"))
.avgRowsPerScan(rs.getLong("avg_rows_per_scan"))
.build());
}
}
查询优化
// 查询优化器
@Component
public class QueryOptimizer {
private final JdbcTemplate jdbcTemplate;
private final QueryPlanAnalyzer planAnalyzer;
// 分析查询计划
public QueryPlan analyzeQuery(String sql) {
// 获取执行计划
String explainSql = "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + sql;
String planJson = jdbcTemplate.queryForObject(explainSql, String.class);
return planAnalyzer.parse(planJson);
}
// 优化建议
public List<OptimizationSuggestion> getSuggestions(String sql) {
QueryPlan plan = analyzeQuery(sql);
List<OptimizationSuggestion> suggestions = new ArrayList<>();
// 检查全表扫描
if (plan.hasSequentialScan()) {
suggestions.add(OptimizationSuggestion.builder()
.type("INDEX")
.description("检测到全表扫描，建议添加索引")
.impact("HIGH")
.build());
}
// 检查排序操作
if (plan.hasSort()) {
suggestions.add(OptimizationSuggestion.builder()
.type("INDEX")
.description("检测到排序操作，建议添加排序索引")
.impact("MEDIUM")
.build());
}
// 检查嵌套循环
if (plan.hasNestedLoop()) {
suggestions.add(OptimizationSuggestion.builder()
.type("QUERY_REWRITE")
.description("检测到嵌套循环，建议重写查询")
.impact("MEDIUM")
.build());
}
return suggestions;
}
}
// 批量查询优化
@Component
public class BatchQueryOptimizer {
// IN子句优化
public <T> List<T> batchInClause(String column, List<String> values,
Function<ResultSet, T> mapper) {
int batchSize = 1000;
List<T> results = new ArrayList<>();
for (int i = 0; i < values.size(); i += batchSize) {
List<String> batch = values.subList(i,
Math.min(i + batchSize, values.size()));
String placeholders = batch.stream()
.map(v -> "?")
.collect(Collectors.joining(","));
String sql = String.format(
"SELECT * FROM table WHERE %s IN (%s)",
column, placeholders);
results.addAll(jdbcTemplate.query(sql,
batch.toArray(),
(rs, rowNum) -> mapper.apply(rs)));
}
return results;
}
// 分页查询优化
public <T> List<T> optimizedPaginate(String baseSql, int offset,
int limit, Object[] params) {
// 使用游标分页代替OFFSET
String cursorSql = baseSql + " ORDER BY id LIMIT ?";
Object[] newParams = Arrays.copyOf(params, params.length + 1);
newParams[params.length] = limit;
return jdbcTemplate.query(cursorSql, newParams,
(rs, rowNum) -> mapResult(rs));
}
}
读写分离架构
// 读写分离数据源配置
@Configuration
public class ReadWriteSplittingConfig {
@Bean
@Primary
public DataSource routingDataSource(
@Qualifier("writeDataSource") DataSource writeDataSource,
@Qualifier("readDataSource") DataSource readDataSource) {
ReadWriteRoutingDataSource routingDataSource =
new ReadWriteRoutingDataSource();
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put("write", writeDataSource);
targetDataSources.put("read", readDataSource);
routingDataSource.setTargetDataSources(targetDataSources);
routingDataSource.setDefaultTargetDataSource(writeDataSource);
return routingDataSource;
}
@Bean("writeDataSource")
@ConfigurationProperties("spring.datasource.write")
public DataSource writeDataSource() {
return DataSourceBuilder.create().build();
}
@Bean("readDataSource")
@ConfigurationProperties("spring.datasource.read")
public DataSource readDataSource() {
return DataSourceBuilder.create().build();
}
}
// 数据源路由
public class ReadWriteRoutingDataSource extends AbstractRoutingDataSource {
private static final ThreadLocal<String> CONTEXT = new ThreadLocal<>();
@Override
protected Object determineCurrentLookupKey() {
return CONTEXT.get();
}
public static void setRead() {
CONTEXT.set("read");
}
public static void setWrite() {
CONTEXT.set("write");
}
public static void clear() {
CONTEXT.remove();
}
}
// 读写分离拦截器
@Aspect
@Component
public class ReadWriteSplittingAspect {
@Around("@annotation(ReadOnly)")
public Object handleReadOnly(ProceedingJoinPoint joinPoint) throws Throwable {
try {
ReadWriteRoutingDataSource.setRead();
return joinPoint.proceed();
} finally {
ReadWriteRoutingDataSource.clear();
}
}
@Around("@annotation(WriteOnly)")
public Object handleWriteOnly(ProceedingJoinPoint joinPoint) throws Throwable {
try {
ReadWriteRoutingDataSource.setWrite();
return joinPoint.proceed();
} finally {
ReadWriteRoutingDataSource.clear();
}
}
}
// 使用示例
@Service
public class OrderService {
@ReadOnly
public List<Order> getOrders(Long userId) {
return orderRepository.findByUserId(userId);
}
@WriteOnly
public Order createOrder(OrderRequest request) {
return orderRepository.save(createOrderFromRequest(request));
}
}
数据库性能监控
# 数据库性能配置
database:
optimization:
# 连接池配置
connection_pool:
type: "hikari"
maximum_pool_size: 100
minimum_idle: 10
idle_timeout: "30m"
max_lifetime: "1h"
connection_timeout: "30s"
# 查询超时
query_timeout: "30s"
# 慢查询日志
slow_query_log:
enabled: true
threshold: "1s"
log_path: "/var/log/mysql/slow-query.log"
# 读写分离
read_write_splitting:
enabled: true
read_strategy: "round_robin"
health_check_interval: "10s"
# 监控
monitoring:
enabled: true
metrics:
- "connection_pool_active"
- "connection_pool_idle"
- "query_duration"
- "slow_queries"
- "deadlocks"
alerts:
- metric: "connection_pool_active"
threshold: 80
action: "warning"
- metric: "query_duration_avg"
threshold: 1000
action: "warning"
- metric: "deadlocks"
threshold: 1
action: "critical"
数据库优化通过索引优化、查询优化、读写分离和性能监控，显著提升数据库的查询性能和系统整体吞吐量。