共计 860 个字符,预计需要花费 3 分钟才能阅读完成。
通配符结尾的含糊查问
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%';
正文完