四、索引与性能优化
4.1 索引设计与优化
-- 查看索引使用情况
SELECT
OBJECT_NAME(s.object_id) AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
s.last_user_seek,
s.last_user_scan
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id
AND s.index_id = i.index_id
WHERE database_id = DB_ID()
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC;
-- 查找缺失索引
SELECT
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS Impact,
migs.avg_total_user_cost,
migs.avg_user_impact,
mid.statement AS TableName,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE database_id = DB_ID()
ORDER BY Impact DESC;
-- 索引碎片整理
-- 查看碎片率
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
ORDER BY ips.avg_fragmentation_in_percent DESC;
-- 重建索引
ALTER INDEX ALL ON Employees REBUILD;
-- 重新组织索引
ALTER INDEX ALL ON Employees REORGANIZE;
-- 在线重建索引（企业版）
ALTER INDEX IX_Employees_LastName ON Employees REBUILD WITH (ONLINE = ON);
-- 索引维护脚本
DECLARE @TableName NVARCHAR(255);
DECLARE @IndexName NVARCHAR(255);
DECLARE @FragPercent FLOAT;
DECLARE index_cursor CURSOR FOR
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 5
AND i.name IS NOT NULL;
OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @FragPercent;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @FragPercent > 30
EXEC('ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD');
ELSE
EXEC('ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE');
FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @FragPercent;
END
CLOSE index_cursor;
DEALLOCATE index_cursor;
4.2 查询优化
-- 查看执行计划
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Employees WHERE DeptID = 10;
GO
SET SHOWPLAN_XML OFF;
-- 实际执行计划（包含运行时统计）
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM Employees WHERE DeptID = 10;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
-- 查询提示
-- 强制使用索引
SELECT * FROM Employees WITH (INDEX(IX_Employees_DeptID))
WHERE DeptID = 10;
-- 强制使用 NOLOCK（脏读）
SELECT * FROM Employees WITH (NOLOCK)
WHERE DeptID = 10;
-- 强制重新编译
SELECT * FROM Employees
WHERE DeptID = @DeptID
OPTION (RECOMPILE);
-- 强制使用特定连接方式
SELECT *
FROM Employees e
INNER LOOP JOIN Departments d ON e.DeptID = d.DeptID
WHERE e.DeptID = 10;
-- 并行查询提示
SELECT *
FROM Employees
OPTION (MAXDOP 4); -- 限制并行度
-- 批量操作优化
-- 使用表值参数
CREATE TYPE EmployeeType AS TABLE (
EmployeeID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
);
CREATE PROCEDURE sp_InsertEmployees
@Employees EmployeeType READONLY
AS
BEGIN
INSERT INTO Employees (EmployeeID, FirstName, LastName)
SELECT EmployeeID, FirstName, LastName
FROM @Employees;
END;
-- 分批处理大事务
DECLARE @BatchSize INT = 1000;
DECLARE @RowsAffected INT = 1;
WHILE @RowsAffected > 0
BEGIN
DELETE TOP (@BatchSize) FROM LargeTable
WHERE ProcessDate < DATEADD(YEAR, -1, GETDATE());
SET @RowsAffected = @@ROWCOUNT;
WAITFOR DELAY '00:00:01'; -- 避免阻塞
END
4.3 内存优化表（SQL Server 2014+）
-- 创建内存优化文件组
ALTER DATABASE SalesDB
ADD FILEGROUP SalesDB_Mem CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE SalesDB
ADD FILE (NAME = SalesDB_Mem_File, FILENAME = 'D:\Data\SalesDB_Mem')
TO FILEGROUP SalesDB_Mem;
-- 创建内存优化表
CREATE TABLE dbo.MemoryOptimizedOrders (
OrderID INT NOT NULL,
OrderDate DATETIME2 NOT NULL,
CustomerID INT NOT NULL,
Amount DECIMAL(10,2) NOT NULL,
CONSTRAINT PK_MemoryOptimizedOrders
PRIMARY KEY NONCLUSTERED HASH (OrderID)
WITH (BUCKET_COUNT = 1000000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
-- 本地编译存储过程
CREATE PROCEDURE sp_InsertOrder
@OrderID INT,
@OrderDate DATETIME2,
@CustomerID INT,
@Amount DECIMAL(10,2)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
INSERT INTO dbo.MemoryOptimizedOrders (OrderID, OrderDate, CustomerID, Amount)
VALUES (@OrderID, @OrderDate, @CustomerID, @Amount);
END;
五、事务与并发控制
5.1 事务
-- 显式事务
BEGIN TRANSACTION;
BEGIN TRY
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
-- 检查余额
IF (SELECT Balance FROM Accounts WHERE AccountID = 1) < 0
THROW 50001, '余额不足', 1;
COMMIT TRANSACTION;
PRINT '事务提交成功';
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT '事务回滚：' + ERROR_MESSAGE();
END CATCH;
-- 事务嵌套
BEGIN TRANSACTION OuterTran;
UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 1;
BEGIN TRANSACTION InnerTran;
UPDATE Accounts SET Balance = Balance + 50 WHERE AccountID = 2;
-- 保存点
SAVE TRANSACTION SavePoint1;
UPDATE Accounts SET Balance = Balance + 10 WHERE AccountID = 3;
-- 回滚到保存点
ROLLBACK TRANSACTION SavePoint1;
COMMIT TRANSACTION InnerTran;
COMMIT TRANSACTION OuterTran;
-- 查看当前活动事务
SELECT
session_id,
transaction_id,
transaction_begin_time,
transaction_type,
transaction_state
FROM sys.dm_tran_active_transactions;
-- 查看锁
SELECT
request_session_id,
resource_type,
resource_database_id,
resource_description,
request_mode,
request_status
FROM sys.dm_tran_locks;
5.2 隔离级别
-- 查看当前隔离级别
DBCC USEROPTIONS;
-- 设置隔离级别
-- READ UNCOMMITTED（允许脏读）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- READ COMMITTED（默认，避免脏读）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- REPEATABLE READ（避免不可重复读）
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SERIALIZABLE（最高级别，避免幻读）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- SNAPSHOT（快照隔离，需要启用数据库）
ALTER DATABASE YourDB SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
-- 演示快照隔离
BEGIN TRANSACTION;
SELECT * FROM Employees WHERE EmployeeID = 1;
-- 其他会话修改数据后再次查询
SELECT * FROM Employees WHERE EmployeeID = 1; -- 返回相同结果
COMMIT;
-- 行版本控制信息
SELECT
DB_NAME(database_id) AS DatabaseName,
snapshot_isolation_state_desc,
is_read_committed_snapshot_on
FROM sys.databases;
5.3 锁与死锁
-- 锁升级控制
ALTER TABLE Employees SET (LOCK_ESCALATION = AUTO); -- 默认
ALTER TABLE Employees SET (LOCK_ESCALATION = DISABLE);
ALTER TABLE Employees SET (LOCK_ESCALATION = TABLE);
-- 死锁捕获
-- 开启跟踪标志
DBCC TRACEON (1204, -1);
DBCC TRACEON (1222, -1);
-- 使用扩展事件捕获死锁
CREATE EVENT SESSION DeadlockMonitor ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename = N'DeadlockReport.xel')
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS);
-- 查看死锁图
SELECT CAST(event_data AS XML) AS DeadlockGraph
FROM sys.fn_xe_file_target_read_file(N'DeadlockReport*.xel', NULL, NULL, NULL);
-- 死锁处理建议
-- 1. 按相同顺序访问资源
-- 2. 保持事务简短
-- 3. 使用较低的隔离级别
-- 4. 使用行版本控制
-- 5. 添加索引避免表扫描
-- 设置锁超时
SET LOCK_TIMEOUT 5000; -- 5秒超时
六、备份与恢复
6.1 备份类型
-- 查看数据库恢复模式
SELECT
name,
recovery_model_desc,
page_verify_option_desc
FROM sys.databases;
-- 完整备份
BACKUP DATABASE SalesDB
TO DISK = 'D:\Backups\SalesDB_Full.bak'
WITH FORMAT, -- 覆盖现有备份集
NAME = 'SalesDB Full Backup',
COMPRESSION, -- 压缩备份
STATS = 10; -- 显示进度
-- 差异备份
BACKUP DATABASE SalesDB
TO DISK = 'D:\Backups\SalesDB_Diff.bak'
WITH DIFFERENTIAL,
NAME = 'SalesDB Differential Backup',
COMPRESSION;
-- 事务日志备份
BACKUP LOG SalesDB
TO DISK = 'D:\Backups\SalesDB_Log_20240101.trn'
WITH NAME = 'SalesDB Log Backup',
COMPRESSION;
-- 文件组备份
BACKUP DATABASE SalesDB
FILEGROUP = 'PRIMARY'
TO DISK = 'D:\Backups\SalesDB_Primary.bak';
-- 镜像备份
BACKUP DATABASE SalesDB
TO DISK = 'D:\Backups\SalesDB_1.bak',
DISK = 'E:\Backups\SalesDB_2.bak'
WITH MIRROR;
-- 备份到多个文件
BACKUP DATABASE SalesDB
TO DISK = 'D:\Backups\SalesDB_1.bak',
DISK = 'D:\Backups\SalesDB_2.bak'
WITH MAXTRANSFERSIZE = 1048576;
-- 查看备份历史
SELECT
database_name,
backup_type,
backup_start_date,
backup_finish_date,
physical_device_name,
backup_size / 1024 / 1024 AS SizeMB,
compressed_backup_size / 1024 / 1024 AS CompressedSizeMB
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
WHERE database_name = 'SalesDB'
ORDER BY backup_start_date DESC;
6.2 恢复数据库
-- 查看备份内容
RESTORE HEADERONLY FROM DISK = 'D:\Backups\SalesDB_Full.bak';
RESTORE FILELISTONLY FROM DISK = 'D:\Backups\SalesDB_Full.bak';
RESTORE LABELONLY FROM DISK = 'D:\Backups\SalesDB_Full.bak';
-- 完整恢复
-- 1. 恢复完整备份
RESTORE DATABASE SalesDB_Restored
FROM DISK = 'D:\Backups\SalesDB_Full.bak'
WITH MOVE 'SalesDB_Data' TO 'D:\Data\SalesDB_Restored.mdf',
MOVE 'SalesDB_Log' TO 'E:\Logs\SalesDB_Restored.ldf',
NORECOVERY, -- 保持数据库处于恢复状态
STATS = 10;
-- 2. 恢复差异备份
RESTORE DATABASE SalesDB_Restored
FROM DISK = 'D:\Backups\SalesDB_Diff.bak'
WITH NORECOVERY;
-- 3. 恢复日志备份（按顺序）
RESTORE LOG SalesDB_Restored
FROM DISK = 'D:\Backups\SalesDB_Log_20240101.trn'
WITH NORECOVERY;
-- 4. 恢复最后一个日志备份并恢复数据库
RESTORE LOG SalesDB_Restored
FROM DISK = 'D:\Backups\SalesDB_Log_20240102.trn'
WITH RECOVERY; -- 完成恢复
-- 时间点恢复
RESTORE DATABASE SalesDB_Restored
FROM DISK = 'D:\Backups\SalesDB_Full.bak'
WITH NORECOVERY;
RESTORE LOG SalesDB_Restored
FROM DISK = 'D:\Backups\SalesDB_Log_20240101.trn'
WITH STOPAT = '2024-01-01 10:00:00',
RECOVERY;
-- 页面恢复
RESTORE DATABASE SalesDB
PAGE = '1:100' -- 文件ID:页面ID
FROM DISK = 'D:\Backups\SalesDB_Full.bak'
WITH NORECOVERY;
RESTORE LOG SalesDB
FROM DISK = 'D:\Backups\SalesDB_Log.trn'
WITH RECOVERY;
-- 将数据库恢复到故障点
RESTORE DATABASE SalesDB
FROM DISK = 'D:\Backups\SalesDB_Full.bak'
WITH REPLACE, -- 覆盖现有数据库
RECOVERY;
