从建表去重到慢SQL优化：一份实战SQL笔记

说实话，作为一个数据库打交道多年的从业者，我手机和电脑里存了一堆乱七八糟的SQL片段，有的是调试时临时贴的，有的是从别人博客抄来的，还有的是自己踩坑之后赶紧记下来的。最近趁着项目间隙整理了一遍，发现这些碎片化的SQL笔记居然能串成一条完整的线——从最基础的建表和去重，到窗口函数、慢SQL优化，再到SQL注入防御和SQL Server运维，覆盖面还挺广。

这篇就把它们重新梳理成一份成体系的SQL笔记。无论你是刚接触数据库的新人，还是被慢查询、连接故障折磨过的老手，应该都能从中找到点有用的东西。我会按实际使用频率来组织，每一段都尽量说清楚“为什么这么做”，而不是只丢给你一条能跑的语句。

1. SQL基础：建表、去重与空值的几个高频套路

1.1 建表与默认值：GUID没那么简单

建表这件事看起来简单，但越基础的越容易埋坑。比如主键用自增ID还是GUID，我见过太多项目在这个选择上反复折腾。

自增ID的好处是短、有序、索引友好，但在分布式场景或者需要合并多库数据的场景下容易冲突。GUID（在SQL Server里是uniqueidentifier）能全局唯一，可它有个让人头疼的问题：默认值怎么写。很多人以为默认值GUID就是简单地写个DEFAULT '00000000-0000-0000-0000-000000000000'，结果每条记录都是同一个值。

正确的写法是使用数据库内置函数生成新值。SQL Server里用NEWID()，MySQL里是UUID()，PostgreSQL里是gen_random_uuid()：

-- SQL Server CREATE TABLE orders ( order_id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY, order_no VARCHAR(32) NOT NULL, created_at DATETIME DEFAULT GETDATE() ); -- MySQL 8.0+ -- 注意：MySQL的默认值不允许直接用表达式，通常靠应用层生成，或使用触发器

如果你要的是有序GUID，可以在SQL Server中用NEWSEQUENTIALID()，它比NEWID()对聚集索引更友好，能减少页分裂。这一点在插入频繁的表上体验特别明显，我是吃过亏的——早期用NEWID()做主键，插入速度越来越慢，后来检查索引碎片才发现是页分裂太多导致的。

1.2 去重三种姿势：distinct、group by、row_number

“SQL去重”应该是搜索量最大的需求之一。很多人一提去重就是SELECT DISTINCT，但它有两个天然短板：一是它会对所有查出来的列去重，没法只针对某几列去重；二是去重后的数据不能方便地挑选“每组的某一条”。

实际工作中我更常用下面几种方案：

• GROUP BY加聚合函数：适合需要去重后还要统计的场景
• ROW_NUMBER() OVER(PARTITION BY ...)：适合“每个分组保留指定顺序的第一条记录”
• 临时表配合DELETE自连接：适合清理表中已有的重复数据

举个典型例子，订单表里因为上游重复推送出现了多行相同order_no，要保留最早一条：

WITH ranked AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY order_no ORDER BY created_at ASC) AS rn FROM orders ) DELETE FROM orders WHERE (order_no, created_at) IN ( SELECT order_no, created_at FROM ranked WHERE rn > 1 );

我之前在一个数据清洗任务里用过这个写法，第一次跑的时候没加ORDER BY，结果保留哪条完全随机，后来统一按created_at ASC先排序再删，才保证结果是“保留最早一条”。这类细节不踩一次坑很难记住。

1.3 空值与类型判断：别再让NULL捣乱

NULL是SQL里最反直觉的东西。它既不等于空字符串，也不等于0，NULL = NULL的结果都不是TRUE而是UNKNOWN。这就导致很多新手在WHERE条件里用col = NULL查不到任何数据。

判断空值的标准写法就两个：

WHERE col IS NULL; WHERE col IS NOT NULL;

查询结果里想把NULL显示成别的值，SQL Server和MySQL各有一套：

• MySQL：IFNULL(col, '默认值')
• SQL Server：ISNULL(col, '默认值')或COALESCE(col, '默认值')
• 通用做法：COALESCE(col1, col2, '兜底值')

COALESCE在多字段取首个非空值场景下很好用，比如一个表里有手机号、微信号、邮箱三个字段，业务上想取任意一个非空联系方式展示，直接用COALESCE(phone, wechat, email)就行，不用写一长串CASE WHEN。

还有一个冷门需求出现在DB2里：判断一个字符串是否是数字。DB2里可以用TRANSLATE把非数字字符替换掉再比较，或者用正则表达式函数。MySQL里则是：

SELECT col, col REGEXP '^[0-9]+$' AS is_numeric FROM temp_table;

这个判断在数据处理阶段特别实用，能提前筛掉脏数据，避免后续做类型转换时直接报错。

2. 窗口函数实战：TopN、排名与同环比

2.1 窗口函数到底解决了什么问题

在没接触窗口函数之前，实现“每个用户最近一单”这类需求，我一般先GROUP BY拿到最大时间，再回表关联，SQL写得又长又绕。窗口函数出现之后，这类问题的写法变得非常直观。

窗口函数的核心是：它能在不合并行的情况下，对每一行计算一个基于“窗口内数据”的结果。你可以把它理解为“在结果集上再加一列计算值，而不是改变结果集的行数”。

最常见的四类窗口函数：

• 排序类：ROW_NUMBER()、RANK()、DENSE_RANK()
• 聚合类：SUM() OVER()、AVG() OVER()、COUNT() OVER()
• 偏移类：LAG(col, n)取前第n行、LEAD(col, n)取后第n行
• 分桶类：NTILE(n)平均分成n组

2.2 分组TopN与排名的写法

分组取TopN是窗口函数最经典的场景。比如查每个部门工资最高的前三名：

SELECT * FROM ( SELECT *, RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rk FROM employees ) t WHERE rk <= 3;

RANK和DENSE_RANK的区别经常有人搞混：RANK遇到相同值会跳过名次，比如两个人并列第1，下一个人就是第3；DENSE_RANK则不会跳过，下一个人还是第2。需要“连续排名”的报表一般用DENSE_RANK。

聚合类的窗口函数在计算“累计”时很有用。比如求每个用户截至当前天的累计消费：

SELECT user_id, order_date, amount, SUM(amount) OVER(PARTITION BY user_id ORDER BY order_date) AS cumulative_amount FROM orders;

这里ORDER BY在窗口内不仅控制排序，还决定了累加的边界。不加ORDER BY时，窗口是整个分组，算出来的是分组总和；加了ORDER BY后，窗口变成从分组第一行到当前行，这就是“累计”的逻辑。

2.3 窗口函数常见的WRONG用法

我见过不少人在窗口函数的边界条件上翻车，挑几个最容易错的：

• 忘记PARTITION BY：没分组就排序，会把全表当成一个窗口，算出完全不符合业务预期的结果。
• WHERE和窗口函数混用：窗口函数在WHERE之后执行，所以不能在WHERE里直接引用窗口函数的别名，必须包一层子查询。
• ORDER BY对聚合窗口的影响：前面说过，有ORDER BY和没ORDER BY结果完全不同，写之前想清楚你到底是想要“累计值”还是“分组总值”。

另外要留意数据库版本。MySQL 8.0才支持窗口函数，5.7及以下没有这个能力；SQL Server从2012开始有大部分窗口函数，但IGNORE NULLS这类选项要到2016+才好用。如果你还在维护老库，遇到这类需求就只能用变量自连接这类土办法替代。

3. 慢SQL优化：定位、改写与并行

3.1 先回答三个问题：真的慢吗、卡在哪、怎么改

慢SQL优化最容易犯的错是一上来就加索引。索引不是万能的，甚至可能让问题更隐蔽。我自己的排查顺序是：先确认是不是真的慢，再定位瓶颈在哪儿，最后才动手改。

第一步，开启慢查询日志。MySQL里是slow_query_log参数，设置long_query_time = 1表示超过1秒的SQL都会记录下来；SQL Server可以用sys.dm_exec_query_stats配合DMV查询耗时靠前的语句。

拿到慢SQL之后，用执行计划看它卡在哪。MySQL里是EXPLAIN，SQL Server是“显示估计的执行计划”，主要看几个东西：

• type列：从ALL（全表扫描）到eq_ref、const，级别越高性能越好
• rows列：预估扫描行数
• Extra列：出现Using filesort或Using temporary往往是排序和临时表导致的慢

3.2 慢SQL典型案例拆解

整理几个我实际遇到过的慢SQL套路：

深分页问题。LIMIT 1000000, 20这种写法，数据库会先扫出前1000020行再丢掉前1000000行。数据量一大，哪怕有索引也扛不住。改写方案是用“上一页最后一条记录的ID”做条件：

-- 优化前 SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; -- 优化后 SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

隐式类型转换。字符串类型的字段用了数字条件，索引直接失效。比如phone是VARCHAR，却写WHERE phone = 13800138000，MySQL会尝试把字段转成数字，导致无法走索引。正确的做法是写成WHERE phone = '13800138000'。

索引列上做运算。WHERE DATE(created_at) = '2024-01-01'看着没毛病，但函数包裹了索引列，索引直接失效。改成范围查询就好：

WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'

3.3 并行优化与参数调优

单条SQL已经没法再优化的情况下，并行是最后的利器。SQL Server默认会为查询选择合适的并行度，但如果服务器的MAXDOP设置不合理，反而可能拖慢整体性能。如果一个OLTP系统频繁出现CXPACKET等待，可以适度调低并行度：

-- 在实例级别查看当前设置的MAXDOP SELECT * FROM sys.configurations WHERE name = 'max degree of parallelism';

更常见的是批处理任务并行。比如要更新一张千万级大表的某些字段，一条UPDATE跑几个小时很正常，拆成按主键范围分批并行执行反而更快。我之前用WHILE循环配合TOP (10000)分批更新，整体耗时有明显下降，同时对在线业务的冲击也小很多。

MySQL这边要注意innodb_buffer_pool_size和sort_buffer_size这类参数。数据量大时，buffer pool过小会导致频繁读磁盘；sort buffer过小则会把排序落到临时表。这些参数不是越大越好，要根据物理内存和业务特性来定，改完需要观察一段时间。

4. SQL注入：原理、复现与防御

4.1 注入是怎么发生的

SQL注入的本质是：把用户输入当成了SQL代码的一部分去执行。最常见的原因是拼接字符串，比如早期很多教程里的写法：

SELECT * FROM users WHERE username = 'admin' AND password = '123456'

如果用户输入的用户名是admin' --，整个语句变成：

SELECT * FROM users WHERE username = 'admin' -- ' AND password = '123456'

--把后面的条件全部注释掉，密码校验形同虚设。这就是“万能密码”的基本原理，只不过实际利用时会有更多变体。

我在测试自己项目的登录功能时，就遇到过这类问题。当时用安全扫描工具随便扫了一下，直接在登录接口报出了注入风险。原因就是早期图省事写了几条拼接SQL，后来全部改成参数化查询才解决。这个教训让我意识到：注入风险不只是攻击者的事，作为开发方必须主动自查。

4.2 常见的注入场景和安全自检

热词里提到“fofa查询sql注入”“dvwa sql注入”“sql注入靶场”“ctfshow sql注入生成文件”，这些都是安全研究和安全测试领域的常见场景。DVWA是一个知名的漏洞练习平台，里面专门有SQL Injection模块，可以练习如何发现和利用注入点；CTF比赛里也经常出现利用注入写文件的题目。这些靶场平台的存在，本身就是为了让安全人员和开发人员能在受控环境中理解注入原理。

从开发者的角度，我的建议是把这些靶场当作“反面教材”来学习。理解了攻击者的思路，才知道为什么参数化查询和输入校验缺一不可。

4.3 防御实践与自检清单

防御SQL注入没有什么黑科技，核心就几条，但必须形成习惯：

• 所有SQL都要用参数化查询或预编译语句，禁止拼接用户输入。这是最有效的一招。Python的pymysql用%s占位符，Java的PreparedStatement，Go的database/sql，都能自动处理参数转义。
• ORM框架通常能降低注入风险，但原生SQL依然要小心。比如Prisma里调用原生SQL时，也要用$queryRaw加参数占位符的方式，而不是直接拼字符串。
• 输入校验按白名单思路来做：该是数字的强制转int，该是日期的强制解析格式，该是枚举值的就严格匹配枚举。
• 数据库账号遵循最小权限原则。业务账号不该有DROP、FILE、超级管理权限——就算被注入了，攻击者也做不了太多破坏。

我给自己定了个自检清单：每个涉及用户输入的SQL，上线前必须确认是不是参数化写法；每次代码审查只要看到字符串拼接SQL，直接打回。这是最笨但最有效的规矩。

5. SQL Server运维实录：安装、密码与连接故障

5.1 版本选择与安装踩坑

SQL Server常见版本有Express、Standard和Enterprise。很多人一开始就纠结用哪个。Express版免费、有10GB数据库大小限制，适合学习和轻量应用；开发/测试场景完全够用。正式商业项目需要高可用、内存大等高级特性时，再考虑Standard或Enterprise。

安装上有几个容易被忽略的点：

• 用SQL Server 2019/2022镜像安装时，实例配置界面要记住实例名。默认实例是MSSQLSERVER，命名实例则是计算机名\实例名，连接字符串写错一个斜杠就会连不上。
• SQL Server安装后的默认端口是1433，如果是云服务器，记得在安全组里放行端口，别只顾着改防火墙。
• SSMS（SQL Server Management Studio）是官方免费的管理工具，微软官网直接下载，进入页面后选对应的中文版或英文版即可。不要从第三方下载站走，那些打包的可能带私货。

5.2 密码到期与卸载清理

SQL Server 2012及以后的版本默认开启了密码过期策略，经常有DBA遇到“密码已过期，必须更改”连不进去。处理办法有两个：一是用系统管理员账号登录后在用户属性里勾选“不强制实施密码过期”；二是直接改密码。如果是Windows身份验证登录，也可以用ALTER LOGIN来关掉过期策略：

ALTER LOGIN sa WITH CHECK_POLICY = OFF;

注意关掉策略会影响安全性，更好的做法是把密码换成强密码，而不是关闭策略。我还见过有人把密码过期问题误认为是服务坏了，其实是服务正常，只是登录工具里缓存了旧密码。

卸载SQL Server是另一个大坑。只通过控制面板卸载，经常会留下服务、注册表和磁盘目录残留，导致重装时冲突。规范流程是先停掉服务，再用控制面板卸载所有SQL Server相关的程序，最后手动删除数据目录和注册表残留。我清理的时候会检查这几处：服务列表、C:\Program Files\Microsoft SQL Server目录、HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server注册表项。

5.3 SolidWorks等软件连不上SQL Server的排查

热词里有个具体场景：SolidWorks Electrical提示“无法连接到SQL Server”，并列出“用户名或密码、服务未启动”等可能原因。这种第三方软件连接数据库失败的排查套路是通用的。

我的排查顺序：

1. 先确认SQL Server服务是否在运行。打开“服务”管理器，找SQL Server (MSSQLSERVER)实例，确保状态是“正在运行”。服务没起来，后面所有排查都是白费。
2. 确认TCP/IP协议是否启用。用“SQL Server配置管理器”打开“SQL Server网络配置”，把TCP/IP启用。很多软件默认走TCP连接，协议禁用必然失败。
3. 验证账号密码。第三方软件内置了数据库账号，通常是sa或专用账号，确认密码没到期、没被锁定。
4. 用SSMS在本机测试同一账号能否登录。如果SSMS能连而软件连不上，问题大概率出在端口、协议或客户端版本兼容上。

其中“用户名或密码”几乎占了这类故障的一半以上。SolidWorks Electrical这个场景我也看到过不少次，多半是安装时输入的sa密码不对，或者SQL实例名写错，换到正确实例名就好。

6. 与代码打交道：ORM原生SQL、脚本执行与AI辅助

6.1 Prisma里调用原生SQL的正确姿势

Prisma是Node.js生态里很火的ORM，平时用prisma.user.findMany()这类API就行。但在复杂报表、窗口函数、批量更新等场景下，ORM的API表达能力不够，原生SQL就派上用场了。Prisma提供$queryRaw和$executeRaw两个方法：

const results = await prisma.$queryRaw` SELECT id, name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees `;

注意这里用的是模板字符串语法，参数直接通过${value}传进去，Prisma会把它们当作绑定参数处理，而不是拼进SQL。千万别手贱去写字符串拼接，一次拼接就等于把注入漏洞亲手打开。$queryRaw适合查询，$executeRaw适合更新和删除，两者的返回值和使用限制有区别，官方文档里写得很清楚。

6.2 Python执行SQL容易忽略的坑

Python连接数据库常见的库有pymysql、psycopg2、pyodbc等。踩坑点主要体现在两处：

一是超时问题。默认情况下，很多库的查询超时设置很长甚至没有设置，遇到慢查询时，Python这边可能早就因为请求堆积而表现异常。我习惯在连接参数里显式设置超时，比如PyMySQL的read_timeout和write_timeout，以及连接层的connect_timeout：

import pymysql conn = pymysql.connect( host="10.0.0.10", user="app_user", password="******", database="app_db", read_timeout=10, connect_timeout=5 )

二是SQLite等轻量库的异常信息容易误导。比如热词里提到的SqliteException(1): while preparing statement, no such column: test_url，这个错误不是“没有test_url列”这么简单。它可能出现在你刚ALTER TABLE加列却没提交事务，或者查询的列名和实际表结构对不上。排查时先查表结构，再确认连接的是不是同一个数据库文件——我就经历过连了旧的库文件、查了半天才发现路径不对。

6.3 AI辅助SQL的正确打开方式

现在用AI辅助写SQL已经很普遍了。热词里也提到“dbx怎么使用ai辅助sql”，这类工具能帮我们快速生成基础语句、解释执行计划、转换不同数据库的方言。

我的用法是：用AI生成初稿，但必须人工理解、验证、改写成符合规范的版本。窗口函数、复杂关联这类场景，AI给出的答案经常有边界条件遗漏，直接用容易埋雷。一个小技巧是让AI给SQL加注释，说清楚每一步的逻辑，然后你逐行阅读，相当于让AI当你的结对编程伙伴。

另外，AI对数据库方言的差异理解并不总是准确。SQL Server的TOP、MySQL的LIMIT、Oracle的FETCH FIRST在语法上完全不同，生成之后最好在目标数据库上跑一遍执行计划再上线。工具是效率放大器，但用工具的这个人懂不懂SQL，决定了产物质量的上限。

结尾说点题外话

整理这份笔记的时候我最大的感受是：SQL这个技能貌似简单，但真正用得顺手需要大量实战积累。从去重用什么写法、窗口函数的边界条件，到慢SQL怎么定位、连接故障怎么排查，每一块都是我踩过坑才记住的。如果你现在还在SQL入门阶段，别急着背语法，先把手头业务的问题写成SQL跑一遍，遇到报错就去查执行计划和官方文档，这样学到的才是真正能用的东西。后面如果时间允许，我打算把这份笔记再扩展一下，补充更多关于数据库迁移和报表优化的实战案例。