18000 字的 SQL 优化大全，收藏直接起飞！

大家好，我是杰哥。

今天给大家分享一篇关于SQL优化的硬核文章，全文有点长，建议收藏后慢慢看。

很多朋友在做数据分析时，分析两分钟，跑数两小时？

在使用SQL过程中不仅要关注数据结果，同样要注意SQL语句的执行效率。

本文涉及三部分，篇幅较长，建议收藏后翻看：

• SQL介绍

• SQL优化方法

• SQL优化实例

1、MySQL的基本架构

1）MySQL的基础架构图

左边的client可以看成是客户端，客户端有很多，像我们经常你使用的CMD黑窗口，像我们经常用于学习的WorkBench，像企业经常使用的Navicat工具，它们都是一个客户端。右边的这一大堆都可以看成是Server(MySQL的服务端)，我们将Server在细分为sql层和存储引擎层。

当查询出数据以后，会返回给执行器。执行器一方面将结果写到查询缓存里面，当你下次再次查询的时候，就可以直接从查询缓存中获取到数据了。另一方面，直接将结果响应回客户端。

2）查询数据库的引擎

① show engines;

② show variables like “%storage_engine%”;

3）指定数据库对象的存储引擎

create table tb(
    id int(4) auto_increment,
    name varchar(5),
    dept varchar(5),
    primary key(id)
) engine=myISAM auto_increment=1 default charset=utf8;

2、SQL优化

1）为什么需要进行SQL优化？

在进行多表连接查询、子查询等操作的时候，由于你写出的SQL语句欠佳，导致的服务器执行时间太长，我们等待结果的时间太长。基于此，我们需要学习怎么优化SQL。

2）mysql的编写过程和解析过程

① 编写过程

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

② 解析过程

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

提供一个网站，详细说明了mysql解析过程：

https://www.cnblogs.com/annsshadow/p/5037667.html

3）SQL优化—主要就是优化索引

优化SQL，最重要的就是优化SQL索引。

索引相当于字典的目录。利用字典目录查找汉字的过程，就相当于利用SQL索引查找某条记录的过程。有了索引，就可以很方便快捷的定位某条记录。

① 什么是索引？

索引就是帮助MySQL高效获取数据的一种【数据结构】。索引是一种树结构，MySQL中一般用的是【B+树】。

② 索引图示说明(这里用二叉树来帮助我们理解索引)

树形结构的特点是：子元素比父元素小的，放在左侧；子元素比父元素大的，放在右侧。

这个图示只是为了帮我们简单理解索引的，真实的关于【B+树】的说明，我们会在下面进行说明。

索引是怎么查找数据的呢？两个字【指向】，上图中我们给age列指定了一个索引，即类似于右侧的这种树形结构。mysql表中的每一行记录都有一个硬件地址，例如索引中的age=50，指向的就是源表中该行的标识符(“硬件地址”)。也就是说，树形索引建立了与源表中每行记录硬件地址的映射关系，当你指定了某个索引，这种映射关系也就建成了，这就是为什么我们可以通过索引快速定位源表中记录的原因。

以【select * from student where age=33】查询语句为例。当我们不加索引的时候，会从上到下扫描源表，当扫描到第5行的时候，找到了我们想要找到了元素，一共是查询了5次。当添加了索引以后，就直接在树形结构中进行查找，33比50小，就从左侧查询到了23，33大于23，就又查询到了右侧，这下找到了33，整个索引结束，一共进行了3次查找。是不是很方便，假如我们此时需要查找age=62，你再想想“添加索引”前后，查找次数的变化情况。

4）索引的弊端

1.当数据量很大的时候，索引也会很大(当然相比于源表来说，还是相当小的)，也需要存放在内存/硬盘中(通常存放在硬盘中)，占据一定的内存空间/物理空间。

2.索引并不适用于所有情况：a.少量数据；b.频繁进行改动的字段，不适合做索引；c.很少使用的字段，不需要加索引；

3.索引会提高数据查询效率，但是会降低“增、删、改”的效率。当不使用索引的时候，我们进行数据的增删改，只需要操作源表即可，但是当我们添加索引后，不仅需要修改源表，也需要再次修改索引，很麻烦。尽管是这样，添加索引还是很划算的，因为我们大多数使用的就是查询，“查询”对于程序的性能影响是很大的。

5）索引的优势

1.提高查询效率(降低了IO使用率)。当创建了索引后，查询次数减少了。

2.降低CPU使用率。比如说【…order by age desc】这样一个操作，当不加索引，会把源表加载到内存中做一个排序操作，极大的消耗了资源。但是使用了索引以后，第一索引本身就小一些，第二索引本身就是排好序的，左边数据最小，右边数据最大。

6）B+树图示说明

MySQL中索引使用的就是B+树结构。

关于B+树的说明：

首先，Btree一般指的都是【B+树】，数据全部存放在叶子节点中。对于上图来说，最下面的第3层，属于叶子节点，真实数据部份都是存放在叶子节点当中的。那么对于第1、2层中的数据又是干嘛的呢？答：用于分割指针块儿的，比如说小于26的找P1，介于26-30之间的找P2，大于30的找P3。

其次，三层【B+树】可以存放上百万条数据。这么多数据怎么放的呢？增加“节点数”。图中我们只有三个节点。

最后，【B+树】中查询任意数据的次数，都是n次，n表示的是【B+树】的高度。

3、索引的分类与创建

1）索引分类

• 单值索引

• 唯一索引

• 复合索引

① 单值索引

利用表中的某一个字段创建单值索引。一张表中往往有多个字段，也就是说每一列其实都可以创建一个索引，这个根据我们实际需求来进行创建。还需要注意的一点就是，一张表可以创建多个“单值索引”。

假如某一张表既有age字段，又有name字段，我们可以分别对age、name创建一个单值索引，这样一张表就有了两个单值索引。

② 唯一索引

也是利用表中的某一个字段创建单值索引，与单值索引不同的是：创建唯一索引的字段中的数据，不能有重复值。像age肯定有很多人的年龄相同，像name肯定有些人是重名的，因此都不适合创建“唯一索引”。像编号id、学号sid，对于每个人都不一样，因此可以用于创建唯一索引。

③ 复合索引

多个列共同构成的索引。比如说我们创建这样一个“复合索引”(name,age)，先利用name进行索引查询，当name相同的时候，我们利用age再进行一次筛选。注意：复合索引的字段并不是非要都用完，当我们利用name字段索引出我们想要的结果以后，就不需要再使用age进行再次筛选了。

2）创建索引

① 语法

语法：create 索引类型 索引名 on 表(字段);

建表语句如下：

查询表结构如下：

② 创建索引的第一种方式

创建单值索引

create index dept_index on tb(dept);

创建唯一索引：这里我们假定name字段中的值都是唯一的

create unique index name_index on tb(name);

创建复合索引

create index dept_name_index on tb(dept,name);

③ 创建索引的第二种方式

先删除之前创建的索引以后，再进行这种创建索引方式的测试；

语法：alter table 表名 add 索引类型 索引名(字段)

创建单值索引

alter table tb add index dept_index(dept);

创建唯一索引：这里我们假定name字段中的值都是唯一的

alter table tb add unique index name_index(name);

创建复合索引

alter table tb add index dept_name_index(dept,name);

④ 补充说明

如果某个字段是primary key，那么该字段默认就是主键索引。

主键索引和唯一索引非常相似。相同点：该列中的数据都不能有相同值；不同点：主键索引不能有null值，但是唯一索引可以有null值。

3）索引删除和索引查询

① 索引删除

语法：drop index 索引名 on 表名;

drop index name_index on tb;

② 索引查询

语法：show index from 表名;

show index from tb;

结果如下：

4、SQL性能问题的探索

人为优化：需要我们使用explain分析SQL的执行计划。该执行计划可以模拟SQL优化器执行SQL语句，可以帮助我们了解到自己编写SQL的好坏。

SQL优化器自动优化：最开始讲述MySQL执行原理的时候，我们已经知道MySQL有一个优化器，当你写了一个SQL语句的时候，SQL优化器如果认为你写的SQL语句不够好，就会自动写一个好一些的等价SQL去执行。

SQL优化器自动优化功能【会干扰】我们的人为优化功能。当我们查看了SQL执行计划以后，如果写的不好，我们会去优化自己的SQL。当我们以为自己优化的很好的时候，最终的执行计划，并不是按照我们优化好的SQL语句来执行的，而是有时候将我们优化好的SQL改变了，去执行。

SQL优化是一种概率问题，有时候系统会按照我们优化好的SQL去执行结果(优化器觉得你写的差不多，就不会动你的SQL)。有时候优化器仍然会修改我们优化好的SQL，然后再去执行。

1）查看执行计划

语法：explain + SQL语句

eg：explain select * from tb;

2）“执行计划”中需要知道的几个“关键字”

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

建表语句和插入数据：

# 建表语句
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') ;

explain执行计划常用关键字详解

1）id关键字的使用说明

① 案例：查询课程编号为2 或 教师证编号为3 的老师信息：

# 查看执行计划
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);

结果如下：

接着，在往teacher表中增加几条数据。

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

再次查看执行计划。

# 查看执行计划
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);

结果如下：

表的执行顺序 ，因表数量改变而改变的原因：笛卡尔积。

a   b   c
2   3   4
最终：2 * 3 * 4  = 6 * 4 = 24
c   b   a
4   3   2
最终：4 * 3 * 2 = 12 * 2 = 24

分析：最终执行的条数，虽然是一致的。但是中间过程，有一张临时表是6，一张临时表是12，很明显6 < 12，对于内存来说，数据量越小越好，因此优化器肯定会选择第一种执行顺序。

结论：id值相同，从上往下顺序执行。表的执行顺序因表数量的改变而改变。

② 案例：查询教授SQL课程的老师的描述(desc)

# 查看执行计划
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值不同，id值越大越优先查询。这是由于在进行嵌套子查询时，先查内层，再查外层。

③ 针对②做一个简单的修改

# 查看执行计划
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值有相同，又有不同。id值越大越优先；id值相同，从上往下顺序执行。

2）select_type关键字的使用说明：查询类型

① simple：简单查询

不包含子查询，不包含union查询。

explain select * from teacher;

结果如下：

② primary：包含子查询的主查询(最外层)

③ subquery：包含子查询的主查询(非最外层)

④ derived：衍生查询(用到了临时表)

a.在from子查询中，只有一张表；

b.在from子查询中，如果table1 union table2，则table1就是derived表；

explain select  cr.cname     
from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

结果如下：

explain select tid from test01 where tid =1 ;

create index test01_index on test01(tid) ;
# 再次查看执行计划
explain select tid from test01 where tid =1 ;

delete from teacher where tcid >= 4;
alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid) ;
explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;

# 添加索引
alter table teacher add index index_name (tname) ;
# 查看执行计划
explain select * from teacher     where tname = 'tz';

# 添加索引
alter table teacher add index tid_index (tid) ;
# 查看执行计划：以下写了一种等价SQL写法，查看执行计划
explain select t.* from teacher t where t.tid in (1,2) ;
explain select t.* from teacher t where t.tid <3 ;

# 先给course表的cname字段，添加一个索引
create index cname_index on course(cname);
# 查看执行计划
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') ;

# 创建表
create table test_kl
(
    name char(20) not null default ''
);
# 添加索引
alter table test_kl add index index_name(name) ;
# 查看执行计划
explain select * from test_kl where name ='' ; 

# 删除原来的索引name和name1
drop index index_name on test_kl ;
drop index index_name1 on test_kl ;
# 增加一个复合索引 
create index name_name1_index on test_kl(name,name1);
# 查看执行计划
explain select * from test_kl where name1 = '' ; --121
explain select * from test_kl where name = '' ; --60

# 新增一个字段name2，name2可以为null
alter table test_kl add column name2 varchar(20) ; 
# 给name2字段，设置为索引字段
alter table test_kl add index name2_index(name2) ;
# 查看执行计划
explain select * from test_kl where name2 = '' ;  

# 给course表的tid字段，添加一个索引
create index tid_index on course(tid);
# 查看执行计划
explain select * from course c,teacher t 
where c.tid = t.tid  
and t.tname = 'tw';

explain select * 
from course c,teacher t  
where c.tid = t.tid
and t.tname = 'tz' ;

# 新建一张表，建表同时创建索引
create table test02
(
    a1 char(3),
    a2 char(3),
    a3 char(3),
    index idx_a1(a1),
    index idx_a2(a2),
    index idx_a3(a3)
);
# 查看执行计划
explain select * from test02 where a1 ='' order by a1 ;
explain select * from test02 where a1 ='' order by a2 ; 

# 删除test02的索引
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) ;
# 查看下面SQL语句的执行计划
explain select *from test02 where a1='' order by a3 ;  --using filesort
explain select *from test02 where a2='' order by a3 ; --using filesort
explain select *from test02 where a1='' order by a2 ;

explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;
explain select a1 from test02 where a1 in ('1','2','3') group by a2 ; --using temporary

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

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

explain select * from test03 where a2=2 and a4=4 group by a2,a4;
explain select * from test03 where a2=2 and a4=4 group by a3;

# 删除test02中的复合索引idx_a1_a2_a3
drop index idx_a1_a2_a3 on test02;
# 重新创建一个复合索引idx_a1_a2
create index idx_a1_a2 on test02(a1,a2);
# 查看执行计划
explain select a1,a3 from test02 where a1='' or a3= '' ;
explain select a1,a2 from test02 where a1='' and a2= '' ;

explain select a1,a2 from test02 where a1='' or a2= '' ;
explain select a1,a2 from test02;

# 删除test02中的复合索引idx_a1_a2
drop index idx_a1_a2 on test02;
# 将a1字段，新增为一个索引
create index a1_index on test02(a1);
# 查看执行计划
explain select a1,a3 from test02 where a1="" and a3="" ;

# 查看执行计划
explain select a1 from test02 where a1="a" and a1="b" ;

# 创建新表
create table test03
(
    a1 int(4) not null,
    a2 int(4) not null,
    a3 int(4) not null,
    a4 int(4) not null
);
# 创建一个复合索引
create index a1_a2_a3_test03 on test03(a1,a2,a3);
# 查看执行计划
explain select a3 from test03 where a1=1 and a2=2 and a3=3;

# 查看执行计划
explain select a3 from test03 where a3=1 and a2=2 and a1=3;

# 查看执行计划
explain select a3 from test03 where a1=1 and a3=2 group by a3;

# 创建新表
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) ;    

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

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

# 添加索引
create index typeid_authorid_bid on book(typeid,authorid,bid);
# 再次查看执行计划
explain 
select bid from book 
where typeid in(2,3) and authorid=1  
order by typeid desc ;

# 删除以前的索引
drop index typeid_authorid_bid on book;
# 再次创建索引
create index authorid_typeid_bid on book(authorid,typeid,bid);
# 再次查看执行计划
explain 
select bid from book 
where authorid=1  and typeid in(2,3)  
order by typeid desc ;

explain select bid from book 
where  authorid=1 and typeid =3
order by typeid desc ;

# 创建teacher2新表
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);
# 创建course2新表
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');

explain 
select *
from teacher2 t 
left outer join course2 c
on t.cid=c.cid 
where c.cname='java';

    小表:10
    大表:300
# 小表驱动大表
select ...where 小表.x10=大表.x300 ;
for(int i=0;i<小表.length10;i++)
{
    for(int j=0;j<大表.length300;j++)
    {
        ...
    }
}
# 大表驱动小表
select ...where 大表.x300=小表.x10 ;
for(int i=0;i<大表.length300;i++)
{
    for(int j=0;j<小表.length10;j++)
    {
        ...
    }
}

# 给左表的字段加索引
create index cid_teacher2 on teacher2(cid);
# 查看执行计划
explain 
select *
from teacher2 t 
left outer join course2 c
on t.cid=c.cid 
where c.cname='java';

# 给cname的字段加索引
create index cname_course2 on course2(cname);
# 查看执行计划
explain 
select t.cid,c.cname
from teacher2 t 
left outer join course2 c
on t.cid=c.cid 
where c.cname='java';

explain select * from book where authorid = 1 and typeid = 2;
explain select * from book where authorid*2 = 1 and typeid = 2 ;

# 针对不是复合索引的情况
explain select * from book where authorid != 1 and typeid =2 ;
explain select * from book where authorid != 1 and typeid !=2 ;

# 删除单独的索引
drop index authorid_index on book;
drop index typeid_index on book;
# 创建一个复合索引
alter table book add index idx_book_at (authorid,typeid);
# 查看执行计划
explain select * from book where authorid > 1 and typeid = 2 ;
explain select * from book where authorid = 1 and typeid > 2 ;

# 删除复合索引
drop index authorid_typeid_bid on book;
# 为authorid和typeid，分别创建索引
create index authorid_index on book(authorid);
create index typeid_index on book(typeid);
# 查看执行计划
explain select * from book where authorid = 1 and typeid =2 ;

# 查看执行计划
explain select * from book where authorid = 1 and typeid =2 ;

explain select * from teacher where tname like "%x%" ;
explain select * from teacher  where tname like 'x%';
explain select tname from teacher  where tname like '%x%';

explain select * from teacher where tname = 'abc' ;
explain select * from teacher where tname = 123 ;

explain select * from teacher where tname ='' and tcid >1 ;
explain select * from teacher where tname ='' or tcid >1 ;

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

# 不一定真的是“单路/1次IO”，有可能多次IO
set max_length_for_sort_data = 1024