sql-database-assistant

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

SQL Database Assistant

SQL数据库助手

Category: Engineering Domain: Database Development & Optimization

分类: 工程 领域: 数据库开发与优化

Overview

概述

The SQL Database Assistant skill provides tools for analyzing SQL query performance, exploring database schemas from DDL files, and generating migration SQL from schema differences. It helps teams write efficient queries, maintain clean schemas, and manage database evolution safely.

SQL数据库助手技能提供用于分析SQL查询性能、从DDL文件探索数据库架构以及根据架构差异生成迁移SQL的工具。它帮助团队编写高效的查询、维护清晰的架构并安全管理数据库演进。

Quick Start

快速开始

bash

undefined

bash

undefined

Analyze a SQL query for performance issues

python scripts/query_optimizer.py --file slow_query.sql

Analyze inline SQL

python scripts/query_optimizer.py --query "SELECT * FROM users WHERE name LIKE '%john%'"

Explore schema from DDL file

python scripts/schema_explorer.py --file schema.sql

Generate migration from schema diff

python scripts/migration_generator.py --from old_schema.sql --to new_schema.sql

JSON output

python scripts/query_optimizer.py --file query.sql --format json

undefined

python scripts/query_optimizer.py --file query.sql --format json

undefined

Tools Overview

工具概述

query_optimizer.py

Analyzes SQL queries for performance issues and optimization opportunities.

Feature	Description
SELECT * detection	Flags queries selecting all columns
Missing index hints	Identifies WHERE/JOIN columns likely needing indexes
N+1 detection	Flags correlated subquery patterns
Full table scan	Detects queries without WHERE clauses on large tables
JOIN analysis	Checks join conditions and types
LIKE optimization	Flags leading wildcard LIKE patterns

分析SQL查询的性能问题与优化空间。

功能	描述
SELECT * 检测	标记选择所有列的查询
缺失索引提示	识别WHERE/JOIN子句中可能需要索引的列
N+1检测	标记关联子查询模式
全表扫描检测	检测针对大表且无WHERE子句的查询
JOIN分析	检查JOIN条件与类型
LIKE优化提示	标记以通配符开头的LIKE模式

schema_explorer.py

Generates documentation from SQL DDL (CREATE TABLE) files.

Feature	Description
Table catalog	Lists all tables with column counts
Column details	Documents types, nullability, defaults
Index listing	Catalogs indexes and their columns
Relationship mapping	Identifies foreign key relationships
Markdown output	Generates schema documentation

从SQL DDL（CREATE TABLE）文件生成文档。

功能	描述
表目录	列出所有表及列数
列详情	记录数据类型、可空性、默认值
索引列表	分类记录索引及其关联列
关系映射	识别外键关系
Markdown输出	生成架构文档

migration_generator.py

Generates migration SQL by comparing two schema DDL files.

Feature	Description
Column additions	ALTER TABLE ADD COLUMN for new columns
Column removals	ALTER TABLE DROP COLUMN for removed columns
Type changes	ALTER TABLE ALTER COLUMN for type modifications
New tables	CREATE TABLE for entirely new tables
Dropped tables	DROP TABLE for removed tables
Index changes	CREATE/DROP INDEX for index differences

通过对比两个架构DDL文件生成迁移SQL。

功能	描述
列新增	为新列生成ALTER TABLE ADD COLUMN语句
列删除	为移除的列生成ALTER TABLE DROP COLUMN语句
类型变更	为类型修改生成ALTER TABLE ALTER COLUMN语句
新表创建	为全新表生成CREATE TABLE语句
表删除	为移除的表生成DROP TABLE语句
索引变更	为索引差异生成CREATE/DROP INDEX语句

Workflows

工作流

Query Optimization Workflow

查询优化工作流

Identify slow queries - Collect queries from slow query log
Analyze - Run query_optimizer.py on each query
Review findings - Prioritize by estimated impact
Optimize - Apply suggested improvements
Verify - Re-analyze to confirm optimization

识别慢查询 - 从慢查询日志中收集查询语句
分析 - 对每个查询运行query_optimizer.py
审查结果 - 根据预估影响优先级排序
优化 - 应用建议的改进方案
验证 - 重新分析以确认优化效果

Schema Documentation Workflow

架构文档工作流

Export DDL - Dump schema from database
Explore - Run schema_explorer.py to generate docs
Review - Check relationships and data types
Publish - Include in project documentation

导出DDL - 从数据库导出架构
探索 - 运行schema_explorer.py生成文档
审查 - 检查关系与数据类型
发布 - 纳入项目文档

Migration Workflow

迁移工作流

Capture current - Export current schema DDL
Define target - Write desired schema DDL
Generate migration - Run migration_generator.py
Review SQL - Check generated migration for safety
Test - Apply to staging database first
Deploy - Apply to production with rollback plan

捕获当前架构 - 导出当前架构DDL
定义目标架构 - 编写期望的架构DDL
生成迁移脚本 - 运行migration_generator.py
审查SQL - 检查生成的迁移脚本安全性
测试 - 先在预发布数据库应用
部署 - 应用到生产环境并准备回滚方案

CI Integration

CI集成

bash

undefined

bash

undefined

Lint SQL queries

python scripts/query_optimizer.py --file queries/ --format json --strict

Generate schema docs

python scripts/schema_explorer.py --file schema.sql --format markdown > SCHEMA.md

undefined

python scripts/schema_explorer.py --file schema.sql --format markdown > SCHEMA.md

undefined

Reference Documentation

参考文档

SQL Optimization - Index strategies, query patterns, anti-patterns

SQL Optimization - 索引策略、查询模式、反模式

Common Patterns Quick Reference

常见模式速查

Query Anti-Patterns

查询反模式

Pattern	Issue	Fix
`SELECT *`	Fetches unnecessary data	List specific columns
`LIKE '%term%'`	Cannot use index	Use full-text search
Correlated subquery	N+1 query pattern	Rewrite as JOIN
No WHERE clause	Full table scan	Add filtering conditions
`OR` in WHERE	Poor index usage	Use UNION or IN
Functions on indexed columns	Prevents index use	Apply to value side

模式	问题	修复方案
`SELECT *`	获取不必要的数据	列出具体列
`LIKE '%term%'`	无法使用索引	使用全文搜索
关联子查询	N+1查询模式	重写为JOIN
无WHERE子句	全表扫描	添加过滤条件
`WHERE` 中使用 `OR`	索引使用效率低	使用UNION或IN
对索引列使用函数	无法使用索引	将函数应用到值的一侧

Index Guidelines

索引指南

Query Pattern	Index Type
`WHERE col = value`	B-tree on col
`WHERE col1 = v AND col2 = v`	Composite (col1, col2)
`ORDER BY col`	B-tree on col
`WHERE col LIKE 'prefix%'`	B-tree on col
`WHERE col IN (...)`	B-tree on col
Full-text search	Full-text index

查询模式	索引类型
`WHERE col = value`	基于col的B-tree索引
`WHERE col1 = v AND col2 = v`	复合索引（col1, col2）
`ORDER BY col`	基于col的B-tree索引
`WHERE col LIKE 'prefix%'`	基于col的B-tree索引
`WHERE col IN (...)`	基于col的B-tree索引
全文搜索	全文索引

Migration Safety

迁移安全

Always generate rollback SQL alongside forward migration
Test migrations against a copy of production data
Add columns as nullable first, then backfill, then add constraints
Never rename columns directly; add new, migrate data, drop old

始终同时生成向前迁移与回滚SQL
针对生产数据副本测试迁移脚本
先添加可空列，再回填数据，最后添加约束
切勿直接重命名列；应新增列、迁移数据、删除旧列