基于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; }
前端模糊检索时，渲染选择的效果
使用这种方式后，比以前检索速度，效率都快了很多，用户体验也好了。
