SQL面试题选择指南｜高频考点·避坑技巧·真题解析

一、SQL基础选择题：SELECT语句执行顺序与语法陷阱

【经典真题】以下SQL语句的逻辑执行顺序正确的是？

在标准SQL中，SELECT语句的逻辑执行顺序（非物理执行顺序）是面试高频考点。请判断以下选项：

正确答案：A

逻辑执行顺序（按SQL语义解析顺序）为：

FROM：确定数据源（表/视图/JOIN结果）
WHERE：过滤行（在分组前执行）
GROUP BY：分组聚合
HAVING：过滤分组（在聚合后执行）
SELECT：选择列与表达式计算
ORDER BY：排序结果

⚠️ 注意：物理执行计划由数据库优化器决定，可能跳过某些步骤（如WHERE下推），但逻辑顺序固定。若在SELECT中定义别名，在WHERE中引用会报错（如`WHERE salary > avg_salary`），因WHERE先于SELECT执行。

错误。SELECT在WHERE之后执行，不能前置。

错误。WHERE必须在GROUP BY之前过滤原始行。

错误。FROM是数据源起点，WHERE不能先于FROM。

【陷阱题】执行以下SQL后，结果集行数可能为？

SELECT * FROM A LEFT JOIN B ON A.id = B.id WHERE B.status = 'active';

表A有100行，表B有50行，B中status='active'的记录有30条。

正确答案：① ≤50行

虽然使用LEFT JOIN，但WHERE B.status = 'active'会过滤掉B为NULL的行（因NULL ≠ 'active'），实际等价于INNER JOIN！

正确写法应为：

              SELECT * FROM A
              LEFT JOIN B ON A.id = B.id AND B.status = 'active';
            

此时保留A所有行，B匹配不到则NULL填充。

仅当A中所有id在B中唯一匹配时成立，但A可能有重复id或B中status='active'的id在A中重复出现，故不一定=30。

LEFT JOIN后理论上最多100行，但WHERE过滤后行数≤B中匹配行数（30），故≤50虽成立但非最优解。

LEFT JOIN+WHERE条件不可能超过A行数（100），更不可能≥100。

【高频考点】关于GROUP BY的正确说法？

所有非聚合列必须出现在GROUP BY子句中
MySQL 5.7+默认启用ONLY_FULL_GROUP_BY模式，违反则报错
PostgreSQL严格遵循此规则，不遵守则报错
Oracle要求GROUP BY中必须包含所有非聚合列
SQL Server与MySQL行为一致（可配置）

💡 避坑提示：在MySQL中关闭ONLY_FULL_GROUP_BY（如`SET sql_mode = '';`）虽可运行非标准SQL，但易引发逻辑错误（如`SELECT name, dept, COUNT(*) FROM emp GROUP BY dept;`中name未聚合，返回的是任意一行的name）。

二、SQL进阶题：NULL值陷阱与三值逻辑辨析

【经典陷阱】执行以下SQL，结果是什么？

            SELECT
              (NULL = NULL) AS "eq",
              (NULL <> NULL) AS "neq",
              (NULL IS NULL) AS "is_null";
          

错误。NULL = NULL是逻辑未知（NULL），非TRUE。

错误。NULL <> NULL同样返回NULL（未知），非FALSE。

正确答案：C

在SQL三值逻辑中：

=和<>对NULL操作返回NULL（未知）
仅IS NULL和IS NOT NULL可判断NULL

⚠️ 常见错误：用`WHERE column = NULL`过滤，应改为`WHERE column IS NULL`。

错误。IS NULL对NULL值判断恒为TRUE。

【实战案例】COUNT(*)、COUNT(1)、COUNT(列)的区别？

某表结构如下：

id	name	score
1	张三	85
2	李四	NULL
3	王五	92

正确答案：①

COUNT(*)：统计所有行（包括NULL），结果=3
COUNT(1)：对每行返回1，不忽略NULL，结果=3
COUNT(score)：忽略NULL值，仅统计非NULL列，结果=2

💡 性能提示：现代数据库中COUNT(*)与COUNT(1)性能几乎一致（优化器会转为相同执行计划），建议统一用COUNT(*)。

错误。COUNT(1)不会忽略任何行。

错误。COUNT(*)统计所有行，结果应为3。

【高阶陷阱】COALESCE与IFNULL的差异？

在处理NULL值时，以下函数行为正确的是？

正确答案：A

COALESCE(a,b,c,...)：标准SQL函数，返回第一个非NULL值，支持任意参数数量
IFNULL(a,b)：MySQL特有函数，仅支持两个参数（若a非NULL返回a，否则返回b）
PostgreSQL/SQL Server/Oracle无IFNULL，需用CASE WHEN或COALESCE

              -- 示例：处理订单金额，若为空则显示0
              SELECT COALESCE(order_amount, 0) AS amount FROM orders;
              SELECT IFNULL(order_amount, 0) AS amount FROM orders; -- 仅MySQL
            

错误。COALESCE返回第一个非NULL值，且IFNULL不支持多参数。

错误。COALESCE是标准函数，跨数据库兼容；IFNULL是MySQL特有，且参数限制不同。

三、SQL性能优化题：索引失效场景与执行计划分析

【高频考点】以下操作会导致索引失效的是？

假设表`users`有索引`idx_name (name)`，以下查询会放弃索引扫描（全表扫描）的是？

错误。前缀匹配（'Zhang%'）可使用索引（B-Tree特性）。

正确答案：②

LIKE '%Zhang'因通配符在开头，无法利用B-Tree索引顺序，导致全表扫描。

💡 优化方案：

改用全文索引（MySQL FULLTEXT）
使用倒排索引（如Elasticsearch）
限制数据范围（如按时间分区）

错误。若`name`列存储的是大小写敏感值，`UPPER()`函数会强制全表计算，导致索引失效；但若建立函数索引（MySQL 8.0+）可避免。

错误。COLLATE指定排序规则，若与索引定义一致则不影响索引使用。

【执行计划分析】EXPLAIN输出中key_len=767说明什么？

执行`EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';`后，部分结果如下：

id	select_type	table	type	possible_keys	key	key_len	ref	rows
1	SIMPLE	users	ref	idx_email	idx_email	767	const	1

错误。覆盖索引要求SELECT字段均在索引中，此处*表示所有列。

正确答案：B

key_len=767说明：

UTF8MB4字符集（1字符=4字节）
索引长度=255字符 × 4字节 = 1020字节，但MySQL限制InnoDB索引前缀767字节（MySQL 5.6/5.7）
实际使用前缀索引：255字符中仅索引191字符（767 ÷ 4 = 191.75）

💡 MySQL 5.7+支持`innodb_large_prefix=ON`可索引3072字节，但默认前缀索引仍常见于旧系统。

错误。key列显示`idx_email`，说明索引存在且被使用。

【时间轴：索引设计演进】

2000年代初

单列索引为主：如`idx_name`，适用于等值查询，但组合查询效率低。

2010年MySQL 5.6

覆盖索引（Covering Index）普及：索引包含所有查询字段（如`idx_name_age`），避免回表。

2016年MySQL 5.7

JSON索引与函数索引：支持`CREATE INDEX idx_json ON users ((JSON_EXTRACT(data, '$.age')))`

2023年主流方案

多维索引（Multicolumn Index）+ 分区索引：结合业务场景设计（如`idx_status_created`），并按时间分区提升查询效率。

💡 网友经验：索引不是越多越好！每增加索引，写入性能下降10%~30%，且占用磁盘空间。建议遵循3-2-1原则：

3：每个表最多3个高频查询索引
2：组合索引字段数≤2（高基数字段优先放左边）
1：定期清理未使用索引（`pt-index-usage`工具检测）

四、数据库差异题：MySQL vs PostgreSQL vs Oracle核心特性对比

【真题对比】分页查询语法差异？

请为以下数据库选择正确的分页查询写法（查询第2页，每页10条）：

正确：MySQL

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;

MySQL 8.0+支持标准`FETCH FIRST`，但`LIMIT`仍最常用。

正确：PostgreSQL

SELECT * FROM users ORDER BY id OFFSET 10 FETCH FIRST 10 ROWS ONLY;

PostgreSQL优先推荐标准SQL语法。

正确：Oracle

              SELECT * FROM (
                SELECT a.*, ROWNUM r FROM (
                  SELECT * FROM users ORDER BY id
                ) a WHERE ROWNUM <= 20
              ) WHERE r > 10;
            

Oracle传统写法需嵌套，12c+支持标准语法：

SELECT * FROM users ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

正确：SQL Server

              SELECT * FROM users
              ORDER BY id
              OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
            

SQL Server 2012+支持标准分页语法。

【网友关注】事务隔离级别差异表

隔离级别	脏读	不可重复读	幻读	MySQL默认	PostgreSQL默认
READ UNCOMMITTED	✓	✓	✓	支持	不支持
READ COMMITTED	✗	✓	✓	不支持	✓
REPEATABLE READ	✗	✗	✓	✓	不支持（等同READ COMMITTED）
SERIALIZABLE	✗	✗	✗	支持	支持

💡 关键差异：

MySQL的REPEATABLE READ通过MVCC实现，但可能产生幻读（通过Next-Key Lock缓解）
PostgreSQL的READ COMMITTED使用行级锁，不会产生不可重复读（但可能幻读）
Oracle默认READ COMMITTED，通过Undo段保证一致性读

【网友还关心】常见SQL函数对比

功能	MySQL	PostgreSQL	Oracle	SQL Server
字符串拼接	CONCAT(a,b) 或 a \|\| b	a \|\| b 或 CONCAT(a,b)	a \|\| b 或 CONCAT(a,b)	a + b 或 CONCAT(a,b)
条件表达式	IF(a,b,c) 或 CASE WHEN	CASE WHEN 或 NULLIF(a,b)	CASE WHEN 或 DECODE(a,b,c)	CASE WHEN 或 IIF(a,b,c)
获取当前时间	NOW() 或 CURRENT_TIMESTAMP	NOW() 或 CURRENT_TIMESTAMP	SYSDATE 或 CURRENT_TIMESTAMP	GETDATE() 或 SYSDATETIME()
字符串截取	SUBSTR(a,1,5)	SUBSTRING(a FROM 1 FOR 5)	SUBSTR(a,1,5)	SUBSTRING(a,1,5)

五、事务与并发控制：隔离级别与幻读辨析

【经典陷阱】READ COMMITTED下会发生幻读吗？

假设表`orders`有数据：

id	status	amount
1	pending	100
2	pending	200

事务A执行：
SELECT * FROM orders WHERE status = 'pending'; -- 返回2行
此时事务B插入新行：
INSERT INTO orders VALUES (3, 'pending', 150);
事务A再次执行相同查询：

错误。READ COMMITTED下新插入的行可见，会返回3行。

正确答案：②

在READ COMMITTED隔离级别下，事务A第二次查询能看到事务B提交的新行，属于幻读现象！

✅ 幻读定义：同一事务中，相同条件查询返回行数不一致。

❌ 注意：不可重复读是同一行数据被修改，幻读是行数变化（插入/删除）。

💡 避免幻读方案：

升级隔离级别至REPEATABLE READ（MySQL）或SERIALIZABLE（全库）
使用SELECT ... FOR UPDATE加锁（但仅锁现有行）

错误。INSERT操作不直接导致死锁，需循环等待锁资源才会死锁。

【真题实战】MVCC工作原理

以下关于多版本并发控制（MVCC）的说法正确的是？

正确答案：A

MVCC核心机制：

每行隐藏字段：DB_TRX_ID（最新事务ID）、DB_ROLL_PTR（回滚指针）
Undo日志存储历史版本
读操作根据事务ID过滤可见版本（Read View）
写操作加排他锁，不阻塞读

💡 支持MVCC的数据库：

MySQL InnoDB（REPEATABLE READ/READ COMMITTED）
PostgreSQL
Sybase

❌ MySQL MyISAM不支持MVCC（仅表级锁）

错误。表级锁是MyISAM特性，非MVCC。

错误。MySQL InnoDB从4.1版本起支持MVCC，Oracle也基于Undo实现。

六、网友还关心：高频真题与实战经验汇总

【TOP 5 面试高频问题】

1. 跨库JOIN如何优化？

答案要点：

避免跨库JOIN（服务化拆分后数据冗余）
使用ES聚合或应用层JOIN
ShardingSphere中间件支持跨库JOIN
弱一致性场景用缓存预热

2. 大表分页优化（LIMIT 1000000,10）

答案要点：

改用子查询：WHERE id > (SELECT id FROM table ORDER BY id LIMIT 1000000,1) LIMIT 10
记录上次最大ID（游标分页）
禁止深度分页（前端限制页数）

3. 如何判断SQL是否走索引？

答案要点：

EXPLAIN查看type列（ref/eq_ref > index > ALL）
检查key_len是否合理
用`FORCE INDEX`强制索引验证性能

4. 死锁排查步骤？

答案要点：

SHOW ENGINE INNODB STATUS\G查看最近死锁
检查事务顺序（固定资源获取顺序）
减少事务内SQL数量（小事务原则）

5. 索引下推（ICP）是什么？

答案要点：

MySQL 5.6+特性，将WHERE过滤下推到存储引擎层
减少回表次数（如联合索引(a,b)，WHERE a=1 AND b>10）
EXPLAIN显示Using index condition

【网友经验贴】SQL优化 Checklist

查询字段：避免SELECT *，只取必要列
WHERE条件：避免函数/计算在列上（如WHERE YEAR(create_time)=2023）
JOIN顺序：小表驱动大表（MySQL自动优化，但PostgreSQL需手动控制）
UNION vs OR：UNION ALL性能优于UNION（去重开销），但需确认无重复
临时表：大查询用临时表分步处理，避免内存溢出
批量操作：INSERT/UPDATE分批执行（如每批1000行）

【延伸学习资源】

✅ 推荐系统学习路径：

《高性能MySQL》第3版（索引/执行计划/锁机制）
《SQL反模式》（避免常见陷阱）
PostgreSQL官方文档（https://www.postgresql.org/docs/）
MySQL 8.0参考手册（https://dev.mysql.com/doc/refman/8.0/en/）
Stack Overflow SQL标签（https://stackoverflow.com/questions/tagged/sql）

💡 实践建议：

搭建本地测试库（Docker容器快速部署）
用pt-query-digest分析慢查询日志
参与开源项目SQL审查（GitHub搜索"good first issue sql"）