From Ad-Hoc Indexing to Verifiable Governance: MySQL Index Governance at Scale

1. Promo Incident: The Problem Was Not a Single Slow SQL but an Uncontrolled Index History

The orders table holds ~120M rows, growing ~3M daily. During promotions, order creation and status updates exceed 10k writes/sec; user order-list P99 degraded from 180 ms to 2.3 s.

SELECT id, order_no, status, pay_amount, created_at
FROM orders
WHERE tenant_id = ?
  AND user_id = ?
  AND status = ?
  AND created_at >= ? AND created_at < ?
ORDER BY created_at DESC, id DESC
LIMIT 20;

Legacy indexes:

KEY idx_user           (user_id),
KEY idx_status         (status),
KEY idx_created_at     (created_at),
KEY idx_user_status    (user_id, status),
KEY idx_status_created (status, created_at),
KEY idx_tenant_created (tenant_id, created_at)

No index fully matches "tenant + user + status + time range + stable sort". The obvious candidate:

KEY idx_tenant_user_status_time
    (tenant_id, user_id, status, created_at, id)

But this remains a hypothesis: the new index would maintain more secondary pages, generate redo, consume buffer pool, and might cause other queries to pick a worse plan. If the API uses OFFSET 100000, adding an index alone cannot eliminate deep pagination scans; seek pagination should be adopted first:

SELECT id, order_no, status, pay_amount, created_at
FROM orders
WHERE tenant_id = ? AND user_id = ? AND status = ?
  AND created_at >= ? AND created_at < ?
  AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The governance system must allow the conclusion "fix the SQL first" rather than blindly adding indexes to prove its worth.

2. Governance Boundaries: Design the Right to Refuse Before the Right to Execute

Collect queries, indexes, health : Automatic read-only; raw snapshots and collection cycles persisted.

Generate candidate proposals : Automatic; no DDL produced.

Create invisible ordinary secondary index : Approved, executed by gateway; replay, space, replication, and MDL gates pass.

Set new index visible : Approved, executed by gateway; target validation and global checks pass.

Set suspected redundant index invisible : Dual approval; observation window covers full business cycle.

Restore index visibility : Automatic; regression threshold triggered.

Physically drop index : Manual via unified change entry; full observation, final review, rebuild plan.

PK, unique, FK-supporting, special indexes : Automatic prohibited; special review required.

Two inviolable rules: COUNT_READ = 0, prefix overlap, code-search no-hit are only candidate evidence, not proof.

Invisible indexes still incur write maintenance. They validate read-path dependency, not write-performance gain; the latter is only confirmed in controlled comparison after physical drop.

3. Architecture: Recommendation System and Change System Fully Isolated

MySQL Instance
P_S / Slowlog / Metadata
    ↓
Collector
Read-only, snapshot
    ↓
Evidence Store
Raw values, cycles, approvals
    ↓
Analyzer
Candidates, risks, replay tasks
    ↓
Approval & Change Gateway
Restricted actions, gates, audit
    ↓
MySQL Cluster
    ↓
App/DB Monitoring

The analyzer has no production DDL rights; the gateway accepts only three whitelisted actions: ADD_INVISIBLE, SET_VISIBLE, SET_INVISIBLE. Physical drops are submitted manually to the same audit entry to avoid the contradiction of "manual drop" in policy but automated DROP in code.

For a dozen instances with five-minute collection, a cron job writing directly to the governance store is usually sufficient; Kafka is only needed when query-event throughput is high, multiple consumers are required, or a replay queue is used.

4. Evidence Collection: Cumulative Counters Are Not Natural Window Metrics

4.1 Use Query Fingerprints to Find Total Cost

SELECT @@server_uuid AS server_uuid, NOW(6) AS collected_at,
       SCHEMA_NAME, DIGEST, LEFT(DIGEST_TEXT, 1024) AS digest_text,
       COUNT_STAR, SUM_TIMER_WAIT, SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
       SUM_NO_INDEX_USED, SUM_NO_GOOD_INDEX_USED,
       LEFT(QUERY_SAMPLE_TEXT, 2048) AS sample_sql,
       FIRST_SEEN, LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
  AND SCHEMA_NAME NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 500;

Governance ranking must combine window call count, cumulative DB time, average latency, rows-examined/rows-sent ratio, temp-table/filesort signals, and interface SLA. Slow logs only supplement slow samples; they cannot surface "8 ms per call, 200k calls/minute" high-total-cost queries.

4.2 Statistical Periods Must Be Explicitly Modeled

Performance Schema cumulative values reset on instance restart, stats-table truncation, or DDL that changes index structure. @@server_uuid usually stays the same after restart, so it cannot serve as a restart marker.

SELECT @@server_uuid AS server_uuid,
       @@hostname AS hostname, @@port AS port, @@version AS mysql_version,
       VARIABLE_VALUE AS uptime_seconds
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Uptime';

The collector uses server_uuid + (collected_at - uptime) to generate a boot_id. On uptime jump, current value < previous value, or detected index DDL, the current sample starts a new stats epoch; negative deltas are never computed.

func counterDelta(now, before uint64, sameBoot, sameStatsEpoch bool) uint64 {
    if !sameBoot || !sameStatsEpoch || now < before {
        return now
    }
    return now - before
}

4.3 Index I/O Is Only One Class of Evidence

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
       COUNT_READ, COUNT_FETCH,
       COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE,
       SUM_TIMER_READ
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
  AND INDEX_NAME IS NOT NULL;

COUNT_READ = 0

only means no read I/O in the current stats window. Inserts are recorded under INDEX_NAME = NULL, so write counts cannot be apportioned as "maintenance cost per index". Covering-index usage must be judged by combining execution plans, fingerprints, slow logs, and invisible-index observation.

4.4 Metadata Must Identify "Cannot Be Handled Automatically" Objects

SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, NON_UNIQUE, INDEX_TYPE,
       IS_VISIBLE, SEQ_IN_INDEX, COLUMN_NAME, SUB_PART, COLLATION,
       EXPRESSION, CARDINALITY
FROM information_schema.statistics
WHERE TABLE_SCHEMA = 'order_db'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

COLUMN_NAME

null with EXPRESSION non-null indicates a functional index. FULLTEXT, SPATIAL, functional indexes, primary keys, unique keys, unique-not-null keys that may act as implicit PKs, and unverifiable FK-supporting indexes are uniformly tagged BLOCKED. When reliable judgment is impossible, the correct strategy is to block, not guess.

4.5 External Dependencies Determine the Observation Window

Also collect ORM/Repository/SQL files, scheduler and reporting platforms, emergency scripts, service-account-to-digest mapping, business owners, and daily/weekly/monthly/refund/compensation/archive/promotion calendars. An index with zero reads for 14 consecutive days during month-end settlement still cannot be dropped.

5. Evidence Store: Preserve Raw Facts, Approval Facts, and Execution Facts

Core tables need not be complex but must include boot cycle and stats epoch:

CREATE TABLE gov_index_io_snapshot (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  instance_id VARCHAR(128) NOT NULL,
  boot_id VARCHAR(160) NOT NULL,
  stats_epoch VARCHAR(128) NOT NULL,
  reset_detected BOOLEAN NOT NULL DEFAULT FALSE,
  collected_at DATETIME(6) NOT NULL,
  schema_name VARCHAR(64) NOT NULL,
  table_name VARCHAR(64) NOT NULL,
  index_name VARCHAR(64) NOT NULL,
  count_read BIGINT UNSIGNED NOT NULL,
  count_fetch BIGINT UNSIGNED NOT NULL,
  KEY idx_object_time(instance_id, schema_name, table_name, index_name, collected_at)
) ENGINE=InnoDB;

CREATE TABLE gov_index_proposal (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  instance_id VARCHAR(128) NOT NULL,
  schema_name VARCHAR(64) NOT NULL,
  table_name VARCHAR(64) NOT NULL,
  action_type ENUM('ADD_INVISIBLE','SET_VISIBLE','SET_INVISIBLE','DROP_MANUAL') NOT NULL,
  index_name VARCHAR(64) NOT NULL,
  definition_json JSON NOT NULL,
  evidence_json JSON NOT NULL,
  risk_level ENUM('LOW','MEDIUM','HIGH','BLOCKED') NOT NULL,
  status ENUM('CANDIDATE','REVIEWING','APPROVED','EXECUTING','OBSERVING','SUCCEEDED','ROLLED_BACK','REJECTED') NOT NULL,
  version INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  KEY idx_status_created(status, created_at)
) ENGINE=InnoDB;

CREATE TABLE gov_proposal_approval (
  proposal_id BIGINT UNSIGNED NOT NULL,
  approver VARCHAR(128) NOT NULL,
  approval_role ENUM('SERVICE_OWNER','DBA','CHANGE_MANAGER') NOT NULL,
  decision ENUM('APPROVED','REJECTED') NOT NULL,
  decided_at DATETIME(6) NOT NULL,
  PRIMARY KEY(proposal_id, approver)
) ENGINE=InnoDB;

evidence_json

must contain: evidence time range, instance and boot_id, stats reset flag, target digest and owner, structural relationships, index hint/constraint checks, replay results, space estimate, replication topology, observation window, and success thresholds. A single boolean approved cannot express dual approval.

6. Candidate Generation: Rules Only Narrow the Scope

Composite indexes should not be mechanically ordered by cardinality. For the order query, tenant_id is the data boundary; user_id, status are equality predicates; created_at serves both range and sort; id enables stable pagination. Whether to include covering columns depends on actual read benefit vs. write cost — the secondary index must not become another wide table.

If A is a left prefix of B (e.g., A=(user_id,status), B=(user_id,status,created_at)), only a "structural overlap candidate" is generated. Automatic redundancy classification is forbidden when: constraint dependency exists, prefix length/sort direction/expression differs, A is narrower and has read benefit, USE INDEX / FORCE INDEX hints exist, or observation window is incomplete.

Before a new candidate enters approval, it must carry this evidence card:

Target Benefit : Replica EXPLAIN ANALYZE, real-parameter replay

Path Change : Key, access type, actual rows examined, temp/filesort

Global Impact : Before/after plan comparison for top digests of target table

Write & Space : DML rate, index width, estimated size, stress-test results

Business Risk : SLA, owner, business cycle, observation & recovery plan

Replica results only prove "worth production observation", not production P99. Replay records must include schema and MySQL version, data snapshot time, replication lag, statistics state, parameter distribution, cold/hot cache, and concurrency conditions.

7. New Index: Build and Optimizer Switch Validated in Two Steps

7.1 Create Invisible Index

SET SESSION lock_wait_timeout = 5;

ALTER TABLE order_db.orders
ADD INDEX idx_tenant_user_status_time
    (tenant_id, user_id, status, created_at, id) INVISIBLE,
ALGORITHM = INPLACE,
LOCK = NONE;

Explicit ALGORITHM and LOCK make changes that don't meet online expectations fail fast rather than silently degrade. LOCK=NONE does not mean no MDL; DDL start or end may still wait for long transactions, so gates must check pending MDL and long transactions on the target table.

In a dedicated replica or isolated session, let only the target query consider the invisible index:

EXPLAIN ANALYZE
SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */
       id, order_no, status, pay_amount, created_at
FROM orders
WHERE tenant_id = 1001
  AND user_id = 900018
  AND status = 'PAID'
  AND created_at >= '2026-08-01 00:00:00'
  AND created_at < '2026-09-01 00:00:00'
ORDER BY created_at DESC, id DESC
LIMIT 20;

EXPLAIN ANALYZE

actually executes the SQL; use only for read-only, desensitized, limited-row, session-timed representative queries — never run automatically on primary by the collector.

7.2 Make Visible and Observe Real Traffic

ALTER TABLE order_db.orders
ALTER INDEX idx_tenant_user_status_time VISIBLE,
ALGORITHM = INPLACE,
LOCK = NONE;

Observation window should not be a fixed 24 hours: high-frequency order list needs at least one full peak; tables involved in daily/monthly settlement must cover the corresponding cycle. Do not mechanically run ANALYZE TABLE after every index change; statistics refresh should be an independent, observable action.

8. Dropping Indexes: Invisible Observation Is the Safety Belt

Candidate → DualApproval → InvisibleObservation → RestoreVisible → ManualDropReview → ManualDrop
   ↓              ↓              ↓                ↓               ↓
Performance or   Full cycle     Observation      If regression    Final
functional       no regression  complete         immediate        review &
regression       → proceed      → proceed        restore visible  drop

ALTER TABLE order_db.orders
ALTER INDEX idx_status_created INVISIBLE,
ALGORITHM = INPLACE,
LOCK = NONE;

During observation, check latency, rows examined, error rate, new slow SQL, index hint errors, reporting replicas, and emergency scripts for associated digests. On regression, restore immediately:

ALTER TABLE order_db.orders
ALTER INDEX idx_status_created VISIBLE,
ALGORITHM = INPLACE,
LOCK = NONE;

Only after a full-cycle observation may a human execute DROP INDEX via the unified entry. Approval must include index rebuild time, acceptable degradation, backup, and on-site owner; post-drop "recovery" is not instantaneously reversible.

9. Change Gateway: Make Unsafe DDL Impossible to Construct

The gateway re-reads proposal, approvals, current index definition, and gate snapshots before execution — it cannot trust approval-time data. State transition uses optimistic locking:

UPDATE gov_index_proposal
SET status = 'EXECUTING', version = version + 1
WHERE id = ? AND status = 'APPROVED' AND version = ?;

Only the worker that affects exactly one row proceeds. Go snippet showing restricted DDL construction:

type Action string
const (
    AddInvisible Action = "ADD_INVISIBLE"
    SetVisible   Action = "SET_VISIBLE"
    SetInvisible Action = "SET_INVISIBLE"
    DropManual   Action = "DROP_MANUAL"
)

var safeIdent = regexp.MustCompile(`^[A-Za-z0-9_]+$`)

func ident(s string) (string, error) {
    if !safeIdent.MatchString(s) { return "", fmt.Errorf("unsafe identifier") }
    return "`" + s + "`", nil
}

func BuildDDL(p Proposal) (string, error) {
    if !hasServiceOwnerAndDBAApproval(p) { return "", errors.New("missing approvals") }
    if p.Action == DropManual { return "", errors.New("drop is never auto-executable") }
    if p.Risk != "LOW" && p.Risk != "MEDIUM" { return "", errors.New("risk blocked") }
    schema, err := ident(p.Schema); if err != nil { return "", err }
    table, err := ident(p.Table); if err != nil { return "", err }
    index, err := ident(p.Index); if err != nil { return "", err }
    object := schema + "." + table
    switch p.Action {
    case AddInvisible:
        cols, err := quotedColumns(p.Columns); if err != nil { return "", err }
        return fmt.Sprintf("ALTER TABLE %s ADD INDEX %s (%s) INVISIBLE, ALGORITHM=INPLACE, LOCK=NONE", object, index, cols), nil
    case SetVisible:
        return fmt.Sprintf("ALTER TABLE %s ALTER INDEX %s VISIBLE, ALGORITHM=INPLACE, LOCK=NONE", object, index), nil
    case SetInvisible:
        return fmt.Sprintf("ALTER TABLE %s ALTER INDEX %s INVISIBLE, ALGORITHM=INPLACE, LOCK=NONE", object, index), nil
    }
    return "", errors.New("unsupported action")
}

Then use GET_LOCK to serialize governance tasks on the same table, but note: it only blocks this platform's concurrency, not manual or other deployment systems' DDL. Therefore, integrate with the unified change entry and external DDL detection.

10. Execution Gates: Replication State Is Not a Single lag Number

Business : Promo, settlement, incident, freeze period, or no owner

Primary : Threads_running, CPU, disk latency, redo, long-txn threshold

MDL : Target table has pending MDL or abnormal long lock hold

Space : Insufficient budget for this DDL's index, binlog, temp space

Replication : Any critical replica: applier error, queue backlog, lag over threshold, member unhealthy

Conflict & Recovery : Existing schema change, invalid backup, no on-call

SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION,
       LOCK_STATUS, OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'order_db'
  AND OBJECT_NAME = 'orders'
  AND LOCK_STATUS = 'PENDING';

Multi-channel replication, lagging replicas, and Group Replication cannot be compressed into one ReplicaLagSecond. The gateway must evaluate each critical replica's connection status, applier errors, queue/lag, GTID progress, and member state individually; any critical state unknown → reject.

Native Online DDL pause/abort granularity is limited. On anomaly, first stop new tasks and alert; whether to terminate an in-flight DDL must be decided by the DBA per runbook based on current phase — cancelling the connection cannot be described as reliable rollback.

11. Native Online DDL vs. gh-ost

Manageable-size ordinary secondary index : Native INPLACE + LOCK=NONE — short path, no shadow-table cutover

Visibility toggle : Invisible index — fast, reversible metadata op

Huge table needing continuous throttling : Evaluate gh-ost — throttle, pause, control cut-over

Foreign keys or triggers : Do not use gh-ost directly — special review required

No non-null PK or unique key : Do not use gh-ost directly — lacks reliable migration key

Multi-source replication, active-active : Special review — topology risk exceeds generic flow

gh-ost first runs noop/dry-run on a dedicated replica. Space budget must include original data + all indexes, shadow table, binlog growth, temp files, and headroom for continued writes during pause; "2.5× original" is not a universal production formula.

12. Regression Judgment: Plan Change ≠ Regression

Persist plan baseline for target table's top digests: table_name, access_type, key, used_key_parts, join order, temp/filesort, representative parameters, estimated and actual rows examined. Plan hash change triggers review; true regression requires simultaneous latency, scan rows, error rate, and business acceptance checks.

Prometheus metrics must aggregate by instance and business dimension with minimum sample size:

expr: |
  (
    histogram_quantile(0.99,
      sum by (le, cluster, instance, schema, digest, route) (
        rate(mysql_query_duration_seconds_bucket[5m])
      )
    ) / on (cluster, instance, schema, digest, route)
    mysql_query_duration_seconds_p99_baseline
  ) > 1.20
  and on (cluster, instance, schema, digest, route)
  increase(mysql_query_duration_seconds_count[5m]) >= 1000

Production rules should also add absolute increase (e.g., >30 ms), consecutive windows, error-rate checks, and proposal_id correlation. Missing, zero, or unmatchable baseline labels must not silently pass; mark "observation invalid".

13. Order Table Full Loop: Gains, Regressions, and Boundaries

The team first converged projection columns and implemented cursor pagination, then replayed 5,000 real-parameter sets on a dedicated replica. Results: target query P95 dropped from ~812 ms to ~46 ms, typical scanned rows from 18,420 to ~20 — but this only qualified for production observation.

During maintenance window, after passing space, MDL, and critical-replica health gates, the invisible candidate index was created; plan validated, then made visible. Real-traffic observation results:

Order-list P99 : Before 2.3 s → After 170 ms; attribution: SQL rewrite + new index combined

Typical scanned rows : Before 18,000+ → After 20–40; attribution: new access path

Order-write P99 : Before 34 ms → After 27 ms; attribution: only compared after confirming old index drop

Index space : Before ~86 GB → After ~78 GB; attribution: net after adding narrow index and dropping one old index

Old indexes not processed concurrently. idx_user_status made invisible → customer-service backend scan rows rose → auto-restored visible; idx_status_created depended on month-end ops report → restored visible and owner recorded; only idx_user showed no regression across refund and daily-settlement cycles, then entered manual drop review.

This is the platform's value: it not only produces one optimization but explicitly blocks two erroneous deletions.

14. Making the Case Real: Plan Comparison, Execution Logs, and On-Site Gates

14.1 Don't Treat EXPLAIN "Looks Better" as Conclusion

Below is a representative-parameter replay summary. Fields from EXPLAIN ANALYZE and application-side same-parameter stats; omitted cost numbers do not participate in decisions, avoiding mistaking optimizer estimates for actual performance.

Index Used : Before idx_tenant_created → After idx_tenant_user_status_time; judgment: new path matches equality prefix

Rows Examined : Before 18,420 → After 23; judgment: target query benefit clear

Sort : Before extra sort after range scan → After reverse index scan; judgment: must still check real param distribution

Rows Returned : Before 20 → After 20; judgment: result set consistent

P95 (replica replay) : Before 812 ms → After 46 ms; judgment: only entry gate, not production promise

Replay must also verify result correctness, not just latency. For same parameters, compare PK set, row count, and key amount fields; for read-write mixed or time-sensitive queries, use fixed data snapshot or read-only replica to avoid mistaking data changes for index-induced differences.

-- Example: replay result PK set verification; actual impl by controlled task.
SELECT COUNT(*) AS row_count,
       BIT_XOR(CAST(id AS UNSIGNED)) AS id_checksum
FROM (
    SELECT id
    FROM orders
    WHERE tenant_id = 1001
      AND user_id = 900018
      AND status = 'PAID'
      AND created_at >= '2026-08-01 00:00:00'
      AND created_at < '2026-09-01 00:00:00'
    ORDER BY created_at DESC, id DESC LIMIT 20
) AS replay_result;

Such checks detect "plan faster but result semantics changed" replay script errors; they are not a general data-consistency algorithm.

14.2 Minimum Run Record for One Change

At approval, the system freezes target, boundaries, and recovery actions — not left to on-call memory:

{
  "proposal_id": 1842,
  "action": "ADD_INVISIBLE",
  "object": "order_db.orders",
  "index": "idx_tenant_user_status_time",
  "evidence_window": {
    "from": "2026-08-18T00:00:00Z",
    "to": "2026-09-01T00:00:00Z",
    "boot_ids": ["uuid-a@2026-08-10T02:03:11Z"],
    "stats_reset_detected": false
  },
  "approvals": ["service_owner", "dba"],
  "execution_window": "02:00-05:00 Asia/Shanghai",
  "success": {
    "target_p99_ms": 300,
    "max_write_p99_increase_ms": 10,
    "min_samples_per_5m": 1000
  },
  "rollback": "ALTER INDEX idx_tenant_user_status_time INVISIBLE"
}

Actual execution order:

Gateway locks proposal state, re-reads table definition, approvals, instance load, space, critical replica set; any mismatch → reject.

Check target table has no pending MDL, abnormal long txn, or other schema change; set short lock_wait_timeout then submit DDL.

During DDL, collect primary load, redo, disk, replication, business metrics every 30s. Native DDL on anomaly: stop new tasks, escalate to human; do not blindly abort current task.

After DDL, record connection ID, SQL, start/end time, error code, before/after snapshots; then enter invisible validation or visible observation state.

Alerts linked to proposal_id. Only visibility of the index affected by this change may be auto-restored, avoiding misattribution of unrelated incidents.

A auditable gate object is more complete than a single lag field:

type ReplicaHealth struct {
    Name           string
    Required       bool
    IOHealthy      bool
    ApplierHealthy bool
    QueueSeconds   int
    GTIDAdvancing  bool
    MemberHealthy  bool // non-GR can be true
}

type GateSnapshot struct {
    ThreadsRunning int
    FreeDiskPct    float64
    LongTxnSeconds int
    PendingMDL     bool
    Replicas       []ReplicaHealth
}

func (s GateSnapshot) Validate() error {
    if s.FreeDiskPct < 35 || s.ThreadsRunning > 40 ||
        s.LongTxnSeconds > 60 || s.PendingMDL {
        return errors.New("primary safety gate rejected")
    }
    for _, r := range s.Replicas {
        if r.Required && (!r.IOHealthy || !r.ApplierHealthy ||
            !r.GTIDAdvancing || !r.MemberHealthy || r.QueueSeconds > 3) {
            return fmt.Errorf("required replica unhealthy: %s", r.Name)
        }
    }
    return nil
}

Thresholds are not universal constants. The example's 35% disk, 40 running threads, 60s txn, 3s queue only illustrate gate structure; production values must derive from instance capacity, historical percentiles, and change risk model.

15. Rollout Sequence and Acceptance Checklist

Suggest phased enablement: observe 2–4 weeks; then only recommend and review false positives; then enable approved invisible-index creation; finally enable low-risk invisible observation. Physical drop always retains human final decision.

Snapshot includes instance, boot_id, stats epoch, collection time; resets not computed across cycles.

Proposal includes owner, business calendar, parameter replay, space budget, replication topology, success criteria.

Special indexes and unverifiable FK dependencies all marked BLOCKED.

Approval records store approver, role, timestamp; no automatic DROP path exists.

DDL pre-check and mid-execution both re-read gates; same-table tasks serialized; external DDL detectable.

Regression alerts carry instance/business labels, sample size, and baseline-missing policy.

Invisible restore, gh-ost pause/panic/cut-over, post-drop rebuild all rehearsed.

16. Closing

The maturity marker of index governance is not "can auto-execute DDL" but the ability to reliably refuse execution when evidence is insufficient, observation fails, replication is abnormal, or business window is unsuitable. Only by putting query fingerprints, index definitions, business ownership, stats cycles, approvals, and change results into a single evidence chain, then gradually expanding automation scope, can sustainable index governance exist in high-concurrency production.

References

MySQL 8.0: Invisible Indexes

MySQL 8.0: Online DDL Operations

MySQL 8.0: Table I/O and Lock Wait Summary Tables

gh-ost Requirements and Limitations