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

问题背景

做一个数据库表查看、标注与分析的工具软件。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缓存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已经没有缓慢感，性能优化工作可以结束了。

从上述优化过程可见，做优化要因地制宜，具体问题具体分析，选择合适的策略；优化效果的衡量要以实测结果为准。