[Tips] Batch fetch database data

In some business scenarios you need to take all the data in a database table for processing, the easiest way is to use the paging query (lazy MySQL for example):

SELECT * FROM DataTable limit Offset,amount

There is a problem here, as the value of the offset is getting bigger and larger, the table data to be scanned by this SQL will be more and more, because to locate the offset line, you need to scan all rows smaller than offset. Obviously in a large data table, to do so, performance will be problematic. To avoid such a situation, we naturally think of using an index to solve it, such as using an automatic sequence ID for batch fetching:

SELECT * from DataTable where ID >= start and Id<start+batchcount

This allows us to index the ID, and then batch to fetch, obviously the effect will be much higher, but if the self-added sequence because the deletion and other operations become not continuous, there will be empty execution and multiple execution occurs. To resolve this approach, we need to work with the advantages of indexing and paging:

SELECT * from DataTable where ID >= start limit batchcount

Then we calculate the starting ID value each time we retrieve it, and then we take a batch of data. This can avoid the first not to go to the index, query performance problems, and solve the second ID discontinuity, resulting in the return of data volume instability caused by the problem of waste.