SQL性能优化助手 - 专业SQL查询调优与索引策略优化工具

sql-optimization by github/awesome-copilot

8,000 周安装量

26,700 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/github/awesome-copilot --skill sql-optimization

开发数据分析开发运维

🇨🇳中文介绍

SQL 性能优化助手

为 ${selection}（如未选择则针对整个项目）提供专业的 SQL 性能优化。专注于适用于 MySQL、PostgreSQL、SQL Server、Oracle 及其他 SQL 数据库的通用 SQL 优化技术。

🎯 核心优化领域

查询性能分析

-- ❌ 不佳：低效的查询模式
SELECT * FROM orders o
WHERE YEAR(o.created_at) = 2024
  AND o.customer_id IN (
      SELECT c.id FROM customers c WHERE c.status = 'active'
  );

-- ✅ 良好：带有适当索引提示的优化查询
SELECT o.id, o.customer_id, o.total_amount, o.created_at
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01' 
  AND o.created_at < '2025-01-01'
  AND c.status = 'active';

-- 所需索引：
-- CREATE INDEX idx_orders_created_at ON orders(created_at);
-- CREATE INDEX idx_customers_status ON customers(status);
-- CREATE INDEX idx_orders_customer_id ON orders(customer_id);

索引策略优化

-- ❌ 不佳：糟糕的索引策略
CREATE INDEX idx_user_data ON users(email, first_name, last_name, created_at);

-- ✅ 良好：优化的复合索引
-- 适用于先按 email 筛选，再按 created_at 排序的查询
CREATE INDEX idx_users_email_created ON users(email, created_at);

-- 适用于全文姓名搜索
CREATE INDEX idx_users_name ON users(last_name, first_name);

-- 适用于用户状态查询
CREATE INDEX idx_users_status_created ON users(status, created_at)
WHERE status IS NOT NULL;

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

📊 性能调优技术

-- ❌ 不佳：低效的 JOIN 顺序和条件
SELECT o.*, c.name, p.product_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01'
  AND c.status = 'active';

-- ✅ 良好：带过滤的优化 JOIN
SELECT o.id, o.total_amount, c.name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id AND c.status = 'active'
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01';

-- ❌ 不佳：基于 OFFSET 的分页（对于大偏移量很慢）
SELECT * FROM products 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

-- ✅ 良好：基于游标的分页
SELECT * FROM products 
WHERE created_at < '2024-06-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 20;

-- 或使用基于 ID 的游标
SELECT * FROM products 
WHERE id > 1000
ORDER BY id 
LIMIT 20;

-- ❌ 不佳：多个独立的聚合查询
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders WHERE status = 'shipped';
SELECT COUNT(*) FROM orders WHERE status = 'delivered';

-- ✅ 良好：使用条件聚合的单次查询
SELECT 
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) as shipped_count,
    COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count
FROM orders;

🔍 查询反模式

-- ❌ 不佳：SELECT * 反模式
SELECT * FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

-- ✅ 良好：显式列选择
SELECT lt.id, lt.name, at.value
FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

-- ❌ 不佳：WHERE 子句中的函数调用
SELECT * FROM orders 
WHERE UPPER(customer_email) = 'JOHN@EXAMPLE.COM';

-- ✅ 良好：对索引友好的 WHERE 子句
SELECT * FROM orders 
WHERE customer_email = 'john@example.com';
-- 考虑：CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));

-- ❌ 不佳：复杂的 OR 条件
SELECT * FROM products 
WHERE (category = 'electronics' AND price < 1000)
   OR (category = 'books' AND price < 50);

-- ✅ 良好：使用 UNION 方法以获得更好的优化
SELECT * FROM products WHERE category = 'electronics' AND price < 1000
UNION ALL
SELECT * FROM products WHERE category = 'books' AND price < 50;

📈 数据库无关优化

-- ❌ 不佳：逐行操作
INSERT INTO products (name, price) VALUES ('Product 1', 10.00);
INSERT INTO products (name, price) VALUES ('Product 2', 15.00);
INSERT INTO products (name, price) VALUES ('Product 3', 20.00);

-- ✅ 良好：批量插入
INSERT INTO products (name, price) VALUES 
('Product 1', 10.00),
('Product 2', 15.00),
('Product 3', 20.00);

-- ✅ 良好：使用临时表进行复杂操作
CREATE TEMPORARY TABLE temp_calculations AS
SELECT customer_id, 
       SUM(total_amount) as total_spent,
       COUNT(*) as order_count
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;

-- 使用临时表进行进一步计算
SELECT c.name, tc.total_spent, tc.order_count
FROM temp_calculations tc
JOIN customers c ON tc.customer_id = c.id
WHERE tc.total_spent > 1000;

🛠️ 索引管理

-- ✅ 良好：覆盖索引设计
CREATE INDEX idx_orders_covering 
ON orders(customer_id, created_at) 
INCLUDE (total_amount, status);  -- SQL Server 语法
-- 或：CREATE INDEX idx_orders_covering ON orders(customer_id, created_at, total_amount, status); -- 其他数据库

-- ✅ 良好：针对特定条件的部分索引
CREATE INDEX idx_orders_active 
ON orders(created_at) 
WHERE status IN ('pending', 'processing');

📊 性能监控查询

-- 识别慢查询的通用方法
-- （具体语法因数据库而异）

-- 对于 MySQL：
SELECT query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC;

-- 对于 PostgreSQL：
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC;

-- 对于 SQL Server：
SELECT 
    qs.total_elapsed_time/qs.execution_count as avg_elapsed_time,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_time DESC;

🎯 通用优化清单

避免在生产查询中使用 SELECT *
使用适当的 JOIN 类型（INNER 与 LEFT/RIGHT）
在 WHERE 子句中尽早过滤
在适当情况下使用 EXISTS 代替 IN 进行子查询
避免在 WHERE 子句中使用会阻止索引使用的函数

为频繁查询的列创建索引
以正确的列顺序使用复合索引
避免过度索引（影响 INSERT/UPDATE 性能）
在有益处的地方使用覆盖索引
为特定的查询模式创建部分索引

数据类型与模式

使用适当的数据类型以提高存储效率
适当规范化（OLTP 使用 3NF，OLAP 使用反规范化）
使用约束来帮助查询优化器
在适当时对大表进行分区

使用 LIMIT/TOP 控制结果集
实现高效的分页策略
对批量数据更改使用批量操作
避免 N+1 查询问题
对重复查询使用预处理语句

使用真实数据量测试查询
分析查询执行计划
随时间监控查询性能
为慢查询设置警报
定期进行索引使用情况分析

📝 优化方法论

识别：使用数据库特定工具查找慢查询
分析：检查执行计划并识别瓶颈
优化：应用适当的优化技术
测试：验证性能改进
监控：持续跟踪性能指标
迭代：定期进行性能审查和优化

专注于可衡量的性能改进，并始终使用真实的数据量和查询模式测试优化。

2026 年 2 月 25 日

🇺🇸English

SQL Performance Optimization Assistant

Expert SQL performance optimization for ${selection} (or entire project if no selection). Focus on universal SQL optimization techniques that work across MySQL, PostgreSQL, SQL Server, Oracle, and other SQL databases.

🎯 Core Optimization Areas

Query Performance Analysis

-- ❌ BAD: Inefficient query patterns
SELECT * FROM orders o
WHERE YEAR(o.created_at) = 2024
  AND o.customer_id IN (
      SELECT c.id FROM customers c WHERE c.status = 'active'
  );

-- ✅ GOOD: Optimized query with proper indexing hints
SELECT o.id, o.customer_id, o.total_amount, o.created_at
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01' 
  AND o.created_at < '2025-01-01'
  AND c.status = 'active';

-- Required indexes:
-- CREATE INDEX idx_orders_created_at ON orders(created_at);
-- CREATE INDEX idx_customers_status ON customers(status);
-- CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Index Strategy Optimization

-- ❌ BAD: Poor indexing strategy
CREATE INDEX idx_user_data ON users(email, first_name, last_name, created_at);

-- ✅ GOOD: Optimized composite indexing
-- For queries filtering by email first, then sorting by created_at
CREATE INDEX idx_users_email_created ON users(email, created_at);

-- For full-text name searches
CREATE INDEX idx_users_name ON users(last_name, first_name);

-- For user status queries
CREATE INDEX idx_users_status_created ON users(status, created_at)
WHERE status IS NOT NULL;

Subquery Optimization

-- ❌ BAD: Correlated subquery
SELECT p.product_name, p.price
FROM products p
WHERE p.price > (
    SELECT AVG(price) 
    FROM products p2 
    WHERE p2.category_id = p.category_id
);

-- ✅ GOOD: Window function approach
SELECT product_name, price
FROM (
    SELECT product_name, price,
           AVG(price) OVER (PARTITION BY category_id) as avg_category_price
    FROM products
) ranked
WHERE price > avg_category_price;

📊 Performance Tuning Techniques

JOIN Optimization

-- ❌ BAD: Inefficient JOIN order and conditions
SELECT o.*, c.name, p.product_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01'
  AND c.status = 'active';

-- ✅ GOOD: Optimized JOIN with filtering
SELECT o.id, o.total_amount, c.name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id AND c.status = 'active'
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01';

Pagination Optimization

-- ❌ BAD: OFFSET-based pagination (slow for large offsets)
SELECT * FROM products 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

-- ✅ GOOD: Cursor-based pagination
SELECT * FROM products 
WHERE created_at < '2024-06-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 20;

-- Or using ID-based cursor
SELECT * FROM products 
WHERE id > 1000
ORDER BY id 
LIMIT 20;

Aggregation Optimization

-- ❌ BAD: Multiple separate aggregation queries
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders WHERE status = 'shipped';
SELECT COUNT(*) FROM orders WHERE status = 'delivered';

-- ✅ GOOD: Single query with conditional aggregation
SELECT 
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) as shipped_count,
    COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count
FROM orders;

🔍 Query Anti-Patterns

SELECT Performance Issues

-- ❌ BAD: SELECT * anti-pattern
SELECT * FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

-- ✅ GOOD: Explicit column selection
SELECT lt.id, lt.name, at.value
FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

WHERE Clause Optimization

-- ❌ BAD: Function calls in WHERE clause
SELECT * FROM orders 
WHERE UPPER(customer_email) = 'JOHN@EXAMPLE.COM';

-- ✅ GOOD: Index-friendly WHERE clause
SELECT * FROM orders 
WHERE customer_email = 'john@example.com';
-- Consider: CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));

OR vs UNION Optimization

-- ❌ BAD: Complex OR conditions
SELECT * FROM products 
WHERE (category = 'electronics' AND price < 1000)
   OR (category = 'books' AND price < 50);

-- ✅ GOOD: UNION approach for better optimization
SELECT * FROM products WHERE category = 'electronics' AND price < 1000
UNION ALL
SELECT * FROM products WHERE category = 'books' AND price < 50;

📈 Database-Agnostic Optimization

Batch Operations

-- ❌ BAD: Row-by-row operations
INSERT INTO products (name, price) VALUES ('Product 1', 10.00);
INSERT INTO products (name, price) VALUES ('Product 2', 15.00);
INSERT INTO products (name, price) VALUES ('Product 3', 20.00);

-- ✅ GOOD: Batch insert
INSERT INTO products (name, price) VALUES 
('Product 1', 10.00),
('Product 2', 15.00),
('Product 3', 20.00);

Temporary Table Usage

-- ✅ GOOD: Using temporary tables for complex operations
CREATE TEMPORARY TABLE temp_calculations AS
SELECT customer_id, 
       SUM(total_amount) as total_spent,
       COUNT(*) as order_count
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;

-- Use the temp table for further calculations
SELECT c.name, tc.total_spent, tc.order_count
FROM temp_calculations tc
JOIN customers c ON tc.customer_id = c.id
WHERE tc.total_spent > 1000;

🛠️ Index Management

Index Design Principles

-- ✅ GOOD: Covering index design
CREATE INDEX idx_orders_covering 
ON orders(customer_id, created_at) 
INCLUDE (total_amount, status);  -- SQL Server syntax
-- Or: CREATE INDEX idx_orders_covering ON orders(customer_id, created_at, total_amount, status); -- Other databases

Partial Index Strategy

-- ✅ GOOD: Partial indexes for specific conditions
CREATE INDEX idx_orders_active 
ON orders(created_at) 
WHERE status IN ('pending', 'processing');

📊 Performance Monitoring Queries

Query Performance Analysis

-- Generic approach to identify slow queries
-- (Specific syntax varies by database)

-- For MySQL:
SELECT query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC;

-- For PostgreSQL:
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC;

-- For SQL Server:
SELECT 
    qs.total_elapsed_time/qs.execution_count as avg_elapsed_time,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_time DESC;

🎯 Universal Optimization Checklist

Query Structure

Avoiding SELECT * in production queries
Using appropriate JOIN types (INNER vs LEFT/RIGHT)
Filtering early in WHERE clauses
Using EXISTS instead of IN for subqueries when appropriate
Avoiding functions in WHERE clauses that prevent index usage

Index Strategy

Creating indexes on frequently queried columns
Using composite indexes in the right column order
Avoiding over-indexing (impacts INSERT/UPDATE performance)
Using covering indexes where beneficial
Creating partial indexes for specific query patterns

Data Types and Schema

Using appropriate data types for storage efficiency
Normalizing appropriately (3NF for OLTP, denormalized for OLAP)
Using constraints to help query optimizer
Partitioning large tables when appropriate

Query Patterns

Using LIMIT/TOP for result set control
Implementing efficient pagination strategies
Using batch operations for bulk data changes
Avoiding N+1 query problems
Using prepared statements for repeated queries

Performance Testing

Testing queries with realistic data volumes
Analyzing query execution plans
Monitoring query performance over time
Setting up alerts for slow queries
Regular index usage analysis

📝 Optimization Methodology

Identify : Use database-specific tools to find slow queries
Analyze : Examine execution plans and identify bottlenecks
Optimize : Apply appropriate optimization techniques
Test : Verify performance improvements
Monitor : Continuously track performance metrics
Iterate : Regular performance review and optimization

Focus on measurable performance improvements and always test optimizations with realistic data volumes and query patterns.

Weekly Installs

8.0K

Repository

github/awesome-copilot

GitHub Stars

26.7K

First Seen

Feb 25, 2026

Security Audits

Gen Agent Trust HubPass SocketPass SnykPass

Installed on

codex7.9K

gemini-cli7.9K

opencode7.8K

github-copilot7.8K

cursor7.8K

kimi-cli7.8K

SQL性能优化助手 - 专业SQL查询调优与索引策略优化工具

🇨🇳中文介绍

SQL 性能优化助手

🎯 核心优化领域

查询性能分析

索引策略优化

相关 Skills

子查询优化

📊 性能调优技术

JOIN 优化

分页优化

聚合优化

🔍 查询反模式

SELECT 性能问题

WHERE 子句优化

OR 与 UNION 优化

📈 数据库无关优化

批量操作

临时表使用

🛠️ 索引管理

索引设计原则

部分索引策略

📊 性能监控查询

查询性能分析

🎯 通用优化清单

查询结构

索引策略

数据类型与模式

查询模式

性能测试

📝 优化方法论

🇺🇸English

SQL Performance Optimization Assistant

🎯 Core Optimization Areas

Query Performance Analysis

Index Strategy Optimization

Subquery Optimization

📊 Performance Tuning Techniques

JOIN Optimization

Pagination Optimization

Aggregation Optimization

🔍 Query Anti-Patterns

SELECT Performance Issues

WHERE Clause Optimization

OR vs UNION Optimization

📈 Database-Agnostic Optimization

Batch Operations

Temporary Table Usage

🛠️ Index Management

Index Design Principles

Partial Index Strategy

📊 Performance Monitoring Queries

Query Performance Analysis

🎯 Universal Optimization Checklist

Query Structure

Index Strategy

Data Types and Schema

Query Patterns

Performance Testing

📝 Optimization Methodology