Deep Dive into MySQL Query Optimizer: From Writing SQL to Controlling Performance

MySQL Query Execution Flow

SQL statement → Parser → Query Cache (if enabled) → Preprocessor → Optimizer → Execution Engine → Storage Engine → Result

Parser

Performs lexical analysis (tokenizing) and syntax analysis (building a parse tree). Example: SELECT * FROM users WHERE id = 1; Tokens: SELECT, *, FROM, users, ...

Preprocessor

Checks table and column existence, validates permissions, expands views, and performs semantic checks.

Optimizer (cost‑based)

Uses a cost model to evaluate multiple execution paths and selects the plan with the lowest estimated cost.

Cost‑Based Optimizer Decision Mechanism

Cost Model Basics

I/O cost : cost of reading data pages.

CPU cost : cost of processing rows.

Memory cost : cost of memory usage.

Cost Calculation

Total cost = I/O cost + CPU cost

I/O cost = number_of_data_pages_read × per_page_read_cost (default 1.0)

CPU cost = number_of_rows × per_row_processing_cost (default 0.2)

Optimizer Decision Process

Example query:

EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND order_date > '2024-01-01' ORDER BY total_amount DESC;

Steps:

Statistics : table row count (e.g., SHOW TABLE STATUS LIKE 'orders'), index selectivity, histograms (MySQL 8.0+).

Candidate Plans : use customer_id index, order_date index, composite index, or full table scan.

Cost Estimation : compute estimated cost for each candidate and pick the lowest.

Index Selection and Full‑Table Scan Judgment

Index Selectivity

Calculate selectivity:

SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;

Higher selectivity indicates a more effective index.

Multiple Conditions

When a query has several predicates, the optimizer:

Computes the filtering effect of each index.

Considers index_merge possibilities.

Chooses the plan with the lowest cost.

Full‑Table Scan Conditions

Very small tables (fewer data pages than an index scan).

Low‑selectivity predicates (match >20‑30% of rows).

No usable index for the predicates.

Explicit FORCE INDEX (PRIMARY) or when the optimizer estimates a lower cost for a scan.

Examples:

-- Small table full scan
SELECT * FROM small_table WHERE name LIKE '%test%';

-- Low selectivity (90% of products have a discount)
SELECT * FROM products WHERE discount > 0;

-- Optimizer chooses full scan based on cost
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

Index Failure Scenarios

1. Functions on indexed columns: WHERE YEAR(create_time) = 2024
2. Leading wildcard LIKE: WHERE name LIKE '%张'
3. Type conversion: WHERE id = '123' (id is integer)
4. Improper OR conditions
5. Functions on indexed columns

Optimizer Hints and Forced Indexes

Common Hint Syntax

-- Use a specific index
SELECT /*+ INDEX(table_name index_name) */ * FROM table_name WHERE ...;

-- Ignore an index
SELECT /*+ IGNORE INDEX(table_name index_name) */ * FROM table_name WHERE ...;

-- Force an index
SELECT /*+ FORCE INDEX(table_name index_name) */ * FROM table_name WHERE ...;

-- Set optimizer switch
SELECT /*+ SET_VAR(optimizer_switch='mrr=off') */ * FROM table_name;

Full Example with Hints

EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND status = 'shipped';

EXPLAIN SELECT /*+ INDEX(orders idx_customer_status), MAX_EXECUTION_TIME(1000) */ *
FROM orders WHERE customer_id = 100 AND status = 'shipped';

Force Index Practical Use

When the optimizer picks a sub‑optimal plan:

-- View current plan
EXPLAIN SELECT * FROM sales WHERE product_id = 123 AND sale_date BETWEEN '2024-01-01' AND '2024-01-31';

-- Force a better index
SELECT * FROM sales FORCE INDEX (idx_product_date) WHERE product_id = 123 AND sale_date BETWEEN '2024-01-01' AND '2024-01-31';

Optimizer Switch Control

-- Show current optimizer settings
SHOW VARIABLES LIKE 'optimizer_switch';

-- Temporarily modify behavior
SET SESSION optimizer_switch = 'index_merge=off,mrr_cost_based=off';

-- Common switches:
-- index_merge: index merge optimization
-- mrr: multi‑range read optimization
-- batched_key_access: batch key access
-- materialization: materialization optimization

Optimizer Monitoring and Debugging

Execution Plan Analysis Tools

-- Traditional EXPLAIN
EXPLAIN SELECT ...;

-- Extended JSON format (MySQL 5.6+)
EXPLAIN FORMAT=JSON SELECT ...;

-- Actual execution statistics (MySQL 5.6+)
EXPLAIN ANALYZE SELECT ...;

Optimizer Trace

SET optimizer_trace = "enabled=on";
SET optimizer_trace_max_mem_size = 1000000;
SELECT * FROM users WHERE ...;  -- run query
SELECT * FROM information_schema.optimizer_trace;  -- view trace
SET optimizer_trace = "enabled=off";

Performance Monitoring Queries

-- Index usage statistics
SELECT * FROM sys.schema_index_statistics;

-- Unused indexes
SELECT * FROM sys.schema_unused_indexes;

-- Statement performance summary
SELECT * FROM performance_schema.events_statements_summary_by_digest;