搜索数据库表的性能优化过程

问题背景

做一个数据库表查看、标注与分析的工具软件。
\(Table\)是数据库中表的信息（information_schema.tables）；\(Documentation\)是\(Table\)的数据字典文档，存储在本地文件中；\(Annotation\)是对\(Table\)的额外标注信息，存储在另一个数据库中。每一条\(Table\)，最多关联到一条\(Documentation\)和一条\(Annotation\)。
现在想搜索\(Table\)。前端向后端提供3个参数，搜索关键词列表、当前页码、每页条数；后端的搜索逻辑是，如果一条完整数据（\(Table\)+\(Documentation\)+\(Annotation\)）包含所有搜索关键词，则将\(Table\)加入搜索结果中。
\(Table\)的数量目前为6000+，要做到秒级搜索。
初步实现

因为跨数据源，所以不能简单连表查询。
对于每个\(Table\)，查出\(Documentation\)、\(Annotation\)，然后将\(Table\)、\(Documentation\)、\(Annotation\)中要搜索的字段值取出来，用空格隔开拼接为字符串，形如"Table字段值 Documentation字段值 Annotation字段值"，我们称之为\(SearchKey\)（搜索键）。如果每个关键词都包含在\(SearchKey\)中，则将\(Table\)加入搜索结果。
搜索时，先获取所有\(Table\)，然后遍历每个\(Table\)，获取\(SearchKey\)并判断是否加入搜索结果。
为了提高速度，用Redis缓存\(Table\)对应的\(SearchKey\)。
分析数据情况：

• \(Table\)只增、不删、不改，因此，搜索时要重新获取所有\(Table\)，确保搜索到新\(Table\)；不必考虑驱逐（evict）\(SearchKey\)的缓存。
  
• \(Documentation\)不增、不删、不改，因此，不必考虑驱逐\(SearchKey\)的缓存。
  
• \(Annotation\)增、删、改，因此，要在\(Annotation\)增删改之后驱逐对应\(SearchKey\)的缓存，确保搜索到\(Annotation\)的最新信息。
  

实测结果：

• 实现了功能，支持同时按\(Table\)、\(Documentation\)、\(Annotation\)的字段搜索。
  
• 有性能问题，即使缓存已经全部完成，但每次搜索都要耗时30s左右，原因是6000+个\(Table\)遍历从Redis获取\(SearchKey\)，每次耗时1~15ms，累计耗时非常长。
  

第一次性能优化

优化缓存策略。
获取所有\(Table\)后，构建\(SearchKeyMap\)（\(Table\)→\(SearchKey\)），然后将\(SearchKeyMap\)缓存，这样，下一次搜索时，只需要从Redis获取一次，提高传输效率。
为了确保搜索到新\(Table\)，缓存\(SearchKeyMap\)时将\(Table\)列表的长度作为缓存键，如果新增了\(Table\)，则\(SearchKeyMap\)不会命中缓存，而是重新构建。
为了减少构建\(SearchKeyMap\)的时间，仍然保留单个\(SearchKey\)的缓存，仍然在\(Annotation\)增删改之后驱逐单个\(SearchKey\)的缓存，但不同的是，还要同时驱逐\(SearchKeyMap\)的缓存。
实测结果：

• 性能提升明显，在缓存全部完成的情况下，搜索耗时降至1.3s左右。
  
• 仍然有性能问题，对一个\(Annotation\)做了增删改，会驱逐整个\(SearchKeyMap\)缓存，重建\(SearchKeyMap\)就又回到了遍历\(Table\)的情况，仍然要耗时30s左右。
  

第二次性能优化

优化缓存策略。
取消单个\(SearchKey\)的缓存，只缓存\(SearchKeyMap\)。
搜索\(Table\)时，要获取\(SearchKeyMap\)。先获取现有的\(SearchKeyMap\)缓存（固定缓存键，不再使用列表长度作为缓存键；没有缓存则取得空Map），然后遍历\(Table\)，如果\(Table\)不在\(SearchKeyMap\)中，则计算\(SearchKey\)并放入\(SearchKeyMap\)。这样，第一次搜索时会计算每个\(Table\)的\(SearchKey\)，后续搜索就只需要计算新\(Table\)的\(SearchKey\)。
\(Annotation\)增删改后，要更新\(SearchKeyMap\)。先获取现有的\(SearchKeyMap\)缓存，然后重新计算指定\(Table\)的\(SearchKey\)并放入\(SearchKeyMap\)。这样，无需每次都重建整个\(SearchKeyMap\)。
实测结果：\(Annotation\)增删改后再搜索，耗时降至1.3s左右。
第三次性能优化

优化缓存实现方式。
既然现在只需要简单地缓存一个\(SearchKeyMap\)，那么不一定要用Redis。
使用Redis作为缓存（RedisCacheManager），虽然内网通信快，但仍有网络开销。实测平均1092.9ms。
使用Map作为缓存（ConcurrentMapCacheManager），其他代码完全不变。实测平均968.3ms。
修改代码，直接用类中的Map字段作为缓存，省去缓存管理器的开销。实测平均915.2ms。
可见，性能有提升，但幅度不大。由于软件在开发中，要频繁重新运行，Redis能保持缓存，Map不能，因此保持上一版方案不做修改。
第四次性能优化

第三次优化其实是盲目的，应该要用事实找出性能瓶颈。
对搜索过程计时分析发现，一次耗时1105ms的搜索，其中获取所有\(Table\)耗时1028ms，占比93%，是绝对的性能瓶颈。
思路1：先只获取所有表名，而不是\(Table\)对象，如果表名对应的\(SearchKey\)匹配，再获取\(Table\)。实测发现，如果匹配的表名很多（例如关键词列表为空时），则即使有表名→\(Table\)的缓存（Redis实现），逐个获取也远远慢于直接从数据库一次性获取。因此，此思路不可行。
思路2：\(Table\)只增、不删、不改，因此可以考虑增量获取。缓存\(Table\)列表，每次获取时跳过缓存的长度，只获取增量部分。然而，information_schema.tables中没有id，无法保证新\(Table\)一定排在最后。因此，此思路不可行。
思路3：获取所有\(Table\)说到底只是为了搜索到新\(Table\)，如果能知道什么时候新增了\(Table\)，就可以放心地使用\(Table\)列表的缓存，或者从数据库重新获取。那么怎么知道？由于\(Table\)只增，所以可以用\(Table\)的数量判断。缓存\(Table\)列表，每次先从数据库查出数量（比直接查出\(Table\)列表明显更快），如果数量与缓存一致，则用缓存，否则查库。实测，此思路可行。
实现思路3后，再次计时分析。无新增\(Table\)时，搜索耗时降至360ms左右（只查库数量）；有新增时，耗时升至1.5s左右（查库数量+列表）。由于搜索\(Table\)的频率远远高于新增\(Table\)，因此，总体性能提升显著。
总结

经过数次性能优化，在满足功能的前提下，搜索时间从30s左右降至稳定0.4s左右，效果显著。0.4s已经没有缓慢感，性能优化工作可以结束了。
从上述优化过程可见，做优化要因地制宜，具体问题具体分析，选择合适的策略；优化效果的衡量要以实测结果为准。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！