接口查询性能优化-层级分类数据查询优化

一.需求:

法规、案例根据输入的关键字、选择的分类进行查询，如果选择的是父级分类要查询出所有子分类的相关数据，还要统计出相关分类的数量。只显示分类数量大于0的分数，子分类的数据要累加到父级上。

效果图：

 左边是相关层级的分类统计，右边是列表，分页。

表设计和数据结构

 法规表：Law

法规分类表 : LawType

 法规分类表 : LawType 表中有1300个分类。

法规表：Law表中有10万条数据左右，后面还会在增加。Law表数据上关联的是最后一级的分类ID

二、实现

第一版实现：

按法规分类递规带上分类ID和传入的参数多次去数据库中查询和统计。

问题：

性能低，分类数量统计不准，在选择父级分类ID时不方便统计到所有的子类的数据，这样只能使用递规所有的子分类再累加才能统计到。

 接口日志结图

 从图中可以看出接口性能是很差的。都在10秒以上，有时还查不出来。超时报错了。

三、优化：

经过对原代码和数据的分析。找差影响性能的几个点。

1.多次访问数据库。

2.分类递规查询。1300条数据每一个分类查一次的话，连数据库就要1300次。

3.分类接口和列表查询接口是两个接口。这样一来从数据库中取出来的数据无法共用，还要增加连接数据库的次数。注意 列表接口查询时也是要按分类递规去查询数据的。

可见数据库连接使用的很多。

思路：

1.法规数据的变化是很慢的。可以优先使用Redis缓存来减少数据库压力。缓存在设计时可以使用二级缓存（一级缓存：远程redis服务器，二级缓存：应用服务器的本地缓存）。为什么要使用本地缓存主要是想到从远程服务器到应用服务器有网络消耗。二级缓存的过期时间推荐使用滑动缓存策略。滑动缓存策略不懂的可以百度一下。关键字 【滑动缓存】

2.将有层级的数据转成扁平数据。在原来有分类表中增加一列叫F_No，用于存放一个int类的有规则的编号。再将这个编号同步到法规数据表中。

表结构修改

 编号规则：

分类的最大层级有5层，分析后发现每层分类的数量不会超过99个，所以设计为每层2位数。示例 99,99,99,99,99。一个,为一级. 从左到右分别是:一级，二级，三级，四级，五级

第一级分类只是两个所以从1开始，效力级别从1开始，100000000。发布部门从2开始 200000000。示例数据如下

第一级数据

第二级数据：

第三级数据：

第四级数据：

第五级数据：

 法规表中的示例数据：

 这样一来，数据就扁平化了。

在根据父级编号查询时就可以使用int的区间查询了，按规则获取分类的起始编号和最大结束编号。这样就可以不递规了。

例:

 3. 原来是使用所有分类进行递规查询。优化的方法是根据查询的参数先找到相关数据。再从查到的数据中获取所有分类ID或编号。再用这些分类数据进行递规统计数量。为什么还要递规呢？现在接口是可以不递规的，但前端显示数据时还是要以层级的方式显示，

所以分类递规显示是少不了的，只能是减少递规次数了。这们处理过后递规次数可以从1300次减到只有几次，且相关法规列表数据已在内存中这样的递规是不会有什么性能上的影响的。

4. 根据查询的关键字进行查询结果缓存。这样可以增加缓存的命中率和接口性能。其他条件分类条件的参数可以在获取缓存结果后在进行内存过滤。

5. 两个接口并成一个接口，查询数据共用。

6. 将法规库和案例库独立出来。接口独立出来。

7. 根据查询条件在相关表中增加索引。

四、代码：

1 /// <summary>
  2         /// 法规搜索（优化，Redis，多线程）
  3         /// </summary>
  4         /// <param name="pinfo"></param>
  5         /// <returns>法规分页列表+分类统计数量</returns>
  6         public LawSearchRspModel GetModelListByWhereTask(LawListRequestViewModel pinfo)
  7         { 10             //父级
 11             var rspModel = new LawSearchRspModel();
 12             var cacheKey = CacheKeyType.GetGetModelListByWhereTaskResultKey(pinfo);
 13             var resultModel = RedisClient.GetValue<LawSearchResultModel>(cacheKey);
 14             if (resultModel == null)
 15             {
 16                 resultModel = new LawSearchResultModel();
 17                 var parentLawCates = new List<LawCateViewModel>();
 18                 //重新统计
 19                 var lawCateViewModelList = GetLawCateViewModelList();
 20                 if (lawCateViewModelList != null && lawCateViewModelList.Count > 0)
 21                 {
 22                     //1、查询到相关数据
 23                     var dataList = GetLawListByWhere_Redis(pinfo, null);
 24                     //2、现提取所有法规分类
 25                     var parentModelList = lawCateViewModelList.FindAll(t => !string.IsNullOrEmpty(t.ParentCateId) && t.ParentCateId.Equals("0"));
 26 
 27                     var cateLevelIdList = dataList.Select(t => t.F_LawCateLevelNo).ToList();
 28                     var catePublishIdLList = dataList.Select(t => t.F_LawCatePublishNo).ToList();
 29 
 30                     cateLevelIdList = cateLevelIdList.Distinct().ToList();
 31                     cateLevelIdList = cateLevelIdList.FindAll(t => t > 0);
 32 
 33                     catePublishIdLList = catePublishIdLList.Distinct().ToList();
 34                     catePublishIdLList = catePublishIdLList.FindAll(t => t > 0);
 35 
 36                     #region 单线程
 37                     //效力级别 分类
 38                     var cateLevelList = new List<LawCateViewModel>();
 39                     var cateLevelList_data = lawCateViewModelList.FindAll(t => cateLevelIdList.Contains(t.No));
 40                     foreach (var itemCate in cateLevelList_data)
 41                     {
 42                         cateLevelList.Add(itemCate);
 43                         var parentCateId = itemCate.ParentCateId;
 44                         for (int level = itemCate.Level - 1; level >= 1; level--)
 45                         {
 46                             var cateModel_Parent = lawCateViewModelList.FirstOrDefault(t => t.CateId.Equals(parentCateId));
 47                             if (cateModel_Parent != null && !cateLevelList.Contains(cateModel_Parent))
 48                             {
 49                                 cateLevelList.Add(cateModel_Parent);
 50                                 parentCateId = cateModel_Parent.ParentCateId;
 51                             }
 52                         }
 53                     }
 54                     var oneCateLevel = parentModelList.FirstOrDefault(t => t.Type == 1);
 55                     var dataModel = countLawCateNew(pinfo.CaseId, dataList, cateLevelList, oneCateLevel);
 56                     dataModel.LawCount = dataModel.ChildList.Sum(t => t.LawCount);
 57                     parentLawCates.Add(dataModel);
 58 
 59                     //发布部门 分类
 60                     var catePublishList = new List<LawCateViewModel>();
 61                     var catePublishList_data = lawCateViewModelList.FindAll(t => catePublishIdLList.Contains(t.No));
 62                     foreach (var itemCate in catePublishList_data)
 63                     {
 64                         catePublishList.Add(itemCate);
 65                         var parentCateId = itemCate.ParentCateId;
 66                         for (int level = itemCate.Level - 1; level >= 1; level--)
 67                         {
 68                             var cateModel_Parent = lawCateViewModelList.FirstOrDefault(t => t.CateId.Equals(parentCateId));
 69                             if (cateModel_Parent != null && !catePublishList.Contains(cateModel_Parent))
 70                             {
 71                                 catePublishList.Add(cateModel_Parent);
 72                                 parentCateId = cateModel_Parent.ParentCateId;
 73                             }
 74                         }
 75                     }
 76                     var oneCatePublish = parentModelList.FirstOrDefault(t => t.Type == 2);
 77                     var dataModel_Publish = countLawCateNew(pinfo.CaseId, dataList, catePublishList, oneCatePublish);
 78                     dataModel_Publish.LawCount = dataModel_Publish.ChildList.Sum(t => t.LawCount);
 79                     parentLawCates.Add(dataModel_Publish);
 80                     #endregion
 81 
 82                     #region 多线程
 83                     //var taskList = new List<Task<LawCateViewModel>>();
 84                     var taskList = new List<Task>();
 85                     3、分别统计分类的数量
 86                     //foreach (var item in parentModelList)
 87                     //{
 88                     //    if (item.Type == 1)
 89                     //    {
 90                     //        var taskLevel = Task.Factory.StartNew(() =>
 91                     //        {
 92                     //            //效力级别 分类
 93                     //            var cateLevelList = new List<LawCateViewModel>();
 94                     //            var cateLevelList_data = lawCateViewModelList.FindAll(t => cateLevelIdList.Contains(t.No));
 95                     //            foreach (var itemCate in cateLevelList_data)
 96                     //            {
 97                     //                cateLevelList.Add(itemCate);
 98                     //                var parentCateId = itemCate.ParentCateId;
 99                     //                for (int level = itemCate.Level - 1; level >= 1; level--)
100                     //                {
101                     //                    var cateModel_Parent = lawCateViewModelList.FirstOrDefault(t => t.CateId.Equals(parentCateId));
102                     //                    if (cateModel_Parent != null && !cateLevelList.Contains(cateModel_Parent))
103                     //                    {
104                     //                        cateLevelList.Add(cateModel_Parent);
105                     //                        parentCateId = cateModel_Parent.ParentCateId;
106                     //                    }
107                     //                }
108                     //            }
109                     //            var dataModel = countLawCateNew(pinfo.CaseId, dataList, cateLevelList, item);
110                     //            dataModel.LawCount = dataModel.ChildList.Sum(t => t.LawCount);
111                     //            return dataModel;
112                     //        });
113                     //        taskList.Add(taskLevel);
114                     //    }
115                     //    else
116                     //    {
117                     //        var taskPublish = Task.Factory.StartNew(() =>
118                     //        {
119                     //            //发布部门 分类
120                     //            var catePublishList = new List<LawCateViewModel>();
121                     //            var catePublishList_data = lawCateViewModelList.FindAll(t => catePublishIdLList.Contains(t.No));
122                     //            foreach (var itemCate in catePublishList_data)
123                     //            {
124                     //                catePublishList.Add(itemCate);
125                     //                var parentCateId = itemCate.ParentCateId;
126                     //                for (int level = itemCate.Level - 1; level >= 1; level--)
127                     //                {
128                     //                    var cateModel_Parent = lawCateViewModelList.FirstOrDefault(t => t.CateId.Equals(parentCateId));
129                     //                    if (cateModel_Parent != null && !catePublishList.Contains(cateModel_Parent))
130                     //                    {
131                     //                        catePublishList.Add(cateModel_Parent);
132                     //                        parentCateId = cateModel_Parent.ParentCateId;
133                     //                    }
134                     //                }
135                     //            }
136                     //            var dataModel = countLawCateNew(pinfo.CaseId, dataList, catePublishList, item);
137                     //            dataModel.LawCount = dataModel.ChildList.Sum(t => t.LawCount);
138                     //            return dataModel;
139                     //        });
140                     //        taskList.Add(taskPublish);
141                     //    }
142                     //}
143                     //Task.WaitAll(taskList.ToArray());
144                     //foreach (var item in taskList)
145                     //{
146                     //    if (item.Result.Type == 1)
147                     //    {
148                     //        parentLawCates.Insert(0, item.Result);
149                     //    }
150                     //    else
151                     //    {
152                     //        parentLawCates.Add(item.Result);
153                     //    }
154                     //}
155                     #endregion
156                     //分类统计结果
157                     resultModel.CateList = parentLawCates;
158 
159                     //查询结果 法规列表
160                     resultModel.LawList = dataList;
161 
162                     var pageModelDataList = resultModel.LawList.Skip((pinfo.Index - 1) * pinfo.Length).Take(pinfo.Length).ToList();
163 
164                     rspModel.CateList = resultModel.CateList;
165                     rspModel.PageModel.pageindex = pinfo.Index;
166                     rspModel.PageModel.pagesize = pinfo.Length;
167                     rspModel.PageModel.datas = resultModel.LawList;
168                     rspModel.PageModel.totalrows = dataList.Count;
169                     rspModel.PageModel.totalpages = (pageModelDataList.Count - 1) / (rspModel.PageModel.pagesize + 1);
170 
171                     if (parentLawCates.Count > 0)
172                     {
173                         RedisClient.SetValue(cacheKey, resultModel, RedisClient.GetMinuteByHour(7 * 24));
174                     }
175                     else
176                     {
177                         RedisClient.SetValue(cacheKey, resultModel, 5);
178                     }
179                 }
180             }
181             else
182             {
183                 rspModel.CateList = resultModel.CateList;
184 
185                 var lawDataList = resultModel.LawList;
186                 if (!string.IsNullOrEmpty(pinfo.LawCateLevelId) || !string.IsNullOrEmpty(pinfo.LawCatePublishId))
187                 {
188                     //从缓存中获取数据后在进行一步筛选
189                     //1、查询到相关数据
190                     lawDataList = GetLawListByWhere_Redis(pinfo, lawDataList);
191                 }
192                 var pageModelDataList = lawDataList.Skip((pinfo.Index - 1) * pinfo.Length).Take(pinfo.Length).ToList();
193 
194                 rspModel.PageModel.pageindex = pinfo.Index;
195                 rspModel.PageModel.pagesize = pinfo.Length;
196                 rspModel.PageModel.datas = pageModelDataList;
197                 rspModel.PageModel.totalrows = resultModel.LawList.Count;
198                 rspModel.PageModel.totalpages = (pageModelDataList.Count - 1) / (rspModel.PageModel.pagesize + 1);
199             }
200             return rspModel;
201         }

/// <summary>
        /// 根据参数查询相关数据
        /// </summary>
        /// <param name="pinfo">查询参数</param>
        /// <param name="redisLawList">redis中缓存的数据</param>
        /// <returns></returns>
        public List<LawListViewModel> GetLawListByWhere_Redis(LawListRequestViewModel pinfo, List<LawListViewModel> redisLawList)
        {
            //var sql = @"select l.F_Id as LawId,l.F_Title as Title,F_Code,l.F_PublishDate as PublishDate,
            //                        l.F_UseDate as UseDate,l.F_Timeliness as TimeLiness,l.F_CreatorTime,F_LawCateLevelId,F_LawCatePublishId,
            //                        l.F_LawCateLevelNo, l.F_LawCatePublishNo
            //                from TB_Law l with(nolock) where 1=1 ";

            //IEnumerable<LawListViewModel> lawListViewModelList = GetLawListViewModelList();
            IEnumerable<LawListViewModel> lawListViewModelList = null;
            if (redisLawList != null && redisLawList.Count > 0)
            {
                lawListViewModelList = redisLawList;
            }
            else
            {
                lawListViewModelList = GetLawListViewModelList();
            }
            if (lawListViewModelList != null && lawListViewModelList.Count() > 0)
            {

                //List<string> lawItemsIdList = null;
                if (pinfo.KeywordType > 0)
                {
                    if (pinfo.KeywordType == 1 && !string.IsNullOrWhiteSpace(pinfo.Keyword))
                    {
                        //sql += " and l.F_Title like '%" + pinfo.Keyword + "%'";
                        lawListViewModelList = lawListViewModelList.Where(t => t.Title != null && t.Title.Contains(pinfo.Keyword));
                        //lawListViewModelList = lawListViewModelList.ToList();
                    }
                    if (pinfo.KeywordType == 2 && !string.IsNullOrWhiteSpace(pinfo.Keyword))
                    {
                        //sql += " and l.F_Id in (select F_LawId from TB_Law_Items where F_Content like '%" + pinfo.Keyword + "%')";
                        var lawItemsList = GetLawItems();
                        if (lawItemsList != null && lawItemsList.Count > 0)
                        {
                            var lawItemsModels = lawItemsList.FindAll(t => t.F_Content != null && t.F_Content.Contains(pinfo.Keyword));
                            if (lawItemsModels != null && lawItemsModels.Count > 0)
                            {
                                var lawItemsIdList = lawItemsModels.Select(t => t.F_Id).ToList();
                                if (lawItemsIdList != null && lawItemsIdList.Count > 0)
                                {
                                    lawListViewModelList = lawListViewModelList.Where(t => lawItemsIdList.Contains(t.LawId));
                                    //lawListViewModelList = lawListViewModelList.ToList();
                                }
                            }
                        }
                    }
                    if (pinfo.KeywordType == 3 && !string.IsNullOrWhiteSpace(pinfo.Keyword))
                    {
                        //sql += " and l.F_Code like '%" + pinfo.Keyword + "%'";
                        lawListViewModelList = lawListViewModelList.Where(t => t.F_Code != null && t.F_Code.Contains(pinfo.Keyword));
                        //lawListViewModelList = lawListViewModelList.ToList();
                    }
                }
                if (!string.IsNullOrWhiteSpace(pinfo.LawCode))
                {
                    //sql += " and l.F_Code like '%" + pinfo.LawCode + "%'";
                    lawListViewModelList = lawListViewModelList.Where(t => t.F_Code != null && t.F_Code.Contains(pinfo.LawCode));
                    //lawListViewModelList = lawListViewModelList.ToList();
                }
                if (!string.IsNullOrWhiteSpace(pinfo.LawCateLevelId))//效力级别
                {
                    //sql += " and l.F_LawCateLevelId='" + pinfo.LawCateLevelId + "'";
                    //要递规分类表 TB_Law_Cate 
                    //_serviceLawCate.GetModelByWhere
                    var lawCateModel = _serviceLawCate.GetModelByIdRedis(pinfo.LawCateLevelId);
                    if (lawCateModel != null)
                    {
                        int endNo = CommonHelpLD.GetLawCateLevelEndNo(lawCateModel.F_Level, lawCateModel.F_No);
                        lawListViewModelList = lawListViewModelList.Where(t => t.F_LawCateLevelNo >= lawCateModel.F_No && t.F_LawCateLevelNo <= endNo);
                        //sql += " and l.F_LawCateLevelNo >= " + lawCateModel.F_No + " and l.F_LawCateLevelNo <= " + endNo;
                    }
                    //lawListViewModelList = lawListViewModelList.ToList();
                }
                if (!string.IsNullOrWhiteSpace(pinfo.LawCatePublishId))//发布单位
                {
                    //sql += " and l.F_LawCatePublishId='" + pinfo.LawCatePublishId + "'";
                    //lawListViewModelList = lawListViewModelList.Where(t => t.F_LawCatePublishId != null && t.F_LawCatePublishId.Equals(pinfo.LawCatePublishId));
                    var lawCateModel = _serviceLawCate.GetModelByIdRedis(pinfo.LawCatePublishId);
                    if (lawCateModel != null)
                    {
                        int endNo = CommonHelpLD.GetLawCateLevelEndNo(lawCateModel.F_Level, lawCateModel.F_No);
                        lawListViewModelList = lawListViewModelList.Where(t => t.F_LawCatePublishNo >= lawCateModel.F_No && t.F_LawCatePublishNo <= endNo);
                        //sql += " and l.F_LawCatePublishNo >= " + lawCateModel.F_No + " and l.F_LawCatePublishNo <= " + endNo;
                    }
                    //lawListViewModelList = lawListViewModelList.ToList();
                }
                if (pinfo.PublishDateStart != null)
                {
                    //sql += " and l.F_PublishDate >= '" + pinfo.PublishDateStart + "'";
                    lawListViewModelList = lawListViewModelList.Where(t => t.PublishDate >= pinfo.PublishDateStart.Value);
                    //lawListViewModelList = lawListViewModelList.ToList();
                }
                if (pinfo.PublishDateEnd != null)
                {
                    //sql += " and l.F_PublishDate <= '" + pinfo.PublishDateEnd + "'";
                    lawListViewModelList = lawListViewModelList.Where(t => t.PublishDate >= pinfo.PublishDateEnd.Value);
                    //lawListViewModelList = lawListViewModelList.ToList();
                }
                if (pinfo.UseDateStart != null)
                {
                    //sql += " and l.F_UseDate >= '" + pinfo.UseDateStart + "'";
                    lawListViewModelList = lawListViewModelList.Where(t => t.UseDate >= pinfo.UseDateStart.Value);
                    //lawListViewModelList = lawListViewModelList.ToList();
                }
                if (pinfo.UseDateEnd != null)
                {
                    //sql += " and l.F_UseDate <= '" + pinfo.UseDateEnd + "'";
                    lawListViewModelList = lawListViewModelList.Where(t => t.UseDate >= pinfo.UseDateEnd.Value);
                    //lawListViewModelList = lawListViewModelList.ToList();
                }
                if (pinfo.Timeliness > 0)
                {
                    //sql += " and l.F_Timeliness = " + pinfo.Timeliness;
                    lawListViewModelList = lawListViewModelList.Where(t => t.Timeliness.Equals(pinfo.Timeliness));
                    //lawListViewModelList = lawListViewModelList.ToList();
                }
            }
            return dataList;
        }

/// <summary>
        /// 法规搜索（优化，Redis，多线程）
        /// 获取法规搜索结果缓存key
        /// </summary>
        /// <returns></returns>
        public static string GetGetModelListByWhereTaskResultKey(LawListRequestViewModel pinfo)
        {
            var key = string.Format("{0}", LawSearchResultTask);
            if (pinfo.KeywordType.HasValue)
            {
                key += string.Format(".{0}", pinfo.KeywordType);
            }
            if (!string.IsNullOrEmpty(pinfo.Keyword))
            {
                key += string.Format(".{0}", pinfo.Keyword);
            }
            return key;
        }

/// <summary>
    /// 法规查询结果实体
    /// </summary>
    public class LawSearchRspModel
    {
        public LawSearchRspModel()
        {
            this.CateList = new List<LawCateViewModel>();
            this.PageModel = new PageResponseModel<LawListViewModel>();
        }

        /// <summary>
        /// 分类统计结果
        /// </summary>
        public List<LawCateViewModel> CateList { get; set; }

        /// <summary>
        /// 法规分页结果
        /// </summary>
        public PageResponseModel<LawListViewModel> PageModel { get; set; }
    }

    /// <summary>
    /// 法规查询结果实体（redis）
    /// </summary>
    public class LawSearchResultModel
    {
        public LawSearchResultModel()
        {
            this.CateList = new List<LawCateViewModel>();
            this.LawList = new List<LawListViewModel>();
        }

        /// <summary>
        /// 分类统计结果
        /// </summary>
        public List<LawCateViewModel> CateList { get; set; }

        /// <summary>
        /// 法规查询结果
        /// </summary>
        public List<LawListViewModel> LawList { get; set; }
    }

/// <summary>
        /// 获取所有法规类似表数据 使用了Redis缓存 cjh2
        /// </summary>
        /// <returns></returns>
        public List<LawCateViewModel> GetLawCateViewModelList()
        {
            var redisKey = CacheKeyType.GetLawCateTableKey();
            var lawCateViewModelList = RedisClient.GetValue<List<LawCateViewModel>>(redisKey);
            if (lawCateViewModelList == null)
            {
                //string sql = "select F_Title as CateName,F_Id as CateId,F_Type as Type from TB_Law_Cate WITH(NOLOCK) where F_DeleteMark=0 and F_EnabledMark=1 and F_ParentCateId='0' order by F_Sort";
                //string sql = "select F_Title as CateName,F_Id as CateId,F_Type as Type from TB_Law_Cate WITH(NOLOCK) where F_DeleteMark=0 and F_EnabledMark=1 and F_ParentCateId='0' order by F_Sort";
                string sql = @"select F_Title as CateName, F_Id as CateId, F_Type as Type, F_ParentCateId as ParentCateId, F_Level as Level, F_Sort as Sort, F_No as No
                           from TB_Law_Cate WITH(NOLOCK)
                           where F_DeleteMark=0 and F_EnabledMark=1 and F_Type IN (1,2)
                           order by F_Sort";
                lawCateViewModelList = SqlDapperHelper.ExecuteReaderReturnList<LawCateViewModel>(DBTypeEnum, sql);
                if (lawCateViewModelList == null || lawCateViewModelList.Count == 0)
                {
                    RedisClient.SetValue(redisKey, lawCateViewModelList, 5);
                }
                else
                {
                    RedisClient.SetValue(redisKey, lawCateViewModelList, RedisClient.GetMinuteByHour(7 * 24));
                }
            }
            return lawCateViewModelList;
        }

五、优化后的效果

 耗时的单位是 ms,毫秒.从结果上看，没有缓存时在1.8秒左右，在并发量大时应该也不会超过3秒。现在的数据还是没有缓存二级缓存的时候。如果使用了大并发情况下应该也不错。

从数据上看本次优化的效果还是不错。

写下博客只是为记录自己的经验和分享。如有更好的方法可以在评论区告诉我。谢谢。