提示

如果你平时没有想过，那么面试官问出来就很容易卡住。实际上这个问题并不难，但是可能的情况比较多，因此你在回答的时候，不一定能够把面试官想要你回答的所有的点都回答出来。但是并不要紧，你只需要答出三五个点就差不多了，足以证明你对这个问题有比较深刻的理解。

而后在平时注意收集一些索引失效的案例就可以了，尽量在回答的时候用上，就非常完美。

题目分析

数据库不用索引的原因有很多：

• WHERE 条件没有命中索引：这也是最简单的理由。例如说你的索引创建在列（A，B，C）上，结果你的 WHERE 部分只有 D 这一个列；
• WHERE 条件不符合最左匹配原则：例如说在索引（A，B，C）上，你的查询 WHERE 部分只使用了 B 这个列。那么数据库也不会使用索引；
• NOT IN 等取反的查询：正常 IN 查询是可以使用索引的，但是 NOT IN 就相当于不用某几个数据，也就是要全表扫描了；
• 索引列上使用了运算：虽然 WHERE 里面使用了索引列，但是并不是直接使用索引列，而是执行了计算、调用了方法等，例如说 WHERE a = b + 1 这种；
• 数据库有更加适合的索引：例如说你有 index_a 和 index_b 两个索引，如果数据库认为使用 index_b 更加合适，那么就不会使用 index_a 了；
• 数据量太小，或者查询命中的数据占比太高：这种情况下，数据库经过评估之后认为使用索引不能带来更好的性能，反而全表扫描会带来更好的性能，那么数据库也不会使用索引；
  
  

面试官也可能换一些问法，比如说：

• 你定义了索引，数据库就一定会用吗？并不一定；
• 什么是索引失效？为什么索引会失效？ 另外有一些面试官喜欢直接列出表结构定义，然后写出查询，问你为什么这个查询会不会走索引，走哪个索引，你记得问一句数据量大小，因为这个也会对索引有影响。而后你记得补充，最终还是要以数据库 EXPLAIN 为准，你自己这种理论分析是不靠谱的。

当然，要是面试官问到慢查询排查，又或者 SQL 查询优化的时候，你也可以用索引失效作为其中的一个点。

15K 回答

（基本原因）数据库不用索引的原因有很多：

• WHERE 条件没有命中索引：例如说 WHERE 条件里面完全没有任何的索引列；
• WHERE 条件不符合最左匹配原则：例如说索引（A，B，C），但是 WHERE 里面只有列 B 的查询条件；
• NOT IN 等取反的查询：正常 IN 查询是可以使用索引的，但是 NOT IN 就相当于不用某几个数据，也就是要全表扫描了；
• 索引列使用了运算：最常见的就是索引列参与了数学运算，或者作为函数的参数；
• 数据库有更加适合的索引：这种其实很常见，或者说有时候我们会预期数据库使用 index_a，但是数据库可能最终用的是 index_b；
• 数据量太小，或者查询命中的数据占比太高：这种情况下，数据库经过评估之后认为全表扫描性能更好，那么也不会使用索引； 在实践中，遇到慢查询，或者 SQL 优化的时候，首先要确定的就是数据库究竟有没有用索引，用的是哪个索引，是否符合我们的预期。

提示

这里你其实已经留下了一个引导点，也就是怎么看数据库究竟有没有使用索引，用的是哪个索引。即使用 EXPLAIN 命令，即便是 NoSQL，它们多半也会提供类似的工具，方便你查看查询的执行计划。

（补充案例）比如说早期我就遇到过一个索引失效的问题。当时我们有一个慢查询，是一个简单的范围查询。大部分时候这个查询都运作非常正常，但是在偶发情况下就会特别慢。后面我去排查的时候就发现，这个查询在某几个大客户使用的时候就会特别慢。后面我在线上数据库用大客户的查询条件 EXPLAIN 了一下这个查询，发现这个查询已经退化为了全表扫描。

后面我就改写了查询，改成了一个分批查询，避免了一次加载数据太多，引发的索引失效问题。

• 关键字： WHERE 最左匹配原则 全表扫描
• 引导点： EXPLAIN；
• 亮 点： 全表扫描案例；

25K 回答

（进一步引导）数据库查询不使用索引的问题不仅仅在于查询会很慢，还可能引发更加严重的问题，比如说表锁。正常在 MySQL 的 Innodb 引擎中，对数据加锁是借助索引来实现的。如果没有命中任何索引，那么 Innodb 引擎就会使用表锁。

提示

这里你可以补充一个没有命中索引导致的表锁案例。

由于未命中索引导致表锁的案例

案例一

好的，以下是一个更完整的案例，其中包含了由于没有命中索引而导致的表锁案例，并提供了相应的解决方案。

1.案例背景

假设我们有一个名为 products 的表，其中包含以下字段：

• id (INT, PRIMARY KEY)
• name (VARCHAR)
• price (DECIMAL)
• category_id (INT)
• created_at (TIMESTAMP)

我们在 category_id 字段上创建了一个索引 idx_category_id。

现在，我们需要查询 category_id 为 1 的所有商品。我们可以使用以下 SQL 查询语句：

SELECT * FROM products WHERE category_id = 1;

2.问题描述

在某些情况下，我们可能会发现这个查询执行非常缓慢。通过使用 EXPLAIN 命令分析查询执行计划，我们可能会发现 type 列的值为 ALL，表示查询使用了全表扫描。

3.原因分析

EXPLAIN 命令的结果表明，MySQL 查询优化器没有选择使用 idx_category_id 索引，而是选择了全表扫描。这可能是因为以下原因：

• 索引失效： 索引 idx_category_id 可能由于某些原因失效，例如索引碎片过多、索引统计信息不准确等。
• 查询条件过于宽泛： 查询条件 category_id = 1 可能匹配了大量的行，导致 MySQL 认为全表扫描比使用索引更快。
• 其他原因： 可能存在其他原因导致 MySQL 没有选择使用索引，例如查询语句的写法、数据库配置等。

4.表锁问题

由于查询使用了全表扫描，可能会导致表锁问题。当一个查询需要扫描整个表时，它可能会获取一个表级锁，阻止其他事务对该表进行修改操作，从而影响并发性能。

5.解决方案

针对以上问题，我们可以采取以下解决方案：

1. 重建索引： 如果索引 idx_category_id 失效，可以尝试重建索引。
2. 优化查询条件： 如果查询条件过于宽泛，可以尝试缩小查询范围，例如添加其他过滤条件。
3. 强制使用索引： 可以使用 FOR INDEX 语法强制 MySQL 使用指定的索引。
4. 分析查询执行计划： 使用 EXPLAIN 命令分析查询执行计划，找出查询性能瓶颈。
5. 优化数据库配置： 检查数据库配置，例如 innodb_buffer_pool_size 等，确保数据库配置合理。

6.案例示例

假设我们发现查询条件 category_id = 1 匹配了大量的行，导致 MySQL 选择了全表扫描。我们可以尝试添加其他过滤条件，例如：

SELECT * FROM products WHERE category_id = 1 AND price < 100;

如果 MySQL 仍然没有选择使用索引，我们可以使用 FOR INDEX 语法强制 MySQL 使用 idx_category_id 索引：

SELECT * FROM products FOR INDEX (idx_category_id) WHERE category_id = 1 AND price < 100;

7.总结

通过以上案例，我们可以了解到由于没有命中索引而导致全表扫描和表锁的问题，并学习了如何通过重建索引、优化查询条件、强制使用索引等方法来解决这些问题。在实际开发中，我们需要根据具体情况选择合适的解决方案，以提高查询性能和并发性能。

案例二

在 MySQL 数据库中，如果查询没有命中索引，可能会导致 全表扫描，进而引发 表锁。以下是一个具体的案例，说明如何因为索引未命中导致表锁，并给出优化方案。

---

1. 场景描述

公司 ERP 系统中有一张 order 订单表，该表结构如下：

CREATE TABLE `order` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `order_no` VARCHAR(50) NOT NULL,
    `user_id` BIGINT NOT NULL,
    `status` TINYINT NOT NULL DEFAULT 0,  -- 订单状态（0:待支付, 1:已支付, 2:已取消）
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)  -- 仅对 user_id 建立索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在并发环境下，某个业务逻辑会执行以下 UPDATE 语句，修改特定用户的订单状态：

UPDATE order SET status = 1 WHERE order_no = 'HT20240208001';

---

2. 问题分析

执行 EXPLAIN 语句查看 SQL 语句的执行计划：

EXPLAIN UPDATE order SET status = 1 WHERE order_no = 'HT20240208001';

查询结果如下：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order	ALL	NULL	NULL	NULL	NULL	100000	Using where

问题点

• 索引未命中：表中 order_no 没有索引，查询条件 WHERE order_no = 'HT20240208001' 只能 全表扫描（ALL）。
• 表锁风险：
  • InnoDB 默认是 行锁（row-level lock），但由于没有命中索引，会导致 表锁（table lock），影响并发操作。
  • 在高并发环境下，多个事务尝试修改不同 order_no 的数据，但由于表锁，所有事务被串行执行，导致性能急剧下降。

---

3. 复现问题

（1）事务A：修改订单状态

START TRANSACTION;
UPDATE order SET status = 1 WHERE order_no = 'HT20240208001';
-- 事务A未提交，表被锁住

（2）事务B：修改另一条订单

START TRANSACTION;
UPDATE order SET status = 1 WHERE order_no = 'HT20240208002';
-- 事务B等待事务A释放锁

此时，事务 B 被阻塞，因为 UPDATE 语句由于全表扫描导致表锁，使所有对 order 表的修改都需要等待事务 A 提交。

---

4. 解决方案

方案 1：为 order_no 创建索引

ALTER TABLE order ADD INDEX idx_order_no (order_no);

再次执行 EXPLAIN，可以看到索引被正确使用：

EXPLAIN UPDATE order SET status = 1 WHERE order_no = 'HT20240208001';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order	ref	idx_order_no	idx_order_no	50	const	1	Using index

• 查询性能提升：type 由 ALL（全表扫描）变为 ref（使用索引）。
• 避免表锁：仅锁住 order_no = 'HT20240208001' 的行，事务 B 也可以更新其他 order_no，不再阻塞。

---

方案 2：使用 ORDER BY + LIMIT 1（适用于行锁问题）

如果 order_no 不是唯一键，可能会影响锁粒度，可以加 LIMIT 1 限制影响行数：

UPDATE order SET status = 1 WHERE order_no = 'HT20240208001' LIMIT 1;

作用：

• MySQL 会尽量锁定少量数据，避免锁住不必要的行。

---

5. 总结

问题	影响	解决方案
未命中索引导致全表扫描	事务执行慢，锁表影响并发	创建索引（ALTER TABLE order ADD INDEX idx_order_no (order_no)）
InnoDB 可能锁住过多行	事务阻塞，导致并发降低	LIMIT 1 限制锁行数

这个案例展示了：

1. 索引缺失导致全表扫描，进而引发表锁。
2. 索引优化如何显著提高 MySQL 查询性能，避免锁冲突。

如果你在项目中遇到类似问题，可以尝试 EXPLAIN 检查索引命中情况，并优化索引来提升性能 🚀。

• 关键字： 表锁
• 引导点： 表锁；锁机制；

贡献者

• hhhbx

变更历史

最后更新于: 查看全部变更历史

• 7040e-

  feat(docs): 更新面试题

  于 2025/2/8
• 72a9b-

  feat(docs): 更新面试题

  于 2025/2/8