like语句百分号前置会使用到索引吗？

最新推荐文章于 2025-05-22 07:30:00 发布
转载 最新推荐文章于 2025-05-22 07:30:00 发布 · 91 阅读 · 0
文章标签：

#数据库

like语句百分号前置会使用到索引吗？


前几天看了这篇文章：谈SQL Server对like '%关键词%' 处理时的索引利用问题

看完了之后，我很想知道这篇文章是不是临时工写的？还是网站的主人写的，网站的主人的微博我都有关注（在微博里私信过）

是某个公司的DBA，这里先不管他是不是临时工写的，今天我也研究一下这个问题o(∩_∩)o

 

说明：我们说的走索引指的是：聚集索引查找、非聚集索引查找

而全表扫描、聚集索引扫描、非聚集索引扫描都不是走索引

 

而这里说的走索引跟全文搜索/全文索引没有关系  SQLSERVER全文搜索

全文搜索/全文索引已经是另外一种技术了

聚集索引表
SQL脚本如下：

 1 --聚集索引表
 2 USE [pratice]
 3 GO
 4 CREATE TABLE Department(
 5     DepartmentID int IDENTITY(1,1) NOT NULL ,
 6     GroupName NVARCHAR(20) NOT NULL,
 7     Company NVARCHAR(20),
 8 )
 9 
10 CREATE CLUSTERED INDEX CL_GroupName ON [dbo].[Department](GroupName ASC)
11 
12 DECLARE @i INT
13 SET @i=1
14 WHILE @i < 100000 
15     BEGIN
16         INSERT  INTO Department (  [Company], groupname )
17         VALUES  (  '中国你好有限公司XX分公司'+CAST(@i AS VARCHAR(20)), '销售组'+CAST(@i AS VARCHAR(20)) )
18         SET @i = @i + 1
19     END
20 
21 
22 SELECT * FROM Department
View Code

表数据


聚集索引创建在GROUPNAME这个字段上，我们就查找GROUPNAME这个字段

如果看过我以前写的文章的人肯定知道：查找只会出现在建立索引的时候的第一个字段（这里我就不再详细叙述了）

1 --聚集索引查找
2 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '销售组1000'
3 --聚集索引扫描
4 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '%销售组1000%'
5 --聚集索引扫描
6 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '%销售组1000'
7 --聚集索引查找
8 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '销售组1000%'

--------------------------------------------------------------------------

IO和时间统计

 1 SET STATISTICS IO ON
 2 SET STATISTICS TIME ON
 3 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '销售组1000'
 4 SET STATISTICS IO OFF
 5 SET STATISTICS TIME OFF
 6 
 7 SET STATISTICS IO ON
 8 SET STATISTICS TIME ON
 9 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '%销售组1000%'
10 SET STATISTICS IO OFF
11 SET STATISTICS TIME OFF
12 
13 SET STATISTICS IO ON
14 SET STATISTICS TIME ON
15 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '%销售组1000'
16 SET STATISTICS IO OFF
17 SET STATISTICS TIME OFF
18 
19 SET STATISTICS IO ON
20 SET STATISTICS TIME ON
21 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '销售组1000%'
22 SET STATISTICS IO OFF
23 SET STATISTICS TIME OFF
View Code 
 1 (1 行受影响)LIKE '销售组1000'
 2 表 'Department'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
 3 
 4 SQL Server 执行时间:
 5    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
 6 
 7 SQL Server 执行时间:
 8    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
 9 
10 (11 行受影响)LIKE '%销售组1000%'
11 表 'Department'。扫描计数 1，逻辑读取 448 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
12 
13 SQL Server 执行时间:
14    CPU 时间 = 47 毫秒，占用时间 = 47 毫秒。
15 
16 SQL Server 执行时间:
17    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
18 
19 (1 行受影响)LIKE '%销售组1000'
20 表 'Department'。扫描计数 1，逻辑读取 448 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
21 
22 SQL Server 执行时间:
23    CPU 时间 = 47 毫秒，占用时间 = 40 毫秒。
24 
25 SQL Server 执行时间:
26    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
27 
28 (11 行受影响)LIKE '销售组1000%'
29 表 'Department'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
30 
31 SQL Server 执行时间:
32    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
33 
34 SQL Server 执行时间:
35    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 

只有LIKE '销售组1000'和LIKE '销售组1000%'用到了查找

为什麽？我会在文中最后说明

非聚集索引表

SQL脚本如下：

我们drop掉刚才的department表

1 DROP TABLE [dbo].[Department]

重新建立department表

 1 --非聚集索引表
 2 USE [pratice]
 3 GO
 4 CREATE TABLE Department(
 5     DepartmentID int IDENTITY(1,1) NOT NULL ,
 6     GroupName NVARCHAR(20) NOT NULL,
 7     Company NVARCHAR(20),
 8 )
 9 
10 CREATE  INDEX CL_GroupName ON [dbo].[Department](GroupName ASC)
11 
12 DECLARE @i INT
13 SET @i=1
14 WHILE @i < 100000 
15     BEGIN
16         INSERT  INTO Department (  [Company], groupname )
17         VALUES  (  '中国你好有限公司XX分公司'+CAST(@i AS VARCHAR(20)), '销售组'+CAST(@i AS VARCHAR(20)) )
18         SET @i = @i + 1
19     END
20 
21 
22 SELECT * FROM Department
View Code

非聚集索引依然建立在GROUPNAME这个字段上

 

表数据


同样，我们使用上面讲解聚集索引表时候的查询语句

1 --非聚集索引查找  RID查找
2 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '销售组1000'
3 --非聚集索引扫描  RID查找
4 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '%销售组1000%'
5 --非聚集索引扫描   RID查找
6 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '%销售组1000'
7 --非聚集索引查找    RID查找
8 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '销售组1000%'

因为是select * 所以SQLSERVER需要到数据页面去找其他的字段数据，使用到RID查找

这里的结果跟聚集索引是一样的

-------------------------------------------------------------------------------------------

IO和时间统计

 1 SET STATISTICS IO ON
 2 SET STATISTICS TIME ON
 3 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '销售组1000'
 4 SET STATISTICS IO OFF
 5 SET STATISTICS TIME OFF
 6 
 7 SET STATISTICS IO ON
 8 SET STATISTICS TIME ON
 9 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '%销售组1000%'
10 SET STATISTICS IO OFF
11 SET STATISTICS TIME OFF
12 
13 SET STATISTICS IO ON
14 SET STATISTICS TIME ON
15 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '%销售组1000'
16 SET STATISTICS IO OFF
17 SET STATISTICS TIME OFF
18 
19 SET STATISTICS IO ON
20 SET STATISTICS TIME ON
21 SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '销售组1000%'
22 SET STATISTICS IO OFF
23 SET STATISTICS TIME OFF
View Code 
 1 (1 行受影响) LIKE '销售组1000'
 2 表 'Department'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
 3 
 4 SQL Server 执行时间:
 5    CPU 时间 = 0 毫秒，占用时间 = 62 毫秒。
 6 
 7 SQL Server 执行时间:
 8    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
 9 
10 (11 行受影响)LIKE '%销售组1000%'
11 表 'Department'。扫描计数 1，逻辑读取 92 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
12 
13 SQL Server 执行时间:
14    CPU 时间 = 16 毫秒，占用时间 = 17 毫秒。
15 
16 SQL Server 执行时间:
17    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
18 
19 (1 行受影响)LIKE '%销售组1000'
20 表 'Department'。扫描计数 1，逻辑读取 82 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
21 
22 SQL Server 执行时间:
23    CPU 时间 = 15 毫秒，占用时间 = 17 毫秒。
24 
25 SQL Server 执行时间:
26    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
27 
28 (11 行受影响)LIKE '销售组1000%'
29 表 'Department'。扫描计数 1，逻辑读取 13 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
30 
31 SQL Server 执行时间:
32    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
33 
34 SQL Server 执行时间:
35    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 

为什麽只有LIKE '销售组1000'和LIKE '销售组1000%'用到了查找???

如果阁下曾经有看过我写的

SQLSERVER聚集索引与非聚集索引的再次研究（上）
SQLSERVER聚集索引与非聚集索引的再次研究（下）

您就会知道在聚集索引页面和非聚集索引页面里都有一个KeyHashValue的字段

聚集索引页面

非聚集索引页面

 

当使用 '%销售组1000%'和'%销售组1000'的时候，因为是模糊匹配（百分号前置）

SQLSERVER不会去匹配hash值（KeyHashValue），直接扫描(SCAN)算了

但是使用'销售组1000'和'销售组1000%'的时候

'销售组1000' ：SQLSERVER能够准确匹配到唯一的一个hash值

'销售组1000%'：SQLSERVER会匹配与销售组1000相同的hash值

与销售组1000%匹配的记录会有多个，所以逻辑读取次数也会有多次

所以，'销售组1000'和'销售组1000%'能够使用查找(SEEK)

总结

只有了解了SQLSERVER的内部原理，才能够明白更多

 

注意：我这里并没有将非聚集索引扫描纳入到“走索引”这个分类，如果将非聚集索引扫描纳入到“走索引”这个分类里

那么我的朋友的文章就是对的，随便加个非聚集索引，让表扫描/聚集索引扫描变成非聚集索引扫描，就认为是走索引

（虽然非聚集索引扫描比聚集索引扫描/表扫描快，IO少）

那么下面四个语句都是属于走索引，没有什么好讨论的，我们讨论的前提是在基础表上不加任何东西，如果在做实验的过程中

随便加个非聚集索引，然后走非聚集索引扫描就说走索引，那么这篇文章就没有意义了，经过再三斟酌，

我决定将“非聚集索引扫描”移出“走索引”这个分类，毕竟查找(SEEK)比扫描(SCAN)快


SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '销售组1000'
SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '%销售组1000%'
SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '%销售组1000'
SELECT * FROM [dbo].[Department] WHERE [GroupName] LIKE '销售组1000%'

 

最最后，补充说一下

我们判断一个执行计划的性能的好坏的标准是什么？？

就是哪个执行计划的逻辑读次数最少

Logical reads：包含该语句从内存数据缓冲区中访问的页数和从物理磁盘读取的页数。

如果全表扫描/聚集索引扫描所使用的逻辑读比聚集索引查找/非聚集索引查找使用的逻辑读要少，

或者全表扫描比非聚集索引扫描使用的逻辑读要少

那么SQLSERVER选择全表扫描/聚集索引扫描这个执行计划就是好的

有些人为了让SQLSERVER使用索引，不惜代价使用查询提示，让SQLSRVER去走索引，这样是得不偿失的

我们的最终目的是：减少逻辑读次数，不要为了索引而索引！！

当然，我这里的实验环境跟各位的真实环境会有差别，不过“逻辑读次数”这个标准无论是哪个环境都是一样的！！

我说完了，谢谢大家o(∩_∩)o

 

physical reads：表示那些没有驻留在内存缓冲区中需要从磁盘读取的数据页。
Read-ahead reads是SQL Server为了提高性能而产生的预读。

如有不对的地方，欢迎大家拍砖o(∩_∩)o

sql性能优化：sql处理千百万级以上的数据提高查询速度的方法，查询速度慢怎么处理
橙-极纪元-JJYCheng
11-29 1万+
处理百万级以上的数据提高查询速度的方法： 1.应尽量避免在 where 子句中使用!=或&lt;&gt;操作符，否则将引擎放弃使用索引而进行全表扫描。 2.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： ...
MySQL索引，查询中like 百分号 能用索引吗？
qidan3500的博客
08-03 1343
大家好，我是知数堂SQL 优化班老师 网名：骑龟的兔子 上述问题的一般情况下，答案肯定是不能，但既然提问了，那肯定有能用的情况 为此，详细介绍下 首先我们先创建一个表 show create table test1\G *************************** 1. row *************************** Table: test1 Create Table: ...
索引法则--LIKE以%开头会导致索引失效进而转向全表扫描（使用覆盖索引解决）
Stone.鱼儿的博客
09-18 3416
什么是覆盖索引？ 解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。 解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。 （即select和where条件中的字段都出现在索引中，即为覆盖索引） 解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索..
LIKE前后置%+复合索引的索引使用情况
qq_34486648的博客
03-14 726
首先建表 create table test03( id int primary key not null auto_increment, c1 varchar(10), c2 varchar(10), c3 varchar(10), c4 varchar(10), c5 varchar(10)); insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5'); insert into test03(c1,c2,c3,c4,c5
索引覆盖与回表
AF_0228的博客
09-09 5965
懂的越多，不懂的越多 上回书说到：什么情况下，索引下推没办法提高sql查询效率？ 表 info 主键 id 名称 name 值 value 别名 realname 对于info表，我们现在有（name,value）联合索引
like百分号前置不会走索引？NO！
卖克的专栏
01-22 7255
　　“模糊查询，前置百分号不走索引；后置百分号才会走索引”这可能是大部分人都知道的“常识”，然而，这周在做SQL优化的时候，无意中碰到了意外情况–模糊查询，前置百分号也走索引！ 举个栗子 表： TEST_USER 索引：INDEX_MOBILE CREATE TABLE `TEST_USER` ( `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT ...
like百分号在前走不走索引
vonlinee的博客
09-25 492
MOBILE FROM t_user WHERE MOBILE LIKE '%12’这种情况没走是因为查了非索引字段，需要回表查询name字段值，如果使用INDEX_MOBILE索引，则要进行一次全索引扫描，同时还要进行一次聚簇索引扫描（即全表扫描），不如直接进行全表扫描。通过EXPLAIN ANALYZE可以看到，过滤成本是13484.35，扫描了14865行，整张表的数据150369条，而符合条件的有1493条，也就是说为了找出这1493条数据，扫描了133801行，大约占全表的89%左右。
30种MySQL查询语句优化方法分享
技术专家
06-16 2962
30种MySQL查询语句优化方法分享，对于MySQL查询语句来说效率是最重要的能用一分钟搞定的绝不能花费两分钟。今天整理了30种MySQL查询语句优化方法，供大家学习使用。
30种SQL查询语句优化方法
庄小法的博客
09-19 1389
15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert。3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进。如果数据量不大，为了缓和系统表的资源，应先。尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全。8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行。9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全。
【后端高阶面经：数据库篇】10、MySQL索引深度解析：为什么B+树是最优选择？
RickyIT的专栏
05-22 719
B+树与B树在数据库底层逻辑中的核心差异主要体现在数据存储结构、磁盘I/O优化和并发控制等方面。B+树通过将数据记录仅存储在叶子节点，非叶子节点仅存储键值，显著减少了树高和磁盘I/O次数，提升了查询效率。相比之下，B树在每个节点都存储键值和数据记录，导致树高较高，查询时需要更多的磁盘I/O。此外，B+树在范围查询和并发控制方面也表现出色，叶子节点通过双向链表连接，支持高效的范围查询，且修改操作集中在叶子节点，减少了锁竞争。在MySQL中，B+树被广泛应用于聚簇索引和非聚簇索引，通过优化索引设计和查询语句，可
like百分号在前面是否不走索引，百分号在后面呢
cjh81378066的博客
07-08 2335
总结如下： %号在前或在后都不一定走索引，和数据量有关，走不走索引说白了还是看实际情况的成本多少。 CREATE ...
模糊查询%在最前面，能否或者怎么使用索引
少年闰土的博客
05-22 2638
前言 众所周知，在索引时，如果模糊查询的%置于最前面，索引会失效。但是在%前置时，什么情况下会使用到索引？ 补充 like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。 like keyword% 索引有效。 like %keyword% 索引失效，也无法使用反向索引。 解决 方式一 反转模糊查询的字段，但是注意，对于"%keywork%"的索引，此方法是无效的。 select * from company w
like查询与索引
wjxbj的博客
08-04 1427
一.like查询与索引         在oracle里的一个超级大的表中,我们的where条件的列有建索引的话，会走索引唯一扫描INDEX UNIQUE SCAN。如select * from table where code = 'Cod25'，而如下这些语句哪些会走索引呢？ select * from table where code like 'Code2%' select * ...
【mysql 5.7】最左原则 & like查询%在前为什么不走索引
m0_45406092的博客
01-09 3904
《从根上理解SQL的like查询%在前为什么不走索引?》
通配符会不会影响sql  索引
weixin_33869377的博客
05-25 286
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql中为什么使用百分号%通配符会导致索引失效
最新发布
08-06
<think>我们正在讨论MySQL中LIKE查询使用百分号%导致索引失效的问题。根据提供的引用材料，我们可以总结如下： 1. 引用[1]指出：LIKE 'A%' 可以使用索引（如果存在），因为它是前缀匹配。而如果模式以通配符开头（如'%A'），则会导致全表扫描，因为索引无法有效使用。 2. 引用[2]进一步解释：B-tree索引是顺序索引，只有在字符串开头匹配时（例如LIKE '10086%'）才能利用索引。如果%放在开头（如'%10086%'），常规索引失效。 3. 引用[3]通过示例说明：使用前导通配符（如'A%'）可以利用索引，而后置通配符（如'%A'）则不能。 现在，我们需要深入解释为什么百分号通配符在开头会导致索引失效。 ### 原因分析： 1. **B-tree索引的结构特性**： MySQL的索引（如InnoDB的B+树索引）是按照列值的**字典序**存储的。这意味着索引中的字符串是按照从第一个字符开始排序的。例如，字符串"apple"、"banana"、"cherry"在索引中按首字母排序。 2. **前缀匹配的索引利用**： 当使用`LIKE 'A%'`时，由于索引是按字符顺序排列的，MySQL可以快速定位到以"A"开头的索引项，然后顺序扫描后续索引直到不以"A"开头为止。这是一个高效的范围扫描。 3. **通配符在开头的失效原因**： 当模式以通配符开头（如`LIKE '%A'`或`LIKE '%A%'`）时，需要查找以任意字符开头但以"A"结尾或中间包含"A"的字符串。由于索引是前缀排序的，无法直接定位到以特定字符结尾的条目。例如： - 模式`'%A'`：要求以"A"结尾，但索引是按开头字符排序的，因此数据库必须扫描所有索引（或全表）来检查每个字符串的结尾字符。 - 模式`'%A%'`：要求包含"A"，可能出现在字符串的任何位置，同样无法通过前缀索引快速定位。 4. **优化器的选择**： 即使在某些特殊情况下（如覆盖索引或小表），优化器可能选择使用索引扫描（Index Scan）而非全表扫描，但效率通常低于索引范围扫描（Index Range Scan）。因为索引扫描需要遍历整个索引树，而全表扫描可能更直接（尤其是当索引列不是主键时，需要回表）。 ### 解决方案（根据引用材料）： 1. **避免前导通配符**： 尽可能将通配符放在模式末尾（如`'A%'`）以利用索引。 2. **使用函数索引（MySQL 8.0+）**： 对于后缀匹配（如`LIKE '%A'`），可创建反向索引（Reverse Key Index）： ```sql ALTER TABLE users ADD COLUMN name_reverse VARCHAR(255) AS (REVERSE(name)) STORED; CREATE INDEX idx_name_reverse ON users(name_reverse); -- 查询时使用反向匹配 SELECT * FROM users WHERE REVERSE(name) LIKE REVERSE('%A'); -- 实际模式变为'A%'的反转 ``` 注意：存储列和索引会占用额外空间。 3. **使用全文索引（Full-Text Index）**： 对于包含任意位置子串的搜索（如`LIKE '%A%'`），可考虑使用MySQL的全文索引（FULLTEXT index），但注意全文索引适用于单词匹配，不适用于任意子串。 4. **使用覆盖索引优化**： 如果查询只需要索引列，即使使用`LIKE '%A%'`，也可能通过索引扫描（Index Scan）避免回表，但效率仍低于范围扫描。 ### 示例验证（引用[3]）： ```sql -- 创建表和索引 CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(255)); CREATE INDEX idx_name ON users(name); -- 有效使用索引（前缀匹配） EXPLAIN SELECT * FROM users WHERE name LIKE 'A%'; -- 输出：可能使用`range`扫描（使用索引） -- 索引失效（后缀匹配） EXPLAIN SELECT * FROM users WHERE name LIKE '%A'; -- 输出：`type=ALL`（全表扫描） ``` ### 总结： > 索引失效的根本原因是**B-tree索引的排序机制基于字符串前缀**，因此只有前缀匹配（即通配符在末尾）才能高效利用索引。当模式以通配符开头时，数据库必须检查所有可能的字符串组合，导致索引失效[^1][^2][^3]。 --- ### 相关问题： 1. 如何通过`EXPLAIN`命令分析MySQL中LIKE查询的索引使用情况？ 2. 在MySQL 8.0中，函数索引如何优化后缀匹配查询？ 3. 全文索引（FULLTEXT）和LIKE通配符查询在性能上有何差异？ 4. 除了索引优化，还有哪些方法可以加速模糊查询？ 5. MySQL中的覆盖索引（Covering Index）如何影响LIKE查询的性能？</think>### MySQL中使用百分号通配符导致索引失效的原因分析 #### 1. **索引的底层结构限制** MySQL的B-tree索引（包括InnoDB的B+树索引）是**基于值的前缀排序**的： - 索引存储结构：`A` -> `Apple` -> `Banana` -> `Cherry`（按首字母顺序存储） - 当使用`LIKE 'A%'`时，索引可以快速定位到以"A"开头的区域（范围扫描） - 当使用`LIKE '%A'`时： - 索引无法反向查找（B-tree索引是单向结构） - 必须扫描**所有索引条目**检查结尾字符 - 相当于全索引扫描，效率甚至低于全表扫描[^1][^2] #### 2. **优化器无法使用索引统计信息** - B-tree索引只记录**起始字符**的分布统计 - 对于`LIKE '%keyword%'`： - 优化器无法预估匹配的行数 - 无法选择最优执行计划 - 默认退化为全表扫描（type=ALL in EXPLAIN）[^3] #### 3. **通配符位置的关键影响 | 查询模式 | 索引使用情况 | 扫描方式 | |------------------|-------------------|-------------------| | `LIKE 'A%'` | ✅ 有效 | 索引范围扫描 | | `LIKE '%A'` | ❌ 失效 | 全表扫描 | | `LIKE '%A%'` | ❌ 失效 | 全表扫描 | | `LIKE 'A%B'` | ✅ 部分有效 | 索引扫描+过滤 | #### 4. **特殊场景分析 ```sql -- 看似使用索引但实际低效的案例 SELECT * FROM users WHERE name COLLATE utf8mb4_unicode_ci LIKE '%A%'; ``` - `COLLATE`子句使优化器误判可能使用索引 - 实际执行仍是全表扫描（可通过`EXPLAIN`验证）[^3] #### 5. **解决方案与替代方案 1. **前缀索引优化**（针对后缀搜索）： ```sql -- 创建反向列+索引 ALTER TABLE users ADD COLUMN name_reverse VARCHAR(255); UPDATE users SET name_reverse = REVERSE(name); CREATE INDEX idx_name_reverse ON users(name_reverse); -- 查询时反转条件 SELECT * FROM users WHERE name_reverse LIKE REVERSE('%A') + '%'; ``` 2. **全文索引替代方案**： ```sql -- 创建全文索引 ALTER TABLE products ADD FULLTEXT(name); -- 使用自然语言搜索 SELECT * FROM products WHERE MATCH(name) AGAINST('+keyword' IN BOOLEAN MODE); ``` 3. **覆盖索引优化**： ```sql -- 创建覆盖索引 CREATE INDEX idx_covering ON users(name, id); -- 查询仅需索引数据 SELECT name FROM users WHERE name LIKE '%A%'; ``` > **根本原因总结**：B-tree索引的**单向有序结构**决定了它只能高效支持前缀匹配。当通配符出现在模式开头时，索引的排序优势完全丧失，导致优化器选择全表扫描[^1][^2][^3]。 --- ### 相关问题 1. MySQL中B-tree索引和哈希索引对LIKE查询的支持有何不同？ 2. 如何通过EXPLAIN命令准确判断LIKE查询是否使用了索引？ 3. 在MySQL 8.0中，函数索引如何优化通配符查询？ 4. 全文索引(Full-Text)和LIKE模糊查询的性能差异有多大？ 5. 除了索引优化，还有哪些方法可以加速模糊查询？
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值