连续写了五篇 MySQL 表操作的教程今天这篇轮到基本查询的核心部分。前面我们聊过建表、字段类型和插入数据好多朋友已经能把数据塞进去但一到查询这里满屏查询语法在眼前飘真正落地时总是各种不顺手。其实 MySQL 表的基本查询真正要理解的东西并不多但它的威力恰恰体现在“看似基础”的写法上同样的结果写法不同在数据量上来之后可能是毫秒和分钟的差别。这篇文章我会结合这些年排查报表慢查询的经验把 SELECT 执行顺序、WHERE 条件与索引、GROUP BY 聚合、JOIN/UNION/子查询选择、排序与分页这几块完整过一遍。适合刚学完增删改、准备系统掌握查询的读者也适合写了几年 SQL 但想回头补一补底层逻辑的朋友。1. SELECT 执行顺序先搞清楚 SQL 是“怎么想”的我刚带新人那会儿经常看到有人写出这样的 SQL先查出一个临时结果再在外面包一层去筛选。写起来绕跑起来还慢。根子在于大家习惯按书写顺序理解 SQL以为 SELECT 先执行、WHERE 后执行。实际上服务端拿到一条 SQL 之后大脑完全不是按你打字的顺序运作的。1.1 真实执行顺序不是书写顺序一条完整的 SELECT 查询MySQL 内部的执行顺序大致是这样的顺序执行阶段对应子句作用1确定数据来源FROM / JOIN从哪张表取数先做表的关联生成中间结果集2行级过滤WHERE对中间结果集的每一行做条件判断过滤掉不满足的行3分组GROUP BY按指定列把行聚合成组4聚合函数计算聚合函数在组内执行 SUM、COUNT、AVG 等计算5组级过滤HAVING对分组聚合后的结果做条件过滤6投影/表达式计算SELECT计算目标列、别名、表达式生成最终结果字段7去重DISTINCT对最终结果去重8排序ORDER BY对结果排序9行数限制LIMIT / OFFSET截取指定行数完成分页很多人看这个表觉得“记不住也没关系”但下面这几个问题都会从这张表里找到答案为什么 WHERE 里不能用 SELECT 里定义的别名为什么 HAVING 能用聚合函数而 WHERE 不能为什么 GROUP BY 的排序结果不稳定理解了执行顺序这些坑都能少踩一大半。1.2 WHERE 与别名冲突一个经典报错场景我经常用下面这个例子给新人讲“别名在何时生效”-- 这段 SQL 会报错 SELECT id, price * quantity AS total FROM order_items WHERE total 500;报错信息大概是 Unknown column total in where clause。原因很简单WHERE 在执行的第二步而 SELECT 别名是在第六步才生成的。WHERE 阶段压根还没见到 total 这个字段自然无法引用。正确写法是把表达式在 WHERE 里重写一遍SELECT id, price * quantity AS total FROM order_items WHERE price * quantity 500;但 ORDER BY 就可以用别名因为排序发生在 SELECT 投影之后字段已经生成SELECT id, price * quantity AS total FROM order_items ORDER BY total DESC;这个差异不是 MySQL 故意为难人而是 SQL 标准的执行模型决定的。你在日常开发里如果经常写子查询过滤别名那就说明还没吃透这条顺序链路。1.3 执行顺序对窗口函数的影响MySQL 8.0 之后窗口函数越来越常用。窗口函数比如 ROW_NUMBER、RANK、SUM OVER是在 SELECT 阶段计算的所以 WHERE 阶段不能用窗口函数的结果过滤。如果你要“按窗口函数排名取前 N”必须套一层子查询SELECT * FROM ( SELECT user_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) t WHERE rn 1;这也是为什么我会把执行顺序放在查询系列第六篇的第一节来讲。后面所有分组、去重、分页、排序优化本质上都是围绕这张顺序表在转。先把这个顺序焊死在脑子里再往下看条件过滤和索引利用你会突然觉得很多东西都串起来了。2. WHERE 条件里的细节等于、IN、LIKE 和 NULL 的取舍WHERE 是查询里最朴素、最常写的子句但恰恰是最容易被优化器“悄悄改变计划”的地方。很多慢查询不是表太大而是 WHERE 条件写得太随意直接让索引失效。这一节我把日常工作中最典型的几个坑逐一拆开。2.1 NULL 判断千万别用 “ NULL”接触过几个转行来的朋友写筛选条件时第一反应是WHERE score NULL然后查出来的结果是空集也不报错就很困惑。原因在于 SQL 里 NULL 不是一个值而是一种“未知”状态。任何 NULL 参与的比较运算结果都不是 TRUE而是一个不确定结果所以score NULL永远不会命中行。正确写法只有两种-- 判断是 NULL SELECT * FROM t WHERE score IS NULL; -- 判断非 NULL SELECT * FROM t WHERE score IS NOT NULL;我见过最隐蔽的 NULL 问题出现在复合条件里比如统计订单金额时WHERE discount 0 OR discount IS NULL和WHERE discount 0的结果可能差很多。前者把没有折扣信息的单子算进来后者只算明确无折扣的单子。业务含义完全不同写之前一定要确认清楚“你没填”和“你填了0”是两种状态。2.2 IN、BETWEEN 与隐式类型转换IN 和 BETWEEN 是查询高频写法但有几个细节值得注意。首先是类型问题。假设手机号存在 varchar 字段里你用数字去匹配SELECT * FROM users WHERE phone 13800001111;MySQL 会尝试把字段类型转换成数值再比较这个隐式转换会让 phone 字段上的索引失效。正确写法是带上引号SELECT * FROM users WHERE phone 13800001111;BETWEEN 是包含边界的WHERE age BETWEEN 18 AND 30等价于age 18 AND age 30这和许多编程语言里“左闭右开”的习惯不太一样。如果你要写“大于等于18且小于30”千万别用 BETWEEN 18 AND 29 这种取巧方式直接写成age 18 AND age 30更清晰。2.3 LIKE 前缀匹配与模糊搜索LIKE 可能是索引优化里最常聊的话题。三句话概括LIKE abc%前缀固定索引可以从根节点往下定位一般能走索引。LIKE %abc后缀匹配索引最左侧部分未知通常无法走索引。LIKE %abc%前后都模糊基本只能全表扫描。我处理过一个几千万行大表的搜索需求业务方想在商品名里模糊找关键词直接WHERE name LIKE %蓝牙%扫一次全表要几十秒。后来把方案改成两段式先按首字拼音或前缀建冗余字段做前缀过滤再用 LIKE 缩小结果集更极端的场景直接引入全文索引或者搜索引擎。基本查询虽然支持 LIKE但你要明白它在底层是“怎么跑的”才能知道什么时候改表结构、什么时候上外部组件。2.4 覆盖索引与回表大表查询的关键收益搜索热词里一直有“辅助索引如何避免回表”这其实和 WHERE 条件密切相关。InnoDB 的辅助索引叶子节点存的是索引列 主键值如果你查询的字段恰好都在辅助索引里MySQL 读完索引就能直接返回结果不需要再拿着主键回主表取其他列。比如有复合索引idx(user_id, order_time)下面这条查询SELECT user_id, order_time FROM orders WHERE user_id 100;索引覆盖整个过程都在辅助索引这颗 B 树上完成不需要回表。但如果 SELECT 后面加上order_amount而order_amount不在索引里MySQL 就得对每个命中的索引条目回表取一次数据。在数据量小的时候感受不到到了千万级回表次数直接变成磁盘随机读次数性能差距可能是几个数量级。所以写 WHERE 之前先看看 SELECT 出来的是哪些列再判断这条查询到底是在“索引里挑数据”还是“索引定位后再回表搬数据”这个思考习惯能帮你提前发现很多慢查询隐患。3. GROUP BY 聚合与 HAVING 过滤分组查询不是“先查出来再分组”GROUP BY 看起来好像很简单把相同值的行放一起然后对每组做聚合。但它踩坑的点非常集中而且一旦出错往往是“逻辑错了但 SQL 没报错”新手很难发现自己写错了。3.1 COUNT(*) 和 COUNT(字段) 不是一回事写聚合查询时我见过太多人混用 COUNT。两者差别就一句COUNT(*) 统计的是行数包括 NULL 值行COUNT(字段) 统计的是该字段非 NULL 的行数。-- 统计总行数 SELECT COUNT(*) FROM orders; -- 统计有优惠券编号的订单数可能比上面小 SELECT COUNT(coupon_id) FROM orders;如果业务上要统计“有优惠信息的订单”第二个写法才正确。COUNT(DISTINCT 字段) 是去重统计比如统计有多少个不同用户下单SELECT COUNT(DISTINCT user_id) FROM orders;这三个函数在热词里经常一起出现真要分清适用场景最简单的办法是拿一条包含 NULL 的记录去试一遍结果你立刻能感受到差异。3.2 ONLY_FULL_GROUP_BY 模式下的列选择限制MySQL 5.7 之后默认开启ONLY_FULL_GROUP_BYSQL 模式在这个模式下SELECT 后面的非聚合列必须出现在 GROUP BY 子句里。我见过一个真实项目有人想查每个部门工资最高的人写成了SELECT department_id, employee_name, MAX(salary) FROM employees GROUP BY department_id;在关闭 ONLY_FULL_GROUP_BY 的旧环境里能跑出来而且返回的 employee_name 是“不确定的某个人”。到了默认开启的 MySQL 5.7 上直接报错。其实数据库是对的同一个 department_id 可能有多个人MAX(salary) 返回的是一个聚合值那 employee_name 到底对应哪一行数据库没有义务帮你做这个决定所以直接拒绝执行。这类需求的正解是用子查询或窗口函数先查每个部门的最高工资再回原表匹配员工姓名或者用 ROW_NUMBER() 按部门分组排序后取第一条。没有第三种“看似聪明、实则拼运气”的写法。3.3 先 WHERE 再 GROUP BY还是先 GROUP BY 再 HAVING执行顺序已经告诉我们WHERE 发生在分组之前HAVING 发生在分组之后。这意味着能用 WHERE 过滤的数据尽量在分组前干掉因为分组处理的行数越少越快。举个统计场景要查 2024 年入职员工里平均工资大于 10000 的部门。错误但常见的写法是把所有部门先分组再在 HAVING 里过滤入职年份SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) 10000 AND MIN(hire_date) 2024-01-01;这个写法不是不行而是让 MySQL 必须先对所有部门做聚合再判断年份条件白白浪费大量计算。正确姿势SELECT department_id, AVG(salary) FROM employees WHERE hire_date 2024-01-01 GROUP BY department_id HAVING AVG(salary) 10000;WHERE 提前把 2024 年之前的数据扔掉了参与分组和聚合的行数大幅减少。一句话原则能前移的过滤条件绝不放后面尤其 HAVING 只负责聚合后的组级条件。3.4 每组最新一条的两种写法订单系统、日志系统里几乎天天遇到“取每个用户最近一笔订单”这种需求。最直观的子查询写法是SELECT o.* FROM orders o JOIN ( SELECT user_id, MAX(order_time) AS max_time FROM orders GROUP BY user_id ) t ON o.user_id t.user_id AND o.order_time t.max_time;但如果同一个用户在同一个秒数下了多单这个写法会返回多行还需要额外加唯一性约束。另一种更稳妥的写法是用窗口函数SELECT * FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders o ) t WHERE rn 1;窗口函数把“组内排序”的责任交给数据库逻辑上更贴近业务意图。MySQL 8.0 之前的版本没有窗口函数所以子查询 JOIN 仍然是兼容方案但 8.0 之后我强烈推荐优先用窗口函数代码可读性强也少踩“时间字段重复”的坑。4. JOIN、UNION 与子查询跨表合并数据的选型逻辑搜索热词里有“跨表合并”恰恰是查询里最容易绕晕的部分。我经常在答疑时收到类似问题“两个表的数据要合在一起用 JOIN 还是 UNION为什么我用了 JOIN 结果行数暴涨”这一节把几个主要合并方式的边界理清楚。4.1 JOIN 和 UNION 的本质区别很多人把 JOIN 和 UNION 混为一谈因为感觉“都是把表连起来”。其实方向完全不同操作方向行数影响列数影响典型场景JOIN横向扩展可能增多可能不变增加另一张表的列订单表关联用户表取用户姓名UNION / UNION ALL纵向扩展增加行数列数不变要求列结构一致合并一月份和二月份的销售记录如果你要把两个结构相同的查询结果“拼成一张长表”用 UNION。如果你要把两张不同结构的表“按某个关联键放在一行里”用 JOIN。这个方向感先对了后面写起来就不会别扭。4.2 LEFT JOIN 里 ON 和 WHERE 的经典陷阱我最常拿出来讲的项目经验是 LEFT JOIN 中 ON 和 WHERE 位置不同导致结果完全错误。看这条查询SELECT u.id, u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.status 1;表面意思是“查所有用户及其状态为 1 的订单”。但实际执行时WHERE 条件在 JOIN 完成之后对整行做过滤如果一个用户只有状态为 0 的订单这个用户的订单行会被过滤掉如果这个用户没有任何订单LEFT JOIN 后右表是 NULLWHEREo.status 1对 NULL 判断不为真整行也被过滤掉。最终结果和 INNER JOIN 没区别Left Join 的左表保底语义完全丢失。想要保留全部用户把过滤条件挪到 ON 里SELECT u.id, u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.status 1 ORDER BY u.id;这个差异我在实际报表排查里至少遇到五六次每次的现场都是“数据好像少了一批又好像没错”非常迷惑。记住LEFT JOIN 时右表的过滤条件写在 ON 里才不改变连接的主表语义。4.3 UNION 与 UNION ALL 的去重开销UNION 默认执行去重而去重这个动作背后要么是排序、要么是哈希操作有额外代价。UNION ALL 直接把两段结果拼接不做任何去重。如果两段查询结果不可能重复比如按日期分区、按业务类型拆分的表果断用 UNION ALL。就算可能重复也要先估算重复量重复率极低用 UNION ALL 后在应用层做一次去重往往比数据库层排序去重更快。我遇到过一个跨年报表两段子查询按年份分开年份之间主键根本不可能相同业务却默认写了 UNION白白多跑了好几次排序。改成 UNION ALL 后查询时间直接掉到原来的三分之一。4.4 IN、EXISTS 与 JOIN别盲信“IN 一定慢”早年网上有个传播很广的说法子查询用 IN 慢应该改成 EXISTS。这个结论在特定版本、特定数据分布下成立但 MySQL 优化器早就做了很多改写不能一概而论。我自己的经验是分场景判断子查询结果集非常小比如就几十个 id用 IN 直观且足够快。外层表大、子查询小EXISTS 能让优化器先查外层表每条记录时做快速判断。两张表都大且关联查询需要返回大量字段JOIN 往往更清晰必要的时候配合 DISTINCT 或去重逻辑。真正的坑不是用哪个而是子查询里忘记加过滤条件导致子句返回了百万级数据集不管 IN 还是 EXISTS 都会变成灾难。写跨表查询前先单独跑一遍子查询看返回行数是最简单却最有效的习惯。4.5 大表 JOIN 的现代优化哈希连接与预过滤MySQL 8.0.18 之后引入了哈希连接等值关联的场景下优化器可能自动选择 hash join不再像以前那样只依赖索引嵌套循环。但这不代表你就可以在大表上随意 JOIN。搜索热词里提到的“哈希表”“几千万行大表”落到实操上还是那几句能提前过滤就提前过滤能在子查询里缩小范围就缩小范围。我的一个常见做法是两个几千万行的大表做 JOIN 前先各自执行一次 WHERE 过滤把一个月前的数据、无效状态数据扔掉再让两个小结果集做关联SELECT * FROM ( SELECT * FROM orders WHERE status 1 AND create_time 2025-01-01 ) a JOIN ( SELECT * FROM users WHERE is_deleted 0 ) b ON a.user_id b.id;虽然 MySQL 优化器本身也会尽量下推条件但这种显式书写在排查问题时更直观也能避免因为统计信息失真导致优化器误判。查询先是给人读的其次才是给机器优化的。5. 排序和分页在大表上的实战深分页为什么慢怎么优化基本查询里“查出来再排序、再分页”看似无脑实际是慢查询重灾区。尤其是搜索热词里反复出现的“mysql排序”和“几千万行大表”叠加在一起时你会发现绝大多数问题都出在ORDER BY配合LIMIT写得太随意。5.1 ORDER BY 背后的 filesort 机制当 MySQL 无法用索引直接满足排序时就会触发 filesort。注意“filesort”不一定表示用了磁盘文件它是“排序操作”的统称内存里排得下就在内存排排不下才写临时文件。判断方式很简单执行 EXPLAINExtra 列出现 Using filesort 就说明排序没走索引。举个例子联合索引idx(user_id, order_time)-- 能利用索引排序等值条件在前排序字段在后 SELECT * FROM orders WHERE user_id 100 ORDER BY order_time DESC; -- 无法利用索引排序排序字段不是索引最左前缀 SELECT * FROM orders WHERE order_time 2025-01-01 ORDER BY user_id;索引是 B 树结构只有排序方向和索引方向一致才能“边读边有序”。如果你有几百行的小表filesort 根本无所谓到了千万行一次 filesort 就可能拖垮接口。设计索引时心里要有一个顺口溜等值条件列放前面排序列紧跟其后最后再加需要查询覆盖的列。5.2 深分页LIMIT 越深越慢“深分页”是基本查询里最容易被低估的问题。写过LIMIT 500000, 20的朋友应该都有体会第一页秒开翻到几十万页的时候卡到怀疑人生。原因是 MySQL 必须先按 ORDER BY 找到排好序的前 500020 行然后丢弃前 500000 行只返回最后 20 行。这些被丢弃的行依然要真实地取出来、参与排序OFFSET 越大白白消耗的资源越多。我接手过一个报表接口线上用户翻到第 200 页时响应时间飙到 8 秒。用 EXPLAIN 一看扫描行数已经接近两百万。这种问题不是加索引能解决的索引帮助排序但排序后的前 N 行它依然要一行行跳过去。5.3 延迟关联先查主键再回原表取列一个通用优化手段叫延迟关联想法很简单排序分页阶段不要拖上整行大字段先在索引里把主键查出来跳过大量无关行之后再拿主键回原表取完整数据。SELECT t1.* FROM orders t1 JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY id LIMIT 100000, 20 ) t2 ON t1.id t2.id;子查询里 SELECT 的只有主键排序和跳过行的过程在索引上进行数据体积小很多。等到确定最终要返回哪 20 行主键再关联回原表取整行瞬间把“搬运两百万行”变成“搬运 20 行”响应速度提升非常明显。这个写法尤其适合表格里有很多冗余字段、大字段备注、JSON、长文本的场景。如果 SELECT 列本身就在覆盖索引里连延迟关联都不需要直接返回即可。5.4 游标分页放弃 OFFSET用 WHERE 定位延迟关联可以降低单次查询成本但依旧没解决“每次都要跳很多行”的本质。更彻底的方案是游标分页也就是不依赖 OFFSET而是记住上一页最后一条数据的位置下一页直接用 WHERE 把范围往后推-- 第一页 SELECT * FROM orders ORDER BY id LIMIT 20; -- 第二页以上一页最后一条 id 作为游标 SELECT * FROM orders WHERE id 10020 ORDER BY id LIMIT 20;这里有个前提排序字段必须唯一。如果只按order_time排序而同一时间有多条记录分页边界就可能出现重复或遗漏。稳妥做法是排序字段加主键组合游标SELECT * FROM orders WHERE (order_time, id) (2025-06-01 10:00:00, 10020) ORDER BY order_time, id LIMIT 20;游标分页的局限是用户不能随意跳页只能一页一页往下翻。恰好绝大多数“翻页报表”场景就是连续翻页这个方案几乎完美。我前面提到的那次 8 秒优化最终就是改成了游标分页接口稳定在几十毫秒。5.5 排序设计不是越多越好聊到最后要泼一盆冷水不是所有排序都必须走索引也不是所有常用查询字段都要建索引。索引本身占空间、拖慢写入建多了是负担。我的经验是三个条件同时满足才专门为 ORDER BY 建索引查询频率高、数据量大、排序列有清晰的业务含义。如果只是偶尔跑一次统计报表数据量又不大filesort 反而更省事因为你不用为一次低频查询付出持续的写入代价。排序和分页是基本查询里最接近“系统设计”的部分它考验的不是语法熟不熟练而是你能不能从磁盘和索引的角度去想象数据是怎么流动的。多拿真实大表做几次 EXPLAIN比背一百条优化口诀都有用。