咨询在用AspNetUsers表聚集主键改非聚集及索引调整的正确方案

核心背景 #

你的AspNetUsers表以GUID类型的Id（nvarchar(128)）作为聚集主键，因GUID的无序性导致页面分裂、性能下降，现在需要新增自增标识列identity_col，将聚集索引转移到该列，同时保留Id作为非聚集主键，且要处理依赖原主键的对象。

关于“已投入使用的表无法操作”的澄清 #

这个说法不准确，只要提前排查并处理好依赖原主键的对象（外键、关联索引等），已上线的表完全可以执行该操作，只是需要选业务低峰期执行，避免锁表影响业务。

完整操作步骤 #

1. 提前排查所有依赖对象

首先要找出所有依赖原主键PK_dbo.AspNetUsers的对象，避免操作失败：

-- 查找依赖AspNetUsers主键的外键
SELECT 
    fk.name AS ForeignKeyName,
    OBJECT_NAME(fk.parent_object_id) AS ReferencingTableName
FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = OBJECT_ID('dbo.AspNetUsers');

-- 查找依赖Id列的非聚集索引（排除主键本身）
SELECT 
    idx.name AS IndexName
FROM sys.indexes idx
JOIN sys.index_columns ic ON idx.object_id = ic.object_id AND idx.index_id = ic.index_id
WHERE idx.object_id = OBJECT_ID('dbo.AspNetUsers')
    AND idx.index_id != 1 -- 排除聚集索引（原主键）
    AND ic.column_id = COLUMNPROPERTY(OBJECT_ID('dbo.AspNetUsers'), 'Id', 'ColumnId');

2. 临时处理依赖对象

• 外键处理：先删除所有查到的外键（后续要重建），示例：

  -- 替换成你的外键名称
  ALTER TABLE dbo.Orders DROP CONSTRAINT FK_Orders_AspNetUsers_UserId;
  GO
  

• 其他依赖索引：如果有非聚集索引是以Id作为唯一键或包含列，无需删除，但若有依赖主键的自定义约束，需临时移除。

3. 执行主键修改与聚集索引创建

-- 移除原聚集主键约束
ALTER TABLE dbo.AspNetUsers DROP CONSTRAINT PK_dbo.AspNetUsers;
GO

-- 将Id重新设置为非聚集主键（保留原主键逻辑）
ALTER TABLE dbo.AspNetUsers ADD CONSTRAINT PK_dbo.AspNetUsers
    PRIMARY KEY NONCLUSTERED (Id);
GO

-- 新增自增标识列，SQL Server会自动为现有行填充连续值
ALTER TABLE dbo.AspNetUsers ADD identity_col INT IDENTITY(1,1);
GO

-- 为identity_col创建聚集索引（这一步是核心，解决原GUID聚集索引的性能问题）
CREATE CLUSTERED INDEX IX_AspNetUsers_identity_col ON dbo.AspNetUsers(identity_col);
GO

4. 恢复依赖对象

重建之前删除的外键，示例：

ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_AspNetUsers_UserId 
    FOREIGN KEY (UserId) REFERENCES dbo.AspNetUsers(Id);
GO

如果之前移除了其他约束或索引，也需要同步重建。

关键注意事项 #

• 操作必须在业务低峰期执行，因为删除/重建主键、创建聚集索引会对表加锁，导致读写阻塞。
• 新增identity_col时，无需手动插入值，SQL Server会自动为现有数据生成从1开始的连续整数，新插入的行也会自动递增。
• 保留Id作为非聚集主键，完全不影响原有业务逻辑（比如用户登录、关联其他表的查询），唯一变化是表的物理存储顺序改为按identity_col排序，提升插入和扫描性能。

内容的提问来源于stack exchange，提问作者Rowan de Graaf