我在7.1.2测试 limit 下推是生效的而且Keeporder: true 。你那个新集群上收集过统计信息吗？
我这边在查询前对四张表都做了analyze table,统计信息应该没有问题的
我又进行了一次测试，主要纬度是
- 主键是否聚簇索引
- 是否分区
- 分区采用周维度还是月维度
建表语句如下
-- 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;
-- test.test_clustered_seq_part definition
CREATE TABLE `test_clustered_seq_part` (
`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_clustered_weekday_part definition
CREATE TABLE `test_clustered_weekday_part` (
`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_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;
-- test.test_nonclustered_seq_part definition
CREATE TABLE `test_nonclustered_seq_part` (
`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));
-- test.test_nonclustered_weekday_part definition
CREATE TABLE `test_nonclustered_weekday_part` (
`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));
针对上述6张表有如下查询结果
explain analyze select * from test_clustered order by day limit 10;
Limit_10 10.00 10 root time:603.3µs, loops:2 offset:0, count:10 N/A N/A
└─TableReader_17 10.00 10 root time:601.5µs, loops:1, cop_task: {num: 1, max: 528.3µs, proc_keys: 10, rpc_num: 1, rpc_time: 509.2µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1} data:Limit_16 2.04 KB N/A
└─Limit_16 10.00 10 cop[tikv] tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 10, total_process_keys_size: 2386, total_keys: 11, get_snapshot_time: 22.9µs, rocksdb: {key_skipped_count: 10, block: {cache_hit_count: 2}}} offset:0, count:10 N/A N/A
└─TableFullScan_15 10.00 10 cop[tikv] table:test_clustered tikv_task:{time:0s, loops:1} keep order:true N/A N/A
--------------------------------------------------------------------------------
explain analyze select * from test_clustered_seq_part order by day limit 10;
TopN_7 10.00 10 root time:2.85s, loops:2 test.test_clustered_seq_part.day, offset:0, count:10 2.37 KB N/A
└─TableReader_14 10.00 100000 root partition:all time:2.85s, loops:100, cop_task: {num: 10029, max: 1.05ms, min: 186.2µs, avg: 273µs, p95: 328µs, max_proc_keys: 10, p95_proc_keys: 10, rpc_num: 10029, rpc_time: 2.69s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1} data:Limit_13 3.95 KB N/A
└─Limit_13 10.00 100000 cop[tikv] tikv_task:{proc max:4ms, min:0s, avg: 41.9µs, p80:0s, p95:0s, iters:10029, tasks:10029}, scan_detail: {total_process_keys: 100000, total_process_keys_size: 23877833, total_keys: 110029, get_snapshot_time: 58.9ms, rocksdb: {key_skipped_count: 100000, block: {cache_hit_count: 20417}}} offset:0, count:10 N/A N/A
└─TableFullScan_12 100585.00 100000 cop[tikv] table:test_clustered_seq_part tikv_task:{proc max:4ms, min:0s, avg: 40.7µs, p80:0s, p95:0s, iters:10029, tasks:10029} keep order:false N/A N/A
--------------------------------------------------------------------------------
explain analyze select * from test_clustered_weekday_part order by day limit 10;
TopN_7 10.00 10 root time:2.89s, loops:2 test.test_clustered_weekday_part.day, offset:0, count:10 2.37 KB N/A
└─TableReader_14 10.00 100000 root partition:all time:2.89s, loops:100, cop_task: {num: 10004, max: 12.3ms, min: 172.1µs, avg: 277.5µs, p95: 325.3µs, max_proc_keys: 10, p95_proc_keys: 10, rpc_num: 10004, rpc_time: 2.73s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1} data:Limit_13 3.95 KB N/A
└─Limit_13 10.00 100000 cop[tikv] tikv_task:{proc max:4ms, min:0s, avg: 34.8µs, p80:0s, p95:0s, iters:10004, tasks:10004}, scan_detail: {total_process_keys: 100000, total_process_keys_size: 23877833, total_keys: 110004, get_snapshot_time: 38ms, rocksdb: {key_skipped_count: 100000, block: {cache_hit_count: 20373}}} offset:0, count:10 N/A N/A
└─TableFullScan_12 100000.00 100000 cop[tikv] table:test_clustered_weekday_part tikv_task:{proc max:4ms, min:0s, avg: 34µs, p80:0s, p95:0s, iters:10004, tasks:10004} keep order:false N/A N/A
--------------------------------------------------------------------------------
explain analyze select * from test_nonclustered order by day limit 10;
Limit_11 10.00 10 root time:1.24ms, loops:2 offset:0, count:10 N/A N/A
└─Projection_19 10.00 10 root time:1.24ms, loops:1, Concurrency:OFF test.test_nonclustered.day, test.test_nonclustered.partner_org_id, test.test_nonclustered.partner_name, test.test_nonclustered.customer_org_id, test.test_nonclustered.license_id 87.2 KB N/A
└─IndexLookUp_18 10.00 10 root time:1.24ms, loops:1, index_task: {total_time: 1.18ms, fetch_handle: 1.17ms, build: 8.37µs, wait: 2.35µs}, table_task: {total_time: 567.8µs, num: 1, concurrency: 5} 22.9 KB N/A
├─Limit_17(Build) 10.00 20 cop[tikv] time:1.16ms, loops:4, cop_task: {num: 2, max: 468.5µs, min: 434.5µs, avg: 451.5µs, p95: 468.5µs, max_proc_keys: 10, p95_proc_keys: 10, rpc_num: 2, rpc_time: 868.6µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:2, tasks:2}, scan_detail: {total_process_keys: 20, total_process_keys_size: 3292, total_keys: 22, get_snapshot_time: 26.2µs, rocksdb: {key_skipped_count: 20, block: {cache_hit_count: 5}}} offset:0, count:10 N/A N/A
│ └─IndexFullScan_15 10.00 20 cop[tikv] table:test_nonclustered, index:PRIMARY(day, partner_org_id, customer_org_id, license_id) tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:2, tasks:2} keep order:true N/A N/A
└─TableRowIDScan_16(Probe) 10.00 10 cop[tikv] table:test_nonclustered time:449.2µs, loops:2, cop_task: {num: 1, max: 393.6µs, proc_keys: 10, rpc_num: 1, rpc_time: 369.1µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 10, total_process_keys_size: 1866, total_keys: 10, get_snapshot_time: 4.25µs, rocksdb: {block: {cache_hit_count: 21}}} keep order:false N/A N/A
--------------------------------------------------------------------------------
explain analyze select * from test_nonclustered_seq_part order by day limit 10;
TopN_7 10.00 10 root time:23.8ms, loops:2 test.test_nonclustered_seq_part.day, offset:0, count:10 9.48 KB N/A
└─TableReader_14 10.00 480 root partition:all time:23.8ms, loops:3, cop_task: {num: 49, max: 8.76ms, min: 2.92ms, avg: 5.5ms, p95: 8.38ms, max_proc_keys: 2277, p95_proc_keys: 2198, tot_proc: 133ms, tot_wait: 74ms, rpc_num: 50, rpc_time: 269.5ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, backoff{regionMiss: 2ms} data:TopN_13 3.82 KB N/A
└─TopN_13 10.00 480 cop[tikv] tikv_task:{proc max:8ms, min:0s, avg: 3.18ms, p80:4ms, p95:8ms, iters:130, tasks:49}, scan_detail: {total_process_keys: 100000, total_process_keys_size: 18677833, total_keys: 100049, get_snapshot_time: 62.5ms, rocksdb: {key_skipped_count: 100000, block: {cache_hit_count: 102, read_count: 301, read_byte: 8.39 MB, read_time: 2.83ms}}} test.test_nonclustered_seq_part.day, offset:0, count:10 N/A N/A
└─TableFullScan_12 100000.00 100000 cop[tikv] table:test_nonclustered_seq_part tikv_task:{proc max:8ms, min:0s, avg: 3.1ms, p80:4ms, p95:8ms, iters:130, tasks:49} keep order:false N/A N/A
--------------------------------------------------------------------------------
explain analyze select * from test_nonclustered_weekday_part order by day limit 10;
TopN_7 10.00 10 root time:17.2ms, loops:2 test.test_nonclustered_weekday_part.day, offset:0, count:10 2.37 KB N/A
└─TableReader_14 10.00 70 root partition:all time:17.2ms, loops:3, cop_task: {num: 7, max: 17ms, min: 16.1ms, avg: 16.5ms, p95: 17ms, max_proc_keys: 14376, p95_proc_keys: 14376, tot_proc: 106ms, rpc_num: 7, rpc_time: 115.4ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} data:TopN_13 3.82 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:99, tasks:7}, scan_detail: {total_process_keys: 100000, total_process_keys_size: 18677833, total_keys: 100007, get_snapshot_time: 2.89ms, rocksdb: {key_skipped_count: 100000, block: {cache_hit_count: 17, read_count: 305, read_byte: 8.84 MB, read_time: 2.67ms}}} test.test_nonclustered_weekday_part.day, offset:0, count:10 N/A N/A
└─TableFullScan_12 100000.00 100000 cop[tikv] table:test_nonclustered_weekday_part tikv_task:{proc max:16ms, min:12ms, avg: 14.9ms, p80:16ms, p95:16ms, iters:99, tasks:7} keep order:false N/A N/A
所以我理解聚簇索引效率的下降主要是因为存在分区导致的，当聚簇索引不存在分区时查询很快
通过执行计划看起来聚簇索引的分区内是无序的，需要扫描分区内所有的记录来获取查询结果，而不是像非聚簇索引一样，只需要使用TopN算子来获取分区内最大的几个记录转交即可
而对于是否因为没有使用分区键导致查询效率下降，我使用了如下查询验证,即便在顺序分区中使用了day来查询，仍然是扫描了全部的记录
explain analyze select * from test_clustered_seq_part where day >= "2020-02-01" order by day limit 10;
TopN_8 10.00 10 root time:2.83s, loops:2 test.test_clustered_seq_part.day, offset:0, count:10 2.37 KB N/A
└─TableReader_15 10.00 97945 root partition:p202002,p202003,p202004,p202005,p202006,p202007,p202008,p202009,p202010,p202011,p202012,p202101,p202102,p202103,p202104,p202105,p202106,p202107,p202108,p202109,p202110,p202111,p202112,p202201,p202202,p202203,p202204,p202205,p202206,p202207,p202208,p202209,p202210,p202211,p202212,p202301,p202302,p202303,p202304,p202305,p202306,p202307,p202308,p202309,p202310,p202311,p202312 time:2.83s, loops:98, cop_task: {num: 9822, max: 1.24ms, min: 188.7µs, avg: 276.2µs, p95: 330.6µs, max_proc_keys: 10, p95_proc_keys: 10, rpc_num: 9822, rpc_time: 2.66s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1} data:Limit_14 3.95 KB N/A
└─Limit_14 10.00 97945 cop[tikv] tikv_task:{proc max:4ms, min:0s, avg: 30.5µs, p80:0s, p95:0s, iters:9822, tasks:9822}, scan_detail: {total_process_keys: 97945, total_process_keys_size: 23387123, total_keys: 107767, get_snapshot_time: 58.8ms, rocksdb: {key_skipped_count: 97945, block: {cache_hit_count: 19997}}} offset:0, count:10 N/A N/A
└─TableRangeScan_13 100585.00 97945 cop[tikv] table:test_clustered_seq_part tikv_task:{proc max:4ms, min:0s, avg: 30.5µs, p80:0s, p95:0s, iters:9822, tasks:9822} range:[2020-02-01,+inf], keep order:false N/A N/A
关于添加分区键查询后的测试结果
explain analyze select * from test_clustered_seq_part where TO_DAYS(day) > 737790 order by day limit 10;
TopN_8 10.00 10 root time:27.4ms, loops:2 test.test_clustered_seq_part.day, offset:0, count:10 58.7 KB N/A
└─Selection_15 80468.00 99940 root time:24.8ms, loops:100 gt(to_days(test.test_clustered_seq_part.day), 737790) 221.1 KB N/A
└─TableReader_14 100585.00 100000 root partition:all time:12.1ms, loops:100, cop_task: {num: 193, max: 3.52ms, min: 484.2µs, avg: 1.35ms, p95: 2.62ms, max_proc_keys: 992, p95_proc_keys: 992, tot_proc: 22ms, rpc_num: 193, rpc_time: 258.8ms, copr_cache_hit_ratio: 0.02, distsql_concurrency: 15} data:TableFullScan_13 926.0 KB N/A
└─TableFullScan_13 100585.00 100000 cop[tikv] table:test_clustered_seq_part tikv_task:{proc max:4ms, min:0s, avg: 476.7µs, p80:0s, p95:4ms, iters:776, tasks:193}, scan_detail: {total_process_keys: 97024, total_process_keys_size: 23167260, total_keys: 97214, get_snapshot_time: 1.27ms, rocksdb: {key_skipped_count: 97024, block: {cache_hit_count: 995}}} keep order:false N/A N/A
---------------------------------------------------------------------------
explain analyze select * from test_clustered_seq_part order by day limit 10;
TopN_7 10.00 10 root time:3.05s, loops:2 test.test_clustered_seq_part.day, offset:0, count:10 2.37 KB N/A
└─TableReader_14 10.00 100000 root partition:all time:3.05s, loops:100, cop_task: {num: 10029, max: 903.3µs, min: 217.6µs, avg: 282.1µs, p95: 370.5µs, max_proc_keys: 10, p95_proc_keys: 10, rpc_num: 10030, rpc_time: 2.78s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1} data:Limit_13 3.96 KB N/A
└─Limit_13 10.00 100000 cop[tikv] tikv_task:{proc max:4ms, min:0s, avg: 28.3µs, p80:0s, p95:0s, iters:10029, tasks:10029}, scan_detail: {total_process_keys: 100000, total_process_keys_size: 23877833, total_keys: 110029, get_snapshot_time: 37.8ms, rocksdb: {key_skipped_count: 100000, block: {cache_hit_count: 20109, read_count: 314, read_byte: 8.84 MB, read_time: 3.78ms}}} offset:0, count:10 N/A N/A
└─TableFullScan_12 100585.00 100000 cop[tikv] table:test_clustered_seq_part tikv_task:{proc max:4ms, min:0s, avg: 28.3µs, p80:0s, p95:0s, iters:10029, tasks:10029} keep order:false N/A N/A
---------------------------------------------------------------------------
explain analyze select * from test_nonclustered_seq_part where TO_DAYS(day) > 737790 order by day limit 10;
TopN_8 10.00 10 root time:111.8ms, loops:2 test.test_nonclustered_seq_part.day, offset:0, count:10 11.2 KB N/A
└─Selection_15 80000.00 99940 root time:109ms, loops:100 gt(to_days(test.test_nonclustered_seq_part.day), 737790) 218.3 KB N/A
└─TableReader_14 100000.00 100000 root partition:all time:94ms, loops:100, cop_task: {num: 193, max: 82.9ms, min: 677.7µs, avg: 8.27ms, p95: 80.6ms, max_proc_keys: 992, p95_proc_keys: 992, tot_proc: 21ms, tot_wait: 92ms, rpc_num: 193, rpc_time: 1.59s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} data:TableFullScan_13 578.7 KB N/A
└─TableFullScan_13 100000.00 100000 cop[tikv] table:test_nonclustered_seq_part tikv_task:{proc max:4ms, min:0s, avg: 476.7µs, p80:0s, p95:4ms, iters:776, tasks:193}, scan_detail: {total_process_keys: 100000, total_process_keys_size: 18677833, total_keys: 100193, get_snapshot_time: 15.8ms, rocksdb: {key_skipped_count: 100000, block: {cache_hit_count: 693}}} keep order:false N/A N/A
---------------------------------------------------------------------------
explain analyze select * from test_nonclustered_seq_part order by day limit 10;
TopN_7 10.00 10 root time:63.6ms, loops:2 test.test_nonclustered_seq_part.day, offset:0, count:10 2.37 KB N/A
└─TableReader_14 10.00 480 root partition:all time:63.6ms, loops:3, cop_task: {num: 49, max: 41.8ms, min: 463.9µs, avg: 11.7ms, p95: 35.1ms, max_proc_keys: 2277, p95_proc_keys: 2191, tot_proc: 340ms, tot_wait: 171ms, rpc_num: 64, rpc_time: 581.4ms, copr_cache_hit_ratio: 0.02, distsql_concurrency: 15} data:TopN_13 3.82 KB N/A
└─TopN_13 10.00 480 cop[tikv] tikv_task:{proc max:36ms, min:0s, avg: 7.35ms, p80:8ms, p95:32ms, iters:130, tasks:49}, scan_detail: {total_process_keys: 97785, total_process_keys_size: 18264142, total_keys: 97833, get_snapshot_time: 302.3µs, rocksdb: {key_skipped_count: 97785, block: {cache_hit_count: 78, read_count: 317, read_byte: 8.49 MB, read_time: 2.55ms}}} test.test_nonclustered_seq_part.day, offset:0, count:10 N/A N/A
└─TableFullScan_12 100000.00 100000 cop[tikv] table:test_nonclustered_seq_part tikv_task:{proc max:36ms, min:0s, avg: 7.18ms, p80:8ms, p95:32ms, iters:130, tasks:49} keep order:false N/A N/A
统计信息可以试着更新一下