关于模糊查询、索引下推、回表的两个问题

问题：

如果索引为(employee_id, department)，而查询语句为SELECT * FROM employees WHERE employee_id > 100 AND department like ‘%Engineering%’;

在这种情况下，有两个问题：

第一，LIKE ‘%Engineering%’这个条件是否运用了索引下推的机制？

第二，LIKE ‘%Engineering%’语句，其中department字段的值已经在索引当中了，为什么还需要回表？直接用索引中的值进行模糊匹配不就可以了吗

解答

第一个问题：LIKE ‘%Engineering%’ 这个条件是否运用了索引下推机制？

答案：不适用索引下推机制（Index Condition Pushdown, ICP）。

原因如下：

索引下推的机制作用：
索引下推（ICP）是在 MySQL 5.6+ 引入的一种优化技术，它让部分查询条件直接在存储引擎层（索引扫描阶段）进行过滤，以减少回表的次数。索引下推的前提是：过滤条件需要针对能够利用索引列的比较操作。
LIKE ‘%Engineering%’ 的本质：

LIKE '%Engineering%' 是一种模糊匹配，通配符 % 在前面的时候，数据库无法利用索引列的排序规则或范围查找功能。这个查询需要对该列逐行进行匹配，没有办法直接通过索引完成过滤。
因此，即使索引包含 department 字段，由于不能利用索引的检索特性，LIKE '%Engineering%' 无法通过索引下推在存储层完成过滤，最终只有回表获取完整记录后在 Server 层逐行处理。

索引下推主要支持的条件：
索引下推主要适用于以下情况：

范围查询（如 >、<、BETWEEN）。
等值查询（如 =、IN）。
可以利用索引字段的部分条件（如 LIKE 'Engineering%'，注意：前缀没有通配符 % 时，索引可以生效）。

第二个问题：LIKE ‘%Engineering%’ 中，department 字段的值已经在索引当中了，为什么还需要回表？直接用索引中的值进行模糊匹配不就可以了吗？

答案：由于索引结构的特性，必须回表。

原因如下：

1. 索引存储内容的特点

MySQL 索引节点通常包含两个部分：

索引列值：即组成索引的字段，例如联合索引 (employee_id, department)。employee_id 和 department 值存储在索引的每个节点中。
指向数据行的指针：每个索引指向表中的具体行，用于索引完成后查询表中的其他列。
存储引擎通过索引扫描时可以读取索引列的值，但如果查询需要访问其他列的数据（非索引列），则需要通过该指针回表进行数据读取。

2. 索引和匹配条件的结合

尽管 department 字段的值存储在索引中，MySQL 依然需要回表，因为：

索引的扫描效率：

MySQL 索引是基于 B+ 树（InnoDB 中）的数据结构实现的，设计的理念是高效地支持范围查找或等值匹配。
像 LIKE '%Engineering%' 这种需要对整个字段内容做逐字符比较的条件，不符合索引的快速查找逻辑。即使 department 的值在索引中，索引无法高效地处理这种不规则匹配条件。
索引的完整性和查询结果的要求：

假如查询结果需要其他非索引列的数据（比如 SELECT *），为了最终完整性，MySQL 仍需要回表取出所有列。
在你的示例中，查询的目标是 SELECT *，不仅需要 employee_id 和部分匹配 department 的值，还需要表中其他列的数据，因此必须回表。

3. 索引无法直接执行模糊匹配

索引中存储的是排序和组织优化后的键值，并没有存储字段的完整匹配结构。LIKE '%Engineering%' 算是一个复杂的字符串运算，MySQL 没有直接在索引中实现这个运算功能，而是需要读取完整数据行后再逐行检查字段内容。

举例说明：

假设有以下表和索引：

CREATE TABLE employees (
    employee_id INT NOT NULL,
    department VARCHAR(100),
    salary INT,
    PRIMARY KEY (employee_id),
    INDEX idx_department (employee_id, department)
);

执行以下查询：

SELECT * FROM employees WHERE employee_id > 100 AND department LIKE '%Engineering%';

流程分析：

索引范围扫描 (employee_id > 100) 将定位候选记录。
因为 LIKE '%Engineering%' 无法通过索引直接处理过滤条件，MySQL 将读取索引指向的表中的完整记录。
回表后，MySQL 对 department 字段逐行做模糊匹配（大多发生在 Server 层）。

为什么无法在索引中直接过滤？

回表主要是因为：

索引设计的优化方向：
索引是为了加速定位记录，而不是进行复杂的值匹配。模糊匹配或全文匹配是另一套优化机制（通常使用全文索引）。
你查询的是 SELECT *：

如果查询目标是索引就能覆盖的字段（比如 SELECT employee_id, department WHERE ...），MySQL 可以避免回表，使用**覆盖索引（Covering Index）**直接完成查询。
但如果查询需要其他字段（如 salary），必须回表取出完整记录。

解决问题的优化建议：

避免使用 % 通配符开头：
如果可能，调整查询条件，比如使用 LIKE 'Engineering%'（后缀通配符匹配），这样 department 可以在索引中直接完成过滤。
使用覆盖索引：
如果查询只涉及索引中的字段（如 employee_id 和 department），可以设计查询为只返回这些列，避免回表。例如：

SELECT employee_id, department FROM employees WHERE employee_id > 100 AND department LIKE '%Engineering%';

使用全文索引：
对于 LIKE '%Engineering%' 这种模糊匹配场景，可以考虑建立全文索引：

ALTER TABLE employees ADD FULLTEXT(department);
SELECT * FROM employees WHERE MATCH(department) AGAINST('Engineering');

总结：

LIKE '%Engineering%' 不能运用索引下推机制，因为模糊匹配没有办法利用索引的排序特性。
即使 department 的值在索引中，由于通配符 % 开头导致索引无法直接进行过滤，所以依然需要回表读取完整数据，并在 Server 层进行模糊匹配和过滤。

关于模糊查询、索引下推、回表的两个问题插图

关注公众号：程序新视界，一个让你软实力、硬技术同步提升的平台

除非注明，否则均为程序新视界原创文章，转载必须以链接形式标明本文链接

本文链接：http://folen.top/2025/09/18/mysql-icp-like/

secbro2

程序新视界，一个软实力、硬知识同时提升的平台

1340

问题：

解答

第一个问题：LIKE ‘%Engineering%’ 这个条件是否运用了索引下推机制？