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

常见提问方式： 索引失效场景、执行计划分析、慢查询优化

预计阅读时间：40分钟

🎯 索引设计原则与B+树结构

B+树索引原理深度解析

面试官： "能详细说说MySQL的B+树索引是如何工作的吗？"

必答要点：

B+树结构特点：

-- B+树的核心特征
/**
 * 1. 所有数据都存储在叶子节点
 * 2. 叶子节点之间通过指针连接，形成有序链表
 * 3. 非叶子节点只存储键值和指针，不存储数据
 * 4. 树的高度通常为3-4层，能存储千万级数据
 */

-- 示例：用户表索引结构
CREATE TABLE users (
    id INT PRIMARY KEY,           -- 主键索引（聚簇索引）
    username VARCHAR(50),         
    email VARCHAR(100),
    age INT,
    create_time TIMESTAMP,
    
    INDEX idx_username (username),     -- 普通索引
    INDEX idx_email (email),          -- 唯一索引
    INDEX idx_age_createtime (age, create_time)  -- 复合索引
);

B+树查找过程：

/**
 * B+树查找算法模拟
 */
public class BPlusTreeSearch {
    
    /**
     * 模拟B+树查找过程
     * 假设查找 id = 1001 的记录
     */
    public void searchProcess() {
        // 第1层：根节点（存储键值范围）
        // [1-1000] [1001-2000] [2001-3000] [3001-4000]
        // 1001 在第2个范围，走右边指针
        
        // 第2层：内部节点
        // [1001-1250] [1251-1500] [1501-1750] [1751-2000]
        // 1001 在第1个范围，走左边指针
        
        // 第3层：叶子节点（存储实际数据）
        // 找到 id=1001 的完整记录
        // 由于叶子节点有序且相互连接，范围查询效率很高
    }
}

索引设计原则

1. 最左前缀原则

-- 复合索引 (a, b, c)
CREATE INDEX idx_abc ON table_name (a, b, c);

-- ✅ 能使用索引的查询
SELECT * FROM table_name WHERE a = 1;                    -- 使用 a
SELECT * FROM table_name WHERE a = 1 AND b = 2;          -- 使用 a, b
SELECT * FROM table_name WHERE a = 1 AND b = 2 AND c = 3; -- 使用 a, b, c
SELECT * FROM table_name WHERE a = 1 AND c = 3;          -- 只使用 a

-- ❌ 不能使用索引的查询
SELECT * FROM table_name WHERE b = 2;                    -- 跳过了 a
SELECT * FROM table_name WHERE c = 3;                    -- 跳过了 a, b
SELECT * FROM table_name WHERE b = 2 AND c = 3;          -- 跳过了 a

2. 索引选择性原则

-- 计算索引选择性
SELECT 
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

-- 选择性越高，索引效果越好
-- 选择性 = 1：完全唯一（如主键）
-- 选择性 > 0.1：适合建索引
-- 选择性 < 0.01：不适合建索引

-- 示例：分析用户表各字段选择性
SELECT 
    'id' AS column_name,
    COUNT(DISTINCT id) / COUNT(*) AS selectivity
FROM users
UNION ALL
SELECT 
    'username',
    COUNT(DISTINCT username) / COUNT(*)
FROM users
UNION ALL
SELECT 
    'age',
    COUNT(DISTINCT age) / COUNT(*)
FROM users;

3. 索引长度优化

-- 前缀索引：只索引字符串的前几个字符
CREATE INDEX idx_email_prefix ON users (email(10));

-- 分析最优前缀长度
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS prefix_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS prefix_15
FROM users;

-- 选择选择性接近完整字段但长度较短的前缀

🔍 执行计划分析

EXPLAIN详解

面试官： "如何通过EXPLAIN分析SQL的执行效率？"

核心字段解析：

-- 示例查询
EXPLAIN SELECT u.username, p.title 
FROM users u 
JOIN posts p ON u.id = p.user_id 
WHERE u.age > 25 AND p.status = 'published'
ORDER BY p.create_time DESC 
LIMIT 10;

-- EXPLAIN输出字段详解
/**
 * id: 查询序列号，数字越大越先执行
 * select_type: 查询类型
 *   - SIMPLE: 简单查询
 *   - PRIMARY: 主查询
 *   - SUBQUERY: 子查询
 *   - DERIVED: 派生表查询
 * 
 * table: 当前操作的表
 * 
 * type: 访问类型（性能从好到差）
 *   - system: 系统表，只有一行
 *   - const: 常量查询，主键或唯一索引
 *   - eq_ref: 唯一索引查找
 *   - ref: 非唯一索引查找
 *   - range: 范围查询
 *   - index: 索引全扫描
 *   - ALL: 全表扫描（最差）
 * 
 * possible_keys: 可能使用的索引
 * key: 实际使用的索引
 * key_len: 索引长度
 * ref: 索引引用
 * rows: 预估扫描行数
 * Extra: 额外信息
 *   - Using index: 覆盖索引
 *   - Using where: WHERE过滤
 *   - Using filesort: 文件排序（需优化）
 *   - Using temporary: 临时表（需优化）
 */

执行计划优化案例：

-- 案例1：索引失效问题
-- ❌ 原始查询（全表扫描）
SELECT * FROM users WHERE YEAR(create_time) = 2024;

-- 执行计划显示：type=ALL, rows=1000000

-- ✅ 优化后（使用索引）
SELECT * FROM users 
WHERE create_time >= '2024-01-01' 
  AND create_time < '2025-01-01';

-- 执行计划显示：type=range, rows=50000

-- 案例2：JOIN优化
-- ❌ 原始查询
SELECT u.username, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id
HAVING post_count > 10;

-- 执行计划显示：Using temporary, Using filesort

-- ✅ 优化后
SELECT u.username, p.post_count
FROM users u
JOIN (
    SELECT user_id, COUNT(*) as post_count
    FROM posts
    GROUP BY user_id
    HAVING COUNT(*) > 10
) p ON u.id = p.user_id;

-- 执行计划显示：Using index

性能分析工具

1. 慢查询日志分析

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1秒以上的查询记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 分析慢查询
-- 使用 mysqldumpslow 工具
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- 慢查询优化步骤
/**
 * 1. 识别慢查询：通过日志找出执行时间长的SQL
 * 2. 分析执行计划：使用EXPLAIN查看执行路径
 * 3. 优化索引：根据WHERE、JOIN、ORDER BY条件建索引
 * 4. 重写SQL：优化查询逻辑
 * 5. 验证效果：对比优化前后的执行时间
 */

2. Performance Schema监控

-- 启用性能监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'statement/%';

-- 查询最耗时的SQL
SELECT 
    DIGEST_TEXT,
    COUNT_S