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

Question

现在存在一个案例：

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

[
    {"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，那么现有答案的缓存方案都是无效的。

Answer 1

方案一 SQL查询（利用 SQL 减少操作数据量，最大化提高 SQL 的执行效率）：

最高价、最低价对于操作相同业务的用户来说是一致，那可以在 JVM 内存中缓存上次查询获得的最高价（last_max_price）。在使用 SQL 查询时加入 last_max_price 条件，可以减少数据库操作的数据量同时减少排序的数据，提高 SQL 的执行效率。

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

上面的 SQL 如果未返回数据，则表示当前内存的价格为最高。反之如果返回数据则更新 JVM 内存缓存的价格。如果你大部分情况下返回的都是相同的数据，上面的 SQL 可以让你在大部分场景下都只需要操作一条数据。

方案二（分布式缓存）：

可以利用 redis zset 数据结构的特性，每次查询价格时，直接通过 redis 返回，每次可精确定位最高价与最低价的值。

---

当有复数条件排序时，可以根据自身的业务组合数字排序完成目标。
就比如：时间间 + 价格 需要这2个不同维度进行排序数据筛选可以模仿如下的方式设计 score。
format：########.########
score 的前部分为时间 timestamp，小数部分为价格 price。

ZRANGEBYSCORE key begin_timestamp end_timestamp

利用上面的命令即可取出某个时间段内价格的排序情况，这种方式需要程序对 score 做额外的处理。获取到 score 需要对分数做 时间、价格 的拆分。

Answer 2

最高最低存在分配律： (区间1 + 区间2) 的max === max(区间1的max，区间2的max)

因此如果在内存中有一些区间的缓存，一次查询结果的大半可能可以用缓存拼出来，拼不出的部分（小于最小缓存单位的区间，还不在缓存的区间）才用DB

具体怎么缓存比较随意，可能树或map都行

注意：如果新数据的ts可能是任意值，你还需要正确处理缓存过期

Answer 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
用两条简单的查询效率会往往更高一些，但要保证服务器与数据通信网络的性能。