如何在mysql中优化id和限制海量数据的查询？

Question

我在mysql表中有十亿行，我想用一个索引字段来查询表，比如说时间戳。

我想查询最近7天的数据，它可以是大约1000000行，我是根据最近获取的id和限制进行查询的。

当我处理最多5000000行数据，即10000个查询时，这个查询可以很好地工作，但是当我将查询数增加到50000行时，我可以看到性能随着时间的推移而下降。查询在开始时需要5-10毫秒，但在长时间运行后，它降级到2秒。我如何优化这一点？

我之前尝试了一个简单的解决方案，它是limit，offset，它给出了高度未优化的结果，所以我试图通过保存最后的id并在每次查询时添加最后的id来优化它，但是如果我继续一个接一个地获取3-4个小时，性能就会再次超时。

JAVA :使用Hibernate和切片

Date date = new Date();
Date timestamp = new DateTime(date).minusDays(7).toDate();

while (true) {
    Integer rowLimit = 500;
    Sort.Order sortingOrder = Sort.Order.asc("timestamp");
    Sort sort = Sort.by(sortingOrder);

    Pageable pageable = PageRequest.of(0, rowLimit, sort);

    long queryStartTime = System.currentTimeMillis();

    entityDataSlice = repository.findAllByTimestampAfterAndIdGreaterThan(
          timestamp, lastId, pageable
    );

    long queryEndTime = System.currentTimeMillis();

    if (!entityDataSlice.hasNext()) {
          break;
    }
}

MYSQL：

select *  
 from table 
where timestamp >= "some_time" 
  and id >= <some_id> 
order 
   by timestamp 
limit 500

预期的结果是性能优化，但随着时间的推移，它会降级。

预计最多100毫秒超时，但实际上最多2-3秒，更有可能进一步降级到5-10秒

Answer 1

请提供SHOW CREATE TABLE。同时，如果你有INDEX(timestamp)，你不需要and id...。事实上，它可能会阻碍ORDER BY的优化。

因此，如果您的查询是：

select  *
    from  table
    where  timestamp >= "some_time"
    order by  timestamp
    limit  500

如果你有INDEX(timestamp)，那么它就会得到很好的优化，而且不会变慢(除了缓存问题)。

如果这只是“真实”查询的简化版本，那么所有的赌注都是错误的。