MySQL优化神器：explain命令及like有无索引问题

坊间有传言：MySQL性能优化有个神器，叫做explain，它可以对select语句进行分析并且输出详细的select执行过程的详细信息，让开发者从这些信息中获得优化的思路。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

下面来讲讲这个MySQL提供的explain命令：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

语法：explain SQL语句
例如：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

1explain select * from user where id=1
复制代码

执行完毕之后，它的输出有以下字段：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

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

要想知道explain命名怎么使用，就必须把这些字段搞清楚文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

1. id

SELECT查询的标识符， 每个SELECT语句都会自动分配一个唯一的标识符文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

2. select_type

每个select查询字句的类型，具体类型以及对应作用如下表：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

3. table

显示这一行的数据是查哪张表的，不过有时短路显示的不是真实的表名。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

4. partitions

匹配的分区(这个目前用处不大)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

5. type

访问类型，表示MySQL在表中找到所需行的方式，对应的值和解释如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

type作为访问类型，其值代表着当前查询所用的类型，是体现性能的一个重要指标，从表中可以看到，从上到下，扫描表的方式越来越宽，性能也就越来越差，因此，对于一个查询，最好能保持在range级别以上。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

6. possible_keys

主动指出查询能用哪个索引在表中找到记录
也就是会列出在查询中的字段中有索引的字段，但不一定被查询所用。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

7. key

显示再查询中实际使用的索引/键，如果没有索引，则显示NULL。
但如果想强制查询中使用或忽视possible_keys列中的索引，则可以在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

8. key_len

表示索引中使用的字节数。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

9. ref

表示哪些列或常量被用于查找索引列上的值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

10. rows

显示当前查询估算到的查找到匹配记录所需的记录行数。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

11. Extra

显示当前查询所用的解决方式，它有以下几种情况：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

讲完了语法，我们来实际操作一波，首先创建个表：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

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

然后给uname字段加上索引：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

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

查看一下索引是否添加成功：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

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

输出结果为：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

可以看出索引已经创建成功，接下来添加一些数据：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

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有四种情况，分别为没有%、 %% 、左%、右%、文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

1. like 字段名

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

输出为：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

可以看出：
type的值为：range，key的值为uname_index，也就是说这种情况下，使用了索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

2. like %字段名%

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

输出为：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

可以看出：
type的值为ALL也就是全表扫描，而且key的值为NULL，也就是说没用到任何索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

3. like %字段名

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

输出为：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

可以看出：
type的值为ALL，key的值为NULL，同样没用到索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

4. like 字段名%

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

输出为：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

可以看出：
type的值为：range，key的值为uname_index，也就是说这种情况下，使用了索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

总结

由上面的试验可以总结出like是否使用索引的规律：
like语句要使索引生效，like后不能以%开始，也就是说 （like %字段名%） 、（like %字段名）这类语句会使索引失效，而（like 字段名）、（like 字段名%）这类语句索引是可以正常使用。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/6539.html

其它

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