前言

我们经常会用到数据库，并可能写一些查询语句，大多数情况下，可能都是一些比较简单的增删改查语句。但某些时候，也可能遇到一些较复杂的语句。

在系统数据量不太大的情况下，可能我们SQL语句的好坏对系统性能并不会造成太大影响，但是随着系统数据量增大，数据库压力增大，除一些其它策略（使用缓存数据库、分库分表等等），良好的SQL语句也是提高数据库性能的一个重要指标。

SQL语句我们基本是按照，能精确查询就精确查询，能简化就简化，能使用索引就使用索引（必要条件下）的原则去编写。

我们今天通过Explain这个关键字来分析及优化我们的SQL语句。

正文

那就先说下Explain吧。

打开数据库，随便找一张数据表。如下：

可以看到，当我们在我们写的SQL语句前加上Explain后，会得到如下的结果表。

PS：如果SQL语句关联其它表，会有多行结果，后续。

我们先来简单说下结果各个字段的含义。

id
暂且叫主键或者顺序吧，如果不止一次查询搜索（比如关联其它表），会按照顺序列出来。序号越大越先被执行。

select_type

查询类型，有下面几种：

 SIMPLE：简单SELECT(不使用UNION或子查询等)
 PRIMARY：最外面的SELECT
 UNION：UNION中的第二个或后面的SELECT语句
 DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
 UNION RESULT：UNION的结果。
 SUBQUERY：子查询中的第一个SELECT
 DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
 DERIVED：导出表的SELECT(FROM子句的子查询)

table
这行数据和哪张表有关联。
partitions
匹配的哪个分区。
type
这列最重要，显示了连接使用了哪种类别，有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。
结果值从好到坏依次是：
```
 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >     index_subquery > range > index > ALL
```
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
possible_keys
MySQL可能使用哪个索引在该表中找到行。
key
MySQL实际决定使用的键（索引）。如果没有选择索引，值是NULL。
key_len
MySQL决定使用的索引长度。如果key是NULL，则key_len为NULL。在不损失精确性的情况下，长度越短越好。
ref
使用哪个列或常数与key一起从表中选择行。
rows
MySQL认为它执行查询时必须检查的行数。正常情况下这个值越小越好。

Extra

MySQL解决查询的详细信息，也是关键参考项之一。

Distinct：一旦MYSQL找到了与行相联合匹配的行，就不再搜索了。
Not exists：MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
Range checked for each Record：没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。
Using filesort：MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。出现这种情况应当优化SQL语句。
Using index：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
Using temporary：说明MySQL需要新建一个临时表来存储结果，当生成的临时表较大时，应当优化SQL语句。
Using where：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，就会出现这种情况。
Using union：使用了合并，有可能合并索引、表等。一般使用or连接条件时可能会出现。

我们创建一张学生表。

CREATE TABLE `student` (
  `id` int(32) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `stu_no` varchar(20) DEFAULT NULL COMMENT '学生编号',
  `stu_name` varchar(50) DEFAULT NULL COMMENT '学生姓名',
  `stu_sex` char(2) DEFAULT NULL,
  `stu_birthday` date DEFAULT NULL COMMENT '学生生日',
  `stu_class` char(2) DEFAULT NULL COMMENT '学生所在班级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50077 DEFAULT CHARSET=utf8;

向表中加入100w数据。（过程略）

关于索引

我们统计5班学生的学生数量。

可以看到耗时2s之久。

使用Explain分析可以看到mysql需要检查994511行才能得到结果，已经相当于全表扫描了。Type是ALL，为最差的一种。

我们在stu_class班级这一列上加上索引。

继续查询及分析，可以看到耗时0.07s。

Explain分析可知type为ref满足我们要求，mysql执行这个查询认为只要扫描191502条数据即可，使用了index_1索引。

是不是感觉到索引很厉害？

我们再来看一下。

这回我们查询5年级所有学生的学生名。如下：

先不使用索引，可以看到，耗时2s左右。

我们给年级加上索引，继续测试。如下：

可以看到耗时竟达到5s左右。

为什么会这样呢？

一般我们认为，这种属于很特殊的情况，一般在硬盘上，数据是连续存储的，但是引入索引后，数据连续性变差，随机波动性要大，在数据量大的情况下更是。

这种情况下，我们读取大量数据（99497条已接近1/10数据总量），由于随机性变大，故读取时间会变大。因此索引在查询一些小数据量数据的情况下效果明显，或者索引带来的优化已经完全超过了数据读取时间。

关于上面，我们可以看下有索引和无索引SQL执行情况。

有索引情况下:

无索引情况下:

可以看到它们明显的差距就在返回数据这儿了，有索引情况下读取数据浪费了太多时间。

关于索引带来的优化已经完全超过了数据读取时间这个，我们可以看下这个例子。

我们在添加一条姓名索引。

我们查询5班所有姓张的童鞋。

有索引情况下:

无索引情况下:

明显的有索引的完爆无索引查询时间啦，这里就不在具体分析了。

我们可以看下这儿的like，可以直接看下面的图。

明显得到的结论就是当有like查询时，%或者_在前面的是无法使用当前字段索引的（如果有），会进行全表扫描，这一点需要注意。

SQL优化

我们接下来分析一些SQL的优化。

1.我想查询生日在1998年至2010年之间的所有学生数量。（生日已建立索引index_3）

可以这样，如下图。

Explain分析仅为index级别，虽然用到了索引index_3。

如何优化下呢？

我们可以这样处理：

通过Explain分析type也达到了range级别。

PS：通过上面例子，我们知道我们应该尽量减少对字段的计算操作，以便使其可以正常利用索引，也就是尽量使条件数据与字段数据去匹配，而不是对字段数据进行操作后去匹配条件数据。

2.我想查询生日在98年后且是5班的且姓张的童鞋。

有生日索引index_3，姓名索引index_2，班级索引index_1。

运行上述SQL后我们看到type级别为range，还可以，但是却只用到了index_2索引，时间还可以0.006s。

还有好一点的方法吗？

我们试着删除上面三个索引，创建一个联合索引。如下：

进行测试。

可以看到时间有提高（反复测试是比单索引要好的），type级别还是range。

PS：联合索引只有在一些特定的情况下能发挥很好的作用，一般单索引可以满足大多需求，所以联合索引的创建是需要慎重的。

联合索引有一些特点需要注意，以免被坑。
对于上面的联合索引，index_1(stu_name, stu_birthday, stu_class)，遵循最左原则。

最左原则可以看下面7个例子。

例子1:

1	EXPLAIN select * from student s where s.stu_birthday >= '1998-01-01';

Explain分析结果如下：

例子2:

1	EXPLAIN select * from student s where s.stu_name like '张%';

Explain分析结果如下：

例子3:

1	EXPLAIN select * from student s where s.stu_class = '5';

Explain分析结果如下：

例子4:

1	EXPLAIN select * from student s where s.stu_class = '5' and s.stu_birthday >= '1998-01-01';

Explain分析结果如下：

例子5:

1	EXPLAIN select * from student s where s.stu_class = '5' and s.stu_name like '张%';

Explain分析结果如下：

例子6:

1	EXPLAIN select * from student s where s.stu_birthday >= '1998-01-01' and s.stu_name like '张%';

Explain分析结果如下：

例子7:

1	EXPLAIN select * from student s where s.stu_birthday >= '1998-01-01' and s.stu_name like '张%' and s.stu_class = '5';

Explain分析结果如下：

可以看到，可以使用索引的查询有（stu_name）（stu_name, stu_birthday）（stu_name, stu_birthday, stu_class）（stu_name, stu_class），也就是必须有stu_name这个条件。这就是联合索引的最左原则。

3.我们统计姓名相同的学生的学生名及数量。

1	select s.stu_name,count(1) from student s GROUP BY s.stu_name HAVING count(1)>1 ORDER BY s.stu_name;

这儿就不在贴图了，当stu_name有索引时，查询效率会有明显提升。

在有索引Explain分析后，我们可以看到，Mysql认为这个查询需要检索全部数据（rows 99万）。

这是Mysql认为要检索的行数，实际由于stu_name有索引，是没有检索这么多的。

因为查询语句中出现了group by，对于某张表，正常情况的的认知肯定是检索完全表后才能进行分组归类。所以，在一些经常被group by 或者 order by 的字段上，数据量较大的情况下，且该字段比较多样性（比如性别这种字段顶多有男、女、未知三种，加不加索引都可以），向表中常用的创建时间、修改时间等字段，还是有必要添加索引的。

4.查询所有学生生日，性别，班级相同的学生数量。

我们假设建立了index_1(stu_birthday)索引，index_2(stu_sex)索引，index_3(stu_class)索引。

你可能会想到如下SQL。

1	select count(1) from student a,(select stu_birthday,stu_class,stu_sex from student) b where a.stu_birthday = b.stu_birthday and a.stu_class=b.stu_class and a.stu_sex=b.stu_sex;

嗯，这个SQL是不正确的，而且耗时无法忍受。

其实不用Explain，单独看它就可以知道它会扫描全表，无论有无索引。而且我们统计学生数量，如果有2个以上情况相同呢？显然上面SQL的3个等于条件会出现问题。

其实我们可以根据分组数量来处理这个问题，如下：

select count(1) from student s ,(select a.stu_birthday,a.stu_sex,a.stu_class from student a GROUP BY a.stu_birthday,a.stu_sex,a.stu_class HAVING count(1)>1) b where s.stu_birthday=b.stu_birthday and s.stu_sex=b.stu_sex and s.stu_class=b.stu_class;

通过学生生日，性别，班级相同，他们的数量要大于1，从而进行数量统计。

耗时在8s左右。我们Explain分析下：

先从id为2的看起，生成了一个临时表a，但一个索引也没用到，向上看它用了学生生日，性别，班级这三个临时表的值做了auto_key（可以理解为临时索引），然后对s表全表扫描得到结果。

那我们的三个索引岂不是创建的是毫无意义的……

我们可以将它的auto_key变成一个联合索引，index_4(stu_birthday, stu_sex, stu_class)。

这时候查询时间减少到5s左右。

可以看到使用到了index_4，且Extra字段原来使用Using temporary现在变为了Using index。

总结

好了，基本上就这些内容，其实也有想写一个多表联查复杂的SQL语句的优化，但是无奈例子比较难找（自己不想再建新表，再插入数据，再想例子），在这儿就不在举例了。

其实实际中SQL语句的编写，复杂的还是比较少的（毕竟有程序结合，一个不行可以拆开嘛）。

这一节主要的内容其实是讲Explain这个关键字的，实际中写完复杂SQL语句可以用它分析下SQL执行情况。

上面也看到了有些索引不是建了就会减少查询时间的，索引的选择还是要认真对待，处理好能提升不少查询效率，另外索引创建也应找到问题的关键点。比如该字段经常被group by，order by ，该字段业务中经常使用等等。

使用Explain可以帮我们分析到索引的利用情况。

另外，Mysql除BTree索引外，还有一种Hash索引。

BTree索引具有范围查找和前缀查找的能力，一个N节点的BTree，检索一条记录时间复杂度为O(LogN)，相当于二分查找。

Hash索引只能做等值查找，但查找时间复杂度为O(1)。

如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更好的选择。
如果值的差异性相对较差，并且以范围查找为主，BTree是更好的选择。

一般使用BTree索引的场合较多。

结语

结束之前，我们可以看看索引的一些失效场景。

索引列查询出现 is not null 情况，会进行全表扫描不走索引。
列类型是字符串，数据需要引号引起来，否则会进行全表扫描不走索引。
模糊查询like的各种情况，不再详述。
联合索引的各种情况，不再详述。
or的情况，当用or连接条件时，应保证条件都有索引才能使用索引，否则索引无效。
可以看到由于stu_name没有索引，故进行了全表扫描。
stu_sex和stu_birthday均有索引，使用了索引。
如果MySQL觉得全表扫描要比使用索引要快，它也不会使用索引。
如下：由于stu_sex=0 或者 stu_class =1 数据很多，且都要返回，故没有使用它们的索引。

SakuraTears的博客

MySql 使用Explain分析SQL语句及索引

前言

正文

关于索引

SQL优化

总结

结语