query-expert by jamesrochabrun/skills
npx skills add https://github.com/jamesrochabrun/skills --skill query-expert精通 SQL 和 NoSQL 系统的数据库查询。生成优化的查询,使用 EXPLAIN 计划分析性能,设计有效的索引,并排查慢查询问题。
帮助您编写高效、性能良好的数据库查询:
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
-- ✅ 仅选择需要的列
SELECT
user_id,
email,
created_at
FROM users
WHERE status = 'active'
AND created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 100;
-- ❌ 避免 SELECT *
SELECT * FROM users; -- 浪费资源
-- INNER JOIN (最常见)
SELECT
o.order_id,
o.total,
c.name AS customer_name,
c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at >= '2024-01-01';
-- LEFT JOIN (包含所有左表行)
SELECT
c.customer_id,
c.name,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
-- 多重 JOIN
SELECT
o.order_id,
c.name AS customer_name,
p.product_name,
oi.quantity,
oi.price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed';
-- WHERE 中的子查询
SELECT name, email
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
WHERE total > 1000
);
-- 相关子查询
SELECT
c.name,
(SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;
-- ✅ 更好:使用 JOIN 替代
SELECT
c.name,
COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
-- 带聚合函数的 GROUP BY
SELECT
category,
COUNT(*) AS product_count,
AVG(price) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price,
SUM(stock_quantity) AS total_stock
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_price DESC;
-- 多重 GROUP BY 列
SELECT
DATE_TRUNC('month', created_at) AS month,
category,
SUM(total) AS monthly_sales
FROM orders
GROUP BY DATE_TRUNC('month', created_at), category
ORDER BY month DESC, monthly_sales DESC;
-- 用于小计的 ROLLUP
SELECT
COALESCE(category, 'TOTAL') AS category,
COALESCE(brand, 'All Brands') AS brand,
SUM(sales) AS total_sales
FROM products
GROUP BY ROLLUP(category, brand);
-- ROW_NUMBER
SELECT
customer_id,
order_date,
total,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS order_rank
FROM orders;
-- 累计总计
SELECT
order_date,
total,
SUM(total) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;
-- RANK 与 DENSE_RANK
SELECT
product_name,
sales,
RANK() OVER (ORDER BY sales DESC) AS rank,
DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rank,
NTILE(4) OVER (ORDER BY sales DESC) AS quartile
FROM products;
-- LAG 和 LEAD
SELECT
order_date,
total,
LAG(total, 1) OVER (ORDER BY order_date) AS prev_total,
LEAD(total, 1) OVER (ORDER BY order_date) AS next_total,
total - LAG(total, 1) OVER (ORDER BY order_date) AS change
FROM orders;
-- 简单 CTE
WITH active_customers AS (
SELECT customer_id, name, email
FROM customers
WHERE status = 'active'
)
SELECT
ac.name,
COUNT(o.order_id) AS order_count
FROM active_customers ac
LEFT JOIN orders o ON ac.customer_id = o.customer_id
GROUP BY ac.customer_id, ac.name;
-- 多重 CTE
WITH
monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total) AS sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
),
avg_monthly AS (
SELECT AVG(sales) AS avg_sales
FROM monthly_sales
)
SELECT
ms.month,
ms.sales,
am.avg_sales,
ms.sales - am.avg_sales AS variance
FROM monthly_sales ms
CROSS JOIN avg_monthly am
ORDER BY ms.month;
-- 递归 CTE (层次结构)
WITH RECURSIVE org_tree AS (
-- 基础情况
SELECT
employee_id,
name,
manager_id,
1 AS level,
ARRAY[employee_id] AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归情况
SELECT
e.employee_id,
e.name,
e.manager_id,
ot.level + 1,
ot.path || e.employee_id
FROM employees e
INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree ORDER BY path;
-- 在频繁查询的列上创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
-- 复合索引 (顺序很重要!)
CREATE INDEX idx_orders_composite
ON orders(status, customer_id, order_date);
-- ✅ 此查询使用索引
SELECT * FROM orders
WHERE status = 'pending'
AND customer_id = 123
AND order_date > '2024-01-01';
-- ❌ 此查询不使用索引 (跳过了第一列)
SELECT * FROM orders
WHERE customer_id = 123;
-- 部分/过滤索引 (更小,更快)
CREATE INDEX idx_active_users
ON users(email)
WHERE status = 'active';
-- 覆盖索引 (包含所有需要的列)
CREATE INDEX idx_users_covering
ON users(email)
INCLUDE (name, created_at);
-- ❌ 差:检索所有列
SELECT * FROM users;
-- ✅ 好:仅选择需要的列
SELECT user_id, email, name FROM users;
-- ✅ 好:对连接更高效
SELECT
u.user_id,
u.email,
o.order_id,
o.total
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
-- ❌ 差:在 JOIN 后过滤
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed';
-- ✅ 好:在 JOIN 前过滤
SELECT u.name, o.total
FROM users u
INNER JOIN (
SELECT user_id, total
FROM orders
WHERE status = 'completed'
) o ON u.user_id = o.user_id;
-- ✅ 更好:对 INNER JOIN 使用 WHERE
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed';
-- ❌ 较慢:带子查询的 IN
SELECT name FROM customers
WHERE customer_id IN (
SELECT customer_id FROM orders WHERE total > 1000
);
-- ✅ 更快:EXISTS
SELECT name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.total > 1000
);
-- ❌ 差:函数阻止索引使用
SELECT * FROM users
WHERE LOWER(email) = 'john@example.com';
-- ✅ 好:使用函数索引
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- 或使用不区分大小写的排序规则
SELECT * FROM users
WHERE email = 'john@example.com' COLLATE utf8_general_ci;
-- ✅ 使用 LIMIT/TOP 进行分页
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
-- ✅ 使用 WHERE 尽早减少行数
SELECT * FROM orders
WHERE created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;
-- ❌ 差:多个单次插入
INSERT INTO users (name, email) VALUES ('User1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('User2', 'user2@example.com');
-- ✅ 好:批量插入
INSERT INTO users (name, email) VALUES
('User1', 'user1@example.com'),
('User2', 'user2@example.com'),
('User3', 'user3@example.com');
-- ✅ 好:批量更新
UPDATE products
SET price = price * 1.1
WHERE category IN ('Electronics', 'Computers');
-- 简单 EXPLAIN
EXPLAIN
SELECT * FROM orders WHERE customer_id = 123;
-- EXPLAIN ANALYZE (实际运行查询)
EXPLAIN ANALYZE
SELECT
c.name,
COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
-- 注意查看:
-- - Seq Scan (差,需要索引)
-- - Index Scan (好)
-- - Bitmap Heap Scan (对多行好)
-- - Hash Join 与 Nested Loop
-- - 高成本数字
-- EXPLAIN
EXPLAIN
SELECT * FROM orders WHERE customer_id = 123;
-- EXPLAIN ANALYZE (MySQL 8.0.18+)
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;
-- 注意查看:
-- - type: ALL (表扫描,差)
-- - type: index (索引扫描,好)
-- - type: ref (索引查找,很好)
-- - Extra: Using filesort (可能需要索引)
-- - Extra: Using temporary (可能需要优化)
✅ 为这些列创建索引:
❌ 不要为这些列创建索引:
-- B-Tree (默认,最常见)
CREATE INDEX idx_users_email ON users(email);
-- 哈希索引 (仅用于相等,PostgreSQL)
CREATE INDEX idx_users_email_hash ON users USING HASH(email);
-- GIN (全文搜索,数组,JSONB)
CREATE INDEX idx_posts_content_gin
ON posts USING GIN(to_tsvector('english', content));
-- GiST (几何,全文)
CREATE INDEX idx_locations_gist
ON locations USING GIST(coordinates);
-- 部分索引 (过滤)
CREATE INDEX idx_orders_pending
ON orders(customer_id)
WHERE status = 'pending';
-- 表达式索引
CREATE INDEX idx_users_email_domain
ON users((email ~~ '%@gmail.com%'));
-- 索引列顺序很重要!
CREATE INDEX idx_orders_search
ON orders(status, customer_id, created_at);
-- ✅ 使用索引 (最左列)
WHERE status = 'completed'
-- ✅ 使用索引 (最左列)
WHERE status = 'completed' AND customer_id = 123
-- ✅ 使用完整索引
WHERE status = 'completed'
AND customer_id = 123
AND created_at > '2024-01-01'
-- ❌ 不使用索引 (跳过第一列)
WHERE customer_id = 123
-- ❌ 不使用索引 (跳过第一列)
WHERE created_at > '2024-01-01'
// 基本 find
db.users.find({ status: 'active' })
// 带投影的 find
db.users.find(
{ status: 'active' },
{ name: 1, email: 1, _id: 0 }
)
// 带操作符的 find
db.orders.find({
total: { $gt: 100, $lt: 1000 },
status: { $in: ['pending', 'processing'] },
'customer.city': 'New York'
})
// 带排序和限制的 find
db.products.find({ category: 'Electronics' })
.sort({ price: -1 })
.limit(10)
// 计数
db.users.countDocuments({ status: 'active' })
// 分组和计数
db.orders.aggregate([
{ $match: { status: 'completed' } },
{ $group: {
_id: '$customer_id',
total_orders: { $sum: 1 },
total_spent: { $sum: '$total' },
avg_order: { $avg: '$total' }
}},
{ $sort: { total_spent: -1 } },
{ $limit: 10 }
])
// Lookup (JOIN)
db.orders.aggregate([
{ $lookup: {
from: 'customers',
localField: 'customer_id',
foreignField: '_id',
as: 'customer'
}},
{ $unwind: '$customer' },
{ $project: {
order_id: 1,
total: 1,
'customer.name': 1,
'customer.email': 1
}}
])
// 复杂聚合
db.sales.aggregate([
// 过滤
{ $match: {
date: { $gte: ISODate('2024-01-01') }
}},
// 添加计算字段
{ $addFields: {
month: { $month: '$date' },
year: { $year: '$date' }
}},
// 按月分组
{ $group: {
_id: { year: '$year', month: '$month' },
total_sales: { $sum: '$amount' },
order_count: { $sum: 1 },
avg_sale: { $avg: '$amount' }
}},
// 排序
{ $sort: { '_id.year': 1, '_id.month': 1 } },
// 重塑
{ $project: {
_id: 0,
date: {
$concat: [
{ $toString: '$_id.year' },
'-',
{ $toString: '$_id.month' }
]
},
total_sales: 1,
order_count: 1,
avg_sale: { $round: ['$avg_sale', 2] }
}}
])
// 单字段索引
db.users.createIndex({ email: 1 })
// 复合索引
db.orders.createIndex({ customer_id: 1, created_at: -1 })
// 唯一索引
db.users.createIndex({ email: 1 }, { unique: true })
// 部分索引
db.orders.createIndex(
{ customer_id: 1 },
{ partialFilterExpression: { status: 'active' } }
)
// 文本索引
db.products.createIndex({ name: 'text', description: 'text' })
// TTL 索引 (超时后自动删除)
db.sessions.createIndex(
{ created_at: 1 },
{ expireAfterSeconds: 3600 }
)
// 列出索引
db.users.getIndexes()
// 分析查询性能
db.orders.find({ customer_id: 123 }).explain('executionStats')
# 基本查询
query {
users {
id
name
email
}
}
# 带参数的查询
query {
user(id: "123") {
name
email
orders {
id
total
status
}
}
}
# 带变量的查询
query GetUser($userId: ID!) {
user(id: $userId) {
name
email
orders(limit: 10, status: COMPLETED) {
id
total
createdAt
}
}
}
# 片段 (可复用字段)
fragment UserFields on User {
id
name
email
createdAt
}
query {
user(id: "123") {
...UserFields
orders {
id
total
}
}
}
# 使用 DataLoader 避免 N+1 查询
query {
orders {
id
total
customer { # 由 DataLoader 批量处理
name
email
}
}
}
-- 差:N+1 查询
SELECT * FROM customers; -- 1 次查询
-- 然后对每个客户:
SELECT * FROM orders WHERE customer_id = ?; -- N 次查询
-- 好:单次 JOIN 查询
SELECT
c.customer_id,
c.name,
o.order_id,
o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- 差:无法有效使用索引
SELECT * FROM products
WHERE name = 'iPhone' OR category = 'Electronics';
-- 好:使用 UNION
SELECT * FROM products WHERE name = 'iPhone'
UNION
SELECT * FROM products WHERE category = 'Electronics';
-- 差:'123' 是字符串,user_id 是整数
SELECT * FROM users WHERE user_id = '123';
-- 好:使用正确的类型
SELECT * FROM users WHERE user_id = 123;
"过早优化是万恶之源,但慢查询是所有挫败感的根源。"
每周安装次数
95
代码仓库
GitHub 星标数
103
首次出现
2026年1月22日
安全审计
安装于
codex79
opencode77
gemini-cli75
cursor72
github-copilot71
amp64
Master database queries across SQL and NoSQL systems. Generate optimized queries, analyze performance with EXPLAIN plans, design effective indexes, and troubleshoot slow queries.
Helps you write efficient, performant database queries:
-- ✅ Select only needed columns
SELECT
user_id,
email,
created_at
FROM users
WHERE status = 'active'
AND created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 100;
-- ❌ Avoid SELECT *
SELECT * FROM users; -- Wastes resources
-- INNER JOIN (most common)
SELECT
o.order_id,
o.total,
c.name AS customer_name,
c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at >= '2024-01-01';
-- LEFT JOIN (include all left rows)
SELECT
c.customer_id,
c.name,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
-- Multiple JOINs
SELECT
o.order_id,
c.name AS customer_name,
p.product_name,
oi.quantity,
oi.price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed';
-- Subquery in WHERE
SELECT name, email
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
WHERE total > 1000
);
-- Correlated subquery
SELECT
c.name,
(SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;
-- ✅ Better: Use JOIN instead
SELECT
c.name,
COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
-- GROUP BY with aggregates
SELECT
category,
COUNT(*) AS product_count,
AVG(price) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price,
SUM(stock_quantity) AS total_stock
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_price DESC;
-- Multiple GROUP BY columns
SELECT
DATE_TRUNC('month', created_at) AS month,
category,
SUM(total) AS monthly_sales
FROM orders
GROUP BY DATE_TRUNC('month', created_at), category
ORDER BY month DESC, monthly_sales DESC;
-- ROLLUP for subtotals
SELECT
COALESCE(category, 'TOTAL') AS category,
COALESCE(brand, 'All Brands') AS brand,
SUM(sales) AS total_sales
FROM products
GROUP BY ROLLUP(category, brand);
-- ROW_NUMBER
SELECT
customer_id,
order_date,
total,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS order_rank
FROM orders;
-- Running totals
SELECT
order_date,
total,
SUM(total) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;
-- RANK vs DENSE_RANK
SELECT
product_name,
sales,
RANK() OVER (ORDER BY sales DESC) AS rank,
DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rank,
NTILE(4) OVER (ORDER BY sales DESC) AS quartile
FROM products;
-- LAG and LEAD
SELECT
order_date,
total,
LAG(total, 1) OVER (ORDER BY order_date) AS prev_total,
LEAD(total, 1) OVER (ORDER BY order_date) AS next_total,
total - LAG(total, 1) OVER (ORDER BY order_date) AS change
FROM orders;
-- Simple CTE
WITH active_customers AS (
SELECT customer_id, name, email
FROM customers
WHERE status = 'active'
)
SELECT
ac.name,
COUNT(o.order_id) AS order_count
FROM active_customers ac
LEFT JOIN orders o ON ac.customer_id = o.customer_id
GROUP BY ac.customer_id, ac.name;
-- Multiple CTEs
WITH
monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total) AS sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
),
avg_monthly AS (
SELECT AVG(sales) AS avg_sales
FROM monthly_sales
)
SELECT
ms.month,
ms.sales,
am.avg_sales,
ms.sales - am.avg_sales AS variance
FROM monthly_sales ms
CROSS JOIN avg_monthly am
ORDER BY ms.month;
-- Recursive CTE (hierarchies)
WITH RECURSIVE org_tree AS (
-- Base case
SELECT
employee_id,
name,
manager_id,
1 AS level,
ARRAY[employee_id] AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive case
SELECT
e.employee_id,
e.name,
e.manager_id,
ot.level + 1,
ot.path || e.employee_id
FROM employees e
INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree ORDER BY path;
-- Create index on frequently queried columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
-- Composite index (order matters!)
CREATE INDEX idx_orders_composite
ON orders(status, customer_id, order_date);
-- ✅ This query uses the index
SELECT * FROM orders
WHERE status = 'pending'
AND customer_id = 123
AND order_date > '2024-01-01';
-- ❌ This doesn't use the index (skips first column)
SELECT * FROM orders
WHERE customer_id = 123;
-- Partial/Filtered index (smaller, faster)
CREATE INDEX idx_active_users
ON users(email)
WHERE status = 'active';
-- Covering index (includes all needed columns)
CREATE INDEX idx_users_covering
ON users(email)
INCLUDE (name, created_at);
-- ❌ Bad: Retrieves all columns
SELECT * FROM users;
-- ✅ Good: Select only needed columns
SELECT user_id, email, name FROM users;
-- ✅ Good: More efficient for joins
SELECT
u.user_id,
u.email,
o.order_id,
o.total
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
-- ❌ Bad: Filtering after JOIN
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed';
-- ✅ Good: Filter before JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN (
SELECT user_id, total
FROM orders
WHERE status = 'completed'
) o ON u.user_id = o.user_id;
-- ✅ Even better: Use WHERE with INNER JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed';
-- ❌ Slower: IN with subquery
SELECT name FROM customers
WHERE customer_id IN (
SELECT customer_id FROM orders WHERE total > 1000
);
-- ✅ Faster: EXISTS
SELECT name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.total > 1000
);
-- ❌ Bad: Function prevents index usage
SELECT * FROM users
WHERE LOWER(email) = 'john@example.com';
-- ✅ Good: Use functional index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Or use case-insensitive collation
SELECT * FROM users
WHERE email = 'john@example.com' COLLATE utf8_general_ci;
-- ✅ Use LIMIT/TOP for pagination
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
-- ✅ Use WHERE to reduce rows early
SELECT * FROM orders
WHERE created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;
-- ❌ Bad: Multiple single inserts
INSERT INTO users (name, email) VALUES ('User1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('User2', 'user2@example.com');
-- ✅ Good: Batch insert
INSERT INTO users (name, email) VALUES
('User1', 'user1@example.com'),
('User2', 'user2@example.com'),
('User3', 'user3@example.com');
-- ✅ Good: Batch update
UPDATE products
SET price = price * 1.1
WHERE category IN ('Electronics', 'Computers');
-- Simple EXPLAIN
EXPLAIN
SELECT * FROM orders WHERE customer_id = 123;
-- EXPLAIN ANALYZE (actually runs query)
EXPLAIN ANALYZE
SELECT
c.name,
COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
-- Look for:
-- - Seq Scan (bad, needs index)
-- - Index Scan (good)
-- - Bitmap Heap Scan (good for multiple rows)
-- - Hash Join vs Nested Loop
-- - High cost numbers
-- EXPLAIN
EXPLAIN
SELECT * FROM orders WHERE customer_id = 123;
-- EXPLAIN ANALYZE (MySQL 8.0.18+)
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;
-- Look for:
-- - type: ALL (table scan, bad)
-- - type: index (index scan, good)
-- - type: ref (index lookup, great)
-- - Extra: Using filesort (may need index)
-- - Extra: Using temporary (may need optimization)
✅ Index these columns:
❌ Don't index:
-- B-Tree (default, most common)
CREATE INDEX idx_users_email ON users(email);
-- Hash index (equality only, PostgreSQL)
CREATE INDEX idx_users_email_hash ON users USING HASH(email);
-- GIN (full-text search, arrays, JSONB)
CREATE INDEX idx_posts_content_gin
ON posts USING GIN(to_tsvector('english', content));
-- GiST (geometric, full-text)
CREATE INDEX idx_locations_gist
ON locations USING GIST(coordinates);
-- Partial index (filtered)
CREATE INDEX idx_orders_pending
ON orders(customer_id)
WHERE status = 'pending';
-- Expression index
CREATE INDEX idx_users_email_domain
ON users((email ~~ '%@gmail.com%'));
-- Index column order matters!
CREATE INDEX idx_orders_search
ON orders(status, customer_id, created_at);
-- ✅ Uses index (left-most column)
WHERE status = 'completed'
-- ✅ Uses index (left-most columns)
WHERE status = 'completed' AND customer_id = 123
-- ✅ Uses full index
WHERE status = 'completed'
AND customer_id = 123
AND created_at > '2024-01-01'
-- ❌ Doesn't use index (skips first column)
WHERE customer_id = 123
-- ❌ Doesn't use index (skips first column)
WHERE created_at > '2024-01-01'
// Basic find
db.users.find({ status: 'active' })
// Find with projection
db.users.find(
{ status: 'active' },
{ name: 1, email: 1, _id: 0 }
)
// Find with operators
db.orders.find({
total: { $gt: 100, $lt: 1000 },
status: { $in: ['pending', 'processing'] },
'customer.city': 'New York'
})
// Find with sort and limit
db.products.find({ category: 'Electronics' })
.sort({ price: -1 })
.limit(10)
// Count
db.users.countDocuments({ status: 'active' })
// Group and count
db.orders.aggregate([
{ $match: { status: 'completed' } },
{ $group: {
_id: '$customer_id',
total_orders: { $sum: 1 },
total_spent: { $sum: '$total' },
avg_order: { $avg: '$total' }
}},
{ $sort: { total_spent: -1 } },
{ $limit: 10 }
])
// Lookup (JOIN)
db.orders.aggregate([
{ $lookup: {
from: 'customers',
localField: 'customer_id',
foreignField: '_id',
as: 'customer'
}},
{ $unwind: '$customer' },
{ $project: {
order_id: 1,
total: 1,
'customer.name': 1,
'customer.email': 1
}}
])
// Complex aggregation
db.sales.aggregate([
// Filter
{ $match: {
date: { $gte: ISODate('2024-01-01') }
}},
// Add computed fields
{ $addFields: {
month: { $month: '$date' },
year: { $year: '$date' }
}},
// Group by month
{ $group: {
_id: { year: '$year', month: '$month' },
total_sales: { $sum: '$amount' },
order_count: { $sum: 1 },
avg_sale: { $avg: '$amount' }
}},
// Sort
{ $sort: { '_id.year': 1, '_id.month': 1 } },
// Reshape
{ $project: {
_id: 0,
date: {
$concat: [
{ $toString: '$_id.year' },
'-',
{ $toString: '$_id.month' }
]
},
total_sales: 1,
order_count: 1,
avg_sale: { $round: ['$avg_sale', 2] }
}}
])
// Single field index
db.users.createIndex({ email: 1 })
// Compound index
db.orders.createIndex({ customer_id: 1, created_at: -1 })
// Unique index
db.users.createIndex({ email: 1 }, { unique: true })
// Partial index
db.orders.createIndex(
{ customer_id: 1 },
{ partialFilterExpression: { status: 'active' } }
)
// Text index
db.products.createIndex({ name: 'text', description: 'text' })
// TTL index (auto-delete after time)
db.sessions.createIndex(
{ created_at: 1 },
{ expireAfterSeconds: 3600 }
)
// List indexes
db.users.getIndexes()
// Analyze query performance
db.orders.find({ customer_id: 123 }).explain('executionStats')
# Basic query
query {
users {
id
name
email
}
}
# Query with arguments
query {
user(id: "123") {
name
email
orders {
id
total
status
}
}
}
# Query with variables
query GetUser($userId: ID!) {
user(id: $userId) {
name
email
orders(limit: 10, status: COMPLETED) {
id
total
createdAt
}
}
}
# Fragments (reusable fields)
fragment UserFields on User {
id
name
email
createdAt
}
query {
user(id: "123") {
...UserFields
orders {
id
total
}
}
}
# Avoid N+1 queries with DataLoader
query {
orders {
id
total
customer { # Batched by DataLoader
name
email
}
}
}
-- Bad: N+1 queries
SELECT * FROM customers; -- 1 query
-- Then for each customer:
SELECT * FROM orders WHERE customer_id = ?; -- N queries
-- Good: Single JOIN query
SELECT
c.customer_id,
c.name,
o.order_id,
o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Bad: Can't use indexes effectively
SELECT * FROM products
WHERE name = 'iPhone' OR category = 'Electronics';
-- Good: Use UNION
SELECT * FROM products WHERE name = 'iPhone'
UNION
SELECT * FROM products WHERE category = 'Electronics';
-- Bad: '123' is string, user_id is integer
SELECT * FROM users WHERE user_id = '123';
-- Good: Use correct type
SELECT * FROM users WHERE user_id = 123;
"Premature optimization is the root of all evil, but slow queries are the root of all frustration."
Weekly Installs
95
Repository
GitHub Stars
103
First Seen
Jan 22, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
codex79
opencode77
gemini-cli75
cursor72
github-copilot71
amp64
React 组合模式指南:Vercel 组件架构最佳实践,提升代码可维护性
122,000 周安装