Why a Missing Index Parameter Crashed Our Production MySQL and How We Fixed It

Incident Overview

During a Thursday afternoon, the author was awakened by a colleague’s alarm that the production database CPU was saturated and read‑only alerts were firing for half an hour. The high‑traffic system shared a single MySQL server handling hundreds of thousands of daily active users.

Root Cause Investigation

Log analysis revealed a single SQL statement that performed full‑table scans on a table with millions of rows, executing more than ten thousand times in 30 minutes, each query taking over 3000 ms. The statement omitted the leftmost column of a composite index ( user_fruit_id), causing the index to be ignored and forcing a costly scan.

Temporary Mitigation

To restore service quickly, a new composite index was added that excluded user_fruit_id, allowing the heavy queries to use the index and reducing scanned rows dramatically. After restarting the instance, the system returned to normal.

KEY `idx_userfruitid_type` (`user_fruit_id`,`task_type`,`receive_start_time`,`receive_end_time`) USING BTREE

KEY `idx_task_type_receive_start_time` (`task_type`,`receive_start_time`,`receive_end_time`,`created_time`) USING BTREE

Understanding the Leftmost‑Match Principle

MySQL composite indexes are B+‑tree structures that can only be traversed using the leftmost column(s). If the leftmost column is missing from the WHERE clause, the index is not used, leading to full scans. The article illustrates this with a (name, age) example, showing that queries must include the leftmost field to benefit from the index.

Bug Identification in Application Code

The offending SQL originated from a Java method that accepted user_fruit_id as a parameter. The parameter was annotated with @NotNull but the controller lacked the @Validated annotation, so the null‑check never ran. Additionally, the code did not verify that the referenced foreign‑key row existed, allowing any value to trigger the costly query.

public class GardenUserTaskListReq implements Serializable {
    private static final long serialVersionUID = -9161295541482297498L;
    @ApiModelProperty(notes = "水果id")
    @NotNull(message = "水果id不能为空")
    private Long userFruitId;
    // ...
}

Lessons Learned

Never trust external callers; validate all critical parameters, even non‑null checks.

Profile SQL with EXPLAIN and add appropriate indexes for high‑volume tables.

Perform thorough code reviews to catch subtle bugs such as missing validation annotations.