Master MySQL Performance: Deep Dive into Indexes, Explain Plans, and Optimization Techniques

1. MySQL Basic Architecture

MySQL consists of a client layer (e.g., CMD, WorkBench, Navicat) and a server layer that separates the SQL layer from the storage engine layer. Query results are cached and returned to the client, reducing repeated execution time.

2. SQL Optimization Overview

Optimization focuses on reducing execution time for complex joins and subqueries. Techniques include analyzing execution plans with EXPLAIN, improving index usage, and understanding how the optimizer may rewrite queries.

2.1 Why Optimize SQL?

Poorly written queries can cause server execution to take minutes or hours; optimizing improves response time and resource usage.

2.2 MySQL Parsing Process

select ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

3. Index Fundamentals

Indexes act like a dictionary, allowing fast record location. MySQL primarily uses B+‑tree structures.

3.1 Types of Indexes

Single‑column index

Unique index

Composite (multi‑column) index

3.2 Creating Indexes

CREATE INDEX idx_name ON tb(col1);

ALTER TABLE tb ADD INDEX idx_name (col1, col2);

4. Using EXPLAIN to Diagnose Queries

Key columns in the output:

id : query step identifier

select_type : query type (simple, primary, subquery, derived)

type : access method (system, const, eq_ref, ref, range, index, ALL)

key : actual index used

possible_keys : indexes that could be used

rows : estimated rows examined

Extra : additional info (e.g., Using filesort, Using temporary, Using index)

4.1 Common EXPLAIN Keywords

Using filesort : extra sorting required, often caused by ORDER BY on columns not covered by an index.

Using temporary : a temporary table is created, typical for GROUP BY.

Using index : index covering; the query can be satisfied using only the index without accessing the table.

5. Practical Optimization Examples

5.1 Single‑Table Optimization

When filtering by multiple columns, create a composite index that matches the query order (e.g., CREATE INDEX idx_ab ON tbl(a,b);). Avoid using functions, LIKE '%x%', or range conditions on the leftmost indexed column, as they can invalidate the index.

5.2 Multi‑Table Joins

Use the “small table drives large table” principle: place the smaller table on the left side of the join and index the join columns. Example:

CREATE INDEX idx_teacher_cid ON teacher2(cid);

EXPLAIN SELECT * FROM teacher2 t LEFT JOIN course2 c ON t.cid=c.cid WHERE c.cname='java';

5.3 Avoiding Index Pitfalls

Do not apply functions or type casts to indexed columns in WHERE clauses.

Do not use OR across indexed columns; it often disables all involved indexes.

Prefer = or IN with a constant prefix for LIKE patterns.

Range conditions ( >, <, BETWEEN, IN) on the leftmost column of a composite index can cause the remaining columns to be ignored.

6. Summary of Best Practices

Design indexes that follow the query’s left‑most prefix order.

Use composite indexes when multiple columns are filtered together.

Analyze queries with EXPLAIN and look for “Using index” or “Using filesort”.

Keep the query simple: avoid functions, casts, OR, and leading wildcards in LIKE.

For joins, place the smaller table first and index the join columns.