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

[mjonss]

MySQL does not support global indexes in partitioning, where a secondary index is not alined/partitioned as the base table, it only supports 'local indexes' where each partition contains a set of indexes, including the primary and unique indexes. As of MySQL 8.0.24, it is still not possible to create a global partitioned index, where the index is partitioned differently than the defined table partitioning.

But if one sees the Primary Key index as a separate index (in InnoDB the PK is the clustered index, containing the row data in the leaf nodes) then one could say it is a Global Index, but I find this very confusion term and we should say that such partitioning is still a local index.

Supporting GLOBAL INDEX (where the index is not aligned with the table partitioning) would be incompatible with MySQL.

LOCAL INDEXes has the benefit that partition management is easy, since everything related to the data in one partition is aligned/grouped together, so any index part will only point to the same partition as the index is included in.

GLOBAL INDEXes would mean that partition management needs to reorganise data when a partition is altered, since the index parts are not aligned with the table partitions, but can point to different partitions, so for example dropping a partition would need update the index with all rows that belonged to that dropped partition, instead of just dropping that index partition.

[mjonss]

This feature is not yet supported (can be tested with adding enable-global-index = true in the config file).

Some related PRs: #18402, #19821, pingcap/parser#917, tikv/tikv#8603

[mjonss]

/component tablepartition

[Defined2014]

ref

tidb/executor/distsql.go

Line 645 in e341791

// TODO: fix global index related bugs later

, seems admin check table with global index has some problems. Record it here.

[Defined2014]

Use another issue to track it, close this one.