Optimizing a Slow SQL Query with AnalyticDB and Data Synchronization Strategies

Introduction: In Xianyu backend, a particular SQL query suffered from high latency (≈2 s) despite simple conditions and no joins.

SQL example:

select id, userid, itemid, status, type, modifiedtime ... from table1 where userid = 123 and status in (0,1,2) and type in ('a','b','c') order by status, modifiedtime desc limit 0,20

Analysis showed the query did not fully use the composite index (userid,status,type,modifiedtime) because the IN predicates caused index range scans and a filesort, leading to full‑table scans.

Partitioning the table by userid modulo was already applied, but the bottleneck remained.

Solution selection: The team evaluated search engines (e.g., Elasticsearch) and OLAP warehouses. AnalyticDB MySQL (ADB) was chosen for its column‑store architecture, real‑time analytics, and MySQL‑compatible syntax.

Data synchronization approaches were compared:

Dual‑write (write to MySQL then ADB) – simple but error‑prone.

DTS (Data Transmission Service) – stable but unavailable in the project.

Binlog listening – capture MySQL changes and write to ADB asynchronously.

Binlog implementation snippet:

insert into ... on duplicate key update ...

After migrating the query to ADB, average latency dropped from 2 s to ~100 ms, with 98 % of queries completing within 1 s.

Further tuning included:

Defining a clustering column (userid) to reduce I/O.

Using index‑filter hints to disable inefficient index columns.

Adjusting block_size parameters to limit scanned rows.

Hint example:

/*+ filter_not_pushdown_columns=[${database}.${tableName}:${col1Name}|${col2Name}] */

Real‑time sync latency issues were mitigated by increasing JVM heap (to 2048 MB) to reduce full GC pauses, and by batching binlog events into a single INSERT … VALUES statement via MyBatis foreach.

Batch insert snippet:

<insert> insert into table1 (id,itemid,userid,…) values <foreach collection="list" item="item" separator=","> (#{item.id},#{item.itemId},#{item.userId},…) </foreach> on duplicate key update … </insert>

Post‑optimization metrics showed 99.94 % of queries finished under 1 s and 99.67 % under 0.5 s.

Conclusion: By moving the heavy query to AnalyticDB, applying appropriate indexing, hinting, and sync strategies, the slow‑SQL problem was resolved, demonstrating a viable architecture for large‑scale, low‑latency data access.