很多人在 MySQL 上折腾了好几年翻来覆去用的其实就那么几条 SQL等真遇到“查得快一点”“把这几个字段拼一下”“按条件统计分组”这类需求时就开始到处搜答案。我自己也带过不少新人发现单表查询看起来简单但要把 WHERE、ORDER BY、GROUP BY、LIMIT 这些组合出花来同时还不踩性能的坑这里面的门道其实不少。这篇文章就把单表查询整个脉络捋一遍从基础语法到执行顺序从条件过滤到分组聚合再到索引优化和常见报错排查帮你一次性搞定。这篇文章适合两类人一是刚入门、写 SELECT 还停留在SELECT * FROM table阶段的初学者二是写了几年 SQL 但没系统梳理过、经常在慢查询和语义错误上栽跟头的开发。内容按“能用 → 用好 → 用快 → 用稳”的顺序展开所有示例都是可直接复制的实战写法。1. 单表查询的核心语法与执行顺序1.1 SELECT 语句的完整结构先看单表查询最完整的语法骨架SELECT [DISTINCT] select_expr, ... [FROM table_references] [WHERE where_condition] [GROUP BY {col_name | expr | position}] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC]] [LIMIT {[offset,] row_count | row_count OFFSET offset}]日常开发中 90% 的查询都跑不出这个框架。很多人写 SQL 是“想到哪写到哪”其实 SQL 跟编程语言一样有固定的执行顺序搞不清这个顺序就容易在逻辑上报错。1.2 执行顺序才是真正的“潜规则”SQL 的书写顺序和执行顺序不一样这是新手最早掉进去的坑。以 MySQL 8.0 为例一条单表查询的执行顺序是FROM确定从哪张表取数。WHERE对行逐条过滤此时 GROUP BY 还没执行所以 WHERE 里不能使用聚合函数。GROUP BY按指定列分组。HAVING对分组后的结果进行过滤这里可以使用聚合函数。SELECT计算并投影需要的列。ORDER BY对最终结果排序。LIMIT截取指定行数。这个顺序解释了三个高频疑问为什么WHERE里不能写WHERE COUNT(*) 1因为执行 WHERE 时还没分组聚合函数根本没法算。为什么HAVING既能过滤分组又能用别名而WHERE不能因为 SELECT 在 HAVING 之前执行HAVING 已经能看到计算后的列了。为什么ORDER BY后面可以写 SELECT 里的别名因为排序发生在投影之后。注意MySQL 8.0.22 之前GROUP BY 默认还允许GROUP BY后面使用 SELECT 中的别名但从 8.0.13 开始部分场景已有变更。实际项目里建议 GROUP BY 一律写原始列名或表达式别依赖别名避免升级数据库后 SQL 突然报错。1.3 写查询前的三个习惯我在写单表查询时无论多简单的语句都会先问自己三个问题我要从哪张表取数确认表名和字段名别想当然。取数范围是什么即 WHERE 条件怎么限定先粗筛还是先细筛。取回来怎么用是要明细、要统计还是要排序分页这个习惯能减少相当一部分“SQL 跑通了但结果不对”的返工。比如你想统计“每个分类的商品数”但 FROM 后面跟错了表后面所有逻辑全白搭。2. WHERE 条件过滤从基础到“花式”写法2.1 基础比较与逻辑组合WHERE 子句是单表查询的重中之重它决定了你能拿回哪些行。最基础的写法就是比较运算符配合逻辑组合SELECT id, name, price, stock FROM product WHERE category_id 5 AND price 100 AND price 500 OR status 1;这里有个经典陷阱AND的优先级高于OR。上面这条 SQL 的实际语义是category_id 5 AND (price 100 AND price 500) OR status 1也就是“分类为 5 且价格在区间内或者状态为 1 的所有商品”。如果你的本意是“分类为 5 且价格在区间内或状态为 1”必须加括号SELECT id, name, price, stock FROM product WHERE category_id 5 AND (price BETWEEN 100 AND 499 OR status 1);优先级这个坑我见过不止一次出现在生产事故里。代码评审阶段看到 OR 和 AND 混用但没加括号的 SQL基本可以直接打回。2.2 模糊查询 LIKE 的正确姿势LIKE 是单表查询里使用频率极高、也最容易拖垮性能的条件之一。SELECT id, name FROM user WHERE name LIKE 张%; -- 前缀匹配可能走索引 SELECT id, name FROM user WHERE name LIKE %张; -- 后缀匹配索引失效 SELECT id, name FROM user WHERE name LIKE %张%; -- 包含匹配索引失效为什么前缀匹配能走索引而后缀匹配不能MySQL 的 B 树索引是按键值顺序排列的前缀匹配相当于在一个有序数组里按范围查找索引天然支持后缀匹配等于要遍历所有索引键做后缀判断优化器只能放弃索引。实际工作中如果业务确实需要模糊搜索且数据量很大我的建议是少量数据的后缀/包含匹配直接 LIKE 扫表可以接受别过度设计。数据量大优先考虑全文索引MySQL 全文索引支持中文要配分词器或者换 Elasticsearch 这类搜索引擎不要硬刚 MySQL LIKE。2.3 IN、BETWEEN 与空值判断SELECT * FROM order WHERE status IN (1, 3, 5); SELECT * FROM order WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31; SELECT * FROM user WHERE phone IS NULL; SELECT * FROM user WHERE phone IS NOT NULL;这里有个特别值得注意的点NULL 是一种状态不是值。phone NULL和phone ! NULL的结果永远是 NULL不会命中任何行。必须用IS NULL/IS NOT NULL判断。同样的坑也出现在 NOT IN 子查询里如果子查询结果中包含 NULL整个 NOT IN 的结果就会是空集。比如SELECT * FROM user WHERE id NOT IN (SELECT user_id FROM blacklist);如果blacklist.user_id中有 NULL这条 SQL 一条数据也查不出来。这属于语义陷阱很多人查了半天没结果最后才发现是 NULL 在作怪。稳妥做法是改成SELECT * FROM user u LEFT JOIN blacklist b ON u.id b.user_id WHERE b.user_id IS NULL;2.4 条件过滤里的“不等于”写法!和都可以表示不等于MySQL 里两者等价。但要注意!通常无法使用索引优化取决于优化器和数据分布尤其当“不等于”过滤掉的数据只占少数时反而走全表扫描更快优化器有自己的判断不用强行干预。真正需要注意的是业务上“不等于”往往还要带上 IS NULLSELECT * FROM user WHERE deleted ! 1 OR deleted IS NULL;很多表设计了逻辑删除位查询有效数据时只写WHERE deleted 0会漏掉deleted为 NULL 的记录如果允许 NULL 的话。在表结构设计阶段就应把这类字段定为NOT NULL DEFAULT 0从源头规避这个坑。3. 排序、分组与聚合统计报表的基石3.1 ORDER BY 的排序规则与多列排序SELECT id, name, price, sales FROM product ORDER BY sales DESC, price ASC;多列排序的执行逻辑是先按第一列排第一列相同的情况下再按第二列排。上面的例子就是“销量高的在前销量相同则价格低的在前”。这在商品列表页、排行榜场景里非常常见。一个隐蔽的坑是排序与字符集/排序规则的关系。MySQL 里ORDER BY默认按列的 collation 排序。如果列是utf8mb4_general_ci排序时是不区分大小写的如果是utf8mb4_bin则区分。碰到“为什么我排序的结果跟字典顺序不一样”的疑问先查列的 collation 设置。3.2 GROUP BY 分组统计的正确打开方式单表查询的分组统计是数据分析的基本功。先看一个典型需求统计每个分类下的商品数量和平均价格。SELECT category_id, COUNT(*) AS cnt, ROUND(AVG(price), 2) AS avg_price, MAX(price) AS max_price, MIN(price) AS min_price FROM product WHERE status 1 GROUP BY category_id ORDER BY cnt DESC;这里要注意COUNT(*)和COUNT(col)的区别COUNT(*)统计分组内的总行数包含 NULL。COUNT(col)统计该列非 NULL 的行数。COUNT(DISTINCT col)统计该列去重后的非 NULL 行数。如果业务上需要统计“有多少个用户下过单”用COUNT(DISTINCT user_id)如果只是统计“有多少条订单记录”用COUNT(*)即可两者语义完全不同混用会导致报表数字对不上。3.3 HAVING 与 WHERE 的分工HAVING 是在 GROUP BY 之后对分组结果做过滤WHERE 是在分组前对明细行做过滤。这个分工决定了两者的使用场景SELECT category_id, COUNT(*) AS cnt FROM product WHERE price 0 GROUP BY category_id HAVING COUNT(*) 10;先通过 WHERE 排除掉price 0的无效数据再对剩余数据分组最后用 HAVING 筛选出“商品数不少于 10 个”的分类。能用 WHERE 过滤的绝不放到 HAVING。因为先过滤再分组每组的数据量更小聚合计算更快也能减少 GROUP BY 的临时表压力。这个习惯在大表上能明显看到性能差异。3.4 聚合函数实战细节常用的聚合函数有COUNT、SUM、AVG、MAX、MIN。有几个细节值得单独拎出来SUM遇到全是 NULL 时返回 NULL而不是 0。需要展示 0 可以用IFNULL(SUM(col), 0)。AVG会自动忽略 NULL但不会忽略 0。如果业务上“除数为 0 的记录”需要平均值为 0要自己预处理。MAX和MIN适用于任何可比较类型包括字符串和日期。日期列取最大最小值很常用SELECT MAX(create_time) FROM user就能拿到最近注册时间。3.5 DISTINCT 的使用边界SELECT DISTINCT category_id FROM product; SELECT COUNT(DISTINCT user_id) FROM order;DISTINCT 在数据量小时没问题数据量大了容易造成临时表过大甚至磁盘临时表。如果只是想去重统计数量用COUNT(DISTINCT col)就够了不要先用SELECT DISTINCT查出所有行再在应用层计数那样网络传输和内存开销都大得多。4. 单表查询的进阶玩法表达式、函数与子查询4.1 使用表达式与 CASE WHEN 加工列查询不仅仅是“原样取列”很多时候需要加工。比如计算商品折扣价SELECT id, name, price, discount, ROUND(price * discount, 2) AS final_price, CASE WHEN discount 0.8 THEN 大促 WHEN discount 0.95 THEN 普通优惠 ELSE 无折扣 END AS discount_level FROM product WHERE status 1;CASE WHEN 是单表查询里非常灵活的工具可以基于多条件生成新的分类列再配合 GROUP BY 做多维统计。比如统计“不同折扣力度的商品数量”SELECT CASE WHEN discount 0.8 THEN 大促 WHEN discount 0.95 THEN 普通优惠 ELSE 无折扣 END AS discount_level, COUNT(*) AS cnt FROM product WHERE status 1 GROUP BY discount_level;这里有个细节MySQL 允许GROUP BY case_level这样的别名写法但在其他数据库里不一定支持。为了可移植性建议 GROUP BY 子句里把 CASE WHEN 完整写一遍而不是用别名。4.2 常用的字符串与日期函数单表查询里高频使用的函数我整理一批字符串函数CONCAT(a, b)拼接字符串。SUBSTRING(str, pos, len)截取子串。LEFT(str, n)/RIGHT(str, n)从左侧/右侧截取。LENGTH(str)返回字节数注意中文在 utf8mb4 下占 3 字节这是初学者最容易蒙圈的地方。CHAR_LENGTH(str)返回字符数统计中文长度用这个。REPLACE(str, old, new)替换字符串。TRIM(str)去掉首尾空格。UPPER(str)/LOWER(str)转大写/小写。日期函数NOW()当前日期时间。CURDATE()当前日期。DATE_FORMAT(date, %Y-%m-%d)格式化日期非常常用。DATE_ADD(date, INTERVAL 1 DAY)日期加减。DATEDIFF(d1, d2)两个日期相差天数。YEAR()/MONTH()/DAY()提取年/月/日。比如按月份统计订单数SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE create_time 2024-01-01 GROUP BY DATE_FORMAT(create_time, %Y-%m) ORDER BY month;注意对create_time使用DATE_FORMAT后这列的索引就基本无法利用了。如果按月份分组的数据量很大建议在表里冗余一个month_key字段或者查询条件用范围过滤而不是函数格式化。这是典型的“写起来省事、跑起来费劲”的写法要权衡取舍。4.3 把字符串转成日期的几种姿势热词里专门提到“mysql将字符串转为日期”实际项目里外部导入的数据常常是字符串日期。MySQL 提供了几个转换方法-- 严格转换格式不对直接报错 SELECT STR_TO_DATE(2024-01-15, %Y-%m-%d); -- 自动解析格式必须符合默认标准 SELECT CAST(2024-01-15 AS DATE); -- 隐式转换比较时 MySQL 自动把字符串转日期 SELECT * FROM orders WHERE create_time 2024-01-15;STR_TO_DATE最灵活因为可以指定任意格式比如2024/01/15 14:30:00对应格式%Y/%m/%d %H:%i:%s。这里提示一句WHERE create_time 2024-01-15这种写法里字符串会被隐式转换前提是字符串格式是YYYY-MM-DD之类的标准格式。如果是2024/01/15MySQL 8.0 里也可能出问题最好先用STR_TO_DATE转换为 DATE 类型再比较避免歧义。4.4 单表子查询当成“临时表”用单表查询里的子查询通常是把子查询的结果当成一个临时数据源外层再对它查询。典型场景是求“每个分类下价格最高的商品”SELECT id, name, price, category_id FROM product p WHERE price ( SELECT MAX(price) FROM product p2 WHERE p2.category_id p.category_id );这种写法叫作相关子查询内层查询引用外层表的列。它的执行方式是逐行判断数据量大时性能往往不好。更好的写法是SELECT p.id, p.name, p.price, p.category_id FROM product p INNER JOIN ( SELECT category_id, MAX(price) AS max_price FROM product GROUP BY category_id ) t ON p.category_id t.category_id AND p.price t.max_price;先算出每个分类的最高价再通过 JOIN 把明细行匹配出来。两者逻辑等价但第二种写法在多数场景下性能更稳因为子查询只跑一次外层 JOIN 还能借用索引。这也是我日常写 SQL 的一个偏好能用 JOIN 表达集合逻辑的尽量少用相关子查询。4.5 LIMIT 分页的深水区LIMIT 的语法很简单但分页深度是个隐藏问题SELECT * FROM user ORDER BY id LIMIT 0, 20; -- 第1页 SELECT * FROM user ORDER BY id LIMIT 20, 20; -- 第2页 SELECT * FROM user ORDER BY id LIMIT 1000000, 20; -- 第50001页性能差LIMIT 的 offset 越大MySQL 需要扫描并丢弃的行越多。LIMIT 1000000, 20实际上要扫描 1000020 行然后丢掉前 1000000 行代价极高。深分页的常见优化方案是延迟关联SELECT u.* FROM user u INNER JOIN ( SELECT id FROM user ORDER BY id LIMIT 1000000, 20 ) t ON u.id t.id;子查询里只查id列扫描和排序的代价大幅下降再用 id 关联回原表取出完整行。这在只查 id 能走覆盖索引的场景下提升非常明显。5. 性能优化让单表查询“飞”起来5.1 索引是提速的第一手段单表查询的性能瓶颈绝大多数都能靠索引解决。索引的本质是排好序的数据结构它让 MySQL 不再全表扫描而是通过 B 树快速定位。创建索引的语法CREATE INDEX idx_product_category ON product(category_id); CREATE INDEX idx_product_cat_price ON product(category_id, price); CREATE INDEX idx_user_phone ON user(phone);给查询条件列建索引是最基础的优化。但索引不是越多越好——每个索引都是“额外的一份有序副本”写入数据时要同步维护索引太多反而拖慢 INSERT/UPDATE。一个经验法则是查询中频繁使用的 WHERE 条件列优先建索引。组合索引有“最左前缀原则”(category_id, price)能加速WHERE category_id ?和WHERE category_id ? AND price ?两类查询但不能直接加速WHERE price ?。区分度低的列如 status 只有 0/1建索引意义不大优化器大概率还是选全表扫描。5.2 最左前缀原则与索引失效场景组合索引(a, b, c)可以匹配的查询条件如下查询条件是否可用索引说明a ?可用走了索引第一列a ? AND b ?可用走了前两列a ? AND b ? AND c ?可用全列匹配b ?不可用跳过了第一列a ? AND c ?部分可用只用 a 列过滤c 在索引内过滤不了a IN (...) AND b ?可用MySQL 8.0 对 IN 有优化较复杂索引失效的常见场景要背下来对列进行函数运算如WHERE DATE(create_time) 2024-01-15。隐式类型转换如WHERE phone 13800138000phone 是 varchar 但用了数字比较。LIKE 通配符开头如WHERE name LIKE %张。OR 连接非索引列如WHERE id 1 OR name 张除非两个条件都走索引。5.3 覆盖索引与回表覆盖索引指查询的列全部在索引中不需要回表读取数据行这是查询性能的重要优化方向。比如-- 只需要 id 和 category_id 两个列 SELECT category_id, COUNT(id) FROM product WHERE category_id 5 GROUP BY category_id;如果存在(category_id, id)组合索引这条 SQL 可以完全在索引上完成不需要回表。日常排查慢查询时看EXPLAIN输出里的Extra字段如果出现Using index就说明走了覆盖索引出现Using filesort则说明排序没走到索引。5.4 EXPLAIN 实测案例写任何一条“感觉会慢”的查询我都会先放EXPLAIN前面走一遍EXPLAIN SELECT id, name, price FROM product WHERE category_id 5 AND price 100;输出结果里重点关注几列type访问类型从好到差依次是systemconsteq_refrefrangeindexALL。看到ALL基本就是全表扫描。possible_keys可能用的索引。key实际用的索引。rows预估扫描行数这个数越小越好。Extra额外信息Using filesort、Using temporary都是要警惕的信号。我自己的习惯是单表查询如果预估rows超过表行数的 10%或者Extra出现Using temporary就停下来重新审视 SQL而不是直接把线上跑挂。5.5 什么时候不该用索引索引不是万能药。比如表只有几百行全表扫描比走索引还快因为走索引要额外做一次回表反而增加 IO。另外频繁更新的列不适合建索引更新索引本身的成本可能比查询收益还大。生产环境里我见过有人把每个列都建了索引结果一张表二十几个索引插入一次耗时翻了几倍这就是典型的“过度索引”。一个务实的做法先用真实数据量压测找到慢查询再针对慢查询建索引而不是凭想象提前建一堆索引“以防万一”。6. 单表查询的常见坑与排查技巧实录6.1 查不出数据先把 WHERE 拆开“为什么我查不到数据”是我被问过最多的问题。排查思路是按“层层剥洋葱”的顺序先不加 WHERE 查全表确认数据在不在。逐步添加条件每次加一个看哪一层过滤后结果开始为空。重点检查字符串两边是否有空格、大小写是否精确、NULL 是否参与判断。检查是否混淆了与后者才是 NULL 安全等于。我踩过最典型的一次导入历史数据时手机号列被存成13800138000末尾带空格前端查询传的是13800138000WHERE phone 13800138000永远查不到。最后用WHERE TRIM(phone) 13800138000排查出来了。这也提醒我入库前做好数据清洗能省掉后面无数个“查不到”的深夜里“。6.2 排序结果“不正常”如果ORDER BY出来的顺序觉得“不对”先确认排序字段的数据类型。常见问题列是 varchar 类型但存的是数字排序结果会是1, 10, 100, 2, 20这种字典序。中文排序依赖 collationutf8mb4_general_ci下的中文排序结果可能不符合拼音预期需要ORDER BY CONVERT(name USING gbk)才能按拼音排。多列排序里某列升降序写反了。有个小经验排序字段如果有 NULL 值ORDER BY ASC 时 NULL 排在最后ORDER BY DESC 时 NULL 排在最前跟大多数业务预期刚好相反。需要让 NULL 固定排最后可以写成SELECT * FROM user ORDER BY ISNULL(phone), phone;6.3 GROUP BY 查询结果只显示一部分SQL 模式开了ONLY_FULL_GROUP_BY时MySQL 5.7 及以后默认开启SELECT 的列必须是 GROUP BY 列或聚合函数。-- 在 ONLY_FULL_GROUP_BY 下报错 SELECT id, name, COUNT(*) FROM product GROUP BY category_id;id和name既不分组也不聚合MySQL 会直接报错。业务上如果要“每个分类下面的一条商品信息”就不能这么写。常见解法是用聚合函数取一个比如MAX(id)或者用上面提到的 JOIN 子查询方案。这条限制刚上线时坑了不少老项目因为旧版本默认没开这个模式。6.4 慢查询从哪查起排查慢 SQL 的完整链路我总结为四步打开慢查询日志定位long_query_time比如 1 秒。对慢查询语句加EXPLAIN看type、rows、Extra。看是否缺索引或索引使用错误按需加索引。看是否可以把SELECT *改成只查需要的列减少回表和网络传输。-- 查看当前慢查询日志配置 SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; -- 动态开启慢查询日志重启失效生产慎用 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;6.5 一个真实的生产排查案例我给读者还原一个之前经手的实际场景一张订单表数据量约 300 万行。业务反馈某统计页面打开要 5 秒多SQL 如下SELECT customer_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-06-30 AND status PAID GROUP BY customer_id ORDER BY total_amount DESC LIMIT 50;EXPLAIN 结果里type ALLrows 3000000Extra 显示Using temporary; Using filesort。问题很清楚status列区分度低索引价值不大。create_time上有单列索引但加上status过滤后优化器评估后还是走了全表扫描。最终方案创建组合索引(status, create_time)同时把create_time BETWEEN的范围再结合业务压缩近一个月。调整后type变成range扫描行数从 300 万降到 30 万左右页面响应从 5 秒降到 0.4 秒。这类优化没有玄学就是一步步用 EXPLAIN 定位“慢在哪”再决定“怎么改索引”和“怎么改 SQL”。6.6 通往查询“快、准、稳”的四个检查清单最后分享一个我自己整理的单表查询检查清单每次上线 SQL 前过一遍正确性WHERE 条件的逻辑组合是否正确括号有没有加到位NULL 判断用没用到IS NULL。语义COUNT(*)与COUNT(col)用得对不对HAVING 是否能用 WHERE 替代。性能EXPLAIN 里type是否还是ALLExtra里有没有Using temporary/Using filesort。健壮性对列做函数运算有没有必要排序字段的 collation 是否符合预期深分页有没有替代方案。这个清单我用了很多年配合慢查询日志基本能把 95% 的单表查询问题拦在上线之前。MySQL 单表查询看着简单真正写好、写快、写稳靠的是对执行顺序、索引原理和数据类型的组合理解。少从网上复制“能跑”的 SQL多花时间琢磨一下为什么慢、为什么错长期下来收益非常大。