数据库性能是系统性能的关键瓶颈。本文深入讲解 MySQL 索引优化技巧，帮助开发者提升查询效率。

一、索引基础原理

1.1 B+ 树结构

MySQL InnoDB 使用 B+ 树存储索引，特点：

• 非叶子节点只存储键值
• 叶子节点存储完整数据
• 叶子节点之间用链表连接

1.2 聚簇索引 vs 二级索引

聚簇索引的叶子节点存储完整行数据，二级索引叶子节点存储主键值。

二、索引创建策略

2.1 选择合适的列

-- ✅ 适合创建索引
WHERE user_id = 123
WHERE created_at > '2026-01-01'
JOIN orders ON users.id = orders.user_id

-- ❌ 不适合创建索引
WHERE status IN (0, 1)  -- 区分度低
WHERE gender = 'M'      -- 区分度低

2.2 复合索引设计

-- 最左前缀原则
CREATE INDEX idx_user_time ON orders(user_id, created_at);

-- ✅ 可以使用索引
WHERE user_id = 1
WHERE user_id = 1 AND created_at > '2026-01-01'

-- ❌ 不能使用索引
WHERE created_at > '2026-01-01'

三、查询优化

3.1 EXPLAIN 分析

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- 关注字段：
-- type: system > const > eq_ref > ref > range > index > ALL
-- key: 实际使用的索引
-- rows: 扫描行数
-- Extra: Using index(好) / Using temporary(需优化)

3.2 避免索引失效

-- ❌ 索引失效
WHERE YEAR(created_at) = 2026
WHERE name LIKE '%张%'
WHERE age != 25
WHERE phone = 13800000000 OR email = 'test@test.com'

-- ✅ 索引有效
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
WHERE name LIKE '张%'
WHERE age = 25
UNION ALL 代替 OR

四、覆盖索引优化

查询的列都在索引中，无需回表。

-- 创建覆盖索引
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 使用覆盖索引
SELECT user_id, status FROM orders WHERE user_id = 123;

五、分页优化

-- ❌ 慢查询（深度分页）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- ✅ 优化方案 1：延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders LIMIT 1000000, 10) tmp
ON o.id = tmp.id;

-- ✅ 优化方案 2：记录上次 ID
SELECT * FROM orders WHERE id > 1000000 LIMIT 10;

六、表结构优化

6.1 选择合适的数据类型

-- ✅ 更小更好
TINYINT < SMALLINT < INT < BIGINT
VARCHAR(20) < VARCHAR(255)
DATETIME < TIMESTAMP (根据需求选择)

6.2 垂直分区

将大字段拆分到独立表。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE users_profile (
    user_id INT PRIMARY KEY,
    bio TEXT,
    avatar VARCHAR(255)
);

七、实战案例

7.1 订单查询优化

-- 优化前：2.5 秒
SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY created_at DESC 
LIMIT 20;

-- 优化后：0.05 秒
CREATE INDEX idx_user_created ON orders(user_id, created_at);

7.2 统计查询优化

-- 优化前：全表扫描
SELECT COUNT(*) FROM orders WHERE status = 1;

-- 优化后：使用覆盖索引
CREATE INDEX idx_status ON orders(status);

八、监控与维护

8.1 慢查询日志

[mysqld]
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

8.2 索引维护

-- 分析表
ANALYZE TABLE orders;

-- 检查索引使用情况
SELECT * FROM sys.schema_unused_indexes;

九、总结

1. 理解 B+ 树原理是优化的基础
2. 使用 EXPLAIN 分析查询计划
3. 避免常见索引失效场景
4. 定期监控慢查询
5. 根据实际业务调整索引策略

数据库优化需要持续监控和调整，建议建立性能基线定期对比。

十、高级优化技巧

10.1 索引下推 (ICP, MySQL 5.6+)

在索引遍历过程中进行条件过滤，减少回表次数。

-- 启用 ICP
SET optimizer_switch='index_condition_pushdown=on';

-- 适用场景
SELECT * FROM users WHERE name LIKE '张%' AND age > 20;
-- ICP 可以在索引层过滤 age > 20

10.2 不可见索引

测试删除索引的影响，无需真正删除。

ALTER TABLE orders ALTER INDEX idx_user INVISIBLE;
-- 观察性能变化
ALTER TABLE orders ALTER INDEX idx_user VISIBLE;

十一、分区表优化

11.1 RANGE 分区

CREATE TABLE orders (
    id INT, order_date DATE
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

十二、总结

1. 理解 B+ 树原理
2. 使用 EXPLAIN 分析查询
3. 避免索引失效
4. 定期监控慢查询
5. 建立性能基线

数据库优化是持续过程。