1. 看懂SQL的思维链路先想清楚数据长什么样再写语句最近团队里来了个新人聊到SQL水平他说增删改查都会写SQL入门嘛。结果一遇到统计每个分类下销量前3的商品这种需求就卡在窗口函数上让他去掉重复订单第一反应就是DISTINCT一把梭重了再GROUP BY加码。我意识到一个问题很多人学SQL的姿势从一开始就反了——把重心放在背语法细节上而不是建立数据操作思路。SQL这东西语法只是表达工具真正值钱的思路拿到一个业务需求能不能快速判断出该用哪些操作、这些操作按什么顺序组合、每一步会在数据上产生什么效果。1.1 从需求到SQL的三步翻译我习惯把一个自然语言需求拆成三段来想这也是我面试别人时必问的框架我要什么东西对应SELECT后面的字段是原始列、聚合值还是计算后的结果。这东西在哪个集合里对应FROM和JOIN先搞清楚数据源是有几张表要做关联还是一些子查询拼出来的临时集合。怎么筛、怎么切、怎么排、取多少对应WHERE、GROUP BY、HAVING、ORDER BY和LIMIT。举个例子查每个省份近30天支付成功的订单总额只要超过1万的按金额倒序要的是省份订单总额SELECT province, SUM(amount)。数据来自订单表可能还要关联省份维度表FROM orders JOIN dim_province。筛近30天、支付成功WHERE里做时间和状态过滤按省份分组用GROUP BY province只保留过1万的组用HAVING SUM(amount) 10000排序ORDER BY。这一步做对了SQL写出来基本八九不离十。做不对的常见原因是把聚合条件混进WHERE——比如想把总额大于1万写在WHERE里报错之后才想起HAVING。根本原因就是没理解WHERE是先筛行、HAVING是后筛组这不是语法问题是执行顺序没进脑子。1.2 执行顺序决定你该把条件写在哪数据库执行一条SQL时逻辑上的顺序是这样的FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT注意SELECT里取的别名ORDER BY能用但WHERE不能用——因为WHERE执行的时候SELECT还没算。这也是一个高频报错Unknown column in where clause的来源。我经常跟人打比方FROM是选食材WHERE是洗菜切菜处理单行的GROUP BY是把菜装盘分组HAVING是把不合格的盘直接扔掉SELECT才是最后摆盘的样子ORDER BY和LIMIT相当于上菜顺序和限桌数。顺序错了就像菜还没洗就开始摆盘逻辑上就是反的。这个顺序认知还会直接帮你看懂很多玄学问题。比如SELECT user_id, COUNT(*) FROM orders WHERE COUNT(*) 3 GROUP BY user_id;这种写法报错就是因为聚合结果在GROUP BY之后才存在WHERE根本看不到它。正确做法是把COUNT(*) 3挪到HAVING。这类错误如果你只是背了聚合条件用HAVING这个结论换个场景还是会踩想通了执行顺序就永远不会再犯。1.3 JOIN的取舍逻辑先定主表再想保留谁另一个特别考验思路的地方是JOIN。内连接、左连接、右连接、全连接看着是四种语法本质只有一个问题连接结果里以哪个表的行为准。左连接就是左边表的每一行必须出现右边能配上就配上配不上就补NULL内连接则是两边都匹配上才保留。实际业务里我几乎只用INNER JOIN和LEFT JOIN右连接和全连接很少碰——因为把表换个方向右连接就能用左连接替代没必要给团队留阅读负担。真正要想清楚的是主表是谁哪张表的记录数决定结果集的记录数。关联键会不会重复关联键重复会导致结果翻倍这是一个大坑。比如订单表left join支付流水表一个订单可能有多条支付流水结果瞬间膨胀好几倍聚合数字全错。这种问题查聚合报表时特别隐蔽——不会报错但数据就是不对。排查思路就是先查两边关联键的唯一性。2. 高频SQL的可靠写法去重、空值与窗口函数热搜词里sql语句去重反复出现说明这是工作中最常碰的硬需求。但很多人对去重的理解就停在DISTINCT遇到复杂一点的需求就不知道怎么拆了。我把日常最高频的几个场景集中讲一遍每一个都是踩过坑之后沉淀下来的写法。2.1 去重这件事DISTINCT和GROUP BY怎么选SELECT DISTINCT col1, col2和SELECT col1, col2 GROUP BY col1, col2结果在大多数场景下是一样的。但两者逻辑内核不同DISTINCT是对结果整体去重GROUP BY是先分组再取值。实际使用我会按两个标准来选只去重、不做聚合用DISTINCT语义清楚代码短。同一组里还要算数量、总和、平均那必然GROUP BY因为DISTINCT不具备聚合能力。还有一个经常被忽略的坑DISTINCT是作用于后面整组字段的不是只作用于第一个字段。SELECT DISTINCT user_id, order_id是把两个字段的组合去重而不是只看user_id。想要每个用户一条记录还得配合聚合函数选代表值或者用窗口函数。真正到清洗级别热搜里出现了清洗---sql语句去重比如保留每组里最新的一条完整记录DISTINCT就完全不够用了。这时候标准答案是ROW_NUMBER()窗口函数WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM user_events ) SELECT * FROM ranked WHERE rn 1;PARTITION BY user_id是给每个用户单独开一组ORDER BY create_time DESC让最新的排第1最后留下每组第1行。这个写法要背下来它是去重场景里的瑞士军刀。注意一点如果业务允许并列最新想保留多条就把ROW_NUMBER()换成RANK()或DENSE_RANK()两者在并列值处理上有细微差别需要根据业务确定。去重还有个经典陷阱就是COUNT配合DISTINCT的语义SELECT COUNT(DISTINCT user_id) FROM orders;这条是统计有多少个不同的用户下了单而不是有多少个不同订单。早期我见过同事把这条当订单数用结果汇报时数字对不上排查了半天。**COUNT(DISTINCT)的语义要格外敏感它不是普通计数的替代品。**另外当去重字段包含大量NULL时COUNT(DISTINCT col)默认是忽略NULL的。如果你想把NULL也算一个独立值得用COUNT(DISTINCT COALESCE(col, 占位值))做兜底。2.2 空值处理COUNT、聚合函数和NULL的三角关系sql去除空值也是个高频问题。NULL在SQL里代表未知它的行为跟普通值完全不同。很多人初学时在这里吃亏NULL NULL结果是未知不是true所以不能用WHERE col NULL必须用IS NULL。NULL参与 - * /运算结果还是NULL做金额字段累加时特别容易丢数据。COUNT(col)只计数非NULL值COUNT(*)计数所有行两者结果可能差很多。举个例子统计用户表有多少人填了手机号SELECT COUNT(*) FROM users; -- 总人数 SELECT COUNT(phone) FROM users; -- 有手机号的人数 SELECT COUNT(1) FROM users; -- 和COUNT(*)基本等价COUNT(phone)自动忽略NULL所以它是有手机号的人数。这个特性日常很实用但也容易坑人因为如果手机号字段有脏数据空字符串COUNT()是会计数的。这时候就得先清洗SELECT COUNT(NULLIF(TRIM(phone), )) FROM users;NULLIF(a, b)在a等于b时返回NULL配合TRIM清空格就能把空字符串也排除掉。这套组合拳在数据清洗SQL里出现频率极高。聚合函数那边也要小心NULL。SUM(amount)如果所有行都是NULL结果是NULL而不是0。做报表时如果不处理前端拿到的就是空值经常被业务方质问为什么没有数据。稳妥做法是外圈包一层COALESCESELECT COALESCE(SUM(amount), 0) AS total_amount FROM orders;2.3 窗口函数TopN和同比环比的行内计算窗口函数是SQL思路进阶的分水岭。它解决的核心问题是既想要明细行的粒度又想要某个范围内的聚合结果。子查询也能做但窗口函数写起来清爽得多性能也更好。我日常工作用到最多的四个函数作用典型场景ROW_NUMBER()组内按条件编号分组去重、TopNLAG()/LEAD()取前后第n行的值同比环比、步进差SUM() OVER()组内累计求和累计销售额、占比RANK()/DENSE_RANK()排名含并列处理排行榜拿每个分类销量Top3来说只需要前面那套ROW_NUMBER()的写法把PARTITION BY换成分类字段即可。而查每天订单量和前一天比是涨是跌是LAG的经典用法WITH daily AS ( SELECT order_date, COUNT(*) AS cnt FROM orders WHERE order_date DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY) GROUP BY order_date ) SELECT order_date, cnt, LAG(cnt, 1) OVER (ORDER BY order_date) AS prev_cnt, cnt - LAG(cnt, 1) OVER (ORDER BY order_date) AS diff FROM daily ORDER BY order_date;这里有个细节LAG(cnt, 1)取的是一天前的数据。如果某一天没有订单不会有cnt0的记录那LAG会跳过缺失日期、取到上一条存在的记录结果就不准确。对这种连续时间分析最好先补全日期序列再算不要想当然。窗口函数的执行顺序也容易栽跟头窗口函数在WHERE和GROUP BY之后计算所以不能在WHERE里直接筛选窗口函数的结果。想筛掉排名3的得把窗口函数放在子查询里外面再过滤就像前面rn 1的写法一样。3. 慢SQL排查与优化从执行计划到分页落地慢sql优化、并行sql优化、sql server 2012密码到期这些热搜词挤在一起其实代表了日常数据库运维的几种典型痛。我先把慢SQL这块讲透因为这是真正影响线上稳定性的问题也是最见功力的地方。3.1 EXPLAIN是慢SQL的照妖镜接到慢SQL反馈第一件事不是猜是去看执行计划。MySQL的写法是EXPLAIN SELECT ...SQL Server可以用SET STATISTICS PROFILE ON或者直接把执行计划图形化打开。我最常用的几个关注点type列从ALL全表扫、range索引范围到ref、eq_ref、const是一个从差到好的序列。看到ALL基本意味着ALL意味着这条SQL是全表扫描数据量大时必然慢。看到这个就先别急着加索引要看WHERE条件里的字段到底能不能走索引。key列实际用的索引是哪个。possible_keys是可能用到的key是最终用的两者不一致时要想为什么——可能因为选择性太差优化器放弃了也可能因为字段经过了函数处理。rows列估算扫描的行数这个数字经常能吓你一跳。同样的查询2000万的表扫出1800万行不快才怪。Extra列出现Using filesort说明排序没用上索引出现Using temporary说明用了临时表通常跟GROUP BY或DISTINCT有关。这两项都是性能杀手。我见过最典型的翻车现场一张订单表几千万行按order_time查最近7天数据order_time上明明有索引但EXPLAIN出来还是ALL。最后发现是因为WHERE order_time 2024-01-01这个字符串和索引字段类型不匹配触发了隐式类型转换索引直接失效。把参数改成时间类型就好了一半。这就是看执行计划比猜索引有用的地方——你不知道索引为什么没生效但EXPLAIN会直接告诉你。3.2 慢SQL的五种典型写法优化做多了会发现慢SQL翻来覆去就那么几类。我把它们归档成五个模式方便按图索骥SELECT *且不需要的列全拿传输数据量暴增还可能让Using filesort出现。改成只取需要的字段。条件列套函数WHERE DATE(create_time) 2024-01-01这种写法索引没法用在create_time上。改写成WHERE create_time 2024-01-01 AND create_time 2024-01-02索引就活了。隐式类型转换字符串和数字比、字符集不一致的关联都会让索引失效。关联条件两边字段类型、字符集要统一。深分页LIMIT 1000000, 20数据库要扫过前100万行再偏移。数据量上去之后这种写法必炸。不必要的大范围过滤WHERE status IN (pending, success)且状态分布极不均衡时优化器可能认为走全表更快——这种要结合实际数据分布解决。这些模式里深分页是我最想展开的因为它最容易线上出事故。硬啃这种分页的场景很常见后台管理列表、用户中心订单列表、运营后台导出任务数据量上来之后用户翻到第几十页就明显变慢。3.3 深分页优化与并行SQL的实际边界深分页的解法行业里成熟的有两条路第一条延迟关联deferred join。先只查主键拿到需要的20个主键之后再用主键去回表取完整数据-- 优化前深分页扫了很多无用行 SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 20; -- 优化后先快速定位20个主键再回表拿数据 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 1000000, 20 ) t ON o.id t.id;子查询里只用id和create_time可以走覆盖索引索引里就有id和create_time不需要回表扫描量小很多。等拿到20个主键再回原表取完整行代价就很小了。第二条游标分页 / 键集分页。不限LIMIT偏移量而是通过WHERE create_time 上次最后一条的create_time不断往下捞。这种做法的前提是排序字段必须唯一或组合唯一否则锚点会漂移。它的写法长这样SELECT * FROM orders WHERE create_time 2024-05-01 10:00:00 ORDER BY create_time DESC, id DESC LIMIT 20;这种方式对用户翻页体验也更好每一页定位固定翻页是O(1)定位而不是从全表重新扫。代价是分页逻辑里必须记住上一页的最后一条。 至于并行sql优化我得泼点冷水并行执行不是银弹。数据库的并行度受限于CPU核数、IO能力而且在小表上并行反而更慢因为调度开销大。实际项目里我更倾向于从SQL本身下手能走索引的走索引能减少扫描量的减少扫描量。并行只是当单条SQL太重、资源够、并发不高时的选项不要一开始就急着上并行。最后提醒一下慢SQL排查不要只看单条语句要看整体负载。有时候一条SQL本身还行但每秒被调用上千次照样把数据库拖垮。优化思路要从单次成本和调用次数两个维度一起算总账这也是为什么我会建议在应用层做缓存、做读写分离而不是一味调SQL。 ## 4. SQL注入防护思路识别拼接风险与参数化底线 sql注入、sql注入万能密码绕过、dvwa sql注入、python sql注入原理这些热搜词一大片说明这个问题是做后端开发绕不开的门槛。这章我不讲攻击技巧重点讲**为什么会产生注入、以及代码层怎么防**——安全的原则是防御不是教人攻击。 ### 4.1 注入的本质用户输入变成了代码 SQL注入的根本原因一句话就能说透**你本想把用户输入当成数据拼进SQL结果它被当成了代码执行**。比如经典的低级写法 python sql SELECT * FROM users WHERE name name 如果name的值是admin OR 11拼接出来的SQL就变成了SELECT * FROM users WHERE name admin OR 11本来name只是一个值现在因为引号被转义多出来一个OR 11整个WHERE条件恒真所有用户记录全被返回。从数据库的角度看它执行的是一条合法SQL从应用的角度看它执行了开发者根本没打算执行的东西。这个问题的本质是数据和指令没有分开。安全领域反复强调的永不信任用户输入落在SQL这层就是一句话不要让用户输入以任何形式变成SQL结构的一部分——要么用参数化查询要么严格白名单校验。4.2 防御的第一道防线参数化查询参数化查询Prepared Statement的原理是先把SQL的骨架发给数据库编译编译完成后再把参数值单独传过去。因为SQL结构已经定死了参数值无论怎么拼接都只是值不可能变成新的SQL片段。以Python为例正确写法是import sqlite3 conn sqlite3.connect(app.db) cur conn.cursor() # 错误字符串拼接 # cur.execute(SELECT * FROM users WHERE name name ) # 正确参数化 cur.execute(SELECT * FROM users WHERE name ?, (name,))注意那个?占位符——数据库先编译按name查用户这个操作再把name当作一个参数绑定进去。哪怕name传的是admin OR 11数据库也只会把它当成一个字符串值去比较不存在语法变化。Java的JDBC用PreparedStatementGo的database/sql用?占位Node.js的mysql库用?或命名参数原理都一样。甚至MyBatis这类ORM框架只要用#{}占位而不是${}拼接底层走的也是预编译绑定。这里有个容易误解的点存储过程不天然防注入。如果存储过程内部仍然用字符串拼接动态SQL注入风险依然存在。真正防护靠的是参数绑定这个机制不是放进数据库这个动作。4.3 纵深防御权限最小化与数据库侧配置参数化能挡住绝大多数注入但安全不能靠单点。我还习惯做这几层防护数据库账号权限最小化应用账号只给需要的表的最低权限比如只读账号给SELECT写账号尽量不给DDL权限、不给DROP权限。即使被注入攻击面也被压缩了——它想读没得读想删也删不掉。输入校验做白名单能枚举的参数状态值、类型、排序字段坚决用枚举数值类型的参数先转成整数再进SQL分页的LIMIT参数强制整型。错误信息不要回显给用户SQL报错细节泄露表结构生产环境的异常处理要统一拦截。日志监控记录异常SQL模式比如单条SQL出现大量连词、引号、注释符号的可以走告警。还有一个被很多人忽略的点ORM不等于绝对安全。用MyBatis的${}或者用JPA原生SQL拼接查询条件照样有注入风险。框架只是工具安全的底线永远是参数和SQL结构分开这条自觉。我面试时经常问候选人一句话你的ORM框架真正执行SQL时是预编译绑定变量还是直接拼接能答清楚的至少在安全这条线上是有意识的。 提示做安全演练或测试漏洞时只在本地靶场如DVWA这类专门构建的测试环境进行绝不要在未授权系统上做任何探测。测试的目的是理解原理、加固防御不是攻击他人系统。5. 环境与工具的常见坑安装、连接、报错排查热搜词里SQL Server相关的占了一大片sql server安装教程、sql server2019安装教程、sql server2022安装教程、solidworks electrical 无法连接到 sql server、navicat for sql server激活码、sql server 2012密码到期、free download for sql server management studio还有Oracle的ora-12518、SQLite的no such column。这些都是实打实的日常环境问题我挑几个共性最强的展开。5.1 SQL Server安装与连接的常见失败点SQL Server在Windows上装起来本身不难但翻车率高在几个地方实例名和端口搞混默认实例是MSSQLSERVER端口1433命名实例的端口是动态分配的默认49152之后本地连接用机器名能连远程连就经常找不到。连接字符串里的ServerIP\\实例名写法很容易因为反斜杠在配置里被转义而翻车。防火墙挡了1433端口装了连不上八成是防火墙没放行。检查方向集中在Windows防火墙入站规则。混合认证模式没开只用Windows认证模式时用账号密码连会报登录失败需要在实例属性里切换为混合模式并启用sa账号。热搜里solidworks electrical 无法连接到 sql server这个报错其实很典型——CAD类的工具软件一般会在本机装一个SQL Server Express实例装完连不上要么是实例服务没启动要么是登录账号密码不匹配要么是防火墙把命名实例端口挡了。排查链路通常是确认SQL Server服务是否在运行services.msc里看SQL Server开头的服务。确认连接用的实例名是否写对本机命名实例一般形如主机名\\SQLEXPRESS。确认账号密码能被接受工具默认账号往往是sa但Windows认证模式下它不可用。5.2 典型连接报错的排查链路我把三类典型的连接类错误放在一起对比方便举一反三报错常见原因排查顺序SQL Server无法连接到服务器服务未启动、防火墙、实例名错先看服务再测1433再查实例名OracleORA-12518 监听程序无法分发监听服务满、进程数超限、连接数爆了查lsnrctl status查进程数查v$process/v$sessionSQLiteno such column: test_url表结构里根本没有这个字段先确认PRAGMA table_info(表名)别想当然SQLite那个no such column我多说一句。它跟数据库主机类报错不同问题往往出在代码侧——建表语句和查询语句字段名不一致。最常见的原因是迁移脚本没执行或者迁移脚本和实际代码不是同一份。排查时先跑一下PRAGMA table_info(表名)看真实字段再回头看代码或迁移脚本里字段名大小写和拼写。SQLite对字段名大小写不敏感但对拼写差异敏感test_url和testUrl这种驼峰命名不一致也是高频翻车点。连接类的排查思路我的习惯永远是自底向上从服务有没有起来→端口能不能通→账号密码对不对→SQL语句本身对不对每一层都要先确认确实没问题再往上走。大部分连不上其实是第一层的问题大部分连上但报错其实是第四层的问题。按这个顺序排查效率高很多。5.3 客户端工具的选型与长SQL调试经验工具这块我在不同项目里都折腾过最终结论是没有全能神器按场景换工具。SQL Server场景官方SSMS功能最全尤其图形化执行计划、索引调优建议这两个功能SQL Server用户必备。下载时认准官方渠道不要搜激活码之类的东西——SSMS本身就是免费的不需要激活搜索引擎里那些带激活码的下载站反而是捆绑风险最大的坑。MySQL场景Navicat和DBeaver都不错。前者对新手友好但它是商业付费软件后者开源免费跨平台连接类型支持多。各有取舍。Oracle场景PL/SQL Developer是老牌工具但首次配置Oracle客户端会很劝退用DBeaver连接Oracle也能凑合复杂调试体验略差。SQLite场景DB Browser for SQLite就够用了轻量、免费、直接看表结构。调试长SQL是我个人特别有体会的一点。SQL一长嵌套子查询一多人眼根本排不出版本差异。我的做法是从上往下拆先跑最内层的子查询确认结果集对再一层层往外包。比如一个三层嵌套的报表SQL我会先把最内层SELECT单独跑一遍看数据量级和字段是否符合预期再用这段子查询作为下一层的FROM数据源去调试。每层都验证完毕整条SQL基本不会出错。我还习惯在调试时把WHERE条件先去掉或者LIMIT给个几十条先把结构和字段逻辑跑通再逐步加上过滤条件验证结果变化。SQL里最让人头疼的不是语法错误而是语法对、逻辑错——结果数字看起来合理实际因为某个关联字段重复导致翻倍了。这种时候就是用前面说的先验关联键唯一性的方法一步步排查。另外SQL Server 2012密码到期这类问题本质就是策略问题ALTER LOGIN sa WITH CHECK_POLICY OFF可以临时关掉密码策略但生产环境更推荐的做法是设置定期改密流程别为了省事把安全基线整个关掉。cmd导出sql、dm数据迁移工具导入.sql这些场景本质是文件编码和分隔符的问题——导出的SQL文件如果是UTF-8带BOM导入到某些数据库客户端会多出一个不可见字符直接报语法错误。遇到导入.sql报错先检查文件编码再用十六进制看一眼文件头很多问题瞬间就有答案了。聊到最后说点个人的经验吧。我从刚入行时只会SELECT *到现在能在一分钟内判断一条SQL该走索引还是该改写法唯一的分水岭就是把上面的每一层都想明白了数据的形态、执行的顺序、代价的来源。工具和语法永远在变但思路这件事一旦建立后面换数据库、换ORM、换项目都不虚。你现在要做的就是拿起手边那条曾经让你头疼的SQL按这篇的思路重新拆一遍看能不能写出更干净、更快的版本。 SQL进阶核心：去重清洗、窗口函数、慢SQL优化与注入防护
1. 看懂SQL的思维链路先想清楚数据长什么样再写语句最近团队里来了个新人聊到SQL水平他说增删改查都会写SQL入门嘛。结果一遇到统计每个分类下销量前3的商品这种需求就卡在窗口函数上让他去掉重复订单第一反应就是DISTINCT一把梭重了再GROUP BY加码。我意识到一个问题很多人学SQL的姿势从一开始就反了——把重心放在背语法细节上而不是建立数据操作思路。SQL这东西语法只是表达工具真正值钱的思路拿到一个业务需求能不能快速判断出该用哪些操作、这些操作按什么顺序组合、每一步会在数据上产生什么效果。1.1 从需求到SQL的三步翻译我习惯把一个自然语言需求拆成三段来想这也是我面试别人时必问的框架我要什么东西对应SELECT后面的字段是原始列、聚合值还是计算后的结果。这东西在哪个集合里对应FROM和JOIN先搞清楚数据源是有几张表要做关联还是一些子查询拼出来的临时集合。怎么筛、怎么切、怎么排、取多少对应WHERE、GROUP BY、HAVING、ORDER BY和LIMIT。举个例子查每个省份近30天支付成功的订单总额只要超过1万的按金额倒序要的是省份订单总额SELECT province, SUM(amount)。数据来自订单表可能还要关联省份维度表FROM orders JOIN dim_province。筛近30天、支付成功WHERE里做时间和状态过滤按省份分组用GROUP BY province只保留过1万的组用HAVING SUM(amount) 10000排序ORDER BY。这一步做对了SQL写出来基本八九不离十。做不对的常见原因是把聚合条件混进WHERE——比如想把总额大于1万写在WHERE里报错之后才想起HAVING。根本原因就是没理解WHERE是先筛行、HAVING是后筛组这不是语法问题是执行顺序没进脑子。1.2 执行顺序决定你该把条件写在哪数据库执行一条SQL时逻辑上的顺序是这样的FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT注意SELECT里取的别名ORDER BY能用但WHERE不能用——因为WHERE执行的时候SELECT还没算。这也是一个高频报错Unknown column in where clause的来源。我经常跟人打比方FROM是选食材WHERE是洗菜切菜处理单行的GROUP BY是把菜装盘分组HAVING是把不合格的盘直接扔掉SELECT才是最后摆盘的样子ORDER BY和LIMIT相当于上菜顺序和限桌数。顺序错了就像菜还没洗就开始摆盘逻辑上就是反的。这个顺序认知还会直接帮你看懂很多玄学问题。比如SELECT user_id, COUNT(*) FROM orders WHERE COUNT(*) 3 GROUP BY user_id;这种写法报错就是因为聚合结果在GROUP BY之后才存在WHERE根本看不到它。正确做法是把COUNT(*) 3挪到HAVING。这类错误如果你只是背了聚合条件用HAVING这个结论换个场景还是会踩想通了执行顺序就永远不会再犯。1.3 JOIN的取舍逻辑先定主表再想保留谁另一个特别考验思路的地方是JOIN。内连接、左连接、右连接、全连接看着是四种语法本质只有一个问题连接结果里以哪个表的行为准。左连接就是左边表的每一行必须出现右边能配上就配上配不上就补NULL内连接则是两边都匹配上才保留。实际业务里我几乎只用INNER JOIN和LEFT JOIN右连接和全连接很少碰——因为把表换个方向右连接就能用左连接替代没必要给团队留阅读负担。真正要想清楚的是主表是谁哪张表的记录数决定结果集的记录数。关联键会不会重复关联键重复会导致结果翻倍这是一个大坑。比如订单表left join支付流水表一个订单可能有多条支付流水结果瞬间膨胀好几倍聚合数字全错。这种问题查聚合报表时特别隐蔽——不会报错但数据就是不对。排查思路就是先查两边关联键的唯一性。2. 高频SQL的可靠写法去重、空值与窗口函数热搜词里sql语句去重反复出现说明这是工作中最常碰的硬需求。但很多人对去重的理解就停在DISTINCT遇到复杂一点的需求就不知道怎么拆了。我把日常最高频的几个场景集中讲一遍每一个都是踩过坑之后沉淀下来的写法。2.1 去重这件事DISTINCT和GROUP BY怎么选SELECT DISTINCT col1, col2和SELECT col1, col2 GROUP BY col1, col2结果在大多数场景下是一样的。但两者逻辑内核不同DISTINCT是对结果整体去重GROUP BY是先分组再取值。实际使用我会按两个标准来选只去重、不做聚合用DISTINCT语义清楚代码短。同一组里还要算数量、总和、平均那必然GROUP BY因为DISTINCT不具备聚合能力。还有一个经常被忽略的坑DISTINCT是作用于后面整组字段的不是只作用于第一个字段。SELECT DISTINCT user_id, order_id是把两个字段的组合去重而不是只看user_id。想要每个用户一条记录还得配合聚合函数选代表值或者用窗口函数。真正到清洗级别热搜里出现了清洗---sql语句去重比如保留每组里最新的一条完整记录DISTINCT就完全不够用了。这时候标准答案是ROW_NUMBER()窗口函数WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM user_events ) SELECT * FROM ranked WHERE rn 1;PARTITION BY user_id是给每个用户单独开一组ORDER BY create_time DESC让最新的排第1最后留下每组第1行。这个写法要背下来它是去重场景里的瑞士军刀。注意一点如果业务允许并列最新想保留多条就把ROW_NUMBER()换成RANK()或DENSE_RANK()两者在并列值处理上有细微差别需要根据业务确定。去重还有个经典陷阱就是COUNT配合DISTINCT的语义SELECT COUNT(DISTINCT user_id) FROM orders;这条是统计有多少个不同的用户下了单而不是有多少个不同订单。早期我见过同事把这条当订单数用结果汇报时数字对不上排查了半天。**COUNT(DISTINCT)的语义要格外敏感它不是普通计数的替代品。**另外当去重字段包含大量NULL时COUNT(DISTINCT col)默认是忽略NULL的。如果你想把NULL也算一个独立值得用COUNT(DISTINCT COALESCE(col, 占位值))做兜底。2.2 空值处理COUNT、聚合函数和NULL的三角关系sql去除空值也是个高频问题。NULL在SQL里代表未知它的行为跟普通值完全不同。很多人初学时在这里吃亏NULL NULL结果是未知不是true所以不能用WHERE col NULL必须用IS NULL。NULL参与 - * /运算结果还是NULL做金额字段累加时特别容易丢数据。COUNT(col)只计数非NULL值COUNT(*)计数所有行两者结果可能差很多。举个例子统计用户表有多少人填了手机号SELECT COUNT(*) FROM users; -- 总人数 SELECT COUNT(phone) FROM users; -- 有手机号的人数 SELECT COUNT(1) FROM users; -- 和COUNT(*)基本等价COUNT(phone)自动忽略NULL所以它是有手机号的人数。这个特性日常很实用但也容易坑人因为如果手机号字段有脏数据空字符串COUNT()是会计数的。这时候就得先清洗SELECT COUNT(NULLIF(TRIM(phone), )) FROM users;NULLIF(a, b)在a等于b时返回NULL配合TRIM清空格就能把空字符串也排除掉。这套组合拳在数据清洗SQL里出现频率极高。聚合函数那边也要小心NULL。SUM(amount)如果所有行都是NULL结果是NULL而不是0。做报表时如果不处理前端拿到的就是空值经常被业务方质问为什么没有数据。稳妥做法是外圈包一层COALESCESELECT COALESCE(SUM(amount), 0) AS total_amount FROM orders;2.3 窗口函数TopN和同比环比的行内计算窗口函数是SQL思路进阶的分水岭。它解决的核心问题是既想要明细行的粒度又想要某个范围内的聚合结果。子查询也能做但窗口函数写起来清爽得多性能也更好。我日常工作用到最多的四个函数作用典型场景ROW_NUMBER()组内按条件编号分组去重、TopNLAG()/LEAD()取前后第n行的值同比环比、步进差SUM() OVER()组内累计求和累计销售额、占比RANK()/DENSE_RANK()排名含并列处理排行榜拿每个分类销量Top3来说只需要前面那套ROW_NUMBER()的写法把PARTITION BY换成分类字段即可。而查每天订单量和前一天比是涨是跌是LAG的经典用法WITH daily AS ( SELECT order_date, COUNT(*) AS cnt FROM orders WHERE order_date DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY) GROUP BY order_date ) SELECT order_date, cnt, LAG(cnt, 1) OVER (ORDER BY order_date) AS prev_cnt, cnt - LAG(cnt, 1) OVER (ORDER BY order_date) AS diff FROM daily ORDER BY order_date;这里有个细节LAG(cnt, 1)取的是一天前的数据。如果某一天没有订单不会有cnt0的记录那LAG会跳过缺失日期、取到上一条存在的记录结果就不准确。对这种连续时间分析最好先补全日期序列再算不要想当然。窗口函数的执行顺序也容易栽跟头窗口函数在WHERE和GROUP BY之后计算所以不能在WHERE里直接筛选窗口函数的结果。想筛掉排名3的得把窗口函数放在子查询里外面再过滤就像前面rn 1的写法一样。3. 慢SQL排查与优化从执行计划到分页落地慢sql优化、并行sql优化、sql server 2012密码到期这些热搜词挤在一起其实代表了日常数据库运维的几种典型痛。我先把慢SQL这块讲透因为这是真正影响线上稳定性的问题也是最见功力的地方。3.1 EXPLAIN是慢SQL的照妖镜接到慢SQL反馈第一件事不是猜是去看执行计划。MySQL的写法是EXPLAIN SELECT ...SQL Server可以用SET STATISTICS PROFILE ON或者直接把执行计划图形化打开。我最常用的几个关注点type列从ALL全表扫、range索引范围到ref、eq_ref、const是一个从差到好的序列。看到ALL基本意味着ALL意味着这条SQL是全表扫描数据量大时必然慢。看到这个就先别急着加索引要看WHERE条件里的字段到底能不能走索引。key列实际用的索引是哪个。possible_keys是可能用到的key是最终用的两者不一致时要想为什么——可能因为选择性太差优化器放弃了也可能因为字段经过了函数处理。rows列估算扫描的行数这个数字经常能吓你一跳。同样的查询2000万的表扫出1800万行不快才怪。Extra列出现Using filesort说明排序没用上索引出现Using temporary说明用了临时表通常跟GROUP BY或DISTINCT有关。这两项都是性能杀手。我见过最典型的翻车现场一张订单表几千万行按order_time查最近7天数据order_time上明明有索引但EXPLAIN出来还是ALL。最后发现是因为WHERE order_time 2024-01-01这个字符串和索引字段类型不匹配触发了隐式类型转换索引直接失效。把参数改成时间类型就好了一半。这就是看执行计划比猜索引有用的地方——你不知道索引为什么没生效但EXPLAIN会直接告诉你。3.2 慢SQL的五种典型写法优化做多了会发现慢SQL翻来覆去就那么几类。我把它们归档成五个模式方便按图索骥SELECT *且不需要的列全拿传输数据量暴增还可能让Using filesort出现。改成只取需要的字段。条件列套函数WHERE DATE(create_time) 2024-01-01这种写法索引没法用在create_time上。改写成WHERE create_time 2024-01-01 AND create_time 2024-01-02索引就活了。隐式类型转换字符串和数字比、字符集不一致的关联都会让索引失效。关联条件两边字段类型、字符集要统一。深分页LIMIT 1000000, 20数据库要扫过前100万行再偏移。数据量上去之后这种写法必炸。不必要的大范围过滤WHERE status IN (pending, success)且状态分布极不均衡时优化器可能认为走全表更快——这种要结合实际数据分布解决。这些模式里深分页是我最想展开的因为它最容易线上出事故。硬啃这种分页的场景很常见后台管理列表、用户中心订单列表、运营后台导出任务数据量上来之后用户翻到第几十页就明显变慢。3.3 深分页优化与并行SQL的实际边界深分页的解法行业里成熟的有两条路第一条延迟关联deferred join。先只查主键拿到需要的20个主键之后再用主键去回表取完整数据-- 优化前深分页扫了很多无用行 SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 20; -- 优化后先快速定位20个主键再回表拿数据 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 1000000, 20 ) t ON o.id t.id;子查询里只用id和create_time可以走覆盖索引索引里就有id和create_time不需要回表扫描量小很多。等拿到20个主键再回原表取完整行代价就很小了。第二条游标分页 / 键集分页。不限LIMIT偏移量而是通过WHERE create_time 上次最后一条的create_time不断往下捞。这种做法的前提是排序字段必须唯一或组合唯一否则锚点会漂移。它的写法长这样SELECT * FROM orders WHERE create_time 2024-05-01 10:00:00 ORDER BY create_time DESC, id DESC LIMIT 20;这种方式对用户翻页体验也更好每一页定位固定翻页是O(1)定位而不是从全表重新扫。代价是分页逻辑里必须记住上一页的最后一条。 至于并行sql优化我得泼点冷水并行执行不是银弹。数据库的并行度受限于CPU核数、IO能力而且在小表上并行反而更慢因为调度开销大。实际项目里我更倾向于从SQL本身下手能走索引的走索引能减少扫描量的减少扫描量。并行只是当单条SQL太重、资源够、并发不高时的选项不要一开始就急着上并行。最后提醒一下慢SQL排查不要只看单条语句要看整体负载。有时候一条SQL本身还行但每秒被调用上千次照样把数据库拖垮。优化思路要从单次成本和调用次数两个维度一起算总账这也是为什么我会建议在应用层做缓存、做读写分离而不是一味调SQL。 ## 4. SQL注入防护思路识别拼接风险与参数化底线 sql注入、sql注入万能密码绕过、dvwa sql注入、python sql注入原理这些热搜词一大片说明这个问题是做后端开发绕不开的门槛。这章我不讲攻击技巧重点讲**为什么会产生注入、以及代码层怎么防**——安全的原则是防御不是教人攻击。 ### 4.1 注入的本质用户输入变成了代码 SQL注入的根本原因一句话就能说透**你本想把用户输入当成数据拼进SQL结果它被当成了代码执行**。比如经典的低级写法 python sql SELECT * FROM users WHERE name name 如果name的值是admin OR 11拼接出来的SQL就变成了SELECT * FROM users WHERE name admin OR 11本来name只是一个值现在因为引号被转义多出来一个OR 11整个WHERE条件恒真所有用户记录全被返回。从数据库的角度看它执行的是一条合法SQL从应用的角度看它执行了开发者根本没打算执行的东西。这个问题的本质是数据和指令没有分开。安全领域反复强调的永不信任用户输入落在SQL这层就是一句话不要让用户输入以任何形式变成SQL结构的一部分——要么用参数化查询要么严格白名单校验。4.2 防御的第一道防线参数化查询参数化查询Prepared Statement的原理是先把SQL的骨架发给数据库编译编译完成后再把参数值单独传过去。因为SQL结构已经定死了参数值无论怎么拼接都只是值不可能变成新的SQL片段。以Python为例正确写法是import sqlite3 conn sqlite3.connect(app.db) cur conn.cursor() # 错误字符串拼接 # cur.execute(SELECT * FROM users WHERE name name ) # 正确参数化 cur.execute(SELECT * FROM users WHERE name ?, (name,))注意那个?占位符——数据库先编译按name查用户这个操作再把name当作一个参数绑定进去。哪怕name传的是admin OR 11数据库也只会把它当成一个字符串值去比较不存在语法变化。Java的JDBC用PreparedStatementGo的database/sql用?占位Node.js的mysql库用?或命名参数原理都一样。甚至MyBatis这类ORM框架只要用#{}占位而不是${}拼接底层走的也是预编译绑定。这里有个容易误解的点存储过程不天然防注入。如果存储过程内部仍然用字符串拼接动态SQL注入风险依然存在。真正防护靠的是参数绑定这个机制不是放进数据库这个动作。4.3 纵深防御权限最小化与数据库侧配置参数化能挡住绝大多数注入但安全不能靠单点。我还习惯做这几层防护数据库账号权限最小化应用账号只给需要的表的最低权限比如只读账号给SELECT写账号尽量不给DDL权限、不给DROP权限。即使被注入攻击面也被压缩了——它想读没得读想删也删不掉。输入校验做白名单能枚举的参数状态值、类型、排序字段坚决用枚举数值类型的参数先转成整数再进SQL分页的LIMIT参数强制整型。错误信息不要回显给用户SQL报错细节泄露表结构生产环境的异常处理要统一拦截。日志监控记录异常SQL模式比如单条SQL出现大量连词、引号、注释符号的可以走告警。还有一个被很多人忽略的点ORM不等于绝对安全。用MyBatis的${}或者用JPA原生SQL拼接查询条件照样有注入风险。框架只是工具安全的底线永远是参数和SQL结构分开这条自觉。我面试时经常问候选人一句话你的ORM框架真正执行SQL时是预编译绑定变量还是直接拼接能答清楚的至少在安全这条线上是有意识的。 提示做安全演练或测试漏洞时只在本地靶场如DVWA这类专门构建的测试环境进行绝不要在未授权系统上做任何探测。测试的目的是理解原理、加固防御不是攻击他人系统。5. 环境与工具的常见坑安装、连接、报错排查热搜词里SQL Server相关的占了一大片sql server安装教程、sql server2019安装教程、sql server2022安装教程、solidworks electrical 无法连接到 sql server、navicat for sql server激活码、sql server 2012密码到期、free download for sql server management studio还有Oracle的ora-12518、SQLite的no such column。这些都是实打实的日常环境问题我挑几个共性最强的展开。5.1 SQL Server安装与连接的常见失败点SQL Server在Windows上装起来本身不难但翻车率高在几个地方实例名和端口搞混默认实例是MSSQLSERVER端口1433命名实例的端口是动态分配的默认49152之后本地连接用机器名能连远程连就经常找不到。连接字符串里的ServerIP\\实例名写法很容易因为反斜杠在配置里被转义而翻车。防火墙挡了1433端口装了连不上八成是防火墙没放行。检查方向集中在Windows防火墙入站规则。混合认证模式没开只用Windows认证模式时用账号密码连会报登录失败需要在实例属性里切换为混合模式并启用sa账号。热搜里solidworks electrical 无法连接到 sql server这个报错其实很典型——CAD类的工具软件一般会在本机装一个SQL Server Express实例装完连不上要么是实例服务没启动要么是登录账号密码不匹配要么是防火墙把命名实例端口挡了。排查链路通常是确认SQL Server服务是否在运行services.msc里看SQL Server开头的服务。确认连接用的实例名是否写对本机命名实例一般形如主机名\\SQLEXPRESS。确认账号密码能被接受工具默认账号往往是sa但Windows认证模式下它不可用。5.2 典型连接报错的排查链路我把三类典型的连接类错误放在一起对比方便举一反三报错常见原因排查顺序SQL Server无法连接到服务器服务未启动、防火墙、实例名错先看服务再测1433再查实例名OracleORA-12518 监听程序无法分发监听服务满、进程数超限、连接数爆了查lsnrctl status查进程数查v$process/v$sessionSQLiteno such column: test_url表结构里根本没有这个字段先确认PRAGMA table_info(表名)别想当然SQLite那个no such column我多说一句。它跟数据库主机类报错不同问题往往出在代码侧——建表语句和查询语句字段名不一致。最常见的原因是迁移脚本没执行或者迁移脚本和实际代码不是同一份。排查时先跑一下PRAGMA table_info(表名)看真实字段再回头看代码或迁移脚本里字段名大小写和拼写。SQLite对字段名大小写不敏感但对拼写差异敏感test_url和testUrl这种驼峰命名不一致也是高频翻车点。连接类的排查思路我的习惯永远是自底向上从服务有没有起来→端口能不能通→账号密码对不对→SQL语句本身对不对每一层都要先确认确实没问题再往上走。大部分连不上其实是第一层的问题大部分连上但报错其实是第四层的问题。按这个顺序排查效率高很多。5.3 客户端工具的选型与长SQL调试经验工具这块我在不同项目里都折腾过最终结论是没有全能神器按场景换工具。SQL Server场景官方SSMS功能最全尤其图形化执行计划、索引调优建议这两个功能SQL Server用户必备。下载时认准官方渠道不要搜激活码之类的东西——SSMS本身就是免费的不需要激活搜索引擎里那些带激活码的下载站反而是捆绑风险最大的坑。MySQL场景Navicat和DBeaver都不错。前者对新手友好但它是商业付费软件后者开源免费跨平台连接类型支持多。各有取舍。Oracle场景PL/SQL Developer是老牌工具但首次配置Oracle客户端会很劝退用DBeaver连接Oracle也能凑合复杂调试体验略差。SQLite场景DB Browser for SQLite就够用了轻量、免费、直接看表结构。调试长SQL是我个人特别有体会的一点。SQL一长嵌套子查询一多人眼根本排不出版本差异。我的做法是从上往下拆先跑最内层的子查询确认结果集对再一层层往外包。比如一个三层嵌套的报表SQL我会先把最内层SELECT单独跑一遍看数据量级和字段是否符合预期再用这段子查询作为下一层的FROM数据源去调试。每层都验证完毕整条SQL基本不会出错。我还习惯在调试时把WHERE条件先去掉或者LIMIT给个几十条先把结构和字段逻辑跑通再逐步加上过滤条件验证结果变化。SQL里最让人头疼的不是语法错误而是语法对、逻辑错——结果数字看起来合理实际因为某个关联字段重复导致翻倍了。这种时候就是用前面说的先验关联键唯一性的方法一步步排查。另外SQL Server 2012密码到期这类问题本质就是策略问题ALTER LOGIN sa WITH CHECK_POLICY OFF可以临时关掉密码策略但生产环境更推荐的做法是设置定期改密流程别为了省事把安全基线整个关掉。cmd导出sql、dm数据迁移工具导入.sql这些场景本质是文件编码和分隔符的问题——导出的SQL文件如果是UTF-8带BOM导入到某些数据库客户端会多出一个不可见字符直接报语法错误。遇到导入.sql报错先检查文件编码再用十六进制看一眼文件头很多问题瞬间就有答案了。聊到最后说点个人的经验吧。我从刚入行时只会SELECT *到现在能在一分钟内判断一条SQL该走索引还是该改写法唯一的分水岭就是把上面的每一层都想明白了数据的形态、执行的顺序、代价的来源。工具和语法永远在变但思路这件事一旦建立后面换数据库、换ORM、换项目都不虚。你现在要做的就是拿起手边那条曾经让你头疼的SQL按这篇的思路重新拆一遍看能不能写出更干净、更快的版本。