Documentation
¶
Overview ¶
Package advisor provides SQL query optimization analysis by walking parsed ASTs and applying configurable rules that detect common performance anti-patterns.
The central type is Optimizer, created with New() (all built-in rules) or NewWithRules(...Rule) for a custom rule set. Optimizer.AnalyzeSQL is a convenience method that parses a SQL string and returns an OptimizationResult containing a slice of Suggestion values, a query complexity classification (simple / moderate / complex), and an optimization score from 0 (worst) to 100 (no issues). Each Suggestion carries a rule ID, severity (info / warning / error), a human-readable message and detail, the source location, and where possible a suggested SQL rewrite.
Eight built-in rules are registered by DefaultRules:
OPT-001 SELECT * Detection — recommend listing columns explicitly OPT-002 Missing WHERE Clause — UPDATE/DELETE without WHERE affects all rows OPT-003 Cartesian Product — implicit cross join from multiple FROM tables OPT-004 SELECT DISTINCT Overuse — DISTINCT may mask incorrect join conditions OPT-005 Subquery in WHERE — suggest converting correlated subqueries to JOINs OPT-006 OR in WHERE Clause — OR on different columns may prevent index usage OPT-007 Leading Wildcard in LIKE — LIKE '%...' forces a full table scan OPT-008 Function on Indexed Column — wrapping a column in a function defeats B-tree indexes
Custom rules implement the Rule interface (ID, Name, Description, Analyze) and are passed to NewWithRules. All built-in rules are stateless and safe for concurrent use.
Quick Start:
opt := advisor.New()
result, err := opt.AnalyzeSQL("SELECT * FROM users")
if err != nil {
log.Fatal(err)
}
for _, s := range result.Suggestions {
fmt.Printf("[%s] %s: %s\n", s.Severity, s.RuleID, s.Message)
}
For fine-grained control, parse the SQL first and call Analyze directly:
astNode, _ := gosqlx.Parse(sql) result := opt.Analyze(astNode)
Index ¶
- Constants
- func FormatResult(result *OptimizationResult) string
- type CartesianProductRule
- type DistinctOveruseRule
- type FunctionOnColumnRule
- type LeadingWildcardLikeRule
- type MissingWhereRule
- type OptimizationResult
- type Optimizer
- type OrInWhereRule
- type Rule
- type SelectStarRule
- type SubqueryInWhereRule
- type Suggestion
Constants ¶
const ( SeverityInfo = "info" SeverityWarning = "warning" SeverityError = "error" )
Severity levels for optimization suggestions.
const ( ComplexitySimple = "simple" ComplexityModerate = "moderate" ComplexityComplex = "complex" )
Complexity levels for query classification.
Variables ¶
This section is empty.
Functions ¶
func FormatResult ¶
func FormatResult(result *OptimizationResult) string
FormatResult produces a human-readable text report from an OptimizationResult.
Types ¶
type CartesianProductRule ¶
type CartesianProductRule struct{}
CartesianProductRule detects implicit cross joins (multiple tables in FROM without JOIN conditions).
func (*CartesianProductRule) Analyze ¶
func (r *CartesianProductRule) Analyze(stmt ast.Statement) []Suggestion
func (*CartesianProductRule) Description ¶
func (r *CartesianProductRule) Description() string
func (*CartesianProductRule) ID ¶
func (r *CartesianProductRule) ID() string
func (*CartesianProductRule) Name ¶
func (r *CartesianProductRule) Name() string
type DistinctOveruseRule ¶
type DistinctOveruseRule struct{}
DistinctOveruseRule warns when DISTINCT might indicate a JOIN issue.
func (*DistinctOveruseRule) Analyze ¶
func (r *DistinctOveruseRule) Analyze(stmt ast.Statement) []Suggestion
func (*DistinctOveruseRule) Description ¶
func (r *DistinctOveruseRule) Description() string
func (*DistinctOveruseRule) ID ¶
func (r *DistinctOveruseRule) ID() string
func (*DistinctOveruseRule) Name ¶
func (r *DistinctOveruseRule) Name() string
type FunctionOnColumnRule ¶
type FunctionOnColumnRule struct{}
FunctionOnColumnRule detects function calls wrapping columns in WHERE clauses.
func (*FunctionOnColumnRule) Analyze ¶
func (r *FunctionOnColumnRule) Analyze(stmt ast.Statement) []Suggestion
func (*FunctionOnColumnRule) Description ¶
func (r *FunctionOnColumnRule) Description() string
func (*FunctionOnColumnRule) ID ¶
func (r *FunctionOnColumnRule) ID() string
func (*FunctionOnColumnRule) Name ¶
func (r *FunctionOnColumnRule) Name() string
type LeadingWildcardLikeRule ¶
type LeadingWildcardLikeRule struct{}
LeadingWildcardLikeRule detects LIKE patterns starting with % which prevent index usage.
func (*LeadingWildcardLikeRule) Analyze ¶
func (r *LeadingWildcardLikeRule) Analyze(stmt ast.Statement) []Suggestion
func (*LeadingWildcardLikeRule) Description ¶
func (r *LeadingWildcardLikeRule) Description() string
func (*LeadingWildcardLikeRule) ID ¶
func (r *LeadingWildcardLikeRule) ID() string
func (*LeadingWildcardLikeRule) Name ¶
func (r *LeadingWildcardLikeRule) Name() string
type MissingWhereRule ¶
type MissingWhereRule struct{}
MissingWhereRule detects UPDATE/DELETE statements without a WHERE clause.
func (*MissingWhereRule) Analyze ¶
func (r *MissingWhereRule) Analyze(stmt ast.Statement) []Suggestion
func (*MissingWhereRule) Description ¶
func (r *MissingWhereRule) Description() string
func (*MissingWhereRule) ID ¶
func (r *MissingWhereRule) ID() string
func (*MissingWhereRule) Name ¶
func (r *MissingWhereRule) Name() string
type OptimizationResult ¶
type OptimizationResult struct {
Suggestions []Suggestion // All suggestions from all rules
QueryComplexity string // One of ComplexitySimple, ComplexityModerate, ComplexityComplex
Score int // 0-100 where 100 is optimal (no suggestions)
}
OptimizationResult contains the full output of an optimization analysis.
type Optimizer ¶
type Optimizer struct {
// contains filtered or unexported fields
}
Optimizer analyzes parsed SQL ASTs and produces optimization suggestions.
An Optimizer is configured with a set of Rules. The default set includes all built-in rules (OPT-001 through OPT-008). The Optimizer is safe for concurrent use after creation.
func New ¶
func New() *Optimizer
New creates a new Optimizer with all built-in optimization rules enabled.
func NewWithRules ¶
NewWithRules creates a new Optimizer with the specified rules.
func (*Optimizer) Analyze ¶
func (o *Optimizer) Analyze(tree *ast.AST) *OptimizationResult
Analyze inspects all statements in the given AST and returns an OptimizationResult.
Each configured rule is applied to every statement in the AST. The result includes all suggestions, a complexity classification, and an optimization score.
func (*Optimizer) AnalyzeSQL ¶
func (o *Optimizer) AnalyzeSQL(sql string) (*OptimizationResult, error)
AnalyzeSQL is a convenience method that parses the SQL string and analyzes it.
This method uses gosqlx.Parse internally for tokenization and parsing. If parsing fails, the error is returned and no analysis is performed.
type OrInWhereRule ¶
type OrInWhereRule struct{}
OrInWhereRule warns about OR conditions that may prevent index usage.
func (*OrInWhereRule) Analyze ¶
func (r *OrInWhereRule) Analyze(stmt ast.Statement) []Suggestion
func (*OrInWhereRule) Description ¶
func (r *OrInWhereRule) Description() string
func (*OrInWhereRule) ID ¶
func (r *OrInWhereRule) ID() string
func (*OrInWhereRule) Name ¶
func (r *OrInWhereRule) Name() string
type Rule ¶
type Rule interface {
// ID returns the unique rule identifier (e.g., "OPT-001").
ID() string
// Name returns a short human-readable rule name.
Name() string
// Description returns a detailed description of what the rule checks.
Description() string
// Analyze inspects a single statement and returns suggestions.
Analyze(stmt ast.Statement) []Suggestion
}
Rule defines the interface that all optimization rules must implement.
Each rule inspects a single AST statement and returns zero or more Suggestions. Rules should be stateless and safe for concurrent use.
func DefaultRules ¶
func DefaultRules() []Rule
DefaultRules returns the default set of all built-in optimization rules.
type SelectStarRule ¶
type SelectStarRule struct{}
SelectStarRule detects SELECT * and recommends listing columns explicitly.
func (*SelectStarRule) Analyze ¶
func (r *SelectStarRule) Analyze(stmt ast.Statement) []Suggestion
func (*SelectStarRule) Description ¶
func (r *SelectStarRule) Description() string
func (*SelectStarRule) ID ¶
func (r *SelectStarRule) ID() string
func (*SelectStarRule) Name ¶
func (r *SelectStarRule) Name() string
type SubqueryInWhereRule ¶
type SubqueryInWhereRule struct{}
SubqueryInWhereRule suggests converting subqueries in WHERE to JOINs.
func (*SubqueryInWhereRule) Analyze ¶
func (r *SubqueryInWhereRule) Analyze(stmt ast.Statement) []Suggestion
func (*SubqueryInWhereRule) Description ¶
func (r *SubqueryInWhereRule) Description() string
func (*SubqueryInWhereRule) ID ¶
func (r *SubqueryInWhereRule) ID() string
func (*SubqueryInWhereRule) Name ¶
func (r *SubqueryInWhereRule) Name() string
type Suggestion ¶
type Suggestion struct {
RuleID string // Unique rule identifier (e.g., "OPT-001")
Severity string // One of SeverityInfo, SeverityWarning, SeverityError
Message string // Short description of the issue
Detail string // Detailed explanation and rationale
Line int // Source line (1-based, 0 if unknown)
Column int // Source column (1-based, 0 if unknown)
OriginalSQL string // The problematic SQL fragment (if available)
SuggestedSQL string // Suggested rewrite (if available)
}
Suggestion represents a single optimization recommendation produced by a Rule.
Source Files