Ubuntu 上 SQL Server 的索引策略
一 策略总览与适用场景
- 索引目标是在查询性能与写入维护成本之间取得平衡。行存储采用 B+ 树，列存储适合数据仓库/分析场景；OLTP 优先考虑高选择性、窄键的 B+ 树索引，OLAP 优先考虑聚集列存储与非聚集列存储。总体策略是为优化器提供多样化且不过度的索引选择，并让其选择最优访问路径。对于高吞吐 OLTP，可考虑内存优化表与索引；对于超大数据集分析，优先列存储。索引设计应围绕业务查询模式持续迭代与验证。
二 索引类型与选型
- 行存储索引
- 聚集索引 Clustered：决定表中数据的物理顺序，适合范围扫描、排序与高选择性键；键应尽量短小且唯一/非空。
- 非聚集索引 Nonclustered：更灵活，适合点查与覆盖查询；通过包含列（INCLUDE）减少回表。
- 唯一索引 Unique：为优化器提供额外选择性信息，利于匹配与去重。
- 筛选索引 Filtered：针对定义良好的数据子集建立，减小索引体积并提升命中率。
- 列存储索引
- 聚集列存储 Clustered Columnstore：压缩率高、批处理快，适合星型联接、聚合、分组等分析查询。
- 非聚集列存储 Nonclustered Columnstore：为行存储表加速分析型查询的覆盖。
- 内存优化索引
- 哈希索引（精确匹配）、非聚集索引（范围/排序），用于内存优化表，提供无闩锁并发优势。
- 其他专用索引
- XML 索引、空间索引、全文索引用于特定数据类型与检索需求。
三 设计原则与列选择
- 为高频出现在 WHERE、JOIN、ORDER BY、GROUP BY 的列建立索引，优先高选择性列（不同值越多越好）。
- 复合索引遵循最左前缀：将筛选性强、能显著缩小结果集的列放在左侧；等值条件列优先靠前，其后按非重复度从高到低排列。
- 优先构建覆盖索引（INCLUDE 非键列），减少回表与 I/O。
- 控制索引数量与宽度：索引越多，INSERT/UPDATE/DELETE 维护成本越高；索引键尽量窄。
- 对小表建立索引往往收益有限，可能不如全表扫描。
- 避免在索引键上使用 varchar(max)/nvarchar(max)/varbinary(max) 等大对象类型；它们可作为包含列参与非聚集索引。
- 对外键建立索引，提升关联查询与级联操作性能。
- 对计算列在满足确定性/可持久化前提下可建立索引，以覆盖派生查询。
四 查询写法与索引利用
- 避免对索引列做函数或计算（如 YEAR(OrderDate)=2024），改写为区间条件（如 OrderDate >= ‘2024-01-01’ AND OrderDate < ‘2025-01-01’）。
- 模糊查询优先前缀匹配：LIKE ‘abc%’ 可利用索引；LIKE ‘%abc%’ 通常失效，可改用全文索引或其他检索方案。
- 减少 *SELECT ，仅返回必要列，提升覆盖索引命中率。
- 在 AND/OR 条件中，让高选择性或能显著缩小结果集的条件先出现，利于索引剪枝。
- 优先使用 JOIN 替代相关子查询，便于优化器选择更优计划。
- 分页/Top-N 查询配合 ORDER BY 上的有序索引，避免全表排序。
五 维护监控与自动化
- 定期更新统计信息，保证优化器生成高效执行计划；必要时使用 UPDATE STATISTICS。
- 监控并重建/重组索引以控制碎片；对高变更表设置周期性维护窗口。
- 识别并删除冗余或重复索引，降低写入开销与存储占用。
- 对超大数据量表考虑分区（如按时间/业务键），提升扫描与维护效率。
- 使用 DMV（如 sys.dm_db_index_usage_stats、sys.dm_db_missing_index_details、sys.dm_db_missing_index_columns）识别未使用/缺失索引，避免重复创建。
- 在 Ubuntu 上启用 SQL Server 代理，配置索引与统计的自动化维护作业；使用 SSMS 图形化执行计划或 SHOWPLAN 分析瓶颈并验证优化成效。
