关于mysql:MySQL-索引在-like-查询中的使用情况及覆盖索引优化-like-查询

通配符结尾的含糊查问

like 查问以通配符结尾时无奈应用索引而应用全表扫描。

EXPLAIN SELECT * FROM staffs WHERE `name` like '%July%';
EXPLAIN SELECT * FROM staffs WHERE `name` like '%July';

通配符结尾的含糊查问

like 查问以通配符结尾能够应用索引，类型为范畴（range）。

EXPLAIN SELECT * FROM staffs WHERE `name` like 'July%';

应用笼罩索引的含糊查问

应用 笼罩索引 能够解决 like 查问以通配符结尾无奈应用索引问题。

ALTER TABLE `tbl_user` ADD INDEX idx_user_nameAge(`name`, `age`);

EXPLAIN SELECT id FROM tbl_user WHERE `name` like '%July%';
EXPLAIN SELECT `name` FROM tbl_user WHERE `name` like '%July%';
EXPLAIN SELECT age FROM tbl_user WHERE `name` like '%July%';

EXPLAIN SELECT id,`name`,age FROM tbl_user WHERE `name` like '%July%';
EXPLAIN SELECT id,`name` FROM tbl_user WHERE `name` like '%July%';
EXPLAIN SELECT id,age FROM tbl_user WHERE `name` like '%July%';
EXPLAIN SELECT `name`,age FROM tbl_user WHERE `name` like '%July%';

超出笼罩索引范畴的含糊查问

超出笼罩索引范畴无奈应用索引。

EXPLAIN SELECT * FROM tbl_user WHERE `name` like '%July%';
EXPLAIN SELECT id,`name`,age,email FROM tbl_user WHERE `name` like '%July%';