数据库这行干久了你会发现在业务代码里写 SQL 的时间往往比写 Java、Python 还要多。尤其是“基本查询”这四个字看着简单真到面试、上线、排查线上问题的时候多少人栽在它上面。我见过写了好几年代码的开发愣是把 LEFT JOIN 当 WHERE 用也见过把 ORDER BY 条件放到子查询里结果排序完全失效。所以这次我打算把 MySQL 基本查询从头到尾捋一遍不讲花架子直接讲那些你每天都会碰到的语句、原理和坑。这篇内容适合三类人刚接触数据库的学生或转行新人写过增删改查但没系统整理过查询逻辑的初级开发准备面试想快速再过一遍基础的人。内容会覆盖 SELECT、WHERE、ORDER BY、GROUP BY、JOIN、子查询以及我在实际操作中遇到的性能陋习和排查方法。你会看到不少可以直接抄走的语句模板也会看到我踩过的坑。下面我们直接开始。1. 从零开始的 SELECT查询的骨架1.1 先记住 SQL 的执行顺序别被书写顺序骗了很多初学者以为 SQL 是从 SELECT 开始执行的这其实是最大的误解。SQL 的书写顺序和执行顺序并不一致。一条标准查询语句写出来是SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT但在 MySQL 内部的执行顺序却是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。这个顺序为什么重要因为它直接决定了你能不能在 WHERE 里用别名。比如SELECT age 1 AS new_age FROM user WHERE new_age 20;这条语句会报错因为 WHERE 在 SELECT 之前执行此时new_age这个别名还不存在。正确做法是把age 1 20直接写进 WHERE或者在外面套一层子查询。理解执行顺序还有一个好处写复杂语句时你会天然知道过滤条件该放在哪里。比如分组前的过滤用 WHERE分组后的过滤用 HAVING原因也是因为 WHERE 在 GROUP BY 之前执行。1.2 SELECT 与 FROM 的使用细节SELECT 是查询的投影操作决定最终返回哪些列。基本规则很简单但有两个细节经常有人忽略。第一能用SELECT *吗我建议尽量别用。尤其是生产环境的表动辄几十个字段SELECT *会把无关字段全部查出来增加网络传输压力如果表结构后面加了字段线上代码拿到的结果集也跟着变极容易埋雷。第二多表查询时所有字段最好都带表名或别名前缀比如SELECT u.id, u.name FROM user u。这样写的好处是查询语义清晰MySQL 优化器也能少一步“猜字段”的环节更重要的是避免两个表存在同名字段时返回结果错乱。FROM 里还支持子查询作为临时表MySQL 规定这种派生表必须起别名比如FROM (SELECT ...) t。这个技巧在拿到中间结果、继续统计时非常常用后面讲子查询时我会再展开。1.3 DISTINCT、别名与基础表达式DISTINCT 的作用是对结果集去重但很多人没注意到它是“整行去重”不是“单列去重”。SELECT DISTINCT department, city FROM employee;上面这句话的意思是department city这两个字段的组合不能重复而不是 department 唯一。如果你只想看有哪些部门应该用SELECT department FROM employee GROUP BY department或者直接对 department 用 DISTINCT。DISTINCT 还有一个性能小坑它本质上是排序或哈希去重数据量大时会额外消耗内存和 CPU能不用就不要滥用。别名和表达式也是日常查询的高频操作。SELECT amount * price AS total能直接算金额CASE WHEN能做条件转换比如SELECT name, CASE WHEN score 60 THEN 及格 ELSE 不及格 END AS result FROM student;看到这里你应该已经明白基本查询不是把 SELECT 背下来就完事它是有执行顺序、有语义边界、有设计取舍的。后面所有复杂写法都是在这些问题的基础上叠加。2. 把数据框进你的范围WHERE 条件过滤2.1 比较运算与逻辑组合WHERE 的核心作用是对表数据进行逐行筛选保留满足条件的行。最常用的是比较运算符、!、、、、、。逻辑组合用 AND、OR、NOT注意 OR 的优先级低于 AND所以WHERE a 1 OR b 2 AND c 3实际是WHERE a 1 OR (b 2 AND c 3)。如果拿不准就加括号别让读到代码的人猜。这里有个特别容易踩的坑字符串和数字比较。如果你在字符串字段上写WHERE phone 13800001111MySQL 会把字段值和数字都转成浮点数再比较。一旦字段内容不是纯数字转换过程就可能让索引失效甚至出现诡异的结果。正确做法是给字符串加引号WHERE phone 13800001111。NULL 的过滤也是重灾区。WHERE name NULL永远查不出数据因为 NULL 不是一个值它表示“未知”。判断空值必须用IS NULL或IS NOT NULL。很多人写条件过滤时栽在 NULL 上往往就是没理解这一点。2.2 模糊查询 LIKE 和字符集陷阱LIKE 是做模糊搜索最直接的写法但它的性能和个人习惯密切相关。LIKE 张%匹配以“张”开头的字符串这种写法在字段有普通索引时是有机会走索引的但LIKE %张%因为通配符在最前面优化器只能全表扫描数据量一大就会卡。实际项目里如果需要频繁做前后模糊匹配一般建议配合全文索引或者用第三方搜索组件而不是硬扛 LIKE。如果只是临时查数据用LIKE %关键词%也能接受但要心里有数。字符集和排序规则Collation也会影响 LIKE 的结果。比如表是utf8mb4_general_ci时LIKE 匹配一般不区分大小写如果改成utf8mb4_bin它就会区分。有些项目从别的数据库迁到 MySQL发现SELECT * FROM user WHERE name Zhang能查出zhang第一反应以为数据出错了其实是排序规则的问题。判断这个问题很简单执行SHOW TABLE STATUS LIKE user;查看表的 Collation或者用SHOW FULL COLUMNS FROM user;看字段的 Collation。2.3 IN、BETWEEN 与 NULL 处理IN 适合匹配一组固定值比如WHERE status IN (open, closed)。它的语义清楚但有两个使用细节IN 列表里的值太多会降低性能通常超过几百个就应该考虑用连接或临时表如果 IN 子查询返回的结果集特别大优化器可能选择不友好后面会单独讲 EXISTS。BETWEEN 是范围过滤的语法糖WHERE age BETWEEN 18 AND 30等价于age 18 AND age 30。这里要注意 BETWEEN 是包含边界值的跟有些语言里的半开区间不一样。边界是日期时更要小心BETWEEN 2024-01-01 AND 2024-01-31不包含当天的 23:59:59需要判断是否应该用datetime 2024-02-01这种方式。NULL 和空字符串是两个概念。NULL 表示没有值空字符串是有效值。在统计时COUNT(col)会忽略 NULL 但不会忽略空字符串很多数据不一致的问题就从这里来。写过滤条件时最好明确需求到底是排除 NULL还是排除空串还是两个都排除。3. 让数据有秩序ORDER BY 排序与 LIMIT 分页3.1 ORDER BY 多字段排序和字符集问题排序是查询里最容易“想当然”的一个环节。ORDER BY created_at DESC很简单但多字段排序时经常有人写反。比如先按部门升序再按工资降序正确写法是SELECT name, department, salary FROM employee ORDER BY department ASC, salary DESC;这里的关键是只有当前面字段值相同时后面的字段才参与排序。如果你写ORDER BY salary DESC, department ASC结果就是把工资高的排最前部门字段只在工资相同时起作用跟预期完全不同。中文排序的坑也不少。MySQL 的默认排序规则对中文字段通常按拼音排序但如果你用的是gbk_chinese_ci和utf8mb4_general_ci不同版本的 MySQL 表现可能不完全一致。更麻烦的是如果字段里既有中文又有英文、数字排序结果会很“迷”。遇到严格的排序需求可以在 ORDER BY 里显式指定ORDER BY name COLLATE utf8mb4_bin ASC。这个操作能强制按字节序排但要注意它会失去拼音排序功能。3.2 LIMIT 分页与性能优化分页最常见的写法是LIMIT offset, size也就是跳过前 offset 条数据取 size 条。它的性能问题出现在 offset 很大的时候比如查第 100 万页MySQL 还是要先扫描并丢弃前 1000 万条数据这个过程非常浪费。一个经典优化方案是“记录上一页最后一条 ID”SELECT id, name, created_at FROM user WHERE id 100000 ORDER BY id ASC LIMIT 20;它用 WHERE 条件缩小范围让查询能利用主键索引直接定位速度比LIMIT 100000, 20快得多。如果业务必须用传统分页且表很大可以考虑“延迟关联”先只查主键SELECT id FROM user ORDER BY id LIMIT 100000, 20拿到 20 个 ID 后再 JOIN 回原表取完整数据。这样做能减少第一阶段的回表数量实测提升非常明显。3.3 排序与索引的联动很多人以为 ORDER BY 无非就是最后加一句其实排序是否走索引直接决定查询是毫秒级还是秒级。当排序字段和 WHERE 过滤字段能组成联合索引时MySQL 可以直接按索引顺序读取不需要额外排序操作执行计划里的 Extra 列就不会出现Using filesort。举例子表里有索引idx_status_created(status, created_at)查询WHERE status 1 ORDER BY created_at DESC就能从索引里同时完成过滤和排序。但如果写WHERE status 1 ORDER BY namename 字段不在索引里MySQL 就得先把数据查出来再用临时文件排序这就是 filesort。想验证也非常简单执行EXPLAIN SELECT ...看到 Extra 列里有Using filesort就要警觉了。这不是说 filesort 一定慢数据量大时它一定不轻松。所以遇到排序慢优先想能不能调整索引而不是一味加内存。4. 聚合与分组从明细到统计4.1 常用聚合函数聚合函数就是把多行数据计算成一个结果。最常用的是COUNT、SUM、AVG、MAX、MIN。它们各自的细节值得单独说COUNT(*)统计的是行数不管字段是不是 NULLCOUNT(字段)统计的是该字段非 NULL 的行数。SUM(字段)遇到所有值都是 NULL 时返回 NULL不是 0。算业绩时最好用IFNULL(SUM(amount), 0)包一层。AVG(字段)会自动忽略 NULL 行也就是说它不是拿总行数做分母而是拿非 NULL 行数做分母。MAX和MIN对字符串、日期类型也能用日期最大就是最晚的日期字符串比较规则由排序规则决定。聚合函数配合 WHERE 时执行顺序是先 WHERE 过滤再聚合。所以SELECT COUNT(*) FROM orders WHERE status paid统计的是已支付订单数量逻辑很清楚。4.2 GROUP BY 与 HAVING 的使用场景GROUP BY 的作用是分组统计它会把同一字段值的行合并成一组然后每组输出一行结果。分组之后SELECT 后面能出现的字段只分两类分组字段本身或者聚合函数计算结果。比如SELECT department, AVG(salary) AS avg_salary FROM employee WHERE status active GROUP BY department HAVING AVG(salary) 8000;这条语句先过滤在职员工再按部门分组算平均工资最后只保留平均工资大于 8000 的部门。注意HAVING是对分组后的结果做过滤能用 WHERE 提前过滤的条件就尽量不放在 HAVING 里因为 HAVING 是在分组后执行能处理的数据范围已经没那么“友好”了。MySQL 的sql_mode如果开启了ONLY_FULL_GROUP_BY你 SELECT 的非聚合列必须出现在 GROUP BY 中否则直接报错。很多老项目从宽松模式迁到严格模式时会冒出一堆错误原因就在这里。建议新项目直接保持默认严格模式强行让自己写出标准 SQL。4.3 分组统计常见坑我见过最多的问题是统计“每个用户最近一次登录时间”时想当然写成SELECT user_id, MAX(login_time), login_ip FROM login_log GROUP BY user_id;在严格模式下这直接报错因为login_ip不在 GROUP BY 里。但即使不报错返回的 login_ip 也不一定是最近一次登录的那条记录的 IP因为 MySQL 只保证MAX(login_time)的结果正确不保证同行其他字段也是“最大值对应的值”。这个语义问题非常隐蔽正确做法是用子查询先查出每个用户的最大登录时间再 JOIN 回原表取完整记录。分组统计另一个坑是 NULL 值会被单独分成一组。比如GROUP BY department所有 department 为 NULL 的数据会聚合成一组看起来就像凭空多了一个“空部门”。如果你不想统计 NULL记得先加WHERE department IS NOT NULL。5. 多表连接 JOIN从一行看到另一张表5.1 理解 JOIN 的几种类型JOIN 是基本查询里最考验逻辑能力的部分核心就是“把两张表按某种条件拼起来”。最常见的三种INNER JOIN只返回两表都匹配上的行其他行丢掉。LEFT JOIN返回左表全部行右表没有匹配时用 NULL 填充。RIGHT JOIN反过来返回右表全部行左表没有匹配时用 NULL 填充。举一个订单和用户的例子。订单表有 user_id用户表有 id。我想查所有订单以及对应的用户姓名SELECT o.order_id, u.name FROM orders o LEFT JOIN users u ON o.user_id u.id;这里的语义是“订单是主角即使某些订单的 user_id 在用户表里不存在订单行也要出现在结果里只是用户姓名为 NULL”。如果用INNER JOIN那些没有匹配用户的订单就会消失。两种写法就差几个字母结果差出好几行这也是联表查询最容易出 bug 的地方。MySQL 本身不支持FULL OUTER JOIN如果业务需要“合并两张表的所有数据匹配得上就匹配匹配不上就补 NULL”可以同时 LEFT JOIN 再 RIGHT JOIN然后用 UNION 去重。不过这种情况比较少一般通过表结构设计就能避免。5.2 ON 与 WHERE 的区别这个知识点我几乎每次培训都要强调。ON 后面的条件负责“如何连接两张表”WHERE 负责“连接完成后过滤结果”。两者在 LEFT JOIN 中有着本质区别。SELECT o.order_id, u.name FROM orders o LEFT JOIN users u ON o.user_id u.id AND u.status active;上面的查询会返回所有订单即使关联到的用户 status 不是 active用户字段照样返回只是不满足 ON 条件的用户行会被置为 NULL。但如果写成SELECT o.order_id, u.name FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE u.status active;那 LEFT JOIN 的结果还要经过 WHERE 过滤一旦用户 status 不满足条件整行订单都被过滤掉LEFT JOIN 就变成了 INNER JOIN 的效果。很多线上数据异常排查到最后都是这个原因。对于 INNER JOINON 和 WHERE 在结果上等价但为了让语义清楚最好还是把连接条件放 ON业务过滤条件放 WHERE。5.3 JOIN 性能注意事项多表 JOIN 的性能比单表查询容易失控。我总结几个基本注意事项连接字段类型要一致。如果一张表 id 是 INT另一张表 user_id 是 VARCHARJOIN 时 MySQL 要做隐式类型转换索引大概率失效。小表驱动大表。MySQL 优化器会尽量用小表作为驱动表但如果你在 JOIN 条件上用了函数或表达式优化器就没法准确估算。关注执行计划里的 type 和 Extra。最优是eq_ref或ref如果出现ALL且是大表就要考虑加索引。不要 JOIN 太多张表。三张以上大表关联时数据量会呈指数级膨胀可以先在应用层拆查询或者提前汇总。JOIN 本身没有罪关键是要让每一步都走索引。每次写 JOIN 之前先看连接字段是否有索引这比事后调优省事得多。6. 子查询与进阶技巧让查询更灵活6.1 子查询的三种常见写法子查询就是嵌套在查询里的查询常见有三种位置各有各的用途。第一种在 WHERE 里SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 100);这种写法适合“先查出符合条件的集合再用集合过滤外层”。第二种在 FROM 里SELECT department, AVG(amount) FROM ( SELECT user_id, department, amount FROM orders o JOIN users u ON o.user_id u.id ) t GROUP BY department;FROM 子查询相当于先构建一张临时结果表外层继续做统计。注意 MySQL 对派生表有严格要求每个派生表都必须有别名上面例子里的t就是干这个用的。第三种在 SELECT 后面作为标量子查询SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id users.id) AS order_count FROM users;这种写法适合给每一行补充一个聚合值但要小心如果子查询返回多行MySQL 会直接报错所以通常都会配合聚合函数或者直接限制返回一行。6.2 EXISTS 与 IN 的选择WHERE id IN (SELECT ...)和WHERE EXISTS (SELECT ...)并不总是可以互相替代它们的执行思路不同。IN 子查询通常会先把子查询结果集算出来再和外层逐行比较。如果子查询结果集很小比如只有几十个 idIN 很高效。但子查询结果集很大时内存和比较成本都会上升。EXISTS 则是相关子查询对外层的每一行都会去判断“是否存在满足条件的行”只要找到一行就会停止。如果子表上有合适的索引EXISTS 在外层表数据量大的时候往往表现更好。实际项目中我一般这样选子查询结果集小且稳定用 IN外层表数据量很大并且能在子查询关联字段上走索引用 EXISTS。当然最靠谱的不是拍脑袋而是用 EXPLAIN 看执行计划数据说话。MySQL 8.0 之后的优化器已经能把不少 IN 子查询改写为更优执行方式所以也不用过度焦虑掌握基本取舍原则就够用了。6.3 UPDATE / DELETE 中的子查询实践MySQL 查数据的能力强更新和删除也经常要结合子查询完成。比如要把“没有下过任何订单的用户”标记为无效UPDATE users SET status inactive WHERE id NOT IN (SELECT user_id FROM orders);这个需求很常见但 MySQL 有个著名的限制不能在修改一张表的同时直接查询这张表。也就是说如果你试图删除某张表里某个条件的记录而子查询也来自同一张表就会报错DELETE FROM t WHERE id IN (SELECT id FROM t WHERE ...); -- ERROR 1093: You cant specify target table t for update in FROM clause解决办法是给子查询套一层临时表DELETE FROM t WHERE id IN ( SELECT id FROM ( SELECT id FROM t WHERE ... ) tmp );这个“你要改表 t先从表 t 里把目标 id 捞出来放临时表里再去更新”的思路我几乎每个月都会用上。遇到类似报错不要慌套一层派生表就能绕过去这也再次印证了 FROM 子查询的重要性。7. 新手避坑指南MySQL 基本查询的实战心得7.1 慢查询的排查思路基本查询写得再漂亮跑得慢一样白搭。我遇到查询性能问题第一步不是猜而是看执行计划EXPLAIN SELECT ...;重点看几个关键列type至少达到range最好到ref或eq_ref如果出现ALL基本可以判定是全表扫描。key实际用到的索引如果为 NULL说明没走索引。rows预估扫描行数这个数字越大越危险。Extra出现Using filesort、Using temporary都要警惕。排查出全表扫描的原因后优先考虑两个方向WHERE 条件里的字段有没有索引查询语句有没有在字段上做函数、运算、隐式转换导致索引失效。加索引也不是越多越好但针对高频查询建联合索引收益非常明显。比如这个查询SELECT * FROM orders WHERE status paid ORDER BY created_at DESC;建议索引idx_status_created(status, created_at)能同时覆盖过滤和排序。7.2 常见错误速查表新手阶段遇到的报错很多都有固定解法。我整理了一份速查表遇到类似问题可以直接对号入座。错误/表现常见原因解决办法Unknown column xxx in where clause字段名写错或者表别名没写对检查字段是否存在多表查询显式加别名Error 1055: Expression #... not in GROUP BY开启了 ONLY_FULL_GROUP_BYSELECT 了非分组字段把多余字段移出 SELECT或改用聚合函数Error 1093: You cant specify target table for update in FROM clause同一张表出现在 UPDATE/DELETE 和子查询中子查询外层再套一层临时表WHERE name NULL 查不到数据NULL 不能用等号比较改成 IS NULL字符串字段和数字比较结果诡异隐式类型转换导致索引失效字符串加引号字段类型保持一致ORDER BY 没生效子查询内部排序被外层覆盖排序放到最外层或改用 JOIN / 聚合分页到后面越来越慢offset 过大用 ID 条件分页或延迟关联优化这些错误不是背下来就行最好自己在本地复现一遍。我当年就是故意把这些错误 SQL 写出来逐个看报错信息后来遇到类似问题一眼就能定位。7.3 练习建议和扩展方向基本查询想练扎实光看文章不够必须上手敲。我比较推荐三个练习素材官方文档里的示例库 employees 和 sakila数据量适中关联关系齐全自己随便建两张表插入几千条测试数据然后疯狂改写查询去公司真实业务场景里做只读查询但要注意权限和敏感数据保护。练习时要有目的地“换着写”。同一个需求先用 JOIN 写一版再用子查询写一版然后对比执行计划同一张表先统计总数再分组再带条件分组再排序分页把一个场景吃透远比机械地刷一百道题有用。如果这篇文章看到这里还觉得不过瘾下一步值得研究的方向是索引的工作原理、EXPLAIN 各列含义、慢查询日志分析、窗口函数MySQL 8.0。这些知识和基本查询是一脉相承的查询语法是招式索引和执行计划是内功二者叠加才能真正告别“小白”。最后分享一个我自己的习惯写完任何一条查询我都会顺手执行一次 EXPLAIN哪怕数据量很小。这个动作坚持半年以后你再写 SQL就会本能地避开那些全表扫描、超大分页、隐形类型转换的写法。不是因为它多高级而是因为养成习惯才能让基础真正变成肌肉记忆。
