MySQL单表查询全攻略：从执行顺序到性能优化

写单表查询这件事我太有发言权了。我带团队这几年每次给新人做代码评审翻来覆去看到的坑十有八九都出在看起来最简单的 SELECT 上。很多人觉得单表查询不就是“SELECT 字段 FROM 表 WHERE 条件”吗可一旦数据量上了千万或者 WHERE 条件里带个隐式类型转换、LIKE 前后通配符、GROUP BY 后面多了个非聚合字段性能直接断崖式下滑结果还可能悄悄出错。所以这篇围绕“第4章 MySQL 数据操纵语句——单表查询”展开的文章我打算把 SELECT 的完整执行链路、WHERE 过滤规则、排序分页、聚合统计全部串起来把我这些年排查慢查询、做 SQL 规范时积累的关键点一次讲透。不管是刚接触 SQL 的新人还是已经写了一阵子、想回头夯实基础的朋友这篇文章都能让你少踩几个坑。1. 一条 SELECT 的执行顺序决定你写 SQL 的姿势1.1 书写顺序不等于执行顺序几乎所有教材都会在最开始强调一件事SQL 的书写顺序和执行顺序不一样。但真正能理解这句话分量的人不多。我举一个最常见的例子SELECT emp_name, salary * 12 AS annual_salary FROM t_employee WHERE salary 5000 ORDER BY annual_salary LIMIT 5;这条 SQL 看起来平平无奇但里面藏着一个经典问题为什么 WHERE 后面不能写annual_salary 60000而 ORDER BY 后面却可以写annual_salary答案就在于执行顺序。MySQL 实际执行这条语句时内部大致是按这个顺序处理的FROM先确定数据来自哪张表WHERE对 FROM 阶段取出的每一行做条件过滤不满足条件的直接剔除GROUP BY如果语句里有分组就在过滤后的结果上分组HAVING对分组后的组做条件过滤SELECT才真正计算要输出的字段、表达式和别名ORDER BY对 SELECT 阶段产出的结果排序LIMIT最后做行数限制。所以annual_salary这个别名是在第 5 步才“诞生”的。WHERE 在第 2 步执行那时候别名还不存在你写了就等于引用一个不存在的字段ORDER BY 在第 6 步执行别名早就生成好了当然可以随便用。注意这个顺序是最核心的逻辑顺序它会直接帮你判断一条 SQL 里哪些地方能用别名、哪些地方不能用。我见过太多新人因为搞不清这个把 WHERE 里的别名条件改成在 HAVING 里写结果一顿折腾还不如老实重复写一遍表达式。1.2 HAVING 引用别名是个例外前面说的执行顺序是标准逻辑但 MySQL 有一个“额外通融”的地方HAVING 子句在逻辑上位于 SELECT 之前可 MySQL 却允许 HAVING 引用 SELECT 中的别名。比如下面这句在很多版本的 MySQL 里是可以正常执行的SELECT dept_id, COUNT(*) AS emp_cnt FROM t_employee GROUP BY dept_id HAVING emp_cnt 3;注意我说的是“允许引用”不代表“建议依赖”。因为别的数据库比如某些严格模式的 SQL 标准实现可能会直接报错而且一旦你的查询被改写成嵌套子查询、视图合并等情况别名的作用范围会变得不可控。我的个人习惯是HAVING 里宁可把 COUNT(*) 再写一遍也不去赌 MySQL 的宽容行为。求稳是写 SQL 的第一原则。1.3 别名的第二个坑和真实列名冲突如果说执行顺序是第一个坑那别名撞列名就是第二个。假设表里真的有一列叫emp_name你写SELECT salary AS emp_name FROM t_employee ORDER BY emp_name;这条语句不会报错但结果可能让你一脸懵。因为 ORDER BY 解析时优先认列名还是别名在 MySQL 的规则里存在优先级问题如果别名和列名相同MySQL 倾向于按列名处理。也就是说ORDER BY emp_name很可能会按照原始emp_name列排序而不是你定义的“salary 的别名”。这种歧义在代码评审里特别容易被忽略因为单条语句测试时数据量小看不出问题一旦数据量变大、排序结果出现偏差排查起来极其痛苦。所以我的建议很简单别名不要和任何真实列名重名且别名的语义要一眼能看懂比如annual_salary、emp_cnt这种。1.4 别急着说单表很简单单表查询看似只涉及一张表但实际业务场景里一点都不简单。举个例子某张订单表可能有几千万行你需要统计“最近 30 天的每日订单金额”还要按金额区间分档。这些需求虽然还是在单表上做但会涉及日期函数、CASE WHEN、分组、排序、分页的组合。任何一个环节的写法不对都可能让查询从毫秒级变成分钟级。所以下面几个章节我会把 WHERE、ORDER BY、GROUP BY 这些核心环节一个个拆开讲每个环节都有真实坑点。2. WHERE 条件过滤一张表也能挖出不少坑2.1 AND 和 OR 的优先级真的会改变结果WHERE 子句最基础的坑就是 AND 与 OR 的优先级混用。SQL 里 AND 的优先级高于 OR也就是说表达式会先计算所有 AND 两边的内容再计算 OR 两边的内容。看这个例子SELECT emp_name, gender, age, dept_id FROM t_employee WHERE gender M OR age 30 AND dept_id 3;很多新人写这条语句时心里想的是“找出男性或者年龄大于 30 的人并且他们在部门 3 ”。但 MySQL 实际解读的是WHERE gender M OR (age 30 AND dept_id 3)也就是“找出所有男性加上部门 3 里年龄大于 30 的人”。这两者的含义天差地别。我当年在线上查一个人员名单时就因为少写了一对括号筛出来的结果多了好几倍最后逐条核对才发现是优先级问题。重要凡是 AND 和 OR 混合出现必须用括号把 OR 两侧的逻辑明确括起来。这不仅是为了正确性也是为了让下一个看你代码的人不产生误解。宁可多写括号也不要给任何歧义留空间。2.2 隐式类型转换索引失效的头号原因这是我在慢查询排查里遇到频率最高的“隐形杀手”。比如emp_no字段定义的是 VARCHAR但 WHERE 里写WHERE emp_no 123MySQL 在比较时会把字符类型的列转换成数字相当于给每一行的emp_no都套了一层转换函数再比较。一旦索引列被函数包裹B 树索引的快速定位就失效了查询只能退化成全表扫描。正确写法是让常量类型和字段类型保持一致WHERE emp_no 123同样的道理也出现在日期字段上。比如hire_date是 DATE 类型你写WHERE hire_date 2024-5-1和标准写法WHERE hire_date 2024-05-01在部分场景下可能触发隐式转换。所以一个非常实用的习惯日期永远写成 YYYY-MM-DD 的标准格式字符串字段比较永远带引号。2.3 LIKE 条件最左匹配是底线LIKE 模糊查询也是单表查询里高频出现的问题。如果你写WHERE emp_name LIKE %伟%MySQL 为了匹配字符串中间和末尾的通配符只能把整张表扫一遍。如果把通配符放在字符串末尾写成LIKE 张%优化器就有机会利用索引的最左匹配规则直接用索引定位到“张”开头的区间。所以业务里能用前缀匹配的绝不写前后双通配符。如果业务确实需要“中间包含”的搜索比如搜索商品名称、文章标题那就别硬扛 LIKE应该考虑全文索引或者专门的搜索组件。单表查询的优化思路永远是“先让数据库能用上索引再谈其他技巧”。2.4 NULL 的判断等号从来都不是答案SQL 里有个反直觉的规则NULL不等于NULL。当你写WHERE salary NULL时这条条件永远不会成立因为 NULL 代表“未知”未知和未知做等值比较结果还是“未知”而 WHERE 只会留下结果为 TRUE 的行。想判断空值必须写IS NULL或者IS NOT NULL。这个坑让人印象深刻的点在于它不会像语法错误那样直接报错而是“悄悄”让结果少了几行。尤其是统计报表里如果可空字段参与条件过滤一不留神就会漏掉一批数据而且非常难察觉。我的习惯是建表时凡是业务上不允许为空的字段一律加 NOT NULL允许为空的字段在查询里凡涉及空值判断全部显式写IS NULL或IS NOT NULL绝不偷懒。3. ORDER BY 排序与 LIMIT 分页性能的分水岭3.1 排序为什么这么慢很多人对 ORDER BY 的理解就是“最后排个序”但不知道排序可能是整个查询中最贵的操作之一。如果 ORDER BY 的字段恰好和某个索引的顺序一致MySQL 可以直接按索引的有序链表扫描不需要额外排序这叫利用索引的有序性。如果字段没有索引MySQL 就只能把查询结果集中到内存或临时文件里做 filesort。数据量一大filesort 可能要把数据分成多块、排序后再合并性能非常差。我看到过一份慢查询日志有一条 SQL 本身只过滤出几十行数据结果因为 ORDER BY 一个无索引字段执行时间硬生生从几毫秒变成几秒。所以写完一条带排序的查询第一反应应该是看 EXPLAIN 结果里有没有 “Using filesort”。有的话要么调整索引要么优化排序字段。3.2 深分页LIMIT 偏移量越大越慢分页是单表查询里另一个性能重灾区。很多人写分页都是这个套路SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;这条语句的逻辑是先按主键顺序找到第 1000000 行之后的 20 行。但 MySQL 的实际执行方式是把前 1000020 行全部取出来然后丢掉前面的 1000000 行只留下最后 20 行。偏移量越大需要处理的无用行越多查询自然越来越慢。推荐用“键集分页”的方式替代传统 LIMIT 偏移分页。核心思想是记住上一页最后一条记录的位置下一页从这个位置往后取-- 第一页 SELECT * FROM t_order WHERE id 0 ORDER BY id LIMIT 20; -- 第二页假设上一页最后一条记录的 id 100 SELECT * FROM t_order WHERE id 100 ORDER BY id LIMIT 20;这种写法里数据库可以直接通过主键索引定位到 id 100 的区间然后取前 20 行偏移量再大也不怕。注意这种方法要求排序字段唯一稳定通常用主键 id 最稳妥如果业务上必须按其他字段排序可以改成ORDER BY sort_field, id并把上一页的(sort_field, id)同时带进 WHERE 条件。提示深分页还有一种常见解法是“子查询延迟关联”。先只查主键 id分页后再和原表关联取详情避免排序时携带大量宽字段。但相比起来键集分页更简单直接推荐优先使用。3.3 排序字段与 WHERE 条件的配合还有一个值得注意的点当一条 SQL 同时有 WHERE 和 ORDER BY 时索引的选择会变得复杂。优化器一般先用 WHERE 条件选择一个索引把行过滤出来然后按 ORDER BY 字段排序。如果 WHERE 用的索引和 ORDER BY 的字段不是同一个索引就可能出现“先用索引过滤出一批数据再额外 filesort”的情况。最理想的情况是设计一个联合索引同时覆盖 WHERE 和 ORDER BY。比如查询条件是dept_id 3排序要求是salary DESC那么联合索引(dept_id, salary)就能让数据库先按 dept_id 定位到部门 3 的区间然后在这个区间里直接按 salary 的有序性扫描完全不需要 filesort。建索引的时候多想想“哪些查询会一起来”收益远大于单纯给每个字段各建一个单列索引。4. 聚合与分组统计GROUP BY 和 HAVING 的正确用法4.1 COUNT 的三个细节聚合函数里COUNT 是最容易被用错的一个。三个写法要分清COUNT(*)统计结果集的行数包括 NULL 行COUNT(字段)统计该字段非 NULL 的行数COUNT(DISTINCT 字段)统计该字段去重后的非 NULL 值个数。比如统计每个部门在职员工数很多人用COUNT(emp_no)如果 emp_no 有唯一约束结果没问题但如果统计的是一个允许 NULL 的字段比如COUNT(remark)那么结果里那些 NULL 行就会被悄悄忽略。再比如统计部门数量如果用COUNT(dept_id)恰好有个员工的 dept_id 是 NULL这个部门就被漏掉了。还有一个常见误传COUNT(1) 比 COUNT(*) 快。在主流版本里两者执行计划基本一致你不需要为了性能纠结。真正要关注的是语义你到底想数行还是数某个字段的非空值。4.2 ONLY_FULL_GROUP_BY 规则别想着绕过去MySQL 5.7 之后默认开启了ONLY_FULL_GROUP_BYSQL 模式。它规定SELECT 列表里出现的非聚合列必须出现在 GROUP BY 子句中。比如下面这条SELECT dept_id, emp_name, AVG(salary) FROM t_employee GROUP BY dept_id;在开启了ONLY_FULL_GROUP_BY的 MySQL 里这条语句直接报错。原因很清晰按 dept_id 分组后每组里通常有多行emp_name取哪一行是没有明确定义的。如果数据库强行返回一个值那就是随机结果无法保证确定性。有些人在旧项目里习惯了这种不规范写法升级到 5.7 后一脸懵。解决方案不是去关闭 SQL 模式而是把查询写规范要么把emp_name也加入 GROUP BY要么用聚合函数包住它比如MAX(emp_name)、GROUP_CONCAT(emp_name)。总之任何非聚合字段在分组查询里都必须“有明确归属”。4.3 WHERE 和 HAVING 的分工不只是位置不同WHERE 和 HAVING 都能写过滤条件但执行时机完全不一样。WHERE 在分组之前过滤行HAVING 在分组之后过滤组。所以一个很直接的性能原则能在 WHERE 里过滤掉的绝对不要留给 HAVING。比如要统计“每个部门里在职且薪资大于等于 3000 的员工人数”并且只要人数大于 3 的部门。正确的写法是先 WHERE 过滤掉离职员工和低薪资员工把分组前的数据量尽量缩小SELECT dept_id, COUNT(*) AS emp_cnt FROM t_employee WHERE status 1 AND salary 3000 GROUP BY dept_id HAVING COUNT(*) 3 ORDER BY emp_cnt DESC;如果把status 1和salary 3000写到 HAVING 里MySQL 就得先按 dept_id 把所有行分组再在组内做过滤白白多处理大量数据。两者结果可能一样但消耗完全是两个量级。注意HAVING 里可以写聚合函数条件比如HAVING COUNT(*) 3、HAVING MAX(salary) 20000WHERE 里不能写聚合函数因为聚合发生在 WHERE 之后。5. 一套完整实操从建表到结果核对5.1 建一张测试表把理论落在地上说了这么多理论不如直接建表演练一把。我用一张虚拟的员工表t_employee来演示。表结构是这样的CREATE TABLE t_employee ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, emp_no VARCHAR(20) NOT NULL, emp_name VARCHAR(50) NOT NULL, gender CHAR(1) DEFAULT M, dept_id INT NOT NULL, salary DECIMAL(10,2) NOT NULL DEFAULT 0.00, hire_date DATE NOT NULL, status TINYINT NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uk_emp_no (emp_no), KEY idx_dept_salary (dept_id, salary) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入几行测试数据INSERT INTO t_employee (emp_no, emp_name, gender, dept_id, salary, hire_date, status) VALUES (E001, 张三, M, 1, 12000.00, 2022-03-01, 1), (E002, 李四, M, 1, 9000.00, 2021-07-15, 1), (E003, 王五, F, 2, 15000.00, 2020-01-10, 1), (E004, 赵六, M, 2, 8000.00, 2023-05-20, 1), (E005, 孙七, F, 3, 20000.00, 2019-11-11, 1), (E006, 周八, M, 3, 7500.00, 2024-02-01, 1), (E007, 吴九, F, 1, 11000.00, 2023-09-05, 0), (E008, 郑十, M, 2, 9500.00, 2022-12-12, 1);注意idx_dept_salary (dept_id, salary)这个联合索引后面好几个查询都会用到。建表时建这个索引就是为了覆盖“按部门过滤再按薪资排序”这类高频查询。5.2 从基础查询到统计查询逐个跑通第一步最简单的部门过滤加排序SELECT emp_no, emp_name, salary FROM t_employee WHERE dept_id 2 ORDER BY salary DESC LIMIT 3;因为联合索引(dept_id, salary)正好能同时满足 WHERE 和 ORDER BYMySQL 会直接定位到部门 2 的索引区间然后按 salary 降序取前三行。看到 EXPLAIN 的 Extra 列里没有 “Using filesort”就可以放心了。第二步算年薪并排序SELECT emp_name, salary * 12 AS annual_salary FROM t_employee WHERE status 1 ORDER BY annual_salary DESC LIMIT 5;这里 ORDER BY 用了别名 annual_salary完全没问题因为别名在 SELECT 阶段已经生成。如果非要在 WHERE 里用 annual_salary那就会直接报错。第三步按部门统计在职人数只保留人数不少于 2 的部门SELECT dept_id, COUNT(*) AS emp_cnt FROM t_employee WHERE status 1 GROUP BY dept_id HAVING COUNT(*) 2 ORDER BY emp_cnt DESC;先 WHERE 把离职的周八过滤掉剩下 7 人按部门分组。部门 1 有张三、李四 2 人部门 2 有王五、赵六、郑十 3 人部门 3 有孙七、周八中的周八被过滤了所以只有孙七 1 人。最终 HAVING 过滤后返回部门 2 和部门 1按人数倒序。第四步给薪资分档统计SELECT CASE WHEN salary 10000 THEN 低薪 WHEN salary 15000 THEN 中薪 ELSE 高薪 END AS salary_level, COUNT(*) AS cnt FROM t_employee WHERE status 1 GROUP BY salary_level;这里有个细节GROUP BY 用的是 SELECT 里的别名 salary_level。MySQL 允许在 GROUP BY 后面引用别名这不算违规。结果会输出三档人数非常直观。如果觉得 CASE WHEN 写在 GROUP BY 太啰嗦也可以用子查询先打标签再分组不过单表场景下直接写完全够用。5.3 常见问题速查表建议直接收藏我把前面几节提到的问题汇总成一张速查表方便你排查时对照问题现象最常见原因解决思路查询结果明显偏多WHERE 里 OR 和 AND 混用优先级没理清用括号明确组合逻辑字符串字段查询突然变慢等值比较时用了数字常量触发隐式类型转换常量改成字符串保持类型一致LIKE 搜索很慢通配符放在字符串头部无法用索引改成前缀匹配或改用全文索引翻页越翻越慢LIMIT 偏移量过大MySQL 先取再用改成键集分页利用主键定位分组查询报 ONLY_FULL_GROUP_BY 错误SELECT 里写了非聚合字段且不在 GROUP BY 中把字段加入 GROUP BY或用聚合函数包裹明明有空值却查不到数据写了 NULL而不是IS NULL空值判断一律用 IS NULL / IS NOT NULL排序字段很多结果出现 filesortORDER BY 字段和索引顺序不匹配设计联合索引覆盖 WHERE ORDER BY统计结果不准比预期少COUNT(字段) 忽略了 NULL 行确认统计语义按需要选 COUNT(*) 或 COUNT(字段)这张表里的每一行都是真实线上环境里出现过的案例。你可以把它贴在工位旁边也可以在代码评审时拿来当 checklist 用。最后分享一个我个人的习惯不管写多简单的单表查询提交之前我都会顺手跑一遍 EXPLAIN重点看type和Extra两列。type出现ALL说明在走全表扫描Extra出现Using filesort说明排序没吃到索引红利。这两个信号一旦出现哪怕当前数据量不大我也会重新检查索引和 SQL 写法。数据量小时看不出问题量大了再回头改代价会翻好几倍。写单表查询被很多人低估但它恰恰是所有 SQL 性能问题的起点。你要是从一开始就养成了 EXPLAIN 的习惯后面做复杂查询、优化线上问题时会省下大把时间。