☰

什么时候索引不值得更新

tag-icon • tag-icon sql-server performance indexing
什么时候索引不值得更新

是否存在一个可接受的读写比率，使得索引有价值，或者它没有那么简单？

我正在使用这个：

WITH    UnusedIndexQuery ( Object_ID, ObjectName, IndexName, Index_ID, Reads, Writes, Rows )
          AS ( SELECT
                s.object_id ,
                objectname = OBJECT_NAME(s.OBJECT_ID) ,
                indexname = i.name ,
                i.index_id ,
                reads = user_seeks + user_scans + user_lookups ,
                writes = user_updates ,
                p.rows
               FROM
                sys.dm_db_index_usage_stats s
               JOIN 
                sys.indexes i
               ON
                i.index_id = s.index_id
                AND s.OBJECT_ID = i.OBJECT_ID
               JOIN 
                sys.partitions p
               ON
                p.index_id = s.index_id
                AND s.OBJECT_ID = p.OBJECT_ID
               WHERE
                OBJECTPROPERTY(s.OBJECT_ID, 'IsUserTable') = 1
                AND s.database_id = DB_ID()
                AND i.type_desc = 'nonclustered'
                AND i.is_primary_key = 0
                AND i.is_unique_constraint = 0
                AND p.rows > 10000
             ),
        IndexSizes ( schemaname, tablename, object_id, indexname, index_id, indextype, indexsizekb, indexsizemb, indexsizegb )
          AS ( SELECT
                sys_schemas.name AS SchemaName ,
                sys_objects.name AS TableName ,
                sys_objects.[object_id] AS object_id ,
                sys_indexes.name AS IndexName ,
                sys_indexes.index_id AS index_id ,
                sys_indexes.type_desc AS IndexType ,
                partition_stats.used_page_count * 8 AS IndexSizeKB ,
                CAST(partition_stats.used_page_count * 8 / 1024.00 AS DECIMAL(10,
                                                              3)) AS IndexSizeMB ,
                CAST(partition_stats.used_page_count * 8 / 1048576.00 AS DECIMAL(10,
                                                              3)) AS IndexSizeGB
               FROM
                sys.dm_db_partition_stats partition_stats
               INNER JOIN sys.indexes sys_indexes
               ON
                partition_stats.[object_id] = sys_indexes.[object_id]
                AND partition_stats.index_id = sys_indexes.index_id
                AND sys_indexes.type_desc <> 'HEAP'
               INNER JOIN sys.objects sys_objects
               ON
                sys_objects.[object_id] = partition_stats.[object_id]
               INNER JOIN sys.schemas sys_schemas
               ON
                sys_objects.[schema_id] = sys_schemas.[schema_id]
                AND sys_schemas.name <> 'SYS'
             )
    SELECT
        [IndexSizes].[tablename] ,
        [IndexSizes].[indexname] ,
        [IndexSizes].[indextype] ,
        [IndexSizes].[indexsizekb] ,
        [IndexSizes].[indexsizemb] ,
        [IndexSizes].[indexsizegb] ,
        UnusedIndexQuery.Reads ,
        UnusedIndexQuery.Writes ,
        CAST(CASE WHEN [Reads] = 0 THEN 1
                  ELSE [Reads]
             END / CASE WHEN [Writes] = 0 THEN 1
                        ELSE writes
                   END AS NVARCHAR(8)) + ':1' AS [Benefit Ratio (Read:Write)] ,
        UnusedIndexQuery.[Rows]
    FROM
        UnusedIndexQuery
    INNER JOIN IndexSizes
    ON  UnusedIndexQuery.object_id = IndexSizes.object_id
        AND UnusedIndexQuery.index_id = IndexSizes.index_id
    ORDER BY
        CASE WHEN [Reads] = 0 THEN 1
             ELSE [Reads]
        END / CASE WHEN [Writes] = 0 THEN 1
                   ELSE writes
              END ,
        reads ,
        [Writes] DESC ,
        [indexsizemb] DESC

了解我的指数的效益状况。

在结果的两端，我很清楚 - 1,000,000 次读取和 0 次写入 = 良好的索引，可加快数据检索速度，1,000,000 次写入和 0 次读取意味着我们正在维护零引用的索引。
我不确定的是哪里的活动显示得更平衡 - 我应该在哪里进行削减并开始删除索引？

谢谢

乔纳森

答案1

我认为仅仅根据读/写的次数来做决定是没有意义的（当然，除非你的读取次数==0，但那样的话你为什么还需要这个表呢？:-)）。

考虑一下：

  • 即使读取次数很少，如果没有索引，这些读取也可能非常耗时
  • 读取可能比写入更耗时，因此尽管写入性能有所降低，索引仍然是值得的
  • 写入性能不一定会受到影响；我认为大多数现代 DBMS 可以延迟更新索引，直到需要更新为止，因此，例如，连续执行许多 INSERT 操作只会导致一次索引更新

简而言之，一如既往，唯一的建议是：优化之前先进行分析。没有简单的捷径 :-/。

答案2

你想实现什么？你想提高 I/O 性能吗？你的磁盘空间是否不足？过早优化是万恶之源！

坚持快速取胜，例如 0 次读取和 100,000,000 次写入。其他一切都是权衡。如果您的服务器有空间，但没有磁盘空间，那么从最低的读取与写入比率开始向后工作并密切关注性能。

探索其他替代方案可能更明智，例如优化程序/查询、添加页面压缩、添加磁盘空间/RAM 等。

相关内容