UCP: Support Invisible Indexes

[shenli]

Description

MySQL supports invisible indexes; that is, indexes that are not used by the optimizer.

This is a useful feature when you want to drop an index in a safe way. Invisible indexes make it possible to test the effect of removing an index on query performance, without making a destructive change that must be undone should the index turn out to be required. Dropping and re-adding an index can be expensive for a large table, whereas making it invisible and visible are fast, in-place operations.

Support the option of VISIBLE | INVISIBLE.

CREATE [...] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]

index_option:
    {VISIBLE | INVISIBLE}

Also, consider the following:

• Display information about invisible indexes in the output of INFORMATION_SCHEMA.STATISTICS or SHOW INDEX.
• Index hints need to report errors when used in invisible indexes.
• The primary key cannot be set to an invisible index.

Score

• 1294

Mentor(s)

• @zimulala

Contact the mentors: #tidb-challenge-program channel in TiDB Community Slack Workspace

Recommended Skills

• DDL
• Golang

Learning Materials

MySQL's document for syntax reference.
TiDB DDL architecture

Time

GanttStart: 2020-07-10
GanttDue: 2020-08-15
GanttProgress: 100%

[morgo]

Note that MySQL 8.0 will likely support INVISIBLE columns in the future as well. We can tell this because functional indexes in 8.0 are based on virtual columns + invisible columns.

The semantics behind INVISIBLE columns are harder than invisible indexes, but assuming they are added to MySQL, we should plan on adding this feature too.

[shenli]

@morgo We will discuss it.
/cc @winkyao https://mariadb.com/kb/en/library/invisible-columns/ FYI

[SunRunAway]

Will this feature be appended into 4.0?

[Deardrops]

Let me implement this feature.

[zimulala]

This feature needs to be tested more systematically, reopen this issue to facilitate tracking.