使用SQL Server DMVs优化索引策略

以你之姓@ 2022-07-14 12:13 212阅读 0赞

SQL Server索引的目标是高效，太多太少都是走极端。一旦确定要创建索引，你必须要确保的是负载必须以最小的I/O开销来读取数据。

**聚集索引与主键**

** **

或多或少，SQL Server中的每个表都会有一个聚集索引，通过聚集索引你可以执行高效的数据检索，一般来说，你可以在实际反映数据实际查询的方式的键值上创建聚集索引，或者在自增的整型键值上创建聚集索引（这样做的目的是为了尽可能减少连续碎片），即键值的逻辑顺序决定了表中相应行的物理顺序。创建非聚集索引目的是为了高效检索。请记住，无论如何，绝大部分都应该有一个聚集索引，每一个表都应该有一个主键（可能是聚集索引也可能非聚集索引）。

**覆盖索引**

覆盖索引是指索引包含了一次查询所需要的所有数据列，也就是说，查询或连接条件涉及的每一列都包含在索引中，每一列都是都只是被单纯的检索返回。如果一个索引覆盖了一次查询，就是表示查询优化器可以直接从索引中就可以返回所需数据，而不需要再去执行可怕的表扫描或者通过聚集索引键值查找的方式来获取任何非覆盖数据。覆盖索引无需大量数据库表的“读”操作，因此，通常是最快、最高效的获取数据的方式，这里我们所说是最快的是有一定的依据的，因为即使你认为你在一个有索引的上执行查询，你也无法百分之百确认查询优化器会使用索引来执行查询操作。

**索引应具备高选择性**

** **

索引选择的另一个原则是可识度高，假设数据库表中存在多个数据列同时满足一个索引键值，那么查询优化器可能需要执行表扫描才能返回所需的数据。在实际的开发实践中，我们应尽量避免全表扫描，因为执行全表扫描意味着要读取大量数据（但针对小数据量的表中，全表扫描通常会比索引查询快）。关于索引列的选择，通常情况数据表的第一列应作为考虑对象，但这并不意味着每一个索引都应该以主键开头，作为索引的列必须考虑在实际的业务场景中被频繁检索，更多索引选择原则，可参考：[http://sqlserverpedia.com/wiki/Index\_Selectivity\_and\_Column\_Order.][http_sqlserverpedia.com_wiki_Index_Selectivity_and_Column_Order.]

**不要太多也不能太少**

覆盖查询是一种高效的数据检索方式，但这不意味着你简单的为每一个查询都创建一个索引。如果一个表中属性列很多，并且经常更新，那么大量索引的存在则会在很大程度上影响数据更新的速度，因为SQL Server必须同时更新索引和物理表中数据，这种影响是显而易见的。

你应该根据实际业务场景来评估对应表中的索引个数，如果一个表相对稳定，更新场景相对较少，那么你可以根据实际需要创建满足需求的尽可能索引，但如果一个表经常更新，那么你就应该考虑尽可能的减少索引数量。

**窄索引**

覆盖查询也不意味着，你一次创建一个大的包含所有列的索引，如果你的索引值非常宽，那么出现的情况就是每页数据匹配太少，索引会占据很大的存储空间，执行索引扫描也非常低效，通常情况，在窄索引上执行扫描会更快。

再次说明，合理的创建索引是一件平衡的艺术，一次创建太多单列索引也非明智之举，你的目标应该是使你的索引尽可能覆盖更少的列，但同时可被尽可能多的查询来使用。例如：如果用户需要经常检索用户的LastName属性，那么最好是在LastName列上创建索引，但实际应用中还存在用户可能需要再进一步匹配FirstName，那么在（LastName，FirstName）上创建组合索引或许是个同时满足两种使用场景的不错选择。

**使用索引DMOs**

一般来说，数据库查询优化器会缓存一些与索引使用有关的元数据（一旦SQLServer访问重启，这些数据都会丢失），其实判断数据索引是否合理的前提是你有足够多的查询统计信息来评估分析，比如数据库动态管理视图中的 dm\_db\_index\_usage\_stats可以告诉我们数据库索引的使用信息。

在dm\_db\_index\_usage\_stats中，你可以看到如下信息：

database\_id：database\_id

object\_id：标识索引所属的表或者视图

index\_id：索引ID，0表示堆内存（表中的无聚集索引），1表示（表中的聚集索引），大于1为非聚集索引

user\_seeks：为获取特定数据而使用索引检索次数。

user\_scans：为获取数据而进行的索引叶页面次数。

user\_lookups：聚集索引查询次数，执行标签查询使用的索引次数，非聚集索引实际使用聚集索引键值作为指针指向基础数据行。

user\_updates：索引更新次数。

更多可参考：[http://msdn2.microsoft.com/en-us/library/ms191158.aspx][http_msdn2.microsoft.com_en-us_library_ms191158.aspx]

对于每一个user\_\* columns，都有一个对应的last\_user\_\* column,用以记录上次操作时间，以及一个对应的system\_\* 和last\_system\_\*,用以记录系统调用索引的操作时间。

SELECT  OBJECT_NAME(ddius.[object_id], ddius.database_id) AS [object_name] ,
            ddius.index_id ,
            ddius.user_seeks ,
            ddius.user_scans ,
            ddius.user_lookups ,
            ddius.user_seeks + ddius.user_scans + ddius.user_lookups 
                                                         AS user_reads ,
            ddius.user_updates AS user_writes ,
            ddius.last_user_scan ,
            ddius.last_user_update
    FROM    sys.dm_db_index_usage_stats ddius
    WHERE   ddius.database_id > 4 -- filter out system tables
            AND OBJECTPROPERTY(ddius.OBJECT_ID, 'IsUserTable') = 1
            AND ddius.index_id > 0  -- filter out heaps 
    ORDER BY ddius.user_scans DESC

![Center][]

![Center 1][]

DMV记录的数据是累加的，当系统重启或者索引被删除或者重建时会被更新。当索引被重建或者重新组织，或者即便索引被禁用，索引统计信息仍然有效。若索引自创建之后重未被使用，或自更新使用重未被使用，则sys.dm\_db\_index\_usage\_stats不会包含统计条目信息。

在sys.dm\_db\_index\_usage\_stats中，我们可以通过这个公式计算查询优化器使用索引的次数。

\[user\_seeks\] + \[user\_scans\] + \[user\_lookups\] = \[user reads\]

user\_updates记录的是索引被更新的次数。

seeks是最高效的，其次是lookups,最后是scans，所以理想情况是，统计信息中seeks和lookups的比例较高，但并不是所有的scans都是坏事，如果你需要检索连续的数据，相比多次seek来获取单行记录，按值的范围扫描更为高效。关于这块内容的介绍，可参考如下链接内容：[https://www.simple-talk.com/blogs/a-quick-look-at-dm\_db\_index\_usage\_stats/][https_www.simple-talk.com_blogs_a-quick-look-at-dm_db_index_usage_stats]

确认从未被访问的索引

-- List unused indexes
    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 ;

确认正在被维护但没有被使用的索引

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 ]

当然了，如果简单的根据上述语句来删除索引，推荐的做法要辅以了解这些统计信息的生成时间，比如一个索引是月批量才会用到的，或者是刚刚才新建实现的。

确认统计信息的范围时间段。

SELECT  DATEDIFF(DAY, sd.crdate, GETDATE()) AS days_history
    FROM    sys.sysdatabases sd
    WHERE   sd.[name] = 'tempdb' ;

确认哪些索引效率低下

-- Potentially inefficent non-clustered indexes (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 ;

dm\_db\_index\_usage\_stats可以确认哪些索引不再使用，或者从来没有使用过。

dm\_db\_index\_operational\_stats可以确认那些正在被使用的索引的使用方式

dm\_db\_missing\_index\_details,dm\_db\_missing\_index\_group\_stats可以确认哪些索引消失了。

链接：[https://www.simple-talk.com/sql/performance/tune-your-indexing-strategy-with-sql-server-dmvs/][https_www.simple-talk.com_sql_performance_tune-your-indexing-strategy-with-sql-server-dmvs]

未完（待续）

[http_sqlserverpedia.com_wiki_Index_Selectivity_and_Column_Order.]: http://sqlserverpedia.com/wiki/Index_Selectivity_and_Column_Order
[http_msdn2.microsoft.com_en-us_library_ms191158.aspx]: http://msdn2.microsoft.com/en-us/library/ms191158.aspx
[Center]: /images/20220714/d12a48d4e87b43f7a002791a12f65fa8.png
[Center 1]: /images/20220714/5711f58e42fe4b52a359e9f7b6968549.png
[https_www.simple-talk.com_blogs_a-quick-look-at-dm_db_index_usage_stats]: https://www.simple-talk.com/blogs/a-quick-look-at-dm_db_index_usage_stats/
[https_www.simple-talk.com_sql_performance_tune-your-indexing-strategy-with-sql-server-dmvs]: https://www.simple-talk.com/sql/performance/tune-your-indexing-strategy-with-sql-server-dmvs/