MySQL LIKE查询为什么不走索引？前缀索引的使用场景

在数据库优化中，LIKE 查询导致的性能问题非常普遍。理解索引失效的底层原因，并掌握前缀索引的正确用法，是提升查询效率的关键。

理解 LIKE 查询与索引的关系

MySQL 中的 InnoDB 引擎使用 B+ 树结构存储索引。B+ 树的数据按照顺序排列，类似于字典的目录。

使用 LIKE 'keyword%'（前缀匹配）
当查询条件以确定的字符串开头时，数据库可以利用 B+ 树的顺序特性。它会快速定位到 keyword 的起始位置，然后向后扫描，直到遇到不符合条件的记录。

使用 LIKE '%keyword' 或 LIKE '%keyword%'（后缀或全模糊匹配）
当查询条件以通配符 % 开头时，意味着第一个字符是不确定的。在 B+ 树中，要找到符合条件的记录，必须跳过“按顺序查找”这一步。

数据库无法直接通过索引树定位，只能选择“全表扫描”（读取表中的每一行数据）并逐一比对。这就是为何以 % 开头的查询不走索引的核心原因。

以下流程图展示了 MySQL 的决策逻辑：

前缀索引的使用场景

当需要对较长的字符串列（如 VARCHAR(255) 的 URL、邮箱地址）建立索引时，直接对整个列建立索引存在两个问题：

1. 存储空间大：索引文件会变得非常大，占用磁盘。
2. IO 效率低：索引越大，内存中能存放的索引页越少，增加了磁盘读取次数。

前缀索引通过只对字符串前 N 个字符建立索引，有效解决了上述问题。

适用场景

• 列数据较长（如长文本、URL）。
• 字符串的前几位具有很高的区分度（选择性）。
• 查询主要是“前缀匹配”（如 LIKE 'abc%'）或精确匹配。

不适用场景

• 查询经常使用 LIKE '%keyword'（前缀索引对此无效，依然全表扫描）。
• 字符串前几位区分度极低（如身份证号前几位都是地区码，重复率高）。
• 需要利用索引进行 ORDER BY 或 GROUP BY 操作（前缀索引无法完成排序，必须回表查询）。

实操步骤：分析并创建前缀索引

假设有一张用户表，包含 email 字段，我们需要优化针对邮箱的查询。

1. 创建测试表并插入数据

执行 以下 SQL 语句，创建一个包含 email 字段的表：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(50)
);

插入 随机模拟数据（示例）：

INSERT INTO users (email, username) VALUES 
('zhangsan@example.com', 'zhangsan'),
('zhangsi@test.com', 'zhangsi'),
('lisi@example.com', 'lisi'),
('wangwu@demo.org', 'wangwu');
-- 实际场景中应插入大量数据以观察性能差异

2. 计算最佳前缀长度

在创建前缀索引前，需要计算选择合适的长度 N。我们要找到一个长度，使得“不重复的前缀数量”接近“总记录数”。

选择性的计算公式如下：

$$ \text{Selectivity} = \frac{\text{Count(DISTINCT LEFT(column, n))}}{\text{Count(*)}} $$

依次执行 以下 SQL，测试不同长度的选择性：

-- 计算完整列的选择性
SELECT COUNT(DISTINCT email) / COUNT(*) FROM users;

-- 计算前3个字符的选择性
SELECT COUNT(DISTINCT LEFT(email, 3)) / COUNT(*) AS sel_3 FROM users;

-- 计算前6个字符的选择性
SELECT COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) AS sel_6 FROM users;

观察结果，寻找选择性接近 1.0（或接近完整列选择性）的最小长度。

假设结果如下表所示（数值仅为示例）：

根据表数据，选择长度为 9 既能保持高区分度，又能节省空间。

3. 创建前缀索引

执行 以下命令，基于分析结果创建前缀索引：

CREATE INDEX idx_email_prefix ON users(LEFT(email, 9));

4. 验证索引使用情况

执行 一个前缀匹配的查询，并查看执行计划：

EXPLAIN SELECT * FROM users WHERE email LIKE 'zhangsan%@example.com';

在输出结果中，查看 key 列。如果显示 idx_email_prefix，说明索引生效成功。

5. 测试无法命中索引的场景

执行 以下后缀模糊匹配查询：

EXPLAIN SELECT * FROM users WHERE email LIKE '%@example.com';

查看 type 列。如果显示 ALL，且 key 列为 NULL，说明进行了全表扫描。这证实了前缀索引（以及普通索引）无法解决以 % 开头的查询问题。

覆盖索引与前缀索引的注意事项

如果查询只需要索引列，MySQL 可以直接从索引中读取数据而不需要回表，这叫“覆盖索引”。但对于前缀索引，由于只截取了部分字符，索引中不包含完整的列值。

执行 以下查询：

SELECT email FROM users WHERE email LIKE 'zhangsan%';

即便使用了 idx_email_prefix，MySQL 依然必须回表去读取完整的 email 字段内容，因为索引里只有前 9 个字符。这会导致“回表”操作增加 IO 开销。因此，在使用前缀索引时，要权衡“索引体积减小”与“回表次数增加”之间的利弊。