参考视频教程:  
 **从零开始学调优-java 全技术栈 性能调优  **

目录
一、执行计划分析
二、SQL执行计划中的参数讲解——explain中的id
1、id值相同，从上往下顺序执行。
2、ID值不同，id值越大越优先查询
3、id值有相同，又有不同。id值越大越优先；id值相同，从上往下顺序执行
二、SQL执行计划中的参数讲解——explain中的select_type
三、SQL执行计划中的参数讲解——explain中的table
四、SQL执行计划中的参数讲解——explain中的type
1、type字段中的——system
2、type字段中的——const
3、type字段中的——eq_ref
4、type字段中的——ref
5、type字段中的——range
6、type字段中的——index
7、type字段中的——all
四、SQL执行计划中的参数讲解——explain中的possible_keys
五、SQL执行计划中的参数讲解——explain中的key
六、SQL执行计划中的参数讲解——explain中的key_len
七、SQL执行计划中的参数讲解——explain中的ref
八、SQL执行计划中的参数讲解——explain中的rows
九、SQL执行计划中的参数讲解——explain中的Extra
1、Extra字段中——using filesort
2、Extra字段中——using temporary
3、Extra字段中——using index
4、Extra字段中——using where
十、优化案例，单表优化、两表优化、三表优化
十一、避免索引失效的一些原则

前不久看了SQL优化的视频，学的时候醍醐灌顶，学完后发现就是在围绕着explain的各个字段讲解，先把学习的知识梳理一下，当然自己还是有很多不会的地方，后期不断完善。

sql优化是面试必问的面试点，而SQL优化主要就是在优化索引。 在学习索引优化之前，需要知道SQL的执行顺序，这里只给结论，深入学习可参考文章：步步深入：MySQL架构总览->查询执行流程->SQL解析顺序

SQL执行顺序
先执行SQL语句中的from,在执行on.. join ..where ..group by ….having …,最后执行select dinstinct ..order by limit …

from .. on.. join ..where ..group by ....having ...
select dinstinct ..order by limit ...

一、执行计划分析

通过explain可以知道mysql是如何处理语句的，并分析出查询或是表结构的性能瓶颈，其实就是在干查询优化器的事，通过expalin可以得到查询执行计划。

#语法：
explain +SQL语句
explain  select  * from tb 

• 各字段含义

id : 编号
select_type ：查询类型
table ：表
type ：类型
possible_keys ：预测用到的索引
key ：实际使用的索引
key_len ：实际使用索引的长度
ref :表之间的引用
rows ：通过索引查询到的数据量
Extra :额外的信息

下面讲解explain中各字段的具体含义，我们只关注三种，分别是type，key，rows

二、SQL执行计划中的参数讲解——explain中的id

explain中id是SQL执行顺序的标识，id的返回结果是数字，id 值越大优先级越高,越先被执行；id 相同,从上往下顺序执行。返回结果有三种情况。

目录
1、id值相同，从上往下顺序执行。
2、ID值不同，id值越大越优先查询
3、id值有相同，又有不同。id值越大越优先；id值相同，从上往下顺序执行

1、id值相同，从上往下顺序执行。

案例如下

explain select t.* from teacher t,course c,teacherCard tc where  t.tid=c.tid 
and t.tcid = tc.tcid 
and (c.cid=2 or tc.tcid=3)

图1

在上图SQL逻辑中，id值相同都为1，从上往下顺序执行。先执行teacher表（t表），在执行teacherCard 表（tc表），最后执行course 表（c表）。

建表：
create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);插入数据
insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;

• 改变表中数据量会影响表的执行顺序

图1表中数据量：
teacher表3条
teacherCard 表3条
course 表4条

改变teacher表数据条数，增加3条数据

insert into teacher values(4,'ta',4);
insert into teacher values(5,'tb',5);
insert into teacher values(6,'tc',6);

查看表的执行顺序

图2

我们可以看见修改了表数据量之后，表的执行也改变了。先执行teacherCard 表（tc表），在执行course 表（c表），最后执行teacher表（t表）。

• 表的执行顺序因数量的个数改变而改变的原因
  笛卡儿积。数据量小对程序占用内存小，优先计算（数据小的表，优先查询）

2、ID值不同，id值越大越优先查询

案例如下

mysql> explain select tc.tcdesc from teacherCard tc where tc.tcid = -> (select t.tcid from teacher t where  t.tid =  ->     (select c.tid from course c where c.cname = 'sql')-> );+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
|  1 | PRIMARY     | tc    | ALL  | NULL          | NULL        | NULL    | NULL  |    3 | Using where           |
|  2 | SUBQUERY    | t     | ALL  | NULL          | NULL        | NULL    | NULL  |    6 | Using where           |
|  3 | SUBQUERY    | c     | ref  | cname_index   | cname_index | 23      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+

在上面逻辑中，id值不相同，id值越大越优先查询 (本质：在嵌套子查询时，先查内层 再查外层)
从id字段和table字段可以知道程序在底层先执行course 表（c表），在执行teacher表（t表），最后执行teacherCard 表（tc表）

3、id值有相同，又有不同。id值越大越优先；id值相同，从上往下顺序执行

案例如下

mysql> explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid-> and t.tid = (select c.tid from course c where cname = 'sql') ;+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
|  1 | PRIMARY     | tc    | ALL  | NULL          | NULL        | NULL    | NULL  |    3 | NULL                                               |
|  1 | PRIMARY     | t     | ALL  | NULL          | NULL        | NULL    | NULL  |    6 | Using where; Using join buffer (Block Nested Loop) |
|  2 | SUBQUERY    | c     | ref  | cname_index   | cname_index | 23      | const |    1 | Using index condition                              |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+

在上面案例中，id值有相同，又有不同。先执行course 表（c表），在执行teacherCard 表（tc表），最后执行teacher表（t表）。

二、SQL执行计划中的参数讲解——explain中的select_type

select_type是查询类型，常见的查询类型如下。

1、: 简单查询，查询SQL中不包含子查询或者UNION
2、PRIMARY: 查询中若包含复杂的子查询,最外层的查询则标记为
3、SUBQUERY : 包含子查询SQL中的 子查询 （非最外层）
4、DERIVED : 衍生查询(使用到了临时表)。在from列表中包含子查询被标记为DRIVED衍生,MYSQL会递归执行这些子查询,把结果放到临时表中
5、UNION: 若第二个SELECT出现在union之后,则被标记为UNION, 若union包含在from子句的子查询中,外层select被标记为:derived
6、UNION RESULT: 从union表获取结果的select

查询类型这里只总结了结果，进一步了解建议大家看参考文章。
参考文章1：MySQL的Explain信息中select_type字段解释
参考文章2：explain之select_type

三、SQL执行计划中的参数讲解——explain中的table

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

mysql> explain select cr.cname from->  ( select * from course where tid = 1 union select * from course where tid = 2 ) cr;+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL            |
|  2 | DERIVED      | course     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where     |
|  3 | UNION        | course     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where     |
| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+

四、SQL执行计划中的参数讲解——explain中的type

表示MySQL在表中找到所需行的方式，又称"访问类型"，sql查询优化中一个很重要的指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，好的sql查询至少达到range级别，最好能达到ref

目录：
1、type字段中的——system
2、type字段中的——const
3、type字段中的——eq_ref
4、type字段中的——ref
5、type字段中的——range
6、type字段中的——index
7、type字段中的——all

1、type字段中的——system

实现场景：当表里面只有一行数据的时候就会这样，而且不管有没有索引都一样，这是const连接类型的特殊情况。
实现只有一条数据的系统表 ；或衍生表只有一条数据的主查询

2、type字段中的——const

实现场景：当查询只有唯一的一条记录被匹配，并且使用作为查询条件时， MySQL 会视查询出来的值为常数(可以为字符串或者数字），这种类型非常快。和system不同的地方是system是表里面只有一行数据，而const有多行数据，const是只有一行数据被匹配。

案例如下

create table test01
(tid int(3),tname varchar(20)
);
插入数据
insert into test01 values(1,'a') ;
insert into test01 values(2,'b') ;
增加索引
alter table test01 add constraint tid_pk primary key(tid) ;

#查询一个字段
mysql> explain select tid from test01 where tid =1 ;+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | test01 | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+#查询表中所有字段
mysql> explain select * from test01 where tid =1 ;+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test01 | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+

3、type字段中的——eq_ref

唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0），常见于唯一索引 和主键索引。
必须满足查询结果和表中数据是一致的。
案例如下

create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);
insert into teacher values(4,'ta',4);
insert into teacher values(5,'tb',5);
insert into teacher values(6,'tc',6);insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;建索引
alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid) ;

#1、teacher表和teacherCard 都有唯一索引tcid，查询teacher表的tcid，type类型是ref mysql> explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;
+----+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref          | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------+
|  1 | SIMPLE      | tc    | index | PRIMARY       | PRIMARY | 4       | NULL         |    3 | Using index |
|  1 | SIMPLE      | t     | ref   | uk_tcid       | uk_tcid | 5       | test.tc.tcid |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------+#2、teacher表和teacherCard 都有唯一索引tcid，查询teacherCard 表的tcid，type类型是ref mysql> explain select tc.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;
+----+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref          | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------+
|  1 | SIMPLE      | tc    | index | PRIMARY       | PRIMARY | 4       | NULL         |    3 | Using index |
|  1 | SIMPLE      | t     | ref   | uk_tcid       | uk_tcid | 5       | test.tc.tcid |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------+

为什么上面type类型不是eq_ref
原因：teacher有6条数据，索引查到的数据只有3条，还有3条数据没查到，没查到的数据结果就是0。

上面查询如何达到eq_ref

#删除teacher表中的三条数据
delete from teacher where tid>3;

删除数据后查看type类型

mysql> explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | t     | index  | uk_tcid       | uk_tcid | 5       | NULL        |    3 | Using where; Using index |
|  1 | SIMPLE      | tc    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t.tcid |    1 | Using index              |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+

以上SQL，用到的索引是 t.tcid,即teacher表中的tcid字段；如果teacher表的数据个数 和 连接查询teacherCard 表的数据个数一致（都是3条数据），则有可能满足eq_ref级别；否则无法满足。

4、type字段中的——ref

非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0行，多行或者一行）

出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

准备数据：insert into teacher values(4,'tz',4) ;insert into teacherCard values(4,'tz222');添加索引：
alter table teacher add index index_name (tname) ;

mysql> explain select * from teacher where tname='tz';
+----+-------------+---------+------+---------------+------------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | teacher | ref  | index_name    | index_name | 23      | const |    2 | Using index condition |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-----------------------+mysql> explain select * from teacher where tname='tw';
+----+-------------+---------+------+---------------+------------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | teacher | ref  | index_name    | index_name | 23      | const |    1 | Using index condition |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-----------------------+

5、type字段中的——range

range指的是有范围的索引扫描,where后面是一个范围查询(between ,> < >=, **特殊:in有时候会失效 **，从而转为 无索引all)

#添加索引
alter table teacher add index tid_index (tid) ;

#1、in范围查询时索引失效
mysql> explain select t.* from teacher t where t.tid in (1,2) ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | tid_index     | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+#2、<范围查询时type 类型是range
mysql> explain select t.* from teacher t where t.tid <3 ;
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | tid_index     | tid_index | 5       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+

6、type字段中的——index

查询全部索引中数据

mysql> explain select tid from teacher;--tid 是索引， 只需要扫描索引表，不需要所有表中的所有数据+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | teacher | index | NULL          | tid_index | 5       | NULL |    4 | Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+

7、type字段中的——all

查询全部表中的数据。这便是所谓的"全表扫描"，如果是展示一个数据表中的全部数据项 ，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。

mysql> explain select cid from course;--cid不是索引，需要全表所有，即需要所有表中的所有数据
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | course | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+

参考文章1：mysql中explain的type的解释
参考文章2：MySQL执行计划分析(EXPLAIN)之type字段详细介绍说明

四、SQL执行计划中的参数讲解——explain中的possible_keys

possible_keys可能用到的索引，是一种预测。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用，如果为空,说明没有可用的索引。

mysql> explain select cid from course;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | course | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+

该查询中possible_keys 为null，说明没有索引。

五、SQL执行计划中的参数讲解——explain中的key

实际使用到的索引

六、SQL执行计划中的参数讲解——explain中的key_len

key_len 索引的长度 ，用于判断复合索引是否被完全使用

案例如下

#创建一张表
create table test_kl
(name char(20)
);创建索引
alter table test_kl add index index_name(name) ;

查看执行计划

mysql> explain select * from test_k2 where name ='' ; 
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test_k2 | ref  | index_name    | index_name | 23      | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+

在utf8：1个字符站3个字节
如果索引字段可以为Null,则会使用1个字节用于标识。
20*1=20 + 1(null) +2(用2个字节 标识可变长度，字段类型是可变长度) =23

utf8:1个字符3个字节
gbk:1个字符2个字节
latin:1个字符1个字节

复合索引案例如下

alter table test_k2 add column name1 varchar(20) ;  --name1可以为null
drop index index_name on test_k2 ;增加一个复合索引 
alter table test_k2 add index name_name1_index (name,name1) ; 

#复合索引，要用name1 ，得先用name
mysql> explain select * from test_k2 where name1 = '' ; 
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test_k2 | index | NULL          | name_name1_index | 46      | NULL |    1 | Using where; Using index |
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+#2、虽然是复合索引，但是只用到了name
mysql> explain select * from test_k2 where name = '' ; 
+----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys    | key              | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test_k2 | ref  | name_name1_index | name_name1_index | 23      | const |    1 | Using where; Using index |
+----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+

七、SQL执行计划中的参数讲解——explain中的ref

注意与type中的ref值区分，显示索引的哪一列被使用了 。返回两种结果，const（是常量则该字段结果是const），或者使用了那个字段。

alter table course  add index tid_index (tid) ;

mysql> explain select * from course c,teacher t where c.tid = t.tid  and t.tname ='tw' ;
+----+-------------+-------+------+----------------------+------------+---------+------------+------+------------------------------------+
| id | select_type | table | type | possible_keys        | key        | key_len | ref        | rows | Extra                              |
+----+-------------+-------+------+----------------------+------------+---------+------------+------+------------------------------------+
|  1 | SIMPLE      | t     | ref  | index_name,tid_index | index_name | 23      | const      |    1 | Using index condition; Using where |
|  1 | SIMPLE      | c     | ref  | tid_index            | tid_index  | 5       | test.t.tid |    1 | NULL                               |
+----+-------------+-------+------+----------------------+------------+---------+------------+------+------------------------------------+

八、SQL执行计划中的参数讲解——explain中的rows

被索引优化查询的数据个数 (实际通过索引而查询到的 数据个数)
explain select * from course c,teacher t where c.tid = t.tid
and t.tname = ‘tz’ ;

九、SQL执行计划中的参数讲解——explain中的Extra

目录：
1、Extra字段中——using filesort
2、Extra字段中——using temporary
3、Extra字段中——using index
4、Extra字段中——using where

Extra其他信息，出现Using filesort、Using temporary 意味着不能使用索引,效率会受到重大影响。应尽可能对此进行优化

1、Extra字段中——using filesort

性能消耗大；需要"额外"的一次排序（或者是额外得一次查询），常见于 order by 语句中。
排序：排序得前提是先查询，在排序。比如需要对10个人根据年龄排序，所以排序之前需要先排序

• 案例如下

create table test02
(a1 char(3),a2 char(3),a3 char(3),index idx_a1(a1),index idx_a2(a2),index idx_a3(a3)
);

mysql> explain select * from test02 where a1 ='' order by a2 ; +----+-------------+--------+------+---------------+--------+---------+-------+------+----------------------------------------------------+
| id | select_type | table  | type | possible_keys | key    | key_len | ref   | rows | Extra                                              |
+----+-------------+--------+------+---------------+--------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | test02 | ref  | idx_a1        | idx_a1 | 4       | const |    1 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------+---------------+--------+---------+-------+------+----------------------------------------------------+

Extra字段中出现了Using filesort。

小结： 对于单索引， 如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则会出现using filesort；
如何避免： where哪些字段，就order by那些字段2

• 复合索引——分析Extra字段
  复合索引,不能跨列（最佳左前缀）

drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ;

test02 有复合索引idx_a1_a2_a3 (a1,a2,a3) ,where条件是a1,排序是a3。跨列了，所以出现了Using filesort。

mysql> explain select *from test02 where a1='' order by a3 ;
+----+-------------+--------+------+---------------+--------------+---------+-------+------+------------------------------------------+
| id | select_type | table  | type | possible_keys | key          | key_len | ref   | rows | Extra                                    |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+------------------------------------------+
|  1 | SIMPLE      | test02 | ref  | idx_a1_a2_a3  | idx_a1_a2_a3 | 4       | const |    1 | Using where; Using index; Using filesort |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+------------------------------------------+

Extra字段存在Using filesort

mysql> explain select *from test02 where a2='' order by a3 ;
+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------------------------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra                                    |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | test02 | index | NULL          | idx_a1_a2_a3 | 12      | NULL |    1 | Using where; Using index; Using filesort |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------------------------------+

Extra字段无Using filesort

mysql> explain select *from test02 where a1='' order by a2 ;+----+-------------+--------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test02 | ref  | idx_a1_a2_a3  | idx_a1_a2_a3 | 4       | const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+--------------------------+

小结：避免where和order by 按照复合索引的顺序使用，不要跨列或无序使用。

2、Extra字段中——using temporary

using temporary性能损耗大 ，用到了临时表。一般出现在group by 语句中，已经有表了，但不适用，必须在来一张表。了解表概念，需要知道sql的执行顺序，SQL的执行顺序会产生虚拟表。
参考文章：步步深入：MySQL架构总览->查询执行流程->SQL解析顺序

mysql> explain select a1 from test02 where a1 in ('1','2','3') group by a2;+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra                                                     |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | test02 | index | idx_a1_a2_a3  | idx_a1_a2_a3 | 12      | NULL |    1 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------------------------------------------+

出现了Using temporary，根据a2分组，但是没查询a2.

3、Extra字段中——using index

出现using index性能会提升，即索引覆盖（覆盖索引）。
索引覆盖该SQL不读取原文件，只从索引文件中获取数据 （不需要回表查询），只要使用到的列 全部都在索引中，就是索引覆盖using index。

案例如下

#例如：test02表中有一个复合索引(a1,a2,a3)
mysql> explain select a1,a2 from test02 where a1='' or a2= '' ;+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test02 | index | idx_a1_a2_a3  | idx_a1_a2_a3 | 12      | NULL |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+

4、Extra字段中——using where

using where 回表查询，需要在索引中查，有需要在原表中查就会出现using where

mysql> explain select a1,a3 from test02 where a3 = '' ; +----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test02 | index | NULL          | idx_a1_a2_a3 | 12      | NULL |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+

5、Extra字段中——impossible where

where子句永远为false，会出现impossible where

案例如下：

mysql> explain select * from test02 where a1='x' and a1='y'  ;+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

a1不会出现即等于x，又等于y

十、优化案例，单表优化、两表优化、三表优化

1、单表优化

小结：a.最佳做前缀，保持索引的定义和使用的顺序一致性
b.索引需要逐步优化
c.将含In的范围查询 放到where条件的最后，防止失效。

2、两表优化

1、索引往哪张表加？
小表驱动大表
2、索引建立经常使用的字段上
一般情况对于左外连接，给左表加索引；右外连接，给右表加索引
3、join时，为什么需要用小表驱动大表？
原因是join时是双层循环，一般建议将数据小的循环放外层；数据大的循环放内存。
在编程语言中，外层越小，内存越大，性能越高（小表在左，外层循环少，依赖的原则是程序优化）

3、三张表优化A B C

a.小表驱动大表
b.索引建立在经常查询的字段上–where 字段加索引，和常用字段加索引

十一、避免索引失效的一些原则

使用数据如下

create table book
(bid int(4) primary key,name varchar(20) not null,authorid int(4) not null,publicid int(4) not null,typeid int(4) not null 
);insert into book values(1,'tjava',1,1,2) ;
insert into book values(2,'tc',2,1,2) ;
insert into book values(3,'wx',3,2,1) ;
insert into book values(4,'math',4,2,3) ;   alter table book add index idx_bta (bid,typeid,authorid);

1、复合索引
a.复合索引，不要跨列或无序使用（最佳左前缀）
(a,b,c)
b.复合索引，尽量使用全索引匹配
(a,b,c)
2、不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效

mysql> explain select * from book where authorid = 1 and typeid = 2 ;
---用到了at 2个索引
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------+
|  1 | SIMPLE      | book  | ref  | idx_atb       | idx_atb | 8       | const,const |    1 | NULL  |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.00 sec)mysql> explain select * from book where authorid = 1 and typeid*2 = 2 ; 
--用到了a1个索引
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | book  | ref  | idx_atb       | idx_atb | 4       | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)mysql> explain select * from book where authorid*2 = 1 and typeid*2 = 2 ; 
----用到了0个索引
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)mysql> explain select * from book where authorid*2 = 1 and typeid = 2 ;
---用到了0个索引,
--原因：对于复合索引，如果左边失效，右侧全部失效。(a,b,c)，例如如果 b失效，则b c同时失效。+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

3、复合索引不能使用不等于（!= <>）或is null (is not null)
复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。复合索引中如果有>，则自身和右侧索引全部失效。

案例如下

mysql> explain select * from book where authorid != 1 and typeid =2 ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | idx_atb       | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)mysql> explain select * from book where authorid != 1 and typeid !=2 ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | idx_atb       | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。

• 体验概率情况(< > =)
  原因是服务层中有SQL优化器，可能会影响我们的优化。

drop index idx_typeid on book;drop index idx_authroid on book;alter table book add index idx_book_at (authorid,typeid);explain select * from book where authorid = 1 and typeid =2 ;--复合索引at全部使用
explain select * from book where authorid > 1 and typeid =2 ; --复合索引中如果有>，则自身和右侧索引全部失效。
explain select * from book where authorid = 1 and typeid >2 ;--复合索引at全部使用

• 明显的概率问题

explain select * from book where authorid < 1 and typeid =2 ;--复合索引at只用到了1个索引
explain select * from book where authorid < 4 and typeid =2 ;--复合索引全部失效

我们学习索引优化 ，是一个大部分情况适用的结论，但由于SQL优化器等原因 该结论不是100%正确。一般而言， 范围查询（> < in），之后的索引失效。

4、 like尽量以"常量"开头，不要以’%’开头，否则索引失效
如下

select * from xx where name like '%x%' ; --name索引失效explain select * from teacher  where tname like '%x%'; --tname索引失效
explain select * from teacher  where tname like 'x%';
explain select tname from teacher  where tname like '%x%';

如果必须使用like ’%x%’进行模糊查询 ，可以使用索引覆盖挽救一部分。

5、尽量不要使用类型转换（显示、隐式），否则索引失效

explain select * from teacher where tname = 'abc' ;
explain select * from teacher where tname = 123 ;//程序底层将 123 -> '123'，即进行了类型转换，因此索引失效

6、尽量不要使用or，否则索引失效

explain select * from teacher where tname ='' or tcid >1 ; --将or左侧的tname 失效。

7、一些其他的优化方法

• （1）exist和in

select ..from table where exist (子查询) ;select ..from table where 字段 in  (子查询) ;

如果主查询的数据集大，则使用In ,效率高。
如果子查询的数据集大，则使用exist,效率高。

• （2）order by 优化
  using filesort 有两种算法：双路排序、单路排序 （根据IO的次数）
  MySQL4.1之前 默认使用 双路排序；双路：扫描2次磁盘（1：从磁盘读取排序字段 ,对排序字段进行排序（在buffer中进行的排序） 2：扫描其他字段 ）
  –IO较消耗性能

MySQL4.1之后 默认使用 单路排序 ： 只读取一次（全部字段），在buffer中进行排序。但种单路排序 会有一定的隐患 （不一定真的是"单路|1次IO"，有可能多次IO）。原因：如果数据量特别大，则无法 将所有字段的数据 一次性读取完毕，因此 会进行"分片读取、多次读取"。
**注意：单路排序 比双路排序 会占用更多的buffer。**单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小： set max_length_for_sort_data = 1024 单位byte

如果max_length_for_sort_data值太低，则mysql会自动从 单路->双路 （太低：需要排序的列的总大小超过max_length_for_sort_data定义的字节数）

提高order by查询的策略
a.选择使用单路、双路 ；调整buffer的容量大小；
b.避免select * …
c.复合索引 不要跨列使用 ，避免using filesort
d.保证全部的排序字段 排序的一致性（都是升序 或 降序）

后记

最后SQL优化简单记录了，SQL优化也暂时学习到这，后期学习中会继续完善。文章写得不好，但是总比自己不梳理好。

在等2天就上班了，过年好像什么都没学到，新的一年也要加油呀。发现自己在目标规划，以及拆解目标上面特别擅长，但是在落实到每天或每周上面执行有点差，新的一年得改掉这个习惯，自己还有很多事想去做。除了工作，还想学习其它的。

另外下个春节不这么早回成都了，要在老家陪外婆，对爸妈也要像外婆一样好好的