MySQL Architecture, Indexes, and SQL Optimization Guide

Many developers spend minutes analyzing data but hours waiting for results; focusing only on data output ignores SQL execution efficiency. This article covers three parts: an introduction to SQL, SQL optimization methods, and practical optimization examples.

1. MySQL Basic Architecture

1) MySQL architecture diagram

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

2) Viewing storage engines

show engines;

show variables like "%storage_engine%";

3) Specifying a table's storage 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

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

2) MySQL query compilation process

① Writing phase

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

② Parsing phase

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

A detailed explanation of the parsing process is available at this site .

3) Indexes are the core of SQL optimization

An index works like a dictionary: it allows fast location of rows. MySQL mainly uses B+ trees for indexes.

4) Index types

Single‑column index

Unique index

Composite (multi‑column) index

5) Creating indexes

Syntax: create index index_name on table(column); Examples:

create index dept_index on tb(dept);

create unique index name_index on tb(name);

create index dept_name_index on tb(dept, name);

Alternative method using ALTER:

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);

6) Deleting and querying indexes

drop index name_index on tb;

show index from tb;

3. SQL Performance Analysis with EXPLAIN

Using explain <SQL> shows the execution plan, including several important columns:

id : execution order; lower id runs first.

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

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

possible_keys : indexes that could be used.

key : the actual index used.

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

rows : estimated rows examined.

Extra : additional info such as using filesort, using temporary, using index, using where, etc.

Common observations: using filesort appears when the ORDER BY column differs from the indexed column. using temporary often occurs with GROUP BY on columns without suitable indexes. using index (index covering) means the query can be satisfied from the index alone, avoiding table reads. using where indicates a need to read the base table after using an index.

Optimization Examples

Single‑table optimization

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

Query without indexes:

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

Result shows type=ALL and Extra=using filesort, indicating a full table scan.

Index‑driven optimization

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

After adding the composite index, the plan still shows using where because the IN clause can cause the second column to be ineffective. Reordering the index columns often helps:

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

Placing the IN column last can preserve index usage.

Multi‑table joins

When joining tables, drive the larger table with the smaller one and add indexes on the join columns. Example:

create index cid_teacher2 on teacher2(cid);

create index cname_course2 on course2(cname);

Using these indexes eliminates full scans and reduces Extra=Using join buffer.

Common Index Pitfalls

Do not use functions, calculations, or type casts on indexed columns; they invalidate the index.

Avoid !=, <>, IS NULL, or range conditions ( >, <) on the leftmost column of a composite index, as they can cause the remaining columns to be ignored.

Do not use OR across different indexed columns; it often forces a full scan.

For LIKE patterns, place a constant prefix (e.g., 'x%') to allow index usage; leading wildcards ( '%x%') prevent it.

Following these principles—using the best left‑most prefix, keeping index definition order consistent with query order, and removing unused indexes—helps achieve optimal query performance.