前不久看了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= or tc.tcid=3)
图1
在上图SQL逻辑中，id值相同都为1，从上往下顺序执行。先执行teacher表（t表），在执行teacherCard 表（tc表），最后执行course 表（c表）。
建表：
create table course
(
cid int(),
cname varchar(),
tid int()
);
create table teacher
(
tid int(),
tname varchar(),
tcid int()
);
create table teacherCard
(
tcid int(),
tcdesc varchar()
);
插入数据
insert into course values(,' java ',1);
insert into course values(,'html',1);
insert into course values(,'sql',2);
insert into course values(,'web',3);
insert into teacher values(,'tz',1);
insert into teacher values(,'tw',2);
insert into teacher values(,'tl',3);
insert into teacherCard values(,'tzdesc') ;
insert into teacherCard values(,'twdesc') ;
insert into teacherCard values(,'tldesc') ;
- 改变表中数据量会影响表的执行顺序
图表中数据量： teacher表条 teacherCard 表条 course 表条
改变teacher表数据条数，增加3条数据
insert into teacher values(,'ta',4);
insert into teacher values(,'tb',5);
insert into teacher values(,'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 |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
| | PRIMARY | tc | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| | SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
| | 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 |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
| | PRIMARY | tc | ALL | NULL | NULL | NULL | NULL | 3 | NULL |
| | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
| | 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 = union select * from course where tid = 2 ) cr;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 8 | NULL |
| | DERIVED | course | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
| | UNION | course | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
| NULL | UNION RESULT | <union,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 test
(
tid int(),
tname varchar()
);
插入数据
insert into test values(1,'a') ;
insert into test values(2,'b') ;
增加索引
alter table test add constraint tid_pk primary key(tid) ;
#查询一个字段
mysql> explain select tid from test where tid =1 ;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
| | SIMPLE | test01 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
#查询表中所有字段
mysql> explain select * from test where tid =1 ;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| | SIMPLE | test01 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
3、type字段中的——eq_ref
唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0），常见于唯一索引 和主键索引。
必须满足查询结果和表中数据是一致的。
案例如下
create table teacher
(
tid int(),
tname varchar(),
tcid int()
);
create table teacherCard
(
tcid int(),
tcdesc varchar()
);
insert into teacher values(,'tz',1);
insert into teacher values(,'tw',2);
insert into teacher values(,'tl',3);
insert into teacher values(,'ta',4);
insert into teacher values(,'tb',5);
insert into teacher values(,'tc',6);
insert into teacherCard values(,'tzdesc') ;
insert into teacherCard values(,'twdesc') ;
insert into teacherCard values(,'tldesc') ;
建索引
alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid) ;
#、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 |
+----+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------+
| | SIMPLE | tc | index | PRIMARY | PRIMARY | 4 | NULL | 3 | Using index |
| | SIMPLE | t | ref | uk_tcid | uk_tcid | 5 | test.tc.tcid | 1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------+
#、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 |
+----+-------------+-------+-------+---------------+---------+---------+--------------+------+-------------+
| | SIMPLE | tc | index | PRIMARY | PRIMARY | 4 | NULL | 3 | Using index |
| | 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>;
删除数据后查看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 |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
| | SIMPLE | t | index | uk_tcid | uk_tcid | 5 | NULL | 3 | Using where; Using index |
| | 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(,'tz',4) ;
insert into teacherCard values(,'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 |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-----------------------+
| | 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 |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-----------------------+
| | 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) ;
#、in范围查询时索引失效
mysql> explain select t.* from teacher t where t.tid in (,2) ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| | SIMPLE | t | ALL | tid_index | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
#、<范围查询时type 类型是range
mysql> explain select t.* from teacher t where t.tid < ;
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| | 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 |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
| | 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 |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| | 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 |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| | 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()
);
创建索引
alter table test_kl add index index_name(name) ;
查看执行计划
mysql> explain select * from test_k where name ='' ;
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
| | 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_k add column name1 varchar(20) ; --name1可以为null
drop index index_name on test_k ;
增加一个复合索引
alter table test_k add index name_name1_index (name,name1) ;
#复合索引，要用name ，得先用name
mysql> explain select * from test_k where name1 = '' ;
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+
| | SIMPLE | test_k2 | index | NULL | name_name1_index | 46 | NULL | 1 | Using where; Using index |
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+
#、虽然是复合索引，但是只用到了name
mysql> explain select * from test_k where name = '' ;
+----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+
| | 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 |
+----+-------------+-------+------+----------------------+------------+---------+------------+------+------------------------------------+
| | SIMPLE | t | ref | index_name,tid_index | index_name | 23 | const | 1 | Using index condition; Using where |
| | 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 test
(
a char(3),
a char(3),
a char(3),
index idx_a(a1),
index idx_a(a2),
index idx_a(a3)
);
mysql> explain select * from test where a1 ='' order by a2 ;
+----+-------------+--------+------+---------------+--------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+--------+---------+-------+------+----------------------------------------------------+
| | 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_a on test02;
drop index idx_a on test02;
drop index idx_a on test02;
alter table test 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 test where a1='' order by a3 ;
+----+-------------+--------+------+---------------+--------------+---------+-------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+------------------------------------------+
| | 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 test where a2='' order by a3 ;
+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------------------------------+
| | SIMPLE | test02 | index | NULL | idx_a1_a2_a3 | 12 | NULL | 1 | Using where; Using index; Using filesort |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------------------------------+
Extra字段无Using filesort
mysql> explain select *from test where a1='' order by a2 ;
+----+-------------+--------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+--------------------------+
| | 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 a from test02 where a1 in ('1','2','3') group by a2;
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------------------------------------------+
| | 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。
案例如下
#例如：test表中有一个复合索引(a1,a2,a3)
mysql> explain select a,a2 from test02 where a1='' or a2= '' ;
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
| | 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 a,a3 from test02 where a3 = '' ;
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
| | 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 test where a1='x' and a1='y' ;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| | 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() primary key,
name varchar() not null,
authorid int() not null,
publicid int() not null,
typeid int() not null
);
insert into book values(,'tjava',1,1,2) ;
insert into book values(,'tc',2,1,2) ;
insert into book values(,'wx',3,2,1) ;
insert into book values(,'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 = and typeid = 2 ;
---用到了at个索引
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------+
| | SIMPLE | book | ref | idx_atb | idx_atb | 8 | const,const | 1 | NULL |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-------+ row in set (0.00 sec)
mysql> explain select * from book where authorid = and typeid*2 = 2 ;
--用到了a个索引
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| | SIMPLE | book | ref | idx_atb | idx_atb | 4 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ row in set (0.00 sec)
mysql> explain select * from book where authorid* = 1 and typeid*2 = 2 ;
----用到了个索引
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+ row in set (0.00 sec)
mysql> explain select * from book where authorid* = 1 and typeid = 2 ;
---用到了个索引,
--原因：对于复合索引，如果左边失效，右侧全部失效。(a,b,c)，例如如果 b失效，则b c同时失效。
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+ row in set (0.00 sec)
3、复合索引不能使用不等于（!= <>）或is null (is not null)
复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。复合索引中如果有>，则自身和右侧索引全部失效。
案例如下
mysql> explain select * from book where authorid != and typeid =2 ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| | SIMPLE | book | ALL | idx_atb | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+ row in set (0.00 sec)
mysql> explain select * from book where authorid != and typeid !=2 ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| | 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 = and typeid =2 ;--复合索引at全部使用
explain select * from book where authorid > and typeid =2 ; --复合索引中如果有>，则自身和右侧索引全部失效。
explain select * from book where authorid = and typeid >2 ;--复合索引at全部使用
- 明显的概率问题
explain select * from book where authorid < and typeid =2 ;--复合索引at只用到了1个索引
explain select * from book where authorid < 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'，即进行了类型转换，因此索引失效
6、尽量不要使用or，否则索引失效
explain select * from teacher where tname ='' or tcid > ; --将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优化也暂时学习到这，后期学习中会继续完善。文章写得不好，但是总比自己不梳理好。