MCP 服务器

MCP Database Performance Analyzer

An MCP server that analyzes SQL schemas, generates realistic test data, benchmarks queries, and provides optimization recommendations to improve database performance.

README

MCP Database Performance Analyzer

Autonomous MCP server for SQL schema analysis, data generation, benchmarking and optimization.

A production-ready Model Context Protocol (MCP) server that acts as an intelligent SQL performance analysis and optimization assistant. Point it at a SQL dump, and it will parse the schema, populate a database with realistic relational data at scale, benchmark queries, identify bottlenecks, explain root causes, and generate optimized SQL and indexing recommendations.

✨ Features

Category	Capabilities
Schema Analysis	Parse SQL dumps · Detect PK/FK/indexes/views/procedures/triggers · Relationship graph · Missing FK indexes
Data Generation	6 business domains · 10k–10M rows · Referential integrity · Deterministic seed
Query Execution	SELECT/JOIN/CTE/Window functions · Execution metrics · EXPLAIN analysis
Benchmarking	P50/P95/P99 latencies · QPS · Concurrent workloads · Before/after comparison
Optimization	Anti-pattern detection · Query rewriting · Index recommendations · Health score (0–100)
Reporting	JSON · Markdown · HTML · CSV · PDF · ERD diagrams

🚀 Quick Start

Prerequisites

Node.js 20+
MySQL 8.0 (or use Docker)

Option 1: Docker (Recommended)

# Start MySQL + MCP server
docker compose up -d

# Wait for MySQL to be healthy
docker compose logs -f mcp-server

Option 2: Local

# Install dependencies
npm install

# Copy and configure environment
cp .env.example .env
# Edit .env with your MySQL credentials

# Build
npm run build

# Run
npm start

🔧 MCP Tools

Tool	Description
`load_schema`	Parse a SQL dump file and extract the full schema model
`analyze_schema`	Analyze relationships, ERD, missing FK indexes, circular dependencies
`create_database`	Execute all DDL in dependency order and validate creation
`generate_test_data`	Preview generated data without inserting (dry run)
`populate_database`	Insert realistic relational data at specified scale
`execute_query`	Execute a SQL query with metrics capture
`benchmark_query`	Benchmark a query with P50/P95/P99 and QPS
`benchmark_database`	Run a mixed workload simulation
`analyze_execution_plan`	EXPLAIN + EXPLAIN ANALYZE with insights
`detect_slow_queries`	Classify queries and detect root causes
`recommend_indexes`	Detect missing, duplicate, unused, redundant indexes
`optimize_query`	Anti-pattern detection + query rewriting
`compare_queries`	Side-by-side benchmark with improvement %
`generate_report`	Full report in JSON/Markdown/HTML/CSV/PDF
`reset_database`	Truncate or recreate the database

📋 Example Workflow

1. load_schema(sql: "<content of dump.sql>")
2. analyze_schema()
3. create_database()
4. populate_database(domain: "ecommerce", datasetSize: "100k")
5. benchmark_query(sql: "SELECT * FROM orders JOIN customers ON ...")
6. analyze_execution_plan(sql: "...")
7. detect_slow_queries(queries: [...])
8. recommend_indexes()
9. optimize_query(sql: "...")
10. generate_report(formats: ["html", "pdf"])

🗄 Database Support

Database	Status
MySQL 8.0	✅ Full support (primary)
PostgreSQL	🔶 Stub (core ops work, advanced metrics pending)
SQLite	✅ Full support (used for tests)
SQL Server	📋 Planned

🏢 Business Domains

The data generator supports 6 business domain profiles:

ecommerce — customers, products, orders, payments, reviews
erp — suppliers, purchase orders, inventory, warehouses, accounts
crm — contacts, leads, deals, activities, campaigns
hospital — patients, doctors, appointments, prescriptions, diagnoses
banking — accounts, transactions, loans, cards, branches
hr — employees, departments, positions, payroll, performance reviews

🔍 Optimization Detectors

Anti-Pattern	Detection	Auto-Fix
SELECT *	✅	✅
Large OFFSET pagination	✅	✅
Non-sargable predicates	✅	✅
Leading wildcard LIKE	✅	—
Implicit type cast	✅	✅
Missing FK indexes	✅	✅
Duplicate indexes	✅	✅
Unused indexes	✅	✅
Full table scans	✅	—
Filesort	✅	—
Temporary tables	✅	—

📊 Health Score

The database health score (0–100, Grade A–F) is computed from weighted categories:

Category	Weight
Index Strategy	30%
Schema Design	25%
Query Efficiency	25%
Data Distribution	10%
Security	10%

🧪 Testing

# Unit tests (no external DB required)
npm test

# Integration tests (requires MySQL or use SQLite path)
npm run test:integration

# Type check
npm run typecheck

📁 Project Structure

src/
├── index.ts              # Entry point
├── server.ts             # MCP server + adapter setup
├── config/               # Typed configuration
├── adapters/             # Database adapters (MySQL, PostgreSQL, SQLite)
├── modules/
│   ├── sql-parser/       # SQL DDL/DML parser
│   ├── schema-analyzer/  # Relationship graph, orphan/cycle detection
│   ├── erd-generator/    # Mermaid ERD generation
│   ├── database-creator/ # DDL execution in dependency order
│   ├── data-generator/   # Faker-based relational data generation
│   ├── bulk-loader/      # Batched INSERT with concurrency control
│   ├── query-executor/   # Query execution with metrics
│   ├── benchmark-engine/ # P50/P95/P99 latency benchmarking
│   ├── explain-analyzer/ # EXPLAIN plan parsing and insights
│   ├── slow-query-detector/ # Query classification
│   ├── index-analyzer/   # Missing/duplicate/unused index detection
│   ├── optimization-engine/ # Anti-patterns, query rewriting, health score
│   └── report-generator/ # JSON/MD/HTML/CSV/PDF rendering
├── tools/                # All 15 MCP tool handlers
├── types/                # Shared TypeScript interfaces
└── utils/                # Logger, progress reporter, retry

🌐 Claude Desktop Integration

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "db-optimizer": {
      "command": "node",
      "args": ["/path/to/database-benchmark/dist/index.js"],
      "env": {
        "DB_ADAPTER": "mysql",
        "DB_HOST": "127.0.0.1",
        "DB_PORT": "3306",
        "DB_USER": "root",
        "DB_PASSWORD": "root",
        "DB_NAME": "mcp_benchmark"
      }
    }
  }
}