感谢解答,生产环境的数据量是3亿条左右,具体情况在这个问题中,这张表在order by查询时非常慢,基本无法查询,查看执行计划是扫描了全表,我理解因为聚簇索引的特性,所以TablFullScan就表示IndexFullScan,所以出现扫描全表实际上就表示走了索引
但我不太理解的是:索引应该表示数据是有序的,所以可以看到在v4.0.11中的查询计划中从7各分区中各自取了最大的前五个,然后在TiDB中进行比较
你的日期是按日范围的那么在每个分区里的时间其实是无序的,索引索引的意义就没有
v6.5.0中却直接扫描了全部的记录,那这个是否表示聚簇索引在分区内不再有序了?我理解即便是按照WEEKDAY分区,分区内还是可以保持有序的,如果不在有序的话应该需要特别说明,但是我没有看到聚簇索引的文档中提到过这一点
TopN_24 5.00 root analytics.day:desc, offset:0, count:5
└─PartitionUnion_28 35.00 root
├─Projection_44 5.00 root analytics.day, analytics.partner_org_id, analytics.partner_name, analytics.customer_org_id, analytics.customer_name, analytics.offer_code, analytics.external_subscription_id, analytics.subscription_start_date, analytics.subscription_end_date, analytics.is_trial, analytics.is_auto_renew, analytics.available_licenses, analytics.assigned_licenses, analytics.actively_used_licenses, analytics.capacity, analytics.trial_duration, analytics.license_status, analytics.license_id, analytics.workload, analytics.daily_actively_used_licenses, analytics.weekly_actively_used_licenses, analytics.is_sa_display, analytics.decrypted_customer_name
│ └─IndexLookUp_43 5.00 root limit embedded(offset:0, count:5)
│ ├─Limit_42(Build) 5.00 cop[tikv] offset:0, count:5
│ │ └─IndexFullScan_40 5.00 cop[tikv] table:subscription_analytics, partition:p0, index:PRIMARY(day, partner_org_id, customer_org_id, offer_code, external_subscription_id, license_id) keep order:true, desc
│ └─TableRowIDScan_41(Probe) 5.00 cop[tikv] table:subscription_analytics, partition:p0 keep order:false, stats:pseudo
├─Projection_64 5.00 root analytics.day, analytics.partner_org_id, analytics.partner_name, analytics.customer_org_id, analytics.customer_name, analytics.offer_code, analytics.external_subscription_id, analytics.subscription_start_date, analytics.subscription_end_date, analytics.is_trial, analytics.is_auto_renew, analytics.available_licenses, analytics.assigned_licenses, analytics.actively_used_licenses, analytics.capacity, analytics.trial_duration, analytics.license_status, analytics.license_id, analytics.workload, analytics.daily_actively_used_licenses, analytics.weekly_actively_used_licenses, analytics.is_sa_display, analytics.decrypted_customer_name
│ └─IndexLookUp_63 5.00 root limit embedded(offset:0, count:5)
│ ├─Limit_62(Build) 5.00 cop[tikv] offset:0, count:5
│ │ └─IndexFullScan_60 5.00 cop[tikv] table:subscription_analytics, partition:p1, index:PRIMARY(day, partner_org_id, customer_org_id, offer_code, external_subscription_id, license_id) keep order:true, desc
│ └─TableRowIDScan_61(Probe) 5.00 cop[tikv] table:subscription_analytics, partition:p1 keep order:false, stats:pseudo
├─Projection_84 5.00 root analytics.day, analytics.partner_org_id, analytics.partner_name, analytics.customer_org_id, analytics.customer_name, analytics.offer_code, analytics.external_subscription_id, analytics.subscription_start_date, analytics.subscription_end_date, analytics.is_trial, analytics.is_auto_renew, analytics.available_licenses, analytics.assigned_licenses, analytics.actively_used_licenses, analytics.capacity, analytics.trial_duration, analytics.license_status, analytics.license_id, analytics.workload, analytics.daily_actively_used_licenses, analytics.weekly_actively_used_licenses, analytics.is_sa_display, analytics.decrypted_customer_name
│ └─IndexLookUp_83 5.00 root limit embedded(offset:0, count:5)
│ ├─Limit_82(Build) 5.00 cop[tikv] offset:0, count:5
│ │ └─IndexFullScan_80 5.00 cop[tikv] table:subscription_analytics, partition:p2, index:PRIMARY(day, partner_org_id, customer_org_id, offer_code, external_subscription_id, license_id) keep order:true, desc
│ └─TableRowIDScan_81(Probe) 5.00 cop[tikv] table:subscription_analytics, partition:p2 keep order:false, stats:pseudo
├─Projection_104 5.00 root analytics.day, analytics.partner_org_id, analytics.partner_name, analytics.customer_org_id, analytics.customer_name, analytics.offer_code, analytics.external_subscription_id, analytics.subscription_start_date, analytics.subscription_end_date, analytics.is_trial, analytics.is_auto_renew, analytics.available_licenses, analytics.assigned_licenses, analytics.actively_used_licenses, analytics.capacity, analytics.trial_duration, analytics.license_status, analytics.license_id, analytics.workload, analytics.daily_actively_used_licenses, analytics.weekly_actively_used_licenses, analytics.is_sa_display, analytics.decrypted_customer_name
│ └─IndexLookUp_103 5.00 root limit embedded(offset:0, count:5)
│ ├─Limit_102(Build) 5.00 cop[tikv] offset:0, count:5
│ │ └─IndexFullScan_100 5.00 cop[tikv] table:subscription_analytics, partition:p3, index:PRIMARY(day, partner_org_id, customer_org_id, offer_code, external_subscription_id, license_id) keep order:true, desc
│ └─TableRowIDScan_101(Probe) 5.00 cop[tikv] table:subscription_analytics, partition:p3 keep order:false, stats:pseudo
├─Projection_124 5.00 root analytics.day, analytics.partner_org_id, analytics.partner_name, analytics.customer_org_id, analytics.customer_name, analytics.offer_code, analytics.external_subscription_id, analytics.subscription_start_date, analytics.subscription_end_date, analytics.is_trial, analytics.is_auto_renew, analytics.available_licenses, analytics.assigned_licenses, analytics.actively_used_licenses, analytics.capacity, analytics.trial_duration, analytics.license_status, analytics.license_id, analytics.workload, analytics.daily_actively_used_licenses, analytics.weekly_actively_used_licenses, analytics.is_sa_display, analytics.decrypted_customer_name
│ └─IndexLookUp_123 5.00 root limit embedded(offset:0, count:5)
│ ├─Limit_122(Build) 5.00 cop[tikv] offset:0, count:5
│ │ └─IndexFullScan_120 5.00 cop[tikv] table:subscription_analytics, partition:p4, index:PRIMARY(day, partner_org_id, customer_org_id, offer_code, external_subscription_id, license_id) keep order:true, desc
│ └─TableRowIDScan_121(Probe) 5.00 cop[tikv] table:subscription_analytics, partition:p4 keep order:false, stats:pseudo
├─Projection_144 5.00 root analytics.day, analytics.partner_org_id, analytics.partner_name, analytics.customer_org_id, analytics.customer_name, analytics.offer_code, analytics.external_subscription_id, analytics.subscription_start_date, analytics.subscription_end_date, analytics.is_trial, analytics.is_auto_renew, analytics.available_licenses, analytics.assigned_licenses, analytics.actively_used_licenses, analytics.capacity, analytics.trial_duration, analytics.license_status, analytics.license_id, analytics.workload, analytics.daily_actively_used_licenses, analytics.weekly_actively_used_licenses, analytics.is_sa_display, analytics.decrypted_customer_name
│ └─IndexLookUp_143 5.00 root limit embedded(offset:0, count:5)
│ ├─Limit_142(Build) 5.00 cop[tikv] offset:0, count:5
│ │ └─IndexFullScan_140 5.00 cop[tikv] table:subscription_analytics, partition:p5, index:PRIMARY(day, partner_org_id, customer_org_id, offer_code, external_subscription_id, license_id) keep order:true, desc
│ └─TableRowIDScan_141(Probe) 5.00 cop[tikv] table:subscription_analytics, partition:p5 keep order:false, stats:pseudo
└─Projection_164 5.00 root analytics.day, analytics.partner_org_id, analytics.partner_name, analytics.customer_org_id, analytics.customer_name, analytics.offer_code, analytics.external_subscription_id, analytics.subscription_start_date, analytics.subscription_end_date, analytics.is_trial, analytics.is_auto_renew, analytics.available_licenses, analytics.assigned_licenses, analytics.actively_used_licenses, analytics.capacity, analytics.trial_duration, analytics.license_status, analytics.license_id, analytics.workload, analytics.daily_actively_used_licenses, analytics.weekly_actively_used_licenses, analytics.is_sa_display, analytics.decrypted_customer_name
└─IndexLookUp_163 5.00 root limit embedded(offset:0, count:5)
├─Limit_162(Build) 5.00 cop[tikv] offset:0, count:5
│ └─IndexFullScan_160 5.00 cop[tikv] table:subscription_analytics, partition:p6, index:PRIMARY(day, partner_org_id, customer_org_id, offer_code, external_subscription_id, license_id) keep order:true, desc
└─TableRowIDScan_161(Probe) 5.00 cop[tikv] table:subscription_analytics, partition:p6 keep order:false, stats:pseudo