Indexes Aren't Free: Production Index Governance for High-Write Order Systems

1. A Real Index Incident Before a Major Promotion

A new operational page required querying orders by buyer, status, and creation time. Slow SQL showed scans of tens of thousands of rows for heavy buyers. The team added a composite index:

CREATE INDEX idx_buyer_status_ctime_id
ON orders (buyer_id, order_status, create_time DESC, id DESC);

In staging, the target query dropped from 420 ms to 18 ms. After release, read latency stabilized, but as traffic increased during the promotion, payment and cancellation write latency degraded:

10:00  Index deployed, list P99: 420ms → 18ms
11:30  Traffic rises, payment UPDATE P99: 45ms → 110ms
12:10  Redo writes, dirty pages, disk latency rise together
12:25  Replica apply falls behind, replica lag: 2s → 95s
12:40  Read-after-write hits stale replica data, retries increase
12:55  Connection pool piles up, order chain times out

The root cause was not that the index "didn't work"; it contained the high-frequency changing order_status. The go-live decision only validated Q1 read benefit, did not compare critical write path costs, and did not include replica lag and DDL observation in the gate.

Slow list SQL
  ↓
Add composite index ──→ list scan reduced, sort eliminated
  ↓
High-frequency order_status updates ──→ maintain more secondary indexes
  ↓
Redo / dirty pages / disk latency rise
  ↓
Replica apply slows ──→ read-after-write stale ──→ retries & connection pool pile-up

2. Define Queries First, Not Fields

The simplified orders table uses a narrow, monotonically increasing id as clustered primary key; business order number keeps a unique constraint.

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  buyer_id BIGINT UNSIGNED NOT NULL,
  seller_id BIGINT UNSIGNED NOT NULL,
  order_status TINYINT NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL,
  create_time DATETIME(3) NOT NULL,
  update_time DATETIME(3) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no)
) ENGINE=InnoDB;

List real SQL fingerprints, frequency, SLO, and consistency requirements:

Q1 : Buyer + status, time descending, limit 20 — 2,500 QPS, P99 < 80ms — Brief eventual consistency OK

Q2 : By order_no detail — 1,800 QPS, P99 < 30ms — Must read primary after payment

Q3 : Merchant recent orders — 600 QPS, P99 < 120ms — Brief eventual consistency OK

Q4 : Archive by create_time — Low-frequency batch — Not on online transaction path

Don't translate "buyer_id often queried", "status often queried", "create_time often queried" directly into three single-column indexes. Indexes serve the access pattern composed of WHERE, ORDER BY, LIMIT, and returned columns together.

3. Index Cost: What One Status Update Adds

Assume the table already has:

uk_order_no(order_no)
idx_buyer_status_ctime_id(buyer_id, order_status, create_time DESC, id DESC)
idx_status_ctime(order_status, create_time)

Payment callback executes:

UPDATE orders
SET order_status = 1,
    update_time = NOW(3)
WHERE id = ?;

This updates not only the clustered index record but also every secondary index containing order_status. Each additional tree containing a high-frequency change column adds continuous write, page modification, redo, and buffer pool contention cost.

Cost cannot be simplified to "one index slows 30%". It depends on:

Index count and width, primary key width, update frequency of indexed columns
Page in buffer pool, write orderliness, concurrent transactions
Redo flush strategy, disk latency, data distribution, working set size

Therefore compare real workload index sets:

Baseline          PRIMARY + uk_order_no
Candidate A       Baseline + idx_buyer_status_ctime_id
Candidate B       Candidate A + idx_seller_ctime_id

Compare not just target SQL but Q1/Q2/payment update P95, P99, TPS, redo bytes/s, disk latency, and replica lag deltas.

4. Q1 Index: Filter, Sort, and Pagination Protocol Together

Buyer order list query:

SELECT id, order_no, order_status, total_amount, create_time
FROM orders
WHERE buyer_id = ?
  AND order_status = ?
ORDER BY create_time DESC, id DESC
LIMIT 20;

Candidate index:

CREATE INDEX idx_buyer_status_ctime_id
ON orders (buyer_id, order_status, create_time DESC, id DESC);

It uses two equality conditions to narrow range, then reads in the application's required stable order. id is not arbitrarily appended: multiple orders can occur in the same millisecond; create_time, id together form a stable pagination cursor.

Avoid deep pagination:

-- Not recommended: more records scanned and discarded further back
SELECT ... LIMIT 100000, 20;

Use Keyset Pagination:

SELECT id, order_no, order_status, total_amount, create_time
FROM orders
WHERE buyer_id = ?
  AND order_status = ?
  AND (create_time < ? OR (create_time = ? AND id < ?))
ORDER BY create_time DESC, id DESC
LIMIT 20;

Caller must pass the last row's create_time and id from previous page; otherwise same-timestamp data may duplicate or be missed.

This index does not cover order_no or total_amount, so it still does a lookup. For 20 rows per page, lookup is often acceptable; stuffing all returned columns into the index for "Using index" often brings wider index, larger working set, and heavier write amplification.

5. Verifying Effectiveness: Plan, Measurement, and Data Distribution

Regular EXPLAIN shows optimizer plan and estimates; EXPLAIN ANALYZE actually executes and outputs actual rows, loops, and time.

EXPLAIN ANALYZE
SELECT id, order_no, order_status, total_amount, create_time
FROM orders
WHERE buyer_id = 10086
  AND order_status = 0
ORDER BY create_time DESC, id DESC
LIMIT 20;

Key checks:

Difference between estimated rows and actual rows
Ratio of scanned rows to returned rows
Extra filesort / temporary table
First row return time, total time, actual lookup count

EXPLAIN ANALYZE

is available from MySQL 8.0.18 and really executes SQL. Prefer staging, shadow traffic, or load test environments; production only for read-only queries with resource limits and kill switch. It is not a zero-cost EXPLAIN.

Example: estimated scan 120 rows, actual 180,000 rows — first suspect statistics and data skew, not immediately add a second index. order_status is low cardinality, but if PENDING is only 0.2%, it may still filter well; conversely, when returning most data, a single-column status index usually has no value.

MySQL 8.0 can add histograms on demand:

ANALYZE TABLE orders
UPDATE HISTOGRAM ON order_status WITH 32 BUCKETS;

Histograms only improve optimizer's understanding of data distribution; they do not create B+ trees and cannot replace a composite index matching the query pattern.

6. Change Buffer, Unique Constraints, and DESC Index Boundaries

Change Buffer can cache secondary index page changes not in buffer pool, aiming to avoid immediate random reads; it does not eliminate index maintenance cost.

Three boundaries must be stated together:

It only applies to eligible secondary indexes, not clustered, fulltext, or spatial indexes.

Unique secondary indexes require uniqueness check at write time; cannot treat them as relying on Change Buffer for performance.

Secondary indexes containing descending key columns do not support Change Buffer. Q1's create_time DESC, id DESC is for sort protocol; do not assume it also gains Change Buffer benefit.

Therefore true data invariants like order number and payment idempotency keys must keep UNIQUE. Cannot drop constraints for performance and let application "try to guarantee uniqueness".

7. Converge Order Table Indexes, Not Keep Stacking

For the four access patterns, start load testing with this set:

ALTER TABLE orders
ADD KEY idx_buyer_status_ctime_id
  (buyer_id, order_status, create_time DESC, id DESC),
ADD KEY idx_seller_ctime_id
  (seller_id, create_time DESC, id DESC),
ADD KEY idx_create_time (create_time);

uk_order_no

— Serves Q2, order idempotency — Main cost: uniqueness check, but business correctness first idx_buyer_status_ctime_id — Serves Q1 — Main cost: status update maintenance, DESC index writes idx_seller_ctime_id — Serves Q3 — Main cost: order insert writes, buffer pool working set idx_create_time — Serves Q4 — Main cost: evaluate if archiving should move off main table

Primary key design also affects all secondary indexes: InnoDB secondary index leaf records contain primary key value. Using a long random business string as primary key widens every secondary index; high-write tables typically use a narrow clustered primary key and carry order number via a business unique index.

8. Deleting Indexes: From Candidates to Real Write Cost Release

sys.schema_unused_indexes

, slow log, and duplicate index tools only provide candidates. Instance restart, month-end jobs, DR switch SQL, BI/ETL, or promotion paths can make an index appear "unused" in the observation window.

Recommended path:

Candidate index
  ↓
SQL fingerprint + slow log + code/task search + full business cycle
  ↓
ALTER INDEX ... INVISIBLE
  ↓
Observe plan, latency, business alerts
  ↓
DROP INDEX

ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;

Invisible Index only prevents optimizer from choosing it by default; DML still maintains it, write cost does not drop. It validates "can read path lose this index", not final deletion. Requires MySQL 8.0; change itself still needs DDL risk control.

Even if idx_buyer(buyer_id) is a left prefix of idx_buyer_status(buyer_id, order_status), don't mechanically delete; the shorter index may still have value in specific covering, scan, and cache scenarios — decide by real plan and load test.

9. Read-Write Separation: Routing Decided by Consistency

"All SELECT to replica" creates order status illusion. After payment success, immediate detail query may hit replica lag and show "payment succeeded, order still pending".

Payment result / fund status / state machine advance ─────────→ Primary
User post-write short window (Session Sticky) ────→ Primary
History order list / non-critical display ───────────────→ Replica, protected by lag threshold
Report / large scans ───────────────────────────────→ Dedicated query or analytics system

Extra indexes on replica are not free capacity: row replication apply must also maintain replica's local indexes. If read side truly needs different index set, build a dedicated query replica with independent DDL, monitoring, rebuild, and failover plan — not by temporarily altering a regular replica.

10. Post-Sharding Merchant Query: Index Problem Becomes Routing Problem

If orders sharded by buyer_id, buyer queries route directly; merchant query WHERE seller_id = ? cannot. Even with seller_id index on every shard, cross-shard scatter-gather remains.

Suitable for eventual consistency: a routable merchant order index table:

CREATE TABLE seller_order_index (
  seller_id BIGINT UNSIGNED NOT NULL,
  create_time DATETIME(3) NOT NULL,
  order_no VARCHAR(32) NOT NULL,
  buyer_id BIGINT UNSIGNED NOT NULL,
  shard_id INT NOT NULL,
  order_status TINYINT NOT NULL,
  event_version BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (seller_id, create_time, order_no),
  UNIQUE KEY uk_order_no (order_no)
) ENGINE=InnoDB;

seller_id
  ↓
seller_order_index (by create_time DESC)
  ↓ order_no + shard_id
Target orders shard

This is a derived read model; must explicitly define latency tolerance, fallback on missing index, event replay, and periodic reconciliation; it must not carry fund, inventory, or payment result strong-consistency decisions.

11. Reliable Sync: Don't Treat Local Transaction and Kafka as One Transaction

The following Java code cannot guarantee atomic consistency between MySQL update and Kafka send:

@Transactional
public void updateOrderStatus(Long orderId, OrderStatus status) {
  orderMapper.updateStatus(orderId, status);
  kafkaTemplate.send("order-index-update", buildEvent(orderId, status));
}

It may produce "DB committed, message send failed" or "message received, DB eventually rolled back". Reliable path is Transactional Outbox.

CREATE TABLE outbox_event (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  event_id CHAR(36) NOT NULL,
  aggregate_type VARCHAR(32) NOT NULL,
  aggregate_id BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(64) NOT NULL,
  event_version BIGINT UNSIGNED NOT NULL,
  payload JSON NOT NULL,
  status VARCHAR(16) NOT NULL,
  create_time DATETIME(3) NOT NULL,
  sent_time DATETIME(3) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_event_id (event_id),
  KEY idx_status_id (status, id)
) ENGINE=InnoDB;

Write order fact and outbox fact in same local transaction:

BEGIN;

UPDATE orders
SET order_status = ?, update_time = NOW(3)
WHERE id = ?;

INSERT INTO outbox_event (
  event_id, aggregate_type, aggregate_id, event_type,
  event_version, payload, status, create_time
) VALUES (?, 'ORDER', ?, 'ORDER_STATUS_CHANGED', ?, ?, 'NEW', NOW(3));

COMMIT;

orders update + outbox insert (same transaction)
  ↓
Publisher batches and sends to Kafka
  ↓
Mark SENT on success; failure window allows duplicate send
  ↓
Consumer idempotently writes read model by unique key and version

Publisher needs a claim or equivalent mechanism to avoid multiple instances scanning unbounded; but must still accept at-least-once delivery from "send succeeded, mark failed".

12. Consumer Idempotency: Converge with DB State, Not Rely on Redis Locks First

Duplicate delivery, out-of-order delivery, consumer restart, and Kafka rebalance are normal. If final read model lands in MySQL, prefer unique key with business version:

INSERT INTO seller_order_index (
  seller_id, create_time, order_no, buyer_id,
  shard_id, order_status, event_version
) VALUES (?, ?, ?, ?, ?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
  order_status = IF(new.event_version > event_version,
                    new.order_status, order_status),
  shard_id = IF(new.event_version > event_version,
                new.shard_id, shard_id),
  buyer_id = IF(new.event_version > event_version,
                new.buyer_id, buyer_id),
  event_version = GREATEST(event_version, new.event_version);

event_version

placed last in assignment list ensures preceding conditions still compare against old version. Production implementation must have integration tests covering: duplicate, out-of-order, replay, dead letter recovery, and full reconciliation. MySQL 8.0.20+ should not use deprecated VALUES(col) to access new row; example uses row alias new.

13. Online DDL: LOCK=NONE ≠ No Lock, ≠ Auto Rollback

When adding index, explicitly declare required algorithm and lock level; if target version doesn't support, it should fail rather than silently degrade:

ALTER TABLE orders
ADD INDEX idx_seller_status_ctime
  (seller_id, order_status, create_time),
ALGORITHM=INPLACE,
LOCK=NONE;

LOCK=NONE

aims to allow concurrent queries and DML, but DDL still needs metadata lock. If a long transaction already accessed orders and hasn't committed, DDL waits; subsequent requests may queue behind DDL, filling connection pool.

Pre-deployment checks for long transactions and lock waits:

SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

SELECT ml.OBJECT_SCHEMA, ml.OBJECT_NAME, ml.LOCK_TYPE, ml.LOCK_STATUS,
       t.PROCESSLIST_ID, t.PROCESSLIST_USER, t.PROCESSLIST_TIME,
       t.PROCESSLIST_INFO
FROM performance_schema.metadata_locks AS ml
JOIN performance_schema.threads AS t
  ON ml.OWNER_THREAD_ID = t.THREAD_ID
WHERE ml.OBJECT_SCHEMA = 'ecommerce'
  AND ml.OBJECT_NAME = 'orders';

Also check disk and temp space, redo, CPU/IO, business peak, and replica lag. Large tables can use gh-ost to throttle, but it still copies data and consumes CPU, IO, network, and disk:

gh-ost \
  --host=mysql-primary \
  --database=ecommerce \
  --table=orders \
  --alter="ADD INDEX idx_seller_status_ctime (seller_id, order_status, create_time)" \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=100" \
  --execute

Can pause or terminate before cut-over; no automatic rollback after cut-over . Must prepare reverse DDL, execution window, and business validation. On Kubernetes also verify PVC capacity/expansion, IOPS, Pod resource limits, and Operator failure recovery behavior.

14. Index Go-Live Gate: Turn Experience into Auditable Decisions

Every new index must bind a change ticket and owner. Minimum gate:

Query Benefit : SQL fingerprint, current vs target P99, scanned rows delta, sort/lookup changes

Write Cost : Affected DML, indexed column update frequency, allowed write P99 increase

Capacity : Index size estimate, buffer pool budget, backup and replica headroom

DDL : MySQL version, algorithm/lock requirements, execution window, stop thresholds

Rollback : Canary observation items, and different handling before/after cut-over

Lifecycle : Business owner, dependent services, review date, deletion candidate criteria

Load test needs near-real data volume, hotspots, transaction concurrency, and read-write ratio — only swap index set. Canary observation should cover:

Query: SQL fingerprint P95/P99, rows examined, filesort, temp table, plan changes
Write: Order/payment/cancel TPS & P99, error rate, connection pool wait
InnoDB: redo bytes/s, fsync, dirty pages, page reads/writes, buffer pool, disk latency
Replication: replica lag, apply throughput, worker/SQL thread pressure
Capacity: index size, disk headroom, backup and recovery window

Alerts are not "observe anomaly then discuss". Pre-agree: if payment UPDATE P99 exceeds baseline 20% for 5 continuous minutes, replica lag > 30s, disk latency exceeds threshold — immediately stop or pause change and execute plan.

15. Long-Term Governance: Every B+ Tree Has an Owner

Maintain an index asset register for core databases:

table / index: orders / idx_buyer_status_ctime_id

owner: Order Team

SQL fingerprint: Q1

read_qps / SLO: 2,500 / P99 < 80ms

affected_write: Order create, payment callback, cancel

size_gb: 38

version / ticket: MySQL 8.0.x / DDL-2026-0916

last_review / status: 2026-09-16 / active

Continuously monitor index usage, duplicate candidates, schema drift, and business ownership. sys.schema_unused_indexes is suitable for generating candidate lists but never as direct basis for drop commands.

When root cause is historical data far exceeding online transaction working set, prioritize cold-hot tiering, archive library, dedicated query replica, CDC to search or OLAP; don't let core transaction table endlessly stack indexes trying to serve all queries.

Summary

Mature index design is not "SQL slow, add index", but a chain of continuous questions:

Which query does it serve, what work exactly reduced?
Which write paths become expensive, is cost within budget?
Will it squeeze buffer pool and replica headroom?
How to canary, monitor, stop, and handle at each phase?
Who owns it, when to review or delete?

An index is a long-term budget. Only indexes proven valuable in real workload and able to bear their write, capacity, and operational costs deserve to stay in the core transaction table.