SQL优化 MySQL版，explain SQL执行计划详解

目录
一、执行计划分析
二、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、 $\color{#0000FF }{SIMPLE }$ : 简单查询，查询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

实现场景：当查询只有唯一的一条记录被匹配，并且使用 $\color{red}{主键等于常数或者唯一索引等于某常数}$ 作为查询条件时， 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天就上班了，过年好像什么都没学到，新的一年也要加油呀。发现自己在目标规划，以及拆解目标上面特别擅长，但是在落实到每天或每周上面执行有点差，新的一年得改掉这个习惯，自己还有很多事想去做。除了工作，还想学习其它的。

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

$\color{red}{新的一年加油}$

最后编辑于：2021.02.16 16:46:13

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 161,873评论 4赞 370
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 68,483评论 1赞 306
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 111,525评论 0赞 254
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,595评论 0赞 218
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 53,018评论 3赞 295
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,958评论 1赞 224
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 32,118评论 2赞 317
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,873评论 0赞 208
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,643评论 1赞 250
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,813评论 2赞 253
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,293评论 1赞 265
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,615评论 3赞 262
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,306评论 3赞 242
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,170评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,968评论 0赞 201
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 36,107评论 2赞 285
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,894评论 2赞 278

SQL优化 MySQL版 ，explain SQL执行计划详解

一、执行计划分析

二、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

5、Extra字段中——impossible where

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

1、单表优化

2、两表优化

3、三张表优化A B C

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

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

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

7、一些其他的优化方法

后记

推荐阅读更多精彩内容

SQL优化 MySQL版，explain SQL执行计划详解