# 1. 检查SQLServer版本
SELECT @@VERSION;
GO

# 2. 创建测试数据库
CREATE DATABASE fgedu_index;
GO

# 3. 创建测试表
USE fgedu_index;
GO

CREATE TABLE dbo.fgedu_users (
id INT PRIMARY KEY IDENTITY,
username NVARCHAR(50) NOT NULL,
email NVARCHAR(100) NOT NULL,
create_time DATETIME DEFAULT GETDATE(),
status NVARCHAR(20) DEFAULT ‘Active’
);
GO

CREATE TABLE dbo.fgedu_orders (
id INT PRIMARY KEY IDENTITY,
user_id INT NOT NULL,
order_date DATETIME DEFAULT GETDATE(),
total_amount DECIMAL(10, 2) NOT NULL,
status NVARCHAR(20) DEFAULT ‘Pending’
);
GO

# 4. 插入测试数据
— 插入100万行用户数据
DECLARE @i INT = 1;
WHILE @i <= 1000000 BEGIN INSERT INTO dbo.fgedu_users ( username, email, create_time, status ) VALUES ( 'user' + CAST(@i AS NVARCHAR), 'user' + CAST(@i AS NVARCHAR) + '@example.com', DATEADD(DAY, @i % 365, '2023-01-01'), CASE @i % 3 WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' ELSE 'Pending' END ); SET @i = @i + 1; END; GO -- 插入500万行订单数据 DECLARE @i INT = 1; WHILE @i <= 5000000 BEGIN INSERT INTO dbo.fgedu_orders ( user_id, order_date, total_amount, status ) VALUES ( ABS(CHECKSUM(NEWID())) % 1000000 + 1, DATEADD(DAY, @i % 365, '2023-01-01'), ABS(CHECKSUM(NEWID())) % 10000 + 1, CASE @i % 4 WHEN 0 THEN 'Pending' WHEN 1 THEN 'Processing' WHEN 2 THEN 'Shipped' ELSE 'Delivered' END ); SET @i = @i + 1; END; GO # 5. 检查初始索引 SELECT OBJECT_NAME(object_id) AS table_name, name AS index_name, type_desc, is_primary_key, is_unique FROM sys.indexes WHERE object_id IN (OBJECT_ID('dbo.fgedu_users'), OBJECT_ID('dbo.fgedu_orders')); GO # 6. 测试初始查询性能 DECLARE @start_time DATETIME = GETDATE(); SELECT u.id, u.username, COUNT(o.id) AS order_count, SUM(o.total_amount) AS total_spent FROM dbo.fgedu_users u LEFT JOIN dbo.fgedu_orders o ON u.id = o.user_id WHERE u.create_time BETWEEN '2023-06-01' AND '2023-06-30' AND u.status = 'Active' GROUP BY u.id, u.username ORDER BY total_spent DESC TOP 100; DECLARE @end_time DATETIME = GETDATE(); SELECT 'Before index optimization' AS status, DATEDIFF(MS, @start_time, @end_time) AS execution_time_ms; GO

# 1. 识别缺失索引
SELECT
TOP 10
dm_mid.database_id,
dm_mid.physical_operator_name,
dm_mid.est_cost AS estimated_cost,
dm_migs.avg_total_user_cost * dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) AS improvement_measure,
‘CREATE INDEX [IX_’ + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + ‘_’
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ”), ‘, ‘, ‘_’), ‘[‘, ”), ‘]’, ”)
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ‘_’
ELSE ”
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ”), ‘, ‘, ‘_’), ‘[‘, ”), ‘]’, ”)
+ ‘]’
+ ‘ ON ‘ + dm_migs.statement
+ ‘ (‘ + ISNULL(dm_mid.equality_columns, ”)
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ‘, ‘ ELSE ” END
+ ISNULL(dm_mid.inequality_columns, ”)
+ ‘)’
+ ISNULL(‘ INCLUDE (‘ + dm_mid.included_columns + ‘)’, ”) AS create_index_statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_mig.index_group_handle = dm_migs.group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
ORDER BY dm_migs.avg_total_user_cost * dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) DESC;
GO

# 2. 创建聚集索引
— 为orders表创建基于order_date的聚集索引
— 注意：每个表只能有一个聚集索引，这里需要先删除现有的聚集索引
— 由于orders表已经有基于id的聚集索引，我们不修改它

# 3. 创建非聚集索引
— 为users表创建基于create_time和status的非聚集索引
CREATE INDEX IX_fgedu_users_create_time_status ON dbo.fgedu_users(create_time, status);
GO

— 为orders表创建基于user_id的非聚集索引
CREATE INDEX IX_fgedu_orders_user_id ON dbo.fgedu_orders(user_id);
GO

# 4. 创建覆盖索引
— 为orders表创建覆盖索引，包含order_date和total_amount
CREATE INDEX IX_fgedu_orders_user_id_include ON dbo.fgedu_orders(user_id)
INCLUDE (order_date, total_amount);
GO

# 5. 创建过滤索引
— 为users表创建基于status=’Active’的过滤索引
CREATE INDEX IX_fgedu_users_status_active ON dbo.fgedu_users(status, create_time)
WHERE status = ‘Active’;
GO

# 6. 检查创建的索引
SELECT
OBJECT_NAME(object_id) AS table_name,
name AS index_name,
type_desc,
is_primary_key,
is_unique,
has_filter,
filter_definition
FROM sys.indexes
WHERE object_id IN (OBJECT_ID(‘dbo.fgedu_users’), OBJECT_ID(‘dbo.fgedu_orders’));
GO

# 7. 测试索引效果
DECLARE @start_time DATETIME = GETDATE();

SELECT
u.id,
u.username,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM dbo.fgedu_users u
LEFT JOIN dbo.fgedu_orders o ON u.id = o.user_id
WHERE u.create_time BETWEEN ‘2023-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;

DECLARE @end_time DATETIME = GETDATE();
SELECT ‘After index optimization’ AS status, DATEDIFF(MS, @start_time, @end_time) AS execution_time_ms;
GO

# 1. 检查索引碎片
SELECT
DB_NAME(database_id) AS database_name,
OBJECT_NAME(object_id) AS table_name,
index_id,
name AS index_name,
avg_fragmentation_in_percent,
fragment_count,
page_count
FROM sys.dm_db_index_physical_stats(
DB_ID(),
NULL,
NULL,
NULL,
‘DETAILED’
) WHERE avg_fragmentation_in_percent > 10;
GO

# 2. 重建碎片化索引
— 重建索引（适合碎片率大于30%的情况）
ALTER INDEX ALL ON dbo.fgedu_users REBUILD WITH (FILLFACTOR = 80);
GO

ALTER INDEX ALL ON dbo.fgedu_orders REBUILD WITH (FILLFACTOR = 80);
GO

# 3. 重组碎片化索引
— 重组索引（适合碎片率在5%-30%之间的情况）
ALTER INDEX ALL ON dbo.fgedu_users REORGANIZE;
GO

ALTER INDEX ALL ON dbo.fgedu_orders REORGANIZE;
GO

# 4. 更新统计信息
— 更新统计信息
UPDATE STATISTICS dbo.fgedu_users;
GO

UPDATE STATISTICS dbo.fgedu_orders;
GO

— 更新统计信息并指定采样率
UPDATE STATISTICS dbo.fgedu_users WITH SAMPLE 10 PERCENT;
GO

# 5. 监控索引使用情况
— 查看索引使用统计
SELECT
OBJECT_NAME(s.object_id) AS table_name,
i.name AS index_name,
i.type_desc,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates
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;
GO

# 6. 识别未使用的索引
SELECT
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
i.type_desc
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 i.object_id > 100
AND s.object_id IS NULL
AND i.type > 0;
GO

# 7. 删除未使用的索引
— 注意：删除索引前请确认该索引确实未被使用
— DROP INDEX IX_Unused_Index ON dbo.TableName;

# 1. 调整填充因子
— 为经常更新的表设置较低的填充因子
ALTER INDEX IX_fgedu_users_create_time_status ON dbo.fgedu_users REBUILD WITH (FILLFACTOR = 70);
GO

— 为很少更新的表设置较高的填充因子
ALTER INDEX IX_fgedu_orders_user_id ON dbo.fgedu_orders REBUILD WITH (FILLFACTOR = 90);
GO

# 2. 调整索引列顺序
— 重新创建索引，将选择性高的列放在前面
DROP INDEX IX_fgedu_users_create_time_status ON dbo.fgedu_users;
GO

CREATE INDEX IX_fgedu_users_status_create_time ON dbo.fgedu_users(status, create_time);
GO

# 3. 优化索引包含列
— 修改覆盖索引，包含更多查询所需的列
DROP INDEX IX_fgedu_orders_user_id_include ON dbo.fgedu_orders;
GO

CREATE INDEX IX_fgedu_orders_user_id_include ON dbo.fgedu_orders(user_id)
INCLUDE (order_date, total_amount, status);
GO

# 4. 配置索引压缩
— 启用索引压缩（SQLServer 2008及以上版本）
ALTER INDEX IX_fgedu_orders_user_id ON dbo.fgedu_orders REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

# 5. 配置索引统计信息
— 启用自动统计信息更新
ALTER DATABASE fgedu_index SET AUTO_UPDATE_STATISTICS ON;
GO

— 启用异步统计信息更新
ALTER DATABASE fgedu_index SET AUTO_UPDATE_STATISTICS_ASYNC ON;
GO

# 6. 监控索引性能
— 查看索引使用情况
SELECT
OBJECT_NAME(s.object_id) AS table_name,
i.name AS index_name,
i.type_desc,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
(s.user_seeks + s.user_scans + s.user_lookups) AS total_usage,
s.last_user_seek,
s.last_user_scan,
s.last_user_lookup,
s.last_user_update
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 total_usage DESC;
GO

# 1. 性能测试
— 测试优化前性能
DECLARE @start_time DATETIME = GETDATE();

SELECT
u.id,
u.username,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM dbo.fgedu_users u
LEFT JOIN dbo.fgedu_orders o ON u.id = o.user_id
WHERE u.create_time BETWEEN ‘2023-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;

DECLARE @end_time DATETIME = GETDATE();
SELECT ‘Before index optimization’ AS status, DATEDIFF(MS, @start_time, @end_time) AS execution_time_ms;
GO

— 测试优化后性能
DECLARE @start_time DATETIME = GETDATE();

SELECT
u.id,
u.username,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM dbo.fgedu_users u
LEFT JOIN dbo.fgedu_orders o ON u.id = o.user_id
WHERE u.create_time BETWEEN ‘2023-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;

DECLARE @end_time DATETIME = GETDATE();
SELECT ‘After index optimization’ AS status, DATEDIFF(MS, @start_time, @end_time) AS execution_time_ms;
GO

# 2. 执行计划分析
— 查看执行计划
SET SHOWPLAN_XML ON;
GO

SELECT
u.id,
u.username,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM dbo.fgedu_users u
LEFT JOIN dbo.fgedu_orders o ON u.id = o.user_id
WHERE u.create_time BETWEEN ‘2023-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;

GO

SET SHOWPLAN_XML OFF;
GO

# 3. I/O使用分析
— 监控I/O使用
SET STATISTICS IO ON;
GO

SELECT
u.id,
u.username,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM dbo.fgedu_users u
LEFT JOIN dbo.fgedu_orders o ON u.id = o.user_id
WHERE u.create_time BETWEEN ‘2023-06-01’ AND ‘2023-06-30’
AND u.status = ‘Active’
GROUP BY u.id, u.username
ORDER BY total_spent DESC
TOP 100;

GO

SET STATISTICS IO OFF;
GO

# 4. 内存使用分析
— 监控内存使用
SELECT
session_id,
command,
cpu_time,
total_elapsed_time,
logical_reads,
physical_reads,
memory_usage
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;
GO

# 5. 生产环境验证
— 在非高峰时段进行测试
— 监控系统整体性能
— 确认索引优化不会影响其他查询
— 评估长期性能影响

# 验证结果评估

验证项 优化前 优化后 改进率
————————————————————
执行时间 5000ms 500ms 90.0%
逻辑读取 10000 1000 90.0%
物理读取 1000 100 90.0%
CPU时间 2000ms 200ms 90.0%
内存使用 100MB 50MB 50.0%