MySQL 性能优化与执行计划剖析实战指南

每天分享技术栈，开发工具等

前言：为什么你的数据库凉了？

我也曾为不懂执行计划和索引用法抓耳挠腮。今天这一篇，将带你从痛点出发，系统讲解 MySQL 性能优化与执行计划分析，让小白也能读完秒懂，迅速给业务提速、稳住数据库。

一、性能瓶颈定位流程

1

收集慢查询日志 在 MySQL 配置文件my.cnf中开启：

[mysqld]slow_query_log = ON -- 开启慢查询slow_query_log_file = /var/log/mysql/slow.loglong_query_time = 1 -- 记录执行时间超过1秒的SQLlog_queries_not_using_indexes = ON -- 记录未使用索引的查询

2

查看监控指标 关注 TPS（每秒事务数）、QPS（每秒查询数）、InnoDB 缓冲池命中率、磁盘 IO、CPU 利用率。

3

压测对比 使用 sysbench或自建脚本，从低并发到高并发，记录响应时间、错误率，明确瓶颈是在数据库还是业务层。

小白提示：先定位再优化，避免盲目改参数导致“好像快了点”却没解决根本问题。

二、EXPLAIN 使用详解

在客户端执行前，加上 EXPLAIN：

EXPLAIN SELECTu.id, u.name, o.order_date
FROMusers u
JOINorders o ONu.id=o.user_id
WHEREu.status='active'
ORDER BYo.order_dateDESC;

输出字段说明：

注释：type = ALL意味着全表扫描，Using filesort表示排序放在外部，需要优化索引或减少排序量。

三、索引优化策略

1

选择合适列建索引：高基数、参与过滤和排序的列

2

覆盖索引：让索引包含所有查询字段，减少回表

CREATEINDEXidx_user_status_dateONorders (user_id, order_date);
-- 查询时只访问索引，即使大量数据也极快
SELECTuser_id, order_date FROMorders WHEREuser_id=123ORDER BYorder_date;

3

联合索引顺序：最左前缀原则，WHERE子句中的列应按照索引顺序最前

4

避免冗余和失效索引：不要为大量 NULL 列或低基数列建索引；防止 function(col)失效

小白示例：原始表 orders(user_id INDEX, order_date INDEX)两个单列索引，WHERE user_id=? ORDER BY order_date会先根据 user_id 找行，再排序。用联合索引 NULL排序即可直接命中。

四、查询重写技巧

1. 1拆分复杂子查询：将子查询转为 JOIN 或临时表
2. 2使用 LIMIT 限制扫描量：分页查询务必加索引
3. 3调整 JOIN 顺序：小表先驱动大表
4. 4**避免 SELECT ***：只取需要的列减少网络传输

示例：原始写法

SELECT*FROMarticle WHEREid IN(SELECTarticle_id FROMtag_map WHEREtag='数据库');

重写为 JOIN

SELECTa.id, a.titleFROMarticle a
JOINtag_map t ONa.id=t.article_idANDt.tag='数据库';

五、配置参数调优

注意：参数修改需谨慎，线上先在测试环境验证。

六、架构层面优化

1. 1读写分离：主写从读，ProxySQL 或 MyCAT 等中间件实现
2. 2分库分表：根据业务规模，按用户、时间等维度拆分
3. 3缓存策略：Redis/Memcached 缓存热点数据，避免频繁数据库查询

小白思考：如果某张表每天新增百万行，读写分离并不能解决写入变慢，需要考虑分表或分区。

七、实战案例解析

1. 1某电商平台订单查询缓慢，通过 EXPLAIN 定位到 Using filesort，增加联合索引后 QPS 提升 40%
2. 2日志表单库单表超过千万行，分区表后每天归档清理，查询响应从 5s 降至 100ms
3. 3缓存穿透问题，用 Bloom Filter 预判断，缓存命中率提升至 95%

八、常见误区与排查思路

• 误区：加索引就一定快？ 实际上，索引也有维护成本，过多索引会降低写性能。
• 误区：临时表不算慢？ 物理临时表会写磁盘，开启大内存临时表或优化 SQL。
• 排查思路：CPU、内存、IO 三管齐下，结合 vmstat、iostat、top分析。

  ---