先说个我上周刚处理完的线上事故。客户SaaS系统下午三点突然卡死页面转圈订单列表都打不开。我上去一看数据库CPU直接拉满抓出来的那条SQL把三张大表做了三个JOIN每张表几百万行WHERE条件里还套了函数等于全表扫描了好几次。改完之后CPU从100%掉到10%整个查询从15秒缩到0.3秒。整个过程不到一小时但那种“数据库怎么这么慢”的绝望感我相信每个跟SQL打过交道的人都懂。SQL这东西入门门槛极低SELECT、INSERT、UPDATE、DELETE四个词背下来就能干活但真正决定你能走多远的是后面那层东西索引怎么走、执行计划怎么读、慢SQL怎么定位、数据量大之后怎么保性能。这篇内容就是从SQL的基础认知开始一路讲到优化实战包括我这些年在MySQL、SQL Server、PostgreSQL上踩过的坑和验证过有效的解法。适合刚学SQL想系统打基础的人也适合写了几年代码但一遇到慢查询就抓瞎的开发者数据库运维和面试前突击的人看了也不亏。1. 打好SQL基础查询顺序、去重与空值处理1.1 写SQL前先理解查询的执行顺序很多人写SQL是“想到什么写什么”但底层引擎是按固定顺序解析的。知道这个顺序很多低级报错和性能问题都能提前避免。一条常见SELECT语句的实际逻辑执行顺序是FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT注意这个顺序和SQL语句的书写顺序完全不同。我为什么要把这个单独拎出来讲因为它能解释两个高频问题。第一为什么WHERE条件里不能直接用SELECT子句里定义的别名比如这么写SELECT order_amount / 100 AS amount_yuan FROM orders WHERE amount_yuan 1000;在MySQL里会直接报“Unknown column”在有些数据库里可能不报错但结果不对。原因就是WHERE在SELECT之前执行别名是SELECT阶段才生成的WHERE阶段根本没见过这个字段。正确做法是写完整表达式或者用子查询包一层。第二理解GROUP BY和HAVING的位置关系。HAVING可以用聚合函数WHERE不行因为HAVING是分组之后才执行的。像“筛选下单次数大于5次的用户”这种需求只能写在HAVING里。很多人习惯用WHERE筛完再分组结果发现要么报错要么语义不对本质是对执行顺序没概念。这个顺序不只是理论知识它直接决定你的SQL能不能跑出预期结果以及优化器能不能利用上索引。后面讲优化的时候你会发现几乎所有“SQL写得慢”的问题都能追溯到这个逻辑顺序上没有贴合索引结构。1.2 去重DISTINCT、GROUP BY和窗口函数怎么选标题里有个热词叫“sql语句去重”这也是群里问得最多的需求之一。去重看着简单但实现方式不同性能和结果都不一样。最简单的场景查询某个字段有哪些不重复的值SELECT DISTINCT customer_id FROM orders;这种适合单字段或几个字段组合去重。但如果你需要“每个客户最新的订单金额”DISTINCT就完全不够用了。因为它只能去重不能做“按某字段分组后取每组某条记录”这种操作。第二个常见写法是GROUP BY比如统计每个客户的订单数SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;GROUP BY和DISTINCT的去重逻辑本质相同很多时候引擎会用同一种方式执行。区别在于GROUP BY可以同时带聚合信息DISTINCT不行。第三个场景是真正高频的坑删除/保留重复数据中的特定一条。比如同一张订单表里有重复记录要保留每个订单号最新的一条。直接GROUP BY取不到“最新”的那种正确解法是用窗口函数SELECT order_id, customer_id, amount, created_at FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY created_at DESC) AS rn FROM orders ) t WHERE rn 1;ROW_NUMBER()按order_id分组按时间倒序编号rn1就是每个订单号最新的一条。这个写法在MySQL 8.0、PostgreSQL、SQL Server 2005以上、Oracle里都能用算是我最常用的去重保留方案。我给你的选择建议很简单只值列表用DISTINCT带统计用GROUP BY要保留组内特定一条用窗口函数。千万别用DISTINCT去“假装”拿到最新记录那只会把正确数据丢掉。1.3 空值处理NULL的三值逻辑与默认值设计热词里还有个高频词是“sql去除空值”。空值处理的坑比大多数人以为的多核心在于SQL里的NULL不是“没有值”而是一个“未知”状态。打个比方如果我问你“兜里有多少钱”你说“没带钱”是0元但你说“不知道”就是NULL。0元可以被比较NULL不行。所以在SQL里field NULL永远是NULL永远不会为真判断空值必须用IS NULLfield ! NULL同样无法筛出非空数据必须用IS NOT NULLNOT IN子查询里如果结果集包含NULL整个查询会返回空集合最后那条我实际踩过。有一个查询要排除某些用户用了WHERE user_id NOT IN (SELECT user_id FROM blacklist)结果明明黑名单表里只有三条记录这个查询却慢到像在扫全表更诡异的是部分正常用户也没查出来。排查后发现黑名单表里有一条记录的user_id是NULL整个NOT IN因为三值逻辑直接失效了。改成NOT EXISTS后问题立刻消失。去除空值的一个实用写法是用COALESCE给默认值SELECT customer_name, COALESCE(phone, 无手机号) AS phone FROM customers;COALESCE会从左到右返回第一个非NULL值常用于报表填充、拼接字符串时防NULL。聊到空值和默认值正好说下热词里的“sql 默认值guid”。用GUID当主键在分布式系统里很常见但如果你直接在MySQL里给主键设默认UUID比如DEFAULT (UUID())在高并发插入场景下会引发严重的页分裂因为UUID是随机无序的每次插入都要在索引中间找位置。SQL Server里就提供了NEWSEQUENTIALID()生成的是趋势递增的GUID顺序插入减少页分裂MySQL 8.0没有内置顺序UUID可以换UUIDv7或者在应用层生成有序ID。一句话能用自增用自增必须用GUID就选有序版本别让随机的UUID坑了写入性能。2. 索引优化让查询从全表扫描到秒回2.1 索引失效的五个典型场景我全踩过索引是SQL提速的第一功臣但它不是什么情况下都生效。以下五类是我在真实业务里反复遇到的索引失效场景每一条都配了“翻车写法”和“正确姿势”。第一在索引列上套函数。比如记录手机号时统一存成13800000000这种纯数字查询时却用WHERE SUBSTR(mobile, 1, 3) 138索引列被函数包裹后优化器只能放弃索引。正确姿势是改写条件或者在设计时就冗余出一个前缀列。第二隐式类型转换。字段是VARCHAR类型查询条件传的是数字WHERE mobile 13800000000。MySQL会把字段转换成数字再比较结果索引失效全表扫描。每个字段什么类型查询条件就传什么类型这个习惯一定要养成。第三LIKE前置通配符。WHERE name LIKE %张这种写法因为要从字符串中间开始匹配无法利用B树的顺序结构但WHERE name LIKE 张%就可以走索引。这是最经典的失效场景之一。第四OR连接的条件不全走索引。OR两边有一个条件没索引整个查询就可能放弃索引。解决办法是拆成两个查询UNION ALL或者优化成IN列表。第五对索引列做计算。WHERE price * 1.1 100和函数问题同源。任何对列的加工都会让索引失效把计算挪到条件值那侧才是正解。这几个场景我是踩全了的。最气人的是隐式类型转换那次一张20万行的表怎么查都慢EXPLAIN一看typeALL后来发现是字符串字段对数字比较加上一个引号查询直接从300ms变成20ms。所以排查慢SQL的第一步永远是看索引用没用到。2.2 联合索引最左前缀原则一顿饭的功夫讲明白联合索引是“多个字段组成的一个索引”它遵循最左前缀原则查询条件从索引的最左列开始并且连续匹配才能用上索引。跟你吃套餐一个道理。菜单上写着“主食饮料小食”套餐你只点饮料不点主食服务员没法给你套餐价但你只点主食是可以的。联合索引(a, b, c)就相当于这个套餐只用a走索引没问题用a和b走索引没问题用a、b、c走索引没问题只用b不走只用了a和c中间跳过b那么a能用、c用不上开发中另一个高频坑是范围条件后面的列失效。还是(a, b, c)联合索引如果查询是WHERE a 1 AND b 10 AND c 5因为b用了范围查询c的条件就只能在b的结果集里过滤索引到b就用尽了c走不了索引。所以设计联合索引时要把范围查询的字段尽量往后放。联合索引的设计口诀经常一起出现在WHERE里的字段适合建联合索引等值条件的字段放前面范围条件的字段放后面区分度高的字段放前面区分度低的往后。区分度比如性别只有男女区分度就很低放在最左列会让索引实际效果大打折扣。还有一个容易忽略的地方ORDER BY也能跟索引配合。如果查询WHERE a 1 ORDER BY b联合索引(a, b)不仅能筛数帮排序也省了filesort。这一点很多资料不讲但实测对排序多的查询收益非常明显。2.3 覆盖索引与执行计划EXPLAIN怎么看才有效不会看EXPLAIN的SQL优化等于不会看仪表盘开车。我要求团队里每个写SQL的人都必须会用EXPLAIN这是排查慢SQL的第一工具。在MySQL里只要在SQL前面加EXPLAIN就能看到执行计划。核心看四列type列是访问方式从好到差依次是const eq_ref ref range index ALL。看到ALL全表扫描和index全索引扫描性能也不好就要警惕。rows列是估算扫描行数数字越小越好。虽然是估算值但对比较两个写法的优劣非常直观。Extra列有几个标志性字段Using index代表覆盖索引SQL要的字段都在索引里不用回表这是最优状态Using where表示索引使用后还有额外过滤Using filesort表示要额外排序应避免Using temporary表示要用临时表通常出现在GROUP BY或DISTINCT场景也要小心。覆盖索引值得单独说一下。比如有一个查询只需要SELECT order_id和customer_id两个字段而索引恰好是(customer_id, order_id)那么引擎直接从索引里取数不需要回表再读一遍整行数据。这就是为什么我建议查询尽量只SELECT需要的字段别一上来就SELECT *。SELECT *会让覆盖索引直接失效被迫回表全字段读翻了配置覆盖索引的白费。SQL Server里的SET STATISTICS IO ON、PostgreSQL里的EXPLAIN ANALYZE也是同理核心思想一致搞清楚引擎是怎么找数据的索引有没有命中有没有多余的回表和排序。这些信息比任何“优化技巧”都直击本质。3. 慢SQL排查与改写实战3.1 三步定位问题SQL开日志、看进程、拉执行计划接到“数据库变慢了”“接口超时了”这种反馈别急着改代码先按这个流程找出罪魁祸首。第一步开启慢查询日志让数据库把“超时的SQL”主动记下来。MySQL里可以动态设置SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time是阈值单位秒线上建议先设成1秒抓一圈看看再逐步收紧。SQL Server可以用扩展事件或直接看sys.dm_exec_query_stats视图。PostgreSQL里有pg_stat_statements扩展。没有日志数据支撑的优化都是瞎猜。第二步看当前正在跑的会话。MySQL里SHOW PROCESSLIST能看到每个连接的当前状态、执行时间和正在执行的SQL。数据库突然打满CPU时这个命令能立刻锁定那几个“跑了几百秒”的查询。第三步拿到具体SQL后前面讲过用EXPLAIN分析执行计划。我自己的排查套路是先看type是不是ALL再看rows是不是异常大最后看Extra里有没有Using filesort或Using temporary。这三处如果都有问题就回到索引该怎么加、SQL该怎么改的正题上了。定位阶段最怕的是“哪条都像又哪条都不是”。所以我建议慢SQL日志一定要长期开着哪怕只是记录到表里碰到问题才有据可查。3.2 深分页优化延迟关联与书签法实测慢SQL里头深分页是最隐蔽的杀手之一。业务方要一个“交易记录查询”为了支持跳页前端传个页码SQL长这样SELECT * FROM orders WHERE customer_id 12345 ORDER BY created_at DESC LIMIT 100000, 20;这条SQL看着人畜无害实际上LIMIT 100000, 20的意思是先扫描出前100020行丢掉前100000行只要最后20行。数据量一大越往后翻页越慢核心原因就是前面那十万行的扫描全浪费了。我在一张200万行的订单表上实测过翻到第100页时这条查询要800ms而只翻第1页只要10ms。90倍差距用户翻页越深越卡这体验基本等于劝退。对付深分页两个方案最实用。方案一是延迟关联子查询先只查主键再回表取完整数据SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE customer_id 12345 ORDER BY created_at DESC LIMIT 100000, 20 ) t ON o.id t.id;内层子查询查主键时不需要回表扫描成本比直接SELECT *低得多。实测同一页从800ms降到了180ms。方案二是书签法不用LIMIT偏移量改成记住上一页最后一条的IDSELECT * FROM orders WHERE customer_id 12345 AND id 100000 ORDER BY created_at DESC LIMIT 20;这个方法翻页越深优势越大因为它直接跳到书签位置往后扫不做无谓的偏移。代价是无法精确跳页只支持“上一页/下一页”。现在的业务模型里无限下拉列表比翻页器多得多书签法基本够用。深分页的底层原理一句话就能说明白数据库的LIMIT偏移是“先找再扔”不是“直接跳过去”所以偏移越大越吃亏。理解了这一点你自然就会选择书签法或延迟关联了。3.3 并行与批量别让一条大事务拖垮整个库热词里有个“并行sql优化”在OLTP系统里我建议慎用并行。MySQL 8.0虽然引入了一些并行查询能力但生产上默认配置下单条SQL的并行度并不是你想开就能开的。SQL Server的并行度控制更明确MAXDOP和“并行成本阈值”两个参数直接决定查询是否并行。设太高一条大查询可能占满所有CPU核心把其他小请求也拖死设太低又会出现“一条SQL占一堆核”但效率不升反降的情况。我踩过的真实教训是给一个报表系统把MAXDOP设成了0不限并行结果一条复杂JOIN直接吃掉了全部8个核心订单写入的延迟涨了十倍。后来限制MAXDOP2并让该报表在低峰期运行其他业务才缓过来。并行优化不是“越并行越好”而是“该串行的串行该并行的小范围并行”。除了并行还有一类导致“整个库变慢”的问题是大批量UPDATE。想象一下一条UPDATE语句准备更新500万行会在表上持有大量行锁其他事务只能排队等系统看起来就像“卡死”了。正确做法是分批提交比如按主键范围切片每批更新1万行-- 每批只更新一个主键区间批与批之间留出间隙 UPDATE orders SET status archived WHERE id BETWEEN 1 AND 10000;配合脚本循环执行既能控制锁范围又不会让事务日志暴涨。处理百万级以上的数据变更这条是最稳的套路没有之一。4. 数据库运维与常见问题排查速查4.1 SQL Server连接与安装问题处理搜索热词里有大量“sql server 2016安装教程”“ssms安装”“sql server无法连接”之类的关键词说明SQL Server环境问题困扰的人非常多。我挑两个高频的讲。第一个是SQL Server的SSL加密连接报错。报错信息常长这样“驱动程序无法通过使用安全套接字层(SSL)加密与SQL Server建立安全连接”。这个报错在JDBC连接SQL Server 2016以上版本时很常见本质是客户端驱动和服务器端的TLS协议版本对不上或者服务器证书不受客户端信任。解法按顺序试升级JDBC驱动到最新版连接串里显式关闭加密encryptfalse仅限内网测试环境生产建议解决证书问题或者给JVM导入SQL Server的证书。很多时候不用重装任何东西就是驱动版本太老。第二个是安装SQL Server时报“对密钥无访问权限”。这个我在帮同事装2019的时候遇到过卡在实例配置那一步。原因是当前Windows用户对注册表里某些加密密钥目录没有权限。常见解法是右键安装程序“以管理员身份运行”如果还是不行需要给当前用户授予对应密钥容器的权限。不必慌网上那些“重装系统”的建议基本是偷懒和SQL Server本身没关系。SQL Server的安装本身并不复杂核心是提前把账号权限、端口默认1433、防火墙规则准备好。建议在安装前统一检查别等到中间报错再回来补。4.2 ORM里写原生SQL的正确姿势以Prisma为例“prisma 如何调用sql”这个热词很有意思说明现在ORM使用者也在面对“ORM处理不了复杂SQL”的瓶颈。事实确实如此ORM处理常规增删改查很舒服但一旦遇到特殊的分页统计、多表关联的复杂报表ORM生成的SQL往往不够高效这时就需要手写原生SQL。以Prisma为例它提供了$queryRaw接口写原生查询const results await prisma.$queryRaw SELECT customer_id, COUNT(*) AS order_count FROM orders WHERE created_at ${startDate} GROUP BY customer_id HAVING COUNT(*) 5 ORDER BY order_count DESC ;注意这里的写法${startDate}是参数占位而不是字符串拼接Prisma会自动做参数绑定这一点非常关键能防SQL注入。如果你需要执行UPDATE、DELETE这类写操作用法是$executeRaw同样用参数占位。我的经验是ORM造不好又查得慢的SQL赶紧手写别让代码为ORM的抽象买单。但写原生SQL有一条高压线永远不要拼接用户输入永远使用参数化占位。这句话适用于所有编程语言和所有数据库也是下一节要展开的安全底线。4.3 那些年我们交过的SQL优化学费有一个环节我想单独列出来算是我这些年做SQL review时最常打的回帖也是年轻人最容易踩的学费坑。下面用一张速查表总结常见写法隐蔽问题更优姿势SELECT *查询所有字段覆盖索引失效回表读全部数据只查需要的字段COUNT(某字段)统计行数该字段为NULL时不会计入统计错明确用COUNT(*)或COUNT(主键)在索引列上做运算/函数索引失效走全表扫描改写条件把运算放到值一侧IN后面带上千个值单条SQL负担过重优化器难处理拆批查询或先导入临时表再JOIN忘记加LIMIT一次拉回几十万行把应用内存打爆任何时候都加LIMITWHERE里用OR连接可能有部分条件不走索引改写UNION ALL或转IN这里单独解释下COUNT(*)和COUNT(字段)。很多人以为COUNT(*)会“查所有列”所以慢实际上在MySQL和SQL Server里COUNT(*)是统计所有行引擎会选成本最低的方式COUNT(字段)是统计该字段非NULL的行数。这两个语义不同不是简单的“哪个快哪个慢”问题。如果你的目标是统计总行数放心用COUNT(*)。4.4 SQL注入与参数化这条底线不能松关于止于安全。SQL注入攻击年年都有原理其实很简单开发者在拼SQL字符串时把用户输入直接当成了SQL代码的一部分。比如不加防护的登录查询sql SELECT * FROM users WHERE username user_input AND password pwd 如果用户输入的是admin --拼出来就变成了SELECT * FROM users WHERE username admin -- AND password ...--把后面的密码判断注释掉了攻击者无需密码就能登录。这是最经典的注入手法也是为什么所有数据库开发规范里都把“参数化查询”列为铁律。正确写法是用参数占位让数据库把输入当作“值”而不是“代码”cursor.execute(SELECT * FROM users WHERE username ? AND password ?, (username, password))Java的PreparedStatement、Go的database/sql占位符、Node.js的mysql2问号占位符本质上都是同一个思路。ORM框架内部都走参数绑定反而是手写SQL拼接的情况最危险。我的建议很朴素所有涉及用户输入的SQL一律参数化所有能访问数据库的应用账号按最小权限分配。这两条做到位SQL注入风险能降掉九成。像“万能密码”这类词大家当反面教材了解原理就好千万别觉得是“技巧”那是在给自己挖坑。最后再讲一个我自己的习惯写完一条SQL我会习惯性地EXPLAIN一下哪怕只是一个简单的单表查询也会看有没有触发Using filesort或者回表。这个习惯坚持了五六年帮我挡下了无数潜在慢查询。SQL从基础到优化其实没有太多高深莫测的东西就是把执行顺序、索引、执行计划这几件事揉碎了吃透然后在每次实战里验证和复盘。你要是有条件拿自己业务里最慢的几条SQL按这篇的思路走一遍我相信体感会非常直接。
先说个我上周刚处理完的线上事故。客户SaaS系统下午三点突然卡死页面转圈订单列表都打不开。我上去一看数据库CPU直接拉满抓出来的那条SQL把三张大表做了三个JOIN每张表几百万行WHERE条件里还套了函数等于全表扫描了好几次。改完之后CPU从100%掉到10%整个查询从15秒缩到0.3秒。整个过程不到一小时但那种“数据库怎么这么慢”的绝望感我相信每个跟SQL打过交道的人都懂。SQL这东西入门门槛极低SELECT、INSERT、UPDATE、DELETE四个词背下来就能干活但真正决定你能走多远的是后面那层东西索引怎么走、执行计划怎么读、慢SQL怎么定位、数据量大之后怎么保性能。这篇内容就是从SQL的基础认知开始一路讲到优化实战包括我这些年在MySQL、SQL Server、PostgreSQL上踩过的坑和验证过有效的解法。适合刚学SQL想系统打基础的人也适合写了几年代码但一遇到慢查询就抓瞎的开发者数据库运维和面试前突击的人看了也不亏。1. 打好SQL基础查询顺序、去重与空值处理1.1 写SQL前先理解查询的执行顺序很多人写SQL是“想到什么写什么”但底层引擎是按固定顺序解析的。知道这个顺序很多低级报错和性能问题都能提前避免。一条常见SELECT语句的实际逻辑执行顺序是FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT注意这个顺序和SQL语句的书写顺序完全不同。我为什么要把这个单独拎出来讲因为它能解释两个高频问题。第一为什么WHERE条件里不能直接用SELECT子句里定义的别名比如这么写SELECT order_amount / 100 AS amount_yuan FROM orders WHERE amount_yuan 1000;在MySQL里会直接报“Unknown column”在有些数据库里可能不报错但结果不对。原因就是WHERE在SELECT之前执行别名是SELECT阶段才生成的WHERE阶段根本没见过这个字段。正确做法是写完整表达式或者用子查询包一层。第二理解GROUP BY和HAVING的位置关系。HAVING可以用聚合函数WHERE不行因为HAVING是分组之后才执行的。像“筛选下单次数大于5次的用户”这种需求只能写在HAVING里。很多人习惯用WHERE筛完再分组结果发现要么报错要么语义不对本质是对执行顺序没概念。这个顺序不只是理论知识它直接决定你的SQL能不能跑出预期结果以及优化器能不能利用上索引。后面讲优化的时候你会发现几乎所有“SQL写得慢”的问题都能追溯到这个逻辑顺序上没有贴合索引结构。1.2 去重DISTINCT、GROUP BY和窗口函数怎么选标题里有个热词叫“sql语句去重”这也是群里问得最多的需求之一。去重看着简单但实现方式不同性能和结果都不一样。最简单的场景查询某个字段有哪些不重复的值SELECT DISTINCT customer_id FROM orders;这种适合单字段或几个字段组合去重。但如果你需要“每个客户最新的订单金额”DISTINCT就完全不够用了。因为它只能去重不能做“按某字段分组后取每组某条记录”这种操作。第二个常见写法是GROUP BY比如统计每个客户的订单数SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;GROUP BY和DISTINCT的去重逻辑本质相同很多时候引擎会用同一种方式执行。区别在于GROUP BY可以同时带聚合信息DISTINCT不行。第三个场景是真正高频的坑删除/保留重复数据中的特定一条。比如同一张订单表里有重复记录要保留每个订单号最新的一条。直接GROUP BY取不到“最新”的那种正确解法是用窗口函数SELECT order_id, customer_id, amount, created_at FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY created_at DESC) AS rn FROM orders ) t WHERE rn 1;ROW_NUMBER()按order_id分组按时间倒序编号rn1就是每个订单号最新的一条。这个写法在MySQL 8.0、PostgreSQL、SQL Server 2005以上、Oracle里都能用算是我最常用的去重保留方案。我给你的选择建议很简单只值列表用DISTINCT带统计用GROUP BY要保留组内特定一条用窗口函数。千万别用DISTINCT去“假装”拿到最新记录那只会把正确数据丢掉。1.3 空值处理NULL的三值逻辑与默认值设计热词里还有个高频词是“sql去除空值”。空值处理的坑比大多数人以为的多核心在于SQL里的NULL不是“没有值”而是一个“未知”状态。打个比方如果我问你“兜里有多少钱”你说“没带钱”是0元但你说“不知道”就是NULL。0元可以被比较NULL不行。所以在SQL里field NULL永远是NULL永远不会为真判断空值必须用IS NULLfield ! NULL同样无法筛出非空数据必须用IS NOT NULLNOT IN子查询里如果结果集包含NULL整个查询会返回空集合最后那条我实际踩过。有一个查询要排除某些用户用了WHERE user_id NOT IN (SELECT user_id FROM blacklist)结果明明黑名单表里只有三条记录这个查询却慢到像在扫全表更诡异的是部分正常用户也没查出来。排查后发现黑名单表里有一条记录的user_id是NULL整个NOT IN因为三值逻辑直接失效了。改成NOT EXISTS后问题立刻消失。去除空值的一个实用写法是用COALESCE给默认值SELECT customer_name, COALESCE(phone, 无手机号) AS phone FROM customers;COALESCE会从左到右返回第一个非NULL值常用于报表填充、拼接字符串时防NULL。聊到空值和默认值正好说下热词里的“sql 默认值guid”。用GUID当主键在分布式系统里很常见但如果你直接在MySQL里给主键设默认UUID比如DEFAULT (UUID())在高并发插入场景下会引发严重的页分裂因为UUID是随机无序的每次插入都要在索引中间找位置。SQL Server里就提供了NEWSEQUENTIALID()生成的是趋势递增的GUID顺序插入减少页分裂MySQL 8.0没有内置顺序UUID可以换UUIDv7或者在应用层生成有序ID。一句话能用自增用自增必须用GUID就选有序版本别让随机的UUID坑了写入性能。2. 索引优化让查询从全表扫描到秒回2.1 索引失效的五个典型场景我全踩过索引是SQL提速的第一功臣但它不是什么情况下都生效。以下五类是我在真实业务里反复遇到的索引失效场景每一条都配了“翻车写法”和“正确姿势”。第一在索引列上套函数。比如记录手机号时统一存成13800000000这种纯数字查询时却用WHERE SUBSTR(mobile, 1, 3) 138索引列被函数包裹后优化器只能放弃索引。正确姿势是改写条件或者在设计时就冗余出一个前缀列。第二隐式类型转换。字段是VARCHAR类型查询条件传的是数字WHERE mobile 13800000000。MySQL会把字段转换成数字再比较结果索引失效全表扫描。每个字段什么类型查询条件就传什么类型这个习惯一定要养成。第三LIKE前置通配符。WHERE name LIKE %张这种写法因为要从字符串中间开始匹配无法利用B树的顺序结构但WHERE name LIKE 张%就可以走索引。这是最经典的失效场景之一。第四OR连接的条件不全走索引。OR两边有一个条件没索引整个查询就可能放弃索引。解决办法是拆成两个查询UNION ALL或者优化成IN列表。第五对索引列做计算。WHERE price * 1.1 100和函数问题同源。任何对列的加工都会让索引失效把计算挪到条件值那侧才是正解。这几个场景我是踩全了的。最气人的是隐式类型转换那次一张20万行的表怎么查都慢EXPLAIN一看typeALL后来发现是字符串字段对数字比较加上一个引号查询直接从300ms变成20ms。所以排查慢SQL的第一步永远是看索引用没用到。2.2 联合索引最左前缀原则一顿饭的功夫讲明白联合索引是“多个字段组成的一个索引”它遵循最左前缀原则查询条件从索引的最左列开始并且连续匹配才能用上索引。跟你吃套餐一个道理。菜单上写着“主食饮料小食”套餐你只点饮料不点主食服务员没法给你套餐价但你只点主食是可以的。联合索引(a, b, c)就相当于这个套餐只用a走索引没问题用a和b走索引没问题用a、b、c走索引没问题只用b不走只用了a和c中间跳过b那么a能用、c用不上开发中另一个高频坑是范围条件后面的列失效。还是(a, b, c)联合索引如果查询是WHERE a 1 AND b 10 AND c 5因为b用了范围查询c的条件就只能在b的结果集里过滤索引到b就用尽了c走不了索引。所以设计联合索引时要把范围查询的字段尽量往后放。联合索引的设计口诀经常一起出现在WHERE里的字段适合建联合索引等值条件的字段放前面范围条件的字段放后面区分度高的字段放前面区分度低的往后。区分度比如性别只有男女区分度就很低放在最左列会让索引实际效果大打折扣。还有一个容易忽略的地方ORDER BY也能跟索引配合。如果查询WHERE a 1 ORDER BY b联合索引(a, b)不仅能筛数帮排序也省了filesort。这一点很多资料不讲但实测对排序多的查询收益非常明显。2.3 覆盖索引与执行计划EXPLAIN怎么看才有效不会看EXPLAIN的SQL优化等于不会看仪表盘开车。我要求团队里每个写SQL的人都必须会用EXPLAIN这是排查慢SQL的第一工具。在MySQL里只要在SQL前面加EXPLAIN就能看到执行计划。核心看四列type列是访问方式从好到差依次是const eq_ref ref range index ALL。看到ALL全表扫描和index全索引扫描性能也不好就要警惕。rows列是估算扫描行数数字越小越好。虽然是估算值但对比较两个写法的优劣非常直观。Extra列有几个标志性字段Using index代表覆盖索引SQL要的字段都在索引里不用回表这是最优状态Using where表示索引使用后还有额外过滤Using filesort表示要额外排序应避免Using temporary表示要用临时表通常出现在GROUP BY或DISTINCT场景也要小心。覆盖索引值得单独说一下。比如有一个查询只需要SELECT order_id和customer_id两个字段而索引恰好是(customer_id, order_id)那么引擎直接从索引里取数不需要回表再读一遍整行数据。这就是为什么我建议查询尽量只SELECT需要的字段别一上来就SELECT *。SELECT *会让覆盖索引直接失效被迫回表全字段读翻了配置覆盖索引的白费。SQL Server里的SET STATISTICS IO ON、PostgreSQL里的EXPLAIN ANALYZE也是同理核心思想一致搞清楚引擎是怎么找数据的索引有没有命中有没有多余的回表和排序。这些信息比任何“优化技巧”都直击本质。3. 慢SQL排查与改写实战3.1 三步定位问题SQL开日志、看进程、拉执行计划接到“数据库变慢了”“接口超时了”这种反馈别急着改代码先按这个流程找出罪魁祸首。第一步开启慢查询日志让数据库把“超时的SQL”主动记下来。MySQL里可以动态设置SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time是阈值单位秒线上建议先设成1秒抓一圈看看再逐步收紧。SQL Server可以用扩展事件或直接看sys.dm_exec_query_stats视图。PostgreSQL里有pg_stat_statements扩展。没有日志数据支撑的优化都是瞎猜。第二步看当前正在跑的会话。MySQL里SHOW PROCESSLIST能看到每个连接的当前状态、执行时间和正在执行的SQL。数据库突然打满CPU时这个命令能立刻锁定那几个“跑了几百秒”的查询。第三步拿到具体SQL后前面讲过用EXPLAIN分析执行计划。我自己的排查套路是先看type是不是ALL再看rows是不是异常大最后看Extra里有没有Using filesort或Using temporary。这三处如果都有问题就回到索引该怎么加、SQL该怎么改的正题上了。定位阶段最怕的是“哪条都像又哪条都不是”。所以我建议慢SQL日志一定要长期开着哪怕只是记录到表里碰到问题才有据可查。3.2 深分页优化延迟关联与书签法实测慢SQL里头深分页是最隐蔽的杀手之一。业务方要一个“交易记录查询”为了支持跳页前端传个页码SQL长这样SELECT * FROM orders WHERE customer_id 12345 ORDER BY created_at DESC LIMIT 100000, 20;这条SQL看着人畜无害实际上LIMIT 100000, 20的意思是先扫描出前100020行丢掉前100000行只要最后20行。数据量一大越往后翻页越慢核心原因就是前面那十万行的扫描全浪费了。我在一张200万行的订单表上实测过翻到第100页时这条查询要800ms而只翻第1页只要10ms。90倍差距用户翻页越深越卡这体验基本等于劝退。对付深分页两个方案最实用。方案一是延迟关联子查询先只查主键再回表取完整数据SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE customer_id 12345 ORDER BY created_at DESC LIMIT 100000, 20 ) t ON o.id t.id;内层子查询查主键时不需要回表扫描成本比直接SELECT *低得多。实测同一页从800ms降到了180ms。方案二是书签法不用LIMIT偏移量改成记住上一页最后一条的IDSELECT * FROM orders WHERE customer_id 12345 AND id 100000 ORDER BY created_at DESC LIMIT 20;这个方法翻页越深优势越大因为它直接跳到书签位置往后扫不做无谓的偏移。代价是无法精确跳页只支持“上一页/下一页”。现在的业务模型里无限下拉列表比翻页器多得多书签法基本够用。深分页的底层原理一句话就能说明白数据库的LIMIT偏移是“先找再扔”不是“直接跳过去”所以偏移越大越吃亏。理解了这一点你自然就会选择书签法或延迟关联了。3.3 并行与批量别让一条大事务拖垮整个库热词里有个“并行sql优化”在OLTP系统里我建议慎用并行。MySQL 8.0虽然引入了一些并行查询能力但生产上默认配置下单条SQL的并行度并不是你想开就能开的。SQL Server的并行度控制更明确MAXDOP和“并行成本阈值”两个参数直接决定查询是否并行。设太高一条大查询可能占满所有CPU核心把其他小请求也拖死设太低又会出现“一条SQL占一堆核”但效率不升反降的情况。我踩过的真实教训是给一个报表系统把MAXDOP设成了0不限并行结果一条复杂JOIN直接吃掉了全部8个核心订单写入的延迟涨了十倍。后来限制MAXDOP2并让该报表在低峰期运行其他业务才缓过来。并行优化不是“越并行越好”而是“该串行的串行该并行的小范围并行”。除了并行还有一类导致“整个库变慢”的问题是大批量UPDATE。想象一下一条UPDATE语句准备更新500万行会在表上持有大量行锁其他事务只能排队等系统看起来就像“卡死”了。正确做法是分批提交比如按主键范围切片每批更新1万行-- 每批只更新一个主键区间批与批之间留出间隙 UPDATE orders SET status archived WHERE id BETWEEN 1 AND 10000;配合脚本循环执行既能控制锁范围又不会让事务日志暴涨。处理百万级以上的数据变更这条是最稳的套路没有之一。4. 数据库运维与常见问题排查速查4.1 SQL Server连接与安装问题处理搜索热词里有大量“sql server 2016安装教程”“ssms安装”“sql server无法连接”之类的关键词说明SQL Server环境问题困扰的人非常多。我挑两个高频的讲。第一个是SQL Server的SSL加密连接报错。报错信息常长这样“驱动程序无法通过使用安全套接字层(SSL)加密与SQL Server建立安全连接”。这个报错在JDBC连接SQL Server 2016以上版本时很常见本质是客户端驱动和服务器端的TLS协议版本对不上或者服务器证书不受客户端信任。解法按顺序试升级JDBC驱动到最新版连接串里显式关闭加密encryptfalse仅限内网测试环境生产建议解决证书问题或者给JVM导入SQL Server的证书。很多时候不用重装任何东西就是驱动版本太老。第二个是安装SQL Server时报“对密钥无访问权限”。这个我在帮同事装2019的时候遇到过卡在实例配置那一步。原因是当前Windows用户对注册表里某些加密密钥目录没有权限。常见解法是右键安装程序“以管理员身份运行”如果还是不行需要给当前用户授予对应密钥容器的权限。不必慌网上那些“重装系统”的建议基本是偷懒和SQL Server本身没关系。SQL Server的安装本身并不复杂核心是提前把账号权限、端口默认1433、防火墙规则准备好。建议在安装前统一检查别等到中间报错再回来补。4.2 ORM里写原生SQL的正确姿势以Prisma为例“prisma 如何调用sql”这个热词很有意思说明现在ORM使用者也在面对“ORM处理不了复杂SQL”的瓶颈。事实确实如此ORM处理常规增删改查很舒服但一旦遇到特殊的分页统计、多表关联的复杂报表ORM生成的SQL往往不够高效这时就需要手写原生SQL。以Prisma为例它提供了$queryRaw接口写原生查询const results await prisma.$queryRaw SELECT customer_id, COUNT(*) AS order_count FROM orders WHERE created_at ${startDate} GROUP BY customer_id HAVING COUNT(*) 5 ORDER BY order_count DESC ;注意这里的写法${startDate}是参数占位而不是字符串拼接Prisma会自动做参数绑定这一点非常关键能防SQL注入。如果你需要执行UPDATE、DELETE这类写操作用法是$executeRaw同样用参数占位。我的经验是ORM造不好又查得慢的SQL赶紧手写别让代码为ORM的抽象买单。但写原生SQL有一条高压线永远不要拼接用户输入永远使用参数化占位。这句话适用于所有编程语言和所有数据库也是下一节要展开的安全底线。4.3 那些年我们交过的SQL优化学费有一个环节我想单独列出来算是我这些年做SQL review时最常打的回帖也是年轻人最容易踩的学费坑。下面用一张速查表总结常见写法隐蔽问题更优姿势SELECT *查询所有字段覆盖索引失效回表读全部数据只查需要的字段COUNT(某字段)统计行数该字段为NULL时不会计入统计错明确用COUNT(*)或COUNT(主键)在索引列上做运算/函数索引失效走全表扫描改写条件把运算放到值一侧IN后面带上千个值单条SQL负担过重优化器难处理拆批查询或先导入临时表再JOIN忘记加LIMIT一次拉回几十万行把应用内存打爆任何时候都加LIMITWHERE里用OR连接可能有部分条件不走索引改写UNION ALL或转IN这里单独解释下COUNT(*)和COUNT(字段)。很多人以为COUNT(*)会“查所有列”所以慢实际上在MySQL和SQL Server里COUNT(*)是统计所有行引擎会选成本最低的方式COUNT(字段)是统计该字段非NULL的行数。这两个语义不同不是简单的“哪个快哪个慢”问题。如果你的目标是统计总行数放心用COUNT(*)。4.4 SQL注入与参数化这条底线不能松关于止于安全。SQL注入攻击年年都有原理其实很简单开发者在拼SQL字符串时把用户输入直接当成了SQL代码的一部分。比如不加防护的登录查询sql SELECT * FROM users WHERE username user_input AND password pwd 如果用户输入的是admin --拼出来就变成了SELECT * FROM users WHERE username admin -- AND password ...--把后面的密码判断注释掉了攻击者无需密码就能登录。这是最经典的注入手法也是为什么所有数据库开发规范里都把“参数化查询”列为铁律。正确写法是用参数占位让数据库把输入当作“值”而不是“代码”cursor.execute(SELECT * FROM users WHERE username ? AND password ?, (username, password))Java的PreparedStatement、Go的database/sql占位符、Node.js的mysql2问号占位符本质上都是同一个思路。ORM框架内部都走参数绑定反而是手写SQL拼接的情况最危险。我的建议很朴素所有涉及用户输入的SQL一律参数化所有能访问数据库的应用账号按最小权限分配。这两条做到位SQL注入风险能降掉九成。像“万能密码”这类词大家当反面教材了解原理就好千万别觉得是“技巧”那是在给自己挖坑。最后再讲一个我自己的习惯写完一条SQL我会习惯性地EXPLAIN一下哪怕只是一个简单的单表查询也会看有没有触发Using filesort或者回表。这个习惯坚持了五六年帮我挡下了无数潜在慢查询。SQL从基础到优化其实没有太多高深莫测的东西就是把执行顺序、索引、执行计划这几件事揉碎了吃透然后在每次实战里验证和复盘。你要是有条件拿自己业务里最慢的几条SQL按这篇的思路走一遍我相信体感会非常直接。
