Deep Dive into JD’s sql-analysis Slow‑SQL Component Architecture

Background

The author revisits the sql‑analysis component introduced in a previous post, emphasizing the importance of daily monitoring of slow SQL statements for developers.

2.1 Source Structure

The component is organized into eight functional modules:

core : integrates the component with MyBatis and orchestrates other modules.

config : initializes configuration information.

extrat : parses MyBatis objects to extract the complete SQL to be executed.

analysis : assembles and runs EXPLAIN statements and retrieves analysis results.

rule : loads and initializes SQL analysis rules, supporting custom rules.

score : matches analysis results against scoring rules to generate optimization suggestions.

out : outputs results; currently supports error‑log and MQ.

replace : dynamically replaces SQL based on DUCC configuration.

2.2 Entry Interceptor

The core entry point is the MyBatis interceptor SqlAnalysisAspect located in the core module. Its configuration follows the typical MyBatis interceptor pattern. During initialization, setProperties loads core configuration, rule loader, and determines the output mode.

In the intercept method, the component checks whether it is enabled and whether the first argument is a Connection at the Prepare stage. If so, the following steps are executed:

Obtain the current StatementHandler.

Extract the SQL into a SqlExtractResult object.

Analyze the SQL and obtain the analysis result.

Score the analysis result.

Output the result to the console or MQ.

2.3 SQL Extraction

The extraction logic resides in SqlExtract.extract(statementHandler). It first retrieves the MappedStatement from the MyBatis context, then obtains the SQL ID and type, checks whether the SQL should be analyzed (four branch conditions: type mismatch, ID excluded, already checked, within time window), binds the SQL parameters, constructs the final executable SQL, and wraps everything into a SqlExtractResult.

Key code snippet:

SqlExtractResult sqlExtractResult = SqlExtract.extract(statementHandler);

2.4 Query‑Plan Analysis

The analysis module receives the extracted SQL and a Connection object:

Connection connection = (Connection)invocation.getArgs()[0];
SqlAnalysisResultList resultList = SqlAnalysis.analysis(sqlExtractResult, connection);

It performs a standard JDBC query, converts the ResultSet into SqlAnalysisResult objects that mirror the MySQL EXPLAIN output, and records version‑specific details.

2.5 Rule Scoring

Scoring uses the open‑source rule engine easy‑rules . Rule definitions are loaded from configuration files and refreshed via RuleEngineExcutor.refresh. The core scoring method is:

SqlScoreResult sqlScoreResult = sqlScoreService.score(resultList);

The implementation follows the Strategy pattern: SqlScoreServiceRulesEngine iterates over analysis results, applies a 100‑point baseline, deducts points according to matched rules, and returns a SqlScoreResultDetail object.

2.6 Result Notification

After scoring, the result is dispatched through the output module: sqlScoreResultOut.outResult(sqlScoreResult); The module currently supports three strategies (error log, MQ, etc.) and is also designed with the Strategy pattern, allowing future SPI‑based extensions.

Summary of Design Benefits

The component offers strong preventive capabilities by analyzing SQL execution plans before problems surface, improves system stability in high‑load e‑commerce scenarios, provides real‑time optimization and rapid mitigation via hot‑SQL replacement, and lowers the technical barrier by generating actionable suggestions without deep database expertise.