在学习索引时,有一个问题通常很绕,那就是like
。它有时可以使用到索引,而有时又不可以使用到索引。因此,今天按照我的个人理解对like进行一个总结。希望可以帮助到初学者,也欢迎大家一起讨论。
1.以通配符 % 开头
- 例子:
select * from table where a like ‘%kk’
- 当
%
处于最左侧时,将无法使用索引帮助我们查询数据。- 如果此时是覆盖索引,那么此时可以直接遍历索引,不需要遍历数据表,也可以一定程度上提升我们的查询速度。
- 如果没有覆盖索引,那么就需要对数据表进行遍历,是最坏情况。.
2.以通配符%结尾
- 例子:
select * from table where a like ‘kk%’
- 当
%
处于最右侧时,查询数据仍然可以使用到索引找到以kk开头的数据,此时相当于范围查询,也就是range
3. 通配符%在中间
- 例子:
select * from table where a like ‘kk%kk’
- 当%处于中间时,与第二种情况相同
4. 以通配符 % 开头的like后跟随查询条件
- 例子:
select * from table where a like a = 1 and b like ‘%kk’ and b = 3
- 可以看到在例子中,在以通配符
%
开头的like
的语句后还有一个查询条件。如果此时有一个符合索引包含了列a,b,c
,那么索引的a
与b
列可以使用,而c
无法使用。
5. 以通配符 % 结尾的like后跟随查询条件
- 例子:
select * from table where a like a = 1 and b like ‘kk%’ and b = 3
- 可以看到在例子中,在以通配符
%
结尾的like
的语句后还有一个查询条件。如果此时有一个符合索引包含了列a
,b
,c
,那么索引的a、b、c
列都可以使用。
注意
此时可能又有一个疑惑了,前面在第二个例子中,不是说以通配符结尾的like相当于是利用索引进行范围查询吗,那为什么范围查询之后的列c
还可以使用索引?我们都知道select * from table where a like a = 1 and b > 2 and b = 3
只能使用索引的a、b列。那为什么第五个例子中还可以使用索引的c
列?
虽然LIKE
和 >
确实都可以被认为是范围查询,但 MySQL 在处理这两种操作符时的索引使用规则是不同的。
- 对于前缀匹配,MySQL 可以利用
B-tree
索引,因为索引按顺序存储,而且所有以特定前缀开头的值都是连续存储的,这使得查找起来非常高效。因此,对于LIKE 'prefix%'
这样的查询,MySQL 通常可以有效地使用索引。 - 当使用
>
时,MySQL 需要查找大于给定值的所有值。这种操作需要在索引中进行范围扫描,而不是简单的精确匹配或前缀匹配。