做了这么多年数据库相关的开发和问题排查我越来越觉得SQL真正见功底的地方不在多少层JOIN和花哨的窗口函数而在最普通的MySQL单表查询上。单表查询听起来基础可线上慢查询、统计报表对不上账、新人对着一条SELECT发呆问题大多都堆在这一层。这篇文章想把单表查询这条线完整捋一遍SQL执行顺序、WHERE条件里的隐藏坑、聚合分组的统计逻辑、ORDER BY/LIMIT/DISTINCT的性能代价以及EXPLAIN和索引怎么配合使用。适合刚入门SQL的读者也适合写过几年SQL但从没认真抠过细节的开发者后者很可能在下面每一节里找到自己曾经踩过的坑。1. 看懂一条SELECT的执行顺序比背语法更重要1.1 书写顺序和执行顺序不是一回事大部分SQL教程会把SELECT语法写在最前面导致很多初学者默认数据库是从SELECT开始工作的先挑列再读表再过滤。这是一个根深蒂固的误解。MySQL处理一条单表查询时实际执行顺序大概是执行顺序阶段干了什么事1FROM确定从哪张表读数据2WHERE对原始行逐行过滤3GROUP BY把过滤后的行按分组键分组4HAVING对分组后的结果继续过滤5SELECT计算要返回的列和表达式6ORDER BY对结果集排序7LIMIT只取指定行数理解这个顺序最粗暴的方式是做菜先去菜市场买菜FROM把烂叶子摘掉WHERE把菜按种类分堆GROUP BY再看哪一堆符合今晚菜谱的标准HAVING最后装进盘子SELECT按照上菜顺序摆好ORDER BY服务员一次只端出去几盘LIMIT。这个顺序会直接决定你能写什么、不能写什么很多看起来“莫名其妙”的报错回到执行顺序上就一目了然。1.2 WHERE里用别名为什么会报错先看一个几乎每个新人都踩过的场景SELECT user_name, age 1 AS next_age FROM user_info WHERE next_age 20;这段SQL会报错Unknown column next_age in where clause。很多人不理解我明明已经在SELECT里定义好了next_age为什么WHERE不认因为按照上面的执行顺序WHERE在第2步SELECT在第5步。WHERE开始执行的时候next_age这个别名压根还不存在MySQL当然不知道你在说什么。反过来ORDER BY就可以使用别名SELECT user_name, age 1 AS next_age FROM user_info ORDER BY next_age;因为ORDER BY在SELECT之后执行别名已经生成完毕自然可以引用。这里有个规律值得记死SELECT阶段可以看作是整条SQL的“分水岭”分水岭之前不能碰别名分水岭之后才允许。提示如果你在过SQL时看到Unknown column ... in where clause第一反应先检查是不是在WHERE里用了SELECT别名不要急着怀疑表结构。实际写代码时过滤条件直接写成原始列表达式就好WHERE age 1 20。不过要注意另一层坑如果对列做了计算比如WHERE age 1 20MySQL很可能无法使用age列上的索引能改写为WHERE age 19就改写既保证逻辑等价又给优化器留了走索引的空间。这个习惯在单表查询里越早养成越好和数据量大了之后再去拆SQL完全是两个成本。2. WHERE条件里的细节坑NULL、隐式转换与字符匹配2.1 NULL不是“没有值”而是“未知值”在MySQL里NULL不等同于空字符串也不等同于0它表示“未知”。这导致一个非常经典的反直觉行为SELECT * FROM user WHERE name NULL;这条SQL不会报错但它永远查不到任何数据。因为NULL与任何值做比较结果既不是TRUE也不是FALSE而是UNKNOWN。WHERE只接受TRUE的结果UNKNOWN直接被扔掉了。正确的写法是SELECT * FROM user WHERE name IS NULL;这个知识点经常被忽略是因为很多开发者在代码里判断null用的是等号比如Java里name null是合法的于是把同样的思维搬到了SQL里。SQL里没有只有但NULL不能用比较。最容易让人困惑的是“排除某个值但保留NULL”的场景想表达的意图常见错误写法正确写法字段为空值column NULLcolumn IS NULL字段不为空值column ! NULLcolumn IS NOT NULL排除状态1但要保留NULL行column ! 1column 1 OR column IS NULL第三行值得多说一句。如果status字段允许NULL你写WHERE status 1MySQL只会返回那些能明确判断为“不等于1”的行NULL行因为比较结果是UNKNOWN照样被过滤掉。结果就是数据凭空少了一批排查起来非常恼火。我现在的习惯是设计表的时候凡是能用默认值表达的字段一律NOT NULL DEFAULT xxx从源头减少NULL带来的三值逻辑问题。查询语法可以在写SQL时临时改表结构里的NULL一旦混进来所有过滤、统计、索引都会变得麻烦。2.2 隐式类型转换会让索引悄悄失效你有多少年没仔细看过字段类型了做单表查询时最容易忽视的问题不是语法错误而是类型不匹配带来的隐式转换。举个例子user表里的mobile字段是varchar类型存储的是13812345678这种手机号。很多人图省事写成SELECT * FROM user WHERE mobile 13812345678;MySQL在处理这个比较时会把mobile列里的字符串转换成数字再比较本质上相当于执行了SELECT * FROM user WHERE CAST(mobile AS SIGNED) 13812345678;一旦列被函数或者类型转换包裹mobile列上的索引就发挥不出作用了查询退化成全表扫描。表小的时候感觉不到表里几十万行之后这就是慢查询的源头之一。更反直觉的是字符串和数字比较时MySQL的“宽容”SELECT abc 0;猜猜结果是什么不是0也不是报错而是1。MySQL尝试把abc转成数字转不过去就当作0处理于是abc等于0成立。这意味着如果你用数字0去过滤一个varchar列那些存着非数字字符串的行说不定也会被匹配上结果集里混入奇怪的数据。所以写WHERE条件之前先看一眼字段类型字符串就老老实实加引号。用ORM的时候也一样——有些框架的自动类型转换会悄悄把字符串参数转成数字遇到慢查询时不妨先EXPLAIN看type是不是ALL再回头检查查询参数的真实类型。2.3 LIKE、IN和BETWEEN的边界问题字符串模糊匹配是单表查询里最常见的需求但LIKE不是什么时候都能用索引的。LIKE 张%这种前缀匹配只要字符集排序规则允许优化器有机会走索引范围扫描。LIKE %张或者LIKE %张%这种前后都带通配符的写法索引基本帮不上忙因为数据库无法从索引的第一个字符开始定位只能把所有行的值取出来做匹配。我在实际项目里看到过不少后台系统搜索用户昵称直接LIKE %关键词%数据量在十万以内还能忍一旦上了百万这条SQL就是数据库的主要压力来源。业务如果真的需要低频中间模糊搜索小表可以硬扛大表就该考虑全文索引或者更重一点的搜索方案而不是继续压在线SQL上。IN也有一个隐藏的“反直觉”很多人以为WHERE id IN (3, 1, 2)查出来的数据会按照3、1、2的顺序返回实际上MySQL根本不保证这个顺序没写ORDER BY时返回顺序完全由执行计划决定可能和表扫描顺序有关也可能和索引读取路径有关。想要按自定义顺序返回可以用ORDER BY FIELD(id, 3, 1, 2)但FIELD函数对索引不友好只适合小结果集。BETWEEN同样有边界陷阱。MySQL的BETWEEN是包含两端边界的BETWEEN 2025-01-01 AND 2025-01-02对DateTime字段来说会把2025-01-02 00:00:00这一瞬间也包含进去但不会包含1月2日白天的数据。很多人以为是在取“1月1日和1月2日这两天”结果统计出来的数据少一截。更稳妥的日期范围写法是WHERE created_at 2025-01-01 AND created_at 2025-01-03用左闭右开区间既包含1月1日全天也包含1月2日全天逻辑清晰不会受“边界到底是零点还是晚上12点”的困扰。3. GROUP BY与聚合函数统计报表别偷偷少了一行3.1 聚合函数遇到NULL的行为不一样GROUP BY和聚合函数是单表查询里用来做统计的核心武器但很多人对COUNT、SUM、AVG在NULL面前的行为掌握得比较模糊。SELECT COUNT(*), COUNT(amount), COUNT(1) FROM orders;先说这三者的区别COUNT()统计的是表中的行数不管某列是否为NULLCOUNT(1)和COUNT()逻辑上等价COUNT(amount)则只统计amount不为NULL的行。如果orders表有1000行amount字段里有100个NULLCOUNT(amount)返回的是900而不是1000。SUM和AVG也遵循同样的规律自动跳过NULL行。这个机制看起来贴心但统计时很容易出错。我之前核对过一批线上支付数据SUM(amount)的结果和渠道账单总是差那么几十笔排查了半天发现是支付金额字段在早期版本里没做非空约束有部分退款记录根本没写入金额NULL被SUM直接忽略报表自然少了一截。另外注意一个细节如果某组数据全为NULLSUM(amount)返回的不是0而是NULL。业务里做合计展示时最好习惯性包一层处理SELECT IFNULL(SUM(amount), 0) AS total_amount FROM orders;先统计再看空值比出了结果再四处找反而省事。3.2 WHERE和HAVING分工别搞混“统计每个用户支付订单数超过3单”是一个特别常见的需求SELECT user_id, COUNT(*) AS paid_cnt FROM orders WHERE order_status paid GROUP BY user_id HAVING paid_cnt 3;这条SQL的执行路径是先通过WHERE把所有非支付订单过滤掉然后按user_id分组统计每个用户的订单数最后用HAVING把订单数不足3的用户组扔掉。每一步职责非常清晰。但有些初学者会把WHERE能干的活交给HAVING比如SELECT user_id, COUNT(*) AS paid_cnt FROM orders GROUP BY user_id HAVING order_status paid AND COUNT(*) 3;这条SQL大概率会报错因为HAVING是在分组之后过滤组分组时order_status根本没保留成可过滤的普通行。即使某些写法能通过性能也很差因为HAVING跑在临时分组结果之上没有办法像WHERE那样利用索引过滤原始行。判断标准很简单凡是针对原始行的过滤条件一律放在WHERE凡是针对分组后的统计结果才放在HAVING。另外HAVING是在SELECT之后执行的所以HAVING里可以使用SELECT别名这一点和WHERE恰恰相反是我区分两者时的记忆抓手。3.3 ONLY_FULL_GROUP_BY模式下为什么报错反而是好事有同事一脸愁容地跟我抱怨说SQL明明在旧环境跑得好好的换了新环境就报错看了下大概是这样的SQLSELECT user_name, order_id, COUNT(*) FROM orders GROUP BY user_id;报错信息指向sql_mode里的ONLY_FULL_GROUP_BY。这个模式默认开启它的要求是SELECT后出现的非聚合列必须全部出现在GROUP BY里。换句话说user_name和order_id都选出来之后分组只按user_id分可一个user_id对应的用户组里有多个user_name、多个order_idMySQL到底该输出哪一个旧环境没开这个模式时MySQL会随机从组内挑一行返回结果不确定。表面上看“好像能跑”实际上每次执行可能返回不一样的数据线上数据一多这种不确定就是事故。所以我一直认为ONLY_FULL_GROUP_BY报错是好事它在保护你而不是在刁难你。正确做法有几种如果确实想按user_id分组但也想知道组内每个用户的名字那就把user_name也放进GROUP BY如果没有特别的分组需求只是想把每个用户的订单数量查出来那SELECT里就不要带order_id如果只是想要某个组内代表值可以用MAX(user_id)或者MIN(user_id)这种明确语义的聚合函数。千万不要为了图省事去改sql_mode把ONLY_FULL_GROUP_BY关掉那等于给自己埋了一颗不确定性的雷。4. ORDER BY、LIMIT和DISTINCT取数页面的性能陷阱4.1 LIMIT深分页为什么越翻越慢后台列表分页是单表查询最常见的形态很多人习惯了LIMIT offset, size的写法比如SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;这条SQL看起来只是“跳过前100万行取20行”但MySQL的实际执行方式非常笨拙它会把前1000020行全部读出来再丢掉前面的1000000行最后只返回20行。随着页码越来越大扫描的无关数据越来越多查询速度自然越来越慢。比较实用的优化思路有两种。第一种是“上一页结尾定位”SELECT * FROM orders WHERE id 1000000 ORDER BY id LIMIT 20;应用层记住上一页最后一条记录的id把LIMIT offset转换成WHERE id last_id让数据库直接顺着主键往后扫20条效率高得多。这个方案要求排序字段稳定如果排序用了created_at而created_at有重复建议ORDER BY created_at, id这样加一个唯一字段兜底。第二种是“子查询先拿主键再回表取数据”SELECT * FROM orders WHERE id ( SELECT id FROM orders ORDER BY created_at, id LIMIT 1 OFFSET 1000000 ) ORDER BY created_at, id LIMIT 20;子查询只扫描主键和排序字段避免了前面100万行的大字段读取再通过主键定位跳过去最后只取一页数据。这里的核心思想是能只查索引就不回表能少读大字段就少读。4.2 ORDER BY不是永远都要“排序”单表查询里排序也可能成为性能瓶颈。注意一个细节ORDER BY不是一定非要额外排序的如果排序字段有索引MySQL可以直接按索引顺序读取数据天然就是有序的如果没有合适的索引MySQL就得把所有候选行放进排序缓冲区处理EXPLAIN里会出现Using filesort。我第一次看到Using filesort时被吓到以为干了什么重活。其实filesort优先使用内存中的排序缓冲区只有数据量超过sort_buffer_size时才会落盘但无论如何额外的排序步骤在小表上没感觉大表上就是实打实的时间开销。优化思路不是看到排序慢就单独给排序字段建索引而是要把过滤条件和排序条件放在一起考虑。举个例子后台订单列表最常见的查询是WHERE status paid ORDER BY created_at DESC这时候建联合索引(status, created_at)既能用status快速过滤也能让created_at按顺序输出过滤和排序一起解决。单独建status索引、再单独建created_at索引效果往往不如一个联合索引。4.3 DISTINCT去重到底贵在哪DISTINCT看起来很简单就是去掉重复行但它内部通常需要通过排序或者临时表来识别重复数据。数据量一大这个操作就不便宜。SELECT DISTINCT status FROM orders WHERE created_at 2025-01-01;如果status列上没有合适的索引MySQL要把满足条件的行的status全部捞出来再去重。如果字段本身是varchar且比较长临时表里的数据量会非常大内存放不下就落盘查询直接拖慢。业务上如果只是需要知道订单有哪几种状态而且状态本身是业务枚举值其实可以考虑让应用层缓存一份没必要每次都跑DISTINCT。至于COUNT(DISTINCT user_id)这种去重计数在小表上没有问题大表上的代价会成倍上涨要提前想清楚要不要做预聚合或者换一种近似统计方案。单表查询这条路上很多坑不是语法难而是“功能实现了性能没了”。5. 优化不是玄学EXPLAIN和索引设计的基本功5.1 EXPLAIN里最值得看的四列SQL慢不慢不能靠感觉要拿证据。MySQL给的最直接工具就是EXPLAIN。EXPLAIN SELECT id, mobile FROM user WHERE mobile 13812345678;执行结果里列很多我最常盯的是四列列名关注点type访问类型从好到差大致是 system const eq_ref ref range index ALLkey实际用到的索引如果是NULL说明没走索引rows优化器估计的扫描行数是估算值不是精确值Extra出现Using filesort、Using temporary时重点排查排查一条线上慢查询时我习惯先看type是不是ALL。ALL代表全表扫描基本可以立刻断定这条SQL没吃上索引再看Extra里有没有Using filesort和Using temporary前者代表额外排序后者代表额外临时表都是需要优化的信号。rows虽然是估算值数量级很有参考意义。一条SQL预估扫100行和预估扫100万行就算扫描行数不完全准确优化方向也完全不同。5.2 覆盖索引为什么我劝你少用SELECT *覆盖索引是单表查询优化里性价比很高的概念。简单说如果查询需要的所有列都能在索引里找到MySQL就不需要回表读完整行数据。比如user表上有索引(mobile, id)执行SELECT id, mobile FROM user WHERE mobile 13812345678;MySQL只需要在索引里匹配一次就能拿到id和mobile不需要再回表。但如果你写SELECT * FROM user WHERE mobile 13812345678;MySQL不得不在索引定位到对应主键后再回表把整行读出来。InnoDB的二级索引叶子节点存的是主键值回表意味着额外的磁盘IO。表越宽、字段越多回表成本越明显。所以我在代码评审里看到SELECT *会劝人改掉不只是习惯问题。明确写出需要的字段既可能让查询命中覆盖索引也能减少网络传输的数据量对单表查询来说是一个低投入、高回报的习惯。5.3 复合索引的最左前缀原则不是每个查询都要建一个索引单表查询的索引匹配绕不开最左前缀原则。一个联合索引(a, b, c)能服务的查询条件是只查a查a和b查a、b、c但查询条件如果以b开头或者只查b和c这个联合索引基本帮不上忙。这个原则决定了联合索引的字段顺序不能拍脑袋定必须贴合真实查询模式。拿订单列表来说如果高频查询是WHERE status paid AND created_at 2025-01-01 ORDER BY created_at DESC建索引(status, created_at)就很合适status等值匹配走索引created_at在status相同的前提下天然有序连ORDER BY的排序都省了。反过来如果只知道WHERE created_at 2025-01-01却不带status条件(status, created_at)联合索引就无法发挥全部作用。这时候要么调整索引字段顺序要么接受全表扫描或另建索引。索引不是万能药也不是越多越好每个索引都会占用写入和存储成本。正确做法是收集线上真正高频的WHERE和ORDER BY组合针对性设计联合索引而不是每看到一个WHERE就顺手加一个索引。这些内容如果一口气消化不完我建议先只记住一个动作把自己项目里最常跑的几条单表查询一条条EXPLAIN过去挨个对照执行顺序和索引原理过一遍。我在实际工作里见过太多慢查询和诡异数据最后定位下来都是文里这些“基础”问题——NULL判断、隐式类型转换、深分页、乱加索引。单表查询不复杂但它像四合院的院子院子扫不干净屋里再华丽的JOIN也只是把脏东西堆到了别处。
