tencent cloud

TDSQL Boundless

DocumentaçãoTDSQL BoundlessKernel FeaturesColumnar Read-Only Analysis Engine FeaturesOrdered Pagination Feature

Ordered Pagination Feature

Baixar
Modo Foco
Tamanho da Fonte
Última atualização: 2026-07-09 09:54:04
In MySQL, when data is queried with an ORDER BY clause and the ORDER BY field contains duplicate values, using a LIMIT clause for pagination may cause data on the second page to duplicate that on the first page. This occurs because when the sort key has duplicate values, the sorting results are non-deterministic across multiple executions, and the output order may be inconsistent. This instability is further exacerbated by multi-threaded sorting operations, especially during parallel SQL execution. Therefore, the recommended practice for queries using ORDER BY ... LIMIT ... is to ensure the ORDER BY field has an index, or to append additional fields to the ORDER BY clause, making the overall sort key unique.
The read-only analysis instance supports a pagination order-preserving feature to prevent this issue. After you enable pagination order preservation, users do not need to concern themselves with the underlying sorting implementation logic of the database. Even if the sorting field contains duplicate values, the output of ORDER BY ... LIMIT ... will not exhibit cross-page duplication, thereby ensuring the stability of paginated results.

Implementation Principles

After pagination order preservation is enabled, the read-only analysis instance automatically appends the table's unique key to the end of the sort key while preserving the user's original sorting semantics. This makes the overall sort key globally unique, thereby eliminating the uncertainty in paginated results.

Ordered Pagination Scenarios

A query without an ORDER BY clause but only with a LIMIT clause.
A query where the ORDER BY field contains duplicate values.
A scenario where a subquery contains sorting operations, but the outer query does not.
A scenario where the GROUP BY field contains duplicate values and pagination is performed using LIMIT in the outer query.

Impact on Performance

After the pagination order-preserving feature is enabled, additional sorting operations are introduced, which may cause performance regression for some queries. Please decide whether to use the pagination order-preserving capability based on your actual business requirements.
Anterior: ETL Writeback AccelerationPróximo: Billing Overview

Ajuda e Suporte

Esta página foi útil?

comentários