MyBatis SQL Analysis Component for Slow Query Prevention and Real‑time Alerting

The article addresses the critical risk of slow SQL statements during large‑scale promotions and daily operations, explaining how traditional log‑based detection is reactive and often too late. It proposes a MyBatis‑integrated analysis component that proactively examines SQL using EXPLAIN, issues alerts, and can replace problematic queries on the fly.

Background : Slow SQL can cause application jitter and requires experienced evaluation; many bad queries slip into production, leading to alarms and service disruption.

Solution Approach : The component intercepts MyBatis execution, extracts the full SQL, runs an EXPLAIN analysis, applies configurable rules to score and suggest optimizations, and optionally performs dynamic replacement via a DUCC configuration service.

Design Overview : The system is divided into eight modules – core (integration), config (initialization), extract (SQL extraction), analysis (EXPLAIN execution), rule (rule loading), score (scoring and suggestions), out (output handling), and replace (dynamic replacement). Default rules include detecting missing indexes, poor index filtering, excessive row returns, and file sorting.

Core Functionality : Real‑time SQL analysis with pre‑emptive alerts and dynamic replacement capability.

Configuration Example (Maven Dependency) : <dependency> <groupId>com.jd.sql.analysis</groupId> <artifactId>sql-analysis</artifactId> <version>1.2-SNAPSHOT</version> </dependency>

MyBatis Plugin Configuration : <configuration> <plugins> <plugin interceptor="com.jd.sql.analysis.core.SqlAnalysisAspect"> <!-- Enable analysis --> <property name="analysisSwitch" value="true"/> <!-- Enable SQL replacement --> <property name="sqlReplaceModelSwitch" value="true"/> <property name="duccAppName" value="XXx"/> <property name="duccUri" value="ucc://[email protected]/v1/namespace/workbench_backend/config/default/profiles/test?longPolling=60000&necessary=false"/> <property name="duccMonitorKey" value="refundBugFlag"/> </plugin> </plugins> </configuration>

Performance Test : In a test of 10,000 simple queries, the component added roughly 10 ms overhead per execution when enabled, showing negligible impact compared to the baseline.

Applicable Scenarios : Preventing slow SQL before release, quickly mitigating online issues, and providing optimization guidance with minimal integration effort.

Advantages : Early detection based on syntax and index analysis, dynamic replacement with near‑zero runtime cost, extensible rule and output mechanisms, low integration overhead, and cost‑effective operation.

Conclusion : The component demonstrates a practical way to embed proactive SQL analysis into Java backend services, balancing performance, configurability, and extensibility.