2.5W字，图文详解 "MySQL优化" 问题！

目录

  1、MySQL的基本架构
   1）MySQL的基础架构图
   2）查询数据库的引擎
   3）指定数据库对象的存储引擎
  2、SQL优化
   1）为什么需要进行SQL优化？
   2）mysql的编写过程和解析过程
   3）SQL优化—主要就是优化索引
   4）索引的弊端
   5）索引的优势
   6）B+树图示说明
  3、索引的分类与创建
   1）索引分类
   2）创建索引的两种方式
   3）索引删除和索引查询
  4、SQL性能问题的探索
   1）查看执行计划
   2）“执行计划”中需要知道的几个“关键字”
  5、explain执行计划常用关键字详解
   1）id关键字的使用说明
   2）select_type关键字的使用说明：查询类型
   3）type关键字的使用说明：索引类型
   4）possible_keys和key
   5）key_len
   6）ref
   7）rows
   8）extra
  6、优化示例
   1）引入案例
   2）单表优化
   3）两表优化
   4）三表优化
  7、避免索引失效的一些原则
  8、一些其他的优化方法
   1）exists和in的优化
   2）order by优化

1、MySQL的基本架构

1）MySQL的基础架构图

  左边的client可以看成是客户端，客户端有很多，像我们经常你使用的CMD黑窗口，像我们经常用于学习的WorkBench，像企业经常使用的Navicat工具，它们都是一个客户端。右边的这一大堆都可以看成是Server(MySQL的服务端)，我们将Server在细分为sql层和存储引擎层。
  首先，需要建立客户端与服务器之间的连接。这里通过一个【连接器】，我们建立的客户端与服务器之间的连接，此时，你在客户端写的SQL语句，就可以发送到MySQL的服务了。（接下来要做的事儿肯定不是立即去分析呀！查询底层的数据呀！这样的话效率就太低下了。）
  接着，这个SQL语句将会被交到这个【查询缓存】中，如果可以查到，就直接响应回来给你；如果在查询缓存中没有查到，就需要接着往下走。
  然后，这个SQL语句将会被交到这个【分析器】中，这个分析器用于词法分析、语法分析，检查你写的SQL语句有没有单词拼写错误，语法书写错误。如果都没有错误，就需要接着往下走。
  再接着，这个SQL语句将会被交到这个【优化器】中，优化器如果觉得你的SQL写的太差了，它会帮你写一个性能高一些的等价SQL，去执行。如果优化器觉得你的SQL写的还行，就不会动你的SQL语句。这个优化器与我们下面需要讲解的“索引”有着千丝万缕的关系。
  再然后，优化器将最终确定好的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 table tb(
id int(4) auto_increment,
name varchar(5),
dept varchar(5),
primary key(id)
) engine=myISAM auto_increment=1 default charset=utf8;


• 查询表结构如下：

② 创建索引的第一种方式

Ⅰ 创建单值索引

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

insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;


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