MySQL 索引原理与优化

索引是数据库查询性能优化的核心技术，深入理解索引原理对于写出高效 SQL 至关重要。

索引基础

什么是索引

索引是一种数据结构，能够快速定位数据，类似书籍的目录：

sql复制
-- 创建索引
CREATE INDEX idx_user_name ON users(name);

-- 查看索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_user_name ON users;

索引的优缺点

优点：

• 加速数据检索
• 减少 I/O 操作
• 加速排序和分组

缺点：

• 占用磁盘空间
• 降低写操作性能（INSERT/UPDATE/DELETE）
• 索引维护开销

B+ 树索引

InnoDB 索引结构

InnoDB 使用 B+ 树作为索引结构：

                    [根节点/内部节点]
                    /      |       \
              [页1]    [页2]    [页3]
              /  \     /  \     /   \
           [叶] [叶] [叶] [叶] [叶]  [叶]
           数据  数据  数据  数据 数据  数据

B+ 树的特点

1. 所有数据在叶子节点：查询稳定
2. 叶子节点链表连接：范围查询高效
3. 多路平衡查找树：树高低，I/O 次数少

主键索引 vs 辅助索引

sql复制
-- 主键索引（聚簇索引）
-- 数据和索引在一起，叶子节点存储完整行数据
CREATE TABLE users (
    id INT PRIMARY KEY,  -- 主键索引
    name VARCHAR(50),
    age INT
);

-- 辅助索引（非聚簇索引）
-- 叶子节点存储主键值，查找需要回表
CREATE INDEX idx_name ON users(name);  -- 辅助索引
CREATE INDEX idx_age ON users(age);

辅助索引查询过程（回表）

sql复制
SELECT * FROM users WHERE name = '张三';

-- 1. 在 idx_name 索引中查找 '张三'
-- 2. 得到主键 id = 5
-- 3. 再回到主键索引查找完整行数据
-- 4. 返回结果

-- 如果只查询索引列，不需要回表（覆盖索引）
SELECT name FROM users WHERE name = '张三';

索引类型

1. 单列索引

sql复制
CREATE INDEX idx_name ON users(name);

2. 组合索引（最左前缀原则）

sql复制
-- 创建组合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 生效情况：
-- ✓ WHERE name = '张三'        -- 命中索引最左列
-- ✓ WHERE name = '张三' AND age = 25   -- 命中全部
-- ✓ WHERE name LIKE '张%'      -- 命中最左前缀
-- ✗ WHERE age = 25             -- 不命中
-- ✗ WHERE name LIKE '%三'      -- 不命中（不满足最左前缀）

3. 唯一索引

sql复制
-- 值必须唯一，可为空
CREATE UNIQUE INDEX idx_email ON users(email);

-- 主键本身就是唯一索引

4. 全文索引

sql复制
-- 用于文本搜索
CREATE FULLTEXT INDEX idx_content ON articles(content);

SELECT * FROM articles
WHERE MATCH(content) AGAINST('关键词');

索引失效情况

1. 使用函数或运算

sql复制
-- ✗ 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✓ 索引生效
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

2. 类型转换

sql复制
-- ✗ 隐式类型转换，索引失效
SELECT * FROM users WHERE age = '25';  -- age 是 INT

-- ✓ 正确写法
SELECT * FROM users WHERE age = 25;

3. LIKE 前缀通配符

sql复制
-- ✗ 索引失效
SELECT * FROM users WHERE name LIKE '%三';

-- ✓ 索引生效
SELECT * FROM users WHERE name LIKE '张三%';

4. OR 条件

sql复制
-- ✗ OR 导致索引失效（部分情况）
SELECT * FROM users WHERE name = '张三' OR age = 25;

-- ✓ 改写为 UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 25;

5. NOT NULL / IS NOT NULL

sql复制
-- 可能导致索引失效
SELECT * FROM users WHERE name IS NOT NULL;

-- 建议：给默认值，避免 NULL
ALTER TABLE users MODIFY name VARCHAR(50) NOT NULL DEFAULT '';

SQL 性能分析

EXPLAIN 分析

sql复制
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 关键字段：
-- type: 查询类型（const, ref, range, ALL 等）
--        const > eq_ref > ref > range > index > ALL
-- key:  实际使用的索引
-- rows: 预计扫描的行数
-- extra: 额外信息（Using index, Using filesort 等）

慢查询日志

sql复制
-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

-- 查看慢查询
SHOW FULL PROCESSLIST;

索引优化最佳实践

1. 遵循最左前缀原则

sql复制
-- 创建恰当的组合索引
-- 如果经常按 (a, b, c) 查询，创建 idx_a_b_c
CREATE INDEX idx_a_b_c ON orders(a, b, c);

-- 如果只按 a 和 c 查询，创建 idx_a_c
CREATE INDEX idx_a_c ON orders(a, c);

2. 使用覆盖索引

sql复制
-- 覆盖索引：查询的所有列都在索引中
-- 不需要回表，性能更好

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

-- 使用覆盖索引，不需要回表
SELECT name, age FROM users WHERE name = '张三';

3. 避免过多索引

sql复制
-- 每个索引都会占用磁盘空间
-- 写操作时需要更新所有索引
-- 选择性高的列更适合建索引

-- 查看列的选择性
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
-- 选择性越高（接近 1），索引效果越好

4. 前缀索引

sql复制
-- 对于长字符串列，使用前缀索引
CREATE INDEX idx_email ON users(email(10));

-- 适用于：email, url, UUID 等

常见面试问题

Q1: B+ 树和 B 树的区别？

Q2: 聚簇索引 vs 非聚簇索引？

Q3: 为什么建议主键用自增 ID？

sql复制
-- 自增主键优点：
-- 1. 插入时追加到末尾，避免页分裂
-- 2. 保持数据紧凑，减少空间碎片
-- 3. 查询性能稳定

-- UUID 作为主键的问题：
-- 1. UUID 随机，插入位置不确定
-- 2. 可能触发页分裂，影响插入性能
-- 3. 字符串比较比整数慢

-- 推荐使用：自增 BIGINT 或雪花 ID

Q4: 如何优化分页查询？

sql复制
-- ✗ 低效分页（深度分页问题）
SELECT * FROM orders LIMIT 1000000, 10;
-- 需要扫描 1000010 行，只返回 10 行

-- ✓ 优化：使用游标分页
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;
-- 直接定位到起始位置

-- ✓ 优化：子查询
SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders LIMIT 1000000, 1)
LIMIT 10;

Q5: 什么情况下不应该建索引？

1. 数据量小的表（几百行）
2. 频繁更新的列
3. 选择性低的列（性别只有男/女）
4. 参与计算的列
5. 很少使用的查询条件列