Practical Slow Query Optimization for MySQL at Ctrip

Slow queries are SQL statements whose execution time exceeds a predefined threshold, causing high resource consumption and slow business responses. With Ctrip's hotel business expansion and a large SQLServer‑to‑MySQL migration, the number of slow queries and daily alerts surged, prompting an urgent need for systematic MySQL slow‑query governance.

The original release process was fast but allowed many low‑quality SQL statements into production, increasing alerts and rollbacks. A new SQLReview stage was introduced to pre‑screen potential slow queries, ensuring that only DBA‑approved statements reach production. Although an automated review platform exists, its recommendations are not fully adopted due to varying developer expertise.

Understanding how a query is processed is essential. The execution flow consists of: (1) SQL cache lookup (QC), (2) syntax parsing, (3) execution‑plan generation, (4) query execution, and (5) result output. Adding EXPLAIN before a statement displays the plan in a tabular form, helping pinpoint performance bottlenecks.

Most slow queries stem from index issues. Common problems include missing indexes ( select * from hotel where name='xc'; ), index misuse, composite‑index left‑most rule violations, uneven data distribution, and index‑merge inefficiencies. Remedies involve creating appropriate indexes ( alter table hotel add index idx_hotelid_name_isdel(hotelid,name,status); ), using FORCE INDEX() or IGNORE INDEX() judiciously, and avoiding functions or LIKE patterns that prevent index usage.

High query frequency and concurrency amplify the problem. Bad loops, flash‑sale traffic spikes, uncontrolled batch jobs, and cache breakdowns can flood the database with requests, turning ordinary queries into slow ones.

Poor SQL writing practices also contribute. Deep pagination with large OFFSET values degrades performance ( SELECT * FROM testlimittable ORDER BY id LIMIT 10000, 10; ). Alternatives include using indexed “max/min” pagination or ordering with LIMIT 1 on a descending index. Group‑by and order‑by operations may trigger temporary tables and filesorts; adding covering indexes (e.g., alter table hotel add index idx_name_hotelid(name,hotelid); ) can eliminate these extra steps.

Resource contention such as lock waits, network jitter, and hardware saturation (CPU or disk I/O) further slows queries. Recommendations include cautious use of FOR UPDATE , ensuring DML statements leverage indexes, and limiting concurrent modifications on hot rows.

In summary, slow‑query mitigation is a long‑term effort that requires a robust daily workflow, collaboration between developers and DBAs, and continuous education on SQL best practices. Database‑level tuning alone cannot eradicate all slow queries; application‑level improvements and disciplined coding are equally vital.