数据库表的性能优化过程

问题背景做一个数据库表查看、标注与分析的工具软件。是数据库中表的信息information_schema.tables是的数据字典文档存储在本地文件中是对的额外标注信息存储在另一个数据库中。每一条最多关联到一条和一条。现在想搜索。前端向后端提供3个参数搜索关键词列表、当前页码、每页条数后端的搜索逻辑是如果一条完整数据包含所有搜索关键词则将加入搜索结果中。的数量目前为6000要做到秒级搜索。初步实现因为跨数据源所以不能简单连表查询。对于每个查出、然后将、、中要搜索的字段值取出来用空格隔开拼接为字符串形如Table字段值 Documentation字段值 Annotation字段值我们称之为搜索键。如果每个关键词都包含在中则将加入搜索结果。搜索时先获取所有然后遍历每个获取并判断是否加入搜索结果。为了提高速度用Redis缓存对应的。分析数据情况只增、不删、不改因此搜索时要重新获取所有确保搜索到新不必考虑驱逐evict的缓存。不增、不删、不改因此不必考虑驱逐的缓存。增、删、改因此要在增删改之后驱逐对应的缓存确保搜索到的最新信息。实测结果实现了功能支持同时按、、的字段搜索。有性能问题即使缓存已经全部完成但每次搜索都要耗时30s左右原因是6000个遍历从Redis获取每次耗时1~15ms累计耗时非常长。第一次性能优化优化缓存策略。获取所有后构建→然后将缓存这样下一次搜索时只需要从Redis获取一次提高传输效率。为了确保搜索到新缓存时将列表的长度作为缓存键如果新增了则不会命中缓存而是重新构建。为了减少构建的时间仍然保留单个的缓存仍然在增删改之后驱逐单个的缓存但不同的是还要同时驱逐的缓存。实测结果性能提升明显在缓存全部完成的情况下搜索耗时降至1.3s左右。仍然有性能问题对一个做了增删改会驱逐整个缓存重建就又回到了遍历的情况仍然要耗时30s左右。第二次性能优化优化缓存策略。取消单个的缓存只缓存。搜索时要获取。先获取现有的缓存固定缓存键不再使用列表长度作为缓存键没有缓存则取得空Map然后遍历如果不在中则计算并放入。这样第一次搜索时会计算每个的后续搜索就只需要计算新的。增删改后要更新。先获取现有的缓存然后重新计算指定的并放入。这样无需每次都重建整个。实测结果增删改后再搜索耗时降至1.3s左右。第三次性能优化优化缓存实现方式。既然现在只需要简单地缓存一个那么不一定要用Redis。使用Redis作为缓存RedisCacheManager虽然内网通信快但仍有网络开销。实测平均1092.9ms。使用Map作为缓存ConcurrentMapCacheManager其他代码完全不变。实测平均968.3ms。修改代码直接用类中的Map字段作为缓存省去缓存管理器的开销。实测平均915.2ms。可见性能有提升但幅度不大。由于软件在开发中要频繁重新运行Redis能保持缓存Map不能因此保持上一版方案不做修改。第四次性能优化第三次优化其实是盲目的应该要用事实找出性能瓶颈。对搜索过程计时分析发现一次耗时1105ms的搜索其中获取所有耗时1028ms占比93%是绝对的性能瓶颈。思路1先只获取所有表名而不是对象如果表名对应的匹配再获取。实测发现如果匹配的表名很多例如关键词列表为空时则即使有表名→的缓存Redis实现逐个获取也远远慢于直接从数据库一次性获取。因此此思路不可行。思路2只增、不删、不改因此可以考虑增量获取。缓存列表每次获取时跳过缓存的长度只获取增量部分。然而information_schema.tables中没有id无法保证新一定排在最后。因此此思路不可行。思路3获取所有说到底只是为了搜索到新如果能知道什么时候新增了就可以放心地使用列表的缓存或者从数据库重新获取。那么怎么知道由于只增所以可以用的数量判断。缓存列表每次先从数据库查出数量比直接查出列表明显更快如果数量与缓存一致则用缓存否则查库。实测此思路可行。实现思路3后再次计时分析。无新增时搜索耗时降至360ms左右只查库数量有新增时耗时升至1.5s左右查库数量列表。由于搜索的频率远远高于新增因此总体性能提升显著。总结经过数次性能优化在满足功能的前提下搜索时间从30s左右降至稳定0.4s左右效果显著。0.4s已经没有缓慢感性能优化工作可以结束了。从上述优化过程可见做优化要因地制宜具体问题具体分析选择合适的策略优化效果的衡量要以实测结果为准。