模糊查询%在最前面，能否或者怎么使用索引

最新推荐文章于 2024-01-03 21:08:51 发布

原创最新推荐文章于 2024-01-03 21:08:51 发布 · 2.6k 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

数据库专栏收录该内容

6 篇文章

订阅专栏

本文探讨了SQL中模糊查询（like语句）与索引使用的复杂关系，特别是当查询字符串以通配符%开头时的情况。文章详细介绍了如何通过反转函数和建立相应的索引，使查询效率得到提升，避免全表扫描。同时，分析了在不同场景下，如仅选择索引字段或主键，以及where条件包含主键时，索引的有效利用。

前言

众所周知，在索引时，如果模糊查询的%置于最前面，索引会失效。但是在%前置时，什么情况下会使用到索引？

补充

like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。
like keyword% 索引有效。
like %keyword% 索引失效，也无法使用反向索引。

解决

方式一

反转模糊查询的字段，但是注意，对于"%keywork%"的索引，此方法是无效的。

select * from company where companyName like '%江南皮革长';

我们可以改造为：

select * from company where reverse(companyName) like reverse('%江南皮革长');

方式二

首先我们有TEST_USER表：

CREATE TABLE `USER` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `NAME` varchar(64) DEFAULT NULL COMMENT '名字',
  `MOBILE` varchar(11) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`ID`),
  KEY `INDEX_MOBILE` (`MOBILE`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试的用户表';

1.只查询索引

EXPLAIN SELECT
	MOBILE
FROM
	USER
WHERE
	MOBILELIKE '%151'

图片.png
Extra:Using index. (走索引)

2.查索引字段和主键

EXPLAIN SELECT
	ID,MOBILE
FROM
	USER
WHERE
	MOBILE LIKE '%151'

图片.png

Extra:Using index. (走索引)

3.查非索引字段

EXPLAIN SELECT
	MOBILE,NAME
FROM
	USER
WHERE
	MOBILE LIKE '%151'

图片.png
Extra:Using where. (不走索引)

4.where后面多条件

4.1where后跟普通条件

EXPLAIN SELECT
	MOBILE
FROM
	USER
WHERE
	NAME = '江南皮革厂'
AND MOBILE LIKE '%151'

图片.png
Extra:Using where. (不走索引)

4.2where后跟主键

EXPLAIN SELECT
	MOBILE
FROM
	USER
WHERE
	ID >1
AND MOBILE LIKE '%151'

图片.png
Extra:Using index. (走索引)

总结

like查询百分号前置，并不是100%不会走索引。
如果只select索引字段，或者select索引字段和主键，也会走索引的。
如果where条件中有主键时，走索引。

一句话：在select和where中存在除了索引和主键外的其他条件或字段时，不走索引。

个人博客
 腾讯云社区
 掘金
 优快云
 简书
 GitHub
码云
 OSCHINA
Segmentfault
公众号：