How Deep Pagination Slowed Our System and the SQL Fixes That Saved It

Incident Background

This incident is a typical example of a pagination slow‑query problem we encountered in our team; the article lets you follow the reconstruction of the incident scene and the adjustments made at each step.

Incident Problem Scene

16:00 Received colleague feedback that the fusion system's pagination query availability dropped.

16:05 Checked the UMP monitoring interface and found the TP99 metric unusually high.

Machine monitoring showed that almost all machines had abnormal TP999 values, while CPU usage was not high.

16:10 Database monitoring revealed abnormal CPU usage, pinpointing a database issue and a flood of slow‑SQL emails.

We determined the problem required a two‑step approach: first enable rate limiting to prevent more slow‑SQL requests, then analyze and refactor the slow SQL. Most slow SQL involved the fusion system's pagination query interface, and a traffic surge around 15:35 caused database CPU and TP999 spikes, suggesting inventory queries overloaded the database.

16:15 Log analysis showed most SQL pointed to a merchant with 100,000 rows (10% of total), and MQ retries surged; the pagination interface timeout was set to 2 seconds, indicating slow queries caused high‑frequency retries that crippled the database.

16:25 After reviewing code, we identified a deep‑pagination issue and decided to optimize the SQL by first querying IDs, then using a range query instead of offset‑based pagination, and paused upstream MQ consumption.

17:40 Optimized code went live, but accumulated MQ messages still pressured the database, causing TP99 to spike again and CPU to hit 100%.

18:00 Decided to develop a new interface based on rolling IDs for pagination, involving upstream collaboration.

22:20 New interface launched, MQ consumption resumed, and the system stabilized, marking the problem solved.

Root Causes and Solutions

Why Deep Pagination Occurs

Problem SQL example:

select * from table where org_code = xxxx limit 1000,100

MySQL's LIMIT works by reading the first 1000 rows, discarding them, then returning the next 100 rows; larger offsets degrade performance.

Various Solutions for Deep Pagination

Query ID + ID‑Based Query

First query IDs, then use them in an IN clause for higher efficiency.

select id from table where org_code = xxxx limit 1000,5

Then fetch rows by ID:

select * from table where id in (1,2,3,4,5);

ID‑Based Range Query

After obtaining IDs, use a range query to avoid IN list comparisons.

select * from table where org_code = xxxx and id >= 1 and id <= 5;

Sub‑Query Approach

Using a sub‑query reduces I/O interactions with the database.

select a.id,a.dj_sku_id,a.jd_sku_id from table a join (select id from jd_spu_sku where org_code = xxxx limit 1000,5) b on a.id = b.id;

Cursor‑Based (Rolling) Query

Each request returns the maximum ID (cursor); the next request supplies this cursor to fetch the next N rows.

select * from table where org_code = xxxx and id > 0 limit 10;

After testing, this method proved simple to implement and performant, though it requires client changes and ordered IDs.

Final Solution

Join‑Based Query

We first query IDs (avoiding leaf‑node fields to ensure index usage), then join to fetch full rows:

select a.id as id, a.dj_org_code as djOrgCode, a.dj_sku_id as djSkuId, a.jd_sku_id as jdSkuId, a.yn as yn from table a join (SELECT id FROM table where org_code = xxxx and id > 0 order by id asc limit 500) t on a.id = t.id;

This query uses the primary key index, avoiding full table scans.

Problem Summary

B‑side systems must implement protection mechanisms such as rate limiting to prevent abnormal traffic from overwhelming the database.

Potential risk points should never be ignored; earlier warnings about the pagination interface were missed, leading to the incident.

SQL optimizations must be thoroughly tested across edge cases before deployment.