Spiga

sqlserver索引优化

2021-11-14 11:12:25

一、基础概念

1. 聚集索引（Clustered Index）

结构特点

  1. 数据存储:
    • 聚集索引决定了表中数据的物理存储顺序。
    • 表中的每一行数据都会按照聚集索引的键值进行排序存储。
  2. 叶节点:
    • 聚集索引的叶节点包含实际的数据行。
    • 叶节点的数据行是按照聚集索引的键值连续存储的。
  3. 唯一性:
    • 每个表只能有一个聚集索引。
    • 聚集索引的键值必须是唯一的，除非在创建时允许重复键值（通过 ALLOW_ROW_LOCKS 和 ALLOW_PAGE_LOCKS 选项）。
  4. 存储效率:
    • 由于数据按照键值连续存储，聚集索引在范围查询和排序操作中非常高效。
    • 如果表中有大量数据，聚集索引的维护成本相对较高，因为插入、删除和更新操作需要重新排列数据。

使用场景

  1. 范围查询:
    • 适用于需要频繁进行范围查询（如 BETWEEN、>、<）的表。
  2. 排序和分组:
    • 适用于需要频繁进行排序和分组操作的列。
  3. 主键:
    • 通常主键会创建为聚集索引，因为主键需要唯一标识每一行数据，并且主键列通常用于范围查询和排序操作。
  4. 数据访问模式:
    • 适用于访问模式以顺序访问数据为主的场景。

2. 非聚集索引（Non-Clustered Index）

结构特点

  1. 数据存储:
    • 非聚集索引的键值存储的是指向实际数据行的指针。
    • 表中的数据行可以按照插入顺序或其他顺序存储，但非聚集索引提供了一种快速查找数据的方式。
  2. 叶节点:
    • 非聚集索引的叶节点包含指向实际数据行的指针。
    • 叶节点的数据行指针是按照非聚集索引的键值排序的。
  3. 唯一性:
    • 每个表可以有多个非聚集索引。
    • 非聚集索引的键值可以是唯一的，也可以不唯一。
  4. 存储效率:
    • 非聚集索引的维护成本相对较低，因为插入、删除和更新操作不会重新排列实际数据行。
    • 非聚集索引可以提高特定列的查询性能，但对整个表的数据存储没有影响。

使用场景

  1. 等值查询:
    • 适用于需要频繁进行等值查询（如 =、IN）的列。
  2. 范围查询:
    • 虽然非聚集索引也可以用于范围查询，但效率可能不如聚集索引，尤其是在范围较大时。
  3. 排序和分组:
    • 适用于需要频繁进行排序和分组操作的列。
  4. 外键:
    • 外键列通常会创建非聚集索引，以提高外键约束的性能。
  5. 数据访问模式:
    • 适用于访问模式以随机访问数据为主的场景。

3. 索引优化规则

  1. 避免过多索引:
    • 每个表的索引数量不宜过多，过多的索引会增加数据插入、删除和更新的操作成本。
  2. 选择合适的列:
    • 索引应覆盖在查询条件中频繁使用的列（等值列和不等值列）。
    • 索引应覆盖在 JOIN 操作中频繁使用的列。
    • 索引应覆盖在排序和分组操作中频繁使用的列。
  3. 考虑包含列:
    • 使用 INCLUDE 子句在索引中包含不作为键的列，以避免覆盖扫描，提高查询性能。
  4. 定期监控和分析:
    • 定期监控索引的使用情况，使用动态管理视图（如 sys.dm_db_index_usage_stats 和 sys.dm_db_missing_index_details）来获取索引的统计信息。
    • 分析查询计划（使用 SET SHOWPLAN_XML ON; 或 SQL Server Profiler）以了解查询如何使用索引。
  5. 删除未使用的索引:
    • 删除那些从未被查询使用的索引（Total Reads为0），以减少索引维护的开销。
  6. 重构低效索引:
    • 对于那些更新频繁但读取很少的索引（Difference高），评估是否可以删除或重构这些索引以优化性能。
  7. 考虑索引类型:
    • 确保索引类型（如聚集索引、非聚集索引）适合其使用场景。
    • 聚集索引适用于频繁排序和范围查询的表。
    • 非聚集索引适用于频繁查找和过滤的列。
  8. 避免重复索引:
    • 确保没有重复的索引，即不要在相同的列上创建多个索引。
  9. 使用覆盖索引:
    • 创建覆盖索引，确保查询所需的所有列都在索引中，以避免回表查询。
  10. 索引维护:
    • 定期重建或重组索引以保持其效率。
    • 使用 ALTER INDEX ... REBUILD 或 ALTER INDEX ... REORGANIZE 来维护索引。

4. 索引优化步骤

  1. 收集索引使用统计信息:
    • 使用 sys.dm_db_index_usage_stats 收集索引的读取和写入统计信息。
    • 使用 sys.dm_db_missing_index_details 收集缺失索引的详细信息。
  2. 分析查询计划:
    • 使用 SQL Server Profiler 或 Extended Events 捕获实际的查询计划。
    • 使用 SET SHOWPLAN_XML ON; 查看查询计划的详细信息。
  3. 识别无效索引:
    • 查找那些 Total Reads 为0且 Total Writes 高的索引。
    • 查找那些 Difference 高的索引，即更新频繁但读取很少的索引。
  4. 生成优化SQL命令:
    • 生成删除未使用索引的SQL命令。
    • 生成重构低效索引的SQL命令。
    • 生成创建缺失索引的SQL命令。
  5. 备份数据:
    • 在执行任何索引优化操作之前，确保对数据库进行了备份。
  6. 在测试环境中验证:
    • 在测试环境中执行生成的SQL命令，监控性能变化。
    • 确保删除或重构索引不会对查询性能或写操作性能产生负面影响。
  7. 在生产环境中实施:
    • 如果测试结果满意，可以在生产环境中实施这些优化操作。
  8. 监控和调整:
    • 定期监控数据库的性能和索引的使用情况。
    • 根据实际性能表现调整索引策略。

5. 示例优化流程

  1. 收集统计信息:

    SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        i.[index_id] ,
        user_updates AS [Total Writes] ,
        user_seeks + user_scans + user_lookups AS [Total Reads] ,
        user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM    sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]
                                                       AND i.index_id = ddius.index_id
WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY [Difference] DESC ,
         [Total Writes] DESC ,
         [Total Reads] ASC;

  2. 生成删除命令:

    DROP INDEX [idx1] ON [table1];
DROP INDEX [idx2] ON [table2];

  3. 生成重构命令:

    -- 重构 table3.idx3
CREATE INDEX [new_idx3] ON [table3] ([column_a], [column_b])
INCLUDE ([column_c]);
DROP INDEX [idx3] ON [table3];

  4. 生成创建缺失索引的命令:

    -- 假设缺失索引的建议
CREATE INDEX [missing_idx1] ON [table4] ([column_d])
INCLUDE ([column_e]);

  5. 备份数据:

    • 确保对数据库进行了完整备份。

  6. 在测试环境中验证:

    • 执行删除、重构和创建索引的命令。
    • 监控查询性能和写操作性能。

  7. 在生产环境中实施:

    • 如果测试结果满意，执行相同的命令在生产环境中。

  8. 监控和调整:

    • 定期监控数据库性能。
    • 根据实际需求调整索引策略。

二、索引选择度

select (select count (distinct 列)*1.0 from 表)/(select count(1) from 表)

三、缺失索引

SELECT
ddmid.statement,
ddmid.equality_columns,
ddmid. inequality_columns,
ddmid.included_columns,
ddmigs.user_seeks,
ddmigs.user_scans,
ddmigs.avg_total_user_cost,
ddmigs.avg_user_impact
FROM sys.dm_db_missing_index_details AS ddmid
JOIN sys.dm_db_missing_index_groups AS ddmig
ON ddmid.index_handle = ddmig.index_handle
JOIN sys.dm_db_missing_index_group_stats AS ddmigs
ON ddmig.index_group_handle=ddmigs.group_handle

查询结果字段解释

  1. statement: 表示缺失索引所在的表的完整名称，格式为 [数据库名].[架构名].[表名]。
  2. equality_columns: 表示在查询条件中用于等值比较的列。
  3. inequality_columns: 表示在查询条件中用于不等值比较的列。
  4. included_columns: 表示可以包含在索引中的非键列，以避免查询覆盖扫描。
  5. user_seeks: 用户查询中使用该缺失索引的总查找次数。
  6. user_scans: 用户查询中使用该缺失索引的总扫描次数。
  7. avg_total_user_cost: 用户查询中使用该缺失索引的平均总成本。
  8. avg_user_impact: 用户查询中如果使用该缺失索引，可以节省的平均成本百分比。

怎么分析这个SQL查出来的结果

  1. 识别缺失索引:

    • equality_columns 和 inequality_columns: 查看这些列，了解查询条件中哪些列被频繁用于等值和不等值比较。
    • included_columns: 查看这些列，了解哪些非键列可以包含在索引中以避免覆盖扫描。

  2. 评估索引的必要性:

    • user_seeks 和 user_scans: 如果这些值较高，表示查询对这些列的查找和扫描需求较大，创建索引是有益的。
    • avg_total_user_cost: 如果这个值较高，表示当前查询的性能较差，创建索引可以显著提高性能。
    • avg_user_impact: 如果这个值较高（接近100%），表示创建缺失索引可以显著节省查询成本。

示例分析

假设查询结果如下：

| statement | equality_columns | inequality_columns | included_columns | user_seeks | user_scans | avg_total_user_cost | avg_user_impact | | ---------------------- | ---------------- | ------------------ | ---------------- | ---------- | ---------- | ------------------- | --------------- | | [DB].[schema].[table1] | [column1] | [column2] | [column3] | 100 | 50 | 0.05 | 95 | | [DB].[schema].[table2] | [column4] | | [column5] | 200 | 0 | 0.1 | 85 |

  • table1:

    • 等值列: column1
    • 不等值列: column2
    • 包含列: column3
    • user_seeks: 100
    • user_scans: 50
    • avg_total_user_cost: 0.05
    • avg_user_impact: 95

    这表明查询频繁在 column1 上进行等值比较，并在 column2 上进行不等值比较。创建一个包含 column3 的索引可以显著提高性能。

  • table2:

    • 等值列: column4
    • 不等值列: 无
    • 包含列: column5
    • user_seeks: 200
    • user_scans: 0
    • avg_total_user_cost: 0.1
    • avg_user_impact: 85

    这表明查询频繁在 column4 上进行等值比较。创建一个包含 column5 的索引可以显著提高性能。

进一步的步骤

  1. 查看实际查询:
    • 查看实际的查询语句，确保生成的索引与查询条件匹配。
    • 例如，查看查询中是否确实使用了 column1 和 column2 进行等值和不等值比较。
  2. 分析查询计划:
    • 使用SQL Server Profiler或Extended Events捕获实际的查询计划，验证新的索引是否被使用。
    • 例如，执行 SET SHOWPLAN_XML ON; 然后运行实际的查询，查看生成的查询计划。
  3. 监控性能:
    • 在创建索引之前，使用SQL Server的性能监控工具（如SQL Server Management Studio中的性能监视器）监控数据库的性能。
    • 创建索引后，继续监控性能，看看是否有任何变化。

生成创建索引的SQL命令

基于上述示例，生成的创建索引的SQL命令如下：

CREATE INDEX idx_table1_eq_col1_ineq_col2
ON [DB].[schema].[table1] ([column1])
INCLUDE ([column3])
WHERE [column2] [条件];

CREATE INDEX idx_table2_eq_col4
ON [DB].[schema].[table2] ([column4])
INCLUDE ([column5]);

请注意，[条件]需要根据实际查询条件填写。例如，如果查询条件是 column2 > 10，则命令应为：

CREATE INDEX idx_table1_eq_col1_ineq_col2
ON [DB].[schema].[table1] ([column1])
INCLUDE ([column3])
WHERE [column2] > 10;

总结

通过分析查询结果中的各个字段，我们可以确定哪些索引是缺失的，并评估它们是否可以显著提高查询性能。然后，生成相应的创建索引的SQL命令，并在测试环境中验证其效果。如果测试结果满意，可以在生产环境中实施这些更改。

四、无效索引

SELECT i.name,ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates
FROM sys.dm_db_index_usage_stats AS ddius
JOIN sys.indexes AS i ON ddius.index_id = i.index_id AND ddius.object_id = i.object_id
ORDER BY ddius.user_seeks

要分析上述SQL查询的结果，首先需要了解查询的目的是什么。这个查询的目的是从SQL Server的动态管理视图（DMV）sys.dm_db_index_usage_stats中获取索引的使用统计信息，并将其与索引的元数据信息（来自sys.indexes）相结合。通过这个查询，我们可以查看每个索引的使用情况，包括：

  • ddius.user_seeks: 用户查询中使用的索引查找次数。
  • ddius.user_scans: 用户查询中使用的索引扫描次数。
  • ddius.user_lookups: 用户查询中使用的索引查找次数（针对非聚集索引）。
  • ddius.user_updates: 索引更新的次数（插入、删除、修改）。

无效索引分析：

  1. 低使用次数：如果某些索引的user_seeks、user_scans、user_lookups都非常低，而user_updates相对较高，那么这些索引可能是无效的。这表明虽然数据在这些索引上频繁更新，但这些索引很少被用来加速查询。
  2. 高更新次数：高更新次数意味着索引需要频繁地进行维护，这会增加数据库的负担。如果一个索引很少被使用但更新频繁，它可能是一个无效的索引，因为它对查询性能没有提供足够的好处来弥补维护的成本。
  3. 扫描次数高：如果user_scans很高，而user_seeks和user_lookups相对较低，这可能意味着查询条件没有有效地利用该索引。扫描整个索引比单独查找要耗费更多的时间和资源，因此高扫描次数也可能是无效索引的标志。
  4. 对比查询与更新：理想情况下，索引应该被频繁地用于查询查找和扫描，同时也不应该被过度地更新。对于那些更新次数很高但查找和扫描次数极低的索引，应考虑删除或重构这些索引。

进一步的步骤：

  • 深入分析查询：对于那些低使用次数的索引，查看相关的查询语句，看看是否可以优化查询条件，或者是否可以重构索引以更好地支持查询。
  • 监控系统性能：监控数据库的性能，看看删除或重构某些索引是否会对整体性能产生积极的影响。
  • 使用SQL Server Profiler或Extended Events：这些工具可以帮助我们跟踪数据库的操作，找出哪些查询最经常执行以及它们是如何使用索引的。

通过这些步骤，我们可以更好地理解哪些索引是有效的，哪些可能是无效的，并据此做出优化决策。

五、未被使用的索引

SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,
        i.name
FROM    sys.indexes AS i
        INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id] 
WHERE   i.index_id NOT IN ( SELECT  ddius.index_id
                            FROM    sys.dm_db_index_usage_stats AS ddius
                            WHERE   ddius.[object_id] = i.[object_id]
                                    AND i.index_id = ddius.index_id
                                    AND database_id = DB_ID() )
AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC;
  1. 识别未使用的索引:
    • 如果查询结果显示某个索引的 user_seeks、user_scans 和 user_lookups 都为0，那么这个索引就是未使用的。
    • 未使用的索引可能会占用存储空间，并在数据插入、删除和更新时增加维护成本。
  2. 评估索引的必要性:
    • 叶节点操作次数: 检查索引的叶节点操作次数（leaf_insert_count、leaf_delete_count、leaf_update_count）。如果这些操作频繁，索引可能仍然有价值。
    • 非叶节点操作次数: 检查索引的非叶节点操作次数（nonleaf_insert_count、nonleaf_delete_count、nonleaf_update_count）。如果这些操作频繁，索引可能仍然有价值。
    • 索引定义: 查看索引的具体定义，了解它所覆盖的列。这可以通过查询 sys.index_columns 和 sys.columns 来实现。
  3. 潜在的性能影响:
    • 删除索引: 对于那些从未被使用的索引，删除它们可以减少索引维护的开销，从而提高写操作的性能。
    • 保留索引: 对于那些虽然没有被查询使用但被频繁更新的索引，保留这些索引可能有助于加速这些更新操作。
  4. 生成删除命令:
    • 查询结果会生成相应的删除索引的SQL命令，例如 drop index [索引名] on [表名];。
  5. 备份数据:
    • 在执行删除操作之前，确保对数据库进行了备份，以防止意外的数据丢失或性能问题。
  6. 测试删除影响:
    • 在测试环境中执行这些删除命令，监控性能变化，看看是否对查询性能或写操作性能产生了积极的影响。

进一步的步骤

  1. 查看索引定义:
    • 使用 sp_helpindex 或 sys.indexes 和 sys.index_columns 查看索引的具体定义，了解它所覆盖的列。
    • 例如，执行 sp_helpindex table1 可以查看 idx1 索引的详细信息。
  2. 分析查询计划:
    • 使用 SQL Server Profiler 或 Extended Events 捕获实际的查询计划，验证这些索引是否被使用。
    • 例如，执行 SET SHOWPLAN_XML ON; 然后运行实际的查询，查看生成的查询计划。
  3. 监控性能:
    • 在删除索引之前，使用 SQL Server 的性能监控工具（如 SQL Server Management Studio 中的性能监视器）监控数据库的性能。
    • 删除索引后，继续监控性能，看看是否有任何变化。

总结

通过分析查询结果中的各个字段，我们可以确定哪些索引是未使用的，并评估它们是否可以安全删除以优化数据库性能。然后，生成相应的删除索引的SQL命令，并在测试环境中验证其效果。如果测试结果满意，可以在生产环境中实施这些更改。

六、需要维护但是未被用过的索引

SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] ,
        ddius.[user_updates] AS [user_writes] ,
        SUM(SP.rows) AS [total_rows]
FROM    sys.dm_db_index_usage_stats ddius
        INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]
                                    AND i.[index_id] = ddius.[index_id]
        INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id] AND SP.[index_id] = ddius.[index_id]
        INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
        INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
WHERE   ddius.[database_id] = DB_ID() -- current database only 
        AND OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.[index_id] > 0
GROUP BY su.[name] ,
        o.[name] ,
        i.[name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] ,
        ddius.[user_updates]
HAVING  ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
        su.[name] ,
        o.[name] ,
        i.[name]

查询结果字段解释

  1. statement: 表示索引所在的表的完整名称，格式为 [数据库名].[架构名].[表名]。
  2. index_name: 索引的名称。
  3. user_reads: 用户查询中使用该索引的总读取次数（包括user_seeks、user_scans和user_lookups）。
  4. user_writes: 用户查询中使用该索引的更新次数（包括插入、删除和修改）。
  5. total_rows: 表中的总行数。

怎么分析这个SQL查出来的结果

  1. 识别未使用的索引:
    • user_reads为0: 这些索引从未被用于用户查询（user_seeks、user_scans和user_lookups均为0），可以被认为是无效的。
    • user_writes较高: 即使这些索引没有被查询使用，但如果它们在频繁地被更新，删除这些索引可能会对性能产生负面影响。
  2. 评估索引的必要性:
    • total_rows: 查看表中的总行数。如果表中的行数很少，索引的维护成本可能相对较低。
    • 索引定义: 查看索引的具体定义，了解它所覆盖的列。这可以通过查询 sys.index_columns 和 sys.columns 来实现。
  3. 潜在的性能影响:
    • 删除索引: 对于那些从未被使用的索引（user_reads为0），删除它们可以减少索引维护的开销，从而提高写操作的性能。
    • 保留索引: 对于那些虽然没有被查询使用但被频繁更新的索引，保留这些索引可能有助于加速这些更新操作。
  4. 生成删除命令:
    • 查询结果会生成相应的删除索引的SQL命令，例如 drop index [索引名] on [数据库名].[架构名].[表名];。
  5. 备份数据:
    • 在执行删除操作之前，确保对数据库进行了备份，以防止意外的数据丢失或性能问题。
  6. 测试删除影响:
    • 在测试环境中执行这些删除命令，监控性能变化，看看是否对查询性能或写操作性能产生了积极的影响。

示例分析

假设查询结果如下：

| statement | index_name | user_reads | user_writes | total_rows | | ---------------------- | ---------- | ---------- | ----------- | ---------- | | [DB].[schema].[table1] | idx1 | 0 | 100 | 500 | | [DB].[schema].[table2] | idx2 | 0 | 50 | 200 | | [DB].[schema].[table3] | idx3 | 0 | 0 | 100 |

  • table1.idx1: 这个索引虽然没有被查询使用（user_reads为0），但它被频繁更新（user_writes为100）。删除这个索引可能会影响写操作的性能，因此需要进一步评估。
  • table2.idx2: 这个索引也没有被查询使用（user_reads为0），但它被更新的次数相对较少（user_writes为50）。删除这个索引可能对性能影响较小，可以考虑删除。
  • table3.idx3: 这个索引既没有被查询使用（user_reads为0），也没有被更新（user_writes为0），可以安全删除。

进一步的步骤

  1. 查看索引定义:
    • 使用 sp_helpindex 或 sys.indexes 和 sys.index_columns 查看索引的具体定义，了解它所覆盖的列。
    • 例如，执行 sp_helpindex table1 可以查看 idx1 索引的详细信息。
  2. 分析查询计划:
    • 使用 SQL Server Profiler 或 Extended Events 捕获实际的查询计划，验证这些索引是否被使用。
    • 例如，执行 SET SHOWPLAN_XML ON; 然后运行实际的查询，查看生成的查询计划。
  3. 监控性能:
    • 在删除索引之前，使用 SQL Server 的性能监控工具（如 SQL Server Management Studio 中的性能监视器）监控数据库的性能。
    • 删除索引后，继续监控性能，看看是否有任何变化。

生成删除索引的SQL命令

基于上述示例，生成的删除索引的SQL命令如下：

-- 对于 table1.idx1，保留索引
-- DROP INDEX [idx1] ON [table1];

-- 对于 table2.idx2，可以考虑删除
DROP INDEX [idx2] ON [table2];

-- 对于 table3.idx3，可以安全删除
DROP INDEX [idx3] ON [table3];

总结

通过分析查询结果中的各个字段，我们可以确定哪些索引是未使用的，并评估它们是否可以安全删除以优化数据库性能。然后，生成相应的删除索引的SQL命令，并在测试环境中验证其效果。如果测试结果满意，可以在生产环境中实施这些更改。

七、可能不高效的非聚集索引 (writes > reads)

SELECT  OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
        i.name AS [Index Name] ,
        i.index_id ,
        user_updates AS [Total Writes] ,
        user_seeks + user_scans + user_lookups AS [Total Reads] ,
        user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM    sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]
                                                       AND i.index_id = ddius.index_id
WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY [Difference] DESC ,
        [Total Writes] DESC ,
        [Total Reads] ASC;

查询结果字段解释

  1. Table Name: 表示索引所在的表的名称。
  2. Index Name: 索引的名称。
  3. index_id: 索引的ID。
  4. Total Writes: 用户查询中使用该索引的总更新次数（包括插入、删除和修改）。
  5. Total Reads: 用户查询中使用该索引的总读取次数（包括user_seeks、user_scans和user_lookups）。
  6. Difference: 更新次数与读取次数的差值，即 user_updates - (user_seeks + user_scans + user_lookups)。

排序依据

  • Difference: 按照更新次数与读取次数的差值降序排列，这样可以首先看到那些更新频繁但读取较少的索引。
  • Total Writes: 按照总更新次数降序排列，进一步确认哪些索引更新非常频繁。
  • Total Reads: 按照总读取次数升序排列，确认哪些索引几乎没有被读取。

怎么分析这个SQL查出来的结果

  1. 识别更新频繁但读取较少的索引:
    • Difference: 查看这个字段，数值越大表示更新频繁但读取较少的程度越高。
    • Total Writes: 查看这个字段，数值越大表示索引更新越频繁。
    • Total Reads: 查看这个字段，数值越小表示索引几乎没有被读取。
  2. 评估索引的必要性:
    • 索引定义: 查看索引的具体定义，了解它所覆盖的列。这可以通过查询 sys.index_columns 和 sys.columns 来实现。
    • 表的总行数: 查看表中的总行数。行数较多的表，即使读取次数较少，索引的维护成本也可能较高。
    • 索引类型: 检查索引的类型，了解其用途。例如，某些索引可能用于约束（主键、唯一键等），这些索引通常不能删除。
  3. 潜在的性能影响:
    • 删除索引: 对于那些更新频繁但读取很少的索引，删除这些索引可以减少索引维护的开销，从而提高写操作的性能。
    • 保留索引: 对于那些虽然没有被查询使用但被频繁更新的索引，保留这些索引可能有助于加速这些更新操作。如果这些索引是必要的，可以考虑重构索引或优化其使用。
  4. 生成删除或重构索引的SQL命令:
    • 查询结果会生成相应的删除索引的SQL命令，例如 drop index [索引名] on [表名];。
    • 如果决定重构索引，可以生成相应的创建新索引的SQL命令。
  5. 备份数据:
    • 在执行删除或重构操作之前，确保对数据库进行了备份，以防止意外的数据丢失或性能问题。
  6. 测试删除或重构影响:
    • 在测试环境中执行这些删除或重构命令，监控性能变化，看看是否对查询性能或写操作性能产生了积极的影响。
    • 如果测试结果满意，可以在生产环境中实施这些更改。

示例分析

假设查询结果如下：

| Table Name | Index Name | index_id | Total Writes | Total Reads | Difference | | ---------- | ---------- | -------- | ------------ | ----------- | ---------- | | table1 | idx1 | 2 | 1000 | 50 | 950 | | table2 | idx2 | 3 | 800 | 10 | 790 | | table3 | idx3 | 2 | 500 | 100 | 400 |

  • table1.idx1:

    • Total Writes: 1000
    • Total Reads: 50
    • Difference: 950

    这表明索引 idx1 被频繁更新（1000次），但在查询中几乎没有被使用（50次）。删除这个索引可能对写操作的性能有显著提高。

  • table2.idx2:

    • Total Writes: 800
    • Total Reads: 10
    • Difference: 790

    这表明索引 idx2 被频繁更新（800次），但在查询中几乎没有被使用（10次）。删除这个索引可能对写操作的性能有显著提高。

  • table3.idx3:

    • Total Writes: 500
    • Total Reads: 100
    • Difference: 400

    这表明索引 idx3 被频繁更新（500次），但在查询中被使用（100次）。删除这个索引可能会影响查询性能，因此需要进一步评估。

进一步的步骤

  1. 查看索引定义:
    • 使用 sp_helpindex 或 sys.indexes 和 sys.index_columns 查看索引的具体定义，了解它所覆盖的列。
    • 例如，执行 sp_helpindex table1 可以查看 idx1 索引的详细信息。
  2. 分析查询计划:
    • 使用 SQL Server Profiler 或 Extended Events 捕获实际的查询计划，验证这些索引是否被使用。
    • 例如，执行 SET SHOWPLAN_XML ON; 然后运行实际的查询，查看生成的查询计划。
  3. 监控性能:
    • 在删除或重构索引之前，使用 SQL Server 的性能监控工具（如 SQL Server Management Studio 中的性能监视器）监控数据库的性能。
    • 删除或重构索引后，继续监控性能，看看是否有任何变化。

生成删除或重构索引的SQL命令

基于上述示例，生成的删除或重构索引的SQL命令如下：

-- 对于 table1.idx1，可以考虑删除
DROP INDEX [idx1] ON [table1];

-- 对于 table2.idx2，可以考虑删除
DROP INDEX [idx2] ON [table2];

-- 对于 table3.idx3，需要进一步评估是否保留
-- 如果决定删除
DROP INDEX [idx3] ON [table3];

-- 如果决定重构
CREATE INDEX [new_idx3] ON [table3] ([column_a], [column_b])
INCLUDE ([column_c]);
DROP INDEX [idx3] ON [table3];

总结

通过分析查询结果中的各个字段，我们可以确定哪些索引是更新频繁但读取较少的，并评估它们是否可以安全删除或重构以优化数据库性能。然后，生成相应的删除或重构索引的SQL命令，并在测试环境中验证其效果。如果测试结果满意，可以在生产环境中实施这些更改。

八、没有用于用户查询的索引

--没有用于用户查询的索引
SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] ,
        ddius.[user_updates] AS [user_writes] ,
        ddios.[leaf_insert_count] ,
        ddios.[leaf_delete_count] ,
        ddios.[leaf_update_count] ,
        ddios.[nonleaf_insert_count] ,
        ddios.[nonleaf_delete_count] ,
        ddios.[nonleaf_update_count],
		'drop index '+ i.[name]+' on   '+'[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']'+';'
FROM    sys.dm_db_index_usage_stats ddius
        INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]
                                    AND i.[index_id] = ddius.[index_id]
        INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id]
                                        AND SP.[index_id] = ddius.[index_id]
        INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
        INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
        INNER JOIN sys.[dm_db_index_operational_stats](DB_ID(), NULL, NULL,
                                                       NULL) AS ddios ON ddius.[index_id] = ddios.[index_id]
                                                              AND ddius.[object_id] = ddios.[object_id]
                                                              AND SP.[partition_number] = ddios.[partition_number]
                                                              AND ddius.[database_id] = ddios.[database_id]
WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.[index_id] > 0
        AND ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
        su.[name] ,
        o.[name] ,
        i.[name]

查询结果字段解释

  1. statement: 表示索引所在的表的完整名称，格式为 [数据库名].[架构名].[表名]。
  2. index_name: 索引的名称。
  3. user_reads: 用户查询中使用该索引的总次数（包括user_seeks、user_scans和user_lookups）。
  4. user_writes: 用户查询中使用该索引的更新次数（包括插入、删除和修改）。
  5. leaf_insert_count: 在索引的叶子节点上插入的次数。
  6. leaf_delete_count: 在索引的叶子节点上删除的次数。
  7. leaf_update_count: 在索引的叶子节点上更新的次数。
  8. nonleaf_insert_count: 在索引的非叶子节点上插入的次数。
  9. nonleaf_delete_count: 在索引的非叶子节点上删除的次数。
  10. nonleaf_update_count: 在索引的非叶子节点上更新的次数。
  11. drop index 命令: 用于删除这些无效索引的SQL命令。

排序依据

  • user_writes: 按照更新次数降序排列，这样可以首先看到那些更新频率最高的无效索引。
  • su.[name], o.[name], i.[name]: 进一步按架构名、表名和索引名排序，便于管理。

怎么分析这个SQL查出来的结果

  1. 识别无效索引:
    • user_reads为0: 这些索引从未被用于用户查询，可以被认为是无效的。
    • user_writes较高: 即使这些索引没有被查询使用，但如果它们在频繁地被更新，删除这些索引可能会对性能产生负面影响。
  2. 评估索引的必要性:
    • 叶节点操作次数: 检查leaf_insert_count、leaf_delete_count和leaf_update_count。如果叶节点的更新操作频繁，索引可能仍然有价值，因为它用于加速这些更新操作。
    • 非叶节点操作次数: 检查nonleaf_insert_count、nonleaf_delete_count和nonleaf_update_count。如果非叶节点的更新操作频繁，索引可能仍然有价值。
  3. 潜在的性能影响:
    • 删除索引: 对于那些既没有被查询使用也没有被频繁更新的索引，删除它们可以减少索引维护的开销，从而提高写操作的性能。
    • 保留索引: 对于那些虽然没有被查询使用但被频繁更新的索引，保留它们可能有助于加速这些更新操作。
  4. 执行删除命令:
    • 生成删除脚本: 查询结果会生成相应的删除索引的SQL命令，例如 drop index [索引名] on [数据库名].[架构名].[表名];。
    • 备份数据: 在执行删除操作之前，确保对数据库进行了备份，以防止意外的数据丢失或性能问题。
    • 测试删除影响: 在测试环境中执行这些删除命令，监控性能变化，看看是否对查询性能或写操作性能产生了积极的影响。

示例分析

假设查询结果如下：

| statement | index_name | user_reads | user_writes | leaf_insert_count | leaf_delete_count | leaf_update_count | nonleaf_insert_count | nonleaf_delete_count | nonleaf_update_count | drop index 命令 | | ---------------------- | ---------- | ---------- | ----------- | ----------------- | ----------------- | ----------------- | -------------------- | -------------------- | -------------------- | ------------------------------------------ | | [DB].[schema].[table1] | idx1 | 0 | 100 | 50 | 30 | 20 | 10 | 5 | 2 | drop index idx1 on [DB].[schema].[table1]; | | [DB].[schema].[table2] | idx2 | 0 | 50 | 20 | 20 | 10 | 5 | 5 | 1 | drop index idx2 on [DB].[schema].[table2]; |

  • table1.idx1: 这个索引虽然没有被用于查询（user_reads为0），但它被频繁更新（user_writes为100）。删除这个索引可能会影响写操作的性能，因此需要进一步评估。
  • table2.idx2: 这个索引也没有被用于查询（user_reads为0），但它被更新的次数相对较少（user_writes为50）。删除这个索引可能对性能影响较小，可以考虑删除。

进一步的步骤

  1. 查看索引定义:
    • 使用sp_helpindex或sys.indexes查看索引的具体定义，了解它所覆盖的列。
    • 例如，执行 sp_helpindex table1 可以查看 idx1 索引的详细信息。
  2. 分析查询计划:
    • 使用SQL Server Profiler或Extended Events捕获实际的查询计划，查看哪些索引被查询使用。
    • 例如，执行 SET SHOWPLAN_XML ON; 然后运行实际的查询，查看生成的查询计划。
  3. 监控性能:
    • 在删除索引之前，使用SQL Server的性能监控工具（如SQL Server Management Studio中的性能监视器）监控数据库的性能。
    • 删除索引后，继续监控性能，看看是否有任何变化。

通过以上步骤，我们可以更全面地评估哪些索引是无效的，并决定是否应该删除它们以优化数据库性能。