我做了十几年数据相关工作每年都会整理一遍自己写过的 SQL 脚本和踩坑记录。看到“常用 SQL 技巧与问题汇总”这个题目我其实挺有感触的SQL 这玩意儿入门容易写好写快真难。热搜词里有一大堆人搜“SQL Server 安装失败”“Navicat 怎么导入数据”“sql 语句去重”“慢 sql 优化”说明大家在日常工作中翻来覆去遇到的基本就是这些事。这篇文章我就把自己实际工作中沉淀下来的那些 SQL 技巧、排查思路、还有各类工具使用时的坑一次性梳理清楚。内容会覆盖几个大块高频 SQL 写法与性能调优、窗口函数的实战应用、慢 SQL 排查套路、常用数据库工具的隐藏技巧以及安全相关的 SQL 注入防御等。不管你是刚入行的同学还是带团队的老人这篇文章都适合静下心来翻一翻很多细节是我查了无数资料、踩了无数坑才总结出来的希望能帮你在工作中少走弯路。1. 从基本功到实战那些高频 SQL 写法的正确姿势1.1 数据去重别只会用 DISTINCT数据去重是很多人每天都要面对的需求。热搜里“sql 语句去重”“清洗 sql 语句去重”反复出现但我发现好多人对去重的理解还停留在DISTINCT这个关键字上。其实 DISTINCT 有一个天然的使用限制它对 SELECT 出来的整行数据去重如果两行数据中有一个字段不同那这两行就都会保留。很多时候我们的真实需求是“根据某个业务主键去重保留其中最新的一条”这时候 DISTINCT 就完全不够用了。我这里给出一套在实际项目中用得最多的去重方案用ROW_NUMBER()窗口函数配合分区字段来实现按组去重这是目前最灵活、性能也可控的方式。使用GROUP BY加聚合函数适合做明细汇总级去重。小表场景可以直接用临时表加主键约束去重简单粗暴。以“订单表按用户 ID 去重取最近一笔订单”为例SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE t.rn 1;这个写法在几乎所有主流数据库MySQL 8.0、SQL Server、Oracle、PostgreSQL里都能跑通用性非常好。核心逻辑就是先按用户分组组内按时间倒序编号最后只保留每组里编号为 1 的那条记录。需要注意一个细节如果user_id有 NULL 值PARTITION BY会把所有 NULL 归为一组这可能导致结果里多出一条或丢失记录。我建议先去重前用COALESCE(user_id, unknown)处理一下空值。1.2 空值处理NULL 是个让你防不胜防的坑搜索词里“sql 去除空值”这个热度一直很高说明大家在空值处理上确实吃过不少亏。NULL 在 SQL 里不是 0也不是空字符串它代表“未知”。所以,NULL 参与任何运算结果都是 NULL。举几个常见的坑我估计每个人都踩过至少一个WHERE column NULL永远查不到数据必须用IS NULL。NULL 1的结果是 NULL不是 1。COUNT(column)会忽略 NULL但COUNT(*)不会。CASE WHEN column THEN 空 ELSE 非空 END对 NULL 不生效因为 NULL 的结果是未知不会进入 THEN 分支。实际处理空值的推荐写法是SELECT COALESCE(user_name, 未知用户) AS user_name, NULLIF(phone, ) AS phone FROM user_profile;COALESCE返回第一个非 NULL 的值NULLIF则是把指定的值转换成 NULL。这两个函数在数据清洗阶段几乎天天用到。我个人的习惯是在数仓 ETL 层统一用COALESCE把关键字段全部填上默认值避免下游分析脚本因为处理 NULL 而反复报错。1.3 CASE WHEN 与条件统计用一条 SQL 完成多维报表在写统计报表时很多新人喜欢把一个维度拆成好几条 SQL 分别执行再回 Java 或 Excel 里拼接。其实用CASE WHEN完全可以一次性把多维度的统计结果算出来。比如我要统计不同状态订单的数量和占比SELECT date(order_time) AS order_date, COUNT(*) AS total_order, SUM(CASE WHEN order_status completed THEN 1 ELSE 0 END) AS completed_order, SUM(CASE WHEN order_status cancelled THEN 1 ELSE 0 END) AS cancelled_order, ROUND(SUM(CASE WHEN order_status completed THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS completion_rate FROM orders WHERE order_time 2024-01-01 GROUP BY date(order_time) ORDER BY order_date;这里有个关键点SUM(CASE WHEN xxx THEN 1 ELSE 0 END)的效果等价于COUNT(CASE WHEN xxx THEN 1 END)但前者在可读性上更直观也方便后续扩展成复杂的多条件判断逻辑。另外要注意用* 100.0而不是* 100否则整数相除会把小数抹掉这是新手最常见的低级错误。1.4 常用 SQL 复习与面试核心考点很多人会搜“sql 面试题”和“sql 语句复习”说明在准备面试或日常基础巩固阶段大家需要一份知识框架。我总结了一下面试和真实工作中都逃不掉的核心考点三范式与反范式设计什么时候该拆表什么时候该冗余字段。多表关联的差异INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN 各自的数据特征。GROUP BY 与 HAVING 的执行顺序先 WHERE 过滤再 GROUP BY 分组然后 HAVING 对组过滤最后 ORDER BY 排序。子查询与 CTE 的选择现代 SQL 用 WITH 子句通常比子查询更清晰而且便于后续复用。索引失效场景函数包裹索引列、隐式类型转换、前导模糊匹配等。事务隔离级别与锁机制脏读、不可重复读、幻读分别在哪个隔离级别下被解决。这些内容看起来基础却是地基。地基不稳后面写出来的 SQL 就是定时炸弹。2. 窗口函数实战把复杂的“分组取前N条”变简单2.1 窗口函数到底解决了什么痛点在没有窗口函数的日子里如果你想实现“每个部门工资最高的员工”“每个用户最近一次登录记录”这类需求通常要写自连接、相关子查询或者嵌套去重代码又长又难懂。窗口函数直接在结果集上开一个“窗口”在窗口内进行排序、聚合、偏移计算再返回每一行对应的结果。目前主流数据库里MySQL 8.0 及以上、SQL Server 2005 及以上、Oracle 8i 及以上、PostgreSQL 全都支持窗口函数。如果你的生产环境版本比较老比如 MySQL 5.7那确实用不了这个功能需要另想办法或用子查询替代。2.2 四种最常用的窗口函数ROW_NUMBER()给窗口内的行按顺序编号 1、2、3不重复不跳号。RANK()排名相同会重复但会跳号。比如并列第二后直接是第四。DENSE_RANK()排名相同重复但不跳号。并列第二后还是第三。SUM() OVER()、AVG() OVER()这类聚合窗口函数可以在结果集每一行上同时显示“当前行的值”和“截至当前行或全组的累计值”。以“按产品分类展示销售额并计算累计占比”为例SELECT product_category, sales_amount, SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sales_date) AS cumulative_sales, ROUND(sales_amount * 100.0 / SUM(sales_amount) OVER (PARTITION BY product_category), 2) AS pct FROM product_sales;这段 SQL 实现的效果是每一行都能看到当前产品的销售额、该分类下按日期累加的销售额、以及该产品在分类内的销售占比。这在业务看板开发里非常常用能省掉一大把在应用层做循环计算的逻辑。2.3 窗口函数性能优化要点窗口函数虽然好用但性能上有个天然开销它通常需要对分区字段排序。比如ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC)在底层执行时基本等于对(user_id, create_time)做一次排序。数据量大时这个排序就是性能瓶颈。我的优化建议是三个“必须”必须建好联合索引排序字段和分区字段要一起纳入索引设计。必须避免在窗口函数中使用表达式比如ORDER BY DATE(create_time)会让索引失效。必须控制数据扫描范围先用 WHERE 条件把文件缩小到合理范围再开窗口。2.4 经典面试题分组 TopN 用代码说话“每组取前 N 条”是面试高频题也是最容易体现 SQL 功底的题。以“每个部门薪资最高的前三名员工”为例WITH ranked_emp AS ( SELECT emp_name, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees ) SELECT * FROM ranked_emp WHERE rn 3;这里我要强调一个容易忽略的细节如果用RANK()替代ROW_NUMBER()则当薪资并列时可能出现第 2 名后直接蹦到第 4 名的情况导致结果超过三条。而面试官往往会追问“那并列怎么处理”这时候才是真正区分求职者理解深度的关键时刻。我一般会根据业务语义选择如果允许并列就选RANK()或DENSE_RANK()如果必须只取三个人就用ROW_NUMBER()。3. 慢 SQL 优化从执行计划到索引设计的完整套路3.1 一条慢 SQL 的排查路径“慢 sql 优化”“并行 sql 优化”“oracle sql 性能优化”这些热搜词说明大家在真实环境里都被慢查询折磨过。我处理慢 SQL 的顺序几乎固定分享给各位第一步先逮住那条 SQL。MySQL 里开启慢查询日志SQL Server 里看 DMVOracle 用 AWR 报告无论哪个平台先定位 SQL 文本以及执行次数、平均耗时。第二步看执行计划。执行计划里最关键的信息是访问类型和扫描行数。如果看到全表扫描ALL或者扫描了上百万行后最终返回几十行基本就是索引问题或查询条件设计有问题。第三步分析表结构和数据分布。确认表中数据量、重复度、索引情况判断当前 SQL 写法能不能利用上索引。第四步改写 SQL 或调整索引。改写不是乱改是在不改变结果的前提下重写语义比如拆分复杂 SQL、去除无关子句、更换 JOIN 顺序。第五步验证和回归。用 EXPLAIN 查看执行计划是否变成预期访问方式同时对比优化前后的实际耗时。3.2 看执行计划MySQL 的 type 字段MySQL 执行计划里type字段从好到差大概是这样system表只有一行极端场景。const主键或唯一索引命中最多返回一条。eq_ref多表 JOIN 时被驱动表通过主键或唯一索引匹配。ref普通二级索引命中。range索引范围扫描如BETWEEN、、。index扫了整个索引树但比全表快一点。ALL全表扫描SQL 性能预警信号。大多数优化目标就是把ALL或index提升到ref或range。这也意味着需要为 WHERE 条件中的过滤字段建立合适的索引。3.3 索引设计最核心的优化手段很多人知道“查询慢要建索引”但具体怎么建建在哪些列上字段顺序是什么却一知半解。索引设计里面有三个重要原则等值查询字段放在索引最前面排序字段放在后面范围查询字段最后放。尽量使用覆盖索引让查询列全部包含在索引里避免回表。区分度低的字段不要单独建索引比如性别、状态位这类重复值高的字段单独建索引优化效果很差。我举个例子。现在有一张订单表 orders常用查询是“查某用户在某个时间段的订单”。那么索引设计可以这样ALTER TABLE orders ADD INDEX idx_user_time (user_id, order_time DESC);这里把user_id放前面是因为它是等值条件order_time放后面是排序加范围条件。如果反过来把order_time放前面user_id放后面查询WHERE user_id xxx时可能无法充分利用索引。3.4 常见索引失效场景速查表我整理了一份索引失效的场景清单基本都是工作里反复见过的问题场景示例原因左模糊匹配LIKE %keyword无法使用索引树定位起点索引列使用函数WHERE DATE(create_time) 2024-01-01函数导致索引序列被打乱隐式类型转换WHERE phone 138xxxxphone 是 varchar数据库会把字段转成数值索引失效OR 连接非索引列WHERE id 1 OR status 0优化器可能放弃索引合并范围条件后的等值条件WHERE time 2024 AND user_id x范围查询后索引无法继续精确定位使用不等于WHERE status ! finished不等于基本走不了索引排序方向不一致ORDER BY col ASC但索引是 DESC可能额外排序技巧排查这类问题时我习惯在 EXPLAIN 后加一个SHOW WARNINGSMySQL 会展示优化器改写过后的 SQL能帮你看出它到底怎么理解你写的条件的。3.5 分页查询优化的实战写法分页深翻页是个经典痛点。LIMIT 1000000, 20这种写法MySQL 会扫描 1000020 行然后丢弃前 1000000 行代价非常大。我常用的优化方案有两个。方案一是基于游标的延迟关联SELECT * FROM orders JOIN ( SELECT id FROM orders WHERE create_time 2024-01-01 ORDER BY id LIMIT 1000000, 20 ) tmp ON orders.id tmp.id;内层子查询只查主键然后外层回表取完整数据性能提升常常是数量级的。方案二是基于排序字段的 WHERE 条件翻页SELECT * FROM orders WHERE create_time 2024-01-01 AND id {last_seen_id} ORDER BY id LIMIT 20;这种方案适合按 id 顺序翻页的场景不需要记住偏移量只需传上一页最后一个 id性能非常稳定。4. 实用工具与场景方案Navicat、MyBatis-Plus 与代码生成4.1 Navicat 导入 SQL 数据的正确姿势“navicate如何导入sql数据”这个搜索词上榜我觉得很多人的问题不是“不会导入”而是“导入时老出错”。Navicat 导入 SQL 文件其实有三种常见途径各有适用场景直接打开 SQL 文件并执行适合小文件、单次执行。右键连接 - 运行 SQL 文件选择.sql文件即可。优点是操作简单缺点是出错了不好定位特别是一个文件里包含建表和 INSERT 语句时如果中间某条语句报错后面可能就停了。数据传输工具适合把一个库的表结构和数据整体搬到另一个库。在连接上右键 - 数据传输可以自定义选择表也可以选择仅结构或仅数据。命令行导入适合超大文件。用mysql -u root -p database_name file.sql这种方式速度比图形界面快很多还能避免工具超时问题。我心里的一个坑是导入前一定要检查 SQL 文件里的字符集是不是跟目标库一致。如果文件是 UTF-8 而目标表是 latin1导完后中文就是乱码。最好统一在连接属性里设置编码为 UTF-8。4.2 MyBatis-Plus 根据实体类生成建表 SQL“mybatisplus根据java实体类生成创建表的sql语句”这个搜索词很有意思说明现在很多人不写原生建表语句而是想从 Java 实体类直接生成表结构。MyBatis-Plus 提供了多种方式来实现这个需求。一种方式是利用 MyBatis-Plus Generator 的能力配合数据库逆向生成从表结构生成实体类但这是反过来的方向。真正想从实体类生成建表 SQL通常需要借助数据库工具内置的表结构生成器或第三方插件例如用 IDEA 的 JPA Buddy、Database Tool 同步实体和表结构或者在 MyBatis-Plus 里使用扩展特性。我的建议是小项目可以直接用手写 SQL 建表因为可控中大型项目用 Flyway 或 Liquibase 这套数据库版本管理工具来管理表结构变更实体类只作为 ORM 映射不让它反向驱动表结构。原因很简单实体类里的字段注解不总是能表达出索引、约束、外键等数据库细节过度依赖实体类生成表结构容易丢掉性能设计和数据安全设计。4.3 SQL Server 安装问题与常见故障排查“SQL Server 安装失败”“SQL Server 2019 安装教程”“SQL Server 2022 关闭密码策略”这类热搜词霸屏。SQL Server 的安装和后续故障排查确实是很多运维同学头疼的事。我在这里集中列出几个高频问题和高概率解决方案安装时提示“此计算机上安装了 Microsoft Visual Studio 2015 的早期版本”多见于 SQL Server 2016/2017 安装失败。解决方案是检查系统已安装的程序卸载不相容的 Visual C 相关组件然后重新运行安装程序。SQL Server 服务无法启动常见原因是服务账户权限不对或者端口被占用。建议在服务管理器里确认 SQL Server 服务使用的账号是否具备目录读写权限同时用netstat -ano | findstr 1433检查端口是否冲突。出现“已成功与服务器建立连接但是在登录过程中发生错误”的报错很多人以为密码错了其实多数时候是协议或证书问题。优先检查客户端协议是否启用了 TCP/IP并按提示查看 SQL Server 错误日志。经验SQL Server 安装前先把 Windows 更新和所有杀毒软件暂停再把安装包完整解压到纯英文路径下能避免 80% 的诡异报错。我见过很多次“SQL 安装失败”其实是因为安装包放在带中文或空格的目录里导致路径解析失败。4.4 SQL Server 数据库文件附加与日志问题处理搜索词里提到“sql server writelog”和“SQL Server 2000 Desktop Engine 命令行”看起来是有朋友在处理数据库附加、日志文件异常的场景。这里我要特别说一下MDF/LDF文件附加时可能会遇到的问题。数据库文件分离后重新附加如果没有 LDF 日志文件SQL Server 通常会拒绝附加。这时可以用命令行方式重建日志文件比如CREATE DATABASE [DemoDB] ON (FILENAME ND:\Data\DemoDB.mdf) FOR ATTACH_REBUILD_LOG;FOR ATTACH_REBUILD_LOG会在附加时重建缺失的日志文件这个命令能解决很多“日志文件丢失/损坏导致附加失败”的问题。但需要注意如果原库本身处于异常关闭状态执行这条指令前最好先备份一份 MDF 文件的副本防止二次损坏。4.5 命令行执行 SQL 脚本的通用方法“mysql执行sql脚本”“sql server 2000 desktop engine 命令行”让我想到很多人在没有图形界面工具的情况下还是要执行 SQL 脚本。我列出几个平台上最常用的命令行执行方式平台命令示例说明MySQLmysql -h localhost -u root -p dbname script.sql需要把脚本文件路径指正确PostgreSQLpsql -h localhost -U postgres -d dbname -f script.sql-f参数执行脚本文件SQL Serversqlcmd -S localhost -U sa -P password -d dbname -i script.sql使用 sqlcmd 工具注意-i输入文件Oraclesqlplus user/passorcl script.sqlsqlplus 自动读入脚本并执行需要提醒的是执行大文件脚本时尽量先在事务内包裹或者在脚本头部加上错误处理逻辑。比如 MySQL 客户端可以加--force参数让错误后继续执行但这样容易造成数据半成品状态。更稳妥的做法是先备份再分段执行并在关键步骤用日志记录执行位置。5. SQL 安全问题SQL 注入的原理、危害与防护5.1 SQL 注入是怎么发生的“sql注入万能密码绕过”“python sql注入原理”“bwapp”“[swpuctf 2021 新生赛]sql”这些关键词说明很多人都在学习 SQL 注入的攻防原理。我在这里明确一点SQL 注入的本质是把用户输入的数据片段拼接进了 SQL 语句里导致数据库把用户输入当成代码执行。一个经典漏洞示例String sql SELECT * FROM users WHERE username username AND password password ;如果用户输入的用户名是admin --拼出来的 SQL 就变成SELECT * FROM users WHERE username admin -- AND password xxx双横线把后面的条件注释掉了这时只要知道 admin 用户名不需要密码就能登录。这就是网上常说的“万能密码”的基础原理。5.2 防护 SQL 注入的正确姿势防护 SQL 注入的原则其实很简单永远不要信任用户的任何输入所有 SQL 参数必须经过参数化处理。参数化的含义是用户在请求里输入的值只作为数据传入 SQL而不是作为 SQL 代码的一部分。以 Java 的 JDBC 为例正确写法是使用PreparedStatementString sql SELECT * FROM users WHERE username ? AND password ?; PreparedStatement ps connection.prepareStatement(sql); ps.setString(1, username); ps.setString(2, password); ResultSet rs ps.executeQuery();这样即使输入admin --数据库也只会把它当成字符串字面量绝不会触发注释逻辑。同理MyBatis 框架里要使用#{}而不是${}来传递参数#{}会生成占位符预编译语句${}则是直接字符串拼接存在注入风险。5.3 数据库权限最小化原则除了参数化查询数据库权限设计也是防注入的第二道防线。很多公司的应用账号权限给得过大一句注入就能拖库甚至删库。我强烈建议应用连接数据库的账号只授予 SELECT、INSERT、UPDATE、DELETE 权限绝不授予 DDL 权限。分离管理账号和应用账号禁止应用账号有FILE、SUPER这类敏感权限。核心敏感表用户表、支付表做单独的权限控制和脱敏策略不要把所有数据都放在一个通用账号下。提示即使是内部后台系统也要做参数化和权限控制。我见过不止一次内部系统因为图方便直接拼接 SQL被同事测试时一句OR 11把整个表 dump 出来。内部威胁并不比外部攻击少。5.4 MyBatis-Plus 与原生 SQL 中的安全实践MyBatis-Plus 确实能简化开发但它的QueryWrapper、LambdaQueryWrapper也不是银弹。当你在 Wrapper 里使用apply方法拼接自定义 SQL 片段时仍然要小心用户输入。apply的内容是直接进入 SQL 的如果里面包含未转义的用户输入一样会出问题。MyBatis 注解里写原生 SQL 时同样要用#{}参数Select(SELECT * FROM users WHERE username #{username}) ListUser findByUsername(Param(username) String username);我处理过多个因为直接在注解里用${}拼接排序字段、where 条件而导致的线上注入漏洞。特别是排序字段很多人觉得“排序字段总不可能注入”但实际上下发一个子查询或者报错信息就能泄露数据不要低估任何输入入口。6. Hive SQL 与并行优化大数据场景下的特殊问题6.1 Hive SQL 与关系型数据库 SQL 的差异“hive sql”在热搜里出现说明数据开发岗的朋友也在搜索 Hive 相关的技巧。Hive SQL 语法上接近 MySQL但底层是 MapReduce/Tez/Spark 引擎所以很多 SQL 写法和优化思路完全不同。第一个重大差异是 Hive 支持多表INSERT可以一次扫描源表写多个目标表减少源表读取次数FROM user_logs INSERT OVERWRITE TABLE dws_user_logs_part PARTITION (dt2024-01-01) SELECT user_id, action, count(*) GROUP BY user_id, action INSERT OVERWRITE TABLE dws_user_daily_stats PARTITION (dt2024-01-01) SELECT user_id, count(*) GROUP BY user_id;第二个差异是 Hive 中ORDER BY是全局排序会产生一个 reducer 处理所有数据代价极大。通常在数据量很大时不要使用ORDER BY而是改用SORT BY每个 reducer 内排序或DISTRIBUTE BYSORT BY配合。第三个差异是 Hive 的谓词下推。写成子查询时要注意如果子查询没有WHERE过滤它可能会加载全表数据再过滤。标准做法是尽早使用分区裁剪把 WHERE 条件下沉到最里层。6.2 并行 SQL 优化以 Oracle 为例搜索词里“并行sql优化”指的通常是 Oracle 的并行执行特性。Oracle 的并行查询是把一个 SQL 的执行拆分成多个并行子任务可以极大缩短大查询的响应时间但也容易引发资源争抢和系统波动。我见到过的最常见的并行优化误用场景是开发人员为了让一条查询变快随手加了/* PARALLEL(8) */提示结果把服务器 CPU 打满影响了其他所有业务。并行并不是越快越好它只适合大表全量扫描。大表和大表之间的 Hash Join。数据仓库类型的大型分析查询。对于 OLTP 类型的短查询并行反而是负优化。我建议如果你要用并行至少要通过v$session_longops或 Oracle 性能报告来确认 SQL 执行阶段到底消耗在哪再做针对性调整。6.3 Hive 数据倾斜处理经验数据倾斜是 Hive SQL 开发里最常遇到的问题。最常见表现是某个 key 的数据量极多导致一个 reduce 任务卡住很久其他 reduce 早都跑完了。解决方案一般有三种两阶段聚合先用group by将数据打散到足够多的唯一键再对结果二次聚合。添加随机前缀把倾斜的 key比如一个空值 user_id 占了 99%加上随机后缀让它分散到不同 reduce。对小表做 MapJoin 广播避免在大表关联时把大量 key 集中在同一个 reduce。具体操作例子比如处理空值导致的倾斜SELECT COALESCE(user_id, unknown) AS user_key, COUNT(*) FROM logs WHERE dt 2024-01-01 GROUP BY COALESCE(user_id, unknown);如果在 GROUP BY 之前不处理 NULL多个空值都会落到同一个 NULL 组形成严重倾斜。先转成常态字符串至少能让倾斜 redistribute 到正常逻辑里进一步再用加盐方案解决。7. 高频系统问题速查与解决实录7.1 SQL Server 登录失败问题排查方法热搜词里有“sql server 已成功与服务器建立连接,但是在登录前”这个问题我反复遇到过通常出现在以下几种情况场景报错特征解决方案密码错误或密码策略限制报告“用户登录失败”确认密码复杂度或由管理员重置密码混合认证模式未开启无法用 SQL 账号登录在服务端设置为 Windows 和 SQL Server 混合模式认证TCP/IP 未启用客户端连接超时或报协议错误打开 SQL Server 配置管理器启用 TCP/IPTLS 版本不兼容登录过程中握手失败更新客户端驱动或启用相应 TLS 协议默认数据库不存在或被删除建立连接后登录报错用管理员账号将用户默认数据库改为 master 或其他存在库我处理过的最离奇的一个案例是某个服务的登录账号默认数据库被运维清理掉了导致每次登录都报错。从表面看像是账号权限问题查了很多日志才发现是默认数据库缺失。这个问题在 SQL Server 里可以通过将上下文切到 master 后重建默认库权限解决。7.2 MySQL 执行 SQL 脚本时编码和超时问题使用mysql命令导入大 SQL 文件时我遇到过几个特无语的问题脚本文件有前导 BOM 字符导致第一行表名解析失败。文件包含DELIMITER $$存储过程语法直接在 Navicat 里运行时报错。max_allowed_packet配置太小导入大字段时提示“Packet too large”。解决方法分别是导入前用编辑器将文件转成 UTF-8 无 BOM 格式存储过程脚本用mysql命令行而非图形界面执行在 MySQL 配置里调大max_allowed_packet。一个小技巧是导入前先SET FOREIGN_KEY_CHECKS0;可以在多表关联插入时避免外键校验带来的性能损失导入完再恢复。7.3 Navicat 导入导出数据时的常见陷阱用 Navicat 导入导出的场景实在太多我整理几个容易踩的点导出时没有勾选“包含建表语句”导致在目标库执行时提示表不存在。导入时如果目标表已存在且字段顺序不一致数据会错位。建议先删表或选择“删除表数据再插入”。CSV 导入时日期格式不规范比如2024-01-01 12:00:00在某些区域设置下会变成2024-01-01 12:00导致时间精度丢失。大批量数据导入时没有提交分批导致事务过大内存暴涨甚至锁表。我在做 MySQL 到 SQL Server 的数据迁移时一般不会直接用 Navicat 的数据传输而是把数据导成 CSV 再通过各平台的原生导入工具处理虽然多了一步但稳定性提高了很多。7.4 SQL Server Management Studio 的实用配置搜索词里多次出现“sql server management studio 下载”“SQL Server Management 2012 下载及安装”说明很多人还在找版本适配的客户端工具。SSMS 是官方免费的图形化管理工具目前推荐直接安装最新版它能向前兼容管理大多数 SQL Server 版本。日常使用 SSMS 有几个非常提效的设置在“工具 - 选项 - 查询执行 - SQL Server - 高级”里勾选“SET SHOWPLAN_ALL”可以默认显示执行计划。使用“结果到网格”和“结果到文件”切换快捷键 CtrlD / CtrlShiftF。在查询编辑器里输入SET STATISTICS IO ON;和SET STATISTICS TIME ON;能看到每个表的逻辑读数和 CPU 耗时这对性能分析极有帮助。使用活动监视器定位当前阻塞和长时间运行会话。8. 我的老生常谈SQL 开发的十条心法写到最后分享十条我从实际工作中总结出来的 SQL 开发心法也算是对这篇文章的一个沉淀第一写 SQL 前先想清楚数据模型和索引结构写完再调优的成本远高于提前设计。第二永远不要让 SELECT * 出现在生产代码里需要什么列就查什么列。第三WHERE 条件里的字段尽量不做函数包裹和类型转换保持索引的可用性。第四大查询一定要设置合理的超时时间避免长事务拖垮数据库。第五表连接时小表驱动大表尽量用 JOIN 而不是嵌套子查询。第六正确理解 COUNT(*) 和 COUNT(1) 没有性能差异但 COUNT(列名) 会忽略 NULL语义要搞清楚。第七做数据更新和删除前一定要先 SELECT 确认影响范围特别是生产环境。第八每个新项目都必须建立数据库版本管理机制最好用 Flyway 或 Liquibase 管理脚本。第九SQL 日志要记录参数化后的完整语句方便排障但要注意敏感字段脱敏。第十调 SQL 永远不要“凭感觉”要用 EXPLAIN、执行计划、IO 统计来说话。这些心法看起来平平无奇但每一条背后都对应着某个具体的线上事故或者长时间的加班排查。数据库是业务的地基地基稳定了上面的应用才能跑得安稳。希望这篇汇总对你日常工作有帮助也欢迎在评论区把你遇到过的“离谱 SQL 问题”分享出来大家一起排查一起学习。
常用SQL技巧与高频问题排查实战汇总
我做了十几年数据相关工作每年都会整理一遍自己写过的 SQL 脚本和踩坑记录。看到“常用 SQL 技巧与问题汇总”这个题目我其实挺有感触的SQL 这玩意儿入门容易写好写快真难。热搜词里有一大堆人搜“SQL Server 安装失败”“Navicat 怎么导入数据”“sql 语句去重”“慢 sql 优化”说明大家在日常工作中翻来覆去遇到的基本就是这些事。这篇文章我就把自己实际工作中沉淀下来的那些 SQL 技巧、排查思路、还有各类工具使用时的坑一次性梳理清楚。内容会覆盖几个大块高频 SQL 写法与性能调优、窗口函数的实战应用、慢 SQL 排查套路、常用数据库工具的隐藏技巧以及安全相关的 SQL 注入防御等。不管你是刚入行的同学还是带团队的老人这篇文章都适合静下心来翻一翻很多细节是我查了无数资料、踩了无数坑才总结出来的希望能帮你在工作中少走弯路。1. 从基本功到实战那些高频 SQL 写法的正确姿势1.1 数据去重别只会用 DISTINCT数据去重是很多人每天都要面对的需求。热搜里“sql 语句去重”“清洗 sql 语句去重”反复出现但我发现好多人对去重的理解还停留在DISTINCT这个关键字上。其实 DISTINCT 有一个天然的使用限制它对 SELECT 出来的整行数据去重如果两行数据中有一个字段不同那这两行就都会保留。很多时候我们的真实需求是“根据某个业务主键去重保留其中最新的一条”这时候 DISTINCT 就完全不够用了。我这里给出一套在实际项目中用得最多的去重方案用ROW_NUMBER()窗口函数配合分区字段来实现按组去重这是目前最灵活、性能也可控的方式。使用GROUP BY加聚合函数适合做明细汇总级去重。小表场景可以直接用临时表加主键约束去重简单粗暴。以“订单表按用户 ID 去重取最近一笔订单”为例SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE t.rn 1;这个写法在几乎所有主流数据库MySQL 8.0、SQL Server、Oracle、PostgreSQL里都能跑通用性非常好。核心逻辑就是先按用户分组组内按时间倒序编号最后只保留每组里编号为 1 的那条记录。需要注意一个细节如果user_id有 NULL 值PARTITION BY会把所有 NULL 归为一组这可能导致结果里多出一条或丢失记录。我建议先去重前用COALESCE(user_id, unknown)处理一下空值。1.2 空值处理NULL 是个让你防不胜防的坑搜索词里“sql 去除空值”这个热度一直很高说明大家在空值处理上确实吃过不少亏。NULL 在 SQL 里不是 0也不是空字符串它代表“未知”。所以,NULL 参与任何运算结果都是 NULL。举几个常见的坑我估计每个人都踩过至少一个WHERE column NULL永远查不到数据必须用IS NULL。NULL 1的结果是 NULL不是 1。COUNT(column)会忽略 NULL但COUNT(*)不会。CASE WHEN column THEN 空 ELSE 非空 END对 NULL 不生效因为 NULL 的结果是未知不会进入 THEN 分支。实际处理空值的推荐写法是SELECT COALESCE(user_name, 未知用户) AS user_name, NULLIF(phone, ) AS phone FROM user_profile;COALESCE返回第一个非 NULL 的值NULLIF则是把指定的值转换成 NULL。这两个函数在数据清洗阶段几乎天天用到。我个人的习惯是在数仓 ETL 层统一用COALESCE把关键字段全部填上默认值避免下游分析脚本因为处理 NULL 而反复报错。1.3 CASE WHEN 与条件统计用一条 SQL 完成多维报表在写统计报表时很多新人喜欢把一个维度拆成好几条 SQL 分别执行再回 Java 或 Excel 里拼接。其实用CASE WHEN完全可以一次性把多维度的统计结果算出来。比如我要统计不同状态订单的数量和占比SELECT date(order_time) AS order_date, COUNT(*) AS total_order, SUM(CASE WHEN order_status completed THEN 1 ELSE 0 END) AS completed_order, SUM(CASE WHEN order_status cancelled THEN 1 ELSE 0 END) AS cancelled_order, ROUND(SUM(CASE WHEN order_status completed THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS completion_rate FROM orders WHERE order_time 2024-01-01 GROUP BY date(order_time) ORDER BY order_date;这里有个关键点SUM(CASE WHEN xxx THEN 1 ELSE 0 END)的效果等价于COUNT(CASE WHEN xxx THEN 1 END)但前者在可读性上更直观也方便后续扩展成复杂的多条件判断逻辑。另外要注意用* 100.0而不是* 100否则整数相除会把小数抹掉这是新手最常见的低级错误。1.4 常用 SQL 复习与面试核心考点很多人会搜“sql 面试题”和“sql 语句复习”说明在准备面试或日常基础巩固阶段大家需要一份知识框架。我总结了一下面试和真实工作中都逃不掉的核心考点三范式与反范式设计什么时候该拆表什么时候该冗余字段。多表关联的差异INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN 各自的数据特征。GROUP BY 与 HAVING 的执行顺序先 WHERE 过滤再 GROUP BY 分组然后 HAVING 对组过滤最后 ORDER BY 排序。子查询与 CTE 的选择现代 SQL 用 WITH 子句通常比子查询更清晰而且便于后续复用。索引失效场景函数包裹索引列、隐式类型转换、前导模糊匹配等。事务隔离级别与锁机制脏读、不可重复读、幻读分别在哪个隔离级别下被解决。这些内容看起来基础却是地基。地基不稳后面写出来的 SQL 就是定时炸弹。2. 窗口函数实战把复杂的“分组取前N条”变简单2.1 窗口函数到底解决了什么痛点在没有窗口函数的日子里如果你想实现“每个部门工资最高的员工”“每个用户最近一次登录记录”这类需求通常要写自连接、相关子查询或者嵌套去重代码又长又难懂。窗口函数直接在结果集上开一个“窗口”在窗口内进行排序、聚合、偏移计算再返回每一行对应的结果。目前主流数据库里MySQL 8.0 及以上、SQL Server 2005 及以上、Oracle 8i 及以上、PostgreSQL 全都支持窗口函数。如果你的生产环境版本比较老比如 MySQL 5.7那确实用不了这个功能需要另想办法或用子查询替代。2.2 四种最常用的窗口函数ROW_NUMBER()给窗口内的行按顺序编号 1、2、3不重复不跳号。RANK()排名相同会重复但会跳号。比如并列第二后直接是第四。DENSE_RANK()排名相同重复但不跳号。并列第二后还是第三。SUM() OVER()、AVG() OVER()这类聚合窗口函数可以在结果集每一行上同时显示“当前行的值”和“截至当前行或全组的累计值”。以“按产品分类展示销售额并计算累计占比”为例SELECT product_category, sales_amount, SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sales_date) AS cumulative_sales, ROUND(sales_amount * 100.0 / SUM(sales_amount) OVER (PARTITION BY product_category), 2) AS pct FROM product_sales;这段 SQL 实现的效果是每一行都能看到当前产品的销售额、该分类下按日期累加的销售额、以及该产品在分类内的销售占比。这在业务看板开发里非常常用能省掉一大把在应用层做循环计算的逻辑。2.3 窗口函数性能优化要点窗口函数虽然好用但性能上有个天然开销它通常需要对分区字段排序。比如ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC)在底层执行时基本等于对(user_id, create_time)做一次排序。数据量大时这个排序就是性能瓶颈。我的优化建议是三个“必须”必须建好联合索引排序字段和分区字段要一起纳入索引设计。必须避免在窗口函数中使用表达式比如ORDER BY DATE(create_time)会让索引失效。必须控制数据扫描范围先用 WHERE 条件把文件缩小到合理范围再开窗口。2.4 经典面试题分组 TopN 用代码说话“每组取前 N 条”是面试高频题也是最容易体现 SQL 功底的题。以“每个部门薪资最高的前三名员工”为例WITH ranked_emp AS ( SELECT emp_name, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees ) SELECT * FROM ranked_emp WHERE rn 3;这里我要强调一个容易忽略的细节如果用RANK()替代ROW_NUMBER()则当薪资并列时可能出现第 2 名后直接蹦到第 4 名的情况导致结果超过三条。而面试官往往会追问“那并列怎么处理”这时候才是真正区分求职者理解深度的关键时刻。我一般会根据业务语义选择如果允许并列就选RANK()或DENSE_RANK()如果必须只取三个人就用ROW_NUMBER()。3. 慢 SQL 优化从执行计划到索引设计的完整套路3.1 一条慢 SQL 的排查路径“慢 sql 优化”“并行 sql 优化”“oracle sql 性能优化”这些热搜词说明大家在真实环境里都被慢查询折磨过。我处理慢 SQL 的顺序几乎固定分享给各位第一步先逮住那条 SQL。MySQL 里开启慢查询日志SQL Server 里看 DMVOracle 用 AWR 报告无论哪个平台先定位 SQL 文本以及执行次数、平均耗时。第二步看执行计划。执行计划里最关键的信息是访问类型和扫描行数。如果看到全表扫描ALL或者扫描了上百万行后最终返回几十行基本就是索引问题或查询条件设计有问题。第三步分析表结构和数据分布。确认表中数据量、重复度、索引情况判断当前 SQL 写法能不能利用上索引。第四步改写 SQL 或调整索引。改写不是乱改是在不改变结果的前提下重写语义比如拆分复杂 SQL、去除无关子句、更换 JOIN 顺序。第五步验证和回归。用 EXPLAIN 查看执行计划是否变成预期访问方式同时对比优化前后的实际耗时。3.2 看执行计划MySQL 的 type 字段MySQL 执行计划里type字段从好到差大概是这样system表只有一行极端场景。const主键或唯一索引命中最多返回一条。eq_ref多表 JOIN 时被驱动表通过主键或唯一索引匹配。ref普通二级索引命中。range索引范围扫描如BETWEEN、、。index扫了整个索引树但比全表快一点。ALL全表扫描SQL 性能预警信号。大多数优化目标就是把ALL或index提升到ref或range。这也意味着需要为 WHERE 条件中的过滤字段建立合适的索引。3.3 索引设计最核心的优化手段很多人知道“查询慢要建索引”但具体怎么建建在哪些列上字段顺序是什么却一知半解。索引设计里面有三个重要原则等值查询字段放在索引最前面排序字段放在后面范围查询字段最后放。尽量使用覆盖索引让查询列全部包含在索引里避免回表。区分度低的字段不要单独建索引比如性别、状态位这类重复值高的字段单独建索引优化效果很差。我举个例子。现在有一张订单表 orders常用查询是“查某用户在某个时间段的订单”。那么索引设计可以这样ALTER TABLE orders ADD INDEX idx_user_time (user_id, order_time DESC);这里把user_id放前面是因为它是等值条件order_time放后面是排序加范围条件。如果反过来把order_time放前面user_id放后面查询WHERE user_id xxx时可能无法充分利用索引。3.4 常见索引失效场景速查表我整理了一份索引失效的场景清单基本都是工作里反复见过的问题场景示例原因左模糊匹配LIKE %keyword无法使用索引树定位起点索引列使用函数WHERE DATE(create_time) 2024-01-01函数导致索引序列被打乱隐式类型转换WHERE phone 138xxxxphone 是 varchar数据库会把字段转成数值索引失效OR 连接非索引列WHERE id 1 OR status 0优化器可能放弃索引合并范围条件后的等值条件WHERE time 2024 AND user_id x范围查询后索引无法继续精确定位使用不等于WHERE status ! finished不等于基本走不了索引排序方向不一致ORDER BY col ASC但索引是 DESC可能额外排序技巧排查这类问题时我习惯在 EXPLAIN 后加一个SHOW WARNINGSMySQL 会展示优化器改写过后的 SQL能帮你看出它到底怎么理解你写的条件的。3.5 分页查询优化的实战写法分页深翻页是个经典痛点。LIMIT 1000000, 20这种写法MySQL 会扫描 1000020 行然后丢弃前 1000000 行代价非常大。我常用的优化方案有两个。方案一是基于游标的延迟关联SELECT * FROM orders JOIN ( SELECT id FROM orders WHERE create_time 2024-01-01 ORDER BY id LIMIT 1000000, 20 ) tmp ON orders.id tmp.id;内层子查询只查主键然后外层回表取完整数据性能提升常常是数量级的。方案二是基于排序字段的 WHERE 条件翻页SELECT * FROM orders WHERE create_time 2024-01-01 AND id {last_seen_id} ORDER BY id LIMIT 20;这种方案适合按 id 顺序翻页的场景不需要记住偏移量只需传上一页最后一个 id性能非常稳定。4. 实用工具与场景方案Navicat、MyBatis-Plus 与代码生成4.1 Navicat 导入 SQL 数据的正确姿势“navicate如何导入sql数据”这个搜索词上榜我觉得很多人的问题不是“不会导入”而是“导入时老出错”。Navicat 导入 SQL 文件其实有三种常见途径各有适用场景直接打开 SQL 文件并执行适合小文件、单次执行。右键连接 - 运行 SQL 文件选择.sql文件即可。优点是操作简单缺点是出错了不好定位特别是一个文件里包含建表和 INSERT 语句时如果中间某条语句报错后面可能就停了。数据传输工具适合把一个库的表结构和数据整体搬到另一个库。在连接上右键 - 数据传输可以自定义选择表也可以选择仅结构或仅数据。命令行导入适合超大文件。用mysql -u root -p database_name file.sql这种方式速度比图形界面快很多还能避免工具超时问题。我心里的一个坑是导入前一定要检查 SQL 文件里的字符集是不是跟目标库一致。如果文件是 UTF-8 而目标表是 latin1导完后中文就是乱码。最好统一在连接属性里设置编码为 UTF-8。4.2 MyBatis-Plus 根据实体类生成建表 SQL“mybatisplus根据java实体类生成创建表的sql语句”这个搜索词很有意思说明现在很多人不写原生建表语句而是想从 Java 实体类直接生成表结构。MyBatis-Plus 提供了多种方式来实现这个需求。一种方式是利用 MyBatis-Plus Generator 的能力配合数据库逆向生成从表结构生成实体类但这是反过来的方向。真正想从实体类生成建表 SQL通常需要借助数据库工具内置的表结构生成器或第三方插件例如用 IDEA 的 JPA Buddy、Database Tool 同步实体和表结构或者在 MyBatis-Plus 里使用扩展特性。我的建议是小项目可以直接用手写 SQL 建表因为可控中大型项目用 Flyway 或 Liquibase 这套数据库版本管理工具来管理表结构变更实体类只作为 ORM 映射不让它反向驱动表结构。原因很简单实体类里的字段注解不总是能表达出索引、约束、外键等数据库细节过度依赖实体类生成表结构容易丢掉性能设计和数据安全设计。4.3 SQL Server 安装问题与常见故障排查“SQL Server 安装失败”“SQL Server 2019 安装教程”“SQL Server 2022 关闭密码策略”这类热搜词霸屏。SQL Server 的安装和后续故障排查确实是很多运维同学头疼的事。我在这里集中列出几个高频问题和高概率解决方案安装时提示“此计算机上安装了 Microsoft Visual Studio 2015 的早期版本”多见于 SQL Server 2016/2017 安装失败。解决方案是检查系统已安装的程序卸载不相容的 Visual C 相关组件然后重新运行安装程序。SQL Server 服务无法启动常见原因是服务账户权限不对或者端口被占用。建议在服务管理器里确认 SQL Server 服务使用的账号是否具备目录读写权限同时用netstat -ano | findstr 1433检查端口是否冲突。出现“已成功与服务器建立连接但是在登录过程中发生错误”的报错很多人以为密码错了其实多数时候是协议或证书问题。优先检查客户端协议是否启用了 TCP/IP并按提示查看 SQL Server 错误日志。经验SQL Server 安装前先把 Windows 更新和所有杀毒软件暂停再把安装包完整解压到纯英文路径下能避免 80% 的诡异报错。我见过很多次“SQL 安装失败”其实是因为安装包放在带中文或空格的目录里导致路径解析失败。4.4 SQL Server 数据库文件附加与日志问题处理搜索词里提到“sql server writelog”和“SQL Server 2000 Desktop Engine 命令行”看起来是有朋友在处理数据库附加、日志文件异常的场景。这里我要特别说一下MDF/LDF文件附加时可能会遇到的问题。数据库文件分离后重新附加如果没有 LDF 日志文件SQL Server 通常会拒绝附加。这时可以用命令行方式重建日志文件比如CREATE DATABASE [DemoDB] ON (FILENAME ND:\Data\DemoDB.mdf) FOR ATTACH_REBUILD_LOG;FOR ATTACH_REBUILD_LOG会在附加时重建缺失的日志文件这个命令能解决很多“日志文件丢失/损坏导致附加失败”的问题。但需要注意如果原库本身处于异常关闭状态执行这条指令前最好先备份一份 MDF 文件的副本防止二次损坏。4.5 命令行执行 SQL 脚本的通用方法“mysql执行sql脚本”“sql server 2000 desktop engine 命令行”让我想到很多人在没有图形界面工具的情况下还是要执行 SQL 脚本。我列出几个平台上最常用的命令行执行方式平台命令示例说明MySQLmysql -h localhost -u root -p dbname script.sql需要把脚本文件路径指正确PostgreSQLpsql -h localhost -U postgres -d dbname -f script.sql-f参数执行脚本文件SQL Serversqlcmd -S localhost -U sa -P password -d dbname -i script.sql使用 sqlcmd 工具注意-i输入文件Oraclesqlplus user/passorcl script.sqlsqlplus 自动读入脚本并执行需要提醒的是执行大文件脚本时尽量先在事务内包裹或者在脚本头部加上错误处理逻辑。比如 MySQL 客户端可以加--force参数让错误后继续执行但这样容易造成数据半成品状态。更稳妥的做法是先备份再分段执行并在关键步骤用日志记录执行位置。5. SQL 安全问题SQL 注入的原理、危害与防护5.1 SQL 注入是怎么发生的“sql注入万能密码绕过”“python sql注入原理”“bwapp”“[swpuctf 2021 新生赛]sql”这些关键词说明很多人都在学习 SQL 注入的攻防原理。我在这里明确一点SQL 注入的本质是把用户输入的数据片段拼接进了 SQL 语句里导致数据库把用户输入当成代码执行。一个经典漏洞示例String sql SELECT * FROM users WHERE username username AND password password ;如果用户输入的用户名是admin --拼出来的 SQL 就变成SELECT * FROM users WHERE username admin -- AND password xxx双横线把后面的条件注释掉了这时只要知道 admin 用户名不需要密码就能登录。这就是网上常说的“万能密码”的基础原理。5.2 防护 SQL 注入的正确姿势防护 SQL 注入的原则其实很简单永远不要信任用户的任何输入所有 SQL 参数必须经过参数化处理。参数化的含义是用户在请求里输入的值只作为数据传入 SQL而不是作为 SQL 代码的一部分。以 Java 的 JDBC 为例正确写法是使用PreparedStatementString sql SELECT * FROM users WHERE username ? AND password ?; PreparedStatement ps connection.prepareStatement(sql); ps.setString(1, username); ps.setString(2, password); ResultSet rs ps.executeQuery();这样即使输入admin --数据库也只会把它当成字符串字面量绝不会触发注释逻辑。同理MyBatis 框架里要使用#{}而不是${}来传递参数#{}会生成占位符预编译语句${}则是直接字符串拼接存在注入风险。5.3 数据库权限最小化原则除了参数化查询数据库权限设计也是防注入的第二道防线。很多公司的应用账号权限给得过大一句注入就能拖库甚至删库。我强烈建议应用连接数据库的账号只授予 SELECT、INSERT、UPDATE、DELETE 权限绝不授予 DDL 权限。分离管理账号和应用账号禁止应用账号有FILE、SUPER这类敏感权限。核心敏感表用户表、支付表做单独的权限控制和脱敏策略不要把所有数据都放在一个通用账号下。提示即使是内部后台系统也要做参数化和权限控制。我见过不止一次内部系统因为图方便直接拼接 SQL被同事测试时一句OR 11把整个表 dump 出来。内部威胁并不比外部攻击少。5.4 MyBatis-Plus 与原生 SQL 中的安全实践MyBatis-Plus 确实能简化开发但它的QueryWrapper、LambdaQueryWrapper也不是银弹。当你在 Wrapper 里使用apply方法拼接自定义 SQL 片段时仍然要小心用户输入。apply的内容是直接进入 SQL 的如果里面包含未转义的用户输入一样会出问题。MyBatis 注解里写原生 SQL 时同样要用#{}参数Select(SELECT * FROM users WHERE username #{username}) ListUser findByUsername(Param(username) String username);我处理过多个因为直接在注解里用${}拼接排序字段、where 条件而导致的线上注入漏洞。特别是排序字段很多人觉得“排序字段总不可能注入”但实际上下发一个子查询或者报错信息就能泄露数据不要低估任何输入入口。6. Hive SQL 与并行优化大数据场景下的特殊问题6.1 Hive SQL 与关系型数据库 SQL 的差异“hive sql”在热搜里出现说明数据开发岗的朋友也在搜索 Hive 相关的技巧。Hive SQL 语法上接近 MySQL但底层是 MapReduce/Tez/Spark 引擎所以很多 SQL 写法和优化思路完全不同。第一个重大差异是 Hive 支持多表INSERT可以一次扫描源表写多个目标表减少源表读取次数FROM user_logs INSERT OVERWRITE TABLE dws_user_logs_part PARTITION (dt2024-01-01) SELECT user_id, action, count(*) GROUP BY user_id, action INSERT OVERWRITE TABLE dws_user_daily_stats PARTITION (dt2024-01-01) SELECT user_id, count(*) GROUP BY user_id;第二个差异是 Hive 中ORDER BY是全局排序会产生一个 reducer 处理所有数据代价极大。通常在数据量很大时不要使用ORDER BY而是改用SORT BY每个 reducer 内排序或DISTRIBUTE BYSORT BY配合。第三个差异是 Hive 的谓词下推。写成子查询时要注意如果子查询没有WHERE过滤它可能会加载全表数据再过滤。标准做法是尽早使用分区裁剪把 WHERE 条件下沉到最里层。6.2 并行 SQL 优化以 Oracle 为例搜索词里“并行sql优化”指的通常是 Oracle 的并行执行特性。Oracle 的并行查询是把一个 SQL 的执行拆分成多个并行子任务可以极大缩短大查询的响应时间但也容易引发资源争抢和系统波动。我见到过的最常见的并行优化误用场景是开发人员为了让一条查询变快随手加了/* PARALLEL(8) */提示结果把服务器 CPU 打满影响了其他所有业务。并行并不是越快越好它只适合大表全量扫描。大表和大表之间的 Hash Join。数据仓库类型的大型分析查询。对于 OLTP 类型的短查询并行反而是负优化。我建议如果你要用并行至少要通过v$session_longops或 Oracle 性能报告来确认 SQL 执行阶段到底消耗在哪再做针对性调整。6.3 Hive 数据倾斜处理经验数据倾斜是 Hive SQL 开发里最常遇到的问题。最常见表现是某个 key 的数据量极多导致一个 reduce 任务卡住很久其他 reduce 早都跑完了。解决方案一般有三种两阶段聚合先用group by将数据打散到足够多的唯一键再对结果二次聚合。添加随机前缀把倾斜的 key比如一个空值 user_id 占了 99%加上随机后缀让它分散到不同 reduce。对小表做 MapJoin 广播避免在大表关联时把大量 key 集中在同一个 reduce。具体操作例子比如处理空值导致的倾斜SELECT COALESCE(user_id, unknown) AS user_key, COUNT(*) FROM logs WHERE dt 2024-01-01 GROUP BY COALESCE(user_id, unknown);如果在 GROUP BY 之前不处理 NULL多个空值都会落到同一个 NULL 组形成严重倾斜。先转成常态字符串至少能让倾斜 redistribute 到正常逻辑里进一步再用加盐方案解决。7. 高频系统问题速查与解决实录7.1 SQL Server 登录失败问题排查方法热搜词里有“sql server 已成功与服务器建立连接,但是在登录前”这个问题我反复遇到过通常出现在以下几种情况场景报错特征解决方案密码错误或密码策略限制报告“用户登录失败”确认密码复杂度或由管理员重置密码混合认证模式未开启无法用 SQL 账号登录在服务端设置为 Windows 和 SQL Server 混合模式认证TCP/IP 未启用客户端连接超时或报协议错误打开 SQL Server 配置管理器启用 TCP/IPTLS 版本不兼容登录过程中握手失败更新客户端驱动或启用相应 TLS 协议默认数据库不存在或被删除建立连接后登录报错用管理员账号将用户默认数据库改为 master 或其他存在库我处理过的最离奇的一个案例是某个服务的登录账号默认数据库被运维清理掉了导致每次登录都报错。从表面看像是账号权限问题查了很多日志才发现是默认数据库缺失。这个问题在 SQL Server 里可以通过将上下文切到 master 后重建默认库权限解决。7.2 MySQL 执行 SQL 脚本时编码和超时问题使用mysql命令导入大 SQL 文件时我遇到过几个特无语的问题脚本文件有前导 BOM 字符导致第一行表名解析失败。文件包含DELIMITER $$存储过程语法直接在 Navicat 里运行时报错。max_allowed_packet配置太小导入大字段时提示“Packet too large”。解决方法分别是导入前用编辑器将文件转成 UTF-8 无 BOM 格式存储过程脚本用mysql命令行而非图形界面执行在 MySQL 配置里调大max_allowed_packet。一个小技巧是导入前先SET FOREIGN_KEY_CHECKS0;可以在多表关联插入时避免外键校验带来的性能损失导入完再恢复。7.3 Navicat 导入导出数据时的常见陷阱用 Navicat 导入导出的场景实在太多我整理几个容易踩的点导出时没有勾选“包含建表语句”导致在目标库执行时提示表不存在。导入时如果目标表已存在且字段顺序不一致数据会错位。建议先删表或选择“删除表数据再插入”。CSV 导入时日期格式不规范比如2024-01-01 12:00:00在某些区域设置下会变成2024-01-01 12:00导致时间精度丢失。大批量数据导入时没有提交分批导致事务过大内存暴涨甚至锁表。我在做 MySQL 到 SQL Server 的数据迁移时一般不会直接用 Navicat 的数据传输而是把数据导成 CSV 再通过各平台的原生导入工具处理虽然多了一步但稳定性提高了很多。7.4 SQL Server Management Studio 的实用配置搜索词里多次出现“sql server management studio 下载”“SQL Server Management 2012 下载及安装”说明很多人还在找版本适配的客户端工具。SSMS 是官方免费的图形化管理工具目前推荐直接安装最新版它能向前兼容管理大多数 SQL Server 版本。日常使用 SSMS 有几个非常提效的设置在“工具 - 选项 - 查询执行 - SQL Server - 高级”里勾选“SET SHOWPLAN_ALL”可以默认显示执行计划。使用“结果到网格”和“结果到文件”切换快捷键 CtrlD / CtrlShiftF。在查询编辑器里输入SET STATISTICS IO ON;和SET STATISTICS TIME ON;能看到每个表的逻辑读数和 CPU 耗时这对性能分析极有帮助。使用活动监视器定位当前阻塞和长时间运行会话。8. 我的老生常谈SQL 开发的十条心法写到最后分享十条我从实际工作中总结出来的 SQL 开发心法也算是对这篇文章的一个沉淀第一写 SQL 前先想清楚数据模型和索引结构写完再调优的成本远高于提前设计。第二永远不要让 SELECT * 出现在生产代码里需要什么列就查什么列。第三WHERE 条件里的字段尽量不做函数包裹和类型转换保持索引的可用性。第四大查询一定要设置合理的超时时间避免长事务拖垮数据库。第五表连接时小表驱动大表尽量用 JOIN 而不是嵌套子查询。第六正确理解 COUNT(*) 和 COUNT(1) 没有性能差异但 COUNT(列名) 会忽略 NULL语义要搞清楚。第七做数据更新和删除前一定要先 SELECT 确认影响范围特别是生产环境。第八每个新项目都必须建立数据库版本管理机制最好用 Flyway 或 Liquibase 管理脚本。第九SQL 日志要记录参数化后的完整语句方便排障但要注意敏感字段脱敏。第十调 SQL 永远不要“凭感觉”要用 EXPLAIN、执行计划、IO 统计来说话。这些心法看起来平平无奇但每一条背后都对应着某个具体的线上事故或者长时间的加班排查。数据库是业务的地基地基稳定了上面的应用才能跑得安稳。希望这篇汇总对你日常工作有帮助也欢迎在评论区把你遇到过的“离谱 SQL 问题”分享出来大家一起排查一起学习。