Master MySQL Indexes: Types, Usage, and Performance Optimization

1. Index Overview

1.1 What is a MySQL Index

MySQL defines an index as a data structure that helps retrieve data efficiently.

Indexes are auxiliary structures that reference table rows, enabling fast lookups via advanced algorithms.

In simple terms, an index is a sorted structure that allows rapid data retrieval.

1.2 Index Data Structures

Common index structures in MySQL include B‑Tree (MyISAM ordinary index), B+Tree (InnoDB ordinary index), and Hash (Memory engine).

B‑Tree drawback: when inserting monotonically increasing values, the tree can degenerate into a linked list, reducing query efficiency.

1.3 Benefits of Indexes

Improve data retrieval speed and reduce I/O cost.

Enable sorting via indexes, lowering CPU consumption.

1.4 Drawbacks of Indexes

Indexes occupy space because they store primary keys and indexed columns.

Updates, inserts, and deletes must also modify index files, slowing write operations.

Each indexed column may need adjustment after data changes.

1.5 When to Use Indexes

Recommended scenarios:

Primary keys automatically create unique indexes.

Columns frequently used in WHERE clauses.

Foreign‑key columns used in joins.

Multi‑column queries benefit from composite indexes.

Columns used for ORDER BY can be indexed to speed sorting.

Columns used for GROUP BY or aggregation.

Not recommended:

Tables with very few rows.

Highly volatile tables with frequent writes.

Columns not referenced in query conditions.

2. Index Types

2.1 Primary Key Index

Creating a primary key automatically builds a primary key index.

ALTER TABLE table_name ADD PRIMARY KEY (column);
ALTER TABLE table_name DROP PRIMARY KEY;

2.2 Unique Index

Unique constraints generate a unique index.

ALTER TABLE table_name ADD UNIQUE index_name(column);
DROP INDEX index_name ON table_name;

2.3 Single‑Column Index

A single‑column index contains only one column; a table can have many of them.

ALTER TABLE table_name ADD INDEX index_name(column);
DROP INDEX index_name ON table_name;

2.4 Composite Index

A composite index includes multiple columns.

ALTER TABLE table_name ADD INDEX index_name(col1, col2);
DROP INDEX index_name ON table_name;

3. Performance Analysis

3.1 Common MySQL Bottlenecks

CPU bottleneck when comparing, joining, sorting, or grouping large datasets.

Insufficient memory leads to excessive physical I/O and full‑table scans.

3.2 Using EXPLAIN

EXPLAIN simulates the optimizer’s execution plan, revealing how MySQL processes a query.

Read order of tables.

Which indexes can be used.

Operation type for each table.

Indexes actually used.

Row estimates.

3.3 Important EXPLAIN Fields

Key fields include: SIMPLE: basic SELECT without subqueries or UNION. PRIMARY: outermost query when subqueries exist. DERIVED: subqueries in FROM clause (MySQL 5.7+). SUBQUERY: subqueries in SELECT or WHERE.

Other fields such as type (system, const, eq_ref, ref, range, index, all) indicate access efficiency, with the order from best to worst being system > const > eq_ref > ref > range > index > all. Aim for at least ref or range.

3.4 Additional Fields

possible_keys

: indexes that could be used. key: index actually used (NULL means none). key_len: length of the used index (larger is better). ref: column or constant used with the index. rows: estimated rows examined (smaller is better). extra: extra info such as Using filesort, Using temporary, Using index, Using where.

Avoid Using filesort whenever possible.

4. Query Optimization

4.1 Index Invalidations

Follow the left‑most prefix rule for multi‑column indexes.

Functions or calculations on indexed columns cause loss of index usage.

Range conditions cannot use columns to the right of the indexed range.

Inequality ( !=) often prevents index usage. IS NULL can use an index, but IS NOT NULL cannot.

Leading wildcards in LIKE disable the index.

Missing quotes around string literals break index usage.

Using OR may cause full‑table scans.

4.2 Composite Index Practice

4.3 Single‑Table Query Optimization

CREATE TABLE IF NOT EXISTS article (
  id INT PRIMARY KEY AUTO_INCREMENT,
  author_id INT NOT NULL,
  category_id INT NOT NULL,
  views INT NOT NULL,
  comments INT NOT NULL,
  title VARBINARY(255) NOT NULL,
  content TEXT NOT NULL
);

-- Example query
EXPLAIN SELECT id, author_id FROM article
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC LIMIT 1;

-- Add composite index
ALTER TABLE article ADD INDEX idx_ccv(category_id, comments, views);

-- Re‑run EXPLAIN after index creation

4.4 Join Optimization

In inner joins, MySQL drives the smaller result set; therefore, large tables should have indexes on the join columns. For left outer joins, the left table is scanned fully, so the right‑hand large table should be indexed.

4.5 Order‑By Optimization

Avoid Using filesort by ensuring ORDER BY columns are covered by an index.

Combine WHERE and ORDER BY conditions to satisfy the left‑most index prefix.

Range conditions in WHERE can invalidate ORDER BY indexes.

4.6 Group‑By Optimization

EXPLAIN SELECT COUNT(*), sname FROM students
WHERE sname = "小明" AND age > 22
GROUP BY score;

4.7 Slow Query Log

The slow query log records statements whose execution time exceeds long_query_time. Enable it with:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;  -- seconds
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time%';

Log files are stored under the MySQL data directory (e.g., /data/...-slow.log). Use the log to identify and tune long‑running queries, but be aware that enabling it adds some overhead.