上周五下午一个写了两年业务代码的同事突然问我说面试官让他手写一条“查每个部门工资最高的员工”的SQL他写了十几行结果还是错的。我帮他改完之后他感慨了一句“感觉MySQL查询我天天在写但真往深了问全是基础知识没串起来。”他这句话让我挺有感触的。MySQL基本查询这个名字听着门槛低好像谁都会写但绝大多数人其实是靠肌肉记忆在写SELECT、WHERE、ORDER BY一个个子句都认识组合在一起就乱了。这篇博文就把MySQL基本查询这件事从底层执行顺序到高频实操细节拆开讲一遍覆盖日常开发里95%以上的查询场景适合刚入行的同学系统地补一遍基础也适合写了两三年业务SQL但没时间理清逻辑的老开发查漏补缺顺便应对“MySQL排序”“mysql常用的sql语句”这类高频面试问题。1. 先搞清楚SELECT的执行顺序再谈其他1.1 字面顺序与执行顺序的差异很多人初学SQL时背的是这样的顺序SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT。这个顺序是“写代码”的顺序但不是MySQL真正干活的顺序。MySQL拿到一条SQL之后实际执行顺序是FROM确定数据源如果是多表连接还要先做JOINWHERE对FROM阶段产生的数据逐行过滤GROUP BY按指定列分组HAVING对分组后的结果做过滤SELECT投影选出要返回的列计算表达式ORDER BY排序LIMIT截取行数这个顺序往后我还会反复提到因为它直接决定了很多语法能不能用。举个日常最常见的例子你写SELECT name, salary * 12 AS annual_salary FROM emp WHERE annual_salary 100000MySQL会直接报错。原因就是在WHERE执行的时候SELECT还没有执行别名annual_salary根本不存在。反过来你在ORDER BY里用别名就没问题因为ORDER BY排在第5步之后执行。在实际工作中理解执行顺序最大的价值是帮你养成“先缩小数据再计算”的习惯。我见过很多人在子查询或者JOIN里不管三七二十一先把全表数据查出来然后在外层再过滤导致一张只有几十万行的表查询耗时好几秒。如果按照执行顺序去优化把过滤条件下推到WHERE甚至JOIN的ON条件里数据量提前缩小后面排序、分组、计算的压力都会小很多。基本查询不复杂但执行顺序就是一根贯穿所有查询逻辑的线理清它等于给整个SQL知识体系打了地基。1.2 每张表都绕不开的FROM与WHEREFROM子句是所有查询的起点它指定了要操作的表。单表查询就是简单的一个表名但实际开发里更常见的是多表关联这时候FROM后面跟的是JOIN操作这个我放到后面的多表连接环节细讲。这里想说的是别小看FROM这一步它决定了整个查询的扫描范围。MySQL执行查询时首先确定访问哪张表、走哪个索引、扫描多少数据这些都在FOR阶段就定下了基调。你后续写的WHERE条件能不能用上索引其实在优化器分析FROM的时候就开始盘算了。WHERE是过滤行数据的核心条件也是基本查询里使用频率最高的子句。WHERE支持的操作符类型很多包括比较运算符、、、、、、逻辑运算符AND、OR、NOT、范围判断BETWEEN AND、IN、模糊匹配LIKE以及空值判断IS NULL、IS NOT NULL。这些语法都很直白真正坑人的是我接下来要说的几个细节。2. 条件过滤的三个层次WHERE、HAVING、ON2.1 WHERE的常用操作符与陷阱先给出一张WHERE常见操作符的使用速查表方便平时随时翻操作符作用示例 / 或 !等于 / 不等于name 张三 / / / 大小比较salary 10000BETWEEN AND闭区间范围判断age BETWEEN 18 AND 25IN匹配列表中的任意值dept_id IN (1,2,3)LIKE模糊匹配%匹配任意字符_匹配单个字符name LIKE 张%IS NULL / IS NOT NULL空值判断email IS NULLAND / OR / NOT逻辑组合a1 AND (b2 OR c3)这里列几个我平时见得太多的坑。第一个是NULL判断。很多新手写email NULL结果查询结果永远是空这不是MySQL抽风是因为SQL里NULL表示“未知”任何值与NULL做比较运算结果都是UNKNOWN既不是TRUE也不是FALSE。正确写法只能是IS NULL或者IS NOT NULL。第二个是AND和OR的优先级AND的优先级高于OR所以a1 OR b2 AND c3实际执行的是a1 OR (b2 AND c3)。如果你想把条件表达成(a1 OR b2) AND c3不加括号必出事。我的习惯是只要涉及AND和OR混用一律加括号可读性和安全性都照顾到了。第三个值得提醒的是隐式类型转换。MySQL在遇到字符类型的字段和数字比较时会尝试把字符串转换成数字。比如status1写成status1如果status列是varchar类型MySQL通常还是能正常匹配的但一旦字符串里包含非数字字符转换规则会变得很难捉摸并且很可能导致索引失效。我见过一个线上事故订单表的order_no是varchar类型有人在查询时写了order_no12345678901234567890这样一个超过int范围的大数字MySQL把它转成浮点数比较直接导致索引失效全表扫描。所以这类字段的等值匹配永远给值加上引号坚持类型匹配。2.2 ON与WHERE在JOIN中的微妙区别等讲到JOIN的时候你会频繁接触到ON和WHERE两个过滤条件。这里先建立一个概念ON是连接条件负责决定两张表怎么拼在一起WHERE是过滤条件负责在拼接完成后对结果集进行筛选。对INNER JOIN来说把过滤条件写在ON里还是WHERE里最终结果一样因为内连接只会返回两边都匹配的行ON和WHERE都做过滤效果是叠加的。但对LEFT JOIN来说区别就大了。LEFT JOIN会保留左表的全部行右表没有匹配到的位置补NULL。如果你在ON里写右表的过滤条件比如ON a.id b.id AND b.status 1那么左表所有行还是会保留只是右表不满足status条件的行都被过滤掉了最终结果依然是左表全量。可如果你把这个条件写在WHERE里那WHERE是在JOIN完成之后执行的不满足status1的行会被直接丢弃左表中右表不匹配的行也连带被删掉了结果就等于把LEFT JOIN变成了INNER JOIN的效果。我见过不少人在统计报表时犯这个错明明想保留左表全量数据结果统计出来的行数无故变少排查很久才发现是过滤条件写错了位置。3. 排序与分页结果集呈现的最后一道工序3.1 ORDER BY的排序规则与多字段排序ORDER BY是基本查询里最简单的子句语法就一句话ORDER BY column [ASC|DESC]。不写ASC或者DESC时默认是ASC升序。字符串排序按字符集排序规则来决定数字按数值大小日期按时间先后。多字段排序的写法是ORDER BY a DESC, b ASC这里要特别注意逻辑先按a降序排在a相同的前提下再按b升序排不是同时按两个字段独立排序。MySQL里NULL值的排序规则要单独记一下升序时NULL排在最前面降序时NULL排在最后面。这个行为和Oracle不一样Oracle默认NULL永远最大所以升序时NULL在最后。如果你在做跨数据库迁移的SQL适配这地方很容易踩坑。比如你想查员工列表按绩效排序没有绩效的人想要排在最后面用ORDER BY performance DESC就能实现因为NULL在降序时排在最后。另一个实用点是ORDER BY与索引的关系。如果排序字段恰好是索引列并且排序方向和索引顺序一致MySQL直接按索引顺序读取就可以了不需要额外排序操作这叫Using index。如果排序字段和索引对不上MySQL会生成一个内部临时文件做排序也就是常说的filesort。数据量小的时候感觉不到差距真到几十万行的时候filesort可能是秒级延迟的来源。日常开发里最典型的优化措施就是为“WHERE条件字段排序字段”建联合索引比如经常执行WHERE dept_id ? ORDER BY salary DESC那么(dept_id, salary)这个联合索引就很有价值。3.2 LIMIT分页的参数设计与深分页优化LIMIT是MySQL独有的分页语法标准SQL没有这个写法用法有两种LIMIT offset, count和LIMIT count OFFSET offset意思都是跳过offset行取count行。第n页的数据offset就是从0开始算的(n-1)*pageSize。举个例子每页20条第三页就是LIMIT 40, 20。从小到大做分页这个写法毫无问题。但系统跑大了之后会遇到著名的深分页问题。比如你要取第50000页的数据也就是LIMIT 1000000, 20MySQL并不是只读最后20条它是先扫描出前1000020行然后丢弃前1000000行才把最后20行返回给你。这个扫描成本会随着页数增长而增长后台系统翻到很深的页码时接口延迟会肉眼可见地变差。有两个比较实用的优化手段。第一个是延迟关联核心思路是先通过索引快速定位到主键再用主键回表查完整行数据。写成SQL就是SELECT * FROM emp WHERE id IN (SELECT id FROM emp ORDER BY salary DESC LIMIT 1000000, 20)这样内层子查询只扫索引列和主键不查完整行数据扫描成本大幅下降。第二个是游标分页不靠页码而是记录上一页最后一条数据的某个唯一字段值比如WHERE id 上次返回的最大id ORDER BY id LIMIT 20这种写法每次查询都能直接用索引定位到起点不管翻多少页性能都稳定。现在的APP信息流基本都是用这种方案如果你在后端接口里发现用户只需要“上一页/下一页”而不需要“跳页”优先考虑游标分页。分页还有一个必须养成的习惯一定要配合确定的ORDER BY。如果不写ORDER BY或者排序字段存在重复值MySQL返回的行顺序是不保证稳定的。同样一条SQL连续执行两遍翻页时可能出现同一行数据在不同页里重复出现。解决方法是让排序字段带上唯一性比如ORDER BY id或者ORDER BY create_time DESC, id DESC。4. 聚合查询从明细到统计的思维转换4.1 五个聚合函数的使用要点聚合函数把多行数据合并成一行是SQL从“明细查询”到“统计查询”的关键。常用的聚合函数一共五个COUNT、SUM、AVG、MAX、MIN用法本身不难真正容易踩坑的是它们对NULL的处理逻辑。COUNT函数有三个常见写法COUNT()、COUNT(1)、COUNT(列名)。COUNT()统计的是所有行数包括值为NULL的行COUNT(1)的效果和COUNT()一样只是把恒真表达式作为计数依据COUNT(列名)则不同它只统计该列不为NULL的行数。所以在统计“手机号为空的用户数”时用COUNT()减去COUNT(phone)就能拿到结果或者直接COUNT(phone)配合WHERE phone IS NULL的方向思考。SUM和AVG对NULL的处理比较天然忽略NULL值只计算非NULL行的总和或平均值。这一点看起来人性化但往往会带来统计口径的问题。比如计算员工的平均工资AVG(salary)只统计有工资的人如果有的员工工资字段是NULL这个平均值就偏高了。如果业务上需要把无工资的人按0算进分母就必须自己用COALESCE把NULL转成0AVG(COALESCE(salary, 0))。MAX和MIN比较简单自动忽略NULL对字符串字段会按字符集排序规则取最大最小对日期字段按时间先后取。4.2 GROUP BY分组逻辑与HAVING过滤分组的核心逻辑就一句话把相同的列值合并成一组然后在组内做聚合计算。单字段分组很好理解比如SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id这是统计每个部门的人数。多字段分组则是按多个列的组合去重比如GROUP BY dept_id, gender就是统计每个部门下男女各多少人。这里要特别说一个让不少人头疼的点SELECT子句里出现的非聚合列必须包含在GROUP BY中。MySQL在默认配置下其实允许你写出一部分“违规”SQL比如SELECT name, dept_id, COUNT(*) FROM emp GROUP BY dept_id它返回的name是组内随机一条记录的值。但在ONLY_FULL_GROUP_BY模式下这种写法直接报错。我建议你主动去遵守这个规则不要在业务SQL里依赖MySQL的宽松行为因为这种随机取值的行为在语义上就是不确定的换一条SQL执行路径结果可能就变了排错时极难定位。HAVING是专门用来过滤分组结果的子句它和WHERE最大的区别就是执行时机不一样。WHERE在GROUP BY之前执行过滤的是原始行HAVING在GROUP BY之后执行过滤的是分组。比如你要查“人数超过50人的部门”WHERE里没法写COUNT()因为分组还没做聚合函数算不出来只能用HAVING COUNT() 50。日常开发有个很常见的优化技巧能用WHERE提前过滤掉的数据尽量用WHEREWHER阶段过滤掉的原始行不会参与分组和聚合计算能减少GROUP BY的工作量HAVING只负责分组后的最终条件筛选。GROUP BY还有一个高频搭配是GROUP_CONCAT它可以把组内多行的字段拼接成逗号分隔的字符串。比如把每个部门的员工姓名列出来SELECT dept_id, GROUP_CONCAT(name) FROM emp GROUP BY dept_id。这个函数在处理一对多关系的展示时非常好用可以省去在应用层做循环拼接的麻烦。5. JOIN连接多表查询的正确打开方式5.1 INNER JOIN与LEFT JOIN的实战选择JOIN是整个基本查询体系里最像“业务逻辑”的部分因为真实的数据几乎不可能装在一张表里。最常见的JOIN有三种INNER JOIN内连接、LEFT JOIN左连接、RIGHT JOIN右连接。实际开发中INNER JOIN和LEFT JOIN占到了绝大多数使用场景。RIGHT JOIN几乎可以用LEFT JOIN反转表的顺序来替代我基本不用因为它会让SQL的可读性变差。CROSS JOIN是笛卡尔积连接不加ON条件会生成两张表的全部组合日常业务里几乎没有用途误用它的后果是灾难级的后面我会单独说。INNER JOIN只返回两边都匹配成功的行核心语义是“同时满足”。LEFT JOIN返回左表全量数据右表没有匹配上的行显示为NULL核心语义是“以左表为准补充右表信息”。举一个生活化的例子查“所有员工及其部门信息”如果部门表里没有对应记录INNER JOIN查出来的人会少掉那些没部门的人LEFT JOIN则保留所有员工缺失的部门信息显示为NULL。业务上通常用LEFT JOIN来输出完整报表确保主表数据不丢失用INNER JOIN做严格的交集数据筛选这两种选择的业务含义完全不同写SQL之前先问清楚需求是哪种语义。JOIN的ON条件支持等值连接也支持非等值连接比如ON a.salary BETWEEN b.low_salary AND b.high_salary这种按工资区间匹配等级表的写法在业务里也比较实用。多表连接时SQL里按顺序两两连接最终形成一个结果集。JOIN的顺序不同性能可能完全不同在表结构和数据量差异比较大的场景下特别明显后面讲优化时细说。5.2 笛卡尔积陷阱与连接条件缺失笛卡尔积这个词听起来很学术但落到实际就是忘记写ON条件或者ON条件写错导致匹配不上结果行数变成两张表行数的乘积。一张10000行的表和一张50000行的表做笛卡尔积结果是5亿行这个查询在绝大多数线上环境里轻则拖垮当前会话重则把数据库CPU打满。我曾经在一个后台管理系统的日志里看到有人把两张字典表JOIN在一起而没写连接条件查询直接把数据库内存打到报警阈值。幸好是内部低峰期重启才恢复。从业者的习惯是写JOIN时先把结构写完整再补条件。我推荐一个组合拳写法——FROM表A JOIN表B ON连接条件 JOIN表C ON连接条件每一段JOIN紧跟自己的ON条件不要全部连接完再集中加ON那样最容易漏。另外连接条件里尽量用等值连接非等值JOIN和函数表达式JOIN的索引利用率都很差。等值连接时还要留意两端的字段类型字符集不一致或者类型不一致也会导致索引失效。5.3 自连接与驱动表优化思路自连接就是一张表和自己做JOIN核心技巧是给表起不同的别名来区分两个角色。最经典的场景是员工表里存了manager_id指向同一张表的id要查每个员工及其上级的名字就需要把员工表拆成“员工实体”和“管理者实体”两个逻辑表来连接。SQL长这样SELECT e.name AS emp_name, m.name AS manager_name FROM emp e LEFT JOIN emp m ON e.manager_id m.id;这里不给表起别名就根本没法写因为两个角色都指向同一张表。连接性能优化里有一个概念叫驱动表简单说就是JOIN操作中外层循环先读的那张表。MySQL的优化器一般会选数据量小的表作为驱动表也就是“小表驱动大表”。日常开发中你要尽量保证连接条件的字段在驱动表一侧有索引这样可以大大减少被驱动表的查询次数。以员工和部门两张表为例如果都用部门ID做等值连接优化器通常会选择行数更少的部门表作为驱动表然后通过索引去员工表中逐个查找匹配记录。除了理解这个概念更重要的是养成分析执行计划的习惯EXPLAIN输出结果中排在最前面的那张表通常就是驱动表如果发现驱动表选错了可以通过调整表的连接顺序或者使用STRAIGHT_JOIN来干预优化器决策。6. 子查询与条件分支复杂需求的最后一公里6.1 子查询的三种形态与性能注意子查询就是嵌套在外部查询里的SELECT语句常见有三种形态。第一种是标量子查询它返回单行单列可以直接用在SELECT列表或WHERE比较条件里比如查每个员工的姓名以及所在部门名称。第二种是列子查询返回一列多行通常搭配IN、ANY、ALL来使用比如查“部门在销售部和技术部的所有员工”。第三种是表子查询返回多行多列放在FROM子句里充当一张虚拟表这种写法在JavaWeb项目里的复杂报表统计中很常见可以把多个聚合层级嵌套起来。子查询的用法灵活但性能需要留心。最典型的就是IN子查询和EXISTS子查询的选择。早期MySQL版本对IN子查询的优化做得不好业界普遍建议把IN改写成EXISTS。新版本MySQL对IN子查询做了很多的优化改写两者的性能差异已经缩小不少但有一个场景是绕不过去的当子查询结果集合里含有NULL值时NOT IN的语义会变成“结果不确定”导致整个查询不返回任何行。这是SQL三值逻辑TRUE、FALSE、UNKNOWN导致的经典陷阱用NOT EXISTS替代就可以完美绕开。在FROM子句里使用派生表时必须给子查询结果起一个别名这是MySQL的硬性要求。哪怕你只是临时用一下也要写AS t不然直接报语法错误。另外防止一个比较隐蔽的性能坑不要在子查询里出现多余的ORDER BY尤其是放在IN子查询里时排序不但没有意义还会白白消耗排序资源。6.2 CASE WHEN实现条件分支CASE WHEN是SQL里实现条件逻辑的主要手段可以理解成“编程语言里的if-else”。它的基本语法有两种形式一种是简单CASE表达式CASE column WHEN value1 THEN result1 ELSE result2 END另一种是搜索CASE表达式CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result3 END后者更灵活日常开发中用到最多。比如把员工绩效等级分数映射成文字描述SELECT name, CASE WHEN score 90 THEN 优秀 WHEN score 75 THEN 良好 WHEN score 60 THEN 及格 ELSE 不及格 END AS level FROM emp;CASE WHEN还有一个很妙的用途是做“条件聚合”也就是在聚合函数内部嵌套CASE实现一行SQL统计多个指标的效果。比如统计每个部门的男女数量和平均工资如果不用CASE WHEN你可能要写三条SQL分别查询再在应用层合并而用CASE WHEN可以一条SQL完成SELECT dept_id, COUNT(CASE WHEN gender 男 THEN 1 END) AS male_cnt, COUNT(CASE WHEN gender 女 THEN 1 END) AS female_cnt FROM emp GROUP BY dept_id;这个写法里的COUNT操作符只统计非NULL值CASE WHEN没有匹配到时返回NULL于是被COUNT自动忽略达到按条件计数的效果。同理还可以配合SUM实现“只对满足条件的行求和”比如统计各部门男性员工的工资总和。这种一条SQL完成复杂统计的能力在写报表类需求时价值极大。7. 高频场景速查与实操心得7.1 日常开发最常用的SQL场景速查场景SQL写法要点全表查询SELECT * FROM table生产环境慎用建议指明列名条件过滤WHERE status 1 AND create_time 2024-01-01模糊搜索WHERE name LIKE CONCAT(%, #{keyword}, %)去重统计SELECT DISTINCT dept_id FROM emp分页查询ORDER BY id DESC LIMIT (page-1)*size, size分组统计SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id分组后过滤GROUP BY dept_id HAVING COUNT(*) 10多表关联FROM emp e LEFT JOIN dept d ON e.dept_id d.id最大最小值SELECT MAX(salary), MIN(salary) FROM emp条件聚合COUNT(CASE WHEN status 1 THEN 1 END)字符串聚合GROUP_CONCAT(name) 按组拼接字符串动态日期条件WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-06-01行转列统计结合CASE WHEN与GROUP BY实现行列转换7.2 几条实操经验和避坑总结把基本查询的各个子句都过了一遍之后最后分享几条我个人多年积累的实操经验。第一条是写SQL先搭骨架再补细节。我习惯先写SELECT需要的列再写FROM和JOIN结构接着写WHERE过滤最后补ORDER BY、LIMIT每写完一层就先感叹号检查一下结果是否符合预期这样定位问题的成本最低。不要想着一条SQL一步到位写完几十行的复杂查询中间大概率会出现行列数对不上、分组错误这样的低级问题拆开来写反而更稳。第二条经验是测试SQL时养成使用EXPLAIN的习惯。基本查询虽然简单但一旦涉及多表JOIN和复杂WHERE执行计划里会出现很多有用的信息比如访问类型ALL表示全表扫描ref和range表示走索引、扫描行数、是否使用临时表和文件排序等。发现某个查询突然变慢先别急着改业务逻辑EXPLAIN看一下通常能秒定位到索引失效或者驱动表选错的问题。这个习惯越早养成越好。第三条是格式化SQL。长SQL不乱成一团的话在排查问题时能省下一半时间尤其是多表JOIN和嵌套子查询的场景。团队里如果有人写出几十行没有任何缩进的SQL接手的人真的会想打人。我自己处理这种历史SQL时第一步永远是先格式化把每个子句单独换行、对齐关键字很多时候格式整理完问题也就暴露出来了。最后说一个很实用的习惯基本查询的WHERE里条件顺序不要随意摆放。经验上把等值条件放前面范围条件放中间模糊匹配放最后。虽然MySQL优化器会自动调整执行顺序来优化查询成本但这样写直观上增强可读性别人读你的SQL时能快速理解这个查询的主要过滤条件自己下次维护时也更容易构建整体的查询逻辑这个细节看着不起眼在团队协作里收益很大。基本查询的内容到这里就展开完了。我个人带新人时的习惯永远是让他先把这一套基础过一遍因为不管是复杂的存储过程、性能调优还是那些天花乱坠的面试题最后拆开来看都是从这几十行SQL基础长出来的。摸透了一条SELECT怎么被解析、怎么被优化、怎么被执行后面学连接池、索引优化、读写分离心里都有底。你如果现在正被某个查询卡住不妨把上面几个章节里对应的部分再对照着过一遍大概率问题就出在某个子句的语义理解上。