Master MySQL Index Optimization: Deep Dive into EXPLAIN Execution Plans

Overview

MySQL’s query optimizer generates an execution plan for each statement, deciding which tables to access, which indexes to use, and in what order to join tables. Understanding the EXPLAIN output is essential for distinguishing beginner from advanced DBA skills.

MySQL 8.0/8.4 introduces several enhancements: richer EXPLAIN output (ANALYZE, JSON, TREE), new index types (descending, functional, invisible), and optimizer improvements such as Hash Join, Anti Join, derived‑table expansion, and Index Skip Scan.

Detailed Steps

1. Test Environment Setup

-- Create test database
CREATE DATABASE IF NOT EXISTS idx_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE idx_test;

-- Create orders table (e‑commerce scenario)
CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(32) NOT NULL COMMENT '订单号',
  user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  product_id BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
  amount DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '订单金额',
  quantity INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '数量',
  status TINYINT NOT NULL DEFAULT 0 COMMENT '状态:0待支付,1已支付,2已发货,3已完成,4已取消',
  payment_time DATETIME NULL COMMENT '支付时间',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- Create users and products tables (omitted for brevity)

2. Core EXPLAIN Syntax

EXPLAIN SELECT * FROM orders WHERE user_id = 1000;

Additional formats: EXPLAIN FORMAT=JSON SELECT ... – detailed JSON output. EXPLAIN FORMAT=TREE SELECT ... – tree‑style visualisation. EXPLAIN ANALYZE SELECT ... – executes the query and returns real timing and row counts (MySQL 8.0.18+).

3. EXPLAIN Output Fields

Key columns include:

id – execution order.

select_type – SIMPLE, PRIMARY, SUBQUERY, etc.

type – access type (most important, ordered from best to worst: system > const > eq_ref > ref > range > index > ALL).

possible_keys – candidate indexes.

key – actual index used (NULL means no index).

key_len – length of index used.

rows – estimated rows examined (smaller is better).

Extra – additional information such as Using index, Using where, Using temporary, Using filesort, etc.

4. Access‑type Deep Dive

Examples of each type: system – single‑row system table (fastest). const – primary‑key or unique‑index equality (max one row). eq_ref – join on primary/unique key. ref – non‑unique index equality (may return multiple rows). range – index range scan (>, <, BETWEEN, IN). index – full index scan (covers the index, no table lookup). ALL – full table scan (worst).

Practical Optimization Cases

1. Pagination on Large Result Sets

Original query suffers from deep offset:

SELECT * FROM orders WHERE user_id = 1000 ORDER BY created_at DESC LIMIT 10000, 10;

Two solutions:

Deferred join : first fetch primary keys, then join to retrieve full rows.

Cursor pagination (recommended) : remember the last created_at and id, then query with a (created_at, id) < (last_created_at, last_id) condition and a covering composite index idx_user_created_id (user_id, created_at DESC, id DESC). Execution time drops from hundreds of milliseconds to a few milliseconds.

2. Multi‑Table Join Optimization

Moving filter conditions from WHERE into the ON clause allows the optimizer to choose a better join order and use the composite index idx_user_status (user_id, status).

3. Index‑Failure Diagnosis

Common reasons for index not being used:

Implicit type conversion (e.g., comparing BIGINT column with a string).

Operations on indexed columns (arithmetic, functions).

LIKE patterns with leading wildcard.

OR conditions that prevent full index usage.

Solutions include rewriting the query, adding functional indexes, or using prefix indexes.

Best Practices and Caveats

Index Design Guidelines

Limit single‑table indexes to ≤5 to avoid write overhead.

Composite indexes should contain ≤5 columns; place high‑selectivity columns first.

Avoid redundant indexes; use tools like pt‑duplicate‑key‑checker or queries against information_schema.STATISTICS to detect them.

Use invisible indexes for safe testing before dropping.

Consider prefix indexes for long VARCHAR columns; compute selectivity to choose an optimal length.

SQL Writing Guidelines

SELECT only required columns to enable covering indexes.

Avoid functions on indexed columns; rewrite as range conditions.

Prefer EXISTS over IN for large subqueries.

Replace SELECT DISTINCT with GROUP BY when possible.

For pagination, use key‑based cursor rather than LIMIT offset, n.

Optimizer Hints

SELECT * FROM orders FORCE INDEX(idx_user_id) WHERE user_id = 1000;
SELECT * FROM orders IGNORE INDEX(idx_user_id) WHERE user_id = 1000;
/*+ JOIN_ORDER(u, o) */ SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
/*+ NO_HASH_JOIN(o, p) */ SELECT * FROM orders o JOIN products p ON o.product_id = p.id;
/*+ MAX_EXECUTION_TIME(1000) */ SELECT * FROM orders WHERE amount > 100;

Fault Diagnosis and Monitoring

Slow‑Query Log Analysis

# Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/data/mysql/logs/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

# Analyse with pt‑query‑digest
pt-query-digest /data/mysql/logs/slow.log --since '2024-01-15 00:00:00' --until '2024-01-15 23:59:59' --limit 20;

Real‑Time SQL Monitoring

SELECT id, user, host, db, command, time, state,
       LEFT(info, 100) AS query
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC;

Lock Wait Analysis

SELECT r.trx_id AS waiting_trx_id, r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx_id, b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;

Key Monitoring Metrics

Full table scans – Handler_read_rnd_next should be <5% of total reads.

Index utilization – Handler_read_key / total reads >95%.

Slow queries – Slow_queries <100 per minute.

Temporary tables – disk‑based temporary tables <10% of total temporary tables.

Prometheus Alerts (example)

# MySQLSlowQueries alert
- alert: MySQLSlowQueries
  expr: rate(mysql_global_status_slow_queries[5m]) > 10
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "MySQL slow queries exceed threshold"
    description: "Instance {{ $labels.instance }} has {{ $value }} slow queries per second."

Conclusion

Technical Takeaways

EXPLAIN is the foundation for index tuning; focus on type, key, rows, and Extra.

Follow the left‑most prefix rule when designing composite indexes.

Covering indexes eliminate bookmark lookups and improve performance.

Functional indexes (MySQL 8+) enable indexing of expressions.

EXPLAIN ANALYZE provides real execution statistics, not just estimates.

Use cursor‑based pagination to avoid deep‑offset penalties.

Further Learning

MySQL optimizer internals – cost estimation and join algorithms.

InnoDB index structures – B+‑tree, clustered vs secondary indexes.

Partitioned table indexing strategies.

FULLTEXT and GIS (R‑Tree) indexes.

References

MySQL 8.0 EXPLAIN Output Format documentation.

MySQL 8.0 Optimization guide.

High Performance MySQL, 4th Edition.

Use The Index, Luke.