Why a Missing Index Parameter Nearly Crashed Our Production Database

Incident Overview

During a routine lunch break the author was awakened by an alert that the production database server’s CPU was maxed out and a read‑only delay alarm had been triggered for half an hour. The high traffic system shared a single database instance serving hundreds of thousands of daily active users, so the outage threatened all services.

Root Cause: Missing Index Parameter

Log analysis revealed a single SQL statement that scanned over a million rows per execution and ran more than ten thousand times, each execution taking around 3000 ms. The query lacked a necessary user_fruit_id parameter, causing the composite index to be ineffective and forcing a full‑table scan.

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

To restore service quickly, a temporary composite index was added that omitted the missing field, allowing the high‑volume queries to use the new index.

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

Understanding MySQL Composite Indexes

MySQL composite indexes follow the “left‑most matching principle”. The index is built as a B+‑tree using the leftmost column(s) as the primary key. Only queries that specify the leftmost column can leverage the index; omitting it renders the index unusable.

Example: a composite index on (name, age) can be used for WHERE name='Zhang San' or WHERE name='Zhang San' AND age=10, but not for WHERE age=10 alone because the leftmost column name is missing.

Bug Details in Code

The offending SQL originated from a Java method where the user_fruit_id field was annotated with @NotNull but the controller method lacked the required @Validated annotation, so the validation never executed. Additionally, the code did not verify that the referenced user_fruit_id existed in the related table.

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

Because the validation was ineffective, any caller could pass a null or invalid user_fruit_id, causing the query to run without the index and leading to the CPU overload.

Key Takeaways

Never trust caller inputs. Validate all critical parameters, even non‑null ones, and reject invalid data before it reaches business SQL.

Test SQL performance. Use EXPLAIN on high‑volume queries and adjust indexes based on the execution plan.

Conduct thorough code reviews. Simple oversights like missing validation annotations can cause severe production incidents.