Abnormal SQL Query Caused CPU Saturation: Diagnosis and Fix with Index Optimization

During a routine check the team noticed the production database CPU was fully utilized and a read‑only alert had been triggered for half an hour, threatening the availability of several high‑traffic services.

Log analysis revealed a single SQL statement that scanned over a million rows, was executed more than ten thousand times, and each execution took around 3000 ms, effectively exhausting database resources.

The root cause was identified as a missing user_fruit_id parameter in the query; because this column is the leftmost field of a composite index, its absence prevented the index from being used, forcing a full‑table scan.

As an urgent mitigation the engineers added a new composite index that omitted user_fruit_id (e.g., KEY `idx_task_type_receive_start_time` (`task_type`,`receive_start_time`,`receive_end_time`,`created_time`) USING BTREE ), dramatically reducing scanned rows and restoring normal operation after a restart.

The incident led to a detailed explanation of MySQL’s “left‑most matching principle”: a composite index can only be used if the query predicates start with the leftmost column(s). An example with a (name, age) index illustrated how queries on name alone use the index, while queries on age alone cannot.

Further investigation uncovered additional bugs: the request object had a @NotNull annotation on userFruitId but the controller method lacked the @Validated annotation, so the validation never ran; also, there was no existence check for the referenced foreign‑key record.

Key take‑aways were summarized: always validate critical input parameters, benchmark SQL with EXPLAIN and tune indexes, and enforce rigorous code reviews to catch simple yet costly mistakes.