sql-optimization

Name: sql-optimization
Author: chaterm

53installs12forks

How to install sql-optimization?

chaterm/terminal-skills/sql-optimization

$npx -y skills add chaterm/terminal-skills --skill sql-optimization

Installs into the current project.

Summary

SQL 优化与调优

Files · 1

View on GitHub

SKILL.md

1	# SQL 优化与调优
2
3	## 概述
4	慢查询分析、执行计划、索引优化等通用 SQL 优化技能。
5
6	## 执行计划分析
7
8	### MySQL EXPLAIN
9	```sql
10	-- 基础执行计划
11	EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
12
13	-- 详细执行计划
14	EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
15
16	-- JSON 格式
17	EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';
18
19	-- 关键字段解读
20	-- type: 访问类型 (system > const > eq_ref > ref > range > index > ALL)
21	-- key: 使用的索引
22	-- rows: 预估扫描行数
23	-- Extra: 额外信息 (Using index, Using filesort, Using temporary)
24	```
25
26	### PostgreSQL EXPLAIN
27	```sql
28	-- 基础执行计划
29	EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
30
31	-- 实际执行
32	EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
33
34	-- 详细信息
35	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM users WHERE email = 'test@example.com';
36
37	-- 关键指标
38	-- Seq Scan: 全表扫描
39	-- Index Scan: 索引扫描
40	-- Bitmap Index Scan: 位图索引扫描
41	-- actual time: 实际执行时间
42	-- rows: 实际返回行数
43	```
44
45	## 索引优化
46
47	### 索引设计原则
48	```sql
49	-- 1. 选择性高的列优先
50	-- 选择性 = 不同值数量 / 总行数
51	SELECT COUNT(DISTINCT column) / COUNT(*) AS selectivity FROM table;
52
53	-- 2. 复合索引列顺序
54	-- 遵循最左前缀原则
55	-- 将选择性高的列放前面
56	CREATE INDEX idx_user ON users(status, created_at, name);
57
58	-- 3. 覆盖索引
59	-- 索引包含查询所需的所有列
60	CREATE INDEX idx_covering ON orders(user_id, status, amount);
61	SELECT user_id, status, amount FROM orders WHERE user_id = 1;
62
63	-- 4. 前缀索引（长字符串）
64	CREATE INDEX idx_email ON users(email(20));
65	```
66
67	### 索引使用检查
68	```sql
69	-- MySQL: 查看索引使用情况
70	SELECT * FROM sys.schema_index_statistics WHERE table_schema = 'mydb';
71
72	-- MySQL: 未使用的索引
73	SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';
74
75	-- PostgreSQL: 索引使用统计
76	SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
77	FROM pg_stat_user_indexes
78	WHERE schemaname = 'public'
79	ORDER BY idx_scan;
80	```
81
82	### 索引失效场景
83	```sql
84	-- 1. 函数操作
85	-- 错误
86	SELECT * FROM users WHERE YEAR(created_at) = 2024;
87	-- 正确
88	SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
89
90	-- 2. 隐式类型转换
91	-- 错误 (phone 是 varchar)
92	SELECT * FROM users WHERE phone = 13800138000;
93	-- 正确
94	SELECT * FROM users WHERE phone = '13800138000';
95
96	-- 3. LIKE 前缀通配符
97	-- 错误
98	SELECT * FROM users WHERE name LIKE '%john%';
99	-- 正确
100	SELECT * FROM users WHERE name LIKE 'john%';
101
102	-- 4. OR 条件
103	-- 可能不走索引
104	SELECT * FROM users WHERE status = 1 OR name = 'john';
105	-- 改写为 UNION
106	SELECT * FROM users WHERE status = 1
107	UNION
108	SELECT * FROM users WHERE name = 'john';
109
110	-- 5. NOT IN / NOT EXISTS
111	-- 尽量避免，改用 LEFT JOIN
112	SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);
113	-- 改写
114	SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL;
115	```
116
117	## 查询优化
118
119	### SELECT 优化
120	```sql
121	-- 1. 只查询需要的列
122	-- 错误
123	SELECT * FROM users;
124	-- 正确
125	SELECT id, name, email FROM users;
126
127	-- 2. 避免 SELECT DISTINCT（考虑是否真的需要）
128	-- 检查是否有重复数据的根本原因
129
130	-- 3. 使用 LIMIT
131	SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;
132
133	-- 4. 分页优化
134	-- 错误（大偏移量性能差）
135	SELECT * FROM users LIMIT 10000, 20;
136	-- 正确（使用游标分页）
137	SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 20;
138	```
139
140	### JOIN 优化
141	```sql
142	-- 1. 小表驱动大表
143	-- 确保 JOIN 顺序合理
144
145	-- 2. 确保 JOIN 列有索引
146	SELECT u.name, o.amount
147	FROM users u
148	JOIN orders o ON u.id = o.user_id -- user_id 需要索引
149	WHERE u.status = 1;
150
151	-- 3. 避免过多 JOIN
152	-- 超过 3-4 个表的 JOIN 考虑拆分查询
153
154	-- 4. 使用 STRAIGHT_JOIN 强制顺序（MySQL）
155	SELECT STRAIGHT_JOIN u.name, o.amount
156	FROM users u
157	JOIN orders o ON u.id = o.user_id;
158	```
159
160	### 子查询优化
161	```sql
162	-- 1. 将子查询改为 JOIN
163	-- 错误
164	SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
165	-- 正确
166	SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100;
167
168	-- 2. EXISTS 替代 IN（大数据集）
169	SELECT * FROM users u WHERE EXISTS (
170	SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100
171	);
172	```
173
174	## 慢查询分析
175
176	### MySQL 慢查询
177	```sql
178	-- 开启慢查询日志
179	SET GLOBAL slow_query_log = 'ON';
180	SET GLOBAL long_query_time = 1;
181	SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
182
183	-- 查看配置
184	SHOW VARIABLES LIKE 'slow_query%';
185	SHOW VARIABLES LIKE 'long_query_time';
186
187	-- 分析慢查询日志
188	-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
189	```
190
191	### PostgreSQL 慢查询
192	```sql
193	-- 配置 postgresql.conf
194	-- log_min_duration_statement = 1000 # 记录超过1秒的查询
195
196	-- 使用 pg_stat_statements
197	CREATE EXTENSION pg_stat_statements;
198
199	SELECT query, calls, total_time, mean_time, rows
200	FROM pg_stat_statements
201	ORDER BY total_time DESC
202	LIMIT 10;
203	```
204
205	## 常见场景
206
207	### 场景 1：大表分页
208	```sql
209	-- 使用延迟关联
210	SELECT u.* FROM users u
211	JOIN (SELECT id FROM users ORDER BY created_at DESC LIMIT 10000, 20) t
212	ON u.id = t.id;
213
214	-- 使用游标分页
215	SELECT * FROM users
216	WHERE id > last_seen_id
217	ORDER BY id
218	LIMIT 20;
219	```
220
221	### 场景 2：批量更新
222	```sql
223	-- 分批更新，避免长事务
224	-- 每次更新 1000 条
225	UPDATE users SET status = 1 WHERE id BETWEEN 1 AND 1000;
226	UPDATE users SET status = 1 WHERE id BETWEEN 1001 AND 2000;
227	-- ...
228
229	-- 或使用存储过程循环
230	```
231
232	### 场景 3：统计查询优化
233	```sql
234	-- 使用汇总表
235	CREATE TABLE daily_stats (
236	date DATE PRIMARY KEY,
237	total_orders INT,
238	total_amount DECIMAL(10,2)
239	);
240
241	-- 定时任务更新汇总表
242	INSERT INTO daily_stats
243	SELECT DATE(created_at), COUNT(*), SUM

People who liked this also viewed

6 picks

Type

azure-storageAzure Storage Services including Blob Storage, File Shares, Queue Storage, Table Storage, and Data Lake. Answers questions about storage access tiers (hot, cool, cold, archive), when to use each tier,SkillsJul 2026471k1.3k
supabase-postgres-best-practicesPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.SkillsJul 2026302k2.4k
supabaseUse when doing ANY task involving Supabase. Triggers: Supabase products (Database, Auth, Edge Functions, Realtime, Storage, Vectors, Cron, Queues); client libraries and SSR integrations (supabase-js, SkillsJul 2026179k2.4k
convex-quickstartCreates or adds Convex to an app. Use for new Convex projects, npm create convex@latest, frontend setup, env vars, or the first npx convex dev run.SkillsJul 202690k39
convex-performance-auditAudits Convex performance for reads, subscriptions, write contention, and function limits. Use for slow features, insights findings, OCC conflicts, or read amplification.SkillsJul 202689k39
convex-setup-authSets up Convex auth, identity mapping, and access control. Use for login, auth providers, users tables, protected functions, or roles in a Convex app.SkillsJul 202689k39

sql-optimization

53installs12forks

View on GitHub

How to install sql-optimization?

chaterm/terminal-skills/sql-optimization

$npx -y skills add chaterm/terminal-skills --skill sql-optimization

Installs into the current project.

Summary

SQL 优化与调优

Files · 1

View on GitHub

SKILL.md

1	# SQL 优化与调优
2
3	## 概述
4	慢查询分析、执行计划、索引优化等通用 SQL 优化技能。
5
6	## 执行计划分析
7
8	### MySQL EXPLAIN
9	```sql
10	-- 基础执行计划
11	EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
12
13	-- 详细执行计划
14	EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
15
16	-- JSON 格式
17	EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';
18
19	-- 关键字段解读
20	-- type: 访问类型 (system > const > eq_ref > ref > range > index > ALL)
21	-- key: 使用的索引
22	-- rows: 预估扫描行数
23	-- Extra: 额外信息 (Using index, Using filesort, Using temporary)
24	```
25
26	### PostgreSQL EXPLAIN
27	```sql
28	-- 基础执行计划
29	EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
30
31	-- 实际执行
32	EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
33
34	-- 详细信息
35	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM users WHERE email = 'test@example.com';
36
37	-- 关键指标
38	-- Seq Scan: 全表扫描
39	-- Index Scan: 索引扫描
40	-- Bitmap Index Scan: 位图索引扫描
41	-- actual time: 实际执行时间
42	-- rows: 实际返回行数
43	```
44
45	## 索引优化
46
47	### 索引设计原则
48	```sql
49	-- 1. 选择性高的列优先
50	-- 选择性 = 不同值数量 / 总行数
51	SELECT COUNT(DISTINCT column) / COUNT(*) AS selectivity FROM table;
52
53	-- 2. 复合索引列顺序
54	-- 遵循最左前缀原则
55	-- 将选择性高的列放前面
56	CREATE INDEX idx_user ON users(status, created_at, name);
57
58	-- 3. 覆盖索引
59	-- 索引包含查询所需的所有列
60	CREATE INDEX idx_covering ON orders(user_id, status, amount);
61	SELECT user_id, status, amount FROM orders WHERE user_id = 1;
62
63	-- 4. 前缀索引（长字符串）
64	CREATE INDEX idx_email ON users(email(20));
65	```
66
67	### 索引使用检查
68	```sql
69	-- MySQL: 查看索引使用情况
70	SELECT * FROM sys.schema_index_statistics WHERE table_schema = 'mydb';
71
72	-- MySQL: 未使用的索引
73	SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';
74
75	-- PostgreSQL: 索引使用统计
76	SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
77	FROM pg_stat_user_indexes
78	WHERE schemaname = 'public'
79	ORDER BY idx_scan;
80	```
81
82	### 索引失效场景
83	```sql
84	-- 1. 函数操作
85	-- 错误
86	SELECT * FROM users WHERE YEAR(created_at) = 2024;
87	-- 正确
88	SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
89
90	-- 2. 隐式类型转换
91	-- 错误 (phone 是 varchar)
92	SELECT * FROM users WHERE phone = 13800138000;
93	-- 正确
94	SELECT * FROM users WHERE phone = '13800138000';
95
96	-- 3. LIKE 前缀通配符
97	-- 错误
98	SELECT * FROM users WHERE name LIKE '%john%';
99	-- 正确
100	SELECT * FROM users WHERE name LIKE 'john%';
101
102	-- 4. OR 条件
103	-- 可能不走索引
104	SELECT * FROM users WHERE status = 1 OR name = 'john';
105	-- 改写为 UNION
106	SELECT * FROM users WHERE status = 1
107	UNION
108	SELECT * FROM users WHERE name = 'john';
109
110	-- 5. NOT IN / NOT EXISTS
111	-- 尽量避免，改用 LEFT JOIN
112	SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);
113	-- 改写
114	SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL;
115	```
116
117	## 查询优化
118
119	### SELECT 优化
120	```sql
121	-- 1. 只查询需要的列
122	-- 错误
123	SELECT * FROM users;
124	-- 正确
125	SELECT id, name, email FROM users;
126
127	-- 2. 避免 SELECT DISTINCT（考虑是否真的需要）
128	-- 检查是否有重复数据的根本原因
129
130	-- 3. 使用 LIMIT
131	SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;
132
133	-- 4. 分页优化
134	-- 错误（大偏移量性能差）
135	SELECT * FROM users LIMIT 10000, 20;
136	-- 正确（使用游标分页）
137	SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 20;
138	```
139
140	### JOIN 优化
141	```sql
142	-- 1. 小表驱动大表
143	-- 确保 JOIN 顺序合理
144
145	-- 2. 确保 JOIN 列有索引
146	SELECT u.name, o.amount
147	FROM users u
148	JOIN orders o ON u.id = o.user_id -- user_id 需要索引
149	WHERE u.status = 1;
150
151	-- 3. 避免过多 JOIN
152	-- 超过 3-4 个表的 JOIN 考虑拆分查询
153
154	-- 4. 使用 STRAIGHT_JOIN 强制顺序（MySQL）
155	SELECT STRAIGHT_JOIN u.name, o.amount
156	FROM users u
157	JOIN orders o ON u.id = o.user_id;
158	```
159
160	### 子查询优化
161	```sql
162	-- 1. 将子查询改为 JOIN
163	-- 错误
164	SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
165	-- 正确
166	SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100;
167
168	-- 2. EXISTS 替代 IN（大数据集）
169	SELECT * FROM users u WHERE EXISTS (
170	SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100
171	);
172	```
173
174	## 慢查询分析
175
176	### MySQL 慢查询
177	```sql
178	-- 开启慢查询日志
179	SET GLOBAL slow_query_log = 'ON';
180	SET GLOBAL long_query_time = 1;
181	SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
182
183	-- 查看配置
184	SHOW VARIABLES LIKE 'slow_query%';
185	SHOW VARIABLES LIKE 'long_query_time';
186
187	-- 分析慢查询日志
188	-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
189	```
190
191	### PostgreSQL 慢查询
192	```sql
193	-- 配置 postgresql.conf
194	-- log_min_duration_statement = 1000 # 记录超过1秒的查询
195
196	-- 使用 pg_stat_statements
197	CREATE EXTENSION pg_stat_statements;
198
199	SELECT query, calls, total_time, mean_time, rows
200	FROM pg_stat_statements
201	ORDER BY total_time DESC
202	LIMIT 10;
203	```
204
205	## 常见场景
206
207	### 场景 1：大表分页
208	```sql
209	-- 使用延迟关联
210	SELECT u.* FROM users u
211	JOIN (SELECT id FROM users ORDER BY created_at DESC LIMIT 10000, 20) t
212	ON u.id = t.id;
213
214	-- 使用游标分页
215	SELECT * FROM users
216	WHERE id > last_seen_id
217	ORDER BY id
218	LIMIT 20;
219	```
220
221	### 场景 2：批量更新
222	```sql
223	-- 分批更新，避免长事务
224	-- 每次更新 1000 条
225	UPDATE users SET status = 1 WHERE id BETWEEN 1 AND 1000;
226	UPDATE users SET status = 1 WHERE id BETWEEN 1001 AND 2000;
227	-- ...
228
229	-- 或使用存储过程循环
230	```
231
232	### 场景 3：统计查询优化
233	```sql
234	-- 使用汇总表
235	CREATE TABLE daily_stats (
236	date DATE PRIMARY KEY,
237	total_orders INT,
238	total_amount DECIMAL(10,2)
239	);
240
241	-- 定时任务更新汇总表
242	INSERT INTO daily_stats
243	SELECT DATE(created_at), COUNT(*), SUM

People who liked this also viewed

6 picks

Type