shaoyu-li opened a new pull request, #12364:
URL: https://github.com/apache/gravitino/pull/12364
### What changes were proposed in this pull request?
Two changes to the MySQL DDL under `scripts/mysql/`:
1. Six index names that were reused across 15 tables (`idx_mid`,
`idx_cid`, `idx_sid`, `idx_rid`, `uk_sid_tn_del`, `uk_sid_fn_del`) are
each prefixed with their table name, e.g. `table_meta_idx_mid`.
32 index declarations are renamed in total.
2. `table_version_info`: `version` and `deleted_at` become `NOT NULL`,
and `uk_table_id_version_deleted_at` is promoted to
`PRIMARY KEY (table_id, version, deleted_at)`. No surrogate `id`
column is added.
16 files are touched: 10 `schema-*.sql` and 6 `upgrade-*.sql`. The
`table_version_info` change affects 5 of them.
This PR deliberately does not add a migration script. Only the
`CREATE TABLE` definitions change, so the new index names and the new
primary key apply to freshly created databases; an existing deployment
keeps its current index names and its primary-key-less
`table_version_info`. Renaming an index on a live table is metadata-only
from MySQL 5.7 onward, but turning the unique key of `table_version_info`
into a primary key is a table rebuild, so migrating existing deployments
deserves its own change with a proper lock and duration analysis rather
than being folded in here.
### Why are the changes needed?
MySQL scopes index names per table, so the reused names were valid, but
they are ambiguous: `idx_mid` indexes `metalake_id` on most tables yet
`metadata_object_id` on `tag_relation_meta` and `policy_relation_meta`.
An `EXPLAIN` or slow query log that mentions `idx_mid` therefore does not
identify the indexed column on its own. Unique names also let these
scripts be consumed by tooling that requires index names to be unique
across the whole schema.
`table_version_info` having no primary key means InnoDB clusters it on an
invisible generated key, so every lookup through the unique key costs a
secondary index traversal plus a lookup into the clustered index.
Promoting the existing unique key removes that without widening the row.
Fix: #(issue)
### Does this PR introduce _any_ user-facing change?
No API or configuration changes.
The DDL for new installations changes: index names differ, and
`table_version_info` gains a primary key with two columns becoming
`NOT NULL`. Existing databases are left untouched by this PR.
### How was this patch tested?
No new tests; this is a DDL-only change.
- Verified that no index or unique-key name is declared more than once
across any of the 16 modified files.
- Confirmed no code selects an index by name: there is no `FORCE INDEX`
or `USE INDEX` in the tree, and no mapper references an index name.
- Confirmed `TableVersionBaseSQLProvider` always supplies `version` and
`deleted_at` on insert, so marking them `NOT NULL` cannot break
existing writes.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
