探索SQL Server元数据（三）：索引元数据

发布时间：2020-12-12 09:07:06 所属栏目：MsSql教程来源：网络整理

导读：div class="content-text" 背景在第一篇中我介绍了如何访问元数据，元数据为什么在数据库里面，以及如何使用元数据。介绍了如何查出各种数据库对象的在数据库里面的名字。第二篇，我选择了触发器的主题，因为它是一个能提供很好例子的数据库对象，并且在这

背景

在第一篇中我介绍了如何访问元数据，元数据为什么在数据库里面，以及如何使用元数据。介绍了如何查出各种数据库对象的在数据库里面的名字。第二篇，我选择了触发器的主题，因为它是一个能提供很好例子的数据库对象，并且在这个对象中能够提出问题和解决问题。

本篇我将会介绍元数据中的索引，不仅仅是因为它们本身很重要，更重要的是它们是很好的元数据类型，比如列或者分布统计，这些不是元数据中的对象。

都有哪些索引可以查到?

让我们通过下面的简单语句来看一下都有哪些索引在你的数据库上，代码如下：

SELECT convert(CHAR(),object_schema_name(t.object_ID)+ +object_name(t.object_ID)) AS == AND i.index_id <> ;

结果如下：

如果你过一个多个指定的表，下面的这个查询是更为合理的，需要在上面的例子中增加对象的指定：

AND t.object_id = OBJECT_ID();

每个表中有多少个索引，并展示他们的名字

前面的表并不特别有用，因为无法一眼看到每个表有多少索引，以及它们是什么。下面这个语句可以实现：

SELECT convert(CHAR(),object_schema_name(t.object_ID)+ +object_name(t.object_ID)) AS ELSE +=),TYPE).value(N,N),,,),== AND i.index_id >

我在老的测试数据库上执行这个测试，对象名称比较短。

The_Table The_Count Index_List -------------------- ----------- -------------------------------------------------- dbo.publishers 1 UPKCL_pubind dbo.titles 2 titleind,UPKCL_titleidind dbo.titleauthor 3 auidind,titleidind,UPKCL_taind dbo.stores 1 UPK_storeid dbo.sales 2 titleidind,UPKCL_sales dbo.roysched 1 titleidind dbo.discounts 0 dbo.jobs 1 PK__jobs__6E32B6A51A14E395 dbo.pub_info 1 UPKCL_pubinfo dbo.employee 2 employee_ind,PK_emp_id dbo.authors 2 aunmind,UPKCL_auidind

(11 row(s) affected)

查找没有聚集索引的表

关于索引，您可以找到很多有趣的东西。例如，这里有一种快速查找表的方法，无需使用聚集索引(堆)

--+ +object_name(sys.tables.object_id) AS == ;

每个索引中有多少行在表里面?

通过连接sys.partitions视图，我们可以计算出索引中大约有多少行。我修改了一些代码，关联了sys.extended_properties，这样可以把备注的信息带出来。

--列出每个索引/++OBJECT_NAME(t.object_id) ) ==) ,coalesce(ep.Value,) == ep.Major_Id AND i.Index_Id = Minor_Id AND Class = ;

然后，你可以修改这个代码，让其只是展示每个在索引表中的表有多少行。

++OBJECT_NAME(t.object_id) AS =< GROUP BY t.object_ID,Index_ID;

表中都有很多索引吗?

如果您对某些表具有大量索引感到怀疑，那么可以使用下面查询，该查询告诉您具有超过4个索引和索引计数超过列计数一半的表。它是一种任意选择具有大量索引的表的方法。

SELECT object_schema_name(TheIndexes.Object_ID) + +*=*==>columns/ AND indexes>;

查询更新过的索引缺没有使用过有哪些？

总是有必要找出自上次启动服务器以来没有使用的索引，特别是如果服务器一直在做各种各样的工作时。

--+ + object_name(i.Object_ID) ===) = > --= --= --+ s.user_scans + s.user_seeks,) = --) > ; --Index being updated.

注意：我已经在代码里使用了动态管理视图sys.dm_db_index_usage_stats，这里起到了手机使用信息的作用，之后我们会更详尽的使用换这个对象来说明其作用。

这些索引占用了多少空间?

如果打算知道索引占了多少空间，有许多‘胖’索引，就是包含了很多列，有可能索引中有的列不会出现在任何查询中，这就是浪费了空间。

+ +) AS ,),(sum(a.total_pages) * ) / ) AS ===) =

计算表总的索引空间

让我们看看每个表的总索引空间，以及表中的行数。

+ +) / ) AS *=i.object_ID AND f.index_ID====) =

如何查询表使用索引的各种方式?

发现关于索引的某些属性，通常最好使用属性函数作为快捷方式。

--++object_name(object_id) ) = -- 查询没有索引的表
SELECT object_schema_name(object_id)+'.'+object_name(object_id) as No_Indexes
FROM sys.tables / see whether the table has any index /
WHERE objectproperty(OBJECT_ID,'TableHasIndex') = 0;

-- )查询没有候选键的表
SELECT object_schema_name(object_id)+'.'+object_name(object_id) as No_Candidate_Key
FROM sys.tables/ if no unique constraint then it isn't relational /
WHERE objectproperty(OBJECT_ID,'TableHasUniqueCnst') = 0
AND objectproperty(OBJECT_ID,'TableHasPrimaryKey') = 0;

--查询带有禁用索引的表
SELECT distinct
object_schema_name(object_id)+'.'+object_name(object_id) as Has_Disabled_indexes
FROM sys.indexes / don't leave these lying around /
WHERE is_disabled=1;

那些是对象，那些不是?

sys.objects，您可以找到关于所有公共数据库组件的基本标准信息，如表、视图、同义词、外键、检查约束、键约束、默认约束、服务队列、触发器和过程。sys.objects中都有parent_ID,

下面的查询向您展示了一种查看这些子对象并将其与父母关联的简单方法。

--查询索引父对象（表名）和索引名称, =<>

你会发现索引不是对象。在第一个查询中，返回的object_ID是定义索引的表的ID。

这里的问题是关系是复杂的。约束可以包含几个列，也可以由索引强制。索引可以包含几个列，但是顺序很重要。统计数据还可以包含几个列，也可以与索引相关联。这意sys.indexes,sys.stats and sys.columns不从sys.objects继承。参数和类型也是如此。

如何查询每一个表的每一个索引的每一个列？

最简单的查询方式如下：

++t.name AS The_Table,------===

<pre class="listing prettyprint lang-tsql prettyprinted">?

当然也可以指定特定表，例如：


 ?
也可以汇总上面语句，每个索引汇总成一行，展示所有索引，具体代码如下：
++t.name AS The_Table,----++
         +stuff (-- +==),TYPE).value(,),) +=),

效果如下：

如何查询XML索引?

XML索引被视为索引的扩展。我发现查看其细节的最好方法是为它们构建一个CREATE语句。

SELECT + when secondary_type then + +coalesce(xi.name,)+ ON + object_schema_name(ic.Object_ID)++++col_name(Ic.Object_Id,Ic.Column_Id)+ USING XML INDEX [] FOR COLLATE database_default,+ WITH ( <> THEN ELSE +, + convert(VARCHAR(),xi.Fill_Factor) + + CASE WHEN xi.Ignore_dUp_Key <> THEN ELSE + CASE WHEN xi.Allow_Row_Locks = THEN ELSE + CASE WHEN xi.Allow_Page_Locks = THEN ELSE ,+ ,,) --+ coalesce(+convert(varchar(),Value)+ ,)--===== ep.Major_Id AND ic.Index_Id = Minor_Id AND Class = <> AND ic.index_id>;

上面的查询结果将显示所有基本的XML索引细节作为构建脚本。

元数据中还有其他类型的索引吗?

还有两种比较特殊的索引，一是空间索引，其信息在sys.spatial_index_tessellations 和 sys.spatial_indexes表中。另一个是全文索引，其信息在fulltext_index_fragments,fulltext_index_catalog_usages,fulltext_index_columns 和fulltext_indexes表中保存。

探索索引统计信息

现在，让我们讨论一下分布统计数据或“stats”。每个索引都有一个附加的统计对象，以便查询优化器能够提供一个合适的查询计划。为此，它需要估计数据的“基数”，以确定为任何索引值返回多少行，并使用这些“stats”对象告诉它数据是如何分布的。

可以查询统计信息对象是如何与表进行关联的，语句如下：

SELECT object_schema_name(t.Object_ID) + +===== t.object_id;

当它们与索引相关联时，统计数据继承索引的名称，并使用与索引相同的列。

检查重复的统计信息

通过比较与每个统计信息相关联的列号列表，您可以快速查看同一列或一组列是否有多个统计信息。

SELECT object_schema_name(Object_ID)++object_name(Object_ID) *) Similar,ColumnList ++min(name) -- +==),*) >;

结果如下：

展示了包含重复的统计对象，在本例中是sales.customer表在AccountNumber列上有两个类似的统计对象。

总结

?在数据库中有很多有价值的信息都在索引上。一旦表的数量变大，很容易让表出现一些问题，比如无意中没有聚集索引或主键，或者有重复的索引或不必要的统计信息等。我们通过掌握如何查询这些索引的动态视图后能够快速查询定位使用表的信息，方便我们预防和解决这类问题，这些基础方法已经在DBA和数据库开发的工作中变得越来越重要了，

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!