MySQL 性能调优：索引设计、慢查询分析与千万级数据实战

一、前言MySQL 性能调优索引设计、慢查询分析与千万级数据实战是后端工程师必须掌握的核心技能。本文从MySQL出发覆盖开发中最实用的知识点配有完整可运行的 SQL/代码示例。二、索引设计与优化2.1 索引类型选择-- 基础索引 CREATE INDEX idx_user_id ON orders(user_id); -- 联合索引遵循最左前缀原则 CREATE INDEX idx_user_status ON orders(user_id, status, created_at); -- 唯一索引保证数据唯一性 CREATE UNIQUE INDEX uk_email ON users(email); -- 前缀索引大字符串列节省空间 CREATE INDEX idx_title ON articles(title(20));2.2 索引失效的典型场景-- ❌ 索引失效函数操作导致无法使用索引 SELECT * FROM users WHERE YEAR(created_at) 2026; -- ✅ 正确使用范围查询 SELECT * FROM users WHERE created_at 2026-01-01 AND created_at 2027-01-01; -- ❌ 索引失效类型转换 SELECT * FROM orders WHERE user_id 12345; -- user_id 是 int -- ✅ 正确类型匹配 SELECT * FROM orders WHERE user_id 12345;三、慢查询分析与优化3.1 开启慢查询日志-- 查看慢查询配置 SHOW VARIABLES LIKE slow_query%; SHOW VARIABLES LIKE long_query_time; -- 设置慢查询阈值为 1 秒 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 分析慢查询 EXPLAIN SELECT u.name, o.total FROM users u JOIN orders o ON u.id o.user_id WHERE u.status active ORDER BY o.created_at DESC LIMIT 20;3.2 分页优化-- ❌ 深度分页问题OFFSET 越大越慢 SELECT * FROM orders ORDER BY id LIMIT 100000, 20; -- ✅ 优化使用游标分页基于上一页最后一条 ID SELECT * FROM orders WHERE id #{last_id} ORDER BY id LIMIT 20; -- ✅ 优化子查询先定位主键 SELECT * FROM orders WHERE id IN ( SELECT id FROM orders WHERE user_id 123 ORDER BY id LIMIT 20 OFFSET 100000 );四、事务与锁4.1 事务隔离级别-- 查看当前隔离级别 SELECT tx_isolation; -- 设置隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 开启事务 START TRANSACTION; UPDATE accounts SET balance balance - 100 WHERE user_id 1; UPDATE accounts SET balance balance 100 WHERE user_id 2; -- 提交或回滚 COMMIT; -- ROLLBACK; -- 出错时回滚4.2 行锁与死锁处理-- 显式加锁用于数据一致性要求高的场景 SELECT * FROM inventory WHERE product_id 100 FOR UPDATE; -- 锁定行防止并发修改 -- 死锁查看 SHOW ENGINE INNODB STATUS;五、千万级数据实战5.1 分库分表策略-- 按用户 ID 哈希分表8 张表 -- table_0 ~ table_7 hash(user_id) % 8 -- 查询路由逻辑应用层实现 function getTableName(userId) { const tableIndex userId % 8; return orders_${tableIndex}; }5.2 数据归档-- 历史数据归档到单独表 INSERT INTO orders_archive SELECT * FROM orders WHERE created_at DATE_SUB(NOW(), INTERVAL 1 YEAR); -- 删除已归档数据 DELETE FROM orders WHERE created_at DATE_SUB(NOW(), INTERVAL 1 YEAR); -- 定期执行建议低峰期 -- 0 2 * * 0 /usr/bin/mysql -e CALL archive_old_orders()六、总结索引不是越多越好——每个索引都增加写入开销慢查询分析是优化第一步——不要猜要实测深度分页用游标替代 OFFSET定期归档历史数据保持表轻盈收藏本文关注我后续更新更多数据库实战系列。觉得有用的话点个赞收藏关注我持续更新优质技术内容标签MySQL | 性能调优 | 索引 | 数据库 | 实战