感谢解答，我做了一个测试，使用如下语句建表
test_clustered - 聚簇索引，WEEKDAY分区
test_clustered_seq - 聚簇索引，按月分区
test_nonclustered - 非聚簇索引，WEEKDAY分区
test_nonclustered_seq - 非聚簇索引，按月分区
-- test.test_clustered definition
CREATE TABLE `test_clustered` (
`day` date NOT NULL,
`partner_org_id` varchar(60) NOT NULL,
`partner_name` text DEFAULT NULL,
`customer_org_id` varchar(60) NOT NULL,
`license_id` varchar(255) NOT NULL,
PRIMARY KEY (`day`,`partner_org_id`,`customer_org_id`,`license_id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (WEEKDAY(`day`))
(PARTITION `p0` VALUES LESS THAN (1),
PARTITION `p1` VALUES LESS THAN (2),
PARTITION `p2` VALUES LESS THAN (3),
PARTITION `p3` VALUES LESS THAN (4),
PARTITION `p4` VALUES LESS THAN (5),
PARTITION `p5` VALUES LESS THAN (6),
PARTITION `p6` VALUES LESS THAN (7));
-- test.test_clustered_seq definition
CREATE TABLE `test_clustered_seq` (
`day` date NOT NULL,
`partner_org_id` varchar(60) NOT NULL,
`partner_name` text DEFAULT NULL,
`customer_org_id` varchar(60) NOT NULL,
`license_id` varchar(255) NOT NULL,
PRIMARY KEY (`day`,`partner_org_id`,`customer_org_id`,`license_id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (TO_DAYS(`day`))
(PARTITION `p201912` VALUES LESS THAN (737790),
PARTITION `p202001` VALUES LESS THAN (737821),
PARTITION `p202002` VALUES LESS THAN (737850),
PARTITION `p202003` VALUES LESS THAN (737881),
PARTITION `p202004` VALUES LESS THAN (737911),
PARTITION `p202005` VALUES LESS THAN (737942),
PARTITION `p202006` VALUES LESS THAN (737972),
PARTITION `p202007` VALUES LESS THAN (738003),
PARTITION `p202008` VALUES LESS THAN (738034),
PARTITION `p202009` VALUES LESS THAN (738064),
PARTITION `p202010` VALUES LESS THAN (738095),
PARTITION `p202011` VALUES LESS THAN (738125),
PARTITION `p202012` VALUES LESS THAN (738156),
PARTITION `p202101` VALUES LESS THAN (738187),
PARTITION `p202102` VALUES LESS THAN (738215),
PARTITION `p202103` VALUES LESS THAN (738246),
PARTITION `p202104` VALUES LESS THAN (738276),
PARTITION `p202105` VALUES LESS THAN (738307),
PARTITION `p202106` VALUES LESS THAN (738337),
PARTITION `p202107` VALUES LESS THAN (738368),
PARTITION `p202108` VALUES LESS THAN (738399),
PARTITION `p202109` VALUES LESS THAN (738429),
PARTITION `p202110` VALUES LESS THAN (738460),
PARTITION `p202111` VALUES LESS THAN (738490),
PARTITION `p202112` VALUES LESS THAN (738521),
PARTITION `p202201` VALUES LESS THAN (738552),
PARTITION `p202202` VALUES LESS THAN (738580),
PARTITION `p202203` VALUES LESS THAN (738611),
PARTITION `p202204` VALUES LESS THAN (738641),
PARTITION `p202205` VALUES LESS THAN (738672),
PARTITION `p202206` VALUES LESS THAN (738702),
PARTITION `p202207` VALUES LESS THAN (738733),
PARTITION `p202208` VALUES LESS THAN (738764),
PARTITION `p202209` VALUES LESS THAN (738794),
PARTITION `p202210` VALUES LESS THAN (738825),
PARTITION `p202211` VALUES LESS THAN (738855),
PARTITION `p202212` VALUES LESS THAN (738886),
PARTITION `p202301` VALUES LESS THAN (738917),
PARTITION `p202302` VALUES LESS THAN (738945),
PARTITION `p202303` VALUES LESS THAN (738976),
PARTITION `p202304` VALUES LESS THAN (739006),
PARTITION `p202305` VALUES LESS THAN (739037),
PARTITION `p202306` VALUES LESS THAN (739067),
PARTITION `p202307` VALUES LESS THAN (739098),
PARTITION `p202308` VALUES LESS THAN (739129),
PARTITION `p202309` VALUES LESS THAN (739159),
PARTITION `p202310` VALUES LESS THAN (739190),
PARTITION `p202311` VALUES LESS THAN (739220),
PARTITION `p202312` VALUES LESS THAN (739251));
-- test.test_nonclustered definition
CREATE TABLE `test_nonclustered` (
`day` date NOT NULL,
`partner_org_id` varchar(60) NOT NULL,
`partner_name` text DEFAULT NULL,
`customer_org_id` varchar(60) NOT NULL,
`license_id` varchar(255) NOT NULL,
PRIMARY KEY (`day`,`partner_org_id`,`customer_org_id`,`license_id`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (WEEKDAY(`day`))
(PARTITION `p0` VALUES LESS THAN (1),
PARTITION `p1` VALUES LESS THAN (2),
PARTITION `p2` VALUES LESS THAN (3),
PARTITION `p3` VALUES LESS THAN (4),
PARTITION `p4` VALUES LESS THAN (5),
PARTITION `p5` VALUES LESS THAN (6),
PARTITION `p6` VALUES LESS THAN (7));
-- test.test_nonclustered_seq definition
CREATE TABLE `test_nonclustered_seq` (
`day` date NOT NULL,
`partner_org_id` varchar(60) NOT NULL,
`partner_name` text DEFAULT NULL,
`customer_org_id` varchar(60) NOT NULL,
`license_id` varchar(255) NOT NULL,
PRIMARY KEY (`day`,`partner_org_id`,`customer_org_id`,`license_id`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (TO_DAYS(`day`))
(PARTITION `p201912` VALUES LESS THAN (737790),
PARTITION `p202001` VALUES LESS THAN (737821),
PARTITION `p202002` VALUES LESS THAN (737850),
PARTITION `p202003` VALUES LESS THAN (737881),
PARTITION `p202004` VALUES LESS THAN (737911),
PARTITION `p202005` VALUES LESS THAN (737942),
PARTITION `p202006` VALUES LESS THAN (737972),
PARTITION `p202007` VALUES LESS THAN (738003),
PARTITION `p202008` VALUES LESS THAN (738034),
PARTITION `p202009` VALUES LESS THAN (738064),
PARTITION `p202010` VALUES LESS THAN (738095),
PARTITION `p202011` VALUES LESS THAN (738125),
PARTITION `p202012` VALUES LESS THAN (738156),
PARTITION `p202101` VALUES LESS THAN (738187),
PARTITION `p202102` VALUES LESS THAN (738215),
PARTITION `p202103` VALUES LESS THAN (738246),
PARTITION `p202104` VALUES LESS THAN (738276),
PARTITION `p202105` VALUES LESS THAN (738307),
PARTITION `p202106` VALUES LESS THAN (738337),
PARTITION `p202107` VALUES LESS THAN (738368),
PARTITION `p202108` VALUES LESS THAN (738399),
PARTITION `p202109` VALUES LESS THAN (738429),
PARTITION `p202110` VALUES LESS THAN (738460),
PARTITION `p202111` VALUES LESS THAN (738490),
PARTITION `p202112` VALUES LESS THAN (738521),
PARTITION `p202201` VALUES LESS THAN (738552),
PARTITION `p202202` VALUES LESS THAN (738580),
PARTITION `p202203` VALUES LESS THAN (738611),
PARTITION `p202204` VALUES LESS THAN (738641),
PARTITION `p202205` VALUES LESS THAN (738672),
PARTITION `p202206` VALUES LESS THAN (738702),
PARTITION `p202207` VALUES LESS THAN (738733),
PARTITION `p202208` VALUES LESS THAN (738764),
PARTITION `p202209` VALUES LESS THAN (738794),
PARTITION `p202210` VALUES LESS THAN (738825),
PARTITION `p202211` VALUES LESS THAN (738855),
PARTITION `p202212` VALUES LESS THAN (738886),
PARTITION `p202301` VALUES LESS THAN (738917),
PARTITION `p202302` VALUES LESS THAN (738945),
PARTITION `p202303` VALUES LESS THAN (738976),
PARTITION `p202304` VALUES LESS THAN (739006),
PARTITION `p202305` VALUES LESS THAN (739037),
PARTITION `p202306` VALUES LESS THAN (739067),
PARTITION `p202307` VALUES LESS THAN (739098),
PARTITION `p202308` VALUES LESS THAN (739129),
PARTITION `p202309` VALUES LESS THAN (739159),
PARTITION `p202310` VALUES LESS THAN (739190),
PARTITION `p202311` VALUES LESS THAN (739220),
PARTITION `p202312` VALUES LESS THAN (739251));
随后插入大约100000条数据，然后analyze table后分别执行下列查询
explain analyze select * from test_nonclustered_seq order by day limit 10;
TopN_7 10.00 10 root time:6.74ms, loops:2 test.test_nonclustered_seq.day, offset:0, count:10 2.37 KB N/A
└─TableReader_14 10.00 480 root partition:all time:6.7ms, loops:3, cop_task: {num: 49, max: 4.44ms, min: 241.6µs, avg: 1.4ms, p95: 3.77ms, max_proc_keys: 2161, p95_proc_keys: 2085, tot_proc: 19ms, tot_wait: 8ms, rpc_num: 49, rpc_time: 68ms, copr_cache_hit_ratio: 0.80, distsql_concurrency: 15} data:TopN_13 9.75 KB N/A
└─TopN_13 10.00 480 cop[tikv] tikv_task:{proc max:12ms, min:0s, avg: 4.33ms, p80:8ms, p95:12ms, iters:133, tasks:49}, scan_detail: {total_process_keys: 18528, total_process_keys_size: 3460454, total_keys: 18538, get_snapshot_time: 22ms, rocksdb: {key_skipped_count: 18528, block: {cache_hit_count: 85}}} test.test_nonclustered_seq.day, offset:0, count:10 N/A N/A
└─TableFullScan_12 100000.00 100000 cop[tikv] table:test_nonclustered_seq tikv_task:{proc max:12ms, min:0s, avg: 4ms, p80:8ms, p95:12ms, iters:133, tasks:49} keep order:false N/A N/A
--------------------------------------------------------------------------------------------------------
explain analyze select * from test_nonclustered order by day limit 10;
TopN_7 10.00 10 root time:10.7ms, loops:2 test.test_nonclustered.day, offset:0, count:10 2.37 KB N/A
└─TableReader_14 10.00 70 root partition:all time:10.7ms, loops:3, cop_task: {num: 7, max: 10.5ms, min: 810.6µs, avg: 2.26ms, p95: 10.5ms, max_proc_keys: 14225, p95_proc_keys: 14225, tot_proc: 9ms, rpc_num: 7, rpc_time: 15.7ms, copr_cache_hit_ratio: 0.86, distsql_concurrency: 15} data:TopN_13 5.77 KB N/A
└─TopN_13 10.00 70 cop[tikv] tikv_task:{proc max:16ms, min:12ms, avg: 15.4ms, p80:16ms, p95:16ms, iters:100, tasks:7}, scan_detail: {total_process_keys: 14225, total_process_keys_size: 2656841, total_keys: 14226, get_snapshot_time: 2.73ms, rocksdb: {key_skipped_count: 14225, block: {cache_hit_count: 47}}} test.test_nonclustered.day, offset:0, count:10 N/A N/A
└─TableFullScan_12 100000.00 100000 cop[tikv] table:test_nonclustered tikv_task:{proc max:16ms, min:12ms, avg: 14.3ms, p80:16ms, p95:16ms, iters:100, tasks:7} keep order:false N/A N/A
--------------------------------------------------------------------------------------------------------
explain analyze select * from test_clustered_seq order by day desc limit 10;
TopN_7 10.00 10 root time:3.07s, loops:2 test.test_clustered_seq.day:desc, offset:0, count:10 9.48 KB N/A
└─TableReader_14 10.00 100000 root partition:all time:3.07s, loops:100, cop_task: {num: 10025, max: 1.28ms, min: 197.8µs, avg: 294.1µs, p95: 364.1µs, max_proc_keys: 10, p95_proc_keys: 10, rpc_num: 10027, rpc_time: 2.9s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1}, backoff{regionMiss: 2ms} data:Limit_13 3.96 KB N/A
└─Limit_13 10.00 100000 cop[tikv] tikv_task:{proc max:4ms, min:0s, avg: 41.5µs, p80:0s, p95:0s, iters:10025, tasks:10025}, scan_detail: {total_process_keys: 100000, total_process_keys_size: 23877748, total_keys: 110025, get_snapshot_time: 66.1ms, rocksdb: {key_skipped_count: 109975, block: {cache_hit_count: 56992, read_count: 202, read_byte: 5.68 MB, read_time: 3.01ms}}} offset:0, count:10 N/A N/A
└─TableFullScan_12 100000.00 100000 cop[tikv] table:test_clustered_seq tikv_task:{proc max:4ms, min:0s, avg: 41.1µs, p80:0s, p95:0s, iters:10025, tasks:10025} keep order:false, desc N/A N/A
--------------------------------------------------------------------------------------------------------
explain analyze select * from test_clustered order by day limit 10;
TopN_7 10.00 10 root time:3.08s, loops:2 test.test_clustered.day, offset:0, count:10 2.37 KB N/A
└─TableReader_14 10.00 110000 root partition:all time:3.08s, loops:109, cop_task: {num: 11004, max: 16.7ms, min: 184.3µs, avg: 268.6µs, p95: 326.1µs, max_proc_keys: 10, p95_proc_keys: 10, rpc_num: 11004, rpc_time: 2.9s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1} data:Limit_13 3.95 KB N/A
└─Limit_13 10.00 110000 cop[tikv] tikv_task:{proc max:4ms, min:0s, avg: 28µs, p80:0s, p95:0s, iters:11004, tasks:11004}, scan_detail: {total_process_keys: 110000, total_process_keys_size: 26265513, total_keys: 121004, get_snapshot_time: 47.1ms, rocksdb: {key_skipped_count: 110000, block: {cache_hit_count: 36493, read_count: 53, read_byte: 1.48 MB, read_time: 889.2µs}}} offset:0, count:10 N/A N/A
└─TableFullScan_12 110000.00 110000 cop[tikv] table:test_clustered tikv_task:{proc max:4ms, min:0s, avg: 27.6µs, p80:0s, p95:0s, iters:11004, tasks:11004} keep order:false N/A N/A
整体上聚簇索引表查询较慢，同时从actRows来看，聚簇索引确实扫描了全部记录，而非直接从分区中取最大的几个，然后传递给上游算子来合并排序
那是否说明对聚簇索引而言，分区内是无序的？