Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 索引失效
|
username: EricSong
[TiDB Usage Environment] Production Environment
[TiDB Version]
Old Cluster: v4.0.11
New Cluster: v6.5.0
[Encountered Problem: Phenomenon and Impact]
There is a table with nearly 300 million rows of data. The table structure is as follows. Now this table has been migrated from the old cluster to the new cluster using Dumpling + Lightning.
CREATE TABLE `analytics` (
`day` date NOT NULL,
`partner_org_id` varchar(60) NOT NULL,
`partner_name` text DEFAULT NULL,
`customer_org_id` varchar(60) NOT NULL,
`customer_name` text DEFAULT NULL,
`offer_code` varchar(40) NOT NULL,
`external_id` varchar(60) NOT NULL,
`license_id` varchar(255) NOT NULL,
`workload` varchar(60) DEFAULT NULL
...(omitted some fields)
PRIMARY KEY (`day`,`partner_org_id`,`customer_org_id`,`offer_code`,`external_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));
EXPLAIN select * from analytics order by day desc limit 5;
Execution plan in the new cluster is as follows
TopN_7 5.00 root analytics.day:desc, offset:0, count:5
└─TableReader_14 5.00 root partition:all data:Limit_13
└─Limit_13 5.00 cop[tikv] offset:0, count:5
└─TableFullScan_12 276107194.00 cop[tikv] table:analytics keep order:false, desc
Execution plan in the old cluster is as follows
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
It seems that the index is invalid in the new cluster, and it remains so even after multiple Analyze operations. Has anyone encountered a similar situation? What could be the possible reasons? Thanks!
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 