企业官网建设流程全解析

刚把前面几篇关于SQL基础、多表关联、聚合查询的文章写完，评论区一直有人催更，问得最多的一句话是：“基础都会了，怎么才算进阶？”说实话，这是一个特别难回答的问题，因为“进阶”不是一个知识点，而是一套思维方式。本篇是这个“从入门到精通”系列的第6讲，我们不用再去背语法，而是要开始解决真实世界里那些让人头大的问题：慢查询为什么慢、重复数据怎么处理才安全、业务指标到底怎么用SQL落地、以及为什么所有写SQL的人都该懂一点注入安全。如果你已经在用SQL做日常取数，或者刚写完第一个项目正打算系统提升，这篇应该是你比较需要的一份地图。

1. 窗口函数：把“组内计算”从子查询里解放出来

1.1 先分清：group by丢了明细，窗口函数不会

很多人第一次接触窗口函数，都是从“排名”这个需求开始的。比如一个订单表，要按商品类别分别算价格排名。如果只用group by，你会很痛苦：因为一旦分组，非聚合字段就不能直接出现在结果集里，你拿不到每条订单自己的业务主键。于是要么嵌套一层子查询去join回来，要么写一堆临时表，绕来绕去。

窗口函数的出现，就是为了解决“既要分组，又要保留每一行明细”这个问题。它基本的写法是：

select order_id, product_category, amount, row_number() over(partition by product_category order by amount desc) as rn from orders;

注意看，partition by负责划分“窗口”，order by负责决定窗口内部的计算顺序，然后每一行都会带着一个计算结果返回。它不会像group by那样把多行压成一行，而是“在旁边多算一列”。我一般跟新人这样打比方：group by像是把一堆硬币倒进存钱罐，你只看到总数；窗口函数则像给每个硬币贴上一张编号标签，你仍然能看到每一枚硬币。

1.2 排名三兄弟：row_number、rank、dense_rank怎么选

窗口函数里最常用的一组是排名函数，名字长得很像，逻辑却不一样。

我记得有一次帮运营同事做商品榜，要求是“价格相同的商品名次相同，下一个名次不能跳”。我一开始用了rank()，结果前两名价格一样的商品占了第1和第1名，第三个商品直接排到第3名。运营看到榜单中间缺了第2名，还以为是数据漏了。其实这种需求就该用dense_rank()，因为“价格名次”需要连续，不希望在名次上留洞。所以你看，这三个函数不只是拼写不同，背后的业务含义差得很远。遇到“唯一编号”选row_number，遇到“展示名次”先问清楚要不要跳号。

1.3 累计与移动平均：sum(...) over(...)不只是排名

窗口函数的功能远不止排名。最常见的进阶玩法是“累计值”和“移动计算”。

比如你要看每个月的累计销售额，最简单的窗口写法是：

select month, sales_amount, sum(sales_amount) over(order by month rows between unbounded preceding and current row) as cum_sales from monthly_sales;

这段SQL的意图是：从最早的月份一直加到当前行。你不需要先查出所有月份再跑到应用层去循环累加，一条SQL就能拿到结果。如果要算“近3个月移动平均”，把rows between改成2 preceding and current row就行。

我在实际项目中见过很多人把这类需求写复杂了：先拉全部明细到脚本里，再按顺序循环算累计，代码又长又容易错。窗口函数能把这类“顺序相关”的计算留在数据库里完成，性能往往也更好。需要提醒的是，MySQL 8.0、PostgreSQL、SQL Server、Oracle这些主流数据库都已经原生支持窗口函数了，如果你的环境还在用MySQL 5.7及以下，升级之后再来用会舒服很多。

2. 慢SQL排查：先从慢查询日志抓起，再到执行计划

2.1 第一步：打开慢查询日志，找到真正慢的SQL

很多项目的数据库性能问题，不是服务器配置不行，而是几条“慢性子”SQL拖垮了全局。排查第一步不是去优化代码，而是先把慢查询日志打开。在MySQL里，这条命令可以用来查看当前状态：

show variables like 'slow_query_log%';

如果没有开启，可以在配置里设置：

slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1

long_query_time意思是超过多少秒的查询会被记下来，我习惯先从1秒开始，线上业务如果没有太多超低频大查询，1秒已经能暴露出大部分问题。拿到慢日志后，优先处理两个特征：执行次数多、单次时间长。执行次数多但单次几毫秒的，可能是高频小查询被锁堵塞；单次几十秒但一天才跑一次的，多半是离线报表，优先级相对低一些。先分清楚这两种，再往下走，才不会白忙一场。

2.2 explain结果该怎么读：先看type、rows、key、Extra

找到具体的慢SQL后，最重要的一步是看执行计划。MySQL里直接执行：

explain select ...;

输出结果里，我通常先盯住四列：

• type：从all到index再到range、ref、eq_ref、const，基本反映扫描方式的级别。all就是全表扫描，通常最需要警惕。
• key：实际用到的索引，如果这一列是NULL，说明优化器没用上索引。
• rows：估算扫描的行数，不是精确值，但量级能帮你判断查询是否“省力”。
• Extra：出现Using filesort或Using temporary时，通常意味着排序或分组使用了临时文件，数据量大时很伤性能。

举个例子，一次线上查询定位到一个普通订单表，几百万条数据，SQL条件只有where status = 'PAID'，status列没有索引，type显示ALL，rows直接显示上百万行。加一个普通索引在status列上，查询时间从800毫秒降到了30毫秒。很多人以为数据库性能优化多高深，其实第一步往往就是“读懂执行计划、补上缺失索引”。

2.3 索引失效的高频原因：不用背，但要会排查方向

索引加了却还是不生效，这是排查路上最常见的坑。我这里列几个高频原因，大家排查时按顺序对照就能少走弯路。

• 对索引列使用函数或计算，比如where date(create_time) = '2024-01-01'，这种写法会让索引失效，正确姿势是where create_time >= '2024-01-01 00:00:00' and create_time < '2024-01-02 00:00:00'。
• 隐式类型转换，比如手机号字段是varchar类型，查询条件写成where phone = 13800001111，数字类型与字符串比较可能触发类型转换，索引就白建了。
• 前导模糊匹配，like '%keyword'无法走索引，like 'keyword%'则可以。
• or连接条件中，如果一部分字段没有索引，整个条件可能退化成全表扫描，通常拆成union all或者改用in会更稳。

这些规则在不同数据库版本里可能略有差异，但排查方向是通用的。拿到慢SQL，我建议先“盯explain→ 看key→ 看rows”三步走，比盲目改SQL高效得多。

3. 先去重，再删重：distinct只是开始

3.1 distinct、group by、union的场景差异

去重大概是日常工作里最常见的需求之一。很多人一上来就写distinct，但它并不是万能钥匙。

distinct适合“纯粹看有哪些不同值”的场景，比如查所有发生过订单的城市列表：

select distinct city from orders;

但如果你同时要做聚合统计，比如按城市统计订单量，distinct就力不从心了，这时候应该用group by：

select city, count(*) from orders group by city;

还有一类场景是跨表合并结果集时需要去重，union天然就会去重，而union all不会。我见过有人为了省事把union all改成union，以为既能合并又能去重，结果在小数据量上确实可行，数据量一大、字段一多，排序去重带来的开销却拖慢了查询。所以，别只记得它们“都能去重”，要分清：distinct是“对结果集整行去重”，group by是“按分组键聚合”，union是“集合运算去重”。业务目的不同，不该互相替代。

3.2 删除重复行：row_number窗口函数才是主力

比起查询去重，真正麻烦的是“清理表里已有的重复记录”。比如一张用户表，因为系统升级漏了唯一约束，同一手机号出现了多条记录。这时候你不仅要找出重复，还要决定“保留哪一条、删除哪几条”。

正确姿势是借助窗口函数给每个分组编号：

delete from users where id in ( select id from ( select id, row_number() over(partition by phone order by create_time desc) as rn from users ) t where t.rn > 1 );

这个思路是：按手机号分组，按创建时间倒序排序，最晚创建的那条记录编号为1，其余的都是应该删掉的。如果你希望保留最早的那条，把order by改成order by create_time asc即可。注意，MySQL处理delete加子查询时不能直接对同一张表做子查询，所以要多包一层派生表。这一步很多新手容易忽略，报错了反而一头雾水。

经验之谈：在进行这类删除操作前，先把delete换成select *跑一遍，确认要删除的id集合是否符合预期，然后开事务执行，备份好相关数据再去删。我自己就吃过一次亏，当年直接执行删除语句，发现“保留一条”的逻辑写反了，把应该保留的那条删了。虽然最后从备份恢复了，但那几小时的紧张感至今记得。

4. 业务分析型SQL实战：复购率、连续登录、留存三连

4.1 复购率：先定义业务口径，再写SQL

SQL写久了你会发现，最难的不是语法，而是“业务口径”。就拿复购率来说，不同业务定义差别很大。是“所有下单用户里，下过2单及以上的人占比”，还是“老用户中再次购买的比率”？没有口径，SQL写得再漂亮也白搭。

一种常见口径是：统计某时间段内，下单次数大于等于2的用户数，除以该时间段内总下单用户数。

with user_orders as ( select user_id, count(*) as cnt from orders where pay_time >= '2024-01-01' and pay_time < '2024-02-01' group by user_id ) select count(*) as total_users, count(case when cnt >= 2 then 1 end) as repeat_users, count(case when cnt >= 2 then 1 end) / count(*) as repurchase_rate from user_orders;

这种写法用了一条CTE（公用表表达式），也就是with子句，把“每个用户的订单数”先算出来，再在上层做汇总。CTE在复杂分析里非常好用，它让SQL像写代码一样有了“先定义中间结果，再基于中间结果二次计算”的结构，比层层嵌套子查询可读性高得多。

4.2 连续登录：日期与序号做差就是分组

连续登录是另一个经典题。比如要找出连续登录3天以上的用户。最容易理解的做法是：先把每个用户的登录日期去重，然后用row_number()按时间排序编号，再用登录日期减去编号得到一个“分组标记”。如果登录是连续的，这个差值会相同。

with distinct_logins as ( select user_id, login_date from login_log group by user_id, login_date ), tagged as ( select user_id, login_date, date_sub(login_date, interval row_number() over(partition by user_id order by login_date) day) as grp from distinct_logins ) select user_id from tagged group by user_id, grp having count(*) >= 3;

逻辑原理其实很简单：连续日期的login_date和序号步进都是“加1”，所以差值恒定；一旦中断，差值就跳变。这个方法我第一次看到时也觉得巧，后来自己推导了一遍才明白，它本质上是用数学关系把“连续性”转化为“分组条件”。

4.3 分析型SQL的调试思路

写这类复杂分析SQL，我建议不要一口气写完就执行。我是这样操作的：先小范围验证，把group by user_id, grp换成只取某几个用户，加上where限制，跑通一步再放开一步。CTE的优势在于你可以单独执行中间的每一段，比如先跑distinct_logins看结果对不对，再跑tagged看标记是否有问题。用这种方式定位错误会快很多。

还有一点值得多说一句：遇到“连续N天”“最多N次”“首次之后首次”这类问题，十有八九是窗口函数加CTE的组合就能解决。如果你的第一反应是写循环，或者把所有数据拉到程序里用Python处理，可以停下来重新想一想，是不是数据库里一条SQL就能完成。

5. SQL注入：写SQL的人必须建立的底线认知

5.1 注入本质：拼字符串的锅

聊SQL进阶，绕不开一个话题：SQL注入。我知道这个话题看着像安全攻击，但我觉得恰恰相反——它是每个写SQL的人都该具备的“底线认知”，因为你只有清楚攻击是怎么发生的，才能在任何地方都下意识写出安全代码。

SQL注入的本质是字符串拼接。当你的SQL是这样拼出来的：

select * from users where username = '" + username + "' and password = '" + password + "'

攻击者只需要在用户名输入框填一个' or '1'='1，整条SQL就会变成：

select * from users where username = '' or '1'='1' and password = '' or '1'='1'

逻辑上恒为真，登录校验直接被绕过。这不是数据库的漏洞，而是代码写法的漏洞。再说了，不光是登录框，搜索、排序、分页、任何把外部输入直接拼进SQL的地方，都是风险入口。

5.2 防御：参数化是第一原则，过滤只是补充

写了几年代码，我的第一原则已经变成一句话：永远不要用字符串拼接的方式去构造SQL，永远使用参数化查询。

以Java的JDBC为例，正确写法是：

PreparedStatement ps = conn.prepareStatement( "select * from users where username = ? and password = ?" ); ps.setString(1, username); ps.setString(2, password);

数据库驱动会把参数值当作“值”而不是可执行的SQL片段来传递，无论用户在输入框里填了什么，都只是一个普通的字符串，不可能改变SQL结构。在Python里，pymysql的示例是：

cursor.execute("select * from users where username = %s", (username,))

注意，不是百分号拼字符串，而是把参数作为第二个参数传给引擎。所有主流语言的数据库驱动都支持参数化，你需要做的只是改变一下习惯。

我还见过有人主张“用关键字过滤来防注入”，比如过滤select、or、'。说实话，这种思路只能算补充，不能当主要防线，因为绕过方式太多了，编码、注释、大小写、函数嵌套都能被利用。真正牢固的防线只有参数化、权限最小化这两条：应用账号不要给drop、delete这类的过高权限，线上库尤其如此。

5.3 我的实战教训

这里分享一个亲身经历。早年间我维护过一个后台报表系统，为了让运营“灵活筛选”，直接在前端传了一段拼好的条件过来，后端拼接进SQL执行。听起来是不是很离谱？但当时确实那么做了。后来渗透测试报告里专门标了一条高危漏洞，指出只要构造特定参数，就能把整张用户表的数据带出来。那次之后，我彻底改了习惯，所有动态条件都改成参数化，并且限制了查询账号的权限。

这件事给我的教训是：安全不是安全工程师一个人的事，所有和SQL打交道的人都该有这根弦。当你习惯性用参数化，习惯性给账号最小权限，很多事故天然就不会发生。而这也是我认为一个使用SQL的从业者，真正从“会用”走向“可靠”的分水岭。

这篇文章写到这里，主要想说的其实是同一件事：SQL进阶的关键不在记更多语法，而在于建立三种感觉——对计算逻辑的感觉、对性能的感觉、对安全边界的感觉。窗口函数帮你打开思路，执行计划帮你看清代价，去重和删除的操作让你谨慎，分析型SQL让你贴业务，注入的认知则让你写出更负责任的代码。至于下一步该练什么，我个人的建议是每天花半小时，把工作中写过的SQL全部重写一遍，尝试用窗口函数或CTE替代老写法，再打开执行计划对比一下差异。坚持一两个月，你会发现自己看查询的方式都不一样了。