MySQL 优化二(内部优化器以及 explain 使用)(高级篇)

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的值了

explain实际干了哪些事?explain实际干了哪些事?

本作品采用《CC 协议》,转载必须注明作者和本文链接
胡军
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!