SQL 面试题

sql
-- INNER JOIN：两表都匹配的行
SELECT * FROM A INNER JOIN B ON A.id = B.a_id;

-- LEFT JOIN：A 全保留，B 无匹配为 NULL
SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;

-- RIGHT JOIN：B 全保留
SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id;

-- FULL OUTER JOIN：两表都保留
SELECT * FROM A FULL OUTER JOIN B ON A.id = B.a_id;

-- CROSS JOIN：笛卡尔积
SELECT * FROM A CROSS JOIN B;

`WHERE` 在**分组前**过滤行——不能用聚合函数。`HAVING` 在**分组后**过滤组——可以用聚合函数。
sql
SELECT dept, AVG(salary) as avg_sal
FROM employees
WHERE hire_date > '2020-01-01'    -- 先过滤行
GROUP BY dept
HAVING AVG(salary) > 10000;       -- 再过滤组

- **COUNT(*)** / **COUNT(1)**：统计所有行（包括 NULL），性能几乎相同
- **COUNT(列名)**：统计该列非 NULL 的行数

sql
SELECT COUNT(*) FROM users;        -- 所有行
SELECT COUNT(email) FROM users;    -- email 不为 NULL 的行
SELECT COUNT(DISTINCT city) FROM users; -- 去重计数

现代数据库中 COUNT(*) 和 COUNT(1) 无性能差异——用 COUNT(*) 即可。

`UNION` 合并结果集并**自动去重**（需要排序比较，性能较低）。`UNION ALL` 直接合并**不去重**（性能好）。

sql
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
-- 如果确定无重复或允许重复，用 UNION ALL

**优先用 UNION ALL**——只在需要去重时用 UNION。

窗口函数在结果集的**窗口**上计算——保留所有行，不折叠。
sql
SELECT name, dept, salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rn,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as dr
FROM employees;

- ROW_NUMBER：唯一序号（1,2,3,4）
- RANK：并列同号跳号（1,1,3,4）
- DENSE_RANK：并列同号不跳号（1,1,2,3）

**ACID**：
- **A**tomicity 原子性——要么全成功，要么全回滚
- **C**onsistency 一致性——事务前后数据满足约束
- **I**solation 隔离性——并发事务不互相影响
- **D**urability 持久性——提交后数据永久保存

**隔离级别**（低→高）：
1. READ UNCOMMITTED——脏读
2. READ COMMITTED——不可重复读（Oracle 默认）
3. REPEATABLE READ——幻读（MySQL InnoDB 默认——通过 MVCC 解决了幻读）
4. SERIALIZABLE——串行执行，最高隔离

| | InnoDB | MyISAM |
|---|--------|--------|
| 事务 | ✅ | ❌ |
| 行锁 | ✅ | ❌（表锁） |
| 外键 | ✅ | ❌ |
| 崩溃恢复 | ✅ | ❌ |
| 全文索引 | MySQL 5.6+ | ✅ |
| 默认 | MySQL 5.5+ | 旧版 |

**现代项目默认用 InnoDB**——MyISAM 仅在全文本搜索引擎等特殊场景使用。

**主从复制流程**：
1. Master 将变更写入 binlog
2. Slave 的 IO 线程读取 binlog → relay log
3. Slave 的 SQL 线程重放 relay log

**一致性方案**：
- **半同步复制**：Master 等待至少一个 Slave 确认收到 binlog
- **GTID**：全局事务 ID，方便切换和恢复
- **MHA / Orchestrator**：自动故障切换

读写分离场景注意**主从延迟**——关键读操作（如支付后立即查状态）走主库。

索引是数据库表中一列或多列的排序数据结构，加速查询（类比书的目录）。

**B+树**是 MySQL InnoDB 的默认索引结构：
- 所有数据存储在**叶子节点**
- 非叶子节点只存 key——高度低，IO 少
- 叶子节点形成有序**双向链表**——范围查询高效

sql
CREATE INDEX idx_name ON users(name);
-- 覆盖索引（无需回表）
CREATE INDEX idx_covering ON users(name, age, email);

1. **EXPLAIN** 分析执行计划——看 type（ALL=全表扫→加索引）、rows、Extra
2. **检查索引**——WHERE/JOIN/ORDER BY 列是否有索引？是否最左前缀？
3. **避免 SELECT *** ——只选需要的列，利用覆盖索引
4. **优化 JOIN** ——小表驱动大表，确保 JOIN 列有索引
5. **避免函数作用于索引列** ——`WHERE DATE(create_time) = '2024-01-01'` 会使索引失效
6. **分库分表 / 读写分离** ——数据量过大时

**B+树优势**：
1. **矮胖结构**——单个节点存更多 key（每个节点通常一页 16KB），高度 3-4 层可索引千万级数据
2. **数据只在叶子节点**——非叶子节点省空间，一页能存更多索引项
3. **叶子节点形成有序链表**——范围查询（BETWEEN、ORDER BY）极快，遍历叶子节点即可
4. **查询稳定**——所有查找都到叶子节点，IO 次数一致

二叉树（如 AVL）太高→大量磁盘 IO。B 树中间节点也存数据→同样高度索引量更少。

当查询的所有列都在索引中时——不需要**回表**查聚簇索引，直接从索引获取数据。

sql
-- 创建覆盖索引
CREATE INDEX idx_cover ON users(name, age, city);

-- 覆盖索引生效：SELECT 的列都在索引中
SELECT name, age FROM users WHERE name = '小明';

-- 覆盖索引不生效：email 不在索引中，需要回表
SELECT name, email FROM users WHERE name = '小明';

通过 EXPLAIN 看 Extra 列是否有 `Using index` 来确认。