基于Redis缓存几十万条记录的快速模糊检索的功能实现(c#)

在开发一套大型的信息系统中,发现很多功能需要按公司查询,各个模块在实现时都是直接查数据库进行模糊检索,虽然对表进行向各个应用的库中进行了同步,但是在使用中发现,模糊检索公司时还是比较卡,原始的查询数据库实现方法:

            var organizeManager = new BaseOrganizeManager(DbHelperFactory.GetHelper(BaseSystemInfo.BusinessDbType, BaseSystemInfo.BusinessDbConnection));
            if (string.IsNullOrEmpty(key))
            {
                return null;
            }
            key = DbLogic.SqlSafe(key);
            var where = "(" + BaseOrganizeEntity.FieldFullName + " LIKE'%" + key
                + "%' OR " + BaseOrganizeEntity.FieldCode + " LIKE '%" + key + "%' OR "
                + BaseOrganizeEntity.FieldSimpleSpelling + " LIKE '%" + key + "%' OR "
                + BaseOrganizeEntity.FieldQuickQuery + " LIKE '%" + key + "%') AND "
                + BaseOrganizeEntity.FieldDeletionStateCode + " = 0 ";
            var items = organizeManager.GetList2<BaseOrganizeEntity>(where, 20, " Id desc");
            
            if (returnId)
            {
                returnList = items.Select(t => new SuggestEntity(t.FullName, t.Id)).ToList();
                returnList = items.Select(t => new SuggestEntity(t.FullName + " " + t.Code, t.Id)).ToList();
            }
            else
            {
                if (showCode)
                {
                    returnList = items.Select(t => new SuggestEntity(t.FullName, t.Code)).ToList();
                }
                else
                {
                    returnList = items.Select(t => new SuggestEntity(t.FullName, t.FullName)).ToList();
                }
            }
            return returnList;

为了提高用户体验,对公司的模糊检索使用了Redis缓存,按照以下原则:

1:读取最少的数据;
2:网络传输最少的数据;
3:所有的可能性都预先缓存;
4:缓存过期后的搜索;
5:数据库的读取压力减少;
6:缓存是否重复;
7:缓存最少的内容,占用最少的内存;
8:所有的应用共享一份缓存数据;

下面来开始具体实现

1、Redis缓存辅助类创建:

    public sealed partial class PooledRedisHelper
    {
        // 数据库
        public static int InitialDb = 0;
        private static PooledRedisClientManager instance = null;
        public static PooledRedisClientManager Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new PooledRedisClientManager(new string[] { BaseSystemInfo.RedisHosts });
                }

                return instance;
            }
        }

        static PooledRedisHelper()
        {
        }

        public static IRedisClient GetClient()
        {
            return Instance.GetClient();
        }
    }

2、缓存数据预热,具体实现时每天缓存更新一次即可(为了更精准找到检索的内容,加入到缓存时按公司名字的顺序缓存)

        public static void CachePreheatingSpelling()
        {
            BaseOrganizeManager organizeManager = new Business.BaseOrganizeManager();
            organizeManager.SelectFields = BaseOrganizeEntity.FieldId
                + ", " + BaseOrganizeEntity.FieldCode
                + ", " + BaseOrganizeEntity.FieldFullName;
            List<KeyValuePair<string, object>> parameters = new List<KeyValuePair<string, object>>();
            parameters.Add(new KeyValuePair<string, object>(BaseOrganizeEntity.FieldDeletionStateCode, 0));
            using (var redisClient = PooledRedisHelper.GetClient())
            {
                //using (IDataReader dataReader = organizeManager.ExecuteReader(parameters, BaseOrganizeEntity.FieldId))
                using (IDataReader dataReader = organizeManager.ExecuteReader(parameters, BaseOrganizeEntity.FieldFullName))
                {
                    while (dataReader.Read())
                    {
                        string id = dataReader[BaseOrganizeEntity.FieldId].ToString();
                        string code = dataReader[BaseOrganizeEntity.FieldCode].ToString();
                        string fullName = dataReader[BaseOrganizeEntity.FieldFullName].ToString();
                        string simpleSpelling = dataReader[BaseOrganizeEntity.FieldSimpleSpelling].ToString();
                        string quickQuery = dataReader[BaseOrganizeEntity.FieldQuickQuery].ToString();

                        string organize = id + ";" + code + ";" + fullName;
                        string key = string.Empty;
                        for (int i = 1; i <= code.Length; i++)
                        {
                            key = code.Substring(0, i).ToLower();
                            redisClient.AddItemToSortedSet(key, organize, double.Parse(id));
                            redisClient.ExpireEntryAt(key, DateTime.Now.AddDays(1));
                        }
                        for (int i = 1; i <= fullName.Length; i++)
                        {
                            key = fullName.Substring(0, i).ToLower();
                            redisClient.AddItemToSortedSet(key, organize, double.Parse(id));
                            redisClient.ExpireEntryAt(key, DateTime.Now.AddDays(1));
                        }
                        for (int i = 1; i <= simpleSpelling.Length; i++)
                        {
                            key = simpleSpelling.Substring(0, i).ToLower();
                            redisClient.AddItemToSortedSet(key, organize, double.Parse(id));
                            redisClient.ExpireEntryAt(key, DateTime.Now.AddDays(1));
                        }
                        for (int i = 1; i <= quickQuery.Length; i++)
                        {
                            key = quickQuery.Substring(0, i).ToLower();
                            redisClient.AddItemToSortedSet(key, organize, double.Parse(id));
                            redisClient.ExpireEntryAt(key, DateTime.Now.AddDays(1));
                        }
                    }
                }
            }
        }

缓存中的数据是怎样的呢,从上面代码中我们可以看出,涉及检索公司的所有可能性(按名称、拼音、Code检索)组合都进行了缓存,读取时直接按Key取数据,比缓存整张表再查询速度要快很多。

下图是缓存数据的局部截图,这是测试环境缓存的数据,一共166440条记录。

3、模糊检索Redis缓存公司数据方法,检索时按Key取数据( List<string> list = redisClient.GetRangeFromSortedList(key, 0, topLimit);)

        /// <summary>
        /// Redis中检索公司
        /// </summary>
        /// <param name="key"></param>
        /// <param name="returnId"></param>
        /// <param name="showCode"></param>
        /// <param name="topLimit"></param>
        /// <returns></returns>
        public static List<KeyValuePair<string, string>> GetOrganizesByKey(string key, bool returnId = true, bool showCode = false, int topLimit = 20)
        {
            List<KeyValuePair<string, string>> result = new List<KeyValuePair<string, string>>();
            using (var redisClient = PooledRedisHelper.GetClient())
            {
                List<string> list = redisClient.GetRangeFromSortedList(key, 0, topLimit);
                if (list != null)
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        string[] organize = list[i].Split(';');
                        string id = organize[0];
                        string code = organize[1];
                        string fullName = organize[2];
                        if (returnId)
                        {
                            if (showCode)
                            {
                                result.Add(new KeyValuePair<string, string>(id, fullName + " " + code));
                            }
                            else
                            {
                                result.Add(new KeyValuePair<string, string>(id, fullName));
                            }
                        }
                        else
                        {
                            if (showCode)
                            {
                                result.Add(new KeyValuePair<string, string>(code, fullName + " " + code));
                            }
                            else
                            {
                                result.Add(new KeyValuePair<string, string>(code, fullName));
                            }
                        }
                        
                    }
                }
            }

            return result;
        }

4、前端在应用时,直接调用底层这个方法,再封装成选择下拉框需要的数据即可,如:

        /// <summary>
        /// 公司检索 从redis中查询
        /// </summary>
        /// <param name="key"></param>
        /// <param name="category"></param>
        /// <param name="userInfo"></param>
        /// <param name="showCode"></param>
        /// <param name="returnId"></param>
        /// <param name="topLimit"></param>
        /// <returns></returns>
        public List<SuggestEntity> GetOrganizesByKey(string key, string category, BaseUserInfo userInfo, bool returnId = true, bool showCode = false, int topLimit = 100)
        {
            List<SuggestEntity> returnList = new List<SuggestEntity>();
            List<KeyValuePair<string, string>> list = BaseOrganizeManager.GetOrganizesByKey(key, returnId, showCode, 100);
            foreach (var organize in list)
            {
                returnList.Add(new SuggestEntity(organize.Value, organize.Key));
            }
            return returnList;
        }

前端模糊检索时,渲染选择的效果

 

使用这种方式后,比以前检索速度,效率都快了很多,用户体验也好了。

 

posted @ 2016-01-05 16:27  三人成虎  阅读(24850)  评论(4编辑  收藏  举报