mysql - mysql中大数据如何优化id和限制查询？

Question

我在 mysql 表中有十亿行，我想使用索引字段(比如时间戳)查询该表。

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

当我处理最多 5000000 行数据(即 10000 个查询)时，此查询工作正常，但是当我将查询数量增加到(比方说)50000 个时，我可以看到性能随着时间的推移而下降。查询过去在启动时需要 5-10 毫秒，但运行很长时间后，它会降低到 2 秒。我该如何优化它？

我之前尝试过一种幼稚的解决方案，即限制、偏移量，它给出了高度未优化的结果，因此我尝试通过保存最后一个 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

请提供显示创建表。同时，如果您有INDEX(timestamp)，则不需要和id...。事实上，它可能会妨碍 ORDER BY 的优化。

所以，如果您的查询是这样的:

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

并且你有INDEX(timestamp)，那么它就得到了很好的优化，并且不会减慢速度(除了缓存问题)。

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