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