数据库设计_MySQL执行计划入门篇(一)

一、介绍

MySQL执行计划的使用，是继简单查询，高级查询后开始对数据库设计必须掌握的技能。对于初学者主要用来分析自己写的SQL是否存在严重的性能缺陷，而后可以根据需要进行优化。

二、认识(此标题定义一个MySQL使用索引和执行计划的方式)

1、使用索引进行查询(常规方式)

2、使用执行计划进行分析，使用索引进行优化! (本文介绍简单的操作思路，不做过分深入)

三、正常SQL执行的顺序 

注释： 你必须先知道了MySQL正常的执行逻辑是怎么样的，在学会执行计划后才知道怎么调优。尽管他们的执行条件很多，但主要逻辑还是符合正常导出逻辑思考。

1-------第一步是   From条件后，From决定了数据的来源

2-------第二步是  跟随JOIN后面的 ON ，ON的条件是最先执行的

3-------第三步是  JOIN条件，JOIN代表了搜索集的联接，是其他一切搜索的基础

4-------第四步是   WHRE条件，Where存在的意义是对前面获取到的结果进行过滤

5-------第五步是   GROUP BY ，Group By是为了对结果集进行分组

6-------第六步是   WITH条件，With是为了  在group分组字段的基础上再进行统计数据，一般是with  rollup;

7-------第七步是   HAVING条件，一般是对分组结果进行过滤，利用聚合函数进行分组统计。

8-------第八步是   Select操作，select获取需要的列。如果在select里面添加其他对列的操作，需要进一步执行完成操作。

9-------第九步是   Order By，排序。利用某些具备顺序的字段进行排序

10-----第十步是   Limit，做条数限制

================================================================================================

思路：通过查询计划查询到SQL的执行计划状态。然后根据其 ID(执行顺序)、type，key字段进行观察。判断以下几个问题

1、执行的顺序是否是比较合理的。比如说你原来用是打算对A表先执行where条件后，再进行连接B表。但实际上是先执行了联接，再执行了过滤。这样便是不合理的。那么便可以，将A表的查询作为子查询进行处理。查询出来后再以此带动B表进行联接

select *

from (select * from  tb_pm_pa_main m where m.c_stauts=4  and m.del_flag=0)  t   

inner  join  tb_pm_staff s

on  m.c_id  =s.s_id

2、是否添加索引。观察Type字段，执行查询的语句是否有索引，实际使用的索引是否合理。（合理的索引）

================================================================================================

三、执行计划的使用

重点：执行计划说到底只是一个工具，并不是万能的。他只能告诉你一些基础信息，真正做取舍的还是开发者需要根据SQL的情况进行分析。到了这里你需要掌握三个前提：

1、-------------索引不是必须的，不合适的索引会导致查询速度变慢。

2、-------------添加索引可能会改变执行的顺序。

3、-------------我们可以手动调整SQL的组合方式，使用合适的SQL操作字段也很重要

注释：执行计划是告诉你这条SQL的操作顺序如何，通过调整索引，调整SQL的组合顺序，从达到比较合理的地步。好的SQL千奇百怪，但都逻辑清晰。坏的SQL结果虽对，却吃力不讨好。

---

认识：只讲几个重要的，如果想要了解执行计划各个字段的用途，百度一下其他博友的。

---------ID : 标记了SQL的执行顺序，一般ID越大优先级越大，相同的ID则是从上到下按照优先级顺序排列。

---------Type: ALL、Index、Range、Ref、Eq_ref、Const、System、null  。从前到后一般要超过range是比较合理的

---------key:真正使用的索引

例子：

explain SELECT s.* FROM ( SELECT * FROM sc_core sc WHERE sc.c_id = 100 AND sc.score = 100 ) t INNER JOIN Student s ON t.s_id = s.s_id

 解释：当我们看到这个分析的的时候，逻辑应该是这样的：

0、--------我这条SQL的目的是为了先查询出某张表的字段，再用里面的字段作为条件去捞其他表的字段。(常见哦)

1、--------ID字段顺序都为1 ，那么按照正常逻辑，是顺序执行

2、--------Type为ALL，那么是无索引了，考虑加下索引

3、--------Extra说明存在using where 说明字段里面存在着索引条件，看看是啥

4、--------第二条SQL

5、--------Type存在说明存在着索引，根据类型是 组件索引或者唯一索引和其他字段组合的条件

6、--------看到key发现二表的主键索引生效了

7、--------开始分析：

8、--------结合SQL的常规执行顺序和执行计划的顺序分析，我确实是先去查询了一表的数据。然后再跟二表建立了联接，跟我原来想的查询逻辑是一样的。因此SQL的组合逻辑不用变动。

9、--------索引情况如何呢？根据执行计划SQL均为检查查询，一表不存在索引。那么我应该往一表添加索引。

10、------查看下执行时间

11、------查看下执行计划。确认SQL的 执行顺序依旧是最合适的，并且索引的使用是产生了好的效果。

12、------如果出问题，原步退回。再重新分析。

================================================================================================

 四、索引的进化

上面说到索引应该寻找合适的，如果不合适的索引被使用不但会改变SQL的底层逻辑，也会造成搜索时间延长。而在数据库设计中(另外一篇 文章)，当时为了方便理解，我把索引形容为字典的检索页。也是出于考虑到索引是会消耗内存或者磁盘容量的。

无论是唯一索引，组合索引，主键索引等都是存储在内存或者磁盘中，占据一定数据量的。数据一旦多了，查询效率是必然会降低(部分索引在内存大部分在磁盘)。因此在优化SQL中，建立个数比较稀少的索引(专业名词叫区分度比较低)，从一点一滴做起积累SQL的效率是有必要的。

单独一个索引每次查询的个数是上几十万上百万条，但是当多个索引执行并集查询，他的个数可能会成量级的降低。在巨大数据量中，建立联合索引会比普通索引的效率要高效! （百万级别）；