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. 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 NULL, the specific behavior is also related to 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, the index column order is job_id、salary When, the following query conditions can usually be supported:

where job_id = ?

where job_id = ? and salary >= ?

but using only salary When querying, the leftmost column of the composite index is usually not 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 be combined MATCH() and AGAINST() Conduct full-text query, often combined with Chinese full-text search ngram Use the 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.
• Leaf nodes are connected in an orderly manner, which is suitable for range queries.
• You can reduce the number of disk page accesses when querying.

Hash structures are suitable for equivalent lookup, but not suitable for range querying and sorting. Regular indexes for InnoDB are not ordinary hash indexes.

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;

can also ALTER TABLE Delete index:

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 analytic query

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

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

Common fields include:

type Common values include ALL、index、range、ref、eq_ref、const。Generally speaking, we should focus on the big list ALL Scan the entire table, but you cannot judge whether SQL is reasonable based on just a single field.

eq_ref Common scenarios where the connection condition uses a primary key or a unique non-null index.ref It is common in scenarios where a common index is 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 slow SQL, use EXPLAIN Check index selection, join order, expected scan rows, and whether temporary tables or additional 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 IN with EXISTS

IN not necessarily EXISTS Slow, the optimizer may convert both into similar execution plans. Data volume, indexing and EXPLAIN The result is chosen rather than mechanically replaced.

careful handling NULL

When the business meaning permits, you can use NOT NULL and reasonable default values 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.