SQL Server 面试题

一、索引（Index）

1. 聚集索引（Clustered Index）和非聚集索引（Non-Clustered Index）的根本区别是什么？

特性	聚集索引	非聚集索引
数据存储方式	表数据按索引键物理排序存储，索引即数据。	索引结构独立于数据存储，叶子节点包含指向数据行的指针（书签）。
数量限制	每个表只能有 1 个。	最多可有 999 个（SQL Server 企业版）。
插入/更新开销	高（可能引发页分裂）。	相对较低（但需维护索引树）。
主键默认	是，主键自动创建聚集索引（除非指定 NONCLUSTERED）。	否。

2. 什么是“书签查找”（Bookmark Lookup / Key Lookup）？如何优化？

定义：当查询使用非聚集索引定位行，但该索引不包含查询所需的所有列时，SQL Server 必须回到堆或聚集索引中查找剩余列，这个过程叫书签查找。
性能影响：随机 I/O，性能差，尤其在大结果集上。

优化方法：

创建覆盖索引（Covering Index）：

  -- 查询
  SELECT Name, Email FROM Users WHERE Dept = 'IT';
  -- 优化：将所有用到的列都包含进来
  CREATE NONCLUSTERED INDEX IX_Users_Dept ON Users(Dept) INCLUDE (Name, Email);

使用包含列（INCLUDE）：避免将大字段放入索引键，减少索引大小。
减少 SELECT *：只查必要字段。

3. 什么情况下索引会失效？举出至少 5 个例子。

索引失效意味着优化器无法使用索引进行 Seek，只能 Scan 或忽略索引。常见场景：

函数包装：

SELECT * FROM Users WHERE YEAR(CreateDate) = 2023; -- 无法使用 CreateDate 索引
-- 应改为：
SELECT * FROM Users WHERE CreateDate >= '2023-01-01' AND CreateDate < '2024-01-01';

隐式类型转换：

-- 字符串 vs 整数
SELECT * FROM Orders WHERE OrderID = '12345'; -- 若 OrderID 是 INT，会转换，可能失效

前导通配符模糊查询：

SELECT * FROM Users WHERE Name LIKE '%John%'; -- 无法使用索引 Seek

OR 条件导致索引合并（Index Union）或扫描：

SELECT * FROM Users WHERE Dept='IT' OR Age > 30; -- 可能全表扫描

IS NULL / IS NOT NULL 在某些情况下：

如果统计信息显示 NULL 值很多，优化器可能选择扫描。

二、锁（Lock）

共享锁（S）、排他锁（X）、更新锁（U）的区别？

锁类型	兼容性	使用场景
共享锁（S）	✅ 与 S 兼容	❌ 与 X/U 不兼容 SELECT 操作，允许多个事务并发读取。
排他锁（X）	❌ 与其他所有锁不兼容	INSERT, UPDATE, DELETE，确保独占访问。
更新锁（U）	✅ 与 S 兼容	❌ 与 X/U 不兼容 UPDATE 的第一阶段（定位行），防止死锁。

更新锁作用：防止两个事务同时读取同一行并都想更新，避免“升级死锁”。

NOLOCK（READ UNCOMMITTED）有哪些风险？何时可用？

风险：
- 脏读：读取未提交的数据，可能被回滚。
- 不可重复读：同一次查询两次结果不同。
- 幻读：出现新插入的行。
何时可用：
- 报表类应用，可接受轻微数据不一致。
- 临时表、日志表等不会频繁更新的表。
- 绝对避免：财务、订单等强一致性场景。

三、事务（Transaction）

详细解释四种隔离级别的并发问题。

隔离级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED	读未提交	✅	✅	✅
READ COMMITTED（默认）	❌	✅	✅	避免脏读
REPEATABLE READ	❌	❌	✅	避免不可重复读
SERIALIZABLE	❌	❌	❌	最高，性能最差

不可重复读：事务 A 读取某行，事务 B 修改并提交，A 再次读取，值不同。
幻读：事务 A 读取满足条件的行数，B 插入新行并提交，A 再次查询，行数变多。

READ UNCOMMITTED（读未提交） 最低隔离级别。允许脏读（Dirty Read）：可以读取其他事务尚未提交的数据。不可重复读（Non-Repeatable Read）和幻读（Phantom Read）也可能发生。优点：并发性能最高。缺点：数据一致性最差。适用场景：对数据一致性要求极低的报表、分析类查询。
```
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
SELECT * FROM Orders WHERE Status = 'Pending';
COMMIT;
```
1
2
3
4
- 💡 等价于在 SELECT 中使用 WITH (NOLOCK) 提示。
READ COMMITTED（读已提交） ✅ 默认级别 SQL Server 的默认隔离级别。不允许脏读：只能读取已提交的数据。允许不可重复读：在同一事务中，两次读取同一行，结果可能不同（因为其他事务可能已修改并提交）。允许幻读：在同一事务中，两次执行相同查询，结果集的行数可能不同（因为其他事务插入了新行）。实现方式：传统：读操作加共享锁（S），读完立即释放。推荐：开启 READ_COMMITTED_SNAPSHOT，使用行版本控制避免锁。
```
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
1

REPEATABLE READ（可重复读） 不允许脏读和不可重复读。允许幻读：虽然能保证已读取的行不会被修改，但其他事务仍可插入满足条件的新行。实现方式：在整个事务期间，对读取的行保持共享锁（S）。风险：可能导致更多阻塞。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM Accounts WHERE CustomerID = 1001; -- 第一次读
-- 其他事务无法修改 CustomerID=1001 的行
-- 但可以插入新的 CustomerID=1001 的行（幻读）
SELECT * FROM Accounts WHERE CustomerID = 1001; -- 第二次读，行数可能变多
COMMIT;

SERIALIZABLE（可串行化） 最高隔离级别。不允许脏读、不可重复读、幻读。提供最严格的一致性，事务如同串行执行。实现方式：使用范围锁（Range Locks）阻止其他事务插入新行。缺点：并发性最差，容易导致阻塞和死锁。
```
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM Products WHERE Category = 'Electronics';
-- 其他事务无法插入 Category='Electronics' 的新行
-- 也无法修改现有行
COMMIT;
```
1
2
3
4
5
6

四、性能优化

1. 如何分析和优化慢查询？

使用执行计划：

sql

-- 查看执行计划
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM Users WHERE Email = 'test@example.com';
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- 图形化执行计划（SSMS）
-- 按 Ctrl + M 或点击"显示执行计划"

-- 分析关键指标
-- - 表扫描（Table Scan）vs 索引查找（Index Seek）
-- - 预估行数 vs 实际行数（统计信息是否准确）
-- - 键查找（Key Lookup）操作（需要优化为覆盖索引）

常见优化方法：

✅ 创建合适的索引
✅ 更新统计信息：UPDATE STATISTICS Users;
✅ 重建索引：ALTER INDEX ALL ON Users REBUILD;
✅ 使用参数化查询，避免执行计划缓存失效
✅ 避免在 WHERE 子句中使用函数
✅ 合理使用查询提示（如 FORCESEEK）

2. 如何查看和优化索引使用情况？

sql

-- 查看缺失的索引
SELECT 
    d.database_id,
    d.object_id,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    s.user_seeks,
    s.user_scans,
    s.avg_user_impact
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
ORDER BY s.avg_user_impact DESC;

-- 查看未使用的索引
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.name IS NOT NULL
    AND s.user_seeks = 0
    AND s.user_scans = 0
    AND s.user_lookups = 0
ORDER BY s.user_updates DESC;

3. 分页查询优化

方案一：使用 OFFSET/FETCH（SQL Server 2012+）

sql

-- 简单但不高效（偏移量大时性能差）
SELECT * FROM Users
ORDER BY Id
OFFSET 10000 ROWS
FETCH NEXT 20 ROWS ONLY;

方案二：使用 ROW_NUMBER()

sql

-- 更灵活
WITH NumberedUsers AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RowNum
    FROM Users
)
SELECT * FROM NumberedUsers
WHERE RowNum BETWEEN 10001 AND 10020;

方案三：使用游标或键值分页（推荐）

sql

-- 基于键值分页（最优）
-- 第一页
SELECT TOP 20 * FROM Users ORDER BY Id;

-- 后续页（记录上一页的最后一个 Id）
SELECT TOP 20 * FROM Users 
WHERE Id > @LastId 
ORDER BY Id;

五、常见面试题

Q1: 如何优化大表查询性能？

索引优化
- 创建合适的聚集索引和非聚集索引
- 使用包含列（INCLUDE）创建覆盖索引
- 定期重建/重组索引

分区表

sql

-- 创建分区函数
CREATE PARTITION FUNCTION pf_OrderDate (datetime)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01', '2025-01-01');

-- 创建分区方案
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

-- 创建分区表
CREATE TABLE Orders (
    Id int,
    OrderDate datetime,
    -- ...
) ON ps_OrderDate(OrderDate);

统计信息更新
sql
```
UPDATE STATISTICS Orders WITH FULLSCAN;
```
1
查询优化
- 避免 SELECT *
- 使用适当的 WHERE 条件
- 合理使用 JOIN

Q2: SQL Server 中如何防止死锁？

死锁预防：

✅ 统一访问顺序（所有事务按相同顺序访问表）
✅ 缩短事务时间
✅ 降低隔离级别（如果可以）
✅ 使用锁提示（如 UPDLOCK）
✅ 使用死锁优先级：SET DEADLOCK_PRIORITY LOW;

死锁处理：

sql

-- 捕获死锁信息
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'blocked process threshold', 5;
RECONFIGURE;

-- 启用跟踪标志
DBCC TRACEON(1222, -1); -- 死锁图

Q3: 索引重建和重组有什么区别？

特性	重建（REBUILD）	重组（REORGANIZE）
操作方式	删除旧索引，创建新索引	物理重组索引页
锁级别	排他锁	共享锁
在线操作	需要企业版（ONLINE）	始终在线
碎片清理	完全清理	部分清理
资源消耗	高	低
适用场景	碎片 > 30%	碎片 10-30%

sql

-- 重建索引（离线）
ALTER INDEX ALL ON Users REBUILD;

-- 重建索引（在线，企业版）
ALTER INDEX ALL ON Users REBUILD WITH (ONLINE = ON);

-- 重组索引
ALTER INDEX ALL ON Users REORGANIZE;

六、最佳实践

✅ 合理使用索引 - 不要过度索引，影响写入性能
✅ 定期维护索引 - 重建/重组索引，更新统计信息
✅ 使用参数化查询 - 防止 SQL 注入，提高执行计划重用
✅ 避免隐式转换 - 确保数据类型匹配
✅ 监控阻塞和死锁 - 及时发现问题
✅ 使用适当的事务隔离级别 - 平衡一致性和性能
✅ 优化分页查询 - 使用键值分页代替 OFFSET
❌ 避免 NOLOCK - 除非明确知道风险
❌ **不要在生产环境使用 SELECT ***
❌ 避免在循环中执行查询 - 使用批量操作

SQL Server 面试题 ​

一、索引（Index） ​

1. 聚集索引（Clustered Index）和非聚集索引（Non-Clustered Index）的根本区别是什么？ ​

2. 什么是“书签查找”（Bookmark Lookup / Key Lookup）？如何优化？ ​

3. 什么情况下索引会失效？举出至少 5 个例子。 ​

二、锁（Lock） ​

共享锁（S）、排他锁（X）、更新锁（U）的区别？ ​

NOLOCK（READ UNCOMMITTED）有哪些风险？何时可用？ ​

三、事务（Transaction） ​

详细解释四种隔离级别的并发问题。 ​

四、性能优化 ​

1. 如何分析和优化慢查询？ ​

2. 如何查看和优化索引使用情况？ ​

3. 分页查询优化 ​

五、常见面试题 ​

Q1: 如何优化大表查询性能？ ​

Q2: SQL Server 中如何防止死锁？ ​

Q3: 索引重建和重组有什么区别？ ​

六、最佳实践 ​