MySQL中like查询使用索引
explain查看执行计划
首先介绍一下mysql explain的各项内容:
序号 | 名称 | 含义 | 备注 |
---|---|---|---|
1 | id | 查询的ID | ID相同从上往下执行,ID不同,越大越先执行 |
2 | select_type | 查询类型 | SIMPLE 简单查询 PRIMARY 最外层的查询 Union SUBQUERY 子查询。 还有很多类型可以看官方文档 https://dev.mysql.com/doc/refman/5.6/en/explain-output.html |
3 | table | 表 | |
4 | partitions | 匹配分区 | |
5 | type | 连接类型 | system:表为空或只有一行 const:查询结果就只有一行,这样msql优化器就能把这一行当成常量处理,一般只有用主键/唯一索引查的时候出现 eq_ref:在当前表中只能找到一行与前面的查询结果一一匹配,一般是用主键或惟一索引进行连接,用=符号 ref:在当前表中可以找到多行与前面的查询结果一一匹配,一般是用主键的最左前缀或非唯一索引,用=, > , < 符号 full_text:用fulltext索引时使用 ref_null:和 ref很像,就是多搜索一个null条件 index_merge: 用到了不止一个索引,mysql做了优化 range:用于范围查询,包括like。Index:索引全表扫描;1.覆盖索引,只需要扫描全部索引,无需回表; ALL:全表扫描 |
6 | possible_keys | 可能选择的索引 | |
7 | key | 实际选择的索引 | 实际选择的索引可能并不在possible_keys中,(这种情况是mysql发现你查的是个覆盖索引,扫描索引比扫描全表要好) |
8 | key_len | 选择的索引的长度 | 主要是联合索引看看用到了多少 |
9 | ref | 与索引相比较的列 | const:用的常数 func:用的函数 |
10 | rows | 预估扫描行数 | |
11 | filtered | 查询条件过滤后占的百分比 | 只对index和all类型的查询生效,其他类型都为100%,可以看下这个文章http://blog.chinaunix.net/uid-20726500-id-5573764.html |
12 | Extra | 额外信息 | 介绍 |
实验过程
- 先建立一个表,如下图所示
- 在loacation_path添加索引后进行单条件,全部数据的查询
- like xxx%查询
explain select * from location where loaction_path like '中国%’
查询结果
可以看出ID为1 没什么好说的,然后是simple的查询,用到的表为location,分区为null,连接的类型是range(因为是用like查询的),possible_keys为可以使用的location_path列上的索引,最终使用的也是这个key,key_length = 1020,这个是怎么算的呢?
先看下表的列的类型和字符集:
Location_path这一列是char(255),uft8mb4_0900_ai_ci正好对于每个字符用4个字节存储:4*255 = 1020; 如果loation_path 允许为null,则还需要多一个字节存储null,key_length会为1021,使用了索引进行查询,扫描行数为6行,使用了索引。
- like %xxx查询
explain select * from location where loaction_path like '%/浦东新区’
这个则没有使用索引
- like %xxx%查询
explain select * from location where loaction_path like '%/上海市/%’
这个也没有使用索引查询
- 分析
如果前缀为%,索引不知道应该如何匹配,所以不会用到
- 进行单条件,查询单个属性的查询
explain select loaction_path from location where loaction_path like '%上海市%’
发现使用了索引,但是过滤的行数还是7行,possible_keys为空,也就是估计没用索引,但是实际查询时候发现是覆盖索引的查询,所以用上了。
Like 不走索引的优化
- 使用LOCATE
SELECT * FROM location WHERE LOCATE('上海市', location_path);
benchmark对比如下:看起来查不了多少,基本上一样(也可能和数据量有关系,毕竟表里面就几条数据)