通过索引优化和缓存更新策略的实际应用快速响应百万级数据查询

背景描述

最近有一个需求是要在一张存储着有存储着百万条数据的表里面进行操作。

在这百万条数据里面，每一条数据里面都有着父类类型以及自身对应的类型，需求是需要根据地区来统计父类类型和自身对应的类型，如果地区选择的是全国，那么操作的就是这张表里面的全部数据了，但如果自身想要查看的省市区相关的数据，那么就需要根据所选择的省市区来进行统计。

实现中出现的问题

统计全国的数据，原本的方案是首先获取到所有的父类类型，然后再去查父类类型下所有拥有的子属性，也就是自身对应的属性，然后再去根据地区来进行统计父类属性和子类属性的数量。

但是这样做的话，查询的次数太多并且由于是一张大宽表，接口的响应时间直接干到1分钟去了。

所以，我就从多次查询优化成一次查询，直接一次查询出子类属性的数量，子类属性的名称，以及子类属性所属的父类属性。

但是这样做接口还是响应的很慢，在父类属性和子类属性上加索引的基础上，接口的响应时间还是有14秒，还是太慢了。

解决方案

我想到三种方案，

• 操作多线程来并行查询；
• 直接将查询结果建成一张表，也就是物化视图；
• 利用定时任务每隔一段时间就执行定时任务将查询结果更新到缓存里面。
  这里选择的是第三种，说说为什么没有选择第一种，原因在于如果在代码层面操作多线程来进行查询的话，不好控制核心线程数和临时线程数的数量。

再来说说，为什么不选择第二种，如果选择第二种的话，那么就需要定时自动更新这张表，每隔一段时间就将表里面的查询结果更新一下，但是使用到数据库是postgresql，在数据库层面上不支持自动更新查询结果，所以也pass了~

最后选择的第三种方案，我觉得是最合适的，由于查询出来的数据量其实很少，只是查询花费的时间很多，所以使用定时任务每隔一个小时就跑一次数据，将数据存储在本地缓存里面，同时这张表的数据是更新的频率比较低，所以缓存和数据库数据不一致的问题虽然存在，但是发生的概率比较小。

同时由于这个项目是toB的，所以有很多个租户，定时任务是加载所有的租户，将所有租户的这张表里面的查询结果都存储在本地缓存里面，计算了一下查询时间以及租户数量，将定时任务的执行时间设置为每隔一个小时执行一次，这样就不会出现由于查询时间过长导致超出应该在规定时间内完成的定时任务没有完成的情况。

通过这样的做法，查询全国的数据接口的响应时间直接降低到了300ms以内。查询省市区的相关统计结果，由于有了查询条件的限制以及索引的加成，所以接口的响应时间基本也在500ms以内了。