Totally support pagination queries of MySQL, PostgreSQL and Oracle; partly support SQLServer pagination query due to its complexity.
Pagination with query offset too high can lead to a low data accessibility, take MySQL as an example:
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10
This SQL will make MySQL acquire another 10 records after skipping 1,000,000 records when it is not able to use indexes. Its performance can thus be deduced. In sharding databases and sharding tables (suppose there are two databases), to ensure the data correctness, the SQL will be rewritten as this:
SELECT * FROM t_order ORDER BY id LIMIT 0, 1000010
It also means taking out all the records prior to the offset and only acquire the last 10 records after ordering. It will further aggravate the performance bottleneck effect when the database is already slow in execution. The reason for that is the former SQL only needs to transmit 10 records to the user end, but now it will transmit 1000010 * 2 records after the rewrite.
ShardingSphere has optimized in two ways.
Firstly, it adopts stream process + merger ordering to avoid excessive memory occupation. SQL rewrite unavoidably occupies extra bandwidth, but it will not lead to sharp increase of memory occupation. Most people may assume that ShardingSphere would upload all the 1,000,010 * 2 records to the memory and occupy a large amount of it, which can lead to memory overflow. But each ShardingSphere comparison only acquires current result set record of each shard, since result set records have their own order.
The record stored in the memory is only the current position pointed by the cursor in the result set of the shard routed to. For the item to be sorted which has its own order, merger ordering only has the time complexity of O(n), with a very low performance consumption.
Secondly, ShardingSphere further optimizes the query that only falls into single shards. Requests of this kind can guarantee the correctness of records without rewriting SQLs. Under this kind of situation, ShardingSphere will not do that in order to save the bandwidth.
For LIMIT cannot search for data through indexes, if the ID continuity can be guaranteed, pagination by ID is a better solution:
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
Or use the ID of last record of the former query result to query the next page:
SELECT * FROM t_order WHERE id > 100000 LIMIT 10
Both Oracle and SQLServer paginations need to be processed by sub-query, ShardingSphere supports pagination related sub-query.
Support rownum pagination:
SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT o.order_id as order_id FROM t_order o JOIN t_order_item i ON o.order_id = i.order_id) row_ WHERE rownum <= ?) WHERE rownum > ?
Do not support rownum + BETWEEN pagination for now.
Support TOP + ROW_NUMBER() OVER pagination:
SELECT * FROM (SELECT TOP (?) ROW_NUMBER() OVER (ORDER BY o.order_id DESC) AS rownum, * FROM t_order o) AS temp WHERE temp.rownum > ? ORDER BY temp.order_id
Support OFFSET FETCH pagination after SQLServer 2012:
SELECT * FROM t_order o ORDER BY id OFFSET ? ROW FETCH NEXT ? ROWS ONLY
Do not support WITH xxx AS (SELECT ...) pagination. Because SQLServer automatically generated by Hibernate uses WITH statements, Hibernate SQLServer pagination or two TOP + sub-query pagination is not available now.
Both MySQL and PostgreSQL support LIMIT pagination, no need for sub-query:
SELECT * FROM t_order o ORDER BY id LIMIT ? OFFSET ?