面试重要程度：⭐⭐⭐⭐⭐

常见提问方式： "如何优化慢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 DES