如何实现一个高性能的以时间为条件的查询器?

现在存在一个案例:

现有一个插入线程不断的往数据库里里面插入数据:

[
    {"ts": 1562902203, "event": "product1", "direction": "buy", "price": 0.8},
    {"ts": 1562902204, "event": "product1", "direction": "sell", "price": 0.8}
]

现在存在N个查询线程在做查询操作,查询内容有:

  1. 当前时间减去X时间内的最高价
  2. 当前时间减去X时间内的最低价

因为存在两个因素: 时间价格 ,所以这两个都得加索引。 查询频率极高,

假设X等于 5分钟, 当前是15:00:00,查询最高价 , 查询条件是 14:55:00 - 15:00:00内的最高价,假设是14:58:00是最高价。
如果当前是 15:00:01其他不变, 查询条件是 14:55:01 - 15:00:01内的最高价,结果很可能仍然是14:58:00是最高价。

两种情况的实际结果很大的情况下是一致的。 所以出现了大量的查询纯粹是浪费资源。
但是 15:00:01是最高价的情况也出现过多次,需求也对数据精准有高要求。

现有的运行方案是: mysql 5.7 ts和price都加索引。

select * from data where `ts` >=  '14:55:00' order by price desc limit 1

现在经常会出现mysql的CPU压力特别高,内存压力特别小。
现在希望得到一个方案,脱离数据库来排序获取,自己实现一个高效的方案,尽量把查询压力放到应用服务器上来。


补充一下,看到大家的答案都是在讨论怎么缓存历史最高价。

重点是 14:55:01 - 15:00:01 的最高价 与 14:55:00-15:00:00 不一定是重合的,只是可能重合。
如果重合,那么是可以存下来 last_max_price,用于减少筛选范围。

但是在查询前是不知道是否重合的,而且这个历史的last_max_price,只对重合有效,如果不重合是完全没有意义的。

这个需求的最大问题是 区间每次都是变化的。下一次查询的起点是 14:55:01,上一次是 14:55:00,起点不同
结尾是 15:00:01, 上一次是 15:00:00,也是不同的,如果上一次的最高价出现在 14:55:00,那么现有答案的缓存方案都是无效的。

阅读 621
评论
    3 个回答

    方案一:
    你这个时间是基于当前时间之前多少分钟的数据,从你描述的业务场景来看,写比较少,主要是读比较大,建立一个5分钟缓存的数据,从中获取最大价格的数值,这个会涉及一些数据结构,来缓存数据,便于剔除过期的数据和查找最大价格的数据,在读取的时候完全从缓存获取数据。

    方案二:
    你这个需求更接近一个概览统计的场景,实际上并不需要太过精确,可以设定时间精度,比如1分钟、10秒钟、30秒钟,甚至是1秒钟。其实1秒钟完全没有必要,为什么这么说你查询需要1秒,程序处理需要1秒,到达客户端需要1秒,客户看到之后可能都是5秒前查询的数据了,这样实时就没有必要了。一般概览统计在1分钟内的精度都是可以接受的, 那么你就有时间做缓存的,而且看你留言QPS在 1K左右 只要保证每秒钟第一个请求去查询,之后的999的请求都是用第一个请求的结果,实际也是可以接受的。

    比如当前时间 14:55:28 精度 10秒 ts >= '14:50:20' 精度30秒 ts >= '14:50:00', 精度1分钟:ts >= '14:50:00', 控制查询精度可以大幅度提升缓冲命中率。

    方案三:
    如果你只需要最高的价格,或者最低的价格,建议 select max(price), min(price) from data where ts >= '14:55:00' 是用这样的方法比排序效率更高。
    你过你还需要其它的数据 select * from data where ts >= '14:55:00' and price=max_price limit 1
    用两条简单的查询效率会往往更高一些,但要保证服务器与数据通信网络的性能。