Detecting and Preventing Slow SQL with JD’s Open‑Source sql‑analysis Component

Problem

Developers often discover slow SQL only after it impacts production, leading to reactive debugging, code redeployment, or index changes.

Solution Overview

JD open‑source sql‑analysis is a MyBatis plugin that intercepts SQL execution, runs EXPLAIN to obtain the execution plan, and evaluates the plan with an Easy‑Rules rule engine. It provides pre‑execution warnings and optimization suggestions, enabling detection of potentially slow SQL before it runs in production.

Core Pain Points Addressed

Inability to discover slow‑SQL candidates before they affect production.

Slow response to online slow‑SQL incidents, requiring code redeployment or index changes.

Design Principles

Detect problematic SQL during development or testing.

Support hot‑update replacement of SQL statements in production.

Basic Usage

Clone the repository from https://github.com/jd-opensource/sql-analysis and import into an IDE.

Load the demo data into a MySQL instance; the demo contains queries with and without indexes.

Configure jdbc.properties with the target database connection.

Execute the provided unit tests. The tests run EXPLAIN for each statement, feed the plan to the rule engine, and output a match result with optimization advice.

Workflow

Within MyBatis, a custom interceptor invokes sql‑analysis, executes EXPLAIN, parses the plan, applies the Easy‑Rules engine, and produces either a warning or an optimization suggestion. The pipeline is: interception → plan extraction → rule evaluation → result output.

Rule Engine Details

Easy‑Rules is used as the rule‑engine framework. Sample rule files reside in the samples module; users can add business‑specific rules by editing these files.

Advantages

Pre‑execution analysis: evaluates SQL based on syntax and index usage rather than runtime latency.

Performance: configurable check frequency; hot‑swap of SQL incurs negligible overhead.

Extensibility: custom rule sets and output formats can be added via Easy‑Rules.

Low integration cost: no invasive code changes required.

Value Highlights

Early warnings reduce production incidents.

Real‑time hot‑update of problematic SQL shortens mitigation time.

Minimal DBA expertise needed because the component automatically generates optimization suggestions.

Key Knowledge Points

SQL execution plans and the EXPLAIN command.

MyBatis plugin architecture for non‑intrusive interception.

Easy‑Rules as an extensible rule‑engine framework.

Preventive vs. reactive strategies for slow‑SQL handling.