Understanding MySQL Index Usage, Slow Queries, and Optimization Strategies

When developing business applications, it is essential to review whether a SQL statement hits an index; otherwise a full table scan can make the query extremely slow, especially on large tables.

Does hitting an index guarantee a fast query? The answer is no—index usage alone is not the sole indicator of performance.

Understanding the B+‑tree storage structure is a prerequisite. If you are unfamiliar, refer to the previous article on B+‑tree capacity.

1. Preparation: Create Table and Generate Data

Create a user table with a primary key id and a secondary index on user_name:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(128) NOT NULL DEFAULT '' COMMENT '用户名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `address` varchar(128) COMMENT '地址',
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (user_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

Insert 10,000 rows into the user table:

@GetMapping("/insert_batch")
public Object insertBatch(@RequestParam("batch") int batch) {
    for (int j = 1; j <= batch; j++) {
        List<User> userList = new ArrayList<>();
        for (int i = 1; i <= 100; i++) {
            User user = User.builder()
                .userName("Tom哥-" + ((j - 1) * 100 + i))
                .age(29)
                .address("上海")
                .build();
            userList.add(user);
        }
        userMapper.insertBatch(userList);
    }
    return "success";
}

2. Slow Query Definition

A MySQL slow‑query log records statements whose execution time exceeds long_query_time. The log is disabled by default ( slow_query_log=OFF).

MySQL’s slow‑query log records statements whose response time exceeds the threshold value, i.e., the execution time is greater than long_query_time .

Key parameters: slow_query_log: enable (1) or disable (0) the slow‑query log. log‑slow‑queries: path for the log file in MySQL 5.6‑ and earlier. slow‑query‑log‑file: path for the log file in MySQL 5.6+. long_query_time: threshold in seconds (default 10 s, often set to 1 s in production). log_queries_not_using_indexes: optionally log queries that do not use indexes.

Enable the log at runtime (effective only for the current instance): SET GLOBAL slow_query_log = 1; For permanent activation, modify my.cnf and restart MySQL.

3. Experiments

Run several EXPLAIN statements and observe the key and rows columns.

Full table scan: EXPLAIN SELECT * FROM user; → key is NULL, indicating no index.

Primary‑key lookup: EXPLAIN SELECT * FROM user WHERE id = 10; → key = PRIMARY.

Secondary index on user_name: EXPLAIN SELECT user_name FROM user; → key = idx_user_name.

Even when a secondary index is used, the rows column may still be close to the total row count (e.g., 9,968), meaning the query behaves like a full scan.

When the table grows to millions of rows, such scans become prohibitively slow and can exhaust connection pools under high concurrency.

4. Composite Index and Its Limitations

Add a composite index on user_name and age:

ALTER TABLE `user` ADD INDEX idx_user_name_age (`user_name`, `age`);

Run:

EXPLAIN SELECT * FROM user WHERE user_name LIKE 'Tom哥-1%' AND age = 29;

Because MySQL follows the left‑most prefix rule, the age part of the composite index is not used for the prefix match, leading to many “back‑table” lookups and poor performance.

5. Index Condition Push‑Down (ICP) Optimization

MySQL 5.6 introduced ICP, which evaluates additional conditions (e.g., age = 29) while scanning the secondary index, reducing the need for back‑table lookups.

Optimized execution flow:

Locate the first index entry matching the prefix Tom哥-1 in idx_user_name_age.

Immediately check age = 29 within the index entry; if it matches, return the row, otherwise discard.

Continue scanning the index until the user_name prefix no longer matches.

Return the result set.

This eliminates most back‑table operations and improves performance, though the left‑most prefix principle still applies.

6. Virtual Column for Better Prefix Matching

Create a generated virtual column that stores the first six characters of user_name and index it together with age:

ALTER TABLE `user` ADD user_name_first VARCHAR(12) GENERATED ALWAYS AS (LEFT(user_name,6)), ADD INDEX idx_user_name_first_age (user_name_first, age);

Now run:

EXPLAIN SELECT * FROM user WHERE user_name_first LIKE 'Tom哥-1%' AND age = 29;

The rows scanned drops dramatically, confirming the effectiveness of the virtual‑column approach.

7. Final Recommendations

Collect slow SQL statements via slow_query_log, analyze them with EXPLAIN, and focus on the number of rows scanned rather than merely whether an index is used. Use composite indexes, ICP, and virtual columns to improve complex query performance, while remembering that the left‑most prefix rule still governs index usability.

Note that some slow‑query log entries may be normal queries delayed by temporary resource contention.

Recommended Reading:

What Is a Bloom Filter? Solving High‑Concurrency Cache Penetration

Using Binlog for Cross‑System Data Synchronization

High‑Concurrency Service Optimization: Detailed RPC Call Process

Designing a High‑Performance Flash‑Sale System