如何应对500万用户？ ASP.NET 身份

我正在运行一个 ASP.NET mvc5 应用程序，目前有 500 万用户。它托管在 Azure 云中。对于身份验证，我使用 EntityFramework 的 Asp.Net Identity。

但是，用户越多，注册功能就越慢。我尝试缩放数据库，但结果仍然相同。新用户注册大约需要 6-7 秒。

我还尝试搜索如何提高身份系统的性能，但我找不到任何相关的内容。

我真的很想听听是否有人知道如何提高它的性能。

更新：我在正在搜索的字段上有索引，而且我在 Azure 中选择的数据库订阅是具有 200 个 DTU 的 P3 SQL 数据库。

我分析了数据库，发现了一个可疑的选择查询。 我删除了一些投影并将它们替换为“....”，这样它就不会太长，您可以看到查询的内容。

SELECT 
    [UnionAll2].[Gender] AS [C1], 
    ....
    [UnionAll2].[UserName] AS [C27], 
    [UnionAll2].[C1] AS [C28], 
    [UnionAll2].[UserId] AS [C29], 
    [UnionAll2].[RoleId] AS [C30], 
    [UnionAll2].[UserId1] AS [C31], 
    [UnionAll2].[C2] AS [C32], 
    [UnionAll2].[C3] AS [C33], 
    [UnionAll2].[C4] AS [C34], 
    [UnionAll2].[C5] AS [C35], 
    [UnionAll2].[C6] AS [C36], 
    [UnionAll2].[C7] AS [C37], 
    [UnionAll2].[C8] AS [C38], 
    [UnionAll2].[C9] AS [C39]
    FROM  (SELECT 
        CASE WHEN ([Extent2].[UserId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
        [Limit1].[Gender] AS [Gender], 
        ....
        [Limit1].[UserName] AS [UserName], 
        [Extent2].[UserId] AS [UserId], 
        [Extent2].[RoleId] AS [RoleId], 
        [Extent2].[UserId] AS [UserId1], 
        CAST(NULL AS int) AS [C2], 
        CAST(NULL AS varchar(1)) AS [C3], 
        CAST(NULL AS varchar(1)) AS [C4], 
        CAST(NULL AS varchar(1)) AS [C5], 
        CAST(NULL AS varchar(1)) AS [C6], 
        CAST(NULL AS varchar(1)) AS [C7], 
        CAST(NULL AS varchar(1)) AS [C8], 
        CAST(NULL AS varchar(1)) AS [C9]
        FROM   (SELECT TOP (1) 
            [Extent1].[Id] AS [Id], 
            ....
            [Extent1].[UserName] AS [UserName]
            FROM [dbo].[Users] AS [Extent1]
            WHERE ((UPPER([Extent1].[UserName])) = (UPPER(@p__linq__0))) OR ((UPPER([Extent1].[UserName]) IS NULL) AND (UPPER(@p__linq__0) IS NULL)) ) AS [Limit1]
        LEFT OUTER JOIN [dbo].[UserRoles] AS [Extent2] ON [Limit1].[Id] = [Extent2].[UserId]
    UNION ALL
        SELECT 
        2 AS [C1], 
        [Limit2].[Gender] AS [Gender], 
        ....
        [Limit2].[UserName] AS [UserName], 
        CAST(NULL AS varchar(1)) AS [C2], 
        CAST(NULL AS varchar(1)) AS [C3], 
        CAST(NULL AS varchar(1)) AS [C4], 
        [Extent4].[Id] AS [Id1], 
        [Extent4].[UserId] AS [UserId], 
        [Extent4].[ClaimType] AS [ClaimType], 
        [Extent4].[ClaimValue] AS [ClaimValue], 
        CAST(NULL AS varchar(1)) AS [C5], 
        CAST(NULL AS varchar(1)) AS [C6], 
        CAST(NULL AS varchar(1)) AS [C7], 
        CAST(NULL AS varchar(1)) AS [C8]
        FROM   (SELECT TOP (1) 
            [Extent3].[Id] AS [Id], 
            ....
            [Extent3].[UserName] AS [UserName]
            FROM [dbo].[Users] AS [Extent3]
            WHERE ((UPPER([Extent3].[UserName])) = (UPPER(@p__linq__0))) OR ((UPPER([Extent3].[UserName]) IS NULL) AND (UPPER(@p__linq__0) IS NULL)) ) AS [Limit2]
        INNER JOIN [dbo].[UserClaims] AS [Extent4] ON [Limit2].[Id] = [Extent4].[UserId]
    UNION ALL
        SELECT 
        3 AS [C1], 
        [Limit3].[Gender] AS [Gender], 
        ....
        [Limit3].[UserName] AS [UserName], 
        CAST(NULL AS varchar(1)) AS [C2], 
        CAST(NULL AS varchar(1)) AS [C3], 
        CAST(NULL AS varchar(1)) AS [C4], 
        CAST(NULL AS int) AS [C5], 
        CAST(NULL AS varchar(1)) AS [C6], 
        CAST(NULL AS varchar(1)) AS [C7], 
        CAST(NULL AS varchar(1)) AS [C8], 
        [Extent6].[LoginProvider] AS [LoginProvider], 
        [Extent6].[ProviderKey] AS [ProviderKey], 
        [Extent6].[UserId] AS [UserId], 
        [Extent6].[UserId] AS [UserId1]
        FROM   (SELECT TOP (1) 
            [Extent5].[Id] AS [Id], 
            ....
            [Extent5].[UserName] AS [UserName]
            FROM [dbo].[Users] AS [Extent5]
            WHERE ((UPPER([Extent5].[UserName])) = (UPPER(@p__linq__0))) OR ((UPPER([Extent5].[UserName]) IS NULL) AND (UPPER(@p__linq__0) IS NULL)) ) AS [Limit3]
        INNER JOIN [dbo].[UserLogins] AS [Extent6] ON [Limit3].[Id] = [Extent6].[UserId]) AS [UnionAll2]
    ORDER BY [UnionAll2].[Id] ASC, [UnionAll2].[C1] ASC  

我的 EntityFramework 用户 POCO 类

    public class User : IdentityUser
    {
        [Index]
        public DateTime Created { get; set; }
        [Index(IsUnique = true), MaxLength(255)]
        public override string Email { get; set; }
        public string Firstname { get; set; }
        public string Lastname { get; set; }
        [Index]
        public GenderType Gender { get; set; }
        [Index]
        public DateTime? Birthdate { get; set; }
        [Index, MaxLength(2)]
        public string Country { get; set; }
        [MaxLength(2)]
        public string Language { get; set; }
        [Index, MaxLength(256)]
        public string Referral { get; set; }
        public string ImageUrl { get; set; }
        [Index]
        public UserIdentityStatus IdentityConfirmed { get; set; }
        [Index]
        public DateTime? Deleted { get; set; }
        public ICollection<Reward> Ads { get; set; }
        public ICollection<Thought> Thoughts { get; set; }
        public ICollection<Achievement> Achievements { get; set; }
        public ICollection<Subscription> Subscriptions { get; set; }
        public DateTime? TutorialShown { get; set; }
        [Index]
        public DateTime? LastActivity { get; set; }
        [Index]
        public DateTime? LastBulkEmail { get; set; }
    }

我想我有解决方案给你。我做了另一个测试，第二个选项 - 计算列索引有效。以下是 sql 代码中的步骤，您可能可以使用 EF 注释执行相同的操作。

1. 在表 users 上创建计算列作为 upper(username)：

   更改表用户将 upper_username 添加为 upper(username)

2. 在该列上创建索引：

   在 a_upload(upper_username) 上创建索引 ix2

就这样。 EF select 仍将使用 UPPER，但 MS SQL 优化器应该能够使用此索引，因为它与 where 子句中的函数具有相同的定义。

以下是我电脑上的测试结果：

测试sql：从a_upload中选择field001，其中up​​per(field001)='10'

BEFORE（SCAN意味着引擎必须一一读取所有记录）

在功能列上创建索引后（SEEK=引擎将利用索引）

不要感到困惑，即使在 BEFORE 场景中，sql 引擎也在使用索引 (ix1)。这只是因为我只选择“field001”并且优化器知道它不仅包含在表中而且也包含在索引中。并且索引的字节数比整个表少。但这并不意味着系统使用了索引，它必须为每个选择的每一行计算 upper() 。