印刻数据库

外观

SQLServer 索引优化 ​

索引优化概述 ​

索引是SQL Server中提高查询性能的关键组件，通过合理设计和优化索引，可以显著提高数据库的查询性能。索引优化涉及索引设计、索引类型选择、索引维护、性能监控等多个方面，是数据库性能优化的核心工作之一。

索引优化的重要性 ​

  • 提高查询性能：索引可以减少查询所需的I/O操作，加速数据检索
  • 降低系统资源消耗：优化后的索引可以减少CPU和内存占用
  • 提高并发处理能力：高效的索引可以减少锁争用，支持更多并发用户
  • 支持复杂查询：合适的索引可以加速JOIN、GROUP BY、ORDER BY等操作
  • 降低存储成本：优化后的索引结构更紧凑，减少存储开销

索引设计原则 ​

选择合适的索引列 ​

  • 高频查询列：为经常出现在WHERE、JOIN、ORDER BY和GROUP BY子句中的列创建索引
  • 高选择性列：选择性 = 不同值的数量 / 总行数，选择性越高，索引效果越好
  • 窄列优先：使用数据类型较小的列创建索引，减少索引大小和I/O开销
  • 避免过多列：索引中包含的列不宜过多，否则会增加索引大小和维护开销

示例：

sql
-- 计算列选择性，判断是否适合创建索引
SELECT 
    COUNT(DISTINCT CustomerID) / COUNT(*) AS CustomerID_Selectivity,
    COUNT(DISTINCT Status) / COUNT(*) AS Status_Selectivity
FROM Sales.Orders;

-- CustomerID选择性高，适合创建索引；Status选择性低，不适合

设计合适的索引类型 ​

  • 主键索引：唯一标识表中的每一行，默认使用聚集索引
  • 唯一索引：确保列或列组合的值唯一，避免重复数据
  • 聚集索引：决定表中数据的物理存储顺序，每个表只能有一个
  • 非聚集索引：独立于表数据存储的索引，每个表可以有多个
  • 覆盖索引：包含查询所需的所有列，避免回表查询
  • 过滤索引：只包含满足特定条件的行，适合稀疏数据
  • 列存储索引：适合数据仓库和分析查询，提高聚合查询性能

示例：

sql
-- 创建覆盖索引，避免回表查询
CREATE NONCLUSTERED INDEX idx_Orders_CustomerID_Include 
ON Sales.Orders(CustomerID) 
INCLUDE (OrderDate, TotalAmount, ShipCity, Status);

-- 创建过滤索引，优化特定条件查询
CREATE NONCLUSTERED INDEX idx_Orders_OpenStatus 
ON Sales.Orders(OrderID, OrderDate) 
WHERE Status = 'Open';

优化索引列顺序 ​

  • 选择性高的列优先：将过滤效果好的列放在索引前面
  • 相等条件列优先：将用于=、IN等条件的列放在前面
  • 范围查询列放在后面：将用于>、<、BETWEEN等条件的列放在后面
  • ORDER BY/GROUP BY列：考虑将排序和分组列包含在索引中

示例：

sql
-- 推荐：选择性高的列在前，范围查询列在后
CREATE NONCLUSTERED INDEX idx_Orders_CustomerID_OrderDate 
ON Sales.Orders(CustomerID, OrderDate);

-- 此索引适合：WHERE CustomerID = 123 AND OrderDate > '2023-01-01'

避免过度索引 ​

  • 权衡查询与修改性能：索引提高查询速度，但会降低INSERT/UPDATE/DELETE性能
  • 定期审查索引使用情况：删除不使用或很少使用的索引
  • 考虑索引维护开销：过多索引会增加重建、重新组织的时间和资源消耗
  • 合并相似索引：将多个单列索引合并为复合索引，减少索引数量

示例：

sql
-- 检查索引使用情况，识别无用索引
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    COALESCE(ius.user_seeks, 0) + COALESCE(ius.user_scans, 0) + COALESCE(ius.user_lookups, 0) AS TotalReads,
    COALESCE(ius.user_updates, 0) AS TotalWrites
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius 
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY TotalReads ASC;

高级索引类型与特性 ​

索引视图 ​

索引视图是将视图结果集物理存储的特殊视图，适合频繁使用的复杂查询，尤其在数据仓库环境中。创建索引视图需要使用SCHEMABINDING，并在视图上创建唯一聚集索引。

示例：

sql
-- 创建架构绑定视图
CREATE VIEW Sales.vw_SalesSummary
WITH SCHEMABINDING
AS
SELECT 
    p.ProductID,
    p.ProductName,
    SUM(od.Quantity) AS TotalQuantity,
    SUM(od.Quantity * od.UnitPrice) AS TotalSales,
    COUNT_BIG(*) AS RowCount
FROM Sales.OrderDetails od
JOIN Production.Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName;

-- 创建唯一聚集索引，物化视图数据
CREATE UNIQUE CLUSTERED INDEX idx_SalesSummary_ProductID 
ON Sales.vw_SalesSummary(ProductID);

列存储索引 ​

列存储索引按列存储数据，适合数据仓库和分析查询，可显著提高聚合查询性能。SQL Server支持聚集列存储索引和非聚集列存储索引。

示例：

sql
-- 创建聚集列存储索引，适合数据仓库表
CREATE CLUSTERED COLUMNSTORE INDEX idx_FactSales_Clustered 
ON DataWarehouse.FactSales;

-- 创建非聚集列存储索引，增强特定查询性能
CREATE NONCLUSTERED COLUMNSTORE INDEX idx_FactSales_Columns 
ON DataWarehouse.FactSales(OrderDate, ProductID, Quantity, TotalAmount);

过滤索引 ​

过滤索引只包含满足特定条件的行，适合稀疏数据列，可减少索引大小并提高查询性能。

示例：

sql
-- 为经常查询的特定状态创建过滤索引
CREATE NONCLUSTERED INDEX idx_Orders_OpenStatus 
ON Sales.Orders(OrderID, OrderDate) 
WHERE Status = 'Open';

-- 为非空电子邮件列创建过滤索引
CREATE NONCLUSTERED INDEX idx_Customers_Email 
ON Sales.Customers(CustomerID) 
WHERE Email IS NOT NULL;

包含列索引 ​

包含列索引在索引叶级别包含非键列，避免回表查询，提高查询性能。

示例：

sql
-- 创建包含列索引
CREATE NONCLUSTERED INDEX idx_Orders_CustomerID_Include 
ON Sales.Orders(CustomerID) 
INCLUDE (OrderDate, TotalAmount, ShipCity, Status);

-- 此查询可直接使用索引，无需回表
SELECT OrderID, OrderDate, TotalAmount, ShipCity, Status 
FROM Sales.Orders 
WHERE CustomerID = 123;

全文索引 ​

全文索引用于复杂文本搜索，支持关键词搜索、短语搜索、模糊搜索等，适合处理大量文本数据。

示例：

sql
-- 创建全文目录
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

-- 创建全文索引
CREATE FULLTEXT INDEX ON Production.ProductDescriptions(Description)
KEY INDEX PK_ProductDescriptions
WITH STOPLIST = SYSTEM;

-- 使用全文索引进行精确搜索
SELECT ProductID, Description 
FROM Production.ProductDescriptions 
WHERE CONTAINS(Description, 'laptop AND (dell OR hp)');

索引维护策略 ​

更新统计信息 ​

统计信息是查询优化器生成执行计划的重要依据，定期更新统计信息可确保生成最优执行计划。

示例：

sql
-- 更新单个表的所有统计信息，使用完整扫描
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

-- 更新单个索引的统计信息，使用50%采样
UPDATE STATISTICS Sales.Orders idx_Orders_CustomerID WITH SAMPLE 50 PERCENT;

-- 更新数据库中所有表的统计信息
EXEC sp_updatestats;

-- 启用异步统计信息更新，减少阻塞
ALTER DATABASE Sales SET AUTO_UPDATE_STATISTICS_ASYNC ON;

重建和重新组织索引 ​

索引在使用过程中会产生碎片，影响性能。根据碎片程度选择重建或重新组织索引：

  • 重新组织：碎片率5%-30%，使用ALTER INDEX ... REORGANIZE，在线操作
  • 重建：碎片率>30%，使用ALTER INDEX ... REBUILD，可选择在线执行

示例：

sql
-- 查看索引碎片情况，过滤碎片率>5%的索引
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    name AS IndexName,
    index_type_desc,
    avg_fragmentation_in_percent,
    page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5
ORDER BY avg_fragmentation_in_percent DESC;

-- 重新组织索引（在线操作）
ALTER INDEX ALL ON Sales.Orders REORGANIZE;

-- 在线重建索引，减少阻塞，使用最大并行度4
ALTER INDEX ALL ON Sales.Orders REBUILD WITH (ONLINE = ON, MAXDOP = 4);

监控索引使用情况 ​

定期监控索引使用情况，识别低效或未使用的索引，及时进行优化。

示例：

sql
-- 监控索引使用情况，包含最后使用时间
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    COALESCE(ius.user_seeks, 0) AS Seeks,
    COALESCE(ius.user_scans, 0) AS Scans,
    COALESCE(ius.user_lookups, 0) AS Lookups,
    COALESCE(ius.user_updates, 0) AS Updates,
    COALESCE(ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup) AS LastUsed
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius 
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY LastUsed ASC;

索引性能监控与分析 ​

使用动态管理视图（DMVs） ​

SQL Server提供多个DMV用于监控索引性能、碎片情况和使用状态，是生产环境中常用的监控工具。

示例：

sql
-- 查看缺失索引建议，按改进潜力排序
SELECT 
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS Improvement_Measure,
    'CREATE NONCLUSTERED INDEX idx_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_' + 
    REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + 
    CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END +
    REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + 
    ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + 
    CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END + 
    ISNULL(mid.inequality_columns, '') + ')' + 
    ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS Create_Index_Script
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
ORDER BY Improvement_Measure DESC;

使用执行计划分析 ​

执行计划是分析索引性能的重要工具，可通过SSMS、Azure Data Studio或T-SQL查看，帮助识别索引使用情况和性能瓶颈。

示例：

sql
-- 查看预估执行计划
SET SHOWPLAN_XML ON;
GO
SELECT OrderID, OrderDate, TotalAmount FROM Sales.Orders WHERE CustomerID = 123;
GO
SET SHOWPLAN_XML OFF;

-- 查看实际执行计划，包含运行时统计信息
SET STATISTICS XML ON;
GO
SELECT OrderID, OrderDate, TotalAmount FROM Sales.Orders WHERE CustomerID = 123;
GO
SET STATISTICS XML OFF;

使用Query Store ​

Query Store是SQL Server 2016及以上版本的功能，用于监控查询性能和执行计划变化，是索引优化的重要工具。

示例：

sql
-- 启用Query Store，配置合理的保留策略
ALTER DATABASE Sales SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60
);

-- 查看查询性能统计，按平均持续时间排序
SELECT 
    qt.query_sql_text,
    qp.query_plan,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.avg_logical_io_reads,
    rs.avg_physical_io_reads
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan qp ON q.query_id = qp.query_id
JOIN sys.query_store_runtime_stats rs ON qp.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

版本差异 ​

SQL Server 2008/2008 R2 ​

  • 支持基本索引类型：聚集索引、非聚集索引、唯一索引
  • 引入过滤索引和稀疏列
  • 支持索引视图
  • 缺少列存储索引和Query Store

SQL Server 2012 ​

  • 引入列存储索引（只读）
  • 增强了索引重建功能
  • 改进了统计信息更新机制

SQL Server 2014 ​

  • 支持可更新列存储索引
  • 引入内存优化表和非聚集哈希索引
  • 改进了索引碎片管理

SQL Server 2016 ​

  • 引入Query Store，用于监控查询性能
  • 增强了列存储索引功能
  • 引入JSON索引支持
  • 支持时态表索引

SQL Server 2017 ​

  • 增强了列存储索引性能
  • 引入自适应查询处理
  • 支持图形索引
  • 改进了自动统计信息更新

SQL Server 2019 ​

  • 引入智能查询处理
  • 增强了列存储索引的批处理模式
  • 支持近似查询处理
  • 引入表变量延迟编译

SQL Server 2022 ​

  • 引入参数敏感计划优化
  • 增强了列存储索引的写入性能
  • 支持JSON路径索引
  • 改进了在线索引重建
  • 引入Ledger功能，支持不可篡改的索引

Azure SQL Database ​

  • 支持自动索引优化
  • 增强了Query Store功能
  • 支持无服务器模式下的索引优化
  • 提供索引建议和自动修复

生产环境最佳实践 ​

电商订单查询优化 ​

问题：电商系统中按客户和日期范围查询订单响应缓慢，执行计划显示键查找。

优化方案：

sql
-- 分析查询执行计划，识别性能瓶颈
SELECT 
    o.OrderID, o.OrderDate, o.TotalAmount, o.ShipCity, o.Status,
    c.CustomerName, c.Email
FROM Sales.Orders o
JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
WHERE o.CustomerID = 123
AND o.OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

-- 创建覆盖索引，包含所有查询列，避免回表查询
CREATE NONCLUSTERED INDEX idx_Orders_CustomerID_OrderDate 
ON Sales.Orders(CustomerID, OrderDate) 
INCLUDE (TotalAmount, ShipCity, Status);

-- 为Customers表创建合适的索引，优化JOIN操作
CREATE NONCLUSTERED INDEX idx_Customers_CustomerID 
ON Sales.Customers(CustomerID) 
INCLUDE (CustomerName, Email);

数据仓库报表优化 ​

问题：月度销售报表生成缓慢，涉及大量聚合计算，执行计划显示哈希聚合和排序操作。

优化方案：

sql
-- 原始报表查询，性能瓶颈在于聚合计算
SELECT 
    YEAR(OrderDate) AS SalesYear,
    MONTH(OrderDate) AS SalesMonth,
    ProductCategory,
    SUM(Quantity) AS TotalQuantity,
    SUM(TotalAmount) AS TotalSales
FROM DataWarehouse.FactSales 
JOIN DataWarehouse.DimProduct ON FactSales.ProductID = DimProduct.ProductID
GROUP BY YEAR(OrderDate), MONTH(OrderDate), ProductCategory;

-- 优化1：创建聚集列存储索引，加速聚合查询
CREATE CLUSTERED COLUMNSTORE INDEX idx_FactSales_Clustered 
ON DataWarehouse.FactSales;

-- 优化2：创建索引视图，预计算聚合结果，提高报表查询速度
CREATE VIEW DataWarehouse.vw_MonthlySales
WITH SCHEMABINDING
AS
SELECT 
    YEAR(OrderDate) AS SalesYear,
    MONTH(OrderDate) AS SalesMonth,
    ProductCategory,
    SUM(Quantity) AS TotalQuantity,
    SUM(TotalAmount) AS TotalSales,
    COUNT_BIG(*) AS RowCount
FROM DataWarehouse.FactSales fs
JOIN DataWarehouse.DimProduct dp ON fs.ProductID = dp.ProductID
GROUP BY YEAR(OrderDate), MONTH(OrderDate), ProductCategory;

CREATE UNIQUE CLUSTERED INDEX idx_MonthlySales 
ON DataWarehouse.vw_MonthlySales(SalesYear, SalesMonth, ProductCategory);

高并发系统索引优化 ​

问题：高并发系统中订单创建操作响应缓慢，锁争用严重，影响系统吞吐量。

优化方案：

sql
-- 分析锁争用情况，识别热点资源
SELECT 
    resource_type,
    resource_associated_entity_id,
    request_mode,
    request_status,
    request_session_id,
    blocking_session_id
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();

-- 优化1：启用行版本控制，减少读写锁冲突
ALTER DATABASE Sales SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION ON;

-- 优化2：优化索引设计，减少锁范围和持有时间
CREATE NONCLUSTERED INDEX idx_Orders_Status 
ON Sales.Orders(Status) 
INCLUDE (OrderID, OrderDate, CustomerID, TotalAmount);

-- 优化3：使用分区表，将锁限制在单个分区内，减少全局锁竞争
CREATE PARTITION FUNCTION pf_Orders_OrderDate (DATE) 
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2023-04-01', '2023-07-01', '2023-10-01');

CREATE PARTITION SCHEME ps_Orders_OrderDate 
AS PARTITION pf_Orders_OrderDate ALL TO ([PRIMARY]);

-- 重新创建聚集索引到分区方案
CREATE CLUSTERED INDEX idx_Orders_OrderDate 
ON Sales.Orders(OrderDate) 
ON ps_Orders_OrderDate(OrderDate) 
WITH (DROP_EXISTING = ON);

常见问题 (FAQ) ​

如何确定是否需要创建索引？ ​

通过以下方式评估是否需要创建索引：

  • 分析查询执行计划，查看是否存在表扫描或键查找等性能瓶颈
  • 监控查询响应时间，识别慢查询
  • 查看缺失索引DMV（sys.dm_db_missing_index_details）获取系统建议
  • 考虑查询频率、数据修改频率和存储成本的平衡
  • 分析业务场景和查询模式，优先为核心查询创建索引

如何处理索引碎片？ ​

根据碎片程度选择合适的处理方式：

  • 碎片率 < 5%：无需处理，性能影响可忽略
  • 碎片率 5%-30%：使用ALTER INDEX ... REORGANIZE，在线操作，开销较低
  • 碎片率 > 30%：使用ALTER INDEX ... REBUILD，可选择在线执行，开销较高
  • 考虑使用自动化脚本定期维护索引，避免手动操作

如何处理索引过多的问题？ ​

  • 定期审查索引使用情况，删除未使用或很少使用的索引
  • 合并相似索引，使用复合索引代替多个单列索引
  • 使用覆盖索引减少索引数量，包含多个查询所需的列
  • 考虑使用列存储索引代替多个非聚集索引，适合分析场景
  • 建立索引管理策略，控制索引数量增长

如何避免索引失效？ ​

  • 避免在WHERE子句中对索引列使用函数或计算
  • 确保数据类型匹配，避免隐式转换
  • 避免使用NOT、!=、<>等操作符，它们会导致索引失效
  • 保持统计信息更新，确保查询优化器了解数据分布
  • 避免在索引列上使用IS NULL/IS NOT NULL（除非创建了过滤索引）

如何监控索引性能？ ​

  • 使用动态管理视图：sys.dm_db_index_usage_stats、sys.dm_db_index_physical_stats
  • 使用Query Store监控查询性能和执行计划变化
  • 分析执行计划，查看索引使用情况和性能瓶颈
  • 设置性能计数器监控索引相关指标
  • 定期生成索引使用报告，识别低效索引

列存储索引适合哪些场景？ ​

列存储索引适合：

  • 数据仓库和分析场景，处理大量历史数据
  • 频繁的聚合查询、JOIN查询和范围查询
  • 低写入、高读取的工作负载
  • 需要快速生成报表的业务场景
  • 全表扫描或大范围扫描为主的查询

如何选择聚集索引列？ ​

选择聚集索引列的原则：

  • 唯一标识每行数据，确保数据完整性
  • 频繁用于范围查询和排序操作
  • 有序增长，减少页分裂
  • 窄列优先，减少索引大小
  • 考虑表的主要访问模式，优先为核心查询优化

如何优化非聚集索引？ ​

  • 选择高选择性列作为索引键，提高过滤效果
  • 合理设计索引列顺序，将等值条件列放在前面
  • 使用包含列（INCLUDE）减少回表查询
  • 为频繁查询的特定条件创建过滤索引
  • 定期维护索引，更新统计信息和重建/重新组织

如何处理参数嗅探问题？ ​

参数嗅探是指查询优化器使用特定参数值生成执行计划，可能不适合其他参数值：

  • 考虑使用查询提示（OPTION RECOMPILE）
  • 启用数据库级别的优化（如SQL Server 2022的参数敏感计划优化）
  • 使用局部变量或重新编译选项
  • 考虑拆分查询，针对不同参数值使用不同索引

如何使用自动索引优化？ ​

在Azure SQL Database中，可以启用自动索引优化：

  • 自动创建缺失索引
  • 自动删除不使用的索引
  • 自动强制使用最优执行计划
  • 提供索引建议和优化报告
  • 对于本地SQL Server，可以使用第三方工具或自定义脚本实现类似功能

总结 ​

索引优化是SQL Server性能优化的核心工作，需要综合考虑索引设计、类型选择、维护策略和性能监控。在实际生产环境中，应根据业务场景和查询模式选择合适的索引策略，并定期进行监控和维护。

关键要点 ​

  • 合理设计索引：选择高选择性列，设计合适的索引类型和列顺序
  • 定期维护索引：更新统计信息，重建或重新组织碎片索引
  • 监控索引性能：使用DMV、Query Store和执行计划分析索引使用情况
  • 考虑版本差异：不同SQL Server版本提供不同的索引功能，应充分利用
  • 平衡查询和修改性能：索引提高查询速度，但会降低写入性能
  • 关注生产场景：根据实际业务需求优化索引，如电商查询、数据仓库报表、高并发系统等

持续优化建议 ​

  • 建立索引管理策略，定期审查和优化索引
  • 结合自动化工具和脚本，减少手动操作
  • 监控索引使用情况，及时移除低效索引
  • 跟踪SQL Server新版本的索引功能，持续学习和应用
  • 建立索引设计规范，确保团队遵循最佳实践

通过持续监控、分析和优化索引，可以显著提高SQL Server数据库的查询性能，降低系统资源消耗，提升用户体验，为业务发展提供可靠的数据支持。