前言

上一篇介绍到了索引，那么索引失效怎么办，查询怎么合理的利用到索引，都是问题，因此本篇记录一些查询优化的学习心得。

一、查询优化篇

首先了解explain（执行计划）

1.explain相关参数

1.1 id（较重要）

用来表示执行顺序。id相同时是顺序执行。从上往下执行。
比如一个查询中包含子查询，则子查询的id字段比外层查询的要搞。id越大越先执行
如某个查询在explain中显示为[1,1,2] 那么先执行2，再执行第1个1，再执行第2个1.

造成id不同的原因是，有子查询时，肯定要先查子查询。当id相同时，说明都是并列的，如where a and b，那么a条件和b条件就是并列的。具体怎么执行a和b，得看MySQL优化器内部有没有执行某个优化，导致并没有严格的从左往右进行。

1.2 select_type

标记你查询的类型。

比如在select中包含子查询，会显示SUBQUERY。有子查询的最外层，会显示PRIMARY.最简单的查询就是SIMPLE。

派生表可以理解成嵌套子查询中的结果。

1.3 table

告诉你当前操作，用的是哪个表。

1.4 type（重要）

表示当前查询的效果怎么样.
只记录几个常用的级别。从好到坏依次为：

SYSTEM > CONST > EQ_REF > REF > RANGE > INDEX > ALL

如果是ALL，百万级别的数据就必然要优化。
一般达到range和ref级别就可以了。

ALL:全表扫描或者范围扫描：不使用索引，顺序扫描，直接读取表上的数据（访问数据文件）
INDEX:索引全表做扫描，是基于索引在索引的叶子节点上找满足条件的数据（不需要访问数据文件）
RANGE：范围扫描，基于索引做范围扫描，为诸如BETWEEN，IN，>=，LIKE类操作提供支持，是有可能导致索引失效。
REF:非唯一索引，某个索引对应多条数据，因此查出来可能有多个数据
EQ_REF：唯一索引,也就是某个索引对应一条数据。
CONST:通过索引，一次就找到了。这里是没有回表的。且数据只有一条。

1.5 possible_keys和key、key_len

possible_keys：是否可能用到某个索引。但实际查的时候不一定会用到。
key：实际上用到的索引
key_len：使用到的索引的字节数，越短越好。

1.6 rows和ref

rows:查出来这个结果要读多少行数据。
res:表和表之间的的关联信息？这里没理解透彻可能为 ：null（非 eq_ref\ref join type时）、const（常量）、关联的谓词列名。
为常量时，比较好。

1.7 extra（重要）

extra会告诉你一些额外的比较重要的信息。比如出现：
Using filesort：系统自己排序，而不是使用索引，可以理解成某些索引失效。
Using temporary:内部产生了一个临时表，会影响性能，原因是使用group by和order by。用了a索引，但是用b分组或者b排序，必然产生临时表。
Using index:使用了覆盖索引。挺好的。
Using where：使用到了索引，需要回表查询。性能还不错

2.索引失效（索引使用的注意事项）

注意：索引失效并不代表有优化空间，要功能实现，有些时候索引就是会失效

2.1 尽量全值匹配

第三句就是全值匹配的。

2.2 最佳左前缀法则要遵守

索引列有 a,b,c三列，如果索引顺序是(a,b,c)，如果最终语句只写了两个索引比如是(a,c)，则b索引用不上，且c索引也用不了。例如2.1中的name age job都建索引，如果你查name+job的组合，则只能用上name的索引，其他索引失效。而三个都用上也就是2.1提到的全值匹配了。
注：最佳左前缀法则也是和复合索引联系在一起的，如果是单值索引，则没有这个问题。复合索引相当于一个2级目录，先用前面的找，再结果集中再用第二个索引找。

2.3 避免在索引上操作

对索引进行计算，类型转换都会造成索引失效。

select * from emp where left(name,4) = 'July'

2.4 索引加入范围会导致失效

select * from emp where name= 'July' and age>25 and job = 'dev'

job这个索引失效，但是前两个索引有效。

2.5 尽量使用覆盖索引

这里有一个新的解释，那就是索引列和查询列一致时，就会是覆盖索引。
对于所有的解释，目前的理解是这样的，正常情况下，我们要查一个数据，如2.1中的例子，那么是一定要回表的，但是当我们指定了name age job这3个索引去查，如下列查询语句，那么我们最终得到的结果就是这个三列的一个结果集，不需要去查其他数据，因此不会去回表。也就是说，July在表中可能还有家庭地址，而你直选索引列去查，那么只会返回索引对应的数据，而你选择*去查，就会查到家庭住址，则此时就需要回表查这个住址

select name,age,job from emp where name= 'July' and age>25 and job = 'dev'

注：这里尽量使用是可以提高性能，但是不使用，也是可以的，并不会导致索引失效。

2.6 索引使用不等于、大于或者小于会导致索引失效（与2.4类似）

但是该写还是得写，毕竟数据还是要查出来的。

2.7 is null,is not null也会导致索引失效

2.8 like以通配符%开头，会导致索引失效（模糊查询）

但是like的字段后面加%没有影响。针对不同版本，可能出现如下结果：如果是(abc)中的b用了like，即使通配符在最后，b不会失效，但是c会失效，这个和2.4出现范围时一致。但有些版本实际上abc都不会失效。
解决这个问题，可以使用覆盖索引。解决查通配符开头的问题。这样可以避免全表扫描

2.9 字符串不加单引号导致索引失效

实际上就是底层有一个类型转换，导致索引失效。

2.10 or的使用也要注意

where 语句里面如果带有or条件, myisam表能用到索引， innodb不行。最终的优化也和引擎的选择不同。
听闻使用Union，分解成多个小查询的合并，从而使用到索引。但没亲手实现过。也听闻可以改成in，也能用到索引，但是有博客记录，in有时候也会失效。因此总体上来说，or的使用需要注意。

附加

上面说到了假如索引顺序是abc，那么如果你的语句写成acb，那么MySQL内部可能会有一个优化过程，将语句变成abc的顺序。因此如果你写的acb，然后a正常，c用范围，b正常，这样的顺序依旧是用到了三个索引。

3.调优步骤

1. 开启慢查询日志，抓取慢的SQL语句
2. explain+慢查询分析
3. show profile 查看SQL语句在MySQL服务器中的具体细节
4. 数据库服务器参数调优

4.一些调优细节

小表驱动大表：where 小表.a = 大表.b 外层是小表，内层大表。
小表驱动大表并不是嵌套子查询，因此这里也有个知识点，对于嵌套子查询，就是如果子查询的结果集比外面的小，in优于exists（外面的大）。其实应该从中文理解，一个是是否包含了某某结果集，因此外大内小。一个是是否被包含于（存在于）某某里面，所以exists适合大的是否包含某个小的，因此外面的小，里面的大。

5.SQL语句较慢的原因

主要有：性能低、执行时间长、等待时间长、索引失效、服务器参数设置不合理

6. order by的细节

• 尽量使用到覆盖索引，避免使用select *
• 索引失效，则出现using filesort，而 using filesort有两种算法。双路和单路排序。4.1版本后默认单路排序，用一次读取所有字段。然后再buffer缓存中进行排序。是有可能多次IO的。因此缓存中不可能一次读取所有的数据字段。那么就只能对单路排序中的参数进行调优，比如buffer的size，尽量一次多读取一些数据。
• 遵守最左前缀法则，等相关约定。

以(a,b,c)为联合索引
可以用到索引
WHERE a = const AND b > const ORDER BY b,c
索引失效
WHERE a = const ORDER BY c //丢失b索引

group by

与order by类似，只是多了一个分组。