SQL优化必看：4层架构破解性能瓶颈

SQL 优化

1.1 概念

在应用的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化。

1.1.1 MySQL 的优化方式

• 从设计上优化：库表结构、字段类型、范式 / 反范式选择
• 从查询上优化：规范 SQL 写法，规避索引失效场景
• 从索引上优化：合理创建索引，遵循联合索引最左前缀原则
• 从存储上优化：引擎选型、分区、分库分表、读写分离、缓存架构

1.2 SQL 优化小技巧

1.2.1 原则

减小查询的数据量、提升 SQL 的索引命中率，尽量让数据库扫描更少的数据行，优先走索引而不是全表扫描。

1.2.2 查询时尽量不要使用*

日常开发图方便会写select *，但生产环境不推荐。 原因：

✅ 推荐写法：明确写出需要查询的字段

— 不推荐
select * from student where id=1;
— 推荐
select id,name,age from student where id=1;

1.2.3 连表查询时尽量不要关联太多表

多表 JOIN 时，MySQL 需要生成笛卡尔积、选择驱动表，表越多执行计划越复杂，消耗资源越高。

• 交互业务（前端用户请求）：关联表控制在5 张以内，阿里开发规范建议控制在 3 张以内；
• 后台统计报表业务：业务复杂，可适当放宽，《高性能 MySQL》建议尽量不超过 16~18 张。

1.2.4 多表查询一定要以小驱大

小表驱动大表：优先查询数据量更小的表，拿小表的结果集合，去大表匹配检索。

小表放前面，大表放后面；性能关键点：被驱动表的关联字段必须建立索引。 原理：外层循环次数少，内层通过索引快速定位，大幅减少扫描行数。

1.2.5 不要使用 like 左模糊和全模糊查询

%xxx、%xxx% 会导致索引失效，触发全表扫描，只有后缀匹配xxx%可以正常走索引。

— 索引失效，禁止
select * from student where name like '%张三';
select * from student where name like '%张三%';
— 可以走索引
select * from student where name like '张三%';

解决方案：

1.2.6 查询时尽量不要对字段做空值判断

select * from xxx where xxx is null;
select * from xxx where xxx is not null;

is null /is not null 很容易造成索引失效，走全表扫描。 ✅ 设计规范：字段定义为NOT NULL，用默认值代替 NULL，例如空字符串''、数字 0。

— 改造后，可以正常走索引
select * from xxx where xxx = '';

1.2.7 不要在条件=前对字段做任何运算、函数操作

— 索引失效示例
select * from student where id * 2 = 8;
select * from student where trim(name) = "张三";
select * from student where date(create_time) = '2026-10-30';

原理：MySQL 优化器无法识别运算、函数之后的值，不会使用该字段索引，触发全表扫描。 ✅ 改造思路：把运算转移到常量一侧

select * from student where id = 8 / 2;
select * from student where name = trim("张三");

1.2.8 !=、not in、not like、or要慎用

这类条件很容易造成索引失效，需要改写 SQL。

— or示例，可能不走索引
select name from student where id=1 or id=2;
— 改写，union all可以分别走索引，性能更高
select name from student where id=1
union all
select name from student where id=2;

区分：union会去重排序，union all直接拼接结果，不需要排序，优先使用 union all。

1.2.9 避免频繁创建、销毁临时表

临时表存放在内存，查询速度快，适合复用结果集； 但是不要频繁新建销毁临时表，频繁创建释放会给 MySQL 带来大量开销，只适合多次复用的统计场景。

1.2.10 从业务设计层面减少一次性返回大量数据

禁止一次性查询全表数据，在应用层做筛选。 一次性返回海量数据会造成：网络阻塞、服务 OOM、数据库 IO 飙升。 ✅ 优化方案：分页查询、分批拉取数据。

1.2.11 尽量避免深分页

— 深分页，低效
select xx,xx,xx from yyy limit 100000,10;

执行逻辑：先读取 100010 行数据，丢弃前 10 万条，只返回最后 10 条，扫描大量无效数据。

✅ 两种优化方案

1.有序连续主键（自增 id）：主键筛选分页（书签分页）

select xx,xx,xx from yyy where id >= 100000 limit 10;

2.无序搜索分页：业务层面限制，不提供深分页入口（搜索引擎常用方案，用户一般只看前几十页）

1.2.12 SQL 务必写完整，不要使用隐式简写

隐式写法底层需要额外语法转换，多一层开销，规范项目建议写完整 SQL。

— 简写（不推荐）
select name "姓名" from student;
select * from t1,t2 where t1.id=t2.tid;

— 完整规范写法（推荐）
select name as "姓名" from student;
select * from t1 inner join t2 on t1.id = t2.tid;

1.2.13 明确只返回一条数据，加上 limit 1

— 优化前，扫描全部匹配行
select * from student where name = "李四";
— 优化后，找到第一条匹配数据立刻停止扫描
select * from student where name = "李四" limit 1;

1.2.14 批量操作合并 SQL，减少循环单次提交

❌ 错误写法：循环里面多次执行 insert，频繁网络交互

for (Student stu : list) {
insert into student values(?,?,?);
}

✅ 推荐批量插入，一条 SQL 完成

insert into student(id,name,age) values (1,'a',18),(2,'b',19),(3,'c',20);

批量 SQL 大幅减少网络往返次数，提升吞吐。

1.3 查看 SQL 执行效率

优化第一步：找到慢 SQL，MySQL 提供多种工具定位低效语句。

1.3.1 show status 查看数据库统计信息

作用：查看当前会话 / 全局的增删改查统计，判断数据库负载类型。

— 当前会话统计
show session status like 'Com_______';
— 全局统计，MySQL启动后累计
show global status like 'Com_______';
— InnoDB行操作统计
show status like 'Innodb_rows_%';

字段说明：

• Com_select：查询次数
• Com_insert / Com_update / Com_delete：写入次数
• Innodb_rows_read：InnoDB 读取行数，数值过大说明大量扫描

1.3.2 定位低效率 SQL 两种方式

1.慢查询日志 slow_query_log：自动记录超过阈值的慢 SQL

— 查询慢日志配置
show variables like '%slow_query_log%';
— 开启慢查询日志（会话级别重启失效，生产建议配置文件持久化）
set global slow_query_log=1;
— 查询慢查询阈值，单位秒
show variables like 'long_query_time%';
— 设置阈值：执行超过4秒就记录
set global long_query_time=4;

2.show processlist：实时查看当前正在运行的线程、SQL、锁状态

show processlist;

字段解析：

1.3.3 explain 执行计划（重点）

作用：拿到慢 SQL 后，使用explain分析 MySQL 的执行计划，判断索引是否生效、扫描行数。

explain select * from student where sno = 's003';

核心字段解读：

• type：访问类型，性能从优到劣：system > const > eq_ref > ref > range > index > ALL，出现 index、ALL 代表全索引扫描 / 全表扫描，需要优化；
• key：实际使用到的索引，NULL = 没有走索引；
• rows：预估扫描行数，数值越大性能越差；
• filtered：满足条件的数据占扫描行比例，值越低代表过滤效率差；
• Extra：额外信息，常见Using filesort（文件排序，无索引排序）、Using temporary（使用临时表，需要优化）。

1.4 show profile（MySQL8.0 已废弃，仅了解）

⚠️ 该命令在新版 MySQL 中已经失效，推荐使用 performance_schema 替代，老版本 5.7 可以使用。 作用：分析 SQL 各个阶段耗时，定位时间消耗在哪一步。

— 查看是否支持
select @@have_profiling;
— 开启
set profiling=1;

— 执行多条SQL
select * from student where year(now())-year(birth)<20;
select count(*) from student;

— 查看所有SQL的profile编号
show profiles;
— 查看指定SQL详细耗时
show profile for query 3;
— 查看CPU资源消耗
show profile cpu for query 3;

1.5 trace 分析优化器执行计划

作用：查看 MySQL 优化器的决策过程，搞清楚为什么优化器没有选择预期索引、为什么选择这个执行方案。

— 开启trace，json格式
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

— 执行目标SQL
select * from student where year(now())-year(birth)<16;

— 读取trace结果
select * from information_schema.optimizer_trace\\G;

输出是 JSON 格式，包含优化、成本计算、索引选择逻辑，适合深度调优。

1.6 使用索引优化

索引是 MySQL 优化最核心、最高频的手段，大部分慢 SQL 问题都可以通过合理索引解决。

1.6.1 索引建立原则

1.7 架构层面优化

SQL 和索引优化到瓶颈之后，就需要架构改造：