Comprehensive Guide to MySQL Architecture, SQL Optimization Techniques, and Explain Plan Analysis

1. MySQL Basic Architecture

MySQL consists of a client layer (e.g., command‑line, Workbench, Navicat) and a server layer that is divided into the SQL layer and the storage‑engine layer. After a query is executed, results are cached in the query cache and then returned to the client.

1.1 Show Engines

Use show engines; to list available storage engines.

1.2 Show Storage Engine Variable

Use show variables like "%storage_engine%"; to view the default engine.

1.3 Create Table with Engine

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

2. SQL Optimization

Optimization is needed when complex joins or sub‑queries cause long execution times. Understanding both manual tuning and the MySQL optimizer’s automatic rewrites is essential.

2.1 Why Optimize SQL?

Poorly written queries can lead to hours of execution for analyses that should take minutes.

2.2 Writing and Parsing Process

The writing process follows

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

. The parser reverses this order during execution.

2.3 Index Optimization

Indexes are the most critical factor for performance. They act like a dictionary, allowing fast record location.

What is an Index? An index is a data structure (usually a B+ tree) that speeds up data retrieval.

2.3.1 Types of Indexes

Single‑column index

Unique index (no duplicate values)

Composite index (multiple columns, e.g., (name, age))

2.3.2 Creating Indexes

Syntax:

create index index_name on table(column);

create index dept_index on tb(dept);

Alternative using ALTER:

alter table tb add index dept_index(dept);

2.3.3 Dropping and Viewing Indexes

drop index name_index on tb;

show index from tb;

3. EXPLAIN Plan Keywords

The EXPLAIN command reveals how MySQL will execute a query. Important columns include: id: step identifier select_type: query type (simple, primary, subquery, derived, union) 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 ref: columns referenced rows: estimated rows examined Extra: additional info (e.g., using filesort, using temporary, using index)

3.1 Using Filesort

Occurs when the ORDER BY column differs from the indexed column; avoid by ordering on the same indexed column.

3.2 Using Index

Indicates a covering index where MySQL can retrieve all needed columns from the index without accessing the table.

3.3 Using Temporary

Appears for GROUP BY or ORDER BY operations that require a temporary table.

4. Practical Optimization Examples

4.1 Composite Index Order

Match the order of columns in the WHERE clause with the order defined in the composite index to avoid index skipping.

create index idx_a1_a2_a3 on test02(a1,a2,a3);

4.2 Single‑Table Optimization

For a table book, an optimal index for the query WHERE typeid IN (2,3) AND authorid=1 ORDER BY typeid DESC is:

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

Place the equality condition first (authorid) and the range condition (typeid) later to keep the index usable.

4.3 Two‑Table Join Optimization

Use the smaller table to drive the join and index the join column on the driving table: create index cid_teacher2 on teacher2(cid); Then add an index on the filtered column of the other table:

create index cname_course2 on course2(cname);

4.4 Avoiding Index Failure

Do not apply functions or arithmetic on indexed columns (e.g., authorid*2 = 1).

Avoid !=, <>, IS NULL, or OR on indexed columns as they can invalidate the index.

Range conditions ( >, <, IN) cause right‑hand side indexes in a composite index to be ignored.

Use leading constants with LIKE (e.g., LIKE 'abc%') instead of leading wildcards.

Prefer EXISTS over IN when the sub‑query returns many rows, and vice‑versa.

5. Summary of Best Practices

Keep index column order consistent with query predicates (best‑left‑prefix).

Prefer covering indexes to eliminate table lookups.

Place equality conditions before range conditions in composite indexes.

Index columns used in JOIN, WHERE, ORDER BY, and GROUP BY clauses.

Regularly review and drop unused indexes to avoid optimizer confusion.