SQL优化与安全实践：从去重、窗口函数到慢查询排查的实用指南

做开发这些年最离不开也最容易被低估的就是 SQL。很多人一说优化就聊索引一说安全就聊注入可真到了线上排查问题才发现连去重怎么写干净、空值怎么处理、窗口函数什么时候能用都容易卡壳。这次我把自己常用的 SQL 技巧、慢 SQL 定位思路、注入防护、以及 SQL Server 安装连接、Navicat 导入这类工具链问题整理成一篇可以随时翻的汇总附上实际场景里的写法对比和踩坑记录希望对正在写 SQL 的你有点用。1. 高频 SQL 技巧每天都在用的那几招1.1 去重查询distinct 不是唯一答案先说去重。很多人一看到重复数据就条件反射写 distinct但实际上去重分好几层distinct、group by、row_number() 各有各的适用场景。最基础的单字段去重用 distinct 没问题比如查客户表里有多少个城市SELECT DISTINCT city FROM customer;但如果需求是“每个城市取一个最新注册的用户”distinct 就完全搞不定了。这种时候需要 row_number() 窗口函数先按城市分组、按注册时间排序再取排名第一的记录WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY create_time DESC) AS rn FROM customer ) SELECT * FROM ranked WHERE rn 1;还有个常见场景是“统计去重后的数量”。这里有个细节COUNT(DISTINCT 字段) 在数据量大时性能很差MySQL 里尤其明显。如果只是粗粒度统计可以先用 group by 子查询或者干脆把结果放到临时表再 count实测下来比 count(distinct) 快不少。我的建议是去重前先想清楚业务上“重复”的定义。是完全重复的整行记录还是某个业务键重复定义搞错了SQL 写出来必然有偏差。1.2 空值处理别让 NULL 悄悄坑了你NULL 是 SQL 里最容易被忽视的坑。它既不是空字符串也不是 0它表示“未知”。所以 NULL 参与任何比较结果都是未知也就是 false。最常见的问题出在 where 条件上。查“没有填写手机号的用户”很多人会写SELECT * FROM user WHERE phone ;但表里如果存在 NULL 值的 phone那这条记录永远查不出来。正确写法是SELECT * FROM user WHERE phone IS NULL OR phone ;再说聚合函数COUNT(phone) 不会统计 NULL 值SUM(amount) 遇到 NULL 会直接返回 NULL而不是 0。所以在做报表统计时我习惯先把可能为 NULL 的字段用 COALESCE 兜底SELECT user_id, COALESCE(SUM(amount), 0) AS total_amount FROM orders GROUP BY user_id;COALESCE 是从左到右返回第一个非 NULL 的值比 IFNULL 通用Oracle、SQL Server、MySQL、PostgreSQL 都支持。还有一点排序时 NULL 的默认位置各数据库不一样MySQL 里 NULL 默认排最前SQL Server 里 NULL 默认排最后想要统一行为就得写 ORDER BY col ASC NULLS LAST但 MySQL 8 之前不支持这种写法只能绕一下SELECT * FROM user ORDER BY (phone IS NULL), phone;这种小技巧面试和实战都经常用到值得记住。1.3 分页与排序写法和性能都要看分页是业务系统最常用的功能写法却大有讲究。早期 MySQL 的写法是 LIMIT offset, size数据量小的时候没问题一旦 offset 到几十万性能断崖式下跌因为数据库得先扫过 offset 条记录再往后取。深分页优化有两个常用思路一个是子查询先取主键再关联回原表另一个是记录上一页最后一条数据的主键或排序字段用大于号往下翻。第二种实现起来很直接-- 第一页 SELECT * FROM orders ORDER BY id LIMIT 20; -- 第二页记住上页最后一条 id 1389 SELECT * FROM orders WHERE id 1389 ORDER BY id LIMIT 20;这种方式只适合排序字段唯一且稳定的场景如果排序字段有重复值一定要在排序后面追加主键比如 ORDER BY create_time DESC, id DESC否则翻页会重复或漏数据。SQL Server 里分页建议用 OFFSET FETCHSELECT * FROM orders ORDER BY create_time DESC OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;它比老式的 ROW_NUMBER() 嵌套写法更简洁执行计划也更好。老项目如果还在用 ROW_NUMBER() 分页性能其实也能接受但新代码我一般统一用 OFFSET FETCH。2. 慢 SQL 优化先定位再动手2.1 慢 SQL 怎么被发现的慢 SQL 不是等用户投诉了才去处理。线上系统我一般会做三件事开慢查询日志、定期看监控大盘、建立 SQL 审计机制。MySQL 里设置慢查询阈值通常是小于 1 秒的都算正常超过的记录下来SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;日志会记录执行时间、锁等待时间、扫描行数等关键信息。拿到慢 SQL 之后不要急着改先问三个问题表的数据量有多大当时系统负载怎么样SQL 本身有没有明显的全表扫描迹象如果是偶发慢可能跟锁竞争或缓存失效有关不一定是 SQL 本身的问题。如果是持续慢基本就是执行计划出了问题下一步就是看执行计划。SQL Server 里没有慢查询日志这个概念但可以用 DMV 查历史慢语句这个后面在问题排查部分细说。2.2 执行计划里几个关键指标执行计划是 SQL 优化的地图。网上讲执行计划的文章很多我只说我最常用的几个指标。第一个是 typeMySQL 里从好到坏大概是 system、const、eq_ref、ref、range、index、ALL。看到 ALL 就要警惕基本是全表扫描。看到 index 也不是好信号说明走的是全索引扫描仍然把整棵索引树都扫了一遍。第二个是 rows预估扫描行数。这个数字和实际返回行数差距太大说明统计信息可能过期或者优化器选错了索引。举个例子订单表按 status 查询status 只有“已支付/未支付”两种选择性太差优化器大概率不走索引因为全表扫描更快。第三个是 ExtraMySQL 里重点关注 Using filesort 和 Using temporary。前者说明排序没有用到索引后者说明用了临时表这两种情况都是优化重点。SQL Server 看执行计划时我主要看两个东西一是缺失索引提示二是在运算符上的实际行数和预估行数。实际行数和预估行数差太多一般统计信息过期或参数嗅探问题这个后面会展开。2.3 常见慢 SQL 场景与优化写法我遇到最多的慢 SQL 场景一个是前边说过的深分页另一个是带前导通配符的模糊查询SELECT * FROM user WHERE name LIKE %张%;这种写法索引基本用不上。如果只是前缀匹配比如查“张”开头的用户写成 LIKE 张% 就能走索引。如果是后缀或中间匹配数据库层面没有太好的办法除了上搜索引擎还可以考虑建反向索引字段或者用生成列把内容反转配合反向 like 查询效果还不错。还有一类慢是“函数套字段”比如SELECT * FROM orders WHERE DATE(create_time) 2024-05-01;DATE 函数套在 create_time 上索引就失效了。改成范围查询SELECT * FROM orders WHERE create_time 2024-05-01 00:00:00 AND create_time 2024-05-02 00:00:00;这个改法执行计划立刻就不一样了明显走索引范围扫描。再有一个容易被忽略的是 OR 条件。OR 两边的字段如果只有一边有索引或者两边索引不同优化器可能直接放弃索引。改写思路是拆成 UNION ALLSELECT * FROM user WHERE phone 13800138000 UNION ALL SELECT * FROM user WHERE email testexample.com;注意用 UNION ALL 而不是 UNION因为两条分支没有交集UNION 反而多做一次去重白白消耗资源。2.4 索引设计不是越多越好说到优化就绕不开索引。我的原则是索引是为了覆盖高频查询而不是为了消灭所有慢 SQL。一个人表上挂了十几个索引写入性能一定受影响而且优化器选索引时也会纠结。建索引前先看查询条件里的列再看排序字段。最左前缀法则要理解透彻比如联合索引 (a, b, c)查询只用 b 条件这个索引用不上查询条件 a 和 c中间跳过 bc 也用不上。很多人把索引建出来但没有命中问题多半出在这。覆盖索引是个容易被忽略的优化点。如果查询只需要某几个列可以建一个覆盖这些列的索引让查询直接从索引拿数据避免回表。比如用户表经常需要查姓名和手机号建联合索引 (name, phone)SELECT name, phone FROM user WHERE name 张三 就可以不回表。但覆盖索引不是万能的索引列多了更新成本也跟着涨。我的建议是覆盖列控制在 3 到 5 个以内只放高频返回的字段。3. 窗口函数SQL 里被低估的利器3.1 窗口函数的基本语法与执行顺序窗口函数这些年越来越常用MySQL 8.0、SQL Server、Oracle、PostgreSQL 都支持。它最核心的价值是“在每一行上看到与它相关的其他行的计算结果”而不像 group by 那样把多行压成一行。基本语法SELECT order_id, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY create_time) AS running_total FROM orders;PARTITION BY 相当于分组ORDER BY 在窗口内排序不加 ORDER BY 就是全组累计。这里的 SUM 是累计和如果加 ORDER BY每行看到的就是从组内第一行到当前行的累计值。执行顺序上有个容易混淆的点窗口函数在 WHERE、GROUP BY、HAVING 之后执行所以不能在 WHERE 里直接引用窗口函数别名。需要先套子查询或 CTEWITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) SELECT * FROM ranked WHERE rn 3;MySQL 8 之前不支持窗口函数只能靠临时变量和子查询模拟写法很痛苦。升级到 8.0 之后这类需求清爽很多。3.2 排名、分组 TopN、同比环比的实际写法窗口函数最常见的三个场景排名、分组 TopN、同比环比。排名有三种ROW_NUMBER、RANK、DENSE_RANK区别在并列时怎么处理。ROW_NUMBER 不管是否并列都给唯一序号RANK 有并列会跳号DENSE_RANK 并列不跳号。考试排名用 DENSE_RANK 更合理。分组 TopN 上面已经写过就是 PARTITION BY 组内排序后取 rn 前 N 条。这里有个优化点如果每组数据量特别大窗口函数需要把所有组内数据全排完内存压力不小。可以结合索引把排序字段建好让数据库用索引顺序直接计算。同比环比用 LAG 和 LEAD 函数非常方便。LAG 取上一行LEAD 取下一行。比如查订单表里每天订单数环比WITH daily AS ( SELECT order_date, COUNT(*) AS cnt FROM orders GROUP BY order_date ) SELECT order_date, cnt, LAG(cnt, 1) OVER (ORDER BY order_date) AS prev_cnt, ROUND((cnt - LAG(cnt, 1) OVER (ORDER BY order_date)) * 100.0 / LAG(cnt, 1) OVER (ORDER BY order_date), 2) AS growth_rate FROM daily;注意这里 LAG 写了两次虽然啰嗦但能复用。如果觉得维护麻烦可以再包一层 CTE。窗口函数可以嵌套在子查询里但不能在同层 SELECT 里直接引用另一个窗口函数的别名这个语法约束要记住。4. SQL 注入与安全防线从原理到堵漏4.1 万能密码过绕过是怎么发生的“万能密码”是 SQL 注入的经典例子原理在于 SQL 语句的拼接把用户输入当成了代码的一部分。比如登录校验写成SELECT * FROM user WHERE username admin AND password 任意值 -- ;如果用户在用户名输入框填了 admin -- 拼出来的 SQL 就变成了SELECT * FROM user WHERE username admin -- AND password 任意值;两个中划线把密码校验条件直接注释掉了查询立刻返回 admin 用户攻击者就能绕过登录。还可以输入 OR 11 -- 让条件恒真SELECT * FROM user WHERE username OR 11 -- AND password xxx;这类攻击能成功根本原因不是某个函数有 bug而是开发时把不可信的用户输入直接拼进了 SQL 语句。Python 连接数据库时如果采用 % 格式化拼接参数也有同样风险这里不展开具体框架核心是务必要用参数化查询。4.2 日常写 SQL 的防御习惯防御 SQL 注入最有效、成本最低的手段是参数化查询。MyBatis 里用 #{} 而不是 ${}JDBC 里用 PreparedStatement 而不是 StatementPython 里用参数占位符而不是字符串拼接这些是底线。大概很多人会想${} 在某些动态排序场景又方便应该没事吧我见过不止一次因为图方便把排序字段用 ${} 拼进去结果被扫描工具报漏洞的案例。排序字段其实可以用白名单映射前端传一个排序 key后端根据 key 查映射表得到真实字段名不要在 SQL 里直接拼。还有数据库账号权限的最小化应用账号只授予 DML 权限不给 DDL不给文件读取权限即使注入发生破坏面也有限。还有一些框架自带的防护比如 MyBatis Plus 的 QueryWrapper 用起来本身是参数化的但要注意不要对字段名做动态拼接。关于 AI 生成 SQL现在很多人喜欢把需求丢给大模型直接出语句效率确实高但一定要检查一条生成结果里有没有拼接用户输入的痕迹有没有把变量直接嵌在 SQL 字符串里。AI 生成的 SQL 跑通容易要再过一眼安全和性能再上线。5. 工具与工程化从生成 SQL 到导入数据5.1 SQL Server 安装和连接报错的排查SQL Server 相关的热搜词里安装失败和连接报错占了很大一部分。我先把最常见的几类问题梳理一下。安装失败。很多人是在 Windows 上装 SQL Server 报错常见是“SQL Server 安装程序失败”或者某个功能安装卡住。经验上先做三件事关掉杀毒软件和 Windows Defender 实时防护确保安装文件解压路径是英文且没有空格以管理员身份运行安装程序。如果之前装过失败残留还需要清理注册表和安装目录这个步骤比较繁琐装之前最好改一下安装路径避免和旧版本冲突。连接报错更经典的是“已成功与服务器建立连接但是在登录过程中发生错误”。这个问题通常不是网络问题而是登录方式或密码策略问题。SQL Server 默认可能是 Windows 身份验证SQL Server 身份验证被禁用了需要在 SSMS 中用 Windows 身份登录后在服务器属性的安全性里启用“SQL Server 和 Windows 身份验证模式”。还有时候是密码过期或密码策略限制安装时默认开启了密码复杂度检查简单密码会被拒。如果一直连不上检查 SQL Server 服务是否启动、TCP/IP 协议是否启用。右键打开 SQL Server 配置管理器确保 Named Pipes 和 TCP/IP 都是 Enabled。5.2 Navicat 导入 SQL 脚本的坑Navicat 是很多人日常管理数据库的工具导入 SQL 脚本看起来很简单文件拖进去点开始就行但坑也不少。最大的坑是编码。我遇到过一份中文 SQL 脚本Navicat 导入后中文全部乱码原因是文件本身是 UTF-8但 Navicat 按 GBK 读取了。导入前先在文件里确认编码或者在 Navicat 连接属性里把编码设置好。如果是 MySQL连接属性里可以指定编码导入前先看一眼表的字符集。第二个坑是 SQL 脚本里带了 USE 语句或跨库操作。Navicat 在导入时如果脚本里切了库可能因为账号权限不足而中断。导入前把脚本里不需要的 USE 语句删掉或者单独跑。第三个坑是批量导入大批量数据时Navicat 默认每条语句一条执行速度会非常慢。遇到几百万行 insert建议用命令行方式导入。如果只是日常同步几十万数据可以把待导入数据先导出成 CSV再通过 Navicat 的导入向导走批量模式比直接跑 insert 脚本快几个量级。5.3 根据 Java 实体类生成建表 SQL以及 AI 生成 SQL 的姿势MyBatis Plus 在很多项目里是标配但“根据 Java 实体类生成建表 SQL”这件事框架本身并没有一键生成而是需要结合代码生成器或手写。最省事的方案是先用 MyBatis Plus Generator 生成实体类再配合 Flyway 这类迁移工具管理表结构把建表 SQL 放进迁移脚本里交给 CI 执行。写建表 SQL 时Java 类型和数据库类型的映射要特别小心。Long 对应 BIGINTString 对应 VARCHAR需不需要指定长度取决于字段语义。比如状态枚举值VARCHAR(2) 就够用户名字段建议 VARCHAR(50)不要一上来就 VARCHAR(255)。BigDecimal 对应 DECIMAL必须指定精度和小数位比如 DECIMAL(10,2)否则默认 DECIMAL(10,0) 会把人坑死。索引也不要跟 DDL 一样全建。普通业务表主键索引必建外键键和唯一约束按业务语义建高频查询条件建联合索引。我见过实体类里加了个 TableIndex 注解就生成一堆索引的项目上线后写入慢得不行。AI 生成 SQL 这个话题最近特别热我的态度是能用但要当“初级工程师”使。给它建表语句让它写查询大部分场景结果都不错但前提是你自己要能看明白执行计划能判断它生成的 SQL 是否合理。AI 容易犯的错包括该用 UNION ALL 却写 UNION、忘记加 WHERE 条件导致全表扫描、窗口函数套错层级。生成之后先 EXPLAIN 一下再上生产这个习惯千万不能丢。6. SQL 面试题速查与避坑清单6.1 高频面试题里暗藏的知识点面试题看似零散其实考察的都是基本功。整理几个高频的。第一个DROP、TRUNCATE、DELETE 的区别。DROP 是删除表结构TRUNCATE 清空数据但保留表结构DELETE 可以带 WHERE 删部分数据。事务上 DELETE 可以回滚TRUNCATE 和 DROP 在多数数据库里默认不能按行回滚。性能上 TRUNCATE 比 DELETE 快很多因为它不做逐行日志。第二个内连接、左连接、右连接、全连接的区别以及连接后再去重。实际工作中最多的是 LEFT JOIN最容易出错的是 LEFT JOIN 后 WHERE 条件误伤主表数据。比如SELECT * FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE u.status 1;这个写法会把没有匹配到用户或用户状态不为 1 的订单都过滤掉LEFT JOIN 名存实亡。想保留所有订单用户不满足条件时为 NULL得把用户条件放到 ON 里SELECT * FROM orders o LEFT JOIN users u ON o.user_id u.id AND u.status 1;这个细节经常在面试和 code review 里出现。第三个HAVING 和 WHERE 的区别。WHERE 在分组前过滤HAVING 在分组后过滤。配合聚合函数时尤其要注意查询分组后的记录数大于 10 的分组只能用 HAVING COUNT(*) 10不能写在 WHERE 里。第四个CHAR 和 VARCHAR 的区别。CHAR 定长VARCHAR 变长。CHAR 会补空格到固定长度读取时可能要做 TRIMVARCHAR 按实际长度存储存储效率更好。面试里常考的是 VARCHAR(100) 和 VARCHAR(10) 存相同短字符串时占用空间是否相同答案是存储长度取决于实际数据长度但字段定义里的最大长度会影响索引统计和内存分配。6.2 能救命的 SQL 避坑清单最后分享一个我自己总结的避坑清单都是实际工作中踩过的。不要相信默认排序。没有 ORDER BY 的查询结果顺序不受任何保证。同一个 SQL 跑两次结果不同完全正常不要觉得是玄学。SELECT 不要滥用星号。生产代码里 SELECT * 会让覆盖索引失效也会增加网络传输还会在表结构变更后悄悄改变查询结果。写字段名花不了几秒钟排查问题省的是几小时。DATE 范围查询边界要小心。如果要查某一天的数据大于等于当天零点且小于次日零点包含次日零点会导致重复数据。批量更新别一把梭。UPDATE 全表或大范围数据建议分批提交单批 1000 到 5000 行。不然行锁范围大、事务日志膨胀可能直接拖垮数据库。最后一点SQL 写完后养成 EXPLAIN 的习惯。不管是手写还是 AI 生成先看执行计划再跑几秒钟的事能避开绝大多数慢 SQL。我个人的体会是SQL 没有那么多高深莫测的东西核心就是把执行计划看懂、把数据特征摸清剩下的就是多写多踩坑。这篇汇总里大部分内容都来自实际项目的修改记录下次再遇到类似问题直接翻出来对着处理就行。