Why a Missing Index Parameter Crashed Our Production DB—and How to Fix It

Production Outage Triggered by a Heavy SQL Query

While monitoring the production environment, the team noticed that the database server CPU was fully utilized and a read‑only alert was triggered for half an hour. The cause was a single SQL statement that performed a full‑table scan, scanning millions of rows and executing more than ten thousand times, each execution taking over 3000 ms. This massive load brought the entire system, serving hundreds of thousands of daily active users, to the brink of collapse.

Temporary Index Fix

Because the problematic SQL was written by the author, they quickly identified that the query missed the user_fruit_id parameter, which is the leftmost column of a composite index. To mitigate the issue without redeploying code, they added a new composite index that omitted user_fruit_id, allowing the high‑volume queries to use the new index.

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

After applying the new index, the number of scanned rows dropped dramatically and the service recovered after a restart.

Understanding MySQL’s Left‑Most Matching Principle

MySQL composite indexes are built on the B+‑tree structure and follow the “left‑most matching principle”. Only the leftmost columns of the index can be used for lookups. If a query does not include the leftmost column, the index cannot be applied, resulting in a full‑table scan.

For example, with a composite index on (name, age):

Querying WHERE name = 'Zhang San' can use the index to locate all matching rows.

Querying WHERE name = 'Zhang San' AND age = 10 can use both columns.

Querying only WHERE age = 10 cannot use the index because the leftmost column name is missing.

The original problematic SQL omitted user_fruit_id, the leftmost column of its composite index, causing the query to fall back to a full scan.

Root Cause Bug in the Application Code

The SQL was generated from a Java request object GardenUserTaskListReq. Although the field userFruitId was annotated with @NotNull, the controller method lacked the @Validated annotation, so the validation never ran. Additionally, the code did not verify whether the referenced user_fruit_id existed in the related table.

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

Because the validation was ineffective, any incoming request could trigger the heavy query, and without a proper existence check, the database performed a full scan each time.

Lessons Learned

Never trust external callers: Validate all critical parameters, even non‑null values, and reject invalid requests before they reach the database.

Profile SQL performance: Use EXPLAIN on high‑volume queries and ensure appropriate indexes are in place.

Enforce code reviews: Regular reviews can catch simple mistakes like missing validation annotations that could lead to severe production incidents.

A seemingly small oversight in parameter validation caused a cascade of performance problems, underscoring the importance of rigorous validation, proper indexing, and diligent code review in high‑traffic systems.