日常开发中,我们都写过类似以下这样的SQL:
select * from t_jar where type like '%jar%';
select * from t_jar where type like '%jar';
select * from t_jar where type like 'jar%';
3条SQL中只是最后的字符串里百分号%位置不同,其他都一样。而我们主要是关注一下它们的索引使用情况。
先看一下用来测试的表t_jar:
CREATE TABLE `t_jar` (
`uuid` varchar(50) NOT NULL,
`name` varchar(200) DEFAULT NULL,
`path` text,
`updateDate` datetime DEFAULT NULL,
`type` varchar(100) DEFAULT NULL,
`click` int(11) DEFAULT NULL,
`downHit` int(11) DEFAULT NULL,
`indexState` int(11) DEFAULT '0',
`tagState` int(11) DEFAULT '0',
`publishStatus` int(11) DEFAULT '0',
PRIMARY KEY (`uuid`),
KEY `index_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
查看这张表现在的索引情况:
show index from t_jar;
![93d48b4bb464dcb03a89869f4c535a9a.png](https://img-blog.csdnimg.cn/img_convert/93d48b4bb464dcb03a89869f4c535a9a.png)
除了主键uuid,还有字段type也建了索引。
现在用explian查看开头3条SQL的执行计划:
![cb09c285ca61c2aded6f2b72031b0c6d.png](https://img-blog.csdnimg.cn/img_convert/cb09c285ca61c2aded6f2b72031b0c6d.png)
从结果看到,3条语句都是全表扫描,第3条语句可能会使用到索引index_type,但和其余两条一样实际都没有用上索引。
这说明查询语句中使用like,无论百分号在哪个位置都会使索引失效(我的MySQL版本是5.7.26),但是在生产环境中有时我们不得不使用like查询,所以我们需要解决这个问题。
一般推荐使用覆盖索引来解决(覆盖索引的概念不在此复述)
现在看以下3条SQL:
select uuid from t_jar where type like '%jar%';
select type from t_jar where type like '%jar%';
select uuid,type from t_jar where type like '%jar%';
explain分析结果:
![1d0441f18e8766aa08a2bba2cde6e4cf.png](https://img-blog.csdnimg.cn/img_convert/1d0441f18e8766aa08a2bba2cde6e4cf.png)
三条语句都使用到了索引,因为uuid为主键,和type字段是表的索引,无论查一个字段还是查两个字段都在索引中。
再看一条语句,查多一个非索引的name列:
select uuid,type,name from t_jar where type like '%jar%';
![472d799b64ab99c975d845eb68fbd05a.png](https://img-blog.csdnimg.cn/img_convert/472d799b64ab99c975d845eb68fbd05a.png)
此时索引失效了,并导致全表扫描。覆盖索引需要和我们查询的列与表的索引一致,当然因为现在不是复合索引,所以可以没有最优左前缀法则的问题。以上语句加入name字段后,这就像我们家里的锅,锅就这么大,但锅盖买大了。
所以,使用like查询会索引失效导致全表扫描,影响性能,为此推荐大家尽量地使用覆盖索引。