为何说MySQL中LIKE语句效率低

在 MySQL 中，LIKE 语句的效率通常较低，主要是因为它对索引的利用有限，尤其是使用前导通配符（%xxx）时。下面是几个关键原因：

---

1. LIKE '%xxx' 不能使用索引，导致全表扫描

问题

当 LIKE 以 % 开头时，MySQL 无法使用 B-tree 索引（如 B+ 树索引），只能进行全表扫描。

SELECT * FROM users WHERE name LIKE '%bob';

原因：

• B-tree 索引是按前缀索引的，必须从头匹配才能使用索引。
• 例如，索引存储 Alice、Bob、Charlie，如果查找 %bob，就无法利用索引加速查找，只能逐行扫描。

优化

• 使用后缀匹配 (xxx%)

  ：索引仍然有效

  SELECT * FROM users WHERE name LIKE 'bob%';  -- ✅ 可用索引
  

• 使用 FULLTEXT 索引

  （适用于长文本匹配）：

  ALTER TABLE users ADD FULLTEXT(name);
  SELECT * FROM users WHERE MATCH(name) AGAINST('bob');
  

---

2. LIKE '%xxx%' 不能使用索引

SELECT * FROM users WHERE name LIKE '%bob%';

• 不能利用索引，必须扫描每一行数据，再进行匹配，影响性能。
• 影响：当表有百万级数据时，查询变慢。

优化

• 使用 FULLTEXT 搜索

• 使用 INSTR() 或 REGEXP 可能更灵活

  SELECT * FROM users WHERE INSTR(name, 'bob') > 0;
  

---

3. LIKE 'xxx%' 可以使用索引

如果 LIKE 只是后缀通配符（不在开头），那么 MySQL 可以使用索引，查询效率高。

SELECT * FROM users WHERE name LIKE 'bob%';

• 索引可用 ✅

• 匹配示例

  ：

  • bob ✅
  • bobby ✅
  • alice ❌

原因：B-tree 索引按字典顺序存储，可以快速找到以 bob 开头的数据范围。

---

4. LIKE BINARY 导致索引失效

• LIKE BINARY
  

  区分大小写

  ，但会让索引失效：

  SELECT * FROM users WHERE name LIKE BINARY 'bob%';
  

• 解决方案：

  给列使用 BINARY 属性

  或

  使用 COLLATE

  ALTER TABLE users MODIFY name VARCHAR(255) BINARY;
  

---

5. 影响查询性能的因素

✔ 索引可用情况

查询方式	索引是否可用	备注
LIKE 'bob%'	✅ 索引可用	前缀匹配
LIKE '%bob'	❌ 索引失效	需要全表扫描
LIKE '%bob%'	❌ 索引失效	需要全表扫描
LIKE BINARY 'bob%'	❌ 索引失效	强制区分大小写
MATCH() AGAINST()	✅ 全文索引可用	适用于长文本

---

6. LIKE 查询优化方案

1. 避免 % 放在开头：

   -- 不推荐：
   SELECT * FROM users WHERE name LIKE '%bob';  -- ❌
   
   -- 推荐：
   SELECT * FROM users WHERE name LIKE 'bob%';  -- ✅
   

2. 使用 FULLTEXT 索引（适用于文本搜索）：

   ALTER TABLE users ADD FULLTEXT(name);
   SELECT * FROM users WHERE MATCH(name) AGAINST('bob');
   

3. 考虑 REGEXP 或 INSTR()（适用于复杂匹配）：

   SELECT * FROM users WHERE name REGEXP 'bob';
   SELECT * FROM users WHERE INSTR(name, 'bob') > 0;
   

4. 使用 LIKE + 覆盖索引

   • 让查询只使用索引，不访问数据行：

   SELECT id FROM users WHERE name LIKE 'bob%';
   

---

总结

查询方式	是否使用索引	影响
LIKE 'xxx%'	✅ 可用索引	快速查询
LIKE '%xxx'	❌ 索引失效	全表扫描，性能低
LIKE '%xxx%'	❌ 索引失效	全表扫描，性能低
MATCH() AGAINST()	✅ 全文索引可用	适合长文本
LIKE BINARY	❌ 索引失效	需要全表扫描

🚀 优化建议：

• 避免 % 开头的 LIKE，改用 LIKE 'xxx%'
• 使用 FULLTEXT 索引代替 LIKE '%xxx%'
• 在小数据量时 LIKE 影响不大，但在大表中应避免

✅ 结论：
LIKE 效率低的核心原因是索引失效（尤其是 %xxx），可以通过索引优化或全文搜索提高性能！