MySQL 索引优化：从原理到实战

索引的基本原理

索引就像书的目录，帮助快速定位数据，避免全表扫描。

B+Tree 索引结构

        [根节点]
       /    |    \
    [内部节点] [内部节点] [内部节点]
    /  \      /  \      /  \
  [叶子节点]...[叶子节点]...[叶子节点]

特点：

• 所有数据存储在叶子节点
• 叶子节点之间有指针连接（范围查询快）
• 树高度低（3-4 层可存储千万级数据）

创建索引的最佳实践

1. 选择合适的列

✅ 适合索引：

• WHERE 条件列
• JOIN 连接列
• ORDER BY 排序列
• GROUP BY 分组列

❌ 不适合索引：

• 区分度低的列（如性别）
• 频繁更新的列
• 过长的字符串列

2. 联合索引

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 遵循最左前缀原则
SELECT * FROM users WHERE name = "张三";        -- 使用索引
SELECT * FROM users WHERE name = "张三" AND age = 25;  -- 使用索引
SELECT * FROM users WHERE age = 25;             -- 不使用索引！

3. 覆盖索引

-- 查询只涉及索引列，无需回表
CREATE INDEX idx_email_name ON users(email, name);

SELECT email, name FROM users WHERE email = "test@example.com";
-- 直接使用索引，性能极高

EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE email = "test@example.com";

关键字段：

• type：访问类型（system > const > eq_ref > ref > range > index > ALL）
• key：实际使用的索引
• rows：扫描行数
• Extra：额外信息（Using index = 覆盖索引）

常见优化场景

场景 1：LIKE 查询

-- ❌ 不使用索引
SELECT * FROM users WHERE name LIKE "%张%";

-- ✅ 使用索引
SELECT * FROM users WHERE name LIKE "张%";

场景 2：函数操作

-- ❌ 不使用索引
SELECT * FROM users WHERE YEAR(create_time) = 2024;

-- ✅ 使用索引
SELECT * FROM users WHERE create_time >= "2024-01-01" 
                        AND create_time < "2025-01-01";

场景 3：OR 条件

-- ❌ 可能不使用索引
SELECT * FROM users WHERE email = "a@test.com" OR phone = "123";

-- ✅ 使用 UNION
SELECT * FROM users WHERE email = "a@test.com"
UNION
SELECT * FROM users WHERE phone = "123";

索引维护

-- 查看索引使用情况
SHOW INDEX FROM users;

-- 删除无用索引
DROP INDEX idx_unused ON users;

-- 分析表统计信息
ANALYZE TABLE users;

记住：索引不是越多越好，合适的索引才是好索引！