MySQL Index Optimization Handbook: Theory, Architecture, and Production Practices

1. A Real Incident: Index Exists but Performance Collapses

A major e‑commerce system experienced a severe slowdown during a promotional warm‑up night. At 02:17 the order‑list API P99 latency jumped from 18 ms to 6.8 s, exhausting the connection pool, blocking threads, and causing a service avalanche. The DBA’s first reaction was “the SQL has an index, why is it still slow?”.

Problematic SQL

SELECT order_id, amount, status, created_at
FROM orders
WHERE merchant_id = 10086
  AND status IN (1,3,7)
  AND created_at >= '2026-05-01 00:00:00'
ORDER BY created_at DESC
LIMIT 20;

Table size: orders has 130 million rows, 8 million new rows per day, peak write 25 k TPS, peak read 120 k QPS.

Original indexes:

KEY idx_merchant_id (merchant_id),
KEY idx_status (status),
KEY idx_created_at (created_at)

The optimizer chose idx_status because the status IN (…) predicate looked highly selective in statistics, but the single‑column indexes cannot satisfy the ordering requirement. The execution plan scanned many rows matching the status filter, performed a back‑to‑table lookup, sorted, and then returned the first 20 rows.

Root causes:

Three single‑column indexes cannot replace a high‑quality composite index. ORDER BY created_at DESC does not use index order.

Selected columns are not covered, causing massive back‑to‑table reads.

Optimizer decisions based on stale or skewed statistics.

Optimized index

ALTER TABLE orders
ADD INDEX idx_merchant_status_created_cover (
    merchant_id,
    status,
    created_at DESC,
    order_id,
    amount
);

Result

Scanned rows reduced from tens of millions to dozens.

Sorting changed from filesort to ordered index reads.

Back‑to‑table reads eliminated.

API P99 latency restored from 6.8 s to 11 ms.

Key takeaway : Index optimization is not “add a KEY to a column”; it is designing data structures around the actual access path.

2. The Essence of Index Optimization: Reduce Scans, Back‑to‑Table, Sorting, and Waits

Many people think of an index as a “directory”. A more engineering‑oriented view is that an index is an ordered data structure pre‑maintained for a specific read path.

Index optimization fundamentally does four things:

Reduce the number of data pages scanned.

Perform filtering as early as possible within the index.

Avoid back‑to‑table lookups.

Leverage index order to eliminate extra sorting.

Typical slow‑query costs can be classified into six categories:

Page read cost – too many pages.

Row evaluation cost – too many rows examined.

Back‑to‑table cost – excessive row fetches.

Sort cost – expensive external sorting.

Lock wait cost – contention under high concurrency.

Network / application cost – large result sets slow the client.

An index is considered good when it simultaneously reduces scan range, matches WHERE, matches ORDER BY, covers SELECT, and keeps write amplification and storage cost under control.

3. Underlying Principles

3.1 Why InnoDB Uses B+Tree

Red‑black trees have high height → unpredictable I/O.

Hash indexes only support equality, not range scans or ordering.

B+Tree has high fan‑out, low height, stable disk access.

Leaf nodes are naturally ordered, ideal for range queries, sorting, and pagination.

InnoDB page size is 16 KB. A small index entry allows thousands of keys per non‑leaf page, so billions of rows usually need only 3–4 levels.

3.2 Clustered vs. Secondary Indexes – Why Back‑to‑Table Is Expensive

Primary‑key (clustered) index leaf nodes store the whole row.

Secondary index leaf nodes store (indexed_columns, primary_key).

To retrieve a full row via a secondary index, MySQL first finds the primary key in the secondary index, then looks up the row in the clustered index – the “back‑to‑table” step. When a query matches millions of rows, even a cheap secondary index can cause massive back‑to‑table traffic.

Covering index benefit : All required columns are stored in the index, eliminating the back‑to‑table step and reducing random I/O.

3.3 Why the Left‑most Prefix Rule Holds

A composite index (a, b, c) can be used for:

a = ?

a = ? AND b = ?

a = ? AND b = ? AND c = ?

a = ? AND b > ?

a = ? ORDER BY b, c

It cannot be used when the leftmost column is omitted, e.g., b = ? or c = ?, because the underlying ordering is lexical.

3.4 Function/Expression Indexes – When They Fail

If an index stores the raw column value but the query applies a function (e.g., DATE(created_at)), the optimizer cannot use the index because the stored order does not match the expression order.

Two solutions:

Rewrite the predicate to avoid the function, e.g., use a range on created_at.

Use a generated (function) index (MySQL 8.0.13+).

3.5 Why the Optimizer Sometimes Picks the Wrong Index

The optimizer relies on statistics and cost models. Common reasons for mis‑selection:

Severe data skew.

Hot values occupying a large proportion.

Out‑of‑date statistics.

Inaccurate cardinality estimation for IN lists.

Strong correlation between columns that single‑column stats cannot capture.

Mitigations include ANALYZE TABLE, designing composite indexes that match the query, using FORCE INDEX, and inspecting EXPLAIN ANALYZE results.

4. Methodology: Index Design Is a Fixed Process

4.1 Step 1 – Draw the Query Access Path

Before adding any index, decompose the SQL into four parts: WHERE – filtering conditions. JOIN ON – join conditions. ORDER BY – sorting requirements. SELECT – columns to return.

Example:

SELECT order_id, amount, created_at
FROM orders
WHERE merchant_id = ?
  AND status = ?
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 20;

Access path summary:

Exact filtering: merchant_id, status Range filtering: created_at Sorting: created_at DESC Returned columns: order_id, amount,

created_at

4.2 Step 2 – Distinguish Equality, Range, and Sorting Columns

Typical ordering of columns in a composite index for best performance:

High‑selectivity equality columns.

Other equality columns.

Range columns.

Sorting columns.

Covering columns that are not used for filtering.

For the example the ideal index is:

(merchant_id, status, created_at DESC, order_id, amount)

Note: columns after a range column cannot be used for further filtering, but in MySQL 8.0 they may still be useful for covering or ordering.

4.3 Step 3 – Evaluate Selectivity, Not Just Cardinality

Selectivity = distinct values / total rows. High selectivity usually means strong filtering, but the index order must still match the query’s access pattern. A low‑selectivity column like gender is useless alone, yet can be valuable as a middle column in a composite index when combined with other predicates.

4.4 Step 4 – Decide Whether a Covering Index Is Worth It

Covering indexes excel for:

High‑frequency read interfaces.

Small result sets.

Stable returned columns.

Latency‑sensitive queries.

They are unsuitable when:

Returned columns change frequently.

The table is very wide.

Write throughput is the primary concern.

Covering indexes increase index size, write cost, and page‑split frequency, so a trade‑off analysis is mandatory.

4.5 Step 5 – Assess Write Cost

Each additional index adds write overhead:

Clustered index write.

Multiple secondary index writes.

Page splits.

Redo/undo log growth.

Binlog amplification.

Replication lag.

For high‑concurrency tables, keep indexes minimal, avoid wide indexes, and do not place frequently updated columns at the beginning of a composite index.

5. Four Typical Index Designs Based on Business Access Patterns

5.1 Order‑List Scenario: Filter + Sort + Pagination

Typical SQL:

SELECT order_id, amount, status, created_at
FROM orders
WHERE merchant_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20;

Recommended index:

ALTER TABLE orders
ADD INDEX idx_merchant_status_created_cover (
    merchant_id,
    status,
    created_at DESC,
    order_id,
    amount
);

Rationale: merchant_id and status provide exact filtering. created_at DESC satisfies ordering. order_id and amount make the index covering, eliminating back‑to‑table.

5.2 User‑Detail Scenario: Unique Lookup

Typical SQL:

SELECT id, nickname, avatar, status
FROM user
WHERE phone = ?;

Recommended index:

ALTER TABLE user
ADD UNIQUE INDEX uk_phone (phone);

Unique indexes give the optimizer a guaranteed single‑row result, stabilizing cost estimates.

5.3 Time‑Range Log Scenario

Typical SQL:

SELECT id, user_id, action, created_at
FROM user_action_log
WHERE user_id = ?
  AND created_at >= ?
  AND created_at < ?
ORDER BY created_at DESC
LIMIT 100;

Recommended index:

ALTER TABLE user_action_log
ADD INDEX idx_user_created_at (user_id, created_at DESC);

Key points:

Single‑column time index is insufficient.

Most queries first filter by user, then by time range.

Pure created_at index often scans too many rows.

5.4 Report‑Aggregation Scenario

Typical SQL:

SELECT merchant_id, COUNT(*), SUM(pay_amount)
FROM orders
WHERE created_at >= ?
  AND created_at < ?
  AND status = 2
GROUP BY merchant_id;

Building an index for this query rarely helps because the scan range is already large and aggregation is heavy. Better solutions are:

Materialized summary tables.

Offline batch computation.

CDC + Kafka + ClickHouse/Doris for analytical workloads.

Offload OLAP queries to a separate read‑only replica.

Conclusion: OLTP‑oriented index optimization does not suit massive analytical scans.

6. Most Effective Production Index Strategies

6.1 Composite Indexes – The First Solution for Most Problems

Many slow queries can be fixed by replacing multiple single‑column indexes with a correctly ordered composite index. Example of a wrong design:

KEY idx_a (merchant_id),
KEY idx_b (status),
KEY idx_c (created_at)

Correct design:

KEY idx_merchant_status_created (merchant_id, status, created_at DESC)

Reasons:

Single‑column indexes filter independently but cannot express the true access path.

Composite indexes match the query’s predicate order. Index Merge is often slower than a single composite index.

6.2 Covering Indexes – Performance Weapon for High‑Frequency Reads

Example query:

SELECT order_id, amount, created_at
FROM orders
WHERE merchant_id = ?
ORDER BY created_at DESC
LIMIT 20;

With a covering index:

(merchant_id, created_at DESC, order_id, amount)

The query can be satisfied entirely from the index, yielding:

Reduced random I/O.

Higher buffer‑pool hit rate.

Eliminated back‑to‑table.

Trade‑offs: larger index size, slower writes, higher page‑split probability. Use only on stable, high‑read, low‑write paths.

6.3 Descending Indexes – A Practical Enhancement in MySQL 8.0

Before MySQL 8.0, descending order required a reverse scan or extra sort. With explicit DESC indexes, the optimizer can use the index directly:

ALTER TABLE orders
ADD INDEX idx_merchant_created_desc (merchant_id, created_at DESC);

Typical use cases: recent‑order lists, news feeds, time‑ordered streams.

6.4 Function Indexes – Solve Expression Queries, Not a Blanket Fix

MySQL 8.0.13+ supports generated (function) indexes:

ALTER TABLE user_login_log
ADD INDEX idx_login_date ((DATE(login_time)));

ALTER TABLE user_profile
ADD INDEX idx_mobile ((CAST(profile->>'$.mobile' AS CHAR(11))));

Best practice: prefer rewriting SQL to avoid functions; function indexes increase maintenance complexity and team learning cost.

6.5 Invisible Indexes – Safe Index Governance

Before dropping an index, make it invisible and observe:

ALTER TABLE orders ALTER INDEX idx_old INVISIBLE;

Monitor core‑API P95/P99, slow‑query count, plan fallback, replica load. If problems appear, revert:

ALTER TABLE orders ALTER INDEX idx_old VISIBLE;

7. Write‑Side Considerations for High‑Concurrency Workloads

7.1 Every Additional Index Adds Write Cost

During an INSERT or UPDATE, MySQL must:

Update the clustered index.

Update each secondary index.

Possibly split pages.

Generate redo/undo logs.

Increase binlog size.

Amplify replication traffic.

Updating indexed columns further increases cost because old index entries must be removed and new ones inserted.

7.2 Index Design Principles for High‑Write Tables

Keep only necessary indexes.

Limit the number of composite indexes.

Avoid wide indexes.

Do not place frequently updated columns at the beginning of a composite index.

Avoid complex indexes on hotspot columns.

Example: if status changes often and write TPS is huge, evaluate the benefit of having status in multiple composite indexes.

7.3 Primary‑Key Choices and Write Impact

Auto‑increment integer : sequential writes, few page splits, excellent insert performance, but not ideal for sharding.

Snowflake ID (BIGINT) : globally unique, roughly increasing, good for distributed systems, slightly more fragmented.

UUID : fully random, causes severe page splits and index bloat; usually not suitable for primary keys in large tables. Prefer ordered UUIDs or snowflake IDs.

7.4 Hotspot Indexes and Write Hotspots

Indexes on monotonically increasing timestamps, a single tenant with massive traffic, or a frequently updated status column become write hotspots, leading to latch contention, insert‑buffer pressure, and page‑split spikes. Mitigations:

Partition or shard by tenant or time.

Batch writes to smooth spikes.

Offload secondary queries to asynchronous pipelines.

Separate hot and cold data.

8. Three Must‑Know Pagination Techniques

8.1 LIMIT offset, size – Why It Becomes Slow

Even with an index, MySQL must skip the first offset rows, so large offsets cause high scan cost.

8.2 Keyset Pagination – The Standard for High‑Concurrency Lists

Use the last row of the previous page as the cursor:

SELECT order_id, amount, created_at
FROM orders
WHERE merchant_id = 10086
  AND created_at < '2026-05-20 10:30:15.123'
ORDER BY created_at DESC
LIMIT 20;

Supporting index: (merchant_id, created_at DESC, order_id) Advantages: no large offset, stable scan cost, ideal for mobile and time‑stream scenarios.

8.3 Composite Cursor Pagination – Handling Duplicate Timestamps

If created_at is not unique, add the primary key to the cursor:

SELECT order_id, amount, created_at
FROM orders
WHERE merchant_id = 10086
  AND (
        created_at < '2026-05-20 10:30:15.123'
        OR (created_at = '2026-05-20 10:30:15.123' AND order_id < 'O202605200001')
      )
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

Supporting index: (merchant_id, created_at DESC, order_id DESC) This is production‑grade stable pagination.

9. Indexing for JOIN Scenarios – Don’t Focus Only on the Driving Table

Complex queries often become slow because the join side lacks proper indexes.

Example:

SELECT o.order_id, o.amount, oi.sku_id, oi.quantity
FROM orders o
JOIN order_item oi ON o.id = oi.order_id
WHERE o.merchant_id = ?
  AND o.created_at >= ?
ORDER BY o.created_at DESC
LIMIT 50;

Recommended indexes:

ALTER TABLE orders
ADD INDEX idx_merchant_created_id (merchant_id, created_at DESC, id);

ALTER TABLE order_item
ADD INDEX idx_order_id (order_id);

Principles:

Filter as many rows as possible on the driving table.

Ensure the join column on the driven table is indexed.

If sorting occurs after the join, keep the driving table result set small.

10. Ten High‑Frequency Index‑Failure Scenarios

Applying functions to indexed columns (e.g., DATE(created_at)) – index cannot be used.

Implicit type conversion (e.g., comparing a string column to a numeric literal) – may prevent index usage.

Leading wildcard LIKE '%xxx' – defeats B‑Tree index.

Skipping the leftmost column of a composite index.

Range condition on a leftmost column cuts off further index usage for later columns.

OR conditions causing index merge or full scan. SELECT * – even if the index is used, back‑to‑table cost can dominate.

Data skew leading to optimizer mis‑estimation.

Stale statistics after massive data changes.

Business access path changes while old indexes remain, making the index obsolete.

11. EXPLAIN Is Only the Beginning; EXPLAIN ANALYZE Shows the Truth

When troubleshooting slow SQL, follow this workflow:

Inspect the raw SQL and parameters.

Run EXPLAIN to see the optimizer’s plan.

Run EXPLAIN ANALYZE to observe actual execution metrics.

Compare with real data distribution.

Check Rows_examined in the slow‑log.

Key fields to watch in EXPLAIN ANALYZE output: type, key, rows, filtered, and whether Using index, Using filesort, or Using temporary appear. Discrepancies between estimated and actual rows indicate bad statistics or data skew.

12. Production Case Study: From Slow Query to Replicable Optimization

12.1 Business Scenario

A payment platform order center needs merchants to view recent orders, filter by status, paginate by time descending, and meet a P99 SLA of < 50 ms under a peak of 100 k QPS.

12.2 Original Table Schema

CREATE TABLE orders (
    id BIGINT NOT NULL AUTO_INCREMENT,
    order_id VARCHAR(32) NOT NULL,
    merchant_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    status TINYINT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    pay_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    source TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_id (order_id),
    KEY idx_merchant_id (merchant_id),
    KEY idx_status (status),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

12.3 Problematic SQL

SELECT order_id, amount, status, created_at
FROM orders
WHERE merchant_id = ?
  AND status IN (1,2)
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 20;

Analysis:

Three single‑column indexes are disjoint.

Sorting cannot reuse the filtering order.

Frequent back‑to‑table reads. status IN (1,2) is a hotspot, misleading the optimizer.

12.4 Optimization Plans

Plan 1 – Core Composite Covering Index

ALTER TABLE orders
ADD INDEX idx_merchant_status_created_cover (
    merchant_id,
    status,
    created_at DESC,
    order_id,
    amount
);

Plan 2 – Avoid Deep Pagination

SELECT order_id, amount, status, created_at
FROM orders
WHERE merchant_id = ?
  AND status IN (1,2)
  AND (
        created_at < ?
        OR (created_at = ? AND order_id < ?)
      )
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

Plan 3 – Online Gray‑Scale Verification

Create the new index with INPLACE and LOCK=NONE, then compare execution plans, Rows_examined, and P95/P99 latency before fully switching.

ALTER TABLE orders
ADD INDEX idx_merchant_status_created_cover (
    merchant_id,
    status,
    created_at DESC,
    order_id,
    amount
) ALGORITHM=INPLACE, LOCK=NONE;

Verification checklist:

Compare old vs. new execution plans.

Compare Rows_examined.

Monitor interface P95/P99 latency.

Observe replica lag.

Watch write latency impact.

12.5 Optimization Results

Significant latency reduction.

CPU usage drops.

Buffer‑pool miss rate declines.

Slow‑query proportion sharply falls.

Success is measured by actual performance metrics, not merely a prettier EXPLAIN output.

13. Production‑Grade Code Samples

13.1 Java – Cursor Pagination Query

public record OrderPageQuery(
    long merchantId,
    List<Integer> statuses,
    LocalDateTime cursorCreatedAt,
    String cursorOrderId,
    int pageSize) {}

@Repository
public class OrderRepository {
    private final NamedParameterJdbcTemplate jdbcTemplate;
    public OrderRepository(NamedParameterJdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    public List<OrderSummary> queryLatestOrders(OrderPageQuery query) {
        String sql = """
            SELECT order_id, amount, status, created_at
            FROM orders FORCE INDEX (idx_merchant_status_created_cover)
            WHERE merchant_id = :merchantId
              AND status IN (:statuses)
              AND (
                    :cursorCreatedAt IS NULL
                    OR created_at < :cursorCreatedAt
                    OR (created_at = :cursorCreatedAt AND order_id < :cursorOrderId)
                  )
            ORDER BY created_at DESC, order_id DESC
            LIMIT :pageSize
            """;
        MapSqlParameterSource params = new MapSqlParameterSource()
            .addValue("merchantId", query.merchantId())
            .addValue("statuses", query.statuses())
            .addValue("cursorCreatedAt", query.cursorCreatedAt())
            .addValue("cursorOrderId", query.cursorOrderId())
            .addValue("pageSize", query.pageSize());
        return jdbcTemplate.query(sql, params, (rs, rowNum) ->
            new OrderSummary(
                rs.getString("order_id"),
                rs.getBigDecimal("amount"),
                rs.getInt("status"),
                rs.getTimestamp("created_at").toLocalDateTime()
            )
        );
    }
}

Key production points:

Avoid deep pagination.

Return only needed columns.

Explicitly force the optimal index when necessary.

13.2 Go – Batch Primary‑Key Lookup to Avoid N+1

type OrderDetail struct {
    OrderID    string
    MerchantID int64
    Amount     float64
    Status     int
    CreatedAt  time.Time
}

func (r *OrderRepo) BatchGetByOrderIDs(ctx context.Context, orderIDs []string) ([]OrderDetail, error) {
    if len(orderIDs) == 0 {
        return []OrderDetail{}, nil
    }
    query, args, err := sqlx.In(`
        SELECT order_id, merchant_id, amount, status, created_at
        FROM orders
        WHERE order_id IN (?)
    `, orderIDs)
    if err != nil {
        return nil, err
    }
    query = r.db.Rebind(query)
    var rows []OrderDetail
    if err := r.db.SelectContext(ctx, &rows, query, args...); err != nil {
        return nil, err
    }
    return rows, nil
}

Use case: after a secondary‑index search returns a batch of IDs, fetch the full rows in one bulk query instead of looping.

13.3 Online DDL Script – Safe Large‑Table Index Addition

gh-ost \
  --host=10.0.0.12 \
  --port=3306 \
  --user=ghost \
  --password="${GHOST_PASSWORD}" \
  --database=order_center \
  --table=orders \
  --alter="ADD INDEX idx_merchant_status_created_cover (merchant_id, status, created_at DESC, order_id, amount)" \
  --max-load=Threads_running=32 \
  --critical-load=Threads_running=64 \
  --chunk-size=1000 \
  --execute

Best practices: use online DDL tools (gh‑ost or pt‑online‑schema‑change), avoid peak traffic, have a rollback plan, and monitor load.

14. Index Design After Sharding

14.1 Local Indexes Remain Important but Only Solve In‑Shard Problems

After sharding, each physical table still needs a local index, e.g., (merchant_id, status, created_at DESC). If a query omits the shard key, it will be broadcast to many shards, each performing its own scan, sort, and limit, then the middleware merges results – dramatically increasing cost.

14.2 Shard‑Key Selection Is Essentially Index Design

If the most common query filters by merchant_id and orders by created_at DESC, merchant_id is a natural shard key. Using a wrong shard key (e.g., user_id) forces cross‑shard scans for core merchant queries.

14.3 Global Unique Index vs. Global Search

In a sharded environment, a local UNIQUE index guarantees uniqueness only within a shard. Global uniqueness typically relies on snowflake IDs or a central ID service. Complex analytical searches are better offloaded to an external search system.

15. MySQL + Elasticsearch Secondary Index Architecture

When OLTP indexes are exhausted but the business still needs flexible, multi‑dimensional search, a typical pipeline is:

MySQL → Binlog → Canal / Debezium → Kafka → Elasticsearch

Query flow:

Complex condition → Elasticsearch → obtain order_id list.

Batch fetch full rows from MySQL using primary/unique key.

Critical financial fields remain authoritative in MySQL.

This separation keeps transactional consistency in MySQL while leveraging ES for full‑text, aggregation, and high‑dimensional queries.

16. Index Governance in Kubernetes / Cloud Environments

16.1 Storage‑Layer IOPS Limits

Even the best index cannot compensate for insufficient disk IOPS. Monitor cloud‑disk type, IOPS ceiling, throughput limits, and burst capability.

16.2 Buffer‑Pool vs. Container Memory

Do not blindly enable innodb_dedicated_server inside containers. Keep innodb_buffer_pool_size at 60‑70 % of the container memory limit, leaving room for connections, sorting, temporary tables, and OS cache.

16.3 Online DDL vs. Traffic Peaks

Adding an index on a large table causes I/O spikes, replica lag, binlog growth, and RT fluctuations. Schedule DDL during low‑traffic windows and use online tools.

16.4 Observability Gaps Lead to Repeated Index Issues

At minimum monitor:

Slow‑query count. Rows_examined. Handler_read%.

Replica lag.

Buffer‑pool hit rate.

Temporary‑table creation.

Threads_running.

DDL execution time.

17. Index Governance System – From Fire‑Fighting to Institutionalization

17.1 Closed‑Loop Slow‑SQL Management

Process:

Collect slow‑log.

Normalize and aggregate SQL.

Rank top‑N slow statements.

Auto‑generate execution plans.

Produce index recommendations.

Human review → gray‑scale rollout.

17.2 Index Asset Ledger

For each core index record store service owner, creation time, reason, owner/reviewer, observed metrics, and whether it can be retired.

17.3 Periodic Scan for Redundant / Unused Indexes

Example query:

SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'order_center'
  AND index_name IS NOT NULL
ORDER BY count_star ASC;

Note: “unused” does not automatically mean “droppable”; some indexes are used only in periodic jobs or fail‑over paths. Use invisible indexes for safe observation before deletion.

17.4 Move SQL Review Into Development Workflow

Code‑review checklist items:

Presence of deep pagination.

Use of SELECT *.

Function on indexed columns.

Missing shard key.

Potential full‑table scan.

Risk of large result‑set export.

Early detection is far cheaper than post‑incident remediation.

18. Practical Index‑Optimization Checklist

18.1 Pre‑Optimization

Clarify business goal and SLA.

Collect real parameters, not just the query template.

Run EXPLAIN ANALYZE.

Inspect table schema, index layout, row count.

Check recent data distribution.

Assess statistics freshness.

18.2 Design Phase

Confirm the query is OLTP‑type.

Identify equality, range, sorting, and return columns.

Prioritize composite indexes.

Decide on covering index feasibility.

Evaluate write‑side impact.

Consider SQL rewrite before adding indexes.

18.3 Release Phase

Select low‑traffic window.

Use online DDL tools for large tables.

Perform gray‑scale verification.

Compare before/after plans and response times.

Monitor replica lag and CPU.

18.4 Wrap‑Up Phase

Update statistics.

Document change record.

Publish index asset description.

Consider making the old index invisible first.

Observe for 24‑72 hours before final deletion.

19. Roadmap: When to Keep Optimizing vs. Upgrade Architecture

Index optimization is not infinite. Typical evolution stages:

Stage 1 – Single‑Instance

Focus on composite and covering indexes, SQL rewrite, pagination.

Suitable for tables up to tens of millions of rows.

Stage 2 – Read‑Write Splitting

Primary handles writes and strong reads; replicas serve read traffic.

Watch replication lag and statistic drift.

Stage 3 – Partition / Sharding

Design shard key aligned with query patterns.

Avoid cross‑shard pagination and sorting.

Stage 4 – External Search System

MySQL remains the transactional core.

Elasticsearch / OpenSearch / ClickHouse handle complex search and analytics.

CDC ensures eventual consistency.

Stage 5 – Distributed SQL Databases (TiDB, CockroachDB, etc.)

Adopt when data scales to billions, multi‑active‑active, and global secondary indexes become a necessity.

Rule of thumb: exhaust single‑node MySQL indexing and query tuning before moving to a new architecture.

20. Summary – Twelve Index Rules from an Architecture Perspective

Index optimization targets the access path, not merely the column.

One well‑ordered composite index beats many single‑column indexes.

Covering indexes are the most effective tool for high‑frequency reads.

Incorrect pagination or sorting cannot be rescued by adding indexes.

Deep pagination is an access‑pattern problem, not a configuration issue.

Every additional index adds write cost; balance read gains against write penalties. EXPLAIN shows estimates; EXPLAIN ANALYZE reveals reality.

Statistics, data distribution, and hotspot values directly affect optimizer decisions.

Report and complex analytical queries should not be forced onto OLTP indexes.

After sharding, index design and shard‑key selection become two sides of the same coin.

Before dropping an index, make it invisible and observe.

Mature teams treat index optimization as a continuous governance process, not a one‑off fix.