SQL · 数据库 / 查询参考
SQL速查
覆盖 SQL 核心语法、四种 JOIN 语义与常用窗口函数分类，从增删改查到排名偏移分析，写查询、做报表时即查即用。
8速查小节
13核心语法
4JOIN 类型
7窗口函数
8索引要点
7事务与锁
8EXPLAIN 字段
6实战排查
📖 速查表
点击展开各小节
🗄️ SQL 核心语法速查
| 语句 / 概念 | 说明 | 示例 |
|---|---|---|
| SELECT | 查询数据，支持 WHERE、ORDER BY、LIMIT 等子句 | SELECT name, age FROM users WHERE age > 18 ORDER BY age DESC LIMIT 10 |
| INSERT | 插入新行，可指定列或使用默认值 | INSERT INTO users (name, age) VALUES ('Alice', 25) |
| UPDATE | 修改已有行，务必配合 WHERE 防止全表更新 | UPDATE users SET age = 26 WHERE name = 'Alice' |
| DELETE | 删除行，务必配合 WHERE 防止全表删除 | DELETE FROM users WHERE age < 18 |
| JOIN | 关联多表查询。INNER、LEFT、RIGHT、FULL | SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id |
| GROUP BY | 按列分组聚合，常配合 HAVING 过滤 | SELECT dept, COUNT(*) FROM employees GROUP BY dept HAVING COUNT(*) > 5 |
| HAVING | 对 GROUP BY 结果进行过滤（不能用 WHERE 替代） | HAVING SUM(amount) > 1000 |
| 子查询 | 嵌套在 SELECT/FROM/WHERE 中的查询 | SELECT * FROM users WHERE id IN (SELECT user_id FROM orders) |
| 窗口函数 | 在结果集的行组上计算，不折叠行。ROW_NUMBER()、RANK()、DENSE_RANK() | SELECT name, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) |
| 索引 | 加速查询的数据结构。B+ Tree、Hash、Composite（复合索引） | CREATE INDEX idx_name ON users(name) CREATE UNIQUE INDEX idx_email ON users(email) |
| 事务 | ACID 特性。BEGIN / COMMIT / ROLLBACK | 隔离级别：READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (MySQL 默认), SERIALIZABLE |
| 窗口函数 - 聚合 | SUM() OVER、AVG() OVER、COUNT() OVER | SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM sales |
| 窗口函数 - 偏移 | LAG() 上一行、LEAD() 下一行 | SELECT date, amount, LAG(amount) OVER (ORDER BY date) AS prev_amount |
🔗 JOIN 类型详解
日常以 LEFT JOIN 为主力：以左表为基准补右表信息；易错点是想当然用 WHERE 过滤右表列把 LEFT JOIN 写成了 INNER JOIN 语义，右表条件应写在 ON 里。
INNER JOIN
只返回两张表中匹配的行。不匹配的行被丢弃。这是最常用的 JOIN 类型。
LEFT JOIN
返回左表所有行，右表无匹配则填充 NULL。适合「查主表及其可选关联」的场景。
RIGHT JOIN
返回右表所有行，左表无匹配则填充 NULL。LEFT JOIN 的反向操作，但 LEFT 更常用。
FULL JOIN
返回两张表的所有行，无匹配处填充 NULL。MySQL 不直接支持，可通过 LEFT JOIN UNION RIGHT JOIN 实现。
📊 窗口函数分类速查
| 类别 | 函数 | 说明 |
|---|---|---|
| 排名 | ROW_NUMBER() | 为结果集每一行分配唯一的连续整数 |
| 排名 | RANK() | 排名相同会跳号（1, 1, 3, 4） |
| 排名 | DENSE_RANK() | 排名相同不跳号（1, 1, 2, 3） |
| 聚合 | SUM() OVER | 累计求和，支持 PARTITION BY 分组 |
| 聚合 | AVG() OVER | 移动平均值，常与 ROWS BETWEEN 配合 |
| 偏移 | LAG() | 访问前 N 行的值（同比/环比分析） |
| 偏移 | LEAD() | 访问后 N 行的值 |
📇 索引速查
| 索引概念 / 类型 | 说明 | 示例 |
|---|---|---|
| 主键索引 | 唯一标识一行，InnoDB 数据按主键组织（聚簇索引） | PRIMARY KEY (id) |
| 唯一 / 普通索引 | 唯一索引值不可重复（NULL 除外）；普通索引仅加速查询 | CREATE UNIQUE INDEX idx_email ON users(email) |
| 联合索引 | 多列组合的有序结构，列顺序决定可用性 | KEY idx_status_created (status, created_at) |
| 全文索引 | 文本关键词检索，中文需 ngram 分词 | MATCH(content) AGAINST ('关键词') |
| 最左前缀原则 | 联合索引 (a,b,c) 只有从 a 开始连续命中才能走索引 | WHERE a=1 AND b=2 ✓ WHERE b=2 ✗ |
| 覆盖索引 / 回表 | 查询列全在索引中即覆盖索引免回表；否则二级索引拿到主键后需回聚簇索引取整行 | SELECT status FROM orders WHERE user_id=1（有 idx_user_status 即覆盖） |
| 索引失效 · 函数 / 隐式转换 | 对索引列做函数或运算、类型不匹配的隐式转换都会放弃索引 | WHERE DATE(create_time)=... ✗ WHERE phone = 138...（字符串列传数字）✗ |
| 索引失效 · 模糊 / OR | 前导模糊匹配不走索引；OR 两侧存在非索引列时整体失效 | LIKE '%abc' ✗ WHERE a=1 OR no_idx_col=2 ✗ |
🔒 事务与锁
| 隔离级别 / 概念 | 说明 | 问题 / 示例 |
|---|---|---|
| ACID | 原子性、一致性、隔离性、持久性——一组操作要么全部生效要么全部不生效 | 转账扣款与入账必须同生共死 |
| READ UNCOMMITTED | 读未提交：能读到其他事务未提交的数据 | 脏读 / 不可重复读 / 幻读全部存在 |
| READ COMMITTED | 读已提交：只能读到已提交数据 | 解决脏读；不可重复读、幻读仍可能（Oracle / PG 默认） |
| REPEATABLE READ | 可重复读：同一事务内多次读结果一致 | MySQL InnoDB 默认；MVCC + 间隙锁基本避免幻读 |
| SERIALIZABLE | 串行化：事务完全串行执行 | 三类问题全部避免，并发性能最差 |
| MVCC | 多版本并发控制：读写互不阻塞 | 基于 undo log 版本链 + ReadView 实现快照读 |
| 死锁排查 | 查看最近一次死锁现场，按统一顺序加锁预防 | SHOW ENGINE INNODB STATUS → LATEST DETECTED DEADLOCK |
📈 EXPLAIN 速查
| 列 | 解读 | 优化建议 |
|---|---|---|
| type | 访问类型等级：system > const > eq_ref > ref > range > index > ALL | 生产 SQL 至少达到 range 级，出现 ALL 需重点优化 |
| const / eq_ref | 主键或唯一索引等值匹配，最多一条结果 | 最优访问方式 优 |
| ref | 普通索引等值匹配，可能多条结果 | 常见且可接受 |
| range / index / ALL | 范围扫描 / 扫全索引 / 扫全表 | index 与 ALL 都意味着扫描量过大 劣 |
| key | 实际使用的索引；possible_keys 有值而 key 为 NULL 说明未走索引 | 检查条件写法与统计信息，必要时 force index 验证 |
| rows / filtered | 预估扫描行数与过滤比例，二者乘积近似返回行数 | 数值越大越慢，结合 key 评估索引效果 |
| Extra · Using index | 覆盖索引生效，无需回表 | 只查需要的列即可达成 优 |
| Extra · Using filesort / temporary | 需要额外排序 / 需要临时表 | 常见于 ORDER BY / GROUP BY 无索引支撑，需建索引优化 劣 |
🧵 复杂查询配方：分组内 TopN
「每个部门工资前三」「每个分类销量 Top5」是报表开发最高频的复杂查询，套路固定：窗口函数编号 + 子查询过滤。看完这段注释就能直接套模板。
-- 需求：查出每个部门工资最高的前 3 名（表 employee：id, dept_id, name, salary）
-- ① 先看只算编号的样子：PARTITION BY 按 dept_id 分组，组内按 salary 降序
-- ROW_NUMBER() 给组内每行发连续编号 1,2,3...（同分也分先后）
SELECT id, dept_id, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employee;
-- ② WHERE 里不能直接写窗口函数（它比 WHERE 后执行），必须包一层子查询先算完再过滤
SELECT id, dept_id, name, salary, rn
FROM (
SELECT id, dept_id, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employee
) t
WHERE rn <= 3;
-- ③ 排序键建联合索引 (dept_id, salary) 可省掉每组内排序的开销
-- ④ 工资并列都想保留：换 RANK()（并列跳号）或 DENSE_RANK()（并列不跳号），阈值改成对应写法
-- ⑤ 只要"每组第一名"？同模板把 rn <= 3 改成 rn = 1，比关联子查询快一个量级
🐢 慢查询优化五步法
慢 SQL 不要上来就改代码，按下面五步从定位到兜底依次推进，每一步都有明确的验证手段，90% 的慢查询前三步就能解决。
| 步骤 | 动作 | 说明 / 示例 |
|---|---|---|
| ① 定位 | EXPLAIN 看执行计划，确认是否全表扫描 | type=ALL、rows 百万级、key=NULL 三大危险信号，先抓住慢的元凶再谈优化 |
| ② 建索引 | 按 WHERE / ORDER BY 的查询路径建联合索引 | 等值列在前、范围与排序列在后，遵守最左前缀；如「查某用户最近订单」建 (user_id, created_at) |
| ③ 免回表 | 覆盖索引：把 SELECT 的列并入索引 | Extra 出现 Using index 即生效；改写 SELECT * 为只取需要的列，索引才装得下 |
| ④ 改写 SQL | 索引到位还慢，改写写法绕开缺陷 | 深分页 LIMIT 1000000,20 改延迟关联：子查询用覆盖索引先取主键再回表；连续翻页用游标法 WHERE id > 上次最大值 |
| ⑤ 架构兜底 | 仍慢就别死磕单库：缓存、归档、读写分离 | 热点结果进 Redis；历史数据归档冷表或数仓，控制单表体积；大流量报表查询走从库 |
