mysql like为什么不走索引，如何改写

本文旨在用最通俗的语言讲述最枯燥的基本知识：

这个话题比较有意思。
昨天中午吃完饭间突然有个同事蹦出了一句：“like有索引吗？”，我顺口就说没有，另一个同事反驳说有啊，还有些同事说看情况的有，这下有点懵逼了，都不知道那种说法是正确的，于是决定花了个半小时来研究验证这个问题，终于得到答案。

怎么验证的呢？

坊间有传言：MySQL性能优化有个神器，叫做explain，它可以对select语句进行分析并且输出详细的select执行过程的详细信息，让开发者从这些信息中获得优化的思路。

下面来讲讲这个MySQL提供的explain命令：

语法：explain SQL语句
例如：

1explain select * from user where id=1

 执行完毕之后，它的输出有以下字段：

• id
• select_type
• table
• partitions
• type
• possible_keys
• key
• key_len
• ref
• rows
• Extra

要想知道explain命名怎么使用，就必须把这些字段搞清楚

1. id

SELECT查询的标识符， 每个SELECT语句都会自动分配一个唯一的标识符

2. select_type

每个select查询字句的类型，具体类型以及对应作用如下表：

类型名	解释
SIMPLE	简单SELECT,不使用UNION或子查询等
PRIMARY	查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
UNION	UNION中的第二个或后面的SELECT语句
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT，取决于外面的查询
DERIVED	派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外链接的第一行

3. table

显示这一行的数据是查哪张表的，不过有时短路显示的不是真实的表名。

4. partitions

匹配的分区(这个目前用处不大)

5. type

访问类型，表示MySQL在表中找到所需行的方式，对应的值和解释如下：

类型名	优级别	解释
system	1	表仅有一行
const	2	表最多有一个匹配行,在查询开始时即被读取
eq_ref	3	使用primary key或者unique key作为多表连接的条件,仅从该表中读取一行
ref	4	作为查询条件的索引在每个表匹配索引值的行从表中读取出来
fulltext	5	全文索引检索
ref_or_null	6	和ref一致，但增加了NULL值查询支持
index_merge	7	表示使用了索引合并优化方法
unique_subquery	8	使用了替换了in子查询
index_subquery	9	使用了替换了in子查询，但只适用于子查询中的非唯一索引
range	10	只检索给定范围的行,使用一个索引来选择行
index	11	全表扫描，但扫描表的方式是按索引的次序进行
ALL	12	全表扫描的方式找到匹配的行

type作为访问类型，其值代表着当前查询所用的类型，是体现性能的一个重要指标，从表中可以看到，从上到下，扫描表的方式越来越宽，性能也就越来越差，因此，对于一个查询，最好能保持在range级别以上。

6. possible_keys

主动指出查询能用哪个索引在表中找到记录
也就是会列出在查询中的字段中有索引的字段，但不一定被查询所用。

7. key

显示再查询中实际使用的索引/键，如果没有索引，则显示NULL。
但如果想强制查询中使用或忽视possible_keys列中的索引，则可以在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

8. key_len

表示索引中使用的字节数。

9. ref

表示哪些列或常量被用于查找索引列上的值。

10. rows

显示当前查询估算到的查找到匹配记录所需的记录行数。

11. Extra

显示当前查询所用的解决方式，它有以下几种情况：

类型名	解释
Using where	列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，
Using temporary	表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort	MySQL中无法利用索引完成的排序操作称为“文件排序”
Using join buffer	改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where	这个值强调了where语句会导致没有符合条件的行。
Select tables optimized away	这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

---

讲完了语法，我们来实际操作一波，首先创建个表：

1-- 创建表
2CREATE TABLE test(
3id INT(11) NOT NULL AUTO_INCREMENT,
4uname VARCHAR(255),
5PRIMARY KEY(id) 
6);
复制代码

然后给uname字段加上索引：

1-- 添加索引
2ALTER TABLE test ADD INDEX uname_index (uname);
复制代码

查看一下索引是否添加成功：

1-- 查看是否有索引
2SHOW INDEX FROM test;
复制代码

输出结果为：

 

可以看出索引已经创建成功，接下来添加一些数据：

1-- 添加一些数据
2INSERT INTO test VALUES(1,'jay');
3INSERT INTO test VALUES(2,'ja');
4INSERT INTO test VALUES(3,'bril');
5INSERT INTO test VALUES(4,'aybar');
复制代码

一切准备就绪，下面用explain这个命令来探究一些like语句是否有索引,
like有四种情况，分别为没有%、 %% 、左%、右%、

1. like 字段名

1EXPLAIN SELECT * FROM test WHERE uname LIKE 'j'; 
复制代码

输出为：

 

可以看出：
type的值为：range，key的值为uname_index，也就是说这种情况下，使用了索引。

2. like %字段名%

1EXPLAIN SELECT * FROM test WHERE uname LIKE '%j%'; 
复制代码

输出为：

 

可以看出：
type的值为ALL也就是全表扫描，而且key的值为NULL，也就是说没用到任何索引。

3. like %字段名

1EXPLAIN SELECT * FROM test WHERE uname LIKE '%j'; 
复制代码

输出为：

 

可以看出：
type的值为ALL，key的值为NULL，同样没用到索引。

4. like 字段名%

1EXPLAIN SELECT * FROM test WHERE uname LIKE 'j%'; 
复制代码

输出为：

 

可以看出：
type的值为：range，key的值为uname_index，也就是说这种情况下，使用了索引。

5. 用其他方法实现：LOCATE（'substr',str,pos）方法

SELECT LOCATE('xbar',`foobar`); 
###返回0 

SELECT LOCATE('bar',`foobarbar`); 
###返回4

SELECT LOCATE('bar',`foobarbar`,5);
###返回7

备注：返回 substr 在 str 中第一次出现的位置，如果 substr 在 str 中不存在，返回值为 0 。如果pos存在，返回 substr 在 str 第pos个位置后第一次出现的位置，如果 substr 在 str 中不存在，返回值为0。

SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0

备注：keyword是要搜索的内容，field为被匹配的字段，查询出所有存在keyword的数据

5.2 FIND_IN_SET(str1,str2)方法

返回str2中str1所在的位置索引，其中str2必须以","分割开。

SELECT * FROM `person` WHERE FIND_IN_SET('apply',`name`);

总结

由上面的试验可以总结出like是否使用索引的规律：
like语句要使索引生效，like后不能以%开始，也就是说 （like %字段名%） 、（like %字段名）这类语句会使索引失效，而（like 字段名）、（like 字段名%）这类语句索引是可以正常使用，也可以换LOCATE的写法、FIND_IN_SET

其它

为了查证like索引的问题，研究了MySQL神奇explain，但explain不仅仅只能检查索引使用情况，还可以提供很多其它的性能优化方面的帮助，至于具体的使用，其实跟上面讲的一样，把explain结果列出来，然后顺藤摸瓜查阅相关的字段就可以得到相应的内容。