MySQL中的LIKE查询能否用的到索引
目录前言索引单值索引前言常常听说,mysql 中的 like 要慎用,因为会全表扫描,对于这句话不是很理解,只能强加记忆。这篇文章我们来一探究竟,了解 like 为什么要慎用,以及为什么会全表扫描索引我们从单值索引和联合索引两个方面来讨论单值索引数据库 SQL 脚本如下CREATE TABLE `user` (`id` int(11) unsigned NOT NULL AUTO_INCREMEN
目录
1. 前言
常常听说,MySQL
中的 like
要慎用,因为会全表扫描,对于这句话不是很理解,只能强加记忆。这篇文章我们从单值索引和联合索引两个方面来一探究竟,了解 like
为什么要慎用,以及为什么会全表扫描。文章如有误,欢迎指正
2. 单值索引
数据库 SQL
脚本如下
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`birthday` date DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_username` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
username
字段是一个单值索引
数据库数据如下
2.1. 使用 like x%
- 测试
SQL
及结果
- 查看
EXPLAIN
执行计划
可以看到 EXPLAIN
执行计划中,type
为 range
表示这是一个索引的范围扫描,Extra
为 Using index condition
表示用到了索引,但是还需要进行过滤。这样也就证明了使用 like x%
查询是可以用得到索引的
2.2. 使用 like %x%
- 测试
SQL
及结果
- 查看
EXPLAIN
执行计划
可以看到 EXPLAIN
执行计划中,不用多说它使用了全表扫描,证明了使用 like %x%
查询是用不到索引的
2.3. 使用 like %x
- 测试
SQL
及结果
- 查看
EXPLAIN
执行计划
可以看到 EXPLAIN
执行计划中,不用多说它使用了全表扫描,证明了使用 like %x
查询是用不到索引的
2.4. 总结
- 使用
like x%
查询是可以用得到索引的,而使用like %x%
和like %x
查询是用不到索引的 - 那么使用
like %x%
和like %x
查询为什么用不到索引?因为索引是一种有序的B+ Tree
数据结构,叶子节点都是按照顺序从左向右排的,如果使用like %x%
和like %x
查询的话,不知道开头是哪个,就会去进行全表扫描,这样就很好理解了
3. 联合索引
测试数据我们依然使用上面的数据,将单值索引改为联合索引,将字段 username
和 age
两个字段作为联合索引,命名为 index_username_age
3.1. 使用 like x%
- 测试
SQL
及结果
- 查看
EXPLAIN
执行计划
从 EXPLAIN
执行计划中,可以看到使用 like x%
查询用到了 index_username_age
这个联合索引,这个 type
为 range
表示这是一个索引的范围扫描(因为是模糊匹配,而模糊匹配可以形成扫描区间),Extra
为 Using where;Using index
,表示用到了索引,但是还需要进行过滤
为什么说模糊匹配就能形成扫描区间呢?因为我们是按照 username
和 age
建立的联合索引,username
在前 age
在后,具体存的时候,是按照 username
排序存储,如果 username
相同,则按照 age
再排序存储。可以参考联合索引的数据存储结构
3.2. 使用 like %x%
- 查看
EXPLAIN
执行计划
3.3. 使用 like %x
- 查看
EXPLAIN
执行计划
从使用 like %x%
和 like %x
的执行计划来看,它们的 type
均为 index
,说明它遍历了索引 B+ Tee
,Extra
为 Using where;Using index
,表示用到了索引,但是还需要进行过滤
那么使用 like %x%
和 like %x
查询时,为什么它需要遍历整颗索引 B+ Tee
?因为对于联合索引 index_username_age
来说,它是以 username
字段来创建维护的索引 B+ Tee
,自然而然在叶子节点中 username
字段是有序排列的,而 age
字段是无序的,只有在 username
字段相同的情况下,age
字段才是有序的;如此如果使用 like %x%
和 like %x
查询的话,不知道开头是哪个,就会去进行全表扫描。可以参考 mysql 的联合索引
3.4. 总结
3.4.1. 使用 like x%
在联合索引中,使用 like x%
查询是可以用到索引的,无论是使用覆盖索引还是查询所有的字段,它都是可以使用到索引的,提高查询效率的
3.4.2. 使用 like %x%
和 like %x
- 使用覆盖索引:虽然用到了索引,但它是需要遍历整颗索引
B+ Tee
的,然后过滤出满足条件的数据 - 查询所有字段:此时它需要读取遍历联合索引
index_username_age
整颗索引B+ Tee
,然后过滤出满足条件的数据,由于联合索引中没有保存address
字段的值,所以还需要回表操作,再去主键索引中找到对应的记录
更多推荐
所有评论(0)