mysql执行计划及sql优化

在mysql5.5之后，就默认使用InnoDB为存储引擎，InnoDB使用B+树实现，B+树的数据全部存储于叶子节点当中，查询任意数据的次数都为N（树的高度）。如上图所示，使用索引查找只需要3次寻找，通过索引当中的地址，在找到对应数据存储的位置。所以使用索引就等于给一本字典加了一个检索目录，可以快速的找到数据。

使用索引有以下好处：

提高查询效率（降低IO使用率）
降低CPU使用率（...order by xxx,因为 B树索引本身就是一个好排序的结构，因此在排序时可以直接使用）

但是也有以下弊端：

索引本身很大，会占用数据库的空间
索引不是所有情况均适用：a.少量数据；b.频繁更新的字段；c.很少使用的字段
索引会降低增删改的效率

但是由于数据库一般都是读多写少，所以建立索引对于提升数据库性能是很显著的。

1、执行计划

在查询语句前加上explain即可查看该sql的执行计划，执行计划包含以下元素：

mysql执行计划及sql优化

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

1.1、id

id值相同：从上往下顺序执行。

id值不同：id值越大越优先查询 (本质：在嵌套子查询时，先查内层再查外层)。

和Java的jit机制一样，数据库执行的顺序也不一定是我们sql的编写顺序，因为mysql提供了sql优化器，可能会对我们编写的sql进行优化。

1.2、select_type（查询类型）

PRIMARY:包含子查询SQL中的主查询（最外层）
SUBQUERY：包含子查询SQL中的子查询（非最外层）
simple:简单查询（不包含子查询、union）
derived:衍生查询(使用到了临时表)

在from子查询中，如果有table1 union table2 ，则table1 就是derived,table2就是union。

1.3、type（索引类型）

从效率从高到低依次为：system>const>eq_ref>ref>range>index>all ，要对type进行优化的前提是要有索引。

其中：system,const只是理想情况；实际能达到 ref>range。

system（忽略）: 只有一条数据的系统表；或衍生表只有一条数据的主查询

create table test01
(
	tid int(3),
	tname varchar(20)
);

insert into test01 values(1,'a') ;

alter table test01 add constraint tid_pk primary key(tid) ;

explain select * from (select * from test01 )t where tid =1 ;

const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引（类型与索引类型有关）

explain select tid from test01 where tid =1 ;

eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、不能0）
ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）
range：检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效，从而转为无索引all)

alter table teacher add index tid_index (tid) ;
explain select t.* from teacher t where t.tid in (1,2) ;
explain select t.* from teacher t where t.tid <3 ;

使用in的时候会让索引失效，使用<之后type变成range。

index：查询全部索引中数据
all：查询全部表中的数据

1.4、possible_keys （可能用到的索引）

只是可能使用到的索引，有可能不key不同。

1.5、key

实际使用到的索引。

1.6、key_len（索引长度）

对于复合索引来说，我们可能不知道使用到了那几个字段，通过key_len可以判断复合索引是否完全被使用。

如果索引字段可以为Null,则会使用1个字节用于标识。

1.7、ref

指明当前表所参照的字段。

1.8、rows

被索引优化查询的数据个数 (实际通过索引而查询到的数据个数)。

1.9、Extra

using filesort ：性能消耗大；需要“额外”的一次排序（查询）。常见于 order by 语句中。

对于单索引，如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则会出现using filesort；

对于复合索引，使用应该遵循最佳左前缀原则，不要跨列或者无序使用。

在where条件中按照哪些条件查找，就在order by中使用哪些字段进行排序，可以最大限度的避免using file sort。

using temporary:性能损耗大，用到了临时表。一般出现在group by 语句中。

为了避免这个问题，查询哪些列，就使用哪些列进行分组。

using index :性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据（不需要回表查询）。

只要使用到的列全部都在索引中，就是索引覆盖using index。

例如：test02表中有一个复合索引(a1,a2,a3)
```
explain select a1,a2 from test02 where a1='' or a2= '' ; --using index

drop index idx_a1_a2_a3 on test02;



alter table test02 add index idx_a1_a2(a1,a2) ;

explain select a1,a3 from test02 where a1='' or a3= '' ;
```
如果用到了索引覆盖(using index时)，会对 possible_keys和key造成影响：

a、如果没有where，则索引只出现在key中；

b、如果有where，则索引出现在key和possible_keys中。
```
explain select a1,a2 from test02 where a1='' or a2= '' ;

explain select a1,a2 from test02 ;
```
using where （需要回表查询）

假设age是索引列，但查询语句select age,name from ...where age =...,此语句中必须回原表查Name，因此会显示using where。

explain select a1,a3 from test02 where a3 = '' ; --a3需要回原表查询

impossible where：where子句永远为false

explain select * from test02 where a1='x' and a1='y' ;

2、sql优化

mysql官网提供有sql优化的文档，感兴趣的可以仔细研究下：https://dev.mysql.com/doc/refman/5.7/en/optimize-overview.html。

2.1、单表优化

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) ;

需求：查询authorid=1且 typeid为2或3的bid

explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc ;

mysql执行计划及sql优化

由于authorid没有添加索引，所以type类型是ALL，进行了全表扫描，同时order by还需要额外的进行一次排序。先进行以下优化：

1、加索引

alter table book add index idx_bta (bid,typeid,authorid);

explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc ;

mysql执行计划及sql优化

添加索引之后，type提升为index，同时bid也在索引中就可以查询到，使用到了索引覆盖。但是此时所以的顺序跟where中使用的顺序不一致，可以继续进行索引优化。

索引一旦进行升级优化，需要将之前废弃的索引删掉，防止干扰。

2、修改索引顺序

drop index idx_bta on book;

alter table book add index idx_tab (typeid,authorid,bid);

explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc ;

mysql执行计划及sql优化

修改索引顺序之后，where和order by的顺序复合最佳左前缀原则，type变成了range，同时因为order by中的tpyeid也在where中，所以using filesort也没有了。

（笔者使用的mysql版本为5.7，这里将typeid的in条件写在前面，索引照样生效了，在5.6中，此时的type还是index）。

如果因为in的原因使索引失效，我们就还需要交换索引的顺序，或者改变sql的顺序，总体原则就是使用到更多的索引，减少索引的失效。

2.2、两表优化

create table teacher2
(
	tid int(4) primary key,
	cid int(4) not null
);

insert into teacher2 values(1,2);
insert into teacher2 values(2,1);
insert into teacher2 values(3,3);

create table course2
(
	cid int(4) ,
	cname varchar(20)
);

insert into course2 values(1,'java');
insert into course2 values(2,'python');
insert into course2 values(3,'kotlin');

对以下sql进行优化：

SELECT * FROM teacher2 t LEFT JOIN course2 c ON t.cid = c.cid WHERE c.cname = 'java';

mysql执行计划及sql优化

Using join buffer:extra中的一个选项，作用：Mysql引擎使用了连接缓存。

没有创建索引，所以type都是ALL，对于多张表来说，写sql的时候我们要遵循小表驱动大表原则，将小表作为主表，这样中间结果会少，占用的空间会少，效率也会提高，创建索引的时候，在这个sql当中，使用的是cid进行关联，则：

将索引建立在频繁使用的字段上：建立在使用频繁的cid上；
将索引建立在主表上：一般对于左外连接，给左边建立索引，对于右外连接，给右表建立索引。

1、加索引

alter table teacher2 add index index_teacher2_cid(cid) ;
alter table course2 add index index_course2_cname(cname);

EXPLAIN SELECT * FROM teacher2 t LEFT JOIN course2 c ON t.cid = c.cid WHERE c.cname = 'java';

mysql执行计划及sql优化

建立索引之后，type提升为ref，同时Using join buffer也消失了。

其它多表优化的原理跟两表优化一样，都是在索引上做文章。

还需要特别注意的一点是最佳左前缀原则：

如果 (a,b,c,d)复合索引和使用的顺序全部一致(且不跨列使用)，则复合索引全部使用。如果部分一致(且不跨列使用)，则使用部分索引。
where和order by 拼起来，不要跨列使用。

2.3、索引失效

sql优化要有很重要的一个方面就是避免索引失效，以下不当操作会使索引失效。

like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
最佳左前缀原则，组合索引，不是使用第一列索引，索引失效。
or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。
数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。
在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0。
不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效；
当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

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

2.4、其它优化小技巧

1、exist和in

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

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

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

2、order by

避免使用select *
保证全部的排序字段排序的一致性（都是升序或降序）

mysql执行计划及sql优化

1、执行计划

1.1、id

1.2、select_type（查询类型）

1.3、type（索引类型）

1.4、possible_keys （可能用到的索引）

1.5、key

1.6、key_len（索引长度）

1.7、ref

1.8、rows

1.9、Extra

2、sql优化

2.1、单表优化

2.2、两表优化

2.3、索引失效

2.4、其它优化小技巧

MySQl优化千万数据级快速分页sql语句

SQL芯片可优化MySQL数据仓库最主要能缓解I/O瓶颈

MySql优化之体系结构及存储引擎（总结分享）

MySQL索引背后的之使用策略及优化(高性能索引策略)

MySQL索引背后的之使用策略及优化(高性能索引策略)

SqlServer 执行计划及Sql查询优化初探

通过分析SQL语句的执行计划优化SQL

Sql Server 索引使用情况及优化的相关Sql语句分享

SqlServer 执行计划及Sql查询优化初探

MySQL性能优化出题业务SQL优化

mysql执行计划及sql优化

1、执行计划

1.1、id

1.2、select_type（查询类型）

1.3、type（索引类型）

1.4、possible_keys （可能用到的索引）

1.5、key

1.6、key_len（索引长度）

1.7、ref

1.8、rows

1.9、Extra

2、sql优化

2.1、单表优化

2.2、两表优化

2.3、索引失效

2.4、其它优化小技巧

MySQl优化千万数据级快速分页sql语句

SQL芯片可优化MySQL数据仓库 最主要能缓解I/O瓶颈

MySql优化之体系结构及存储引擎（总结分享）

MySQL索引背后的之使用策略及优化(高性能索引策略)

MySQL索引背后的之使用策略及优化(高性能索引策略)

SqlServer 执行计划及Sql查询优化初探

通过分析SQL语句的执行计划优化SQL

Sql Server 索引使用情况及优化的相关Sql语句分享

SqlServer 执行计划及Sql查询优化初探

MySQL性能优化 出题业务SQL优化

SQL芯片可优化MySQL数据仓库最主要能缓解I/O瓶颈

MySQL性能优化出题业务SQL优化