MySQL 性能优化与索引策略 - 菜鸟的云端笔记

前言

在大型互联网项目中，MySQL 数据库往往成为性能瓶颈。经过两年的性能优化实践，我总结了一些行之有效的方法。本文将介绍如何通过合理的索引设计和查询优化来提升数据库性能，以及在实际项目中遇到的各种问题和解决方案。

一、索引基础与设计原则

1.1 B+ 树索引的工作原理

MySQL 中最常用的索引类型是 B+ 树索引。它适合范围查询和排序操作。B+ 树的特点是：中间节点只存储指针，叶子节点存储实际数据，这使得查询性能非常稳定。相比二叉树，B+ 树的树高更低，查询次数更少。

-- 查看表的所有索引
SHOW INDEX FROM users;

-- 创建单列索引
CREATE INDEX idx_username ON users(username);

-- 创建复合索引（注意字段顺序很重要）
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- 创建唯一索引，防止重复值
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建全文索引，用于模糊搜索
CREATE FULLTEXT INDEX idx_content ON articles(content);

1.2 复合索引与最左前缀原则

在创建复合索引时，必须深刻理解"最左前缀"原则。对于索引 (a, b, c)，MySQL 查询优化器可以使用该索引的条件是：查询条件包含 a、ab、abc 时能完全使用索引，但仅包含 b 或 c 时则无法使用该索引。

-- 这个查询能充分利用 idx_dept_salary 索引
SELECT * FROM employees 
WHERE department = '技术部' AND salary > 10000;

-- 这个查询只能使用索引的第一列（department）
SELECT * FROM employees WHERE salary > 10000;

-- 正确的复合索引顺序应该是：
-- 高频查询条件 > 等号条件 > 排序字段 > 范围条件
CREATE INDEX idx_better ON orders(user_id, status, created_at DESC);

二、实战优化案例

2.1 案例：电商订单查询性能问题

某电商系统订单表有超过 500 万条记录。当用户查询"近30天的订单"时，响应时间超过 5 秒，这严重影响了用户体验。通过 EXPLAIN 分析发现，该查询没有使用任何索引，执行了全表扫描。

-- 优化前（全表扫描，非常低效）
SELECT * FROM orders 
WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 执行时间：5000+ms

-- 优化方案：为 created_at 添加索引
CREATE INDEX idx_created_at ON orders(created_at);

-- 但更好的方案是：创建组合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

-- 优化后的查询
SELECT * FROM orders 
WHERE user_id = 123 
  AND created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY created_at DESC
LIMIT 20;
-- 执行时间：150ms，性能提升 97%

2.2 索引失效的常见原因

在索引列使用函数：SELECT * FROM users WHERE YEAR(birth_date) = 1990; 会导致索引失效，应改为 birth_date BETWEEN '1990-01-01' AND '1990-12-31'
隐式类型转换：SELECT * FROM users WHERE id = '123'; 会尝试将字符串转为数字，可能导致索引失效
使用 OR 条件：WHERE id = 1 OR id = 2 在某些情况下不走索引，改用 IN 更高效
负向条件：WHERE id != 1 无法使用索引，考虑 WHERE id < 1 OR id > 1
LIKE 通配符：LIKE '%keyword' 无法使用索引，LIKE 'keyword%' 可以使用

三、查询优化技巧

3.1 使用 EXPLAIN 深度分析

EXPLAIN SELECT * FROM orders 
WHERE user_id = 1 AND status = 'completed' 
ORDER BY created_at DESC;

-- 关键字段说明：
-- id: SELECT 查询的序列号
-- type: 查询扫描类型（性能从好到差）
--   const: 最快，按主键查询
--   eq_ref: 使用唯一索引等值查询
--   ref: 使用非唯一索引等值查询
--   range: 范围查询（如 IN、BETWEEN）
--   index: 全索引扫描
--   ALL: 全表扫描，最慢
-- key: 实际使用的索引名称
-- key_len: 使用的索引长度（越小越好）
-- rows: MySQL 预计扫描的行数
-- Extra: 额外信息
--   Using index: 使用了覆盖索引，性能最好
--   Using where: 需要回表查询
--   Using filesort: 需要文件排序，可能需要优化

3.2 分页查询优化

分页查询是最常见的功能。当 offset 很大时，性能会急剧下降，因为 MySQL 需要扫描大量被丢弃的行。

-- 低效的分页方式（offset 很大时非常慢）
SELECT * FROM products LIMIT 100000, 20;
-- 需要扫描 100020 行，然后丢弃前 100000 行

-- 优化方案 1：使用"续点法"
SELECT * FROM products 
WHERE id > 上一页的最后id 
ORDER BY id 
LIMIT 20;

-- 优化方案 2：JOIN 优化（当需要排序时）
SELECT p.* FROM products p
JOIN (
    SELECT id FROM products 
    WHERE ... 
    ORDER BY created_at DESC 
    LIMIT 100000, 20
) t ON p.id = t.id;

-- 优化方案 3：使用覆盖索引
CREATE INDEX idx_status_created ON products(status, created_at DESC);
SELECT id, title FROM products 
WHERE status = 'published' 
ORDER BY created_at DESC 
LIMIT 10000, 20;

四、深层优化策略

4.1 表分区策略

对于超大表（几千万甚至数亿记录），可以使用分区表。按日期分区能显著提升查询性能和维护效率。

-- 按年份分区
CREATE TABLE logs (
    id INT,
    level VARCHAR(10),
    message TEXT,
    created_at DATE
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 删除旧数据分区（性能远好于 DELETE）
ALTER TABLE logs DROP PARTITION p2022;

4.2 垂直分表

当某些列的查询频率低但字段很大时（如文章的完整内容字段），可以分离到另一张表，避免主表的读写压力。

五、监控与维护

5.1 定期维护索引

-- 找出未使用的索引
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_NAME = 'your_table' AND COUNT_READ = 0;

-- 重建索引以消除碎片
OPTIMIZE TABLE your_table;

-- 查看表和索引的大小
SELECT 
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) as size_mb
FROM information_schema.tables
WHERE table_schema = 'your_db'
ORDER BY (data_length + index_length) DESC;

5.2 监控慢查询日志

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 分析慢查询日志
mysqldumpslow -s at /var/log/mysql/slow.log | head -10

-- 使用 Percona Toolkit 工具
pt-query-digest /var/log/mysql/slow.log | head -30

六、性能对比与成果

通过上述优化，我的项目数据库查询性能平均提升了 85%，单个查询的响应时间有显著下降：

📊 订单查询：从 5000ms → 150ms（性能提升 97%）
📊 用户统计：从 3000ms → 200ms（性能提升 93%）
📊 数据导出：从 10000ms → 500ms（性能提升 95%）
📊 实时报表：从 8000ms → 300ms（性能提升 96%）

总结与最佳实践

MySQL 性能优化没有银弹，需要理论与实践相结合。关键是理解数据库的工作原理，合理使用工具进行分析。以下是最重要的几点：

充分理解索引原理和查询优化器的工作方式
使用 EXPLAIN 工具深入分析查询计划
合理设计索引，避免过度索引（过多索引会拖累写入性能）
定期监控和维护数据库（慢查询日志、索引碎片整理）
考虑缓存和分布式方案解决更大规模问题
必要时使用读写分离、分库分表等高级方案