Global Index for partitioned tables

[mjonss]

Enhancement

Development tasks for:
Make GLOBAL INDEX General Available

Stage 1 - full implementation, but Experimental

• Move enable global index from configuration to experimental feature flag, tidb_enable_global_index system variable #48825
• Add tests for all index access plans. Create issues for handling the index access plans currently not supporting Global indexes (first disable the specific plan, then add support).
  • memIndexReader and IndexReader *: fix global index work with memIndexReader executor #52660
  • memIndexLookUp and IndexLookUp
  • memIndexMerge and IndexMerge *: global index support index_merge and mem_index_merge #52971
  • pointGet and batchPointGet
• Tests
  • Add tests for all write transactions (select lock, commit, rollback, different transaction modes, different transaction levels) * (insert, update, delete, select for update, single/multi statement transaction).
• DDL
  • [ ] Support in EXCHANGE PARTITION? Support EXCHANGE PARTITION with GLOBAL INDEX #54056 Will not support EXCHANGE PARTITION for Global Index, will cause too long state where double writing is needed. Better to add support for CONVERT PARTITION/TABLE instead, possibly later.
  • Support in REORGANIZE PARTITION ddl: Reorganize partition supporting global index #53277
  • Support in TRUNCATE PARTITION *: Truncate partition with global index improvement #55831
  • Support in DROP PARTITION ? + *: Drop partition DDL handling for overlapping partitions during State Changes | tidb-test=pr/2402 #56082
• Admin
  • Support admin [recover | cleanup] index with global index #53439
  • Global index add support for check index/check table #53019
• Miscs
  • Add one more column in information_schema.indexes table to indicate it's a global index or not. #53342
  • Add "Global" to SHOW INDEXES output #55452 executor: Add Global to SHOW INDEX output | tidb-test=pr/2391 #56028

Stage 2 - Stabilise / General Available

• Add docs partition: add doc for global index, also with GLOBAL IndexOption/ColumnOption docs#18543
• Fix reported bugs
• Adjust syntax if needed parser: support GLOBAL IndexOption #55259
• Add more tests if needed
  • br: fix br work with global index #57493
  • Add global index related test cases for lightning and dumpling #57623
  • Added global index to partition_table test tiflow#11651
• Enable by default and remove experimental flag *: Deprecated and always set to ON tidb_enable_global_index | tidb-test=pr/2396 #56169

Optional / future enhancements

• Change syntax, to enable (default) LOCAL index, and (explicit) GLOBAL index, Add GLOBAL as IndexOption and require it explicitly for GLOBAL INDEX #52994
• Consider path is global index path or not in skylinePruning function, let user easier to select global index. planner: consider path is global index path or not in skylinePruning function #56196

[Defined2014]

Maybe support for indexMerge and UnionScan can be added, it seems we are not considering global indexes in those related codes.

Also we need to consider the support of the planner part. Like different cost between global index and local index in some executors.

[311ybb]

please make global index General Available faster, we need it !

[vonwind]

This is an urgently needed feature.

[mjonss]

ADD PARTITION is already supported, it uses the same logic as for TRUNCATE PARTITION, i.e. reading from the Global Index will filter away dropped/added partitions, so it will only show index entries from partitions the transaction/schema version sees. Currently filtered here.

[mjonss]

@Defined2014, for the global index, should there be some way to specify placement rules? I don't think there are any for normal indexes?

[Defined2014]

@Defined2014, for the global index, should there be some way to specify placement rules? I don't think there are any for normal indexes?

I think it will follow table's placement rules and it is in line with my expectations. What do you think @mjonss ? And we could add some test cases for this.

[mjonss]

I think it will follow table's placement rules and it is in line with my expectations. What do you think @mjonss ? And we could add some test cases for this.

@Defined2014 I expect the same, i.e. it will follow the table level's placement rules, so we should probably document it and add a test for it.

[Defined2014]

I think it will follow table's placement rules and it is in line with my expectations. What do you think @mjonss ? And we could add some test cases for this.

@Defined2014 I expect the same, i.e. it will follow the table level's placement rules, so we should probably document it and add a test for it.

I think the exists test cases already covered this situation(by check the key ranges for pd.Rule). And push a doc commit for it.

[mjonss]

Closing as done, Stage 2 completed. GA in v8.4 and included in LTS release v8.5.
Any future enhancements or issues should be using new Github issues.