[Optimize]: Query performance optimization

[19-hanhan]

Current Issues

In the [MVCC Refactor] - fd33bffe2773f5f14dae209ed575adb15c868942 commit, the KV operation logic for five types—db, table, index, column, and segment—was modified.

Before MVCC Refactor

the deletion logic was implemented such that: if a table was deleted, the KVInstance would remove the corresponding meta info.
This could lead to a bug where:

• if the database restarted, the meta info in Rocksdb might be inconsistent with the meta info from the last checkpoint (some meta info might have been deleted during Replay operations).
• This inconsistency could cause errors during the Replay process. For example, if an Insert command existed before a Drop command, the current KV might not contain all the meta info required by the Insert command, resulting in an error.

After MVCC Refactor

After the MVCC Refactor, the KVInstance does not remove its corresponding meta info when a table is deleted.

• Instead, it adds a meta info item prefixed with "drop" to indicate that the meta info has been deleted, along with recording the timestamp (TS) when the drop occurred.

This will result in a performance penalty:

• To avoid inconsistencies between Rocksdb and checkpoint data after a restart, every database operation that accesses Meta info must perform an additional query (checking whether a corresponding drop_ts exists in the KVInstance) if the queried content exists in the KVInstance.
• Taking a single query as an example, a single Get operation in the KVInstance takes about 11ms. For operations involving db, table, index, column, and segment, there will be 6 additional Get drop_ts operations, leading to an extra 66ms performance overhead.

Query Optimization Suggestions

Analysis of the root cause:

1. The Before MVCC Refactor strategy was stable for normal operational workflows.
2. The After MVCC Refactor strategy ensures that RocksDB can still query dropped meta info during replay and determine when it was dropped.
3. From point 2, it follows that only the replay process requires checking drop_ts, while normal operations do not need this verification.

Proposed Solution:

1. Modify the current KVInstance storage strategy by combining the pre- and post-MVCC Refactor approaches (ensuring the dropped meta remains queryable in KV):
   • When dropping meta info, prefix the original meta key with "drop" to indicate deletion while preserving a new meta entry containing the original meta info and its drop_ts.
2. Update the verification logic for db, table, index, column, and segment operations (ensuring correctness during replay):
   • First, check if the meta_key exists in KV.
   • If it does not exist, then search for its drop_meta_key in KV.
3. Reimplement a separate set of interfaces (GetDBMeta, GetTableMeta, GetSegmentIDs, GetColumnDefs) specifically for WalReplay by solution 1 & 2, while keeping the original interfaces unchanged (optimizing performance for non-replay operations by avoiding unnecessary drop_ts checks).