做后端开发这些年我见过太多新手写SQL只停留在“能用”的层面SELECT、WHERE、ORDER BY这些关键字都认识真到业务里却总是卡壳要么查询结果不对要么数据一多就慢得要命要么写出来的SQL自己都看不懂。其实MySQL单表查询是最值得花时间打牢的基础它不涉及JOIN、子查询这些复杂概念却能把你对SQL的理解从“语法层面”拉到“执行层面”。这篇文章就围绕单表查询把条件过滤、排序分组、聚合统计、常用函数、执行计划这些核心点一个一个拆开讲配合可直接复制的SQL示例和我在实际项目中踩过的坑。无论你是刚入门的新手还是写SQL多年但没系统梳理过的老手这篇都能帮你把单表查询这块补扎实。1. 单表查询的设计思路为什么它是SQL能力的基石很多人觉得单表查询太简单不值得专门研究直接上手JOIN和子查询才是“高级”。这个想法我特别不赞同。单表查询是所有SQL能力的底盘连单表都写不顺手多表关联只会更乱。更重要的是单表查询里藏着MySQL执行SQL的底层逻辑查询优化器怎么选索引、条件怎么写才能命中索引、聚合和排序怎么在内存里完成这些底层机制在单表场景下最容易看清也最容易实验验证。1.1 单表查询到底在解决什么问题——数据检索的三大需求单表查询表面上就是“从一张表里取数据”但把它拆开其实在解决三类需求。第一类是行筛选从表里捞出的数据要满足什么条件。WHERE子句干的就是这件事它决定结果集里包含哪些行。比如查订单表里今天下的单查用户表里注册时间超过一年的用户本质都是行筛选。第二类是列投影结果集里要保留哪些字段。SELECT后面的列清单干的就是这件事。同样一张用户表后台列表页只需要id、昵称、头像不需要身份证号、密码哈希列投影就把这些敏感字段过滤掉既省网络传输又安全。第三类是汇总计算不关心具体每一行只关心整体特征。比如总共有多少订单、销售额最高的月份是几月、每个城市的用户数分布。这里就需要聚合函数加GROUP BY组合来搞定和前面两类完全不同。把这三大需求分开理解很重要。很多SQL写错不是语法不会而是没分清楚当前业务到底是行筛选、列投影还是汇总计算混在一起自然写不对。1.2 查询优化器的工作方式写SQL前先懂执行计划MySQL拿到一条SELECT语句后并不会按照你写的SQL从左到右机械执行而是先交给**查询优化器Optimizer**做一番“筹划”。优化器会尝试各种执行路径走哪个索引、还是全表扫描、先过滤还是先排序、能不能用覆盖索引直接返回数据然后算出一个它认为成本最低的方案最后才真正去存储引擎里取数。这解释了为什么“同样结果的SQL写法不同性能天差地别”。优化器不是魔法师它主要靠统计信息表的行数、索引的区分度和代价模型来做判断但统计信息可能过期代价模型也可能不准确所以同样一条查询优化器选错执行计划的情况并不罕见。因此写单表查询时有个基本原则先想清楚这条SQL会被优化器翻译成什么执行过程而不是先想SQL长什么样。比如你在WHERE里写了WHERE YEAR(create_time) 2024你觉得挺直观但优化器看到的是“create_time上用了函数索引失效只能全表扫描”因为索引是按照原始值建立的B树函数调用相当于把每一行的值都加工一遍没法直接走索引定位。关于这类问题的排查方法后面专门讲执行计划的时候会详细展开。总之单表查询不是“写出来就行”而是“写出来并且能让优化器高效执行才行”。2. SELECT基础与条件过滤写对WHERE比写对SELECT更重要单表查询里最容易出错、也最影响性能的部分不是SELECT后面的列清单而是WHERE后面的条件。条件写得对不对决定结果集对不对条件能不能走索引决定查询快不快。这一节先把SELECT和WHERE的基础用法完整过一遍重点讲那些文档里容易一笔带过、实际却天天踩坑的细节。2.1 SELECT子句星号与列清单的距离先聊最简单的SELECT。很多人图省事习惯直接写SELECT * FROM users在本地调试没问题但上了生产环境我不建议这么干原因有三第一明确列出列名能让执行计划更稳定。MySQL优化器在处理SELECT *时需要先解析表结构把所有列都展开虽然这个开销不大但确实多了一步。更重要的是如果表结构变动比如新增了一个很大的TEXT字段SELECT *会把新字段也带出来结果集变大网络传输变慢而明确列清单不会受表结构变动影响。第二列清单对索引覆盖有帮助。如果查询只需要id和status而status字段恰好有联合索引(id, status)那MySQL可以直接从索引里取数不需要回表。但如果你写SELECT *索引里没有的列就必须回表去数据页里取性能立刻下降。第三EXPLAIN分析更清晰。看执行计划时明确知道查询需要哪些列更容易判断Extra列里有没有出现Using index覆盖索引还是Using index condition索引条件下推。列清单里还能做点小文章用AS起别名让结果集字段名更友好。比如SELECT id AS user_id, nickname AS name FROM users这样后端拿到的数据字段名直接对应业务字段省去一层转换。别名不影响查询逻辑但能让下游代码更清爽。2.2 WHERE条件比较、范围、模糊与空值判断WHERE是单表查询的灵魂面试和实际工作里最容易出问题的地方基本都在这。我按常见条件类型捋一遍。比较运算、、、、、!或。这里有个默认认知MySQL的字符串比较默认不区分大小写因为默认排序规则Collation是utf8mb4_general_ci或者utf8mb4_0900_ai_cici就是case insensitive。如果你需要区分大小写要么改字段的排序规则为utf8mb4_bin要么在WHERE里加上BINARY关键字WHERE BINARY username Admin。这个坑我踩过用户登录时输入了“Admin”和“admin”走同一个账号排查半天发现是大小写不敏感导致的。范围运算BETWEEN AND和IN。BETWEEN AND是包含边界的也就是WHERE age BETWEEN 18 AND 30等价于age 18 AND age 30。IN后面跟一个值列表内部其实是多个的OR组合。这里提醒两点一是IN列表不要放太多值几百上千个值时优化器可能会放弃索引走全表扫描具体阈值依赖于优化器成本和MySQL版本但经验上超过一两百个就该考虑用临时表JOIN或者分批查询二是IN列表里不能有NULLNULL IN (1,2,3)的结果是NULL不是FALSE这个很容易被忽略。模糊匹配LIKE。通配符%匹配任意长度字符_匹配单个字符。最核心的规则LIKE abc%能走索引LIKE %abc和LIKE %abc%不能走索引除非用全文索引或者前缀索引的特殊场景。原因很简单B树索引是按照字符串从左到右排序的给定前缀就能定位范围但后缀或者中间带通配符没法利用排序特性只能全表扫描。业务里常见的“搜索包含关键词”的需求如果表数据量不大直接LIKE %关键词%就好数据量大就要考虑全文索引或者ES这类搜索引擎别硬撑。空值判断IS NULL和IS NOT NULL。有个经典坑用 NULL判断空值永远返回NULL不是TRUE也不是FALSE因为NULL代表“未知”和任何值比较结果都是“未知”。所以WHERE name NULL查不到任何行必须写成WHERE name IS NULL。另一个坑是空字符串和NULL的区别是长度为0的字符串不是NULL判断空串要用WHERE name 判断NULL要用WHERE name IS NULL两者不能混。很多业务表里有些字段既可能存又可能存NULL写查询前最好先确认数据实际情况。条件组合AND、OR、NOT。这里要注意优先级NOTANDOR。这个顺序和大部分编程语言一致但很多人会忘。比如WHERE a 1 OR b 2 AND c 3实际执行的是a 1 OR (b 2 AND c 3)如果想先算OR必须加括号WHERE (a 1 OR b 2) AND c 3。我的习惯是只要混用了AND和OR就无脑加括号省得以后维护的人包括三个月后的自己还要去猜优先级。2.3 条件过滤的进阶技巧把OR改写成UNION或IN讲一个特别的优化技巧。如果WHERE里出现WHERE a 1 OR b 2并且a和b分别都有索引优化器可能会对a和b分别走索引然后做索引合并Index Merge。但索引合并的行为在不同版本里有差异而且合并结果需要去重性能不一定好。更稳的写法是把OR拆成两个查询用UNION合并SELECT * FROM orders WHERE status pending UNION SELECT * FROM orders WHERE priority high;这样每个分支都能单独走索引合并逻辑由SQL层负责执行计划更可控。不过要注意UNION默认会去重如果业务上不需要去重用UNION ALL性能更好因为省掉了去重排序的开销。另外一个小技巧当IN列表比较长时可以用IN配合子查询或者临时表SELECT * FROM orders WHERE user_id IN ( SELECT id FROM users WHERE status 1 AND register_time 2024-01-01 );但这里有个陷阱MySQL优化器对IN (子查询)的处理方式在不同版本里有变化5.6及以前经常会把子查询物化成临时表再判断5.7以后优化器会尝试把它改成半连接semi-join执行计划里能看到FirstMatch、Materialize之类的策略。不用太深入知道“IN子查询不一定慢但要会看执行计划”就够。3. 排序、去重与分页让结果集真正可读查询结果拿到了但顺序不对、重复太多、一次返回上万行页面根本没法看。排序、去重、分页就是解决这三个问题的。这节的内容不复杂但细节非常值得抠尤其是排序字段的选择和深分页的性能问题直接影响线上体验。3.1 ORDER BY不止是升序和降序ORDER BY的基本语法是ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]多个字段时从左到右依次作为主排序键、次级排序键。比如ORDER BY status ASC, create_time DESC表示先按status升序同一status内再按create_time降序。排序这里有几个容易踩的坑。第一个坑是排序字段和SELECT列的关系。ORDER BY后面既可以写列名也可以写SELECT别名还可以写SELECT列的顺序号比如ORDER BY 2表示按第2列排序。顺序号写法不推荐因为一旦调整SELECT列的顺序排序就变味了。别名写法倒是不错比如SELECT SUM(amount) AS total FROM orders GROUP BY user_id ORDER BY total DESC。第二个坑是排序的默认值。不写ASC还是DESC默认就是升序ASC。对字符串排序时默认按字符集的排序规则来排一般就是拼音序或者字典序数字按值大小排序。这里有个细节如果想按字符串的“数值”排序比如字段存的是10、9、8这样的字符串直接ORDER BY str_column会得到10、8、9这种字典序结果因为字符串按字符一位一位比较。想要按数值排序得ORDER BY CAST(str_column AS SIGNED)或者ORDER BY str_column 0后者是MySQL的隐式转换技巧字符串和数字做算术运算时会自动转成数字。第三个坑是NULL的排序位置。MySQL默认升序时NULL排在最前面降序时NULL排在最后面。这和其他数据库比如Oracle默认NULL在最后不一样如果业务上要求NULL排在最后可以这么写SELECT * FROM users ORDER BY (email IS NULL) ASC, email ASC;email IS NULL的结果是1真或0假升序时0排在1前面也就是非NULL的email排前面NULL的排最后。这个技巧很实用。3.2 去重DISTINCT的边界与陷阱DISTINCT的作用是从结果集中去除重复行。注意是“整行去重”不是“某一列去重”。比如SELECT DISTINCT city FROM users返回所有不重复的城市而SELECT DISTINCT city, age FROM users返回的是(city, age)组合不重复的行城市相同但年龄不同的行都会保留。这个区别经常有人搞混。DISTINCT有两个性能隐患。第一它需要把所有结果集先排序或者哈希去重数据量大时会产生临时表内存放不下就落盘速度立刻变慢。第二如果结合ORDER BY排序和去重的顺序可能会让优化器额外做一轮操作。更关键的是DISTINCT和聚合函数一起用时容易写出错误的SQL。业务里最常见的一个需求是“统计某列不重复的值的数量”。比如统计有多少个不同的城市有用户下单SELECT COUNT(DISTINCT city) AS city_count FROM users;这个写法是合法的意思是“对city去重后计数”。但同样的逻辑如果手滑写成SELECT DISTINCT COUNT(city)意思就完全变了先对整张表计数然后把这个唯一的结果做去重——结果永远是一个数字看起来像是“多条记录去重后只有一行”实际上是把聚合结果去重了语义完全不同。这个坑在新手里出现频率极高。另外补充一个经验去重可以用GROUP BY替代。在MySQL里SELECT DISTINCT col1, col2 FROM t和SELECT col1, col2 FROM t GROUP BY col1, col2的结果通常是相同的严格说GROUP BY还涉及聚合语义但单纯用来去重效果一样。但GROUP BY在语义上更明确是“分组”而且配合聚合函数时能力远超DISTINCT。所以当你发现DISTINCT只是为了去重但后续还要做排序、统计考虑换成GROUP BY后面的分组章节会详细讲。3.3 分页查询LIMIT的正确姿势与深分页优化MySQL分页的语法很简单LIMIT offset, count表示跳过offset行取count行也可以写成LIMIT count OFFSET offset。一般配合ORDER BY使用否则分页结果没有稳定顺序上一页和下一页可能重复或者漏数据。分页最经典的性能问题叫深分页。假设这样一条SQLSELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 20;MySQL的服务器层会先把前1000020行都取出来丢弃前1000000行只返回最后20行。前面的100万行虽然最终被丢弃但每一行都得走一遍排序、回表、传输这个开销非常可观。数据量越大页码越深查询越慢。解决深分页常用两个方案。第一个方案是基于游标的分页也叫键集分页。每次查询都带上上一次结果的最后一条记录的游标值比如按id倒序翻页SELECT * FROM orders WHERE id 上一页最后一条记录的id ORDER BY id DESC LIMIT 20;第二次请求的时候只需要把上一次返回的最后一条id传进来因为id是主键这个条件能直接命中索引每页查询只需要扫描20行完全不随页数变深而变慢。缺点是不能随意跳页只能一页一页往下翻。第二个方案是延迟关联。先只查主键拿到本页的主键列表再回头关联明细SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id DESC LIMIT 1000000, 20 ) tmp ON o.id tmp.id;子查询里只查id这个查询可以走索引覆盖索引里就有id不需要回表速度快得多。拿到20个主键后再回原表取数据总共也就20次回表和之前扫描100万次回表有着量级上的差距。4. 分组统计与聚合函数用一行SQL替代N行程序逻辑单表查询中最能体现SQL声明式魅力的就是聚合和分组。程序里要用循环、字典、累加器写一大段逻辑SQL里一句GROUP BY加几个聚合函数就搞定了。但声明式也意味着底层细节被隐藏理解不到位就容易写出结果正确但性能糟糕的SQL。4.1 GROUP BY的分组语义列的选择决定分组粒度GROUP BY按一个或多个列的值把结果集分成若干小组每组返回一行。分组粒度和GROUP BY后面的列直接相关GROUP BY一个列就是按这个列的不同值分组GROUP BY多个列就是按这些列的组合值分组。举个例子。假设有一张销售明细表sales字段有region地区、city城市、amount金额SELECT region, city, SUM(amount) AS total FROM sales GROUP BY region, city;这个查询返回的是“每个地区下每个城市”的销售额地区城市组合唯一确定一行。如果只GROUP BY region那就只按地区分组城市被合并返回“每个地区的总销售额”。分组粒度取决于GROUP BY后面列的个数多一个列粒度就细一级这个逻辑要刻在脑子里。GROUP BY还有一个容易被忽略的行为它自带排序。MySQL里GROUP BY的分组结果默认按分组列升序排列。如果分组后不需要排序可以加ORDER BY NULL取消排序省掉一次文件排序操作在数据量大时能明显提速。很多老项目里能看到GROUP BY xxx ORDER BY NULL的写法就是这个原因。当然8.0版本里这个优化写法依然有效8.0.x默认排序行为有调整但手动指定仍然可控。如果想分组后再按某个聚合结果排序得显式写ORDER BY比如按每个地区的销售额降序排SELECT region, SUM(amount) AS total FROM sales GROUP BY region ORDER BY total DESC;注意ORDER BY total这里的total用的是SELECT里的别名MySQL允许在ORDER BY里引用聚合别名这个特性很方便。4.2 HAVING与WHERE的区别聚合前过滤还是聚合后过滤这是单表查询里最常见的概念混淆点几乎每轮面试我都会被问到也是实际开发中写错率最高的地方。WHERE在分组之前执行过滤的是原始行HAVING在分组之后执行过滤的是聚合结果。举例来说想查“2024年之后注册的用户按城市统计数量只保留用户数大于100的城市”SELECT city, COUNT(*) AS user_count FROM users WHERE register_time 2024-01-01 GROUP BY city HAVING user_count 100;这里WHERE register_time 2024-01-01是先筛掉2024年以前的行再分组计数HAVING user_count 100是分组完成后把计数小于等于100的城市过滤掉。两个条件虽然都是过滤但作用阶段完全不同。有个典型的错误写法想在聚合前过滤却写进了HAVING比如HAVING register_time 2024-01-01语法上可能不报错取决于有没有配合聚合函数但逻辑上完全不对因为HAVING阶段已经不存在原始行的概念了。性能上也有差别能用WHERE过滤的尽量不要放到HAVING。WHERE在分组前过滤减少了分组的数据量HAVING在分组后过滤前面已经白干了一部分活。所以写分组查询时先想哪些条件是“对单行数据的约束”那些放WHERE哪些条件是“对分组结果的约束”那些才放HAVING。4.3 聚合函数COUNT、SUM、AVG、MAX、MIN的细节五个常用聚合函数每一个都有细节要抠。COUNT是最容易出问题的。COUNT()和COUNT(1)在MySQL里的执行效率几乎没有差别优化器都会把它们转成“统计行数”理解成一样就行。但COUNT(col)就完全不同了它统计的是“col列非NULL的行数”。假设users表有100行其中email有90个非NULL那么COUNT(*)返回100COUNT(email)返回90。业务里想统计“总共有多少用户”应该用COUNT()想统计“有多少用户填了邮箱”才用COUNT(email)。用错这个报表数字直接错。SUM也类似SUM(col)忽略NULL值。如果col列全是NULLSUM返回NULL而不是0。所以写SUM(amount)时如果希望结果是0要写IFNULL(SUM(amount), 0)。这个在生成报表、输出JSON给前端时特别容易炸前端拿到null类型经常直接报错。AVG有个隐藏点AVG(col)是“非NULL值的平均值”不是“全行的平均值”。比如5行数据其中一行amount是NULLAVG(amount)是剩下4行的平均值。想要把NULL当0参与平均得先IFNULL(amount, 0)再算AVG(IFNULL(amount, 0))。MAX和MIN除了用于数值也常用于日期、字符串。比如查订单表里最近一次下单时间SELECT MAX(create_time) FROM orders查用户名字母序最大的昵称SELECT MAX(nickname) FROM users。这两个函数也忽略NULL只有一个细节如果列上建了索引尤其主键或者普通索引MAX(id)和MIN(id)可以直接从索引的B树两端取值速度极快不需要全表扫描。聚合函数和DISTINCT的组合前面提到过比如COUNT(DISTINCT city)。这里再补充一个例子查每个城市有多少个不重复的用户ID和总订单量。SELECT city, COUNT(DISTINCT user_id) AS unique_users, COUNT(*) AS order_count FROM orders GROUP BY city;注意这里两个COUNT的含义不同一个是用户维度去重计数一个是订单行数计数放在同一行返回一次查询拿到两个指标非常实用。5. 常用函数从字符串转日期说起——内置函数怎么用不踩坑MySQL内置函数非常多我没打算铺开讲几百个只挑实际项目里最高频、最容易踩坑的几类字符串函数、日期时间函数、流程控制函数。字符串转日期是很多人在业务里碰到过的问题我们从这个具体案例切入顺便把周边函数都过一遍。5.1 字符串函数CONCAT、SUBSTRING、REPLACE、TRIM先看CONCAT拼接字符串用。它有个容易踩坑的行为CONCAT里任何一个参数是NULL结果就是NULL。比如CONCAT(first_name, , last_name)只要first_name或last_name是NULL整个结果就是NULL。如果希望NULL按空字符串处理用CONCAT_WS带分隔符拼接配合IFNULL或者直接用CONCAT_WS( , first_name, last_name)——CONCAT_WS会跳过NULL但不会跳过空字符串。这个细节在生成导出文件、拼接地址时经常踩雷。SUBSTRING或SUBSTR用于截取子串SUBSTRING(str, pos, len)位置从1开始。如果要截取“从某字符到结尾”可以省略lenSUBSTRING(hello world, 7)返回world。另外一个实用变体是SUBSTRING_INDEX(str, delim, count)按分隔符截取SUBSTRING_INDEX(a,b,c, ,, 2)返回a,bcount为负时从右边开始数SUBSTRING_INDEX(a,b,c, ,, -1)返回c。解析逗号分隔的标签字段时这个函数特别好用。REPLACE(str, from_str, to_str)用于替换字符串中所有匹配的子串。比如把手机号中间四位脱敏REPLACE(phone, SUBSTRING(phone, 4, 4), ****)。注意REPLACE是全局替换不是只替换第一个而且它返回的是新字符串不会改变原字段值必须配合UPDATE或SELECT使用才能生效。TRIM用于去掉首尾空格TRIM(str)。还有LTRIM和RTRIM只去左或只去右。但业务里常遇到的是“去掉字段里所有空格”包括中间的空格这时TRIM就不够用了得用REPLACE(str, , )。如果还要处理制表符、换行符可以配合REGEXP_REPLACE8.0做正则替换REGEXP_REPLACE(str, [[:space:]], )能一次性清除所有空白字符。5.2 日期时间函数STR_TO_DATE、DATE_FORMAT、DATEDIFF热搜词里“mysql将字符串转为日期”这个需求背后其实是两类问题一类是从文本导入时字符串转日期另一类是日期字段格式化输出。两个方向都离不开下面这几个函数。字符串转日期核心函数是STR_TO_DATE(str, format)。format用%开头的格式符表示比如常见的有%Y四位数年份、%m两位数月份、%d两位数日期、%H24小时制、%i分钟、%s秒。假设业务传来一个字符串2024-06-15 14:30:00要转成DATETIME类型SELECT STR_TO_DATE(2024-06-15 14:30:00, %Y-%m-%d %H:%i:%s);需要注意如果字符串格式和format不匹配MySQL不会报错而是返回NULL。这个静默失败坑过不少人尤其在批量导入数据时某一行日期格式异常结果那一行的日期字段就变NULL。建议在导入前先做一轮数据检查用SELECT COUNT(*) FROM your_table WHERE STR_TO_DATE(your_str, %Y-%m-%d) IS NULL看看有多少行解析失败。反过来日期转字符串用DATE_FORMAT(date, format)格式符和STR_TO_DATE是一套。比如把DATETIME字段按月展示SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m);注意这里有个性能隐患对create_time做DATE_FORMAT后索引就失效了分组统计时得先全表扫描再在内存里做格式化。数据量大时可以考虑在表里冗余一个month字段写入时算好查询直接分组速度能快不少。日期运算DATE_ADD(date, INTERVAL expr unit)和DATE_SUB分别用于加和减。比如查最近7天的订单SELECT * FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 7 DAY);日期差值DATEDIFF(date1, date2)返回date1减date2的天数。比如计算用户注册到现在的天数DATEDIFF(NOW(), register_time)。注意DATEDIFF只比较日期部分忽略时间部分所以DATEDIFF(2024-06-15 23:59:59, 2024-06-14 00:00:00)返回1不是2。提取日期某一部分YEAR()、MONTH()、DAY()、HOUR()等把日期字段拆成单独的部分常用于报表分组。配合LAST_DAY(date)能拿到当月的最后一天处理“月末统计”需求很方便。5.3 条件判断与流程控制IF、IFNULL、CASE WHEN这类函数不会改变查询的数据范围但能改变返回结果的值让SQL在“一行内”完成简单的逻辑判断省去后端再处理一遍。IF(expr, true_value, false_value)适合简单的二选一。比如查询订单状态时把数字状态翻译成中文标签SELECT id, IF(status 1, 已支付, 未支付) AS status_label FROM orders;IFNULL(expr, default_value)是专门处理NULL的expr是NULL就返回default_value否则返回expr本身。前面的聚合函数章节已经用过好几次比如IFNULL(SUM(amount), 0)。CASE WHEN是多分支判断等价于其他语言的switch。比如把订单金额分为高、中、低三档SELECT id, amount, CASE WHEN amount 1000 THEN 高 WHEN amount 100 THEN 中 ELSE 低 END AS amount_level FROM orders;CASE WHEN还可以简化成CASE expr WHEN value THEN ... END的形式但要注意这种写法只能做等值判断不能做范围判断范围判断必须用第一种写法。实际项目里报表统计经常配合聚合函数用CASE WHEN比如统计每个用户“高金额订单数”和“低金额订单数”SELECT user_id, SUM(CASE WHEN amount 1000 THEN 1 ELSE 0 END) AS high_count, SUM(CASE WHEN amount 1000 THEN 1 ELSE 0 END) AS low_count FROM orders GROUP BY user_id;这种“CASE WHEN聚合”的写法能在一次查询里同时统计多个条件效率很高比多次查表再合并要优得多。6. 单表查询的性能调优从慢查询到执行计划写SQL谁都会但线上查询快不快靠的是对执行计划的判断和索引的认识。这一节把EXPLAIN、索引失效、慢查询排查讲透你会发现单表查询的性能问题其实有迹可循不需要靠猜。6.1 EXPLAIN执行计划怎么看、重点看什么MySQL最常用的分析命令就是EXPLAIN在SQL前面加个EXPLAIN它不会真正执行查询而是返回执行计划表格。对单表查询来说重点看下面几列type列这是效率高低的最直观标记从好到差依次是system只有一行、const主键或唯一索引等值查询、eq_ref多表关联时基于主键/唯一索引查找、ref普通索引等值查询、range索引范围扫描、index全索引扫描、ALL全表扫描。单表查询中type是const或ref是理想状态range也能接受ALL就要警惕了。比如EXPLAIN SELECT * FROM users WHERE email testexample.com;如果email上有普通索引type就是ref如果没索引type是ALL。key列实际用到的索引名。如果这里显示NULL说明没走任何索引需要检查WHERE条件或表结构。rows列优化器预估扫描的行数只是一个估算值但能反映大致的成本。rows越小越好。Extra列包含大量附加信息。看到Using index表示覆盖索引不需要回表很好看到Using where表示服务层会对存储引擎返回的行再过滤看到Using temporary表示查询使用了临时表常见于GROUP BY、DISTINCT数据量大时要小心看到Using filesort表示有额外的文件排序不是指磁盘文件可能是内存排序但也是性能信号如果Using index condition出现说明走了索引条件下推ICP通常是好事情。建议拿到慢SQL后第一件事就是EXPLAIN看type和rows先确定问题是不是出在索引上再决定怎么优化。6.2 索引对单表查询的影响覆盖索引与索引失效场景单表查询的优化一半是在优化WHERE条件另一半是在优化索引设计。这里讲几个最常遇到的索引问题。先明确一点索引不是越多越好每个索引在写入时都有维护成本查询时优化器也要评估。对单表查询来说最常见的索引需求就是WHERE条件中涉及的列、ORDER BY涉及的列优先考虑建索引。覆盖索引是个很棒的概念当一条查询需要的所有列都包含在某个索引中MySQL可以直接从索引页拿数据不需要再回表。比如用户表有联合索引(city, age)执行SELECT city, age FROM users WHERE city 上海;这里SELECT的列city, age和WHERE的列city都在索引里Extra列会显示Using index说明是覆盖索引扫描比“先走索引定位、再回表取完整行”快很多。所以写查询时尽量把SELECT需要的列控制在联合索引的覆盖范围内能省一次磁盘IO。接着讲索引失效这些场景基本都是优化器“不想用”或者“用不了”索引遇到一个排查一个。第一对索引列使用函数这个前面提过。WHERE YEAR(create_time) 2024会全表扫描写法改成范围条件WHERE create_time 2024-01-01 AND create_time 2025-01-01就能走索引。第二隐式类型转换。如果字段是VARCHAR类型但查询条件是数字WHERE phone 13800138000phone字段是VARCHARMySQL会先把phone转成数字再比较索引失效。正确写法是WHERE phone 13800138000。反过来字段是数字类型但传了字符串同样会隐式转换。这个坑在接口参数直接拼接SQL的项目里特别常见。第三前导通配符的LIKE。LIKE %关键词无法走索引这个前面说过。如果业务确实需要包含匹配数据量小就接受全表扫描数据量大就考虑全文索引或ES。第四NULL值判断。MySQL索引本身可以包含NULL值但WHERE col IS NULL这种条件在InnoDB单列索引下不一定走索引优化器可能认为全表扫描成本更低。具体行为要看版本和表数据建议用EXPLAIN实测。第五联合索引违背最左前缀原则。假设联合索引是(a, b, c)查询条件只包含b或只包含c时用不上这个索引必须从a开始连续使用一个或多个列才能命中。比如WHERE a 1 AND c 3只有a能用到索引c用不上中间缺了b。这个原则要刻在脑子里建联合索引时把最常用作等值查询的列放最左边。6.3 常见问题速查表与排查技巧实录把我在实际项目里遇到的单表查询典型问题整理成一张速查表方便你遇到类似现象时能快速对照定位。现象可能原因排查思路/解决方式查询结果出现NULL字段程序报错字段本身为NULL或聚合函数SUM/AVG返回NULL用IFNULL包裹输出判断业务上NULL和0是否等效WHERE条件传了NULL查不到数据误用 NULL而不是IS NULL统一用IS NULL判断空值禁止 NULL同样的SQL有时快有时慢统计信息过期优化器选错索引执行ANALYZE TABLE更新统计信息或FORCE INDEX指定索引分页翻到后面越来越慢深分页LIMIT offset过大改用游标分页或延迟关联GROUP BY结果乱序和预期不同GROUP BY自带排序或与ORDER BY冲突明确写ORDER BY必要时加ORDER BY NULL字符串字段按数字排序不对字符串字典序和数值序混淆用字段 0或CAST转数字再排序日期字符串导入后全是NULLSTR_TO_DATE格式不匹配静默失败先检查解析失败行逐一核对格式符查询走了全表扫描明明有索引函数/隐式转换/前导通配符导致索引失效用EXPLAIN看type逐条对照索引失效场景再分享一个排查慢查询的实操流程。第一步开启慢查询日志在MySQL配置文件里设置slow_query_log 1和long_query_time 1让超过1秒的查询记录下来。第二步拿到慢SQL后先看SQL本身是否涉及深分页、排序、聚合如果有先按上面说的优化思路改一遍。第三步还没解决就EXPLAIN重点看type和rows。第四步确认是索引问题后用SHOW INDEX FROM table_name看现有索引再决定要不要建新索引或者调整联合索引顺序。整个过程基本能在十分钟内定位问题比瞎猜强得多。单表查询这块内容讲到这里基本覆盖了从语法到性能的完整链路。我个人最大的体会是单表查询写得好不好核心不在记住多少函数和语法而在能不能在写SQL时“看见”优化器将怎么执行它。每个WHERE条件的写法、每个SELECT列的取舍、每次分组聚合的设计背后都对应着一次执行计划的抉择。多花时间对着EXPLAIN调SQL比盲目背语法能收获更多。如果这篇文章能让你少踩几个坑、少走几次弯路那我花在复盘这些经验上的时间就值了。