MySQL Index Fundamentals, Optimization Techniques, and Underlying Data Structures

Source: cnblogs.com/songwenjie/p/9410009.html

This article mainly discusses MySQL index knowledge, divided into three parts: index basics, practical index optimization, and the data structures behind database indexes.

1. MySQL Index Basics

First we introduce what an index is, the different types of indexes, how to create them, and basic design principles.

Example table structure used for testing index creation (user table):

1) What is an index?

“An index (also called a ‘key’) is a data structure that allows the storage engine to quickly locate records.” — *High Performance MySQL*

An index is a data structure that helps quickly match rows, similar to a store guide or a book's table of contents.

2. Index Types

You can view index details with SHOW INDEX FROM table_name; Primary Key (PRIMARY KEY) : a special unique index that does not allow NULL values. Usually created when the table is defined. Only one primary key per table.

Unique Index (UNIQUE) : column values must be unique, but NULL is allowed. For composite indexes, the combination must be unique. Create with

ALTER TABLE table_name ADD UNIQUE (column);

Create a composite unique index with

ALTER TABLE table_name ADD UNIQUE (column1, column2);

Ordinary Index (INDEX) : the most basic index without any restrictions. Create with

ALTER TABLE table_name ADD INDEX index_name (column);

Composite Index (INDEX) : an index that contains multiple columns, often used to avoid table look‑ups. Create with

ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3);

Full‑Text Index (FULLTEXT) : used for full‑text search, a key technology in search engines. Create with

ALTER TABLE table_name ADD FULLTEXT (column);

Indexes cannot be modified directly; they must be dropped and recreated. Drop with DROP INDEX index_name ON table_name; 3. Index Design Principles

Index columns that appear in WHERE clauses or join conditions.

Avoid indexing low‑cardinality columns; they provide little filtering benefit.

Use short indexes; for long string columns specify a prefix length to save space.

Do not over‑index; each index adds storage and slows write operations.

2. MySQL Index Optimization Practice

Before optimization we introduce two important concepts: cardinality and the filtered‑row percentage. Both are crucial for deciding whether an index will be used.

Test user table structure for optimization:

1) Cardinality : the number of distinct values in a column.

SELECT COUNT(DISTINCT name), COUNT(DISTINCT gender) FROM user;

The user table has 5 rows; gender has cardinality 2 (many duplicates), name has cardinality 5 (unique, effectively a primary key).

SELECT * FROM user;

Query rows where gender = 0 (male):

The filtered‑row percentage (shown as *filtered* in EXPLAIN) indicates that this query does **not** use an index.

Query rows where name = 'swj' **does** hit the index (index_name) and the filtered percentage is 100%.

Generally, if the filtered‑row percentage is below ~30%, the optimizer prefers the index; above that, a full table scan may be chosen.

Back‑table (row) lookup : after using an index, MySQL may need to fetch the full row via the rowid. Excessive back‑table lookups degrade performance, so the optimizer may switch to a full scan.

EXPLAIN shows Using Index (no back‑table) versus Using Where (requires back‑table).

2) Index Optimization in Practice

Check index usage with

SHOW STATUS LIKE 'Handler_read%';

Handler_read_key

is high when the index is actively used; Handler_read_rnd_next is high during full scans.

If MySQL estimates that using the index is slower than a full scan, it will skip the index.

Leading‑wildcard LIKE patterns (e.g., name LIKE '%s%') cannot use an index; a non‑leading pattern (e.g., name LIKE 's%') can.

Implicit type conversion prevents index usage; always quote string literals.

Composite indexes follow the left‑most principle: the query must reference the leftmost indexed column(s).

UNION, IN, and OR can use indexes, but IN is generally more efficient.

Negative conditions (!=, <>, NOT IN, NOT LIKE) usually cannot use indexes; rewrite as IN when possible.

Range conditions (<, <=, >, >=, BETWEEN) can use indexes, but only the first range column in a composite index is effective.

Functions on indexed columns (e.g., age+1 > 24) prevent index usage; compute such expressions in application code.

Covering indexes (where all selected columns are in the index) avoid back‑table lookups. Example: EXPLAIN SELECT status FROM user WHERE status=1; shows Using Index.

3) Additional Recommendations

Do not index columns that are frequently updated; index maintenance is costly.

Avoid indexing low‑cardinality columns (e.g., gender) unless the filtered percentage is low.

Use UNIQUE indexes for columns that must be unique, even in composite form.

Ensure join columns are indexed.

Avoid the misconception that “more indexes are always better.” Balance read‑performance gains against write overhead and storage.

3. Summary

When writing SQL, always run EXPLAIN to understand execution plans. Mastery of index concepts is a key skill for backend developers.

(End)