05.4.2.3 T-SQL 编程与优化实战
所属:全栈开发面试题库 / 05 数据库与存储 / 05.4 SQL Server / 05.4.2 T-SQL 与执行计划
覆盖范围
- 批处理、事务边界、变量与表类型作用域
- 临时表/表变量/CTE 选择、窗口函数与递归 CTE
- JOIN、NULL 三值逻辑、IN/EXISTS、APPLY
- 分页、批量写入、集合化改写、游标替代
- 排序规则、类型转换、计算列索引、动态 SQL 与注入
- 事务与异常处理、约束、IDENTITY/SEQUENCE、复盘模板
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.4.2.3-003 | 临时表/表变量/CTE 怎么选 | 进阶 | ★★★★★ |
| 05.4.2.3-004 | 窗口函数与递归 CTE | 进阶 | ★★★★☆ |
| 05.4.2.3-005 | NULL 三值逻辑与 IN/EXISTS | 进阶 | ★★★★☆ |
| 05.4.2.3-006 | 分页写法与深分页优化 | 进阶 | ★★★★☆ |
| 05.4.2.3-007 | 批量写入与 TVP | 高级 | ★★★★☆ |
| 05.4.2.3-008 | 游标/逐行循环的代价 | 进阶 | ★★★★★ |
| 05.4.2.3-013 | 短事务与 XACT_ABORT | 进阶 | ★★★★☆ |
| 05.4.2.3-015 | 动态 SQL 与注入风险 | 高级 | ★★★★☆ |
编程优化主线
集合化优先:用 JOIN/聚合/批量语句替代逐行循环
减少往返:批量写入、TVP、避免循环单条提交
计划友好:谓词不包函数、类型一致、分页走索引
事务短小:边界内只做必要数据操作,错误可回滚
05.4.2.3-001 批处理、GO 与事务边界是什么关系?
频率 ★★☆☆☆ | 难度 基础
一句话结论
GO 是客户端批处理分隔符,把一批语句发给引擎;事务由 BEGIN TRANSACTION/COMMIT 界定,跨越多个批处理时需显式保持连接。
概念解释
- 批处理(Batch):一次提交给引擎的语句组,
GO标记结束。 - 事务边界:
BEGIN TRAN到COMMIT/ROLLBACK,可跨批处理但连接不能断。 - 批处理内语法/编译错误影响整批执行计划。
原理流程
GO 拆分批 → 每批独立编译执行
事务显式开启 → 跨语句/批累积 → 显式提交或回滚
代码 / 命令
BEGIN TRANSACTION;
UPDATE dbo.Orders SET Status = 1 WHERE Id = 1;
GO -- 注意:GO 后事务仍未提交(连接保持)
COMMIT TRANSACTION;
常见误区
- 误区:
GO提交事务。正解:GO与事务无关,只是批处理分隔。 - 误区:存储过程内每条语句自动提交。正解:过程内显式事务按边界提交,隐式提交模式另算。
追问
- 问:隐式事务模式是什么?答:
IMPLICIT_TRANSACTIONS ON时写语句自动开事务,需显式提交。 - 问:批处理编译错误会怎样?答:整批不执行;运行时错误影响视语句与错误处理而定。
05.4.2.3-002 变量、参数和表类型的作用域是什么?
频率 ★★☆☆☆ | 难度 基础
一句话结论
局部变量在声明它的批处理/过程内有效;存储过程参数仅过程内可见;表类型(用户定义表类型)用于参数化传表。
概念解释
- 局部变量:
DECLARE @x,批处理结束释放。 - 过程参数:输入/输出参数,过程退出消失。
- 表类型:预定义结构,可作为过程参数传多行数据。
原理流程
批处理/过程 → 声明变量/接收参数 → 内部使用 → 退出释放
代码 / 命令
CREATE TYPE OrderList AS TABLE (Id INT, Amount MONEY);
GO
CREATE PROCEDURE dbo.UpsertOrders @Rows OrderList READONLY AS
BEGIN
MERGE dbo.Orders o USING @Rows r ON o.Id = r.Id
WHEN MATCHED THEN UPDATE SET o.Amount = r.Amount;
END;
常见误区
- 误区:表类型参数可在过程内修改。正解:默认
READONLY,不能写。 - 误区:变量在不同批处理间保留。正解:跨批需会话级对象或临时表。
追问
- 问:表类型与临时表参数怎么选?答:传多行给过程用 TVP;过程内部中间结果用临时表。
- 问:输出参数怎么声明?答:
@p INT OUTPUT,调用时带OUTPUT。
05.4.2.3-003 临时表、表变量、CTE 怎么选?
频率 ★★★★★ | 难度 进阶
一句话结论
大数据量、需索引/多次过滤用临时表;小数据集单次流转用表变量;一次性中间结果用 CTE。
概念解释
| 维度 | 临时表 | 表变量 | CTE |
|---|---|---|---|
| 统计信息 | 有 | 基本无 | 无物化,内联展开 |
| 数据量 | 中大 | 小 | 单次查询内 |
| 索引 | 可建 | 仅声明约束 | 不支持 |
| 复用 | 过程/会话内 | 批处理内 | 仅当前语句 |
| 事务 | 参与回滚 | 行为受限 | 随主查询 |
原理流程
中间结果大、要 JOIN/聚合 → 临时表
几行数据传递 → 表变量
简化单条语句层级 → CTE
代码 / 命令
-- CTE:一次性引用
WITH Recent AS (
SELECT CustomerId, MAX(CreateTime) AS LastTime
FROM dbo.Orders GROUP BY CustomerId
)
SELECT * FROM Recent WHERE LastTime > '2026-01-01';
-- 临时表:多次使用
CREATE TABLE #Agg (CustomerId INT, Total MONEY);
INSERT INTO #Agg SELECT CustomerId, SUM(Amount) FROM dbo.Orders GROUP BY CustomerId;
常见误区
- 误区:CTE 会物化一张临时表。正解:多数情况下 CTE 内联到主查询,不产生独立表。
- 误区:表变量天然更快。正解:无统计信息,大数据量易出差计划。
追问
- 问:CTE 可以自引用吗?答:可以,递归 CTE 用
UNION ALL引用自身(见 05.4.2.3-004)。 - 问:临时表统计信息何时生成?答:创建后插入数据,优化器按其统计估算。
05.4.2.3-004 窗口函数和递归 CTE 怎么用?
频率 ★★★★☆ | 难度 进阶
一句话结论
窗口函数在不折叠行的前提下做分组排名/聚合;递归 CTE 用于层级与递推数据(组织架构、菜单、账单期数)。
概念解释
- 窗口函数:
ROW_NUMBER/RANK/DENSE_RANK、SUM/AVG OVER,PARTITION BY分组、ORDER BY排序。 - 递归 CTE:锚点成员 + 递归成员,引用自身,设最大递归层数。
原理流程
窗口:行集 → 分区 → 排序 → 窗口计算 → 保留原行
递归:锚点 → 逐层关联自身 → 直到无新行
代码 / 命令
-- 每组按金额排名
SELECT CustomerId, Amount,
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY Amount DESC) AS rn
FROM dbo.Orders;
-- 递归 CTE:员工层级
WITH Org AS (
SELECT Id, ManagerId, Name, 1 AS Level
FROM dbo.Emp WHERE ManagerId IS NULL
UNION ALL
SELECT e.Id, e.ManagerId, e.Name, o.Level + 1
FROM dbo.Emp e JOIN Org o ON e.ManagerId = o.Id
)
SELECT * FROM Org OPTION (MAXRECURSION 100);
常见误区
- 误区:窗口函数和 GROUP BY 一样折叠行。正解:窗口计算保留每行,聚合结果挂在行上。
- 误区:递归 CTE 不设层数限制安全。正解:异常环会耗尽递归,需
MAXRECURSION上限与数据校验。
追问
- 问:
RANK与DENSE_RANK区别?答:并列后跳号 vs 不跳号。 - 问:窗口排序能用索引序吗?答:分区排序列有对应索引可避免 Sort 算子。
05.4.2.3-005 NULL 三值逻辑下 JOIN、IN、EXISTS 有什么坑?
频率 ★★★★☆ | 难度 进阶
一句话结论
SQL 中 NULL = NULL 结果为 UNKNOWN,WHERE 只接受 TRUE;IN 列表含 NULL 时可能匹配不到,涉及 NULL 应显式 IS NULL。
概念解释
- 三值逻辑:TRUE / FALSE / UNKNOWN;UNKNOWN 不被 WHERE 选中。
- IN + NULL:
x IN (1, NULL)等价x=1 OR x=NULL,含 UNKNOWN 分支。 - JOIN NULL:连接键为 NULL 的行不匹配。
原理流程
WHERE Col = NULL → UNKNOWN → 不返回
WHERE Col IS NULL → 正确判断
代码 / 命令
-- 反例:永远查不到 NULL
SELECT * FROM dbo.Users WHERE Phone = NULL;
-- 正例
SELECT * FROM dbo.Users WHERE Phone IS NULL;
-- NOT IN 子查询含 NULL 时结果为空
SELECT * FROM dbo.Orders o WHERE o.Id NOT IN (SELECT OrderId FROM dbo.Refund WHERE OrderId IS NOT NULL);
常见误区
- 误区:
NOT IN比NOT EXISTS通用。正解:子查询含 NULL 时NOT IN整体为空,NOT EXISTS更稳。 - 误区:外连接未匹配行的列是 0 或空串。正解:未匹配列为 NULL。
追问
- 问:
COUNT(Col)与COUNT(*)区别?答:前者忽略 NULL,后者统计全部行。 - 问:唯一索引允许多个 NULL 吗?答:SQL Server 默认只允许一个 NULL,见 05.4.1.2-009。
05.4.2.3-006 分页怎么写?深分页怎么优化?
频率 ★★★★☆ | 难度 进阶
一句话结论
SQL Server 用 OFFSET ... FETCH NEXT 或 ROW_NUMBER() 分页;深分页需按键续传/游标,避免大偏移扫描丢弃大量行。
概念解释
- OFFSET/FETCH:标准分页语法,依赖排序列稳定有序。
- ROW_NUMBER:窗口函数编号后按页号过滤。
- 深分页:大偏移仍先扫描前 N 行再丢弃。
原理流程
OFFSET 100000 ROWS FETCH NEXT 20 ROWS ONLY
→ 扫描跳过 10 万行 → 取 20 行
键续传:WHERE Id > 上一页最后 Id ORDER BY Id LIMIT 20
代码 / 命令
-- 标准分页
SELECT Id, Amount
FROM dbo.Orders
ORDER BY CreateTime DESC, Id DESC
OFFSET 20000 ROWS FETCH NEXT 20 ROWS ONLY;
-- 键续传(记住上一页最后键)
SELECT Id, Amount
FROM dbo.Orders
WHERE CreateTime < @lastTime OR (CreateTime = @lastTime AND Id < @lastId)
ORDER BY CreateTime DESC, Id DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
常见误区
- 误区:分页不指定 ORDER BY 也稳定。正解:无确定排序,跨页可能重复/漏行。
- 误区:键续传支持跳页。正解:只能向前翻,适合"下一页"流。
追问
- 问:排序列重复值怎么保证稳定?答:加唯一列(Id)作为第二排序键打破平局。
- 问:深分页为什么慢?答:大偏移扫描+丢弃,回表时代价叠加。
05.4.2.3-007 批量写入有哪些方式?怎么控制批量大小?
频率 ★★★★☆ | 难度 高级
一句话结论
批量写入可用 BULK INSERT、多行 INSERT、表类型参数(TVP)或 MERGE;大事务分批提交,控制日志与锁规模。
概念解释
- BULK INSERT / 批量导入:高速装载外部数据。
- TVP:客户端传多行集合,过程内集合化处理。
- 分批提交:大表按批循环插入/更新,每批独立事务。
原理流程
单条循环提交 → 每行事务 + 日志往返,慢
批量/TVP → 集合化写入,减少往返
超大批量 → 分批提交,控制锁与日志
代码 / 命令
-- TVP 批量 upsert
MERGE dbo.Orders o
USING @Rows r ON o.Id = r.Id
WHEN MATCHED THEN UPDATE SET o.Amount = r.Amount
WHEN NOT MATCHED THEN INSERT (Id, Amount) VALUES (r.Id, r.Amount);
-- 分批更新示例(每批 1000 行)
WHILE 1 = 1 BEGIN
UPDATE TOP (1000) dbo.Orders SET Flag = 1
WHERE Flag = 0;
IF @@ROWCOUNT = 0 BREAK;
END
常见误区
- 误区:一次性导入百万行最快。正解:超大单事务日志与锁规模大,分批更稳。
- 误区:TVP 内部仍逐行处理。正解:TVP 是集合,可配合集合语句,不要在过程内逐行循环 TVP。
追问
- 问:批量导入日志量受什么影响?答:恢复模式、批量最小记录选项、索引数量。
- 问:
MERGE有什么注意点?答:末尾分号、重复匹配风险、谓词唯一性需核对。
05.4.2.3-008 为什么要避免游标和逐行循环?
频率 ★★★★★ | 难度 进阶
一句话结论
逐行处理每行都有 fetch/评估/日志开销,丧失集合化批量优势;应优先用集合语句、批量更新或基于游标的集合方案替代。
概念解释
- 游标开销:逐行定位、逐行写、多次事务日志往返。
- 集合化:引擎按批量优化扫描、排序与写入。
- 少数场景(复杂逐行逻辑)才考虑循环,且应缩小范围。
原理流程
反例:游标循环逐行 UPDATE → N 次定位 + N 次日志
正例:UPDATE ... WHERE 条件 → 集合一次完成
代码 / 命令
-- 反例(示意):逐行更新
DECLARE cur CURSOR FOR SELECT Id FROM dbo.Orders WHERE Flag = 0;
OPEN cur; FETCH NEXT INTO @id;
WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE dbo.Orders SET Flag = 1 WHERE Id = @id;
FETCH NEXT INTO @id;
END
CLOSE cur; DEALLOCATE cur;
-- 正例:集合更新
UPDATE dbo.Orders SET Flag = 1 WHERE Flag = 0;
常见误区
- 误区:循环写更容易控制错误。正解:集合语句配合事务与错误处理同样可控,且快得多。
- 误区:
WHILE循环比游标高级。正解:逐行WHILE同样有逐行开销,本质相同。
追问
- 问:什么情况下允许逐行?答:每行依赖外部调用或复杂分支逻辑,且数据量可控。
- 问:逐行批处理怎么降开销?答:合并为小批量集合语句,分批提交。
05.4.2.3-009 排序规则(Collation)对查询有什么影响?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
排序规则决定字符比较的大小写、口音、全半角敏感与排序顺序;JOIN/比较两侧排序规则不一致会引发隐式转换或报错。
概念解释
- 大小写敏感:CS/CI;口音敏感:AS/AI。
- 数据库/列级排序规则:列可覆盖库级设置。
- 连接两表字符串列排序规则不同,比较时需
COLLATE。
原理流程
比较两侧排序规则不一致 → 隐式转换 / 排序规则冲突错误
代码 / 命令
-- 查询时指定排序规则
SELECT * FROM dbo.Users
WHERE Name COLLATE Latin1_General_CS_AS = @name;
-- 查看列排序规则
SELECT name, collation_name FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.Users');
常见误区
- 误区:所有中文库排序规则相同。正解:排序规则影响比较与排序,混用需核对。
- 误区:排序规则只影响显示。正解:直接影响索引可用与比较结果。
追问
- 问:排序规则冲突报错怎么解?答:比较侧加
COLLATE对齐,或统一列/库排序规则。 - 问:CI 与 CS 哪个常见?答:多数业务库 CI(大小写不敏感),按需求选择。
05.4.2.3-010 类型转换与隐式转换要注意什么?
频率 ★★★☆☆ | 难度 进阶
一句话结论
显式 CAST/CONVERT 可控;隐式转换发生在列侧会破坏索引,比较前应让参数类型与列一致。
概念解释
- 显式转换:代码写明
CAST,位置可控。 - 隐式转换:类型优先级驱动,计划中表现为
CONVERT_IMPLICIT。 - 日期、字符串、数字之间转换最易出问题。
原理流程
列类型 varchar,参数 int → 列被转换 → 索引失效
代码 / 命令
-- 显式转换
SELECT CAST(Amount AS INT) AS AmountInt FROM dbo.Orders;
-- 避免列侧隐式转换
SELECT * FROM dbo.Users WHERE Phone = @phone; -- @phone 应为 varchar
常见误区
- 误区:隐式转换不影响结果只影响性能。正解:时区/精度/截断还可能影响正确性。
- 误区:
CONVERT风格参数无所谓。正解:日期格式风格号决定输出格式,误用会解析错误。
追问
- 问:怎么识别列侧转换?答:计划谓词中
CONVERT_IMPLICIT作用于列表达式。 - 问:金额用什么类型?答:
MONEY/DECIMAL,避免浮点误差。
05.4.2.3-011 列上用函数为什么走不了索引?计算列加索引怎么做?
频率 ★★★★☆ | 难度 进阶
一句话结论
索引存原值,列被函数包裹后无法按键定位;可改为范围条件,或建持久化计算列并对其建索引。
概念解释
- 函数包裹:
DATE(CreateTime)=...、LOWER(Email)=...破坏 Seek。 - 计算列:持久化列存表达式结果,可建索引。
- 索引匹配:查询表达式与计算列定义一致才能用上。
原理流程
反例:WHERE DATE(CreateTime) = '2026-01-01'
正例:WHERE CreateTime >= '2026-01-01' AND CreateTime < '2026-01-02'
代码 / 命令
-- 计算列 + 索引
ALTER TABLE dbo.Users ADD EmailLower AS LOWER(Email) PERSISTED;
CREATE NONCLUSTERED INDEX IX_EmailLower ON dbo.Users (EmailLower);
-- 查询需使用同一表达式
SELECT * FROM dbo.Users WHERE EmailLower = LOWER(@email);
常见误区
- 误区:函数索引可以直接在函数上建。正解:SQL Server 用计算列方案;PG 有表达式索引。
- 误区:范围改写总能替代函数。正解:非日期类函数需计算列或调整数据模型。
追问
- 问:非持久化计算列能建索引吗?答:索引可引用,但持久化更利于存储与维护(按版本核对)。
- 问:表达式不一致会怎样?答:优化器无法匹配计算列索引,仍走扫描。
05.4.2.3-012 相关子查询和 APPLY 算子怎么用?
频率 ★★★☆☆ | 难度 高级
一句话结论
相关子查询逐外层行引用内层;CROSS APPLY/OUTER APPLY 把表值函数/子查询按行关联到外层,类似 JOIN 但支持按行传参。
概念解释
- 相关子查询:内层引用外层列,逐行执行。
- CROSS APPLY:内层返回行才保留外层行(内连接语义)。
- OUTER APPLY:内层无返回也保留外层行(左连接语义)。
原理流程
外层每行 → 传入子查询/表值函数 → 匹配结果拼接
代码 / 命令
-- 取每个客户最新一笔订单
SELECT c.Name, o.Amount, o.CreateTime
FROM dbo.Customers c
OUTER APPLY (
SELECT TOP 1 Amount, CreateTime
FROM dbo.Orders o WHERE o.CustomerId = c.Id
ORDER BY CreateTime DESC
) o;
常见误区
- 误区:APPLY 就是普通 JOIN。正解:它支持按外层行参数化内层,JOIN 条件不能传递行内参数。
- 误区:相关子查询一定比 JOIN 慢。正解:看优化器展开与索引,低效时改写为 JOIN/APPLY。
追问
- 问:CROSS APPLY 与 INNER JOIN 区别?答:APPLY 可关联表值函数/按行子查询。
- 问:
TOP 1排序无索引会怎样?答:每组排序成本高,需对应索引支撑。
05.4.2.3-013 事务编写有哪些原则?XACT_ABORT 起什么作用?
频率 ★★★★☆ | 难度 进阶
一句话结论
事务尽量短小,边界内只做必要数据操作;XACT_ABORT ON 让运行时错误中止批处理并回滚事务,避免部分提交。
概念解释
- 短事务:减少持锁时间与版本滞留。
- XACT_ABORT:
ON时语句严重错误终止批处理并回滚当前事务。 - 避免事务内交互:外部调用、等待、慢速查询都应移出事务。
原理流程
BEGIN TRAN → 必要读写 → COMMIT
错误 → CATCH 中 ROLLBACK
代码 / 命令
SET XACT_ABORT ON;
BEGIN TRANSACTION;
BEGIN TRY
UPDATE dbo.Stock SET Qty = Qty - 1 WHERE Sku = @sku AND Qty > 0;
IF @@ROWCOUNT = 0 THROW 50001, N'库存不足', 1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
常见误区
- 误区:事务里调用外部接口能保证一致性。正解:长事务持锁等待外部响应,应先提交再异步补偿。
- 误区:
XACT_ABORT ON回滚所有错误。正解:部分错误类型不触发回滚,需结合 CATCH 与XACT_STATE()判断。
追问
- 问:
XACT_STATE()返回什么?答:1 可提交,-1 不可提交需回滚,0 无事务。 - 问:嵌套事务以什么为准?答:@@TRANCOUNT 计数,最外层提交才真正提交;嵌套 ROLLBACK 会回滚全部。
05.4.2.3-014 TRY/CATCH 能处理哪些错误?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
TRY/CATCH 捕获多数运行时错误,CATCH 中可取错误号、消息、行号;编译错误与严重级错误不被捕获。
概念解释
- 错误函数:
ERROR_NUMBER()、ERROR_MESSAGE()、ERROR_LINE()、ERROR_PROCEDURE()。 - 未捕获:语法编译错误、连接中断、部分严重级终止错误。
- 重抛:
THROW重新抛出当前错误。
原理流程
TRY 块出错 → 跳到 CATCH → 记录/回滚 → THROW 或降级处理
代码 / 命令
BEGIN TRY
INSERT INTO dbo.Orders (Id) VALUES (1);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrNo, ERROR_MESSAGE() AS Msg, ERROR_LINE() AS Line;
THROW;
END CATCH;
常见误区
- 误区:TRY/CATCH 能捕获编译期语法错误。正解:编译错误在批处理解析阶段,不进入 CATCH。
- 误区:CATCH 里可以直接继续原事务。正解:
XACT_STATE()=-1时必须先回滚。
追问
- 问:
RAISERROR与THROW区别?答:THROW更简洁,默认重抛当前错误;RAISERROR支持旧式格式与严重级。 - 问:错误记录落库放哪?答:CATCH 内写日志表,注意自身事务回滚不影响日志(独立事务/表)。
05.4.2.3-015 动态 SQL 怎么写才安全?
频率 ★★★★☆ | 难度 高级
一句话结论
动态 SQL 用 sp_executesql 配合参数化,避免字符串拼接用户输入;必须拼接的标识符用白名单校验。
概念解释
- 注入风险:拼接用户输入可被改写语句。
- 参数化:动态语句内定义参数,传入值不拼接。
- 标识符拼接:表名/列名不能参数化,需校验白名单并正确引号转义。
原理流程
值 → 参数传入(防注入)
标识符 → 白名单校验后拼接
代码 / 命令
-- 安全:值参数化
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM dbo.Orders WHERE Status = @p';
EXEC sp_executesql @sql, N'@p INT', @p = @Status;
-- 标识符白名单(示例)
DECLARE @col NVARCHAR(128) = QUOTENAME(@colName);
IF @colName NOT IN (N'Status', N'Amount') THROW 50002, N'非法列名', 1;
常见误区
- 误区:
sp_executesql本身防注入。正解:它只是执行器,仍需参数化;拼接输入照样注入。 - 误区:动态 SQL 完全不能用。正解:排序列/表名等场景需要,按白名单与参数化约束使用。
追问
- 问:动态 SQL 的计划能复用吗?答:参数化后语句模板一致可复用;字面量拼接则各自缓存。
- 问:
QUOTENAME作用?答:给标识符加方括号并转义内部括号。
05.4.2.3-016 有哪些常见的慢 SQL 写法?
频率 ★★★★☆ | 难度 进阶
一句话结论
常见反模式:SELECT *、大事务、嵌套相关子查询、过度 JOIN、列上函数、无谓词全扫、逐行循环、深分页无索引。
概念解释
- 宽查询:取不需要的列放大 IO 与回表。
- 相关子查询:逐行执行,可改写 JOIN/APPLY。
- 缺谓词/弱谓词:扫描范围过大。
原理流程
慢写法 → 计划扫描/回表/排序/spill → 高逻辑读与等待
改写 → 收敛范围、覆盖索引、集合化
代码 / 命令
-- 反例
SELECT * FROM dbo.Orders WHERE YEAR(CreateTime) = 2026;
-- 正例
SELECT Id, Amount FROM dbo.Orders
WHERE CreateTime >= '2026-01-01' AND CreateTime < '2027-01-01';
常见误区
- 误区:慢 SQL 都靠加索引解决。正解:先修正写法与谓词,再评估索引。
- 误区:JOIN 越多越慢。正解:关键是连接列索引与结果集大小。
追问
- 问:怎么系统收集慢写法?答:Query Store/慢查询统计 + 实际计划逐类归档。
- 问:
SELECT *的额外代价?答:宽行 IO、无法覆盖索引、传输冗余数据。
05.4.2.3-017 什么是索引视图(Indexed View)?
频率 ★★☆☆☆ | 难度 高级
一句话结论
索引视图是对视图结果持久化建索引,相当于物化预计算结果;查询匹配视图定义时可直接使用,维护成本随底层写入增加。
概念解释
- 普通视图:存储查询定义,执行时展开。
- 索引视图:视图结果持久化,其上建唯一聚集索引。
- 适用:聚合多、更新少的报表场景。
原理流程
底层表变更 → 同步维护视图索引 → 查询命中视图索引避免重复计算
代码 / 命令
CREATE VIEW dbo.OrderDailyTotal WITH SCHEMABINDING AS
SELECT CustomerId, SUM(Amount) AS Total, COUNT_BIG(*) AS Cnt
FROM dbo.Orders GROUP BY CustomerId;
GO
CREATE UNIQUE CLUSTERED INDEX IX_Vw_Daily ON dbo.OrderDailyTotal (CustomerId);
常见误区
- 误区:索引视图会自动被任意查询使用。正解:查询需匹配视图定义,部分场景受版本与绑定选项限制。
- 误区:索引视图免费提速。正解:底层每次写入都要维护视图索引,写放大明显。
追问
- 问:
SCHEMABINDING作用?答:绑定底层对象结构,防止列/表被随意改动。 - 问:与 PG 物化视图区别?答:PG 物化视图需手动刷新;SQL Server 索引视图随底层数据自动维护。
05.4.2.3-018 约束对性能有什么影响?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
主键/唯一约束保证数据完整性并附带索引;外键、CHECK、DEFAULT 在写入时增加校验成本,但避免脏数据,应按业务保留。
概念解释
- 主键/唯一:自动建索引,加速等值查询。
- 外键:写入时校验关联存在性,级联动作有成本。
- CHECK/DEFAULT:写入时校验/填默认值。
原理流程
写入 → 约束校验(唯一/外键/CHECK)→ 提交
代码 / 命令
ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Customer
FOREIGN KEY (CustomerId) REFERENCES dbo.Customers (Id);
常见误区
- 误区:为性能关掉外键约束。正解:应用层难以保证引用完整,应保留并在批量导入时评估顺序。
- 误区:约束只在插入时检查。正解:更新关联列、删除被引用行同样触发校验。
追问
- 问:外键会阻止删除主表行吗?答:存在引用行时阻止,除非级联删除配置。
- 问:DEFAULT 有写入代价吗?答:很小;列默认值在插入时填充。
05.4.2.3-019 IDENTITY 和 SEQUENCE 有什么区别?
频率 ★★★☆☆ | 难度 进阶
一句话结论
IDENTITY 是表列属性,随行插入自增;SEQUENCE 是独立对象,可被多表共享,支持循环与缓存配置。
概念解释
- IDENTITY:绑定列,插入时自动分配,不可直接写值(显式插入需开关)。
- SEQUENCE:独立序列对象,
NEXT VALUE FOR取值,多表共用。 - 两者都不保证连续(回滚、并发、缓存空洞)。
原理流程
IDENTITY:表插入 → 列自动取下一序号
SEQUENCE:任意处 NEXT VALUE FOR → 取序列值
代码 / 命令
CREATE SEQUENCE dbo.OrderSeq START WITH 1 INCREMENT BY 1 CACHE 100;
CREATE TABLE dbo.Orders (
Id BIGINT PRIMARY KEY DEFAULT NEXT VALUE FOR dbo.OrderSeq,
Amount MONEY
);
-- 查看当前序列值
SELECT current_value FROM sys.sequences WHERE name = N'OrderSeq';
常见误区
- 误区:自增值连续无空洞。正解:回滚、并发分配、序列缓存都会产生空洞。
- 误区:IDENTITY 可跨表共享。正解:它属于表列;跨表发号用 SEQUENCE 或外部发号器。
追问
- 问:序列缓存有什么权衡?答:缓存减少访问次数,实例重启可能丢失缓存内序号。
- 问:重置 IDENTITY 怎么做?答:
DBCC CHECKIDENT重置;生产环境谨慎避免主键冲突。
05.4.2.3-020 EXECUTE AS 与执行上下文是什么?
频率 ★★☆☆☆ | 难度 高级
一句话结论
EXECUTE AS 切换模块/会话的执行安全上下文,用于权限委派;需控制链路权限,避免过度授权。
概念解释
- 执行上下文:当前身份与权限集合。
- 模块级 EXECUTE AS:存储过程/函数以指定身份执行。
- 模拟与还原:
REVERT回到原身份。
原理流程
调用者权限不足 → 模块以授权身份执行 → 内部操作 → 还原上下文
代码 / 命令
CREATE PROCEDURE dbo.DoAdminThing
WITH EXECUTE AS N'AdminUser' AS
BEGIN
-- 以 AdminUser 权限执行
END;
常见误区
- 误区:
EXECUTE AS让调用者获得模块内所有权限。正解:按指定身份权限执行,需评估权限暴露面。 - 误区:切换后自动还原。正解:会话级模拟需
REVERT;模块级在退出时自动还原。
追问
- 问:权限委派为什么不用直接授调用者?答:最小权限原则,只在模块内提升权限。
- 问:
EXECUTE AS LOGIN与USER区别?答:分别模拟登录级与库级用户,按作用域选择。
05.4.2.3-021 给出一个慢查询复盘模板。
频率 ★★★☆☆ | 难度 进阶
一句话结论
复盘按"现象 → 抓取 → 计划分析 → 根因 → 处置 → 复测 → 防复发"组织,结论可核对、可归档。
概念解释
- 现象:耗时、影响范围、发生时间与频率。
- 证据:语句文本、实际计划、IO/时间、等待类型、行数偏差。
- 根因:缺索引/统计过期/参数嗅探/写法反模式/资源压力。
- 处置与复测:改动前后对比逻辑读、耗时与计划;记录防复发措施。
原理流程
1. 记录现象与业务影响
2. 抓取语句、计划、STATISTICS IO/TIME、等待
3. 定位高成本算子与偏差
4. 归因并选择索引/改写/统计/计划干预
5. 低峰实施并复测对比
6. 归档案例,纳入监控告警
代码 / 命令
-- 复盘采集模板
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- 执行问题语句
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
-- 等待与计划关联
SELECT r.session_id, r.wait_type, r.wait_time, t.text
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = <spid>;
常见误区
- 误区:复盘只写"加了索引变快"。正解:需记录根因、改动前后指标与未覆盖的风险。
- 误区:复测在缓存热状态下进行即可。正解:兼顾冷/热缓存与真实数据量对比。
追问
- 问:复盘结论怎么沉淀?答:归档到题库/运维文档,标注场景、根因与适用条件。
- 问:防复发手段有哪些?答:索引评审、慢查询监控、QS 计划回归告警、统计周期维护。
参考来源
- Microsoft Learn:T-SQL 参考:https://learn.microsoft.com/sql/t-sql/language-reference
- Microsoft Learn:查询优化建议:https://learn.microsoft.com/sql/relational-databases/performance/query-optimization
- Microsoft Learn:动态 SQL 与安全:https://learn.microsoft.com/sql/relational-databases/security/dynamic-sql
- Microsoft Learn:序列与标识:https://learn.microsoft.com/sql/relational-databases/sequence-numbers/sequence-numbers