MSSQL索引优化之确定较优的聚集索引候选键
Tags： MSSQLSQL Server监控索引
👉 本文共约1791个字，系统预计阅读时间或需7分钟。
当我们创建一个表时，很难准确的知道数据会被如何访问。因此，当我们选择创建聚集索引时，自增的ID列通常是我们的首先，其使得行唯一。这可能是一个好的选择，但是，当应用开始运营，数据访问方式的统计就会被统计到，你可能需要回过头来，对你的表做出一些调整，确保你聚集索引对你的应用有益，而不是负担。本文将使用一个简单的方式，在实践中确定聚集索引的候选键。
确定聚集索引候选键
下面是我以实际应用中的一个表为例，我们使用改进的 sp_helpindex 来查看表的索引情况：
可以看到表上有四个索引，聚集索引在 [date] 列上。
为了查看这些索引的使用情况，我们使用如下脚本：
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM sys.dm_db_index_usage_stats AS S INNER JOIN sys.indexes AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECT_NAME(S.[OBJECT_ID]) = 'Member_Stat' |
结果如下：
这是表中索引真实的使用情况。我们可以看到，几乎每次使用 ix_userid索引进行查找（50024910次）时，都会有一次键查找（50016458次），我们很容易得出结论 userid 是这个表的较优的聚集索引候选键。
修改聚集索引准备
当然，要修改聚集索引，我们需要做如下几个方面的准备：
如果有依赖于源聚集索引键列的外键，先准备重建外键的脚本，然后删除掉所有外键
如果聚集索引上有主键约束（创建主键时默认创建聚集索引）
- 有主键约束的话，删除主键脚本：ALTER TABLE TableName DROP CONSTRAINT ConName
- 否则，直接删除聚集索引 DROP INDEX ClusteredIndexName ON TableName
确定索引是否进行分区，及对应的分区方案
查看引用指定表的所有外键信息脚本如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | --查询引用指定表的所有外键信息 SELECT SCHEMA_NAME(fk.schema_id) schemaName , OBJECT_NAME(fk.parent_object_id) parentTableName , OBJECT_NAME(fk.referenced_object_id) referencedTableName , fk.name ForieignKeyName , c.name referencedColumnName ,cf.name parentColumnName FROM sys.foreign_keys fk LEFT JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id LEFT JOIN sys.columns c ON c.object_id = fkc.referenced_object_id AND c.column_id = fkc.referenced_column_id LEFT JOIN sys.columns cf ON cf.object_id = fkc.parent_object_id AND cf.column_id = fkc.parent_column_id WHERE fk.referenced_object_id = OBJECT_ID('member_stat', N'U') |
我们可以找到每个表的外键，使用生成脚本的方式，备份外键信息，为恢复外键准备脚本，当然，如果主键和外键都是不是组合键的情形，我们可以使用下面的脚本生成：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(fk.schema_id), ']') + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id),']') + ' ADD CONSTRAINT ' + QUOTENAME(fk.name, ']') + ' FOREIGN KEY(' + QUOTENAME(cf.name, ']') + ')REFERENCES ' + QUOTENAME(SCHEMA_NAME(o.schema_id), ']') + '.' + QUOTENAME(OBJECT_NAME(fk.referenced_object_id), ']') + '(' + QUOTENAME(c.name, ']') + ')' FROM sys.foreign_keys fk LEFT JOIN sys.objects o ON fk.referenced_object_id = o.object_id LEFT JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id LEFT JOIN sys.columns c ON c.object_id = fkc.referenced_object_id AND c.column_id = fkc.referenced_column_id LEFT JOIN sys.columns cf ON cf.object_id = fkc.parent_object_id AND cf.column_id = fkc.parent_column_id WHERE fk.referenced_object_id = OBJECT_ID('member_stat', N'U') |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
