记一次EFCore性能优化

　　最近换公司了，新公司项目技术是用dotnetcore + efcore 做业务层的数据查询。最近早会发言时候，测试提出有个页面查询列表速度特别慢，有几十秒之长。我听着有点诧异，毕竟之前在上一家公司我们接口查询速度必须在200ms以内，有1秒的已经很夸张了，几十秒基本没有用户体验。但是这毕竟不是互联网公司可能要求也没这么严格。所以会后我看了下lambda的查询逻辑。如下

　　　　　　　var doctor = await MyUtils.CurrentEmpl(_dbContext, doctorId);

            var query = (await _dbContext.MedicalRecord
                .Include(x => x.TemplateConfig)
                .ThenInclude(x => x.TemplateDeptAuths)
                .ThenInclude(x => x.TemplateRoleAuths)
                .ToListAsync())
                .Where(x => x.InpatId == inpatId
                    && x.TemplateConfig != null && x.TemplateConfig.TemplateDeptAuths
                         .Any(da =>
                             da.DeptId == doctor.DeptId &&
                             da.IsAuth && da.TemplateRoleAuths != null &&
                             da.TemplateRoleAuths.Any(ra =>
                                 ra.RoleId == doctor.EmplTypeId && ra.AccessPermission > 0)
                         )
                ).AsQueryable();

　　经过解析log发现这段lambda的查询完全没有走where条件的索引，因为linq to ef在ToList()之后就会立即生成sql去数据库查询然后把数据带回内存，而这个多表关联直接带回所有表数据而且有的表是一些大表所以查询速度非常缓慢，经常需要十几秒才能返回数据。我的优化步骤如下

第一步我首先让lambda表达式不执行ToList()，并且删除了一些不必要的null条件查看sql的log发现where条件带上后执行速度快了很多可以在数秒内返回，代码如下

            var query = _dbContext.MedicalRecord
                .Include(x => x.TemplateConfig)
                .ThenInclude(x => x.TemplateDeptAuths)
                .ThenInclude(x => x.TemplateRoleAuths)
                .Where(x => x.InpatId == inpatId
                    && x.TemplateConfig.TemplateDeptAuths.Any(da =>
                            da.DeptId == doctor.DeptId &&
                            da.IsAuth && da.TemplateRoleAuths.Any(ra =>
                                ra.RoleId == doctor.EmplTypeId && ra.AccessPermission > 0)
                         )
                );

　　但是这个查询速度也是远远不够，这才4张表关联做了索引不至于这么慢，于是我排查ef生成sql的log发现sql如下

 SELECT "x.TemplateConfig.TemplateDeptAuths.TemplateRoleAuths".id, "x.TemplateConfig.TemplateDeptAuths.TemplateRoleAuths".access_permission, "x.TemplateConfig.TemplateDeptAuths.TemplateRoleAuths".dept_auth_id, "x.TemplateConfig.TemplateDeptAuths.TemplateRoleAuths".oper_id, "x.TemplateConfig.TemplateDeptAuths.TemplateRoleAuths".oper_time, "x.TemplateConfig.TemplateDeptAuths.TemplateRoleAuths".role_id
      FROM medical_document.template_role_auth AS "x.TemplateConfig.TemplateDeptAuths.TemplateRoleAuths"
      INNER JOIN (
          SELECT DISTINCT "x.TemplateConfig.TemplateDeptAuths0".id, t0.id AS id0
          FROM medical_document.template_dept_auth AS "x.TemplateConfig.TemplateDeptAuths0"
          INNER JOIN (
              SELECT DISTINCT "x.TemplateConfig1".id
              FROM medical_document.medical_record AS x1
              LEFT JOIN medical_document.template_config AS "x.TemplateConfig1" ON x1.template_id = "x.TemplateConfig1".id
              WHERE (x1.inpat_id = @__inpatId_0) AND EXISTS (
                  SELECT 1
                  FROM medical_document.template_dept_auth AS da1
                  WHERE (((da1.dept_id = @__doctor_DeptId_1) AND (da1.is_auth = TRUE)) AND EXISTS (
                      SELECT 1
                      FROM medical_document.template_role_auth AS ra1
                      WHERE ((ra1.role_id = @__doctor_EmplTypeId_2) AND (ra1.access_permission > 0)) AND (da1.id = ra1.dept_auth_id))) AND ("x.TemplateConfig1".id = da1.template_config_id))
          ) AS t0 ON "x.TemplateConfig.TemplateDeptAuths0".template_config_id = t0.id
      ) AS t1 ON "x.TemplateConfig.TemplateDeptAuths.TemplateRoleAuths".dept_auth_id = t1.id
      ORDER BY t1.id0, t1.id

　　排查发现表关联逻辑混乱，一些表不仅在where之后再次关联 ，还多次被关联重新扫全表比如 template_dept_auth和 template_dept_auth表，怎么看这种垃圾sql是完全不可能用的，因为查询可读性和效率必定是非常低的。不得已我只好重新编写sql查询，进行sql查询测试后查询效率基本在35ms可以返回我需要的数据

SELECT DISTINCT mr.*
FROM   medical_document.template_role_auth AS tra
       INNER JOIN medical_document.template_dept_auth AS tda
               ON tra.dept_auth_id = tda.id
       INNER JOIN medical_document.template_config AS tc
               ON tda.template_config_id = tc.id
       INNER JOIN medical_document.medical_record AS mr
               ON tc.id = mr.template_id
WHERE  mr.inpat_id = 1122334455
       AND mr.template_id IS NOT NULL
       AND tda.dept_id = 316
       AND tda.is_auth = TRUE
       AND tra.role_id = 397
       AND tra.access_permission > 0

于是我重构linq查询，抽出关联条件后，linq查询如下

            var query = from tra in _dbContext.TemplateRoleAuth
                        join tda in _dbContext.TemplateDeptAuth on tra.DeptAuthId equals tda.Id
                        join tc in _dbContext.TemplateConfig on tda.TemplateConfigId equals tc.Id
                        join mr in _dbContext.MedicalRecord on tc.Id equals mr.TemplateId
                        where mr.InpatId == inpatId && mr.TemplateId != null && tda.DeptId == doctor.DeptId && tda.IsAuth &&
                             tra.RoleId == doctor.EmplTypeId && tra.AccessPermission > 0
                        select mr;
　　　　　　　var result = await query.Distinct().ToListAsync();

运行后发现所有传参基本可以稳定在200ms内返回，拉去ef 生成的sql日志发现sql与我预期的基本一致，生成sql如下

SELECT DISTINCT mr.id, mr.create_doctor_id, mr.create_doctor_name, mr.create_time, mr.designated_doctor_id, mr.has_comments, mr.inpat_id, mr.need_sign, mr.record_content, mr.record_name, mr.record_parent_type_id, mr.record_parent_type_name, mr.record_type_id, mr.sign_level, mr.signed1_doctor_id, mr.signed1_doctor_name, mr.signed1_time, mr.signed2_doctor_id, mr.signed2_doctor_name, mr.signed2_time, mr.signed3_doctor_id, mr.signed3_doctor_name, mr.signed3_time, mr.spell, mr.template_id, mr.unsigned_doctor_id, mr.unsigned_doctor_name, mr.unsigned_time, mr.wb, mr.write_doctor_id, mr.write_doctor_name, mr.write_time
      FROM medical_document.template_role_auth AS tra
      INNER JOIN medical_document.template_dept_auth AS tda ON tra.dept_auth_id = tda.id
      INNER JOIN medical_document.template_config AS tc ON tda.template_config_id = tc.id
      INNER JOIN medical_document.medical_record AS mr ON tc.id = mr.template_id
      WHERE (((((mr.inpat_id = @__inpatId_0) AND mr.template_id IS NOT NULL) AND (tda.dept_id = @__doctor_DeptId_1)) AND (tda.is_auth = TRUE)) AND (tra.role_id = @__doctor_EmplTypeId_2)) AND (tra.access_permission > 0)

　　至此本次调优完成，耗时从18s左右优化到200ms返回。简单做个优化总结：

• 在表达式没有完全写完的情况下，不能使用ToList来加载数据，否则容易导致许多表还没关联筛选就被全表查询导入内存
• 在查询的时候不要对where条件的参数进行插表查询，再次关联表时候又扫一次表性能很低
• 所有关联的表信息尽量在where条件前进行关联，where条件后进行筛选
• 建立关键的索引能够大幅度提升查询效率

　　最后希望大家在用Orm顺手的情况下，还是需要先些sql来进行查询优化，然后再用orm进行查询，并且比对生成sql结果来比较差异，减少查询性能损。基础很重要，框架只是包装工具。最近看到园里有一篇文章《停止学习框架》写的不错，大家可以看下。