Support global index for partitioned tables

[scsldb]

Description

Support global index for partitioned tables

Category

Feature, Performance

Motivation

TiDB claimed to be MySQL compatible, and it also inherit some of MySQL's constraints.
MySQL does support global index for partition table.

A PRIMARY / UNIQUE INDEX must include all of the table's partitioning columns

This becomes a obstacle and hinder the non-mysql user (such as Oracle) from migrating to TiDB.

Support global index for partitioned table could remove the constraint, and make it easier for user's migration.

Value

Global Index provides a better performance for partitioned tables in some scenarios

Work Estimates

At least 60~90 full man-day for development. Not taking testing into consideration.

This feature involves quite a lot of changes, including DDL, DML and other modules.

DDL:

• create table with global index
• add global index
• drop index
• add partition
• drop partition
• add table
• drop table

DML:

• planner module should consider the global index
• statistics update
• table module should be aware of the global index

Auxiliary:

• HTTP API
• admin check table
  ...

Proposal Doc

https://github.com/pingcap/tidb/blob/master/docs/design/2020-08-04-global-index.md

[tiancaiamao]

Link #15003

[tiancaiamao]

@zhangjinpeng1987