index

The concept of index

An index is a data structure maintained by a database to improve the efficiency of data retrieval. It is similar to a book catalog and can help the database reduce the number of data rows that need to be scanned.

Indexing can increase query speed, but it also takes up extra storage space and increases maintenance costs when adding, modifying, and deleting data. Therefore, the more indexes are not the better, and should be designed based on actual query conditions.

Common index types

primary key index

The primary key of a table automatically creates a primary key index. The primary key value must be unique and cannot be NULL.

unique index

Unique indexes are used to restrict column values from being duplicate. MySQL’s unique index usually allows multiple NULLs to appear, and the specific behavior also depends on the database version and column definition.

general index

Ordinary indexes are mainly used to improve query speed and are not responsible for ensuring data uniqueness.

compound index

A composite index consists of multiple columns. For example, when the index column order is job_id and salary, the following query conditions can usually be supported:

where job_id = ?

where job_id = ? and salary >= ?

However, when using only salary queries, the leftmost column of the composite index cannot be effectively utilized. This is the left-most prefix principle for composite indexes.

full-text index

Full-text indexing is used for text content retrieval. MySQL can combine MATCH() and AGAINST() for full-text query, and Chinese full-text search is often used in conjunction with ngram word segmentation.

Storage structure of index

MySQL’s InnoDB storage engine mainly uses B+ trees to organize ordinary indexes and primary key indexes.

B+ trees have the following characteristics:

• Data key values are organized in an orderly manner.
• The tree is low in height and is suitable for disk and paging storage.
• Ordered connections between leaf nodes, suitable for range queries.
• You can reduce the number of disk page accesses when querying.

Hash structures are good for equivalence lookups, but not for range queries and sorting. The regular index of InnoDB is not a regular hash index.

Create and delete indexes

Create a general index

create index employee_index1
on employee(first_name);

Create a composite index

create index employee_index2
on employee(job_id, salary);

creates a unique index

create unique index employee_index3
on employee(phone_number);

create a full-text index

alter table employee
add address varchar(255);

create fulltext index employee_index_addr
on employee(address)
with parser ngram;

Use full-text indexed queries

Natural language model query:

select address
from employee
where match(address) against('大庆');

Boolean mode query:

select address
from employee
where match(address)
against('+黑龙江 -大庆' in boolean mode);

delete the index

drop index employee_index1 on employee;

You can also delete indexes through ALTER TABLE:

alter table employee
drop index employee_index1;

Index differences between InnoDB and MyISAM

InnoDB

InnoDB uses clustered indexes to organize table data. The leaf nodes of the primary key index hold the complete row data, and the leaf nodes of the secondary index hold the primary key value.

When querying non-indexed columns through a secondary index, the database usually first obtains the primary key from the secondary index, and then finds the complete row through the primary key index. This process is often called table return.

If the table does not have an explicit primary key, InnoDB selects the appropriate unique non-null index, or creates internal hidden row identifiers to organize clustered indexes. Therefore, InnoDB tables should usually proactively design concise and stable primary keys.

MyISAM

MyISAM separates index and data files. The index leaf node stores the physical address of the data record. There is no difference in data positioning methods between the primary key index and the ordinary index between the InnoDB clustered index and the secondary index.

InnoDB supports transaction and row-level locking and is MySQL’s default storage engine. MyISAM does not support transactions, and modern business systems often use InnoDB first.

Use EXPLAIN to analyze queries

You can add EXPLAIN or DESC before the query statement to view the execution plan selected by the optimizer.

explain
select *
from employee
where first_name = 'Tom';

Common fields include:

Field	Description
type	Table access method, usually gradually improving
possible_keys	Possible Index
key	Actual selected index
key_len	Index length used
rows	Expected number of rows scanned
Extra	Additional execution information

Common values in type include ALL, index, range, ref, eq_ref, and const. In general, we should focus on ALL full table scanning on large tables, but we cannot judge whether SQL is reasonable based on just a single field.

eq_ref is commonly used in scenarios where connection conditions use primary keys or unique non-empty indexes. ref is common in scenarios where ordinary indexes are used for equivalent queries.

Slow query positioning

SQL optimization usually locates the problem first, analyzes the execution plan, and finally adjusts the SQL or index.

Open slow query log

Slow query logs can be set in the MySQL configuration file. Configuration file locations may differ for different systems and installation methods.

slow_query_log=ON
slow_query_log_file=WW-slow.log
long_query_time=10

After modifying the configuration, you usually need to restart the MySQL service or use the corresponding dynamic system variables. The production environment should set a reasonable threshold based on business conditions, rather than fixed use for ten seconds.

Analyze slow SQL

After locating the slow SQL, use EXPLAIN to check the index selection, join order, expected scan rows, and whether temporary tables or extra sorting appear.

SQL and index optimization principles

Reasonably design composite index

The column order of a compound index affects availability. Design should incorporate equivalence conditions, range conditions, sorting and grouping requirements rather than just looking at individual query fields.

Avoid unnecessary operations on index columns

The following conditions may make it difficult for ordinary indexes to directly locate:

where salary + 1000 > 8000

A more appropriate way to write it would be:

where salary > 7000

Avoid unnecessary functional processing of index columns

where year(create_time) = 2026

Can be rewritten to range query:

where create_time >= '2026-01-01'
and create_time < '2027-01-01'

Correct understanding of IN and EXISTS

IN is not necessarily slower than EXISTS, and the optimizer may convert the two into similar execution plans. Selection should be combined with data volume, index and EXPLAIN results rather than mechanical replacement.

Handle NULL with caution

When business implications permit, NOT NULL and reasonable default values can be used to simplify data processing. However, you cannot arbitrarily replace the real “unknown” state with meaningless values for index optimization.

Avoid meaningless full-column queries

Only querying columns needed by the business can reduce the amount of data transmitted and returned to tables by the network, and also have the opportunity to form an overlay index.

select employee_id, first_name
from employee
where first_name = 'Tom';

Select an index column with the right degree of discrimination

A large number of columns with duplicate values are indexed separately, and the benefits may be low. The prefix index is suitable for long strings, but the prefix length needs to be selected based on discrimination and query requirements.

create index employee_email_prefix
on employee(email(12));

Design indexes for joining, sorting, and grouping

Connected fields should have consistent data types and be indexed based on query frequency. Foreign key constraints and indexes are two concepts. Foreign keys are used to ensure referential integrity, and indexes are used to improve access efficiency. When InnoDB creates foreign keys, it requires that relevant columns have available indexes, but it cannot be simply understood as “if there are foreign keys, the query must be the fastest.”

Avoid blindly creating indexes

The following categories are usually not suitable for establishing a common index separately:

• A table with a small amount of data.
• Columns that are updated frequently and are rarely queried.
• Columns with extremely low discrimination and no other combined query value.
• Columns that never appear in query, join, sort, or group criteria.

Whether index is ultimately needed should be based on the real query and execution plan.