性能优化（执行计划 / 慢查询 / 常见陷阱） · SQL 改写优化

SELECT * 为什么不推荐？什么时候 SELECT * 反而合理？

Why is SELECT * not recommended? When is it reasonable to use it?

答案语言

考察要点

这道题面试官想看你是否深刻理解 SQL 查询的完整生命周期，以及它如何与数据库的存储引擎、网络、内存等子系统交互。考察的核心是：

覆盖索引（Covering Index）：这是区分初级和资深工程师的关键，能否利用索引避免回表是性能优化的核心。
资源开销意识：是否能从数据库服务器（I/O、CPU）、网络传输、应用服务器（内存、CPU）等多个维度分析 SELECT * 带来的额外成本。
软件工程思维：是否考虑到 SELECT * 对代码可维护性和稳定性的长期负面影响。

核心概念

不推荐 SELECT * 的主要原因有以下四点，其中覆盖索引是最核心的技术原因。

1. 无法利用覆盖索引，导致额外回表

覆盖索引是指一个查询的 SELECT、WHERE、ORDER BY 等所有涉及的列，都恰好存在于一个二级索引中，此时数据库可以直接从该索引获取所有需要的数据，而无需访问主键索引（聚簇索引）去获取完整的行数据。这个过程叫做“避免回表”。

SELECT * 因为要查询所有列，而二级索引通常只包含部分列，所以几乎必然导致回表，大大降低了查询效率。

数据示例： 假设有用户表 users：

sql

 1CREATE TABLE `users` (
 2  `id` INT PRIMARY KEY AUTO_INCREMENT,
 3  `name` VARCHAR(50) NOT NULL,
 4  `age` INT NOT NULL,
 5  `city` VARCHAR(50),
 6  `profile` TEXT, -- 大字段
 7  INDEX `idx_age_city` (`age`, `city`)
 8) ENGINE=InnoDB;

场景对比：

查询语句	执行计划分析
高效查询 (利用覆盖索引)<br>`sql<br>EXPLAIN SELECT age, city FROM users WHERE age = 30; <br>`	Extra: `Using index`<br>意味着查询所需数据全部在 `idx_age_city` 索引中找到，无需回表，性能极高。
*低效查询 (使用 `SELECT `)*<br>`sql<br>EXPLAIN SELECT FROM users WHERE age = 30;<br>`	Extra: `Using index condition`<br>意味着虽然通过 `idx_age_city` 索引找到了 `age=30` 的记录，但由于需要 `id`, `name`, `profile` 等其他列，必须根据索引中存储的主键 `id` 回表到聚簇索引中去读取完整的行数据。这增加了随机 I/O 操作。

回表代价公式（估算）：回表次数 * 单次回表 I/O 成本。在 age=30 的用户非常多的情况下，这将产生大量的随机 I/O，成为性能瓶颈。

2. 增加网络和应用服务器开销

网络 I/O 开销：SELECT * 会返回所有列的数据，包括可能当前业务逻辑并不需要的大字段（如 TEXT, BLOB）。这会显著增加数据库服务器到应用服务器之间的数据传输量，尤其是在高并发场景下，会占满网络带宽。
应用服务器开销：应用服务器（如 Java/Go/Python 服务）接收到数据后，需要分配更多内存来存储这些不需要的字段。同时，反序列化这些数据也会消耗更多的 CPU 资源。

3. 增加数据库服务器 I/O 和 CPU 开销

即使不考虑回表，从磁盘（或 Buffer Pool）中读取数据页时，SELECT * 也会读取包含所有列的数据。如果一张表包含 TEXT 或 VARCHAR(MAX) 等大字段，即使它们是 NULL，也可能因为行溢出（Row Overflow）等机制导致额外的 I/O 操作。将这些不需要的数据从磁盘读入内存，是对宝贵的 Buffer Pool 资源的浪费。

4. 降低代码可维护性和可读性

可读性差：当其他开发者阅读代码时，无法直接看出该查询到底依赖了哪些列，必须去查看后续处理逻辑，增加了理解成本。
可维护性差（脆弱性）：
- 如果应用程序的逻辑依赖于列的顺序（一种非常糟糕的实践），当数据库表结构发生变更（如 ALTER TABLE ... ADD COLUMN），SELECT * 返回的列顺序可能改变，导致程序出错。
- 如果代码中某个对象依赖于所有列，当DBA为优化而删除某个不再使用的列时，会导致应用代码直接崩溃。明确指定列可以使依赖关系显式化，更利于协作和维护。

什么时候 `SELECT *` 反而合理？

虽然 SELECT * 在生产代码中通常被视为反模式，但在某些特定场景下是可接受甚至合理的：

即时查询和数据探索（Ad-hoc Query） 当你在数据库客户端（如 mysql 命令行、DataGrip、Navicat）中手动探查数据时，SELECT * 是最方便快捷的方式，目的是快速了解表的结构和数据概貌。
sql
```
 1-- 临时查看 user 表的前 10 条数据，完全合理
 2SELECT * FROM users LIMIT 10;
```
EXISTS 子句中 在 EXISTS 或 NOT EXISTS 子句中，SELECT 列表的内容是无关紧要的，因为数据库引擎只关心是否存在满足条件的行，并不会真正去获取和返回数据。因此，使用 SELECT *、SELECT 1 或 SELECT 'any_column' 在性能上没有区别。SELECT * 在此场景下因其简洁而被广泛使用。
sql
```
 1-- 检查是否存在 30 岁以上的用户，SELECT * 在这里完全没问题
 2SELECT * FROM products p
 3WHERE EXISTS (SELECT * FROM orders o WHERE o.product_id = p.id);
```
列数极少且高度稳定的表 对于一些只有 2-3 个列的配置表或关系映射表，且表结构几乎从不改变，使用 SELECT * 和明确指定所有列在性能和维护成本上的差异可以忽略不计。例如，一个 user_role_mapping 表只有 user_id 和 role_id 两列。

常见低分回答

“因为 SELECT * 慢。” —— 太过笼统。面试官想听你解释为什么慢，慢在哪里（网络、磁盘、CPU），以及核心原因（无法覆盖索引）。
“会返回不需要的列，浪费资源。” —— 只说对了一半。没有点出“覆盖索引”和“回表”这两个数据库引擎层面的关键概念，深度不够。
“SELECT * 不会走索引。” —— 错误。SELECT * 仍然可以利用索引进行 WHERE 条件的筛选（如 Using index condition），只是无法完美地“覆盖”索引来避免回表。
“在 EXISTS 里应该用 SELECT 1，比 SELECT * 快。” —— 错误。这表明候选人对 EXISTS 的工作原理理解不深。现代数据库优化器会识别出这种情况，两者没有性能差异。
“为了解决 SELECT * 的问题，我可以给所有列建一个联合索引。” —— 严重错误。这暴露了对索引成本的无知。超长联合索引会占用巨大存储空间，并严重拖慢 INSERT、UPDATE、DELETE 的性能。

工程实践与优化

EXPLAIN 分析是金标准：在怀疑 SELECT * 导致性能问题时，立即使用 EXPLAIN 分析查询计划。核心关注 Extra 列：
- Using index：最佳情况，实现了覆盖索引。
- Using index condition：使用了索引，但需要回表。这是 SELECT * 常见的场景。
- Using where：在存储引擎层之上（Server 层）进行过滤，通常性能较差。
监控工具：在生产环境中，通过 pt-query-digest 分析慢查询日志，或使用 Performance Schema，可以批量发现和定位那些由 SELECT * 引起的高成本查询。

Schema 设计：在设计表结构时，要有意识地将查询模式考虑进去。如果一个表有几个非常大的 TEXT/BLOB 字段，且它们不总是被需要，可以考虑垂直分表。

sql

 1-- 主表
 2CREATE TABLE articles (
 3    id INT PRIMARY KEY,
 4    title VARCHAR(255),
 5    author_id INT,
 6    created_at DATETIME
 7);
 8-- 内容扩展表
 9CREATE TABLE article_contents (
10    article_id INT PRIMARY KEY,
11    content MEDIUMTEXT,
12    FOREIGN KEY (article_id) REFERENCES articles(id)
13);

这样，查询文章列表时 SELECT id, title FROM articles 会非常快，只有在查看单篇文章详情时才需要关联查询 article_contents。

团队规范：在团队的 Code Review 流程中，应将“禁止在生产代码中使用 SELECT *”作为一条基本规则来执行。

常见追问与延伸题目

追问：你刚才提到了“回表”，在 InnoDB 中，回表的具体过程是怎样的？它为什么会产生随机 I/O？
- 回答要点：在 InnoDB 中，表数据本身是存储在聚簇索引（Clustered Index，通常是主键索引）的叶子节点上的。二级索引（Secondary Index）的叶子节点存储的是“索引列的值”和对应行的“主键值”。
- 当一个查询通过二级索引（如 idx_age_city）找到了满足条件的记录，但需要获取 SELECT 列表中未包含在二级索引里的列时，它会：
  1. 从二级索引的叶子节点读取到主键值。
  2. 用这个主键值去聚簇索引中进行一次新的 B+ 树查找，定位到完整的行数据。
  3. 读取完整的行数据，获取所需的列。
- 这个“根据主键再查一次”的过程就是回表。因为通过二级索引找到的主键值在物理上可能是无序的，导致对聚簇索引的访问变成了离散的、随机的磁盘读取，所以性能远低于顺序扫描。
追问：MySQL 5.6 引入的索引下推（Index Condition Pushdown, ICP）优化，和你说的 Using index condition 有什么关系？它能完全解决 SELECT * 的回表问题吗？
- 回答要点：Using index condition 就是 ICP 生效的标志。ICP 是一种优化，它将部分 WHERE 条件的过滤操作从 MySQL Server 层下推到存储引擎层。
- 没有 ICP (MySQL 5.5 及之前)：存储引擎通过索引找到 age=30 的所有记录的主键，全部回表，返回给 Server 层，然后 Server 层再根据 city 条件进行过滤。
- 有 ICP (MySQL 5.6 及之后)：存储引擎通过索引找到 age=30 的记录后，不立即回表，而是先在索引内部检查 city 条件是否满足。只有 age 和 city 都满足的记录，才去回表获取完整的行。
- 结论：ICP 减少了回表的次数，从而减少了 I/O 和 Server/Engine 间的数据传输，提升了性能。但它不能完全解决回表问题，因为只要 SELECT 列表请求了索引之外的列（比如 SELECT *），最终的回表动作本身是不可避免的。
追问：如果一张表没有主键，InnoDB 会怎么处理？此时二级索引的回表逻辑有什么不同？
- 回答要点：如果表没有显式定义主键，InnoDB 会按以下优先级选择或创建聚簇索引：
  1. 使用第一个 UNIQUE NOT NULL 的索引作为聚簇索引。
  2. 如果没有，InnoDB 会在内部隐式创建一个 6 字节长的 ROW_ID 列作为聚簇索引。这个 ROW_ID 对用户是不可见的。
- 此时，二级索引的叶子节点存储的就是这个隐式的 ROW_ID。回表的过程就变成：通过二级索引找到 ROW_ID，再用 ROW_ID 去隐式的聚簇索引中查找完整的行数据。
- 关键影响：由于 ROW_ID 的生成和管理机制，以及它作为聚簇索引键的性能可能不如一个有业务含义且趋势增长的整型主键，因此强烈建议所有 InnoDB 表都显式定义一个主键。