MySQL 优化二（内部优化器以及 explain 使用）（高级篇）

huxiaobai_001 的个人博客 / 142 / 0 / 创建于 4年前 / 更新于 4年前

mysql性能优化

mysql的内部优化器介绍
mysql中有专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的query提供他认为最优的执行计划(他认为最优的数据检索方式但不见得是DBA认为最优的，所以这部分最浪费时间)
我们通过某种方式获取得知mysql低层是如何自动优化我们的sql的那么就更加便于我们对自己的sql语句进行优化

mysql常见的瓶颈在哪里？

explain
使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈
explain + sql语句
执行计划包含的信息：

explain当中的ID
select查询的序列号包含一组数字表示查询中执行select子句或操作表的顺序；
第一种情况：id相同执行顺序由上而下
sql案例：
explain select t2.* from t1,t2,t3 where t1.id=t2.id and t1.id =t3.id and t1.other_column=’’;
执行结果：

我们看到id都为1 table一栏表示操作表的执行顺序先操作哪个再操作哪个!
总结：id相同的时候执行顺序是由上而下的
比如:我们分析上边的sql语句发现id都相同那么我们就去看table 执行顺序就是从上到下执行的 t1 t3 t2的顺序
第二种情况：id不同，如果是子查询，id的序号会递增，id值越大优先级越高越先被执行
sql案例：
explain select t2.* from t2 where id = (
select id from t1 where id = (
select t3.id from t3 where t3.other_column = ‘’
)
);
执行结果：

我们看到id的编号是不同的完全不同! table一栏也是表示的执行顺序就是先操作哪个再操作哪个表的意思
总结：id完全不同的时候如果是子查询 id的序号会递增 id越大优先级越高越先被执行（一般会先执行括号里面的一层括号一层括号的往外执行）
比如：我们通过分析上边的sql, id都不相同那么我们就去看table栏 id越大的执行优先级越高顺序也就是t3 t1 t2
第三种情况：id相同又不同的情况（就是有几个是相同的其他的又不同）
sql案例：
explain select t2.* from (
select t3.id from t3 where t3.other_column = ‘’)s1,t2 where s1.id = t2.id;
注意：from后边是一张虚拟表
执行结果：

我们发现id有相同的也有不同的这种情况下如何分析呢我们可以把id相同的看成一组其他的相同的都各自为一组在所有的组当中，id值越大优先级越高越先执行所以这里id=2的是最大的也就是t3表先被执行然后在看相同的为一组 id = 1的这一组那么执行顺序就是从上往下的所以再执行最后执行的是t2!
我们可以能会好奇怎么会有表呢？derived是衍生的意思也就是sql当中的s1 s1是由id=2的对应的table也就是t3衍生出来的!看sql就能看出来啊！
总结：我们发现id有相同的有不同的 id相同的我们可以理解成一组是从上往下执行的 id不同的是单独一组在所有的组当中，id值越大优先级越高越先执行

explain当中的select_type含义介绍：

explain当中的table的含义介绍：
显示这一行的数据是关于哪张表的

explain当中的type的含义介绍：
如果没有创建索引那么通过explain查看到的type都是all全表扫描所以对于大数据量查询的话 where后边的最好加上索引我是说大数据量上百万千万级别甚至上亿

explain当中type各个参数的解释：
type显示的是访问类型是较为重要的一个指标结果的值从最好到最坏依次是：
system > const > eq_erf > ref > range > index > all
一般情况下得保证查询至少能达到range级别最好能达到ref级别
system级别:
表只有一行记录（等于系统表），这是const类型的特例平时不会出现这个也可以忽略不计（注意：实际开发当中没有哪个复杂的逻辑和架构是单行单列的不用考虑这个了）
const级别：
表示通过索引一次就找到了，const用于比较primary_key或者unique索引。因为只匹配一行数据所以很快。如将主键配置于where列表当中了，mysql就能将该查询转换成一个常量
（注意：实际开发当中查询一行没必要进行优化所以我们对它的关注度也不用太高！）

eq_ref级别:
唯一性索引扫描对于每个索引键表中只有一条记录与之匹配常见于主键或者唯一索引扫描；比如给身份账号设置了唯一索引那么我们在where条件后边加上身份证号=‘’那么就会出现eq_ref级别
（注意：用到了索引并且是唯一索引或者主键）
ref级别:
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体!
比如我们要查找java部门的员工我们要查找的是员工员工有可能重名所以不能建立唯一索引可能我们会建立个普通的单值索引那么查找出来的记录会有很多因为一个部门下会有很多员工啊!那么这条sql语句的级别就是在ref级别!
(注意：没有用到唯一性索引但是可能会用到普通单值索引或者符合索引)
range级别:
只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引一般就是在你的where语句中出现了between < > in等的查询这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点而结束语另外一点不用扫描全部索引；
index级别:
full index scan index于all区别为index类型只遍历索引树这通常比all快因为索引文件通常比数据文件小也就是说虽然all和index都是度全表但是index是从索引中读取而all是从硬盘中读取的

all级别:
full table scan 将遍历全表以找到匹配的行如果数据在百万级别或者千万级别的发现存在all级别那么我们就有必要采取sql的优化措施了!

explain当中possible_keys 和 key的含义介绍：
possible_keys:表示sql内部分析出来的该条sql应该会被用到的索引
key:表示实际的sql执行过程当中被用到的索引是哪一个我们可以通过判断key的值是否是null来判断索引是否失效没有被用上
会有三种情况发生：
1.我们创建了索引并且被用上了 possible_keys有值另外key当中也有值
2.我们没有为查询的字段创建索引 possible_keys为null但是key当中有值这就是覆盖索引
什么叫做覆盖索引：

如果要使用覆盖索引那么必须保证select列表中只取出需要的列不可以select * 并且字段的个数也要和要取的索引列字段个数相同
3.我们创建了索引但是没用上 possible_key不为空但是key为空了也就是索引失效了!

explain当中的ref的含义介绍：
显示索引的哪一列被使用了如果可能的话是一个常量其实也就是说哪些列或者常量被用于查找索引上的值；

ref有值的前提是type = ref 表示非唯一性索引扫描返回匹配某个单独值的所有行！ ref展示出来的就是哪些列或者常量被用于查找索引上的值；比如这里shared.t2.col1 表示shared库里面的t2表里的col1字段被用户查找t1表里面col1索引上的值 ac常量也是用于查找t1表里面的col2索引列上对应的值；比较绕是吧刚开始我也晕了!ref有值的前提是type = ref 表示非唯一性索引扫描返回匹配某个单独值的所有行！ ref展示出来的就是哪些列或者常量被用于查找索引上的值；比如这里shared.t2.col1 表示shared库里面的t2表里的col1字段被用户查找t1表里面col1索引上的值 ac常量也是用于查找t1表里面的col2索引列上对应的值；比较绕是吧刚开始我也晕了!

explain当中rows的含义介绍：
表示每张表有多少行被优化器查询当然这个值是越小越好啦!
比如下边的案例：
刚开始我们没有创建索引 t2.col1=’ac’ ac是常量 sql优化器查询的条数是640行但是当我们为t2.col1和col2创建完符合索引的时候那么type类型就变成了ref非唯一性索引扫描大大缩小了范围并且rows的行数从之前的640行缩小到了142行；

explain当中Extra的含义介绍：
这个是比较复杂的东西表示的是包含不适合在其他列显示但十分重要的信息

using filesort（危险程度:九死一生）：
说明mysql会对数据使用一个外部的索引排序而不是按照表内的索说明mysql会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取这就比较消耗IO资源 mysql中无法利用索引完成的排序操作称为”文件排序” 出现using filesort就表示无法通过索引进行排序就比较耗费资源啦最好不要出现这个using filesort 当然数据量少是不会有任何的影响的;
举个例子：
我们并没有给create_time字段创建索引如果我们按照create_time进行排序也会出现using filesort的情况因为它无法通过索引进行排序就会出现所以对于大数据量的排序最好给order by后边的字段创建索引

再举个案例：
比如我们给login字段创建了索引并且我们搜索的是login字段区间内的 30-40之间的这就是range类型我们重点看extra 里面并没有usging filesort 是因为我们是按照login进行的排序我们是给login字段创建了索引的所以就不会存在usging file sort的情况产生这是最完美的！如果我们按照create_time排序因为没有为ccreate_time字段创建索引所以还是会产生using filesort情况的发生所以对于大数据量的排序最好给order by后边的字段创建索引

总结： order by后边的字段排序必须和where后边的子句当中的字段一样并且为该字段创建了索引才不会出现using file sort的情况产生
order by 后边的字段是login_num in （1,2,3） order by username 也就是说前后字段不一致的情况的时候为了避免using filesort情况的发生我们可以创建复合索引 login_num username一起创建复合索引然后sql语句这么写 login_num in (1,2,3) order by login_num,username 就不会再出现usging filesort情况的产生
order by 前边子句当中的字段和order by 后边的子句不一样的时候就会产生临时表查询起来就比较慢所以尽量保持前后字段一致如果非得不一致那就创建复合索引并且要保证order by 后边的字段和复合索引创建时候的字段的顺序一致!

using temporary（危险程度:九死零生）：
说明：使用了临时表保存中间结果说明：使用了临时表保存中间结果 mysql在对查询结果排序的时候使用临时表常见于order by 和分组查询 group by
案例1：前提是我们为create_time创建了索引然后group by前边的字段和group by后边的字段相同那么就不会出现using temporary的情况的发生但是如果我们没有创建索引那么就会出现using temprary临时表的产生那就不大好啦

案例2：前提也是我给create_time创建了索引然后group by前边的和后边的字段可以不相同也不会出现using temporary的情况产生和order by还是有区别的

总结：
group by 前边的字段和group by后边的字段最好相同但是必须后边的字段要创建索引才会不会有using temprary的情况发生
group by 前边的字段和group by后边的字段不相同但是你为后边的字段创建了索引也不会发生using temprary的情况发生
order by 后边的字段是login_num in （1,2,3） group by username 也就是说前后字段不一致的情况的时候为了避免using temporary情况的发生我们可以创建复合索引 login_num username一起创建复合索引然后sql语句这么写 login_num in (1,2,3) group by login_num,username 就不会再出现usging filesort情况的产生
group by 前边的字段和后边的字段不一样的时候并且后边字段没有创建索引的情况下是会产生临时表的也就是我们说的using temporary情况的发生总结当中说的就可以完全避免!
注意：using temporary的发生是很危险的事情效率会非常的卡非常的慢!

using index(棒极啦)：using index(棒极啦)：
直接从索引当中获取了数据而不用再去数据文件当中查找没有比这样更省事更快捷的了!
其他的不用看了哈!其他的不用看了哈!主要的就上邊三種情況!
using where：
表明使用了where过滤
using join buffer：
表示使用了连接缓存这个时候就要去调大join buffer的值了