索引法则--LIKE以%开头会导致索引失效进而转向全表扫描(使用覆盖索引解决)

 

Mysql 系列文章主页 

 

===============

 

1 准备数据

1.1 建表

DROP TABLE IF EXISTS staff;
CREATE TABLE IF NOT EXISTS staff (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(50),
    age INT,
    pos VARCHAR(50) COMMENT '职位',
    salary DECIMAL(10,2)
);

1.2 插入数据

INSERT INTO staff(name, age, pos, salary) VALUES('Alice', 22, 'HR', 5000);

2 测试&Explain分析

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

2.1.1 建立索引

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

2.1.2 测试&Explain分析

Case#1:两边都是%

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

结果:type=all,全表扫描

Case#2:左边是%

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

结果:type=all,全表扫描

Case#3:右边是%

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

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

对上面三个例子的总结:

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

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

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

2.2.1 删除索引

DROP INDEX idx_nameAgePos ON staff;

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

Case#4:查询Id

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

Case#5:查询name

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

Case#6:查询age

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

Case#7:查询 id & name

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

Case#8:查询 name & age

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

Case#9:查询 id & name & age

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

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

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

Case#11:查询 *

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

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

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

2.3.1 建立索引

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

2.3.2 测试&Explain分析

IndexCase#4:查询Id

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

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

IndexCase#5:查询name

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

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

IndexCase#6:查询age

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

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

IndexCase#7:查询 id & name

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

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

IndexCase#8:查询 name & age

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

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

IndexCase#9:查询 id & name & age

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

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

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

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

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

IndexCase#11:查询 *

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以%开头会导致索引失效;使用覆盖索引解决之

 

posted @ 2018-04-24 15:10  cyhbyw  阅读(1122)  评论(1编辑  收藏  举报