12.3 数据库性能优化
面试重要程度：⭐⭐⭐⭐⭐
常见提问方式： "如何优化慢SQL？" "索引设计原则？" "数据库架构如何演进？"
预计阅读时间：45分钟
🔍 SQL性能优化技巧
慢SQL分析与优化
/**
* SQL性能优化工具类
*/
public class SQLOptimizer {
/**
* 常见慢SQL类型
*/
public enum SlowQueryType {
FULL_TABLE_SCAN("全表扫描", "缺少索引或索引失效"),
INEFFICIENT_JOIN("低效连接", "连接条件不当或缺少索引"),
SUBQUERY_OPTIMIZATION("子查询优化", "EXISTS替代IN，JOIN替代子查询"),
FUNCTION_IN_WHERE("WHERE中使用函数", "导致索引失效"),
LIKE_PREFIX_WILDCARD("LIKE前缀通配符", "%开头的LIKE查询"),
ORDER_BY_WITHOUT_INDEX("ORDER BY无索引", "排序字段缺少索引");
private final String name;
private final String description;
SlowQueryType(String name, String description) {
this.name = name;
this.description = description;
}
}
/**
* SQL优化示例
*/
public static class OptimizationExamples {
public static String optimizeFullTableScan() {
return """
-- 原SQL（全表扫描）
SELECT * FROM orders WHERE status = 'PENDING';
-- 优化后（添加索引）
CREATE INDEX idx_orders_status ON orders(status);
SELECT * FROM orders WHERE status = 'PENDING';
""";
}
public static String optimizeJoin() {
return """
-- 原SQL（低效连接）
SELECT o.*, u.name
FROM orders o, users u
WHERE o.user_id = u.id AND o.status = 'PENDING';
-- 优化后（显式JOIN + 索引）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
SELECT o.*, u.name
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'PENDING';
""";
}
public static String optimizeSubquery() {
return """
-- 原SQL（低效子查询）
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'PENDING');
-- 优化后（EXISTS替代IN）
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.status = 'PENDING'
);
""";
}
}
}
📊 索引设计最佳实践
索引策略与优化
/**
* 索引设计最佳实践
*/
public class IndexDesignBestPractices {
/**
* 索引类型枚举
*/
public enum IndexType {
PRIMARY("主键索引", "唯一且不为空，聚簇索引"),
UNIQUE("唯一索引", "保证数据唯一性"),
NORMAL("普通索引", "最常用的索引类型"),
COMPOSITE("复合索引", "多列组合索引"),
COVERING("覆盖索引", "包含查询所需所有列");
private final String name;
private final String description;
IndexType(String name, String description) {
this.name = name;
this.description = description;
}
}
/**
* 复合索引设计示例
*/
public static void demonstrateCompositeIndex() {
String example = """
-- 业务场景：用户订单查询
-- 常见查询模式：
-- 1. WHERE user_id = ? AND status = ?
-- 2. WHERE user_id = ? AND status = ? ORDER BY created_at DESC
-- 3. WHERE user_id = ? ORDER BY created_at DESC
-- 最优复合索引设计
CREATE INDEX idx_orders_user_status_time
ON orders(user_id, status, created_at DESC);
-- 索引列顺序说明：
-- 1. user_id：等值查询，选择性高，放第一位
-- 2. status：等值查询，选择性中等，放第二位
-- 3. created_at：范围查询和排序，放最后
-- 该索引可以支持的查询：
-- ✅ WHERE user_id = ?
-- ✅ WHERE user_id = ? AND status = ?
-- ✅ WHERE user_id = ? AND status = ? ORDER BY created_at
-- ✅ WHERE user_id = ? ORDER BY created_at
-- ❌ WHERE status = ? (不符合最左前缀)
""";
System.out.println(example);
}
/**
* 覆盖索引设计
*/
public static void demonstrateCoveringIndex() {
String example = """
-- 业务场景：用户列表查询
SELECT id, name, email, status
FROM users
WHERE status = 'ACTIVE'
ORDER BY created_at DESC;
-- 覆盖索引设计（包含所有查询列）
CREATE INDEX idx_users_status_time_covering
ON users(status, created_at DESC, id, name, email);
-- 优势：
-- 1. 避免回表查询，直接从索引获取数据
-- 2. 减少I/O操作，提升查询性能
-- 3. 特别适合读多写少的场景
""";
System.out.println(example);
}
}
🔧 数据库连接池优化
HikariCP连接池配置
/**
* 数据库连接池优化配置
*/
@Configuration
public class DatabaseOptimizationConfig {
@Bean
@Primary
public DataSource primaryDataSource() {
HikariConfig config = new HikariConfig();
// 基础连接配置
config.setJdbcUrl("jdbc:mysql://localhost:3306/your_db");
config.setUsername("your_username");
config.setPassword("your_password");
config.setDriverClassName("com.mysql.cj.jdbc.Driver");
// 连接池大小配置
config.setMinimumIdle(10); // 最小空闲连接数
config.setMaximumPoolSize(50); // 最大连接池大小
config.setConnectionTimeout(30000); // 连接超时时间(30秒)
config.setIdleTimeout(600000); // 空闲超时时间(10分钟)
config.setMaxLifetime(1800000); // 连接最大生命周期(30分钟)
config.setLeakDetectionThreshold(60000); // 连接泄漏检测阈值(1分钟)
// 性能优化配置
config.setCachePrepStmts(true); // 开启预编译语句缓存
config.setPrepStmtCacheSize(250); // 预编译语句缓存大小
config.setPrepStmtCacheSqlLimit(2048); // 预编译语句最大长度
config.setUseServerPrepStmts(true); // 使用服务器端预编译
config.setRewriteBatchedStatements(true); // 重写批量语句
// 连接验证配置
config.setConnectionTestQuery("SELECT 1");
config.setValidationTimeout(5000); // 验证超时时间(5秒)
config.setPoolName("HikariCP-Primary");
return new HikariDataSource(config);
}
}
📈 分库分表策略
分片算法实现
/**
* 分库分表策略实现
*/
public class DatabaseShardingStrategy {
/**
* 哈希分片实现
*/
public static class HashShardingAlgorithm {
private final int shardCount;
public HashShardingAlgorithm(int shardCount) {
this.shardCount = shardCount;
}
/**
* 根据用户ID进行分片
*/
public String getShardSuffix(Long userId) {
int shardIndex = Math.abs(userId.hashCode()) % shardCount;
return String.format("_%02d", shardIndex);
}
/**
* 获取分片表名
*/
public String getShardTableName(String baseTableName, Long shardKey) {
return baseTableName + getShardSuffix(shardKey);
}
}
/**
* 范围分片实现
*/
public static class RangeShardingAlgorithm {
private final Map<String, Long> rangeMap;
public RangeShardingAlgorithm() {
this.rangeMap = new HashMap<>();
rangeMap.put("shard_01", 1000000L); // 0-100万
rangeMap.put("shard_02", 2000000L); // 100万-200万
rangeMap.put("shard_03", 3000000L); // 200万-300万
rangeMap.put("shard_04", Long.MAX_VALUE); // 300万以上
}
public String getShardName(Long value) {
for (Map.Entry<String, Long> entry : rangeMap.entrySet()) {
if (value <= entry.getValue()) {
return entry.getKey();
}
}
return "shard_04";
}
}
}
🔄 读写分离实现
数据源路由配置
/**
* 读写分离实现
*/
@Configuration
public class ReadWriteSplitConfig {
/**
* 数据源路由器
*/
public static class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DatabaseContextHolder.getDataSourceType();
}
}
/**
* 数据源上下文持有者
*/
public static class DatabaseContextHolder {
private static final ThreadLocal<DataSourceType> CONTEXT_HOLDER = new ThreadLocal<>();
public static void setDataSourceType(DataSourceType dataSourceType) {
CONTEXT_HOLDER.set(dataSourceType);
}
public static DataSourceType getDataSourceType() {
return CONTEXT_HOLDER.get();
}
public static void clearDataSourceType() {
CONTEXT_HOLDER.remove();
}
}
public enum DataSourceType {
MASTER, SLAVE
}
/**
* 读写分离切面
*/
@Aspect
@Component
@Order
public static class ReadWriteSplitAspect {
@Before("@annotation(org.springframework.transaction.annotation.Transactional)")
public void setWriteDataSourceType(JoinPoint joinPoint) {
DatabaseContextHolder.setDataSourceType(DataSourceType.MASTER);
}
@Before("execution(* com.example.repository.*Repository.find*(..)) || " +
"execution(* com.example.repository.*Repository.get*(..))")
public void setReadDataSourceType(JoinPoint joinPoint) {
if (!TransactionSynchronizationManager.isActualTransactionActive()) {
DatabaseContextHolder.setDataSourceType(DataSourceType.SLAVE);
}
}
@After("execution(* com.example.repository.*Repository.*(..))")
public void clearDataSourceType() {
DatabaseContextHolder.clearDataSourceType();
}
}
}
💡 面试常见问题解答
Q1: 如何定位和优化慢SQL？
标准回答：
慢SQL定位步骤： 1. 开启慢查询日志 - MySQL: slow_query_log=ON, long_query_time=1 - 记录执行时间超过阈值的SQL 2. 分析执行计划 - 使用EXPLAIN分析SQL执行路径 - 关注type、key、rows、Extra字段 - 识别全表扫描、临时表、文件排序 3. 优化策略 - 添加合适的索引 - 重写SQL语句 - 分解复杂查询 - 使用覆盖索引 4. 验证效果 - 对比优化前后的执行时间 - 监控系统资源使用情况
Q2: 索引设计的最佳实践？
标准回答：
索引设计原则： 1. 最左前缀原则 - 复合索引按最左列开始匹配 - (a,b,c)索引支持a、ab、abc查询 2. 选择性原则 - 高选择性列优先建索引 - 选择性 = 不重复值数量 / 总行数 3. 覆盖索引 - 索引包含查询所需的所有列 - 避免回表查询，提升性能 4. 避免冗余 - 不创建重复或包含的索引 - 定期清理未使用的索引 5. 长度控制 - 字符串索引使用前缀索引 - 平衡索引长度和选择性
Q3: 分库分表的设计考虑？
标准回答：
分库分表设计要点： 1. 分片策略选择 - 哈希分片：数据分布均匀，但扩容困难 - 范围分片：便于扩容，但可能数据倾斜 - 目录分片：灵活性高，但需要维护路由表 2. 分片键选择 - 选择查询频率高的字段 - 避免数据倾斜 - 考虑业务关联性 3. 跨分片问题 - 分布式事务处理 - 跨分片查询优化 - 全局唯一ID生成 4. 扩容策略 - 预留扩容空间 - 数据迁移方案 - 业务影响最小化
核心要点总结：
- ✅ 掌握SQL性能优化和慢查询分析方法
- ✅ 理解索引设计原则和最佳实践
- ✅ 熟悉数据库连接池优化配置
- ✅ 具备分库分表和读写分离架构设计能力
Java面试圣经 文章被收录于专栏
Java面试圣经
阿里巴巴公司福利 666人发布