Master MySQL Performance: Indexes, Explain Plans, and Optimization Tips

Introduction

This article provides a practical guide to MySQL performance tuning, focusing on the architecture of MySQL, the role of indexes, how to interpret EXPLAIN output, and concrete optimization techniques.

1. MySQL Basic Architecture

MySQL consists of a client layer (command‑line, Workbench, Navicat, etc.) and a server layer that is split into the SQL processing layer and the storage‑engine layer. After a query is parsed, the optimizer generates an execution plan, the result set may be cached in the query cache, and finally the data are sent back to the client.

Key commands to inspect the environment:

SHOW ENGINES;

SHOW VARIABLES LIKE "%storage_engine%";

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;

2. SQL Optimization

Why Optimize SQL?

Complex joins, sub‑queries or large result sets can cause execution times of minutes or hours. Writing efficient SQL reduces resource consumption and improves user experience.

SQL Writing vs. Parsing Process

Writing order (the order you type the statement):

SELECT DISTINCT ... FROM ... JOIN ... ON ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

Parsing order (how MySQL rewrites the statement internally):

FROM ... ON ... JOIN ... WHERE ... GROUP BY ... HAVING ... SELECT DISTINCT ... ORDER BY ... LIMIT ...

For a detailed description of the parsing steps see: https://www.cnblogs.com/annsshadow/p/5037667.html

Indexes are the Core of Optimization

MySQL uses B‑Tree (specifically B+‑Tree) structures for most indexes. An index works like a dictionary, allowing rapid location of rows.

Creating common index types:

CREATE INDEX dept_index ON tb(dept);

CREATE UNIQUE INDEX name_index ON tb(name);

CREATE INDEX dept_name_index ON tb(dept, name);

How Indexes Work

Without an index MySQL performs a full table scan. With an index it traverses the B+‑Tree, dramatically reducing the number of row look‑ups.

Index Drawbacks

Indexes consume additional memory or disk space, especially on large tables.

Low‑cardinality columns, frequently updated columns, or columns rarely used in queries are poor candidates for indexing.

Indexes speed up reads but slow down INSERT/UPDATE/DELETE because both the table and the index must be modified.

Index Benefits

Reduced I/O and CPU usage for read‑heavy workloads.

Because index entries are stored in sorted order, ORDER BY can be satisfied without an extra sort step.

3. Index Classification and Creation

Index Types

Single‑value index (普通索引)

Unique index (唯一索引)

Composite index (复合索引)

Creating Indexes

SQL syntax:

CREATE INDEX index_name ON table_name(column1, column2, ...);

Alternative using ALTER TABLE:

ALTER TABLE table_name ADD INDEX index_name(column);

Primary keys are indexed automatically; they differ from unique indexes because primary keys cannot be NULL.

Dropping and Querying Indexes

DROP INDEX index_name ON table_name;

SHOW INDEX FROM table_name;

4. Exploring SQL Performance Issues

Manual tuning relies on EXPLAIN to view the optimizer's execution plan. The optimizer may also rewrite queries automatically, which can interfere with manual tuning.

Key Columns in EXPLAIN Output

id : step identifier

select_type : query type (SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, etc.)

table : table name

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

possible_keys : indexes that could be used

key : index actually used

key_len : length of the used index (helps determine whether a composite index is fully applied)

rows : estimated number of rows examined

Extra : additional information such as using filesort, using temporary, using index, using where,

impossible where

Typical EXPLAIN Scenarios

Simple SELECT EXPLAIN SELECT * FROM teacher; Derived (UNION) query

EXPLAIN SELECT cr.cname FROM (
    SELECT * FROM course WHERE tid=1
    UNION
    SELECT * FROM course WHERE tid=2
) cr;

Using Filesort

If the ORDER BY column differs from the indexed column, MySQL adds using filesort, which incurs an extra sorting step.

EXPLAIN SELECT * FROM test02 WHERE a1='' ORDER BY a1;

When ordering by a different column, using filesort appears:

EXPLAIN SELECT * FROM test02 WHERE a1='' ORDER BY a2;

Using Temporary

GROUP BY that requires a temporary table shows using temporary in the Extra column.

EXPLAIN SELECT a1 FROM test02 WHERE a1 IN ('1','2','3') GROUP BY a1;

Index Coverage ( using index )

If all columns referenced by the query are present in the index, MySQL can satisfy the query using only the index, avoiding a table lookup.

EXPLAIN SELECT a1, a2 FROM test02 WHERE a1='' AND a2='';

Using Where

When an index can satisfy part of the condition but additional columns are needed, MySQL adds using where to indicate a table lookup.

Impossible Where

If the WHERE clause is always false, the plan shows impossible where and returns no rows.

EXPLAIN SELECT a1 FROM test02 WHERE a1='a' AND a1='b';

5. Optimization Examples

Single‑Table Optimization

Create a table and a composite index, then query with conditions that match the index order. The optimizer will use the index efficiently regardless of the order of the predicates.

CREATE TABLE test03 (
    a1 INT NOT NULL,
    a2 INT NOT NULL,
    a3 INT NOT NULL,
    a4 INT NOT NULL
);
CREATE INDEX a1_a2_a3_test03 ON test03(a1, a2, a3);
EXPLAIN SELECT a3 FROM test03 WHERE a1=1 AND a2=2 AND a3=3;

Multi‑Table Optimization

When joining tables, place the smaller (driving) table on the left and index the join columns as well as any filter columns.

CREATE TABLE teacher2 (
    tid INT PRIMARY KEY,
    cid INT NOT NULL
);
CREATE TABLE course2 (
    cid INT,
    cname VARCHAR(20)
);
CREATE INDEX cid_teacher2 ON teacher2(cid);
CREATE INDEX cname_course2 ON course2(cname);
EXPLAIN SELECT *
FROM teacher2 t
LEFT JOIN course2 c ON t.cid=c.cid
WHERE c.cname='java';

Guidelines to Avoid Index Loss

Never apply functions, type casts, or arithmetic on indexed columns (e.g., authorid*2=1).

Avoid !=, <>, IS NULL, or OR on indexed columns; they often cause the index to be ignored.

Place range conditions ( IN, >, <) at the end of the predicate list to keep preceding indexes usable.

For composite indexes follow the left‑most prefix rule; do not skip columns or change their order.

Use LIKE 'prefix%' instead of LIKE '%pattern%' to allow index usage.

Prefer EXISTS when the sub‑query returns many rows, and IN when the outer query is large.

Order‑By Optimization

To avoid using filesort, order by columns that are part of the index and use the same sort direction (ASC/DESC). If the sort buffer is too small, MySQL may fall back to a double‑pass sort. The buffer size can be increased:

SET max_length_for_sort_data = 1024;

Additional Practical Tips

Read EXPLAIN output carefully; the type column indicates how efficiently rows are accessed.

Composite indexes should match the order of predicates in the WHERE clause.

If using index appears, the query is fully covered by the index and avoids table reads.

Regularly drop unused indexes to reduce write overhead and prevent optimizer confusion.

Systematic index design, a solid understanding of EXPLAIN fields, and incremental testing can lead to substantial MySQL performance gains.