如何处理500万用户？ ASP.NET身份 [英] How to handle 5 million users? ASP.NET Identity

查看：194
本文介绍了如何处理500万用户？ ASP.NET身份的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我运行目前拥有500万用户一个ASP.NET应用程序mvc5。它在Azure云主持。为了验证我使用Asp.Net身份进行的EntityFramework。

不过，更多的用户，我得到越慢寄存器功能变。我试图扩展数据库，但结果还是一样。大约需要6-7秒的新用户注册。

此外，我试图寻找我怎么能提高识别系统的性能，但我真的不能找到任何有关。

我真的想听到的，如果有人有一些想法，我怎样才能提高它的性能。

更新：我对田指标，我寻找的，另外，我在Azure中选择的数据库订阅P3 SQL数据库200 DTU的

。

我异形的DB，我发现了一个可疑的选择查询。
我删除了一些预测，并与他们取代......所以它不会太长，你可以看到什么是有关查询。

选择
    [UnionAll2] [两性] AS [C1]
    ....
    [UnionAll2]。[用户名] AS [C27]
    [UnionAll2]。[C1] AS [C28]
    [UnionAll2]。[用户ID] AS [C29]
    [UnionAll2]。[角色ID] AS [C30]，
    [UnionAll2]。[UserId1] AS [C31]，
    [UnionAll2]。〔C2〕的AS [C32]，
    [UnionAll2]。[C3] AS [C33]，
    [UnionAll2]。[4] AS [C34]，
    [UnionAll2]。[C5] AS [C35]，
    [UnionAll2]。[6] AS [C36]，
    [UnionAll2]。[C7] AS [C37]，
    [UnionAll2]。[8] AS [C38]，
    [UnionAll2]。[C9] AS [C39]
    FROM（SELECT
        CASE WHEN（[Extent2]。[用户ID] IS NULL）再投（NULL AS INT）ELSE 1 END AS [C1]
        [LIMIT1] [两性] AS [两性]
        ....
        [LIMIT1]。[用户名] AS [用户名]
        [Extent2]。[用户ID] AS [用户ID]
        [Extent2]。[角色ID] AS [角色ID]
        [Extent2]。[用户ID] AS [UserId1]
        CAST（NULL AS INT）AS [C2]
        CAST（NULL为varchar（1））AS [C3]
        CAST（NULL为varchar（1））AS [4]
        CAST（NULL为varchar（1））AS [C5]
        CAST（NULL为varchar（1））AS [6]
        CAST（NULL为varchar（1））AS [C7]
        CAST（NULL为varchar（1））AS [8]
        CAST（NULL为varchar（1））AS [C9]
        FROM（SELECT TOP（1）
            [Extent1]。[ID] AS [ID]
            ....
            [Extent1]。[用户名] AS [用户名]
            FROM [DBO]。[用户] AS [Extent1]
            WHERE（（UPPER（[Extent1]。[用户名]））=（UPPER（@ p__linq__0）））OR（（UPPER（[Extent1]。[用户名]）IS NULL）AND（UPPER（@ p__linq__0）IS NULL）） ）AS [LIMIT1]
        LEFT OUTER JOIN [DBO]。[的UserRole] AS [Extent2] ON [LIMIT1]。[ID] = [Extent2]。[用户ID]
    UNION ALL
        选择
        2 AS [C1]
        [LIMIT2] [两性] AS [两性]
        ....
        [LIMIT2]。[用户名] AS [用户名]
        CAST（NULL为varchar（1））AS [C2]
        CAST（NULL为varchar（1））AS [C3]
        CAST（NULL为varchar（1））AS [4]
        [Extent4]。[ID] AS [Id1的]
        [Extent4]。[用户ID] AS [用户ID]
        [Extent4]。[ClaimType] AS [ClaimType]
        [Extent4]。[ClaimValue] AS [ClaimValue]
        CAST（NULL为varchar（1））AS [C5]
        CAST（NULL为varchar（1））AS [6]
        CAST（NULL为varchar（1））AS [C7]
        CAST（NULL为varchar（1））AS [8]
        FROM（SELECT TOP（1）
            [Extent3]。[ID] AS [ID]
            ....
            [Extent3]。[用户名] AS [用户名]
            FROM [DBO]。[用户] AS [Extent3]
            WHERE（（UPPER（[Extent3]。[用户名]））=（UPPER（@ p__linq__0）））OR（（UPPER（[Extent3]。[用户名]）IS NULL）AND（UPPER（@ p__linq__0）IS NULL）） ）AS [LIMIT2]
        INNER JOIN [DBO]。[UserClaims] AS [Extent4] ON [LIMIT2]。[ID] = [Extent4]。[用户ID]
    UNION ALL
        选择
        3 AS [C1]
        [Limit3] [两性] AS [两性]
        ....
        [Limit3]。[用户名] AS [用户名]
        CAST（NULL为varchar（1））AS [C2]
        CAST（NULL为varchar（1））AS [C3]
        CAST（NULL为varchar（1））AS [4]
        CAST（NULL AS INT）AS [C5]
        CAST（NULL为varchar（1））AS [6]
        CAST（NULL为varchar（1））AS [C7]
        CAST（NULL为varchar（1））AS [8]
        [Extent6]。[LoginProvider] AS [LoginProvider]
        [Extent6]。[ProviderKey] AS [ProviderKey]
        [Extent6]。[用户ID] AS [用户ID]
        [Extent6]。[用户ID] AS [UserId1]
        FROM（SELECT TOP（1）
            [Extent5]。[ID] AS [ID]
            ....
            [Extent5]。[用户名] AS [用户名]
            FROM [DBO]。[用户] AS [Extent5]
            WHERE（（UPPER（[Extent5]。[用户名]））=（UPPER（@ p__linq__0）））OR（（UPPER（[Extent5]。[用户名]）IS NULL）AND（UPPER（@ p__linq__0）IS NULL）） ）AS [Limit3]
        INNER JOIN [DBO]。[UserLogins] AS [Extent6] ON [Limit3]。[ID] = [Extent6]。[用户ID]）AS [UnionAll2]
    ORDER BY [UnionAll2]。[ID] ASC，[UnionAll2] [C1] ASC

我的EntityFramework用户POCO类

 公共类用户：IdentityUser
    {
        [指数]
        公众的DateTime创建{搞定;组; }
        [指数（= isUnique设置真），最大长度（255）
        公众覆盖字符串电子邮件{获得;组; }
        公共字符串名字{获得;组; }
        公共字符串姓氏{搞定;组; }
        [指数]
        公共GenderType性别{搞定;组; }
        [指数]
        公众的DateTime？生日{搞定;组; }
        [索引，最大长度（2）]
        公共字符串国家{搞定;组; }
        [MAXLENGTH（2）]
        公共字符串语言{搞定;组; }
        [索引，最大长度（256）
        公共字符串推荐{搞定​​;组; }
        公共字符串的ImageUrl {搞定;组; }
        [指数]
        公共UserIdentityStatus IdentityConfirmed {搞定;组; }
        [指数]
        公众的DateTime？删除{搞定;组; }
        公众的ICollection＆LT;奖励＆GT;广告{搞定;组; }
        公众的ICollection＆LT;思想理论GT;思想{搞定;组; }
        公众的ICollection＆LT;＆成就GT;成就{搞定;组; }
        公众的ICollection＆LT;订阅与GT;订阅{搞定;组; }
        公众的DateTime？ TutorialShown {搞定;组; }
        [指数]
        公众的DateTime？ LastActivity {搞定;组; }
        [指数]
        公众的DateTime？ LastBulkEmail {搞定;组; }
    }


解决方案

我觉得我对你的解决方案。我已经做了另一个测试和第二个方案 - 在计算列作品索引。以下是SQL code的步骤，你也许可以做同样的使用EF注解。


  1. 创建为上（用户名）上表的用户计算列：

    alter table将用户添加upper_username为上（用户名）


  2. 在该列创建索引：

    创建a_upload（upper_username）指数IX2


这就是所有。该EF选择仍然会使用UPPER，但MS SQL优化器应该能够使用这个指标，因为它具有相同的定义在函数子句。

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

测试SQL：从a_upload选择field001，其中上（field001）= '10'

在（SCAN意味着发动机拥有读取所有记录逐个）

在创建一个有关职能列索引（SEEK =引擎将利用指数）

不明白confuzed，即使在之前的情况下，SQL引擎采用指数（IX1）。它是唯一的，因为我只选择field001和优化器知道，它不仅包含在表中，但在索引太。而指数比整个表少字节。不过，这并不意味着该系统使用的指标，它具有计算上（）的每一行上的每个选择呢。

I am running a ASP.NET mvc5 app which currently has 5 million users. It is hosted in the Azure cloud. For the authentication I use the Asp.Net Identity for EntityFramework.

However, the more users I get, the slower the register function becomes. I tried scaling the database, but the results are still the same. It takes around 6-7 seconds for a new user to register.

Also I tried searching how I can improve the performance of the identity system, but I couldn't really find anything relevant.

I would really like to hear if somebody has some idea how can I improve the performance of it.

UPDATE: I have indexes on the fields that I am searching on, also, the database subscription that I have chosen in Azure is P3 SQL database with 200 DTUs.

I profiled the DB and I found a suspicious select query. I deleted some of the projections and replaced them with "...." so it doesn't get too long and you can see what is the query about.

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

My EntityFramework User POCO class

    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; }
    }

解决方案

I think I have solution for you. I have made another tests and the second option - index on computed column works. Here are steps in sql code, you can probably do the same using EF annotations.

  1. Create computed column on table users as upper(username):

    alter table users add upper_username as upper(username)

  2. Create index on that column:

    create index ix2 on a_upload(upper_username)

Thats all. The EF select will still use UPPER, but MS SQL optimizer should be able to use this index as it has the same definition as the function in where clause.

Here are test results on my PC:

test sql: select field001 from a_upload where upper(field001)='10'

BEFORE (SCAN means that the engine has to read all records one by one)

AFTER CREATING THE INDEX on functional column (SEEK=engine will utilize index)

Dont get confuzed that even in the BEFORE scenario, sql engine is using index (ix1). It is only because I am selecting only "field001" and the optimizer knows, that it is contained not only in the table but in the index too. And index has less bytes than whole table. But it does not mean that the system utilized index, it has to compute upper() for every row on each select anyway.

这篇关于如何处理500万用户？ ASP.NET身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆