Indexes are a fundamental tool in MySQL for improving query performance, much like the table of contents in a book—indexes allow quick access to target data without scanning the entire table. This article covers core concepts, types, usage principles, and best practices from basic to advanced levels.

Core Concepts of Indexes

1. Purpose of Indexes

• Speed up queries: The primary function, avoiding full table scans (Full Table Scan).
• Optimize sorting/grouping: Indexes are inherent ordered, allowing ORDER BY/GROUP BY operations without additional sorting.
• Ensure uniqueness: Primary key indexes and unique indexes guarantee data uniqueness.

2. Costs of Indexes

• Increased write time: Inserting/updating/deleting data requires maintaining indexes (e.g., B+ tree splits/merges).
• Disk space consumption: Indexes are separate physical structures that take up storage space.

Common Index Types in MySQL

1. By Data Structure (Implementation)

The main storage engine (InnoDB) uses B+ Tree Indexes by default, while other structures are used in specific scenarios:

2. By Function/Syntax (Common)

(1) Primary Key Index (PRIMARY KEY)

• A table can have only one primary key index, which is non-null and unique by default.
• In InnoDB, the leaf nodes of the primary key index store the full row data (clustered index), serving as the basis for all other indexes.

-- Define a primary key during table creation
CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20),
  PRIMARY KEY (id) -- Primary key index
);

(2) Unique Index (UNIQUE)

• Ensures values in the indexed column are unique (NULLs allowed, multiple NULLs do not conflict), with multiple such indexes per table.

-- Create a unique index separately
CREATE UNIQUE INDEX idx_user_phone ON user (phone);

(3) Normal Index (INDEX)

• Basic index without uniqueness constraints, used solely for accelerating queries, with multiple such indexes possible.

-- Add a normal index after table creation
ALTER TABLE user ADD INDEX idx_user_name (name);

(4) Composite Index (Combined Index)

• An index based on multiple fields, following the 'leftmost prefix' principle (query conditions must match the left order of index fields).

-- Create a composite index: name + age
CREATE INDEX idx_user_name_age ON user (name, age);
-- Valid query (matches leftmost prefix): WHERE name = 'John'
-- Valid query: WHERE name = 'John' AND age = 30
-- Invalid query (does not match leftmost): WHERE age = 30

Index Usage Principles (Avoid Pitfalls)

1. Suitable Scenarios for Creating Indexes

• Frequently queried fields (e.g., in WHERE/JOIN/ORDER BY).
• High distinct value fields (e.g., phone numbers, not gender—which has only two values, leading to low index efficiency).
• Prefer composite endexes over multiple single-column indexes (reduce maintenance cost).

2. Unsuitable Scenarios for Creating Indexes

• Tables with small data volume (full table scan may be faster).
• Tables with more write operations than read operations (e.g., log tables, where maintaining indexes slows down writes).
• Fields with high duplicate values (e.g., status fields: 0/1/2).
• Fields with high NULL ratio (B+ trees handle NULLs inefficiently).

3. Common Cases Where Indexes Fail

1. Indexed columns involved in functions/operations (e.g., WHERE id + 1 = 10).
2. Using LIKE '%xxx' (fuzzy match starting with %).
3. Using OR to connect non-indexed fields (e.g., WHERE name = 'John' OR address = 'Beijing', if address has no index).
4. Composite indexes not meeting the leftmost prefix rule.
5. Implicit type conversion (e.g., indexed column is INT, query uses string: WHERE id = '123').

Common Index Operations

1. Creating Indexes

-- Method 1: CREATE INDEX
CREATE [UNIQUE] INDEX index_name ON table_name (column1[, column2...]);

-- Method 2: ALTER TABLE
ALTER TABLE table_name ADD [UNIQUE/PRIMARY] INDEX index_name (column1[, column2...]);

2. Viewing Indexes

-- View all indexes of a table
SHOW INDEX FROM table_name;
-- Short form
SHOW INDEXES IN table_name;

3. Dropping Indexes

-- Method 1: DROP INDEX
DROP INDEX index_name ON table_name;

-- Method 2: ALTER TABLE
ALTER TABLE table_name DROP INDEX index_name;
-- Drop primary key (special case)
ALTER TABLE table_name DROP PRIMARY KEY;

4. Verifying Index Effectiveness (EXPLAIN)

Use EXPLAIN to check the query execution plan, focusing on the type column (from best to worst: const > eq_ref > ref > range > ALL) and the key column (showing the actual index used).

-- Example: Check if an index is being used
EXPLAIN SELECT * FROM user WHERE name = 'John';
-- If key shows `idx_user_name`, the index is effective; if key is NULL, the index is ineffective.

Summary

1. Core function: Indexes use B+ Trees to speed up queries but increase write time and storage costs, requiring careful evaluation.
2. Key principle: Composite indexes follow the 'leftmost prefix' rule, avoiding index failure (e.g., function operations, %xxx fuzzy matches).
3. Best practice: Create indexes only for frequently queried, high-distinctness fields, and use EXPLAIN to verify index effectiveness.