Recording the index usage information and more

[winoros]

Feature Request

Is your feature request related to a problem? Please describe:

In SQL Server, there's a view sys.dm_db_index_usage_stats.
In Oracle, there's views V$SEGMENT_STATISTICS and V$OBJECT_USAGE.
These views can capture the usage of index such as how many times this index is used, the last time that this index is used.

Describe the feature you'd like:

Disign a view like them to collect some information help DBA make decisions.

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[winoros]

When you pick up this issue. You should

• Write a design doc to show how you want to implement this feature.
• After the design doc is granted by reviewers, you can begin to implement it.

If you're interested in this but have no clear idea to do this. You can join our community slack workspace to get some help.

[wwar]

In MySQL this is in sys as well: sys.schema_unused_indexes. The underlying views are in performance schema.

[qw4990]

@rebelice PTAL.

[zz-jason]

duplicated with #17508

[rebelice]

/assign

[ghost]

As well as sys.schema_unused_indexes, the forks of MySQL also have this feature in index_statistics / user_statistics etc: https://www.percona.com/doc/percona-server/LATEST/diagnostics/user_stats.html

The advantage of copying one of these two designs, there are tools like percona-toolkit that understand how to read this metadata.