# [FAQ] 使用分区键查询分区表无法利用 Range 分区的有序性

## Key Words

Partition, Range, Limit, TopN, keep order

## Description

对 range 分区表，使用带分区键的过滤条件进行排序时，发现无法很好的利用分区键的有序性来提升数据过滤和排序效率。SQL 示例：

```sql
mysql> create table t1(id int primary key, v int) partition by range(id) (partition p1 values less than(10000), partition p2 values less than(20000));
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from t1 where id > 20001 order by id limit 1;
+-----------------------------+---------+-----------+----------------+----------------------------------------------------+
| id                          | estRows | task      | access object  | operator info                                      |
+-----------------------------+---------+-----------+----------------+----------------------------------------------------+
| TopN_8                      | 1.00    | root      |                | test.t1.id, offset:0, count:1                      |
| └─TableReader_17            | 1.00    | root      | partition:dual | data:TopN_16                                       |
|   └─TopN_16                 | 1.00    | cop[tikv] |                | test.t1.id, offset:0, count:1                      |
|     └─TableRangeScan_15     | 3333.33 | cop[tikv] | table:t1       | range:(20001,+inf], keep order:false, stats:pseudo |
+-----------------------------+---------+-----------+----------------+----------------------------------------------------+
4 rows in set (0.00 sec)

mysql> set tidb_partition_prune_mode='dynamic';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t1 where id > 20001 order by id limit 1;
+-----------------------------+---------+-----------+----------------+----------------------------------------------------+
| id                          | estRows | task      | access object  | operator info                                      |
+-----------------------------+---------+-----------+----------------+----------------------------------------------------+
| TopN_8                      | 1.00    | root      |                | test.t1.id, offset:0, count:1                      |
| └─TableReader_17            | 1.00    | root      | partition:dual | data:TopN_16                                       |
|   └─TopN_16                 | 1.00    | cop[tikv] |                | test.t1.id, offset:0, count:1                      |
|     └─TableRangeScan_15     | 3333.33 | cop[tikv] | table:t1       | range:(20001,+inf], keep order:false, stats:pseudo |
+-----------------------------+---------+-----------+----------------+----------------------------------------------------+
4 rows in set (0.00 sec)
```

更优的 plan 应该是：

```sql
mysql> explain select * from t1 where id > 20001 order by id limit 1;
+-----------------------------+---------+-----------+----------------+----------------------------------------------------+
| id                          | estRows | task      | access object  | operator info                                      |
+-----------------------------+---------+-----------+----------------+----------------------------------------------------+
| Limit_8                     | 1.00    | root      |                | test.t1.id, offset:0, count:1                      |
| └─TableReader_17            | 1.00    | root      | partition:dual | data:TopN_16                                       |
|   └─Limit_16                | 1.00    | cop[tikv] |                | test.t1.id, offset:0, count:1                      |
|     └─TableRangeScan_15     | 3333.33 | cop[tikv] | table:t1       | range:(20001,+inf], keep order:true, stats:pseudo |
+-----------------------------+---------+-----------+----------------+----------------------------------------------------+

```

## Content

该问题属于产品未实现功能，具体跟进 issue：<https://github.com/pingcap/tidb/issues/26166>。建议可以升级到 v6.5.0 以上版本。

## Workaround

N/A

## Affected Versions

 ~ v6.5.0