like使用索引如何避免失效

1 准备数据

1.1 建表

  1. DROP TABLE IF EXISTS staff;
  2. CREATE TABLE IF NOT EXISTS staff (
  3. id INT PRIMARY KEY auto_increment,
  4. name ),
  5. age INT,
  6. pos ) COMMENT '职位',
  7. salary ,)
  8. );

1.2 插入数据

  1. , );

2 测试&Explain分析

2.1 有索引的情况下%的影响(提出问题)

2.1.1 建立索引

  1. CREATE INDEX idx_nameAgePos ON staff(name, age, pos);

2.1.2 测试&Explain分析

Case#1:两边都是%

  1. EXPLAIN SELECT * FROM staff WHERE name LIKE '%Alice%';

结果:type=all,全表扫描

Case#2:左边是%

  1. EXPLAIN SELECT * FROM staff WHERE name LIKE '%Alice';

结果:type=all,全表扫描

Case#3:右边是%

  1. EXPLAIN SELECT * FROM staff WHERE name LIKE 'Alice%';

结果:type=range,效果还可以。

对上面三个例子的总结:

  • 都是 SELECT *
  • %在左边,即使有索引,也会失效
  • 只有当%在右边时,才会生效

但问题是,生产环境中,就是要支持模糊查询(%在右边是不够的),一定要两边都是%来查询,这可咋办?

2.2 无索引情况下的查询汇总

2.2.1 删除索引

  1. DROP INDEX idx_nameAgePos ON staff;

2.2.2 测试&Explain分析(主要为了和 2.3.2 节做对比测试)

Case#4:查询Id

  1. EXPLAIN SELECT id FROM staff WHERE name LIKE '%Alice%';

Case#5:查询name

  1. EXPLAIN SELECT name FROM staff WHERE name LIKE '%Alice%';

Case#6:查询age

  1. EXPLAIN SELECT age FROM staff WHERE name LIKE '%Alice%';

Case#7:查询 id & name

  1. EXPLAIN SELECT id, name FROM staff WHERE name LIKE '%Alice%';

Case#8:查询 name & age

  1. EXPLAIN SELECT name, age FROM staff WHERE name LIKE '%Alice%';

Case#9:查询 id & name & age

  1. EXPLAIN SELECT id, name, age FROM staff WHERE name LIKE '%Alice%';

Case#10:查询 id & name & age & salary (提示:salary 不在索引列中,后面会用上)

  1. EXPLAIN SELECT id, name, age, salary FROM staff WHERE name LIKE '%Alice%';

Case#11:查询 *

  1. EXPLAIN SELECT * FROM staff WHERE name LIKE '%Alice%';

从 Case#4 到 Case#11 可以看出,在没有索引的情况下,两边都使用 % 来查询,不管想查询哪个字段(包括查询Id),全部都是全表扫描。

2.3 有索引情况下的查询汇总

2.3.1 建立索引

  1. CREATE INDEX idx_nameAgePos ON staff(name, age, pos);

2.3.2 测试&Explain分析

IndexCase#4:查询Id

  1. EXPLAIN SELECT id FROM staff WHERE name LIKE '%Alice%';

结果:使用上了索引(因为 name 有索引,同时查询的 Id 是主键肯定也有索引)

IndexCase#5:查询name

  1. EXPLAIN SELECT name FROM staff WHERE name LIKE '%Alice%';

结果:使用上了索引(因为查询条件和查询字段都是有索引的 name)

IndexCase#6:查询age

  1. EXPLAIN SELECT age FROM staff WHERE name LIKE '%Alice%';

结果:使用上了索引(因为查询条件的 name 以及查询字段的 age 都有索引)

IndexCase#7:查询 id & name

  1. EXPLAIN SELECT id, name FROM staff WHERE name LIKE '%Alice%';

结果:使用上了索引(因为查询条件的 name 以及查询字段的 id & name 都有索引)

IndexCase#8:查询 name & age

  1. EXPLAIN SELECT name, age FROM staff WHERE name LIKE '%Alice%';

结果:使用上了索引(因为查询条件的 name 以及查询字段的 name & age 都有索引)

IndexCase#9:查询 id & name & age

  1. EXPLAIN SELECT id, name, age FROM staff WHERE name LIKE '%Alice%';

结果:使用上了索引(因为查询条件的 name 以及查询字段的 id & name & age 都有索引)

IndexCase#10:查询 id & name & age & salary (提示:salary 不在索引列中)

  1. EXPLAIN SELECT id, name, age, salary FROM staff WHERE name LIKE '%Alice%';

结果:没有索引,type=all,全表扫描!(因为查询字段中多了个 salary 而 salary 不在索引列中)

IndexCase#11:查询 *

  1. EXPLAIN SELECT * FROM staff WHERE name LIKE '%Alice%';

结果:没有索引,type=all,全表扫描!(因为 * 包含 salary 而 salary 不在索引列中)

通过 IndexCase#4 到 IndexCase#11 可以看出,当真的需要两边都使用%来模糊查询时,只有当这个作为模糊查询的条件字段(例子中的name)以及所想要查询出来的数据字段(例子中的 id & name & age)都在索引列上时,才能真正使用索引,否则,索引失效全表扫描(比如多了一个 salary 字段)。我想,这应该就是 ‘覆盖索引(索引覆盖)’ 的本质吧。同时,这也能很好的证实 “尽量避免SELECT * 而是一一罗列出所需要查询的字段” 的道理吧,因为,搞不好 SELECT * 就多了一个字段,就导致了全表扫描。

3 结论

LIKE以%开头会导致索引失效;使用覆盖索引解决之

转载于:https://www.cnblogs.com/yhgn/p/11210130.html

当 SQL 中使用 LIKE 操作符进行模糊匹配时,如果没有正确使用索引,可能导致索引失效,从而影响查询性能。以下是一些可能导致 LIKE 索引失效的情况: 1. 前导通配符:如果 LIKE 表达式以通配符 '%' 开头,索引将无法有效利用。例如,"WHERE column LIKE '%abc'",这种情况下索引将无法生效。 2. 不适当的通配符位置:如果 LIKE 表达式中的通配符位于开头和结尾之间,而不是结尾之后,索引的效果可能减弱。例如,"WHERE column LIKE 'abc%def'",索引可能无法完全匹配。 3. 多个通配符:如果 LIKE 表达式中包含多个通配符,如 '%' 或 '_',索引可能无法生效。例如,"WHERE column LIKE '%abc%def%'",这种情况下索引可能无法使用。 4. 字符集问题:如果列的字符集与 LIKE 表达式中的字符集不匹配,索引可能无法使用。确保字符集一致可以提高索引的效果。 5. 数据类型问题:如果列的数据类型与 LIKE 表达式中的数据类型不匹配,索引可能无法使用。确保数据类型一致可以提高索引的效果。 为了让 LIKE 操作符在查询中有效利用索引,可以考虑以下方法: - 尽量避免在 LIKE 表达式的开头使用通配符。 - 使用索引友好的通配符位置,将通配符放在结尾之后。 - 避免使用多个通配符。 - 确保字符集和数据类型一致。 如果以上方法无法解决问题,可以考虑其他技术手段,如全文搜索引擎或者使用其他类型的索引(如全文索引),以满足模糊查询的需求。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值