MySQL Index Types and Practical Guide: Principles, Classification, Application and Optimization

Main Functions of Indexes

1. WHERE Query: Index can quickly locate data rows matching conditions
2. ORDER BY Sorting: Use index’s ordering to directly obtain sorted results

Index Type Classification

By Storage Structure

1. BTree Index: Most common index type
2. Hash Index: Suitable for equi-joins
3. FULLTEXT Index: Used for full-text search
4. RTree Index: Used for spatial data indexing

By Application Layer

1. Regular Index: Most basic index type
2. Unique Index: Requires index column values to be unique
3. Primary Key Index: Special unique index, does not allow NULL values
4. Composite Index: Index established on multiple columns

By Data Storage

1. Clustered Index: Leaf nodes directly store row data
2. Non-clustered Index: Leaf nodes store primary key values

Regular Index

CREATE INDEX index_name ON table_name(column_name);

ALTER TABLE table_name ADD INDEX index_name(column_name);

CREATE TABLE table_name (
    column1 datatype,
    INDEX index_name (column_name)
);

Unique Index

CREATE UNIQUE INDEX index_name ON table_name(column_name);

ALTER TABLE table_name ADD UNIQUE (column_name);

Primary Key Index

CREATE TABLE users (
    user_id INT NOT NULL,
    PRIMARY KEY (user_id)
);

Composite Index

CREATE INDEX idx_name ON table_name(column1, column2);

-- Leftmost prefix principle
SELECT * FROM table WHERE column1 = 'xxx';
SELECT * FROM table WHERE column1 = 'xxx' AND column2 = 'yyy';

Full-text Index

ALTER TABLE wzk_user ADD FULLTEXT INDEX ft_username (username);

SELECT * FROM wzk_user WHERE match(username) against('wzk');