Master MySQL Indexes: Boost Query Performance with Smart B+Tree Strategies

1. Overview

Indexes are data structures used by the storage engine to quickly locate records; proper use of database indexes can greatly improve system access performance. This article introduces MySQL index types and techniques for creating more reasonable and efficient indexes.

Note: The discussion focuses on InnoDB storage engine B+Tree index structures.

2. Advantages of Indexes

Significantly reduces the amount of data the server needs to scan, improving retrieval speed.

Helps the server avoid sorting and temporary tables.

Can turn random I/O into sequential I/O.

3. Creating Indexes

3.1 Primary Key Index

ALTER TABLE 'table_name' ADD PRIMARY KEY 'index_name' ('column');

3.2 Unique Index

ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column');

3.3 Ordinary Index

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column');

3.4 Full‑Text Index

ALTER TABLE 'table_name' ADD FULLTEXT 'index_name' ('column');

3.5 Composite Index

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

4. B+Tree Index Rules

Creating a test user table:

DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test(
  id int AUTO_INCREMENT PRIMARY KEY,
  user_name varchar(30) NOT NULL,
  sex bit(1) NOT NULL DEFAULT '1',
  city varchar(50) NOT NULL,
  age int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Creating a composite index:

ALTER TABLE user_test ADD INDEX idx_user(user_name, city, age);

4.1 Effective Query Patterns

4.1.1 Full‑Value Match

Matches all columns of the index, e.g., querying with conditions on user_name, city, and age simultaneously.

Note: The order of columns in the WHERE clause does not affect index usage.

SELECT * FROM user_test WHERE user_name = 'feinik' AND age = 26 AND city = '广州';

4.1.2 Leftmost Prefix Match

Matches the leftmost index columns, e.g., queries on (user_name), (user_name, city), or (user_name, city, age).

Note: The order of columns in the query does not need to follow the index column order as long as the leftmost prefix is satisfied.

4.1.3 Column Prefix Match

Matches the beginning of a column value, e.g., searching for usernames starting with "feinik".

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

4.1.4 Range Match

Using a range condition on the first index column prevents the use of subsequent columns in the index.

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

4.2 Index Limitations

If the WHERE clause does not contain the leftmost index column, the index cannot be used: SELECT * FROM user_test WHERE city = '广州'; Even if the leftmost column is present, a range condition disables use of later columns:

SELECT * FROM user_test WHERE user_name = 'feinik' AND city LIKE '广州%' AND age = 26;

5. Efficient Index Strategies

5.1 Index Columns Cannot Be Expressions or Function Arguments

SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');

5.2 Prefix Indexes

When a character column is very long, a prefix index can save space and improve efficiency by indexing only the first N characters.

5.2.1 Selectivity of Prefix Indexes

Calculate selectivity to choose an appropriate prefix length:

SELECT COUNT(DISTINCT index_column) / COUNT(*) FROM table_name; -- index_column is the column to be prefixed

Note: A higher selectivity ratio indicates a more efficient prefix index.

Another method evaluates multiple prefix lengths:

SELECT COUNT(DISTINCT LEFT(index_column,1))/COUNT(*),
       COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),
       COUNT(DISTINCT LEFT(index_column,3))/COUNT(*)
FROM table_name;

5.2.2 Creating a Prefix Index

ALTER TABLE table_name ADD INDEX index_name (index_column(length));

5.2.3 Cautions for Prefix Indexes

Prefix indexes cannot be used for ORDER BY, GROUP BY, or covering scans.

5.3 Choosing the Right Column Order

Place the most selective column first in a composite index; however, the final order should be based on actual query patterns.

5.4 Clustered vs. Non‑Clustered Indexes

5.4.1 Clustered Index

A clustered index determines the physical order of rows on disk. In InnoDB, the primary key is the clustered index. If no primary key exists, InnoDB chooses a unique non‑null index or creates an implicit one. Avoid non‑sequential keys like UUIDs, which cause fragmentation and random I/O.

5.4.2 Non‑Clustered Index

Non‑clustered indexes store only the index keys and pointers to the row data; they do not affect the physical row order.

5.5 Covering Indexes

If an index contains all columns needed by a query, it becomes a covering index, eliminating the need to read the table rows.

SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;

When the execution plan shows Using index in the Extra column, a covering index is in effect.

5.6 Using Indexes for Sorting

To let MySQL use an index for ORDER BY, two conditions must be met:

The ORDER BY column order must match the composite index column order, and all sort directions must be the same.

The selected columns must be included in the index (covering index).

Examples that use index sorting:

SELECT user_name, city, age FROM user_test ORDER BY user_name;
SELECT user_name, city, age FROM user_test ORDER BY user_name, city;
SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;
SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' ORDER BY city;

Note: If the WHERE clause contains a constant condition on the first index column, the index can still be used for sorting.

Examples that cannot use index sorting:

SELECT user_name, city, age FROM user_test ORDER BY user_name, sex; -- sex not in index
SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC; -- directions differ
SELECT user_name, city, age, sex FROM user_test ORDER BY user_name; -- sex not covered
SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'feinik%' ORDER BY city; -- range on first column prevents use of later columns

In multi‑table joins, index sorting works only when the ORDER BY columns are indexed in the first table and satisfy the two rules above.

6. Summary

This article covered B+Tree index structures, various index types, and practical techniques for creating efficient indexes to maximize query speed. Continuous learning and experience are essential for mastering additional indexing strategies.