MySQL查询优化实战详解：条件、聚合、排序分页、联表与子查询

SQL 查询性能直接决定系统并发能力与响应速度，线上慢查询大多不是硬件问题，而是 WHERE、排序分页、JOIN 和子查询写法不合理导致。

作者：applelix 分类：数据库 & 存储 标签：MySQL、数据库优化、索引

在MySQL开发中，SQL查询性能直接决定系统并发能力与响应速度。大部分线上慢查询、接口超时、数据库CPU打满问题，并非硬件瓶颈，而是不合理的SQL写法、查询逻辑、索引使用不当导致。

本文针对开发与面试中五大核心查询场景：WHERE条件筛选、GROUP BY/HAVING聚合统计、ORDER BY/LIMIT排序分页、JOIN联表查询、相关/非相关子查询，从语法原理、错误案例、优化方案、面试考点全方位拆解，帮助开发者写出高性能、规范、可落地的查询SQL。

一、WHERE 条件查询优化（筛选层核心）

WHERE是SQL查询的第一层筛选，执行优先级最高，核心优化思想：尽早过滤数据、减少扫描行数、命中索引、避免失效。很多慢查询的根源都是WHERE条件写法不规范，导致索引失效、全表扫描。

1.1 核心优化原则

  • 能在WHERE过滤，绝不放到后级逻辑：优先通过条件缩小数据集，减少后续聚合、排序、联表的计算量

  • 避免索引失效场景：索引列杜绝运算、函数、隐式转换、左右模糊匹配

  • 精准匹配优先，范围查询后置：等值查询精准过滤，范围查询放在索引字段后侧

1.2 高频失效场景与优化方案

（1）索引列使用函数/运算

错误写法：对索引字段做函数运算，会直接导致索引失效，触发全表扫描

-- 低效：name为索引列，使用函数导致索引失效
SELECT * FROM student WHERE LEFT(name,1)='张';
-- 低效：age索引列做算术运算
SELECT * FROM student WHERE age+10=30;

优化方案：常量前移、字段裸用，将运算、函数转移到常量侧

-- 高效：利用模糊查询替代函数，命中索引
SELECT * FROM student WHERE name LIKE '张%';
-- 高效：转换运算逻辑，字段无运算
SELECT * FROM student WHERE age=20;

（2）模糊查询前缀通配符

%关键词、%关键词% 前后模糊匹配无法命中B+树索引，仅**关键词%**右模糊可走索引

-- 低效：全模糊、前缀模糊，索引失效
SELECT * FROM student WHERE name LIKE '%张三%';
SELECT * FROM student WHERE name LIKE '%张三';

优化方案：业务允许情况下使用右模糊；海量数据模糊检索优先使用ES、Solr等专业检索引擎。

（3）隐式类型转换

字符串索引字段与数字比对，MySQL会自动做隐式转换，导致索引失效

-- 低效：phone为varchar索引字段，匹配数字，触发隐式转换
SELECT * FROM student WHERE phone=13800138000;

优化方案：类型严格匹配，字符串字段必须用引号包裹

-- 高效：类型一致，正常命中索引
SELECT * FROM student WHERE phone='13800138000';

（4）OR 条件不当

OR左右字段索引不一致、部分字段无索引，会导致整体索引失效。优化：优先用UNION替代OR多条件跨字段查询。

二、聚合查询优化：GROUP BY / HAVING

聚合统计是业务高频场景（统计、分组、报表），核心问题：临时表创建、文件排序、超大分组数据集。优化核心：先过滤、后分组，减少分组数据量，避免无效聚合。

2.1 WHERE 与 HAVING 核心区别（面试高频）

  • 执行时机不同：WHERE 在分组前过滤原始数据；HAVING 在分组后过滤聚合结果

  • 过滤内容不同：WHERE 过滤行数据，不支持聚合函数；HAVING 过滤分组结果，支持聚合函数

  • 性能差异：能WHERE过滤绝不使用HAVING，提前缩减数据，避免无效分组计算

2.2 反例与优化

-- 低效：所有数据先分组，再过滤，分组数据量大
SELECT sex,COUNT(*) AS num FROM student GROUP BY sex HAVING age>20;

-- 高效：先WHERE过滤有效数据，再分组聚合，计算量大幅减少
SELECT sex,COUNT(*) AS num FROM student WHERE age>20 GROUP BY sex;

2.3 GROUP BY 核心优化方案

  • 建立分组字段+聚合字段联合索引，避免创建临时表、filesort文件排序

  • 禁止对大数量、高离散度字段分组，减少分组组数

  • 严格遵循SQL规范：GROUP BY查询中，非聚合字段必须为分组字段

三、排序分页优化：ORDER BY / LIMIT

ORDER BY极易触发文件排序（filesort），LIMIT深分页是线上经典慢查询场景。优化核心：利用索引排序、避免文件排序、优化分页偏移量。

3.1 ORDER BY 排序优化

MySQL排序分两种：索引排序（高效）、文件排序（低效）。索引字段有序，可直接返回结果无需二次排序。

  • 排序字段建立单列/联合索引，优先命中索引排序

  • 禁止混合排序（ASC+DESC混用），会直接失效索引排序

  • 避免对大量结果集排序，先过滤再排序

3.2 LIMIT 深分页痛点与优化

痛点：LIMIT 100000,10 会先扫描前10万条无效数据，再取后10条，偏移量越大速度越慢。

低效写法

SELECT * FROM student ORDER BY id LIMIT 100000,10;

高效优化：主键回溯分页（推荐）

-- 利用主键有序性，通过WHERE条件精准定位起始位置，避免大偏移扫描
SELECT * FROM student WHERE id > 100000 ORDER BY id LIMIT 10;

适用场景：有序自增主键分页，是企业解决深分页慢查询的主流方案。

四、联表查询优化：INNER / LEFT / RIGHT JOIN

多表联表是开发必备场景，JOIN查询90%的性能问题来自：无索引、驱动表选择错误、笛卡尔积、无效关联。

4.1 三种JOIN核心区别

  • INNER JOIN（内连接）：只返回两表匹配成功的数据，结果集最小、性能最优，优先使用

  • LEFT JOIN（左连接）：以左表为基准，左表所有数据+右表匹配数据，左表大表时性能较差

  • RIGHT JOIN（右连接）：以右表为基准，开发中极少使用，可改写为LEFT JOIN统一逻辑

4.2 JOIN 核心优化准则（重中之重）

1. 小表驱动大表

MySQLJOIN机制：小表做驱动表，大表做被驱动表，减少循环匹配次数。INNER JOIN会自动优化，LEFT JOIN需手动控制表顺序。

2. 关联字段必须建索引

联表ON后的关联字段，务必建立索引，无索引会触发全表匹配，大数据量下直接超时。

3. 禁止SELECT *

只查询业务所需字段，减少网络传输、内存占用，可配合覆盖索引进一步提速。

4. 避免JOIN后大量WHERE过滤

优先单表WHERE过滤缩小数据集，再进行联表，减少联表计算压力。

4.3 经典优劣对比

-- 低效：大表在前、查询所有字段、无前置过滤
SELECT * FROM student s LEFT JOIN class c ON s.class_id=c.id WHERE s.age>20;

-- 高效：先过滤再联表、按需查字段、小表驱动
SELECT s.name,c.class_name FROM (SELECT id,name,class_id FROM student WHERE age>20) s 
LEFT JOIN class c ON s.class_id = c.id;

五、子查询优化：相关子查询 & 非相关子查询

子查询分为非相关子查询（独立执行）和相关子查询（依赖外层查询），二者性能差距极大，是面试高频考点，也是新手主要性能坑点。

5.1 非相关子查询

定义：子查询独立执行，不依赖外层查询的任何字段，仅执行一次。

特点：执行效率高，MySQL可优化，可正常使用。

-- 非相关子查询：子查询仅执行一次
SELECT * FROM student WHERE class_id IN (SELECT id FROM class WHERE status=1);

5.2 相关子查询

定义：子查询引用了外层查询的字段，外层每扫描一行，子查询就要执行一次。

痛点：外层数据量越大，循环执行次数越多，时间复杂度指数级上升，超大表直接超时。

-- 低效相关子查询：逐行循环执行，性能极差
SELECT * FROM student s WHERE EXISTS (SELECT 1 FROM class c WHERE c.id=s.class_id AND c.status=1);

5.3 子查询终极优化方案

核心原则：杜绝大批量相关子查询，优先改写为JOIN联表查询

-- 高效优化：改写为LEFT JOIN/INNER JOIN，仅执行一次联表匹配
SELECT DISTINCT s.* FROM student s
INNER JOIN class c ON s.class_id = c.id AND c.status=1;

5.4 子查询使用规范

  • 小数据量场景可使用非相关子查询，代码简洁

  • 大数据量、列表查询，一律替换为JOIN联表

  • 禁止在WHERE条件中嵌套多层相关子查询

六、全文通用SQL优化总结（面试必背）

  1. 过滤优先：能WHERE过滤绝不后置处理，最大限度缩小数据集

  2. 索引优先：条件、排序、分组、关联字段优先建索引，杜绝索引失效

  3. 避免失效：索引列禁止函数、运算、隐式转换、前缀模糊

  4. 联表规范：小表驱大表，关联字段加索引，先过滤后联表

  5. 分页优化：深分页禁用大偏移量，使用主键回溯分页

  6. 子查询取舍：禁用大批量相关子查询，优先JOIN替代

  7. 杜绝冗余：禁止SELECT *，减少字段查询、网络IO与内存消耗

七、高频面试简答汇总

  • WHERE和HAVING区别：WHERE分组前过滤原始数据，不支持聚合；HAVING分组后过滤结果，支持聚合，优先用WHERE

  • ORDER BY慢原因：未命中索引排序，触发文件排序filesort，数据量大时性能极差

  • 深分页优化原理：规避大偏移量全表扫描，通过主键有序性精准定位起始数据

  • 相关子查询为什么慢：外层每一行都会触发一次子查询，循环执行次数随数据量递增

  • JOIN优化核心：小表驱动大表、关联字段建索引、先过滤后联表、减少结果集

本文总结

  • WHERE、GROUP BY、ORDER BY/LIMIT、JOIN 和子查询的优化核心，都是尽量减少扫描行数、排序开销和无效回表。
  • 能前置到 WHERE 的过滤不要放到 HAVING，能改写成 JOIN 的相关子查询不要保留逐行循环执行。
  • 深分页、跨字段 OR、函数运算和隐式类型转换，是线上慢查询里最常见的性能陷阱。
GYSTACK 文章文末广告 硅云云服务器活动 适合个人项目、轻量建站和出海业务部署。
后浪云移动端信息流广告 后浪云主机服务 适合长期部署、独立站和海外机房需求。