如果你刚接触数据库在命令行里敲下第一行SELECT * FROM users看到一个表格哗啦一下出现在屏幕上那种感觉确实很爽。但MySQL基本查询远远不止把表整个拉出来。我带团队做数据看板那阵子几乎每几天就会碰到同事卡在一条很基础的查询上——可能是JOIN之后数据翻倍可能是IN列表写错报语法错误也可能是日期字段明明有数据却查不出来。这篇文章我会把MySQL基本查询里最常用的那些东西拆开讲从准备环境、单表查询、聚合分组到多表关联、去重技巧再到慢查询与常见报错。没有花里胡哨的高并发架构全是平时写报表、做后台管理、查业务数据时一定会用到的东西。1. 先把环境准备好安装MySQL、建库建表、造测试数据基本查询听起来简单但很多人第一步就卡在环境上。我见过不少人在Windows上装MySQL时一路狂点下一步结果字符集不对、认证插件不兼容后面写中文查不出来或者客户端连不上。所以这一节把环境准备的经验说清楚后面所有查询示例都基于一个能跑起来的环境。1.1 安装时最容易被忽略的两个选项第一个是字符集。MySQL 5.7和MySQL 8.0的默认通常能选utf8mb4如果装的时候选了latin1或utf8后续存中文会有麻烦。基本查询里最常见的明明有数据却查不到的坑就是字符集不一致导致的。建议装的时候直接指定--character-set-serverutf8mb4 --collation-serverutf8mb4_unicode_ci或者在配置文件my.ini里写死。这个选项决定了你后面所有查询比较的基准。第二个是认证插件。MySQL 8.0 默认用caching_sha2_password而有些比较老旧的客户端或者代码库用的还是mysql_native_password会导致连接时报认证失败。解决办法是创建用户时显式指定CREATE USER demo% IDENTIFIED WITH mysql_native_password BY your_password; GRANT ALL PRIVILEGES ON test_db.* TO demo%; FLUSH PRIVILEGES;如果你只是本地学习用root也可以但生产环境千万别这么干。这些细节属于基本查询的前置条件环境不对后面所有语句都会变得玄学。1.2 建一张订单表字段类型怎么选为了讲查询我建议你自己建一张订单表。这是我在实际项目中反复用过的一种简化结构CREATE DATABASE IF NOT EXISTS demo_db DEFAULT CHARACTER SET utf8mb4; USE demo_db; CREATE TABLE orders ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, user_id INT UNSIGNED NOT NULL COMMENT 用户ID, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0待支付 1已支付 2已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;字段类型的选择会影响查询条件怎么写。比如金额用DECIMAL而不是FLOAT因为浮点数在对账时会产生诡异的小数误差。状态用TINYINT而不是VARCHAR因为数字比较比字符串快而且代码中可以直接定义枚举。时间用DATETIME配合索引可以做范围查询。建好表后插入一些测试数据可以自己写几条INSERT或者用存储过程循环造数据。我习惯用下面的方式快速生成100行测试数据INSERT INTO orders (user_id, amount, status, created_at) SELECT FLOOR(RAND() * 100) 1 AS user_id, ROUND(RAND() * 500, 2) AS amount, FLOOR(RAND() * 3) AS status, DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 90) DAY) AS created_at FROM information_schema.tables LIMIT 100;这个技巧利用了系统表的信息不需要写100条手工数据。有了这些数据后面的查询示例才能出效果。2. 单表查询的骨架SELECT、WHERE、ORDER BY、LIMIT怎么组合单表查询是整个MySQL基本查询的核心所有复杂查询最后都会拆解成单表规则。这一节的执行顺序特别重要——虽然你写SQL时的顺序是SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT但MySQL真正执行的顺序不一样。2.1 从SELECT开始列的选择与别名最基本的是确定查哪些列。SELECT *在测试时没问题但生产环境不建议用因为不需要的列会白白增加网络传输和内存消耗。更合理的写法是明确列出需要的列并且用别名让结果集表头更清晰SELECT user_id AS 用户ID, amount AS 金额, status AS 状态 FROM orders;别名不只是为了显示你在后面的ORDER BY里可以直接引用别名比如ORDER BY 金额 DESC。这是一个很实用的语法糖省得再写一遍表达式。2.2 WHERE过滤运算符、逻辑组合、NULL的特殊性基本查询最常用的过滤条件是WHERE。需要注意的是比较对NULL无效任何与NULL的比较结果都是NULL不会匹配到行。判断空值必须用IS NULL或IS NOT NULLSELECT id, amount FROM orders WHERE status 1 AND amount 100; SELECT id, user_id FROM orders WHERE user_id IS NOT NULL;逻辑组合时我一般会在括号里写明优先级比如WHERE (status 1 OR status 2) AND amount 50。很多人不写括号靠默认优先级撑着一旦条件复杂就出逻辑错误。另外字符串比较时要注意字符集和排序规则。你用的utf8mb4_unicode_ci里的ci表示大小写不敏感所以WHERE user_name abc能查到ABC。如果你想区分大小写可以用BINARY关键字但平时基本用不到。2.3 ORDER BY与LIMIT排序的默认规则和分页陷阱排序规则里最容易踩的坑是NULL的排位。MySQL默认ASC排序时NULL排在最前面DESC排序时NULL排在最后面。如果你希望把NULL沉底用ORDER BY ISNULL(column), column这种技巧SELECT id, amount, status FROM orders ORDER BY ISNULL(amount), amount DESC;LIMIT做分页时也有坑。LIMIT 10, 20的含义是跳过前10条返回之后20条而不是取第10条到第20条。当数据量很大时用大的偏移量做深分页会越来越慢比如LIMIT 100000, 20MySQL要扫描前10万行再丢弃。这也是后面慢查询的一个来源。2.4 一个完整的查询模板在实际工作中我经常用这个模板来快速构建单表查询SELECT column_list, (CASE WHEN status 1 THEN 已支付 ELSE 其他 END) AS status_text FROM orders WHERE filter_conditions ORDER BY sort_conditions LIMIT page_size OFFSET offset;CASE WHEN这种列级转换在基本查询里很好用可以把数字状态转成可读文本避免在Java或Python代码里再写一层转换。不过要注意查询结果集变大时在SQL里做转换并不一定比代码里做更快这个后面讲性能时再展开。3. 聚合与分组COUNT、SUM、AVG、GROUP BY和HAVING的配合聚合函数是基本查询从取数变成汇总的分水岭。很多人在GROUP BY上栽跟头主要是没搞懂它和SELECT列的约束关系。3.1 聚合函数与NULL的关系COUNT(*)统计的是行数COUNT(column)统计的是该列非NULL的行数。这个区别特别重要SELECT COUNT(*) AS total_rows, COUNT(status) AS status_not_null, COUNT(DISTINCT user_id) AS distinct_users FROM orders;SUM(amount)会自动忽略NULL但如果所有行都是NULL结果是NULL而不是0。通常我会用IFNULL(SUM(amount), 0)把结果变成0避免前端拿到NULL之后抛异常。AVG同样忽略NULL所以如果你想把NULL当0参与计算得自己写SUM(amount)/COUNT(*)而不是直接AVG(amount)。3.2 GROUP BY的细节只能select分组列和聚合列MySQL有一个历史遗留的宽松模式默认ONLY_FULL_GROUP_BY是关闭的5.7之后默认开启但很多老库没开。如果开着这个模式SELECT中出现的非聚合列必须在GROUP BY中出现否则直接报错。比如-- 会报错的写法 SELECT user_id, status, SUM(amount) FROM orders GROUP BY user_id; -- 正确的写法 SELECT user_id, status, SUM(amount) FROM orders GROUP BY user_id, status;为什么要这样约束因为当按user_id分组时每个组里可能有多条记录对应不同的状态MySQL不知道你想保留哪一个状态所以干脆规定你只能选分组列和聚合列。如果你确实想取组内某个状态的值可以用MAX(status)或MIN(status)或者用子查询。GROUP BY经常和HAVING一起用用于过滤分组后的结果SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY user_id HAVING COUNT(*) 3 AND SUM(amount) 500;3.3 HAVING与WHERE的区别以及执行顺序执行顺序上WHERE先过滤原始行再进行分组聚合HAVING是在分组聚合之后过滤分组。性能上区别很大能用WHERE过滤掉的绝不要留到HAVING里。比如要查已支付订单的汇总先写WHERE status 1再GROUP BY会让聚合计算的数据量大幅减少如果先GROUP BY再HAVING status 1那status根本没法直接放在HAVING里除非用MAX(status)1这种拐弯写法而且效率也差。一个更隐蔽的问题是SELECT中定义的别名不能直接在WHERE中使用但可以在HAVING中使用MySQL支持。例如SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id HAVING total 1000;这里total在HAVING里可用但若把它放在WHERE里就会报错因为WHERE执行时total还不存在。这个顺序逻辑值得记住日常排查SQL报错非常有用。4. 多表查询JOIN、子查询、UNION的选择逻辑单表查询是基本功但真实业务里很少只查一张表。多表查询里最常用的就是JOIN和子查询。这里不谈复杂的优化器只说怎么选、怎么写不出错。4.1 INNER JOIN vs LEFT JOIN哪边的表决定行数我见过最多的查询错误是JOIN导致结果翻倍或变少。根本原因是没搞清楚驱动表和被驱动表的关系。INNER JOIN只保留两边都匹配的行LEFT JOIN保留左表全部行右表没匹配上用NULL填充。举个例子假设用户表有100个用户订单表只有20个用户的订单-- 返回 100 行取决于每个用户的订单数 SELECT u.user_id, o.amount FROM users u LEFT JOIN orders o ON u.user_id o.user_id;如果某个用户有3笔订单那这个用户就会在结果里出现3行。这就是翻倍的来源。很多人看到行数比预期多以为是重复数据坏了其实是多表连接的自然结果。想要每个用户只出现一行你得提前把订单聚合好再连接SELECT u.user_id, COALESCE(t.total_amount, 0) AS total_amount FROM users u LEFT JOIN ( SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) t ON u.user_id t.user_id;这种先聚合后连接的思路在报表场景中几乎每天都要用。4.2 子查询的三种写法与性能陷阱子查询可以写在SELECT子句、WHERE子句和FROM子句中。SELECT子句里放标量子查询最方便但也最容易忽略一行只能返回一个值的限制SELECT user_id, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.user_id) AS order_count FROM users u;这种写法如果子查询扫描的索引不好会产生逐行子查询相关子查询性能会很差。更好的做法是用LEFT JOIN GROUP BY替代。WHERE子句里常用的IN子查询在数据量大时也要小心SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders WHERE status 1);在MySQL 5.7及以后版本优化器会把IN子查询改写成半连接semi-join效率通常可以接受。但如果子查询结果集特别大或者有复杂的OR条件Exsits和IN的选择就有讲究了。经验法则子查询结果集小但外层表大用IN外层表小但子查询结果集大用EXISTS。现代优化器会自动转换一部分但这个法则依然是理解执行计划的好起点。4.3 UNION与UNION ALL的去重差异UNION用于合并多个查询结果但默认会去重。我项目中几乎总是用UNION ALL原因很简单去重需要排序或哈希操作耗时比简单拼接高得多而且业务上如果两个查询是按条件拆开的通常本身就有重叠保护不需要去重。比如统计昨日订单和今日订单合并展示直接SELECT id, amount, 昨日 AS day_type FROM orders WHERE created_at 2025-01-01 AND created_at 2025-01-02 UNION ALL SELECT id, amount, 今日 AS day_type FROM orders WHERE created_at 2025-01-02 AND created_at 2025-01-03;如果使用UNIONMySQL需要比较所有字段是否相同来去重数据量大时会额外消耗内存和CPU。除非你明确需要合并去重否则一律UNION ALL。这是从成本角度非常实在的建议。5. 去重的艺术DISTINCT、GROUP BY、窗口函数谁更合适热搜词里有sql语句去重查询这是基本查询里一个非常具体又容易出错的点。去重不只是SELECT DISTINCT不同场景有不同的最优解。5.1 DISTINCT的适用范围与限制DISTINCT的语义是对所有SELECT的列组合去重。如果你只关心一列的去重值SELECT DISTINCT column没问题。但如果你想让id唯一同时保留每个id的其他字段就麻烦了。比如我想查出每个用户最新的订单-- 错误示例distinct user_id和id组合后id不同user_id重复达不到目的 SELECT DISTINCT user_id, id, amount FROM orders ORDER BY created_at DESC;DISTINCT只做整行去重无法指定你去重哪一列。此时要用GROUP BY或窗口函数。5.2 GROUP BY实现去重的原理GROUP BY user_id天然会按用户分组每组只留一行你可以用聚合函数控制这一行取什么SELECT user_id, MAX(id) AS latest_order_id, MAX(amount) AS max_amount FROM orders GROUP BY user_id;这种方式能实现按user_id去重但要注意SELECT里非聚合列的限制。如果你要的是该用户的最新一条订单的完整信息用GROUP BY很难保证其它字段是同一个最新订单因为你必须把每个字段包在聚合函数里而MAX(amount)和MAX(id)可能来自不同的行。5.3 窗口函数ROW_NUMBER()去重的实战MySQL 8.0 开始支持窗口函数这才是真正优雅的去重方案。用ROW_NUMBER()给每个用户按时间倒序编号然后取编号为1的行SELECT id, user_id, amount, created_at FROM ( SELECT id, user_id, amount, created_at, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders ) t WHERE rn 1;这个方法的好处是每一行都是真实原始数据而不是聚合后的字段拼凑。即使数据有10个字段也能完整保留。而且窗口函数配合索引有非常成熟的执行计划。缺点是不能在MySQL 5.6/5.7上跑所以如果你的环境是5.7只能用优化后的JOIN子查询来实现同效果。我自己的经验是优先用窗口函数代码可读性比GROUP BY强很多。如果环境不允许就退而求其次在子查询里取最大id再自连接SELECT o.* FROM orders o INNER JOIN ( SELECT user_id, MAX(id) AS max_id FROM orders GROUP BY user_id ) t ON o.id t.max_id;这个方法本质是按user_id去重后回原表取完整行比把所有字段塞进聚合函数靠谱得多。6. 慢查询与锁为什么基本查询也会越跑越慢基本查询写对了不代表查得快。热搜词里出现过慢查询日志和mysql锁的分类说明很多人已经意识到查询性能不是靠猜的。这一节讲基本查询最常见的性能影响因素索引、执行计划、慢查询日志以及锁和事务带来的读取问题。6.1 慢查询日志怎么看MySQL 可以开启慢查询日志把执行时间超过阈值的SQL记录下来slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 2开启后用mysqldumpslow -s at -t 10 /var/log/mysql/slow.log把所有慢查询按平均耗时排序就能看到最需要优化的SQL。我排查了很多同事的问题80%的慢查询都是没有走索引而不是SQL本身语法错误。6.2 索引为什么能加速基本查询所谓索引加速就好比查字典时先找偏旁部首而不是从第一页翻到最后一页。对基本查询来说WHERE、ORDER BY、GROUP BY都会受索引影响。判断一个查询有没有走索引最直接的办法是看执行计划EXPLAIN SELECT id, user_id, status FROM orders WHERE user_id 42 AND status 1;执行计划里type列的排序从好到差依次是system、const、eq_ref、ref、range、index、ALL。如果看到ALL全表扫描而表数据量超过几千行就该考虑建索引了。对于WHERE user_id 42这种等值查询建idx_user_id就能让type变成ref。对于范围查询idx_created_at能让type变成range。复合索引的顺序也很重要。比如WHERE user_id ? AND status ?如果建(user_id, status)联合索引两个条件都能利用如果单独建两个索引MySQL优化器可能会用索引合并也可能只用一个。通用口诀等值排在前面范围排在后面。6.3 锁与事务对查询的影响基本查询在SELECT时默认是非锁定读也就是MVCC多版本并发控制下的快照读所以不阻塞其他事务的写操作。但在REPEATABLE READ隔离级别下如果你在事务里先SELECT再更新可能会出现gap lock影响查询范围。最典型的坑是在事务里做两次相同条件的SELECT得到不同结果——如果第一次SELECT和第二次SELECT之间有其他事务提交了新数据第二次读到的可能是同一快照由于快照读的规则也可能读到新数据如果走的是当前读。想排查锁问题用SHOW ENGINE INNODB STATUS; SHOW PROCESSLIST;可以快速看到正在等待锁的事务和被锁住的SQL。基本查询里最常见的锁等待往往是UPDATE或DELETE操作扫描了很多行导致锁范围扩大后续的普通查询被阻塞。所以别以为只有增删改才关心锁一个范围很大的SELECT在某些隔离级别下也可能引发next-key lock。7. 踩坑记录in语句报错、日期查询返回空、视图查询误区最后这一节我把自己在实战里踩过或帮同事排查过的几个高频坑列出来。这些问题都跟基本查询强相关而且搜索引擎里问得特别多。7.1 IN语句报错到底错在哪热搜词里有in查询语句报错我见过最多的原因是两种。第一种是列表里写了多余的东西比如SELECT * FROM orders WHERE user_id IN (1, 2, 3,); -- 多了尾部逗号MySQL 8.0 对(1, 2, 3,)这种写法通常不会直接给语法错误但某些旧版本或者数据库驱动会报错。第二种更典型——子查询返回了多列SELECT * FROM orders WHERE (user_id, amount) IN (SELECT user_id, amount FROM refunds);这种写法在MySQL 8.0 的IN里是合法的元组比较但如果你写的是WHERE user_id IN (SELECT user_id, amount FROM refunds)那么子查询返回了两列而IN左边的user_id只有一列优化器直接报Operand should contain 1 column(s)。这类报错的本质是子查询列数不匹配。排查时先看SELECT后面到底查了几列别想当然。7.2 日期查询返回空类型和边界都会害人我见过一个业务SQL查当天订单返回空折腾了半天发现是时间格式问题SELECT * FROM orders WHERE created_at 2025-03-15;如果created_at是DATETIME那么它的值是2025-03-15 09:30:00你拿2025-03-15去等值比较MySQL会把字符串转成2025-03-15 00:00:00自然是匹配不到的。正确做法是用范围查询SELECT * FROM orders WHERE created_at 2025-03-15 00:00:00 AND created_at 2025-03-16 00:00:00;这个边界条件一直是面试常考题也是实际里玄学问题的来源。写范围查询时注意右边界一定要用 下一天不要用 当天23:59:59因为如果时间带毫秒你丢掉的最后一条数据会让你半夜爬起来修报表。7.3 视图能加快查询速度吗热搜词里有视图可以加快查询速度吗这是典型的概念混淆。视图View本质是一条保存好的SQL语句它不存储数据查询视图时底层执行的是视图对应的那条定义语句。所以在大多数MySQL版本里视图并不会主动提升查询性能它只提供逻辑封装和安全性。真正让视图变快的原因只有一个如果MySQL把视图并入了外层SQL的优化流程且使用了合适的索引那么可能和直接写底层SQL差不多快但不会更快。我曾见过同事把LEFT JOIN的一个复杂逻辑封装成视图然后在外层再加条件结果执行计划里外层条件并没有下推到视图中导致扫描了数百万行。这种时候更好的做法是直接写一条SQL而不是依赖视图。如果你只是想要复用逻辑可以通过函数或存储过程或者干脆在应用层做SQL模板管理。所以在得出结论视图可以加快查询速度之前先EXPLAIN一下视图对应的底层SQL看看实际执行计划是什么。不要以为套了层壳就能优化性能壳本身不提供性能。说实话MySQL基本查询做到后面你会发现真正的瓶颈往往不在语法而在对数据分布、索引结构、执行计划的理解。我在带人的时候常说先把单表玩透再碰多表最后回来看执行计划。这篇内容覆盖了从环境到常见报错的全链路是基于我在报表系统、订单系统等实际项目中进行MySQL基本查询的通用经验总结。你可以照着建一张订单表把每个例子跑一遍遇到报错就按第7节的思路排查。多跑几次基本查询里的那些小动作就会变成肌肉记忆。