Logo

菜单

易君召
发布于 2026-10-17 / 作者：易君召 / 0 阅读
0

SQL 查询性能优化完整指南

优化思路分为 4 大块：SQL 语句本身优化、索引优化、数据库表结构设计、执行计划与数据库配置，优先从执行计划入手定位瓶颈，不要盲目加索引。

一、先看执行计划（定位问题的第一步）

MySQL：EXPLAIN SELECT ... PostgreSQL：EXPLAIN ANALYZE SELECT ... 重点关注字段：

  1. type：最好 system > const > eq_ref > ref > range > index > ALL，ALL 代表全表扫描，需要优先优化

  2. key：实际使用的索引，NULL 说明没走索引

  3. rows：预估扫描行数，数值越大越危险

  4. Extra：

    • Using filesort：文件排序，无法利用索引排序，需要优化

    • Using temporary：创建临时表，常见于 group by / 多表 join

    • Using index：覆盖索引，性能最优

原则：减少扫描行数，尽量避免回表。

二、SQL 语句写法优化

1. 避免索引失效（高频踩坑点）

  • 不要在索引列上做函数、运算、隐式类型转换

-- ❌ 索引失效
WHERE YEAR(create_time) = 2025;
WHERE id + 1 = 100;
WHERE phone = 13800138000; -- phone是字符串，传入数字，隐式转换

-- ✅ 改写
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';
WHERE id = 99;
WHERE phone = '13800138000';

  • like '%关键词' 前缀通配符不走索引；like '关键词%' 可以走索引

  • OR 连接非索引字段容易失效，可以改用 UNION ALL 代替

2. 减少不必要的数据读取

  • 禁止 SELECT *，只查询需要的字段（减少网络 IO、内存消耗，利于覆盖索引）

  • 分页大偏移量优化：limit 100000,10 不要直接写，用主键书签

-- ❌ 低效
SELECT id,name FROM user LIMIT 100000,10;
-- ✅ 书签分页
SELECT id,name FROM user WHERE id > 100000 LIMIT 10;

  • 避免大事务一次性查询大量数据，分批分页读取

  • 尽量少用 DISTINCT，它会产生排序开销；能在业务层去重就业务层处理

3. JOIN、GROUP BY、子查询优化

  • 小表驱动大表：INNER JOIN 数据库会自动选驱动表；LEFT JOIN 左表是驱动表，左表尽量小

  • JOIN 字段必须建立索引，关联字段类型保持一致

  • 尽量避免多表连环 JOIN（超过 3 张表谨慎），可以拆成多次查询或者中间表

  • 子查询：IN (子查询) 数据量大时性能差，优先改成 JOIN；EXISTS 适合外表小、内表大场景

  • GROUP BY 尽量利用索引有序性，避免 Using temporary + Using filesort；可以先过滤再聚合

4. 其他写法规范

  • 用 UNION ALL 代替 UNION：UNION 会自动去重排序，开销巨大；业务确认无重复再用 UNION ALL

  • 过滤条件尽量前置，先 WHERE，后 JOIN，减少参与 join 的数据量

  • 不要在 WHERE 中使用 !=、<>，会放弃索引；可以调整业务逻辑

  • 批量操作：批量 INSERT 用多条值合并，不要循环单条 insert

三、索引优化（索引是双刃剑，不是越多越好）

索引选型

  1. 主键索引：聚簇索引，尽量使用自增整数主键，避免随机主键（UUID 会造成页分裂）

  2. 普通 B + 树索引：等值查询、范围查询、排序

  3. 联合索引：遵循最左前缀原则 

    -- 索引 idx_a_b_c(a,b,c)
-- ✅ 能命中：where a=?；where a=? and b=?；where a=? and b=? and c>?
-- ❌ 无法单独命中 where b=? 或 where c=?

  4. 覆盖索引：查询字段全部包含在索引里，不需要回表，Extra:Using index，性能最好

  5. 其他索引：全文索引（文本搜索）、空间索引；低区分度字段不要建索引（如性别，只有 0/1）

索引设计原则

  • 索引字段优先：高区分度、经常出现在 WHERE/JOIN/ORDER BY/GROUP BY 的字段

  • 联合索引：等值条件放前面，范围条件放最后；order by 字段尽量放到联合索引末尾

  • 不要给很少更新但极少查询的字段建索引；写操作（INSERT/UPDATE/DELETE）会维护所有索引，索引越多写入越慢

  • 定期清理无用索引，冗余索引

四、表结构设计优化

  1. 字段类型：尽量小，够用即可

    • 主键用 BIGINT/INT，不要用字符串做主键；日期推荐 datetime，少用 varchar 存时间

    • 字符串字段：CHAR固定长度，短字符串；VARCHAR可变长度；超长文本用 TEXT，不要放到常规查询里

  2. 大字段拆分：blob、大 text 单独拆到扩展表，主表只存 id，避免查询主表时加载大字段

  3. 分库分表：数据量达到千万 / 亿级别，单表瓶颈，水平分表（按用户 id / 时间分片）；垂直分表（冷热分离）

  4. 冷热数据分离：历史归档数据迁移到归档表，业务库只保留近期热数据

五、数据库层面优化

1. 存储引擎（MySQL）

  • InnoDB：支持事务、行锁，绝大多数业务首选；MyISAM 表锁，不支持事务，现在很少用

  • InnoDB 核心参数：innodb_buffer_pool_size（最重要，一般设为物理内存 50%~70%，缓存索引 + 数据页）

2. 锁与事务优化

  • 避免长事务：长事务会占用回滚段、锁资源，产生 MVCC 版本堆积，拖慢查询

  • 尽量缩小事务粒度，减少锁持有时间；更新尽量按主键更新（行锁），避免无索引更新导致表锁

  • 避免大事务一次性批量更新，分批提交

3. 缓存层（减少 DB 访问）

  • 热点数据：Redis/Memcached 缓存，查询先读缓存，未命中再查数据库

  • 结果集缓存：数据库查询缓存 MySQL8.0 已经移除，不要依赖，应用层做缓存

六、高级方案（当 SQL + 索引优化仍无法满足）

  1. 读写分离：主库写入，从库承担 SELECT 查询压力

  2. 预计算：报表、统计类查询不要实时聚合；定时任务计算结果存入汇总表，业务直接查汇总表

  3. OLAP 引擎：海量数据分析查询（日志、统计），使用 ClickHouse/Doris 替代 MySQL

七、优化排查流程（标准工作流）

  1. 慢查询日志开启，抓取慢 SQL（MySQL slow_query_log）

  2. EXPLAIN 分析执行计划，定位：全表扫描？没走索引？临时表 / 文件排序？

  3. 优化 SQL 写法，调整 where 条件、join 顺序

  4. 新增 / 调整联合索引，优先构建覆盖索引

  5. 验证压测，确认 QPS 提升、耗时下降；同时评估写入性能影响

  6. 仍有瓶颈：考虑分表、读写分离、预聚合

高效前后端 API 接口设计完整指南