上一篇我们刚把表结构建好、数据灌进去这篇就进入日常打交道最多的环节数据查询操作。不管你是写报表、做接口还是排查线上问题写SQL拿到想要的结果集都是绕不开的基本功。很多人对查询的印象停留在“select * from 表 where 条件”但真到实战里排序、分页、去重、分组统计、多表关联每一样都有藏在底层的套路和坑。这篇我会直接从查询的内在逻辑讲起再带你把条件、排序、聚合、JOIN、子查询这些常见操作过一遍最后用一套能直接抄作业的查询模板收尾。这篇内容适合三类人刚接触SQL、只会复制粘贴查询语句的新手写过不少查询但总感觉性能不稳定的初级开发以及准备面试、想系统捋一遍MySQL查询知识点的同学。我尽量少讲空洞的理论多给可以直接上手的写法和你踩几次才能记住的细节。1. 查询的内在逻辑先搞清楚SQL执行顺序1.1 SELECT的基本框架与执行顺序刚开始学查询时我一度以为SQL是照书写顺序执行的先select再from再where。等到被几个诡异的报错教育过才发现真实执行顺序完全不是这么回事。一条完整的查询语句完整执行链路是这样FROM - ON - JOIN - WHERE - GROUP BY - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT你可以把这个过程理解成厨房做饭的流水线先确定食材从哪里来FROM再清洗选菜ON和WHERE然后按菜系分类GROUP BY分类后再做二次筛选HAVING最后才是决定这道菜端出来长什么样SELECT列、排序、限量。这个顺序解释了新手最常见的两个疑问。第一为什么WHERE里不能用SELECT定义的别名因为WHERE执行时SELECT还没执行到别名自然不生效。第二为什么ORDER BY里能用别名因为ORDER BY排在SELECT之后这时候别名已经生成了。看下面这段SELECT user_name AS name, age 1 AS new_age FROM users WHERE new_age 20; -- 报错别名在WHERE中不可见 SELECT user_name AS name, age 1 AS new_age FROM users ORDER BY new_age; -- 正常运行1.2 理解执行顺序能解决哪些实际问题理解执行顺序不只是为了应付报错排查慢查询时更是利器。我在定位线上接口慢SQL时第一步不是看SELECT列了什么而是先看FROM和WHERE确认是全表扫描还是走了索引因为WHERE执行阶段决定了扫描多少行这一步往往是性能瓶颈所在。另一个实际价值是写复杂嵌套子查询时你能判断哪些层可以提前过滤。比如外层WHERE已经过滤了大量数据内层子查询就没必要把所有行都算出来。很多人查询慢不是SQL写得不对而是没意识到执行顺序意味着“尽量早过滤、尽量少参与计算”。记住一个核心原则每一步执行都在缩小结果集尽早把数据量降下来后面的排序和分组就轻松。2. 条件、排序、分页与去重日常查询的四大金刚2.1 WHERE条件过滤与LIKE模糊查询的踩坑点WHERE是查询条件的大门常见写法谁都懂但有几个细节特别容易翻车。第一个是LIKE模糊查询。LIKE %关键字%这种写法因为开头就是通配符索引基本失效数据量大时就是全表扫描。如果你的业务确实需要包含匹配也别让全表扫描成为常态可以考虑搜索引擎或者分词方案如果只是前缀模糊匹配比如LIKE abc%索引还是能用的性能影响不大。第二个是OR条件的陷阱。很多人以为OR和IN等价但OR一旦连接了多个不同字段就可能让优化器放弃索引。比如WHERE user_name 张三 OR phone 13800000000如果两个字段没有组合索引大概率走全表。这种情况改成UNION ALL或者UNION往往能各自走索引。第三个是NULL判断。新手最爱写WHERE age NULL结果查出来永远是空。SQL里的NULL代表“不知道”只能用IS NULL或IS NOT NULL判断任何等值比较都返回“非真非假”直接被过滤掉。另外我不建议在业务表里到处放NULL默认值能解决就别留空查询和统计都会省心不少。还有一个隐蔽的坑是隐式类型转换。字段是字符型你传入数字或者反过来MySQL会自动做转换一旦列上套了函数索引就失效了。比如WHERE phone 13800000000phone字段是varchar这里看起来没问题实际优化器会转成CAST(phone AS int)索引直接废掉。写成字符串形式13800000000才是正解。2.2 ORDER BY排序多字段排序与排序算法有感排序看起来简单但实际执行远不是“排个序”三个字能概括的。MySQL拿到结果集后如果排序字段没走索引会在sort_buffer里做filesort。数据量小于sort_buffer_size时用内存排序数据量超过就会在磁盘上做归并排序性能差距可能有一个数量级。多字段排序的语法本身不难难在语义ORDER BY a DESC, b ASC先按a降序a相同再按b升序。这里有个经典误解有人以为ORDER BY a DESC, b DESC等于ORDER BY a, b DESC错了补全后的写法完全不同第一个是a、b都倒序第二个是a倒序、b正序因为DESC修饰的是整个字段表。我的习惯是每个字段都显式写方向既不模糊也不容易被后续修改带偏。排序优化有两个现实思路。一个是让排序字段尽量用索引比如在经常排序的字段上建索引这样能直接避免filesort另一个是减少参与排序的字段量select时别把无用的大字段带上sort_buffer能装下行数就越多排序越轻快。2.3 LIMIT分页与OFFSET深分页问题分页是后台管理系统的必备技能写法也简单LIMIT offset, count比如每页20条第1页是LIMIT 0, 20第10页是LIMIT 180, 20。但这里藏着一个深分页的性能雷offset越大MySQL要扫描并丢弃的行就越多。举个真实场景订单表500万行你要查第100000页的数据也就是LIMIT 1999980, 20。表面看只要20条实际MySQL把前1999980行全部读出来再扔掉这中间还涉及回表取完整数据慢是必然的。优化方案有两种。一是“基于游标”的分页记住上一页最后一条的ID下一页直接用WHERE id 上一页最大id ORDER BY id LIMIT 20。虽然没有页码跳转了但深翻场景性能提升非常明显而且写法简单。二是“延迟关联”SELECT t.* FROM your_table t INNER JOIN ( SELECT id FROM your_table WHERE status 1 ORDER BY create_time DESC LIMIT 1999980, 20 ) tmp ON t.id tmp.id子查询里只查主键ID让排序过程尽量轻量再通过ID回表取需要的完整记录。代价是SQL变长好处是深分页的速度可以快一个量级。2.4 DISTINCT去重的一个常见误区关于去重我经常被问到一个很具体的问题“OR能去重吗”答案很明确不能。OR只是条件组合和去重没关系。真正的去重要靠DISTINCT或者GROUP BY。但DISTINCT也有坑。当你写SELECT DISTINCT user_name, phone FROM users时它去重的是“两个字段的组合”不是单独把user_name去重。比如表中存在两个“张三”但他们的手机号不同这组数据不会被视为重复。只有你想“只按用户名去重同时拿到该用户名对应的手机号”DISTINCT就做不到了这种需求得靠GROUP BY 聚合函数或者窗口函数而不是DISTINCT。还有一个细节DISTINCT和GROUP BY在功能上有重叠都可以去重但语义不同。单纯去重时两者性能接近但如果还要顺带统计数量、求和那就用GROUP BY。另外DISTINCT对NULL的处理是“所有NULL视为同一组”这在多字段组合去重时会直接影响结果集需要心里有数。3. 聚合与分组统计真的别用肉眼数数3.1 聚合函数使用与COUNT三兄弟区别聚合函数是统计报表的底子COUNT、SUM、AVG、MAX、MIN是最常用的五个。有几个细节太容易被忽略。先说COUNT。很多人纠结COUNT(*)、COUNT(1)、COUNT(字段)到底有啥区别。实践结论是COUNT(*)统计行数包括NULL行COUNT(1)本质也是统计行数和COUNT(*)在InnoDB里性能基本一致COUNT(某列)只统计该列非NULL的的行数。如果你想知道某列到底有多少个非空值就用COUNT(列)否则统一用COUNT(*)逻辑最清楚。再补一个高频需求去重计数COUNT(DISTINCT 列名)。它统计的是该列去重后的非NULL数量比如统计“有多少个用户下过单”就可以用COUNT(DISTINCT user_id)。注意这里的DISTINCT和前面说的SELECT DISTINCT是同一个去重逻辑多字段组合去重也是支持的写法是COUNT(DISTINCT a, b)不过这种场景用得不多。SUM、AVG和NULL的互动也值得一提。SUM(col)遇到整列为NULL时返回NULL而不是0AVG(col)会忽略NULL行再计算平均值。这意味着如果你把NULL当作0参与统计必须自己处理比如AVG(IFNULL(score, 0))先把NULL转成0再求平均否则算出来的结果会偏高。我第一次用AVG统计考试成绩就吃过这个亏全班一个缺考结果平均分被缺考学生拉高了。3.2 GROUP BY分组统计与ONLY_FULL_GROUP_BY分组统计的典型场景是按某个维度汇总比如“按状态统计订单数”“按月份统计销售金额”。语法核心就一句SELECT 分组字段, 聚合函数 FROM 表 GROUP BY 分组字段。这里最大的坑是MySQL的ONLY_FULL_GROUP_BY模式。MySQL 5.7之后默认开启它强制要求SELECT后面出现的非聚合列必须同时出现在GROUP BY里。这在老版本里可以做到“select其他列但只按某列分组”新版本直接报错。比如SELECT user_id, order_no, COUNT(*) FROM orders GROUP BY user_id; -- 非聚合列order_no不在GROUP BY中报错这种写法本来就不符合SQL标准因为同一组里order_no可能有多行选哪一行是不确定的。遇到报错正确做法是把order_no也加入GROUP BY或者用聚合函数包一层比如MAX(order_no)。从业务角度看这也逼着你把统计语义想清楚不完全是坏事。另一个细节是GROUP BY默认会按分组字段做一次排序。如果你只想要分组结果、不关心顺序可以加ORDER BY NULL取消排序能省一点排序开销。这个技巧在做大量分组统计时有效但要注意不是所有场景都适用MySQL 8.0在某些情况下即使写了ORDER BY NULL也没有额外收益不过无伤大雅。3.3 HAVING与WHERE的过滤差异分组之后想过滤怎么办HAVING就是干这个的。WHERE在分组前过滤HAVING在分组后过滤两者执行时机和语义完全不同。举个例子统计“下单超过3次的用户”SELECT user_id, COUNT(*) AS cnt FROM orders WHERE pay_status 1 GROUP BY user_id HAVING COUNT(*) 3;这里WHERE先过滤掉未支付的订单只统计已支付单HAVING再筛掉下单次数不超过3的用户。你不能把COUNT(*) 3写进WHERE因为WHERE执行时分组还没发生聚合结果根本不存在也不能把pay_status 1写进HAVING虽然能跑但把本该提前过滤的数据拖到分组之后再处理性能上就吃亏了。原则很简单能用WHERE过滤的永远优先WHERE。有一个小知识点值得记住HAVING因为排在SELECT之后执行所以它可以使用SELECT里的别名这点和WHERE不同。比如上面可以写成HAVING cnt 3而WHERE里写别名必然报错。这也是很多人一开始绕不清的地方。4. 多表连接查询JOIN其实不难4.1 三种JOIN的区别与ON过滤时机查询操作里多表连接是重点也是难点。先说三种最常用的连接方式。INNER JOIN内连接只返回两个表都匹配上的行其他行丢弃。想找“已经下过单的用户”内连接最合适。LEFT JOIN左连接左表全部保留右表只保留能匹配上的行右表匹配不上就用NULL填充。想找“所有用户以及每个用户的订单信息”即使没下过单也要显示用户就用它。RIGHT JOIN右连接和LEFT JOIN对称右表全部保留用得相对少大多数情况下把两个表互换位置就等效成LEFT JOIN了。ON和WHERE的过滤时机是连接查询里最容易出鬼的地方。对内连接来说ON和WHERE基本可以互换结果一般相同。但对LEFT JOIN可不一样ON的过滤发生在“连接生成临时表”的阶段它决定右表的哪些行能匹配进来WHERE的过滤发生在连接完成之后它会把你辛苦保留下来的右表NULL行再过滤掉。看一个经典例子统计所有用户及其订单金额但只想看金额大于100的订单SELECT u.user_id, o.order_id FROM users u LEFT JOIN orders o ON u.user_id o.user_id AND o.amount 100;这里把o.amount 100写在ON里结果是不满足条件的订单不会出现在右侧但用户行还是保留此时右侧是NULL。如果把这个条件挪到WHERE里SELECT u.user_id, o.order_id FROM users u LEFT JOIN orders o ON u.user_id o.user_id WHERE o.amount 100;由于WHERE过滤了NULL行那些没有超过100元订单的用户直接消失LEFT JOIN的效果等于被削弱成了INNER JOIN。你要“主表全部保留”还是“只保留匹配结果”决定条件应该放ON还是放WHERE这个坑面试高频实战也特别容易踩。4.2 JOIN底层执行逻辑驱动表与被驱动表理解JOIN性能问题必须认识两个概念驱动表和被驱动表。MySQL执行连接查询时不是一下子把两张表都读出来合并而是一张表作为外层循环驱动表另一张表作为内层循环被驱动表来匹配。InnoDB下最典型的执行方式是嵌套循环连接每次从驱动表取一行然后去被驱动表按连接字段找匹配行。所以被驱动表的连接字段有没有索引直接决定每次关联查询快不快。如果没索引MySQL没办法快速查找只能把整张被驱动表都扫一遍连接查询就会变得非常慢。实战中有一个指导原则小表驱动大表。让数据量小的表作为驱动表带连接字段且有索引的大表作为被驱动表能把关联的查询次数降到最低。一般经验是连接字段一定要建索引如果两张表数据量都很大还可以考虑用STRAIGHT_JOIN强制指定驱动表顺序不过这需要你对数据分布有精确认知否则别乱用。最稳的做法是不管大小先用EXPLAIN看执行计划里谁是驱动表再针对性加索引。4.3 连接查询实战示例用一个订单统计场景串起来看。现在有用户表users、订单表orders、订单明细表order_items我需要查“2024年至少下单2次、且支付成功的用户以及他们的订单总额”。SELECT u.user_id, u.user_name, COUNT(DISTINCT o.order_id) AS order_cnt, SUM(oi.quantity * oi.price) AS total_amount FROM users u INNER JOIN orders o ON u.user_id o.user_id AND o.pay_status 1 INNER JOIN order_items oi ON o.order_id oi.order_id WHERE o.pay_time 2024-01-01 AND o.pay_time 2025-01-01 GROUP BY u.user_id, u.user_name HAVING COUNT(DISTINCT o.order_id) 2 ORDER BY total_amount DESC;几个关键点条件pay_status 1放在JOIN的ON里是因为我们希望“已支付订单”参与关联而不是关联后再过滤这样LEFT JOIN换成INNER JOIN也无损语义。COUNT(DISTINCT o.order_id)保证一个订单不管有多条明细都只算一单避免明细表把订单数撑大。GROUP BY带上user_name是因为ONLY_FULL_GROUP_BY模式下非聚合列必须都在分组里而且user_name依赖user_id分组不会导致歧义。HAVING放在WHERE之后过滤的是分组统计结果这里也没办法用WHERE替代。5. 子查询与EXISTS高级查询的两种姿势5.1 IN子查询与EXISTS改写子查询分很多种实践中最常用的是WHERE后面的条件子查询形如WHERE id IN (SELECT user_id FROM ...)。这类写法方便直观但性能上需要留意。先把IN和EXISTS的区别说清楚IN子查询通常先执行子查询把结果集缓存成临时表然后外层表做匹配EXISTS则更贴近逐行判断外层每取一行就去内层查“有没有匹配的记录”一旦找到就返回真并结束该行的判断。因此当外层表数据量小、内层子查询结果集很大时EXISTS往往更合适当内层子查询结果集很小、外层表很大时IN反而更快。一句话原则小表驱动大表谁的数据量小谁放外层。实际写业务时我更喜欢把IN子查询改写成JOIN理由有两个JOIN的关联字段可以走索引优化器的执行路径更透明而且同一个需求经常既要用子查询的数据又要取子查询表的其他字段JOIN能一并拿出来。看个例子查“购买过商品ID100的用户”-- 子查询写法 SELECT user_id, user_name FROM users WHERE user_id IN ( SELECT DISTINCT user_id FROM orders WHERE goods_id 100 ); -- JOIN改写 SELECT DISTINCT u.user_id, u.user_name FROM users u INNER JOIN orders o ON u.user_id o.user_id AND o.goods_id 100;JOIN改写后orders上user_id, goods_id建一个联合索引性能会非常理想。5.2 派生表与标量子查询的注意点FROM后面的子查询叫派生表相当于把一段查询结果当作临时来源。写派生表时最关键的是给它取别名而且MySQL 5.7之后要求派生表必须指定别名。另外派生表会先完整执行一遍数据量大时本身就是消耗所以尽量避免在派生表里放太多无用的行和列能用WHERE提前过滤就把数据压小。还有一种叫标量子查询就是子查询只返回单个值常用于SELECT列里“顺便”算个数比如SELECT user_id, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.user_id) AS order_cnt FROM users u;这种写法用起来方便但对“每一行”都会执行一次子查询外层表有10万行子查询就要执行10万次性能极差。我通常只建议在子查询结果恒定比如查一个配置值或者外层表数据量很小的情况下使用。数据量大时直接改成LEFT JOIN GROUP BY才是正路。有一点容易忽略嵌套太深的子查询可读性会很差排查问题时尤其头疼。在写子查询之前先想一想能不能用JOIN表达。SQL的优化器虽然越来越聪明但你自己把逻辑写直白一点对维护者来说是巨大的善意。6. 综合实战案例三张表完成报表统计6.1 业务场景与表结构设计光讲语法不够我把上面所有知识点串成一个完整案例。假设有一个电商系统三张表如下users用户表字段user_id、user_name、register_time。orders订单表字段order_id、user_id、pay_status1已支付0未支付、pay_time、total_amount。order_items订单明细表字段item_id、order_id、goods_name、price、quantity。业务需求是统计2024年1月到12月之间每个用户的下单总次数、支付总金额、购买商品种类数并按支付金额降序取前20名。6.2 完整查询SQL与步骤拆解SELECT u.user_id, u.user_name, COUNT(DISTINCT o.order_id) AS total_orders, SUM(oi.price * oi.quantity) AS total_paid_amount, COUNT(DISTINCT oi.goods_name) AS goods_types FROM users u INNER JOIN orders o ON u.user_id o.user_id AND o.pay_status 1 AND o.pay_time 2024-01-01 00:00:00 AND o.pay_time 2025-01-01 00:00:00 INNER JOIN order_items oi ON o.order_id oi.order_id GROUP BY u.user_id, u.user_name ORDER BY total_paid_amount DESC LIMIT 20;拆解一下第一段是FROM和ONINNER JOIN把“用户、已支付订单、订单明细”三张表串起来。过滤条件尽量放在ON里因为pay_status、pay_time都属于orders在关联时就限定订单范围结果集更小。第二段是GROUP BY因为我们想统计到用户粒度所以组字段是user_id、user_name。第三段是SELECT各类聚合函数配合DISTINCT解决“订单数不能因明细行翻倍”的问题。第四段是ORDER BY和LIMIT按金额降序再截取20行。如果这个查询响应很慢优先检查orders表的user_id索引、order_items表的order_id索引以及orders表上(pay_status, pay_time)的联合索引。这类统计报表对索引敏感程度极高我在实际优化中经常通过调整索引让查询从几十秒降到百毫秒级别比微调SQL本身收益大得多。7. 查询性能排查与常见问题速查7.1 EXPLAIN执行计划核心字段查询写得不慢靠嘴说不算得用EXPLAIN验证。给任何一条SELECT前加EXPLAINMySQL会返回该查询的执行计划。重点看几个字段。type字段表示访问类型从好到坏大体是system const eq_ref ref range index ALL。system和const通常是主键或唯一索引直接查到一行效率极高ref和range说明索引列做等值或范围匹配是正常状态index表示扫描了整个索引文件可以勉强接受ALL是全表扫描数据量大就是灾难。rows字段是MySQL估计要扫描的行数这个数越大查询越可能慢。Extra字段里如果出现Using filesort或Using temporary往往意味着排序或分组没能利用到索引需要警惕。我第一次定位慢查询时就是看到typeALL、rows50万瞬间明白哪里出了问题随即补了一个联合索引效果立竿见影。7.2 索引失效的常见场景索引和查询条件配合不好索引就失效。下面是我整理的高频失效场景查询调优时逐条对照。场景示例结果隐式类型转换WHERE phone 13800000000phone为varchar列被字段转换索引失效LIKE前置通配符WHERE name LIKE %张%无法利用索引对列使用函数WHERE DATE(create_time) 2024-01-01索引失效改成范围写法对列做运算WHERE salary 1000 5000索引失效运算移到右侧OR连接非索引列WHERE id 1 OR phone 123可能全表扫描用UNION改写联合索引未满足最左前缀联合索引(a,b)条件只写WHERE b 1只能部分利用或不用索引特别注意函数那一行。很多人习惯用DATE(create_time)来查某一天的数据账面读写方便但create_time上的索引完全被绕过了。正确做法是写成范围条件create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00虽然多打几个字索引却实实在在用上了。7.3 查询结果差异问题速查除了性能查询结果不对才是更让人头疼的事。我遇到过的典型问题整理成一张速查表。现象原因解决思路查询结果中文乱码连接字符集与表字符集不一致确认连接使用utf8mb4字符集返回结果多出很多重复行多表JOIN导致结果关联膨胀检查是否存在一对多关联必要时用COUNT(DISTINCT)明明有数据LEFT JOIN右边却是NULL连接条件或ON过滤条件写错单独查右表数据核对关联字段与条件COUNT统计结果和预期差很大COUNT(列)忽略NULLCOUNT(*)包含NULL确认业务语义后选择正确写法查询结果顺序不稳定没有ORDER BY数据物理存储顺序变化显式加上ORDER BY字段深分页越翻越慢OFFSET过大扫描行数太多改用游标分页或延迟关联最后再分享一个我个人特别受益的习惯别急着在正式环境跑大查询先在测试环境用EXPLAIN看一次执行计划顺手把验证后的SQL存到团队的SQL片段库里。一份经过反复打磨的查询模板对新人是最实用的“老师”对团队则是实打实的效率资产。我见过太多人把同样的深分页、同样的隐式类型转换踩了一遍又一遍其实这些坑早就该被记进项目的“查询避坑手册”里了。MySQL查询操作这东西入门容易精通难。我个人体会是与其背语法不如先把执行顺序、索引原理、连接语义这三块地基打牢。实际操作中每次写完SQL都先过一遍“能不能少扫点数据、能不能走索引、连接逻辑是不是我想要的那种关联”养成这个肌肉记忆后写出来的查询质量和那些复制粘贴出来的SQL会有质的差别。后面有机会我们再把索引优化和锁机制这些更硬核的内容拿出来单独聊。