1.8w字的 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 ..

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 ;

结果如下：

⑤ union：union 之后的表称之为 union 表，如上例

⑥ union result：告诉我们，哪些表之间使用了 union 查询

3）type 关键字的使用说明：索引类型

system、const 只是理想状况，实际上只能优化到 index –> range –> ref 这个级别。要对 type 进行优化的前提是，你得创建索引。

① system

源表只有一条数据(实际中，基本不可能)；

衍生表只有一条数据的主查询(偶尔可以达到)。

② const

仅仅能查到一条数据的 SQL ，仅针对 Primary key 或 unique 索引类型有效。

explain select tid from test01 where tid =1 ;

结果如下：

删除以前的主键索引后，此时我们添加一个其他的普通索引：

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

结果如下：

③ eq_ref

唯一性索引，对于每个索引键的查询，返回匹配唯一行数据（有且只有 1 个，不能多 、不能 0），并且查询结果和数据条数必须一致。

此种情况常见于唯一索引和主键索引。

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 ;

结果如下：

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

④ ref

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

准备数据：

创建索引，并查看执行计划：

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

结果如下：

⑤ range

检索指定范围的行 ,where 后面是一个范围查询(between, >, <, >=, in)

in有时候会失效，从而转为无索引时候的 ALL

# 添加索引
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 ;

结果如下：

⑥ index

查询全部索引中的数据(扫描整个索引)

⑦ ALL

查询全部源表中的数据(暴力扫描全表)

注意：cid 是索引字段，因此查询索引字段，只需要扫描索引表即可。但是 tid 不是索引字段，查询非索引字段，需要暴力扫描整个源表，会消耗更多的资源。

4）possible_keys 和 key

possible_keys可能用到的索引。是一种预测，不准。了解一下就好。

key指的是实际使用的索引。

# 先给 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') ;

结果如下：

有一点需要注意的是：如果 possible_key/key 是 NULL，则说明没用索引。

5）key_len

索引的长度，用于判断复合索引是否被完全使用(a,b,c)。

① 新建一张新表，用于测试

# 创建表
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 ='' ;

结果如下：

结果分析：因为我没有设置服务端的字符集，因此默认的字符集使用的是 latin1，对于 latin1 一个字符代表一个字节，因此这列的 key_len 的长度是 20，表示使用了 name 这个索引。

② 给 test_kl 表，新增 name1 列，该列没有设置“not null”

结果如下：

结果分析：如果索引字段可以为 null，则 mysql 底层会使用 1 个字节用于标识。

③ 删除原来的索引 name 和 name1，新增一个复合索引

# 删除原来的索引 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

结果如下：

结果分析：对于下面这个执行计划，可以看到我们只使用了复合索引的第一个索引字段 name，因此 key_len 是 20，这个很清楚。再看上面这个执行计划，我们虽然仅仅在 where 后面使用了复合索引字段中的 name1 字段，但是你要使用复合索引的第 2 个索引字段，会默认使用了复合索引的第 1 个索引字段 name，由于 name1 可以是 null，因此 key_len = 20 + 20 + 1 = 41 呀！

④ 再次怎加一个 name2 字段，并为该字段创建一个索引。

不同的是：该字段数据类型是 varchar。

# 新增一个字段 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 = '' ;

结果如下：

结果分析：key_len = 20 + 1 + 2，这个 20 + 1 我们知道，这个 2 又代表什么呢？原来 varchar 属于可变长度，在 mysql 底层中，用 2 个字节标识可变长度。

6）ref

这里的ref的作用，指明当前表所参照的字段。

注意与type中的ref值区分。在type中，ref只是type类型的一种选项值。

# 给 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';

结果如下：

结果分析：有两个索引，c 表的 c.tid 引用的是 t 表的 tid 字段，因此可以看到显示结果为【数据库名.t.tid】，t 表的 t.name 引用的是一个常量”tw”，因此可以看到结果显示为 const，表示一个常量。

7）rows(这个目前还是有点疑惑)

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

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

结果如下：

8）extra

表示其他的一些说明，也很有用。

① using filesort：针对单索引的情况

当出现了这个词，表示你当前的 SQL 性能消耗较大。表示进行了一次“额外”的排序。常见于 order by 语句中。

Ⅰ 什么是“额外”的排序？

为了讲清楚这个，我们首先要知道什么是排序。我们为了给某一个字段进行排序的时候，首先你得先查询到这个字段，然后在将这个字段进行排序。

紧接着，我们查看如下两个 SQL 语句的执行计划。

# 新建一张表，建表同时创建索引
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 ;

结果如下：

结果分析：对于第一个执行计划，where 后面我们先查询了 a1 字段，然后再利用 a1 做了依次排序，这个很轻松。但是对于第二个执行计划，where 后面我们查询了 a1 字段，然而利用的却是 a2 字段进行排序，此时 myql 底层会进行一次查询，进行“额外”的排序。

总结：对于单索引，如果排序和查找是同一个字段，则不会出现 using filesort；如果排序和查找不是同一个字段，则会出现 using filesort；因此 where 哪些字段，就 order by 哪些些字段。

② using filesort：针对复合索引的情况

不能跨列(官方术语：最佳左前缀)

# 删除 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 ;

结果如下：

结果分析：复合索引的顺序是(a1,a2,a3)，可以看到 a1 在最左边，因此 a1 就叫做“最佳左前缀”，如果要使用后面的索引字段，必须先使用到这个 a1 字段。对于 explain1，where 后面我们使用 a1 字段，但是后面的排序使用了 a3，直接跳过了 a2，属于跨列；对于 explain2，where 后面我们使用了 a2 字段，直接跳过了 a1 字段，也属于跨列；对于 explain3，where 后面我们使用 a1 字段，后面使用的是 a2 字段，因此没有出现【using filesort】。

③ using temporary

当出现了这个词，也表示你当前的 SQL 性能消耗较大。这是由于当前 SQL 用到了临时表。一般出现在 group by 中。

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

结果如下：

结果分析：当你查询哪个字段，就按照那个字段分组，否则就会出现using temporary。

针对using temporary，我们在看一个例子：

using temporary表示需要额外再使用一张表，一般出现在group by语句中。虽然已经有表了，但是不适用，必须再来一张表。

再次来看 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 ..

很显然，where后是group by，然后才是select。基于此，我们再查看如下两个 SQL 语句的执行计划。

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;

分析如下：对于第一个执行计划，where后面是 a2 和 a4，接着我们按照 a2 和 a4 分组，很明显这两张表已经有了，直接在 a2 和 a4 上分组就行了。但是对于第二个执行计划，where后面是 a2 和 a4，接着我们却按照 a3 分组，很明显我们没有 a3 这张表，因此有需要再来一张临时表 a3。因此就会出现using temporary。

④ using index

当你看到这个关键词，恭喜你，表示你的 SQL 性能提升了。

using index称之为“索引覆盖”。

当出现了using index，就表示不用读取源表，而只利用索引获取数据，不需要回源表查询。

只要使用到的列，全部出现在索引中，就是索引覆盖。

# 删除 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= '' ;

结果如下：

结果分析：我们创建的是 a1 和 a2 的复合索引，对于第一个执行计划，我们却出现了 a3，该字段并没有创建索引，因此没有出现using index，而是using where，表示我们需要回表查询。对于第二个执行计划，属于完全的索引覆盖，因此出现了 using index。

针对using index，我们在查看一个案例：

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

结果如下：

如果用到了索引覆盖(using index 时)，会对 possible_keys 和 key 造成影响：

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

b.如果有 where，则索引 出现在 key 和 possible_keys 中。

⑤ using where

表示需要【回表查询】，表示既在索引中进行了查询，又回到了源表进行了查询。

# 删除 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="" ;

结果如下：

结果分析：我们既使用了索引 a1，表示我们使用了索引进行查询。但是又对于 a3 字段，我们并没有使用索引，因此对于 a3 字段，需要回源表查询，这个时候出现了 using where。

⑥ impossible where(了解)

当where子句永远为 False 的时候，会出现impossible where。

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

结果如下：

6、优化示例

1）引入案例

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

结果如下：

结果分析：虽然结果和上述结果一致，但是不推荐这样写。但是这样写怎么又没有问题呢？这是由于 SQL 优化器的功劳，它帮我们调整了顺序。

最后再补充一点：对于复合索引，不要跨列使用。

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

结果如下：

结果分析：a1_a2_a3 是一个复合索引，我们使用 a1 索引后，直接跨列使用了 a3，直接跳过索引 a2，因此索引 a3 失效了，当使用 a3 进行分组的时候，就会出现using where。

2）单表优化

# 创建新表
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，并根据 typeid 降序排列。

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

结果如下：

这是没有进行任何优化的 SQL，可以看到 typ 为 ALL 类型，extra 为using filesort，可以想象这个 SQL 有多恐怖。

优化：添加索引的时候，要根据 MySQL 解析顺序添加索引，又回到了 MySQL 的解析顺序，下面我们再来看看 MySQL 的解析顺序。

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

① 优化 1：基于此，我们进行索引的添加，并再次查看执行计划。

# 添加索引
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 ;

结果如下：

结果分析：结果并不是和我们想象的一样，还是出现了using where，查看索引长度 key_len=8，表示我们只使用了 2 个索引，有一个索引失效了。

② 优化 2：使用了 in 有时候会导致索引失效，基于此有了如下一种优化思路。

将in字段放在最后面。需要注意一点：每次创建新的索引的时候，最好是删除以前的废弃索引，否则有时候会产生干扰(索引之间)。

# 删除以前的索引
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 ;

结果如下：

结果分析：这里虽然没有变化，但是这是一种优化思路。

总结如下：

• 最佳做前缀，保持索引的定义和使用的顺序一致性；
• 索引需要逐步优化(每次创建新索引，根据情况需要删除以前的废弃索引)；
• 将含 In 的范围查询，放到where条件的最后，防止失效。

本例中同时出现了Using where（需要回原表）; Using index（不需要回原表）：原因，where authorid=1 and typeid in(2,3)中 authorid 在索引(authorid,typeid,bid)中，因此不需要回原表（直接在索引表中能查到）；而 typeid 虽然也在索引(authorid,typeid,bid)中，但是含 in 的范围查询已经使该 typeid 索引失效，因此相当于没有 typeid 这个索引，所以需要回原表（using where）；

例如以下没有了 In，则不会出现using where：

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

结果如下：

3）两表优化

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

案例：使用一个左连接，查找教 java 课程的所有信息。

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++)
    {
        ...
    }
}

分析：以上 2 个 FOR 循环，最终都会循环 3000 次；但是对于双层循环来说：一般建议，将数据小的循环，放外层。数据大的循环，放内层。不用管这是为什么，这是编程语言的一个原则，对于双重循环，外层循环少，内存循环大，程序的性能越高。

结论：当编写【…on t.cid=c.cid】时，将数据量小的表放左边（假设此时 t 表数据量小，c 表数据量大。）

我们已经知道了，对于两表连接，需要利用小表驱动大表，例如【…on t.cid=c.cid】，t 如果是小表(10 条)，c 如果是大表(300 条)，那么 t 每循环 1 次，就需要循环 300 次，即 t 表的 t.cid 字段属于，经常使用的字段，因此需要给 cid 字段添加索引。

更深入的说明：一般情况下，左连接给左表加索引。右连接给右表加索引。其他表需不需要加索引，我们逐步尝试。

# 给左表的字段加索引
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 添加一个索引。索引优化是一个逐步的过程，需要一点点尝试。

# 给 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';

结果如下：

最后补充一个：Using join buffer是 extra 中的一个选项，表示 Mysql 引擎使用了“连接缓存”，即 MySQL 底层动了你的 SQL，你写的太差了。

4）三表优化

• 大于等于张表，优化原则一样；
• 小表驱动大表；
• 索引建立在经常查询的字段上。

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

① 复合索引需要注意的点

• 复合索引，不要跨列或无序使用(最佳左前缀)；
• 复合索引，尽量使用全索引匹配，也就是说，你建立几个索引，就使用几个索引。

② 不要在索引上进行任何操作(计算、函数、类型转换)，否则索引失效

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

结果如下：

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

# 针对不是复合索引的情况
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 ;

结果如下：

结论：复合索引中如果有【>】，则自身和右侧索引全部失效。

在看看复合索引中有【<】的情况：

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

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

# 删除复合索引
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 ;

结果如下：

结果分析：我们创建了两个索引，但是实际上只使用了一个索引。因为对于两个单独的索引，程序觉得只用一个索引就够了，不需要使用两个。

当我们创建一个复合索引，再次执行上面的 SQL：

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

结果如下：

⑤ 索引覆盖，百分之百没问题

⑥ like 尽量以“常量”开头，不要以’%’开头，否则索引失效

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%';

结果如下：

结论如下：like尽量不要使用类似”%x%”情况，但是可以使用”x%”情况。如果非使用 “%x%”情况，需要使用索引覆盖。

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

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

结果如下：

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

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

结果如下：

注意：or 很猛，会让自身索引和左右两侧的索引都失效。

8、一些其他的优化方法

1）exists 和 in 的优化

如果主查询的数据集大，则使用i关键字，效率高。

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

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

2）order by 优化

• IO 就是访问硬盘文件的次数
• using filesort 有两种算法：双路排序、单路排序（根据 IO 的次数）
• MySQL4.1 之前默认使用双路排序；双路：扫描 2 次磁盘(1：从磁盘读取排序字段,对排序字段进行排序(在 buffer 中进行的排序)2：扫描其他字段)
• MySQL4.1 之后默认使用单路排序：只读取一次（全部字段），在 buffer 中进行排序。但种单路排序会有一定的隐患(不一定真的是“单路/1 次 IO”，有可能多次 IO)。原因：如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行“分片读取、多次读取”。
• 注意：单路排序 比双路排序 会占用更多的 buffer。
• 单路排序在使用时，如果数据大，可以考虑调大 buffer 的容量大小：

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

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

① 提高 order by 查询的策略：

• 选择使用单路、双路 ；调整 buffer 的容量大小
• 避免使用select * …（select后面写所有字段，也比写*效率高）
• 复合索引，不要跨列使用 ，避免using filesort保证全部的排序字段，排序的一致性（都是升序或降序）

篇幅很长，内容较多，建议收藏。

推荐阅读：15 个实用的 Sql 优化小技巧

来源：数据分析不是个事儿