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

Introduction

Many developers spend minutes analyzing data but hours waiting for query results. Efficient SQL not only returns correct data but also executes quickly. This article covers three parts: SQL basics, SQL optimization methods, and concrete optimization examples.

MySQL Basic Architecture

1) MySQL architecture diagram

The left side represents various clients (CMD, WorkBench, Navicat, etc.). The right side is the MySQL server, split into the SQL layer and the storage‑engine layer. After a query retrieves data, the executor writes results to the query cache and returns them to the client.

2) Viewing storage engines show engines; 3) Viewing engine variables show variables like "%storage_engine%"; 4) Creating a table with a specific engine

create table tb(
    id int(4) auto_increment,
    name varchar(5),
    dept varchar(5),
    primary key(id)
) engine=myISAM auto_increment=1 default charset=utf8;

SQL Optimization

1) Why optimize SQL? Poorly written multi‑table joins or sub‑queries can cause long server execution times. Learning optimization reduces wait time.

2) MySQL compilation and parsing process

a) Compilation

select distinct … from … join … on … where … group by … having … order by … limit …

b) Parsing

from … join … on … where … group by … having … select distinct … order by … limit …

Reference: https://www.cnblogs.com/annsshadow/p/5037667.html

3) Indexes are the key to optimization

An index works like a dictionary’s table of contents, allowing fast location of rows.

① What is an index? An index is a data structure (usually a B+ tree) that helps MySQL retrieve rows efficiently.

② Visualizing an index (binary tree example)

In a B+ tree, leaf nodes store the actual rows; internal nodes store pointers that guide the search.

④ Index drawbacks

Large data sets make indexes large, consuming memory or disk space.

Not suitable for small tables, frequently updated columns, or rarely used columns.

Indexes speed up reads but slow inserts/updates because both the table and the index must be modified.

⑤ Index advantages

Reduced I/O and faster query execution.

Lower CPU usage for ordered queries because the index itself is sorted.

B+ Tree Details

MySQL uses B+ trees for indexes. All data resides in leaf nodes; upper levels contain only pointers.

Index Types and Creation

1) Index categories

Single‑column index

Unique index

Composite (multi‑column) index

① Single‑column index Each column can have its own index; a table may have many such indexes.

② Unique index Guarantees column values are unique (e.g., primary key, student ID).

③ Composite index Combines multiple columns (e.g., (name, age)). The leftmost column must be used for the index to be effective.

2) Creating indexes

a) CREATE INDEX syntax create index idx_name on tb(column); b) First method – CREATE INDEX

create index dept_index on tb(dept);

create unique index name_index on tb(name);

create index dept_name_index on tb(dept, name);

c) Second method – ALTER TABLE … ADD INDEX

alter table tb add index dept_index(dept);

alter table tb add unique index name_index(name);

alter table tb add index dept_name_index(dept, name);

If a column is a PRIMARY KEY, MySQL automatically creates a primary‑key index. Primary key and unique index differ: primary key cannot be NULL, unique index can.

3) Deleting and querying indexes

a) Delete index drop index name_index on tb; b) Show indexes

show index from tb;

Understanding EXPLAIN Output

EXPLAIN helps you see how MySQL will execute a query.

Key columns

id : execution order; lower id runs first.

select_type : query type (simple, primary, subquery, derived, union, etc.).

type : access type (system, const, eq_ref, ref, range, index, ALL). Optimizing aims to reach ref or range .

possible_keys : indexes MySQL could use.

key : index actually used.

key_len : length of the used index (helps judge if a composite index is fully used).

rows : estimated rows examined.

Extra : additional info (using filesort, using temporary, using index, using where, impossible where, etc.).

Examples:

explain select * from teacher;

When using filesort appears, MySQL must sort rows separately (often due to ORDER BY on a column not covered by the index). To avoid it, keep ORDER BY columns aligned with the index’s leftmost prefix.

When using temporary appears, MySQL creates a temporary table (common with GROUP BY on columns not in the index).

using index indicates a covering index – the query can be satisfied from the index alone, avoiding a table lookup.

using where means the optimizer still needs to read the table after using an index (partial index coverage).

Common pitfalls

Do not apply functions or type casts on indexed columns (e.g., where tname = 123 on a VARCHAR column) – this disables the index.

Avoid leading wildcards in LIKE patterns ( LIKE "%x%") because they prevent index use.

Using OR across different columns often forces a full table scan.

Range conditions (>, <, IN) on a composite index cause the right‑hand columns to become ineffective.

Practical Optimization Examples

1) Composite index order matters . Query where a1=1 and a2=2 and a3=3 benefits from an index on (a1, a2, a3). Reordering the WHERE clause does not hurt because the optimizer can reorder predicates.

2) Single‑table optimization

create table book(
    bid int primary key,
    name varchar(20) not null,
    authorid int not null,
    publicid int not null,
    typeid int not null
);

Query:

select bid from book where typeid in (2,3) and authorid=1 order by typeid desc;

Initial plan shows ALL and using filesort . Adding a composite index matching the WHERE order improves it:

create index idx_book_type_author_bid on book(typeid, authorid, bid);

After the index, the plan uses range and eliminates filesort.

3) Two‑table join optimization

create table teacher2(tid int primary key, cid int);
create table course2(cid int, cname varchar(20));
explain select * from teacher2 t left join course2 c on t.cid=c.cid where c.cname='java';

Adding an index on teacher2.cid and on course2.cname lets MySQL use index lookups instead of full scans.

4) Three‑table join follows the same principle: drive the join with the smaller table and index the join columns.

General Recommendations

Follow the “best leftmost prefix” rule for composite indexes.

Keep index definition order consistent with query predicate order.

Remove unused indexes to avoid optimizer confusion.

Place range or IN conditions at the end of a composite index.

Prefer IN for small sets; use EXISTS when the sub‑query returns many rows.

Adjust max_length_for_sort_data and buffer sizes to influence single‑ vs double‑pass sorting.

Overall, understanding MySQL’s execution flow, proper index design, and careful use of EXPLAIN can dramatically improve query performance.