关于后端:你的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，也就是说这种状况下，应用了索引。
   总结
   由下面的试验能够总结出like是否应用索引的法则：like语句要使索引失效，like后不能以%开始，也就是说 （like %字段名%） 、（like %字段名）这类语句会使索引生效，而（like 字段名）、（like 字段名%）这类语句索引是能够失常应用。
   其它
   为了查证like索引的问题，钻研了MySQL神奇explain，但explain不仅仅只能查看索引应用状况，还能够提供很多其它的性能优化方面的帮忙，至于具体的应用，其实跟下面讲的一样，把explain后果列出来，而后顺藤摸瓜查阅相干的字段就能够失去相应的内容。