【知识库】-数据库_MySQL性能分析之Query Optimizer

 

 

　　简书作者：Sio

　　文章出处: MySql优化之索引原理与 SQL 优化

 

 

Query Optimizer

　　MySQL Optimizer是一个专门负责优化SELECT 语句的优化器模块，它主要的功能就是通过计算分析系统中收 集的各种统计信息，为客户端请求的Query 给出他认为最优的执行计划，也就是他认为最优的数据检索方式。

 

MySQL常见瓶颈

  1. CPU饱和:CPU饱和的时候,一般发生在数据装入内存或从磁盘上读取数据的时候
  2. IO瓶颈: 磁盘IO瓶颈发生在装入数据远大于内存容量的时候
  3. 服务器硬件的性能瓶颈

 

执行计划Explai

　　Explain概述

　　使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理SQL语句的.我们可以用执行 计划来分析查询语句或者表结构的性能瓶颈

 

Explain作用

  1. 查看表的读取顺序
  2. 查看数据库读取操作的操作类型
  3. 查看哪些索引有可能被用到
  4. 查看哪些索引真正被用到
  5. 查看表之间的引用
  6. 查看表中有多少行记录被优化器查询

 

4.3.3语法

  • 语法
explain sql语句

 

　　各字段解释

  • 准备工作

 

create table t1(
id int primary key, 
name varchar(20), 
col1 varchar(20), 
col2 varchar(20), 
col3 varchar(20) 
);
create table t2( 
id int primary key,
name varchar(20), 
col1 varchar(20), 
col2 varchar(20), 
col3 varchar(20) 
);
create table t3( 
id int primary key,
name varchar(20),
col1 varchar(20),
col2 varchar(20), 
col3 varchar(20) 
);
insert into t1 values(1,'zs1','col1','col2','col3'); 
insert into t2 values(1,'zs2','col2','col2','col3'); 
insert into t3 values(1,'zs3','col3','col2','col3'); 
create index ind_t1_c1 on t1(col1);
create index ind_t2_c1 on t2(col1); 
create index ind_t3_c1 on t3(col1);
create index ind_t1_c12 on t1(col1,col2);
create index ind_t2_c12 on t2(col1,col2); 
create index ind_t3_c12 on t3(col1,col2);
View Code

 

　　执行explain sql语句后

　　

 

id

　　select 查询的序列号,包含一组数字,表示查询中执行Select子句或操作表的顺序

　　三种情况:

　　一、id值相同,执行顺序由上而下

explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id= t3.id and t1.name = 'zs';

 

 

 　　二、id值不同,id值越大优先级越高,越先被执行

explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.name='zs3'));

 

 

　　三、id值有相同的也有不同的,如果id相同,从上往下执行,id值越大,优先级越高,越先执行

 

 

select_type

　　查询类型,主要用于区别

  • SIMPLE : 简单的select查询,查询中不包含子查询或者UNION
  • PRIMARY: 查询中若包含复杂的子查询,最外层的查询则标记为PRIMARY
  • SUBQUERY : 在SELECT或者WHERE列表中包含子查询
  • DERIVED : 在from列表中包含子查询被标记为DRIVED衍生,MYSQL会递归执行这些子查询,把结果放到临时表 中
  • UNION: 若第二个SELECT出现在union之后,则被标记为UNION, 若union包含在from子句的子查询中,外层 select被标记为:derived
  • UNION RESULT: 从union表获取结果的select

 

 

table

　　显示这一行的数据是和哪张表相关

 

type

　　访问类型: all, index,range,ref,eq_ref, const,system,null
　　最好到最差依次是: system > const > eq_ref>ref >range > index > all , 最好能优化到range级别或则ref级别

  • system: 表中只有一行记录(系统表), 这是const类型的特例, 基本上不会出现
  • const: 通过索引一次查询就找到了,const用于比较primary key或者unique索引,因为只匹配一行数据,所以很 快,如将主键置于where列表中,mysql就会将该查询转换为一个常量
explain select * from (select * from t1 where id=1) s1;
  • eq_ref: 唯一性索引扫描, 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或者唯一索引扫描
explain select * from t1,t2 where t1.id = t2.id;
  • ref : 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有符合条件的行,然而 它可能返回多个符合条件的行
explain select * from t1 where col1='zs1';
  • range : 只检索给定范围的行, 使用一个索引来选择行.key列显示的是真正使用了哪个索引,一般就是在where条 件中使用between,>,<,in 等范围的条件,这种在索引范围内的扫描比全表扫描要好,因为它只在某个范围中扫描, 不需要扫描全部的索引
explain select * from t1 where id between 1 and 10;
  • index : 扫描整个索引表, index 和all的区别为index类型只遍历索引树. 这通常比all快,因为索引文件通常比数据 文件小,虽然index和all都是读全表,但是index是从索引中读取,而all是从硬盘中读取数据
explain select id from t1;
  • all : full table scan全表扫描 ,将遍历全表以找到匹配的行
explain select * from t1;
  • 注意: 开发中,我们得保证查询至少达到range级别,最好能达到ref. 如果百万条数据出现all, 一般情况下就需要考虑使用索引优化了

 

possible_keys

　　SQL查询中可能用到的索引,但查询的过程中不一定真正使用

 

key

　　查询过程中真正使用的索引,如果为null,则表示没有使用索引
　　查询中使用了覆盖索引,则该索引仅出现在key列表中
explain select t2.* from t1,t2,t3 where t1.col1 = ' ' and t1.id = t2.id and t1.id= t3.id;

 

　　

 

explain select col1 from t1;

 

　　

 

key_len

　　索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确度的情况下,长度越短越好, key_len显 示的值为索引字段的最大可能长度,并非实际使用长度, 即key_len是根据表定义计算而得

explain select * from t1 where col1='c1'

 

 

explain select * from t1 where col1='col1' and col2 = 'col2';
‐‐ 注意: 为了演示这个结果,我们删除了c1上面的索引 
alter table t1 drop index ind_t1_c1; 
‐‐ 执行完成之后,再次创建索引 
create index ind_t1_c1 on t1(col1);

 

 

ref

　　显示索引的哪一列被使用了,如果可能的话,是一个常数.哪些列或者常量被用于查找索引列上的值

 

rows

　　根据表统计信息及索引选用的情况,估算找出所需记录要读取的行数 (有多少行记录被优化器读取) ,越少越好
 

extra

　　包含其它一些非常重要的额外信息

  • Using filesort : 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,Mysql中无 法利用索引完成的排序操作称为文件排序
explain select col1 from t1 where col1='col1' order by col3;

　　

 

‐‐ 上面这条SQL语句出现了using filesort,但是我们去执行下面这条SQL语句的时候它,又不会出现using filesort
explain select col1 from t1 where col1='col1' order by col2;

 

 

‐‐ 如何优化第一条SQL语句 ? 
create index ind_t1_c13 on t1(col1,col3);
explain select col1 from t1 where col1='col1' order by col3;

 

 

  • Using temporary : 使用了临时表保存中间结果,Mysql在对查询结果排序时使用了临时表,常见于order by 和分 组查询group by
explain select col1 from t1 where col1>'col1' group by col2;

 

 

explain select col1 from t1 where col1 >'col1' group by col1,col2;

 

  • Using index :
  • 查询操作中使用了覆盖索引(查询的列和索引列一致),避免访问了表的数据行,效率好
  • 如果同时出现了using where, 表明索引被用来执行索引键值的查找
  • 如果没有同时出现using where, 表明索引用来读取数据而非执行查找动作
  • 覆盖索引: 查询的列和索引列一致, 换句话说查询的列要被所键的索引覆盖,就是select中数据列只需从索引中就 能读取,不必读取原来的数据行,MySql可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件
explain select col2 from t1 where col1='col1';

 

 

explain select col2 from t1;

 

  • using where : 表明使用了where条件过滤
  • using join buffer : 表明使用了连接缓存, join次数太多了可能会出现
  • impossible where : where子句中的值总是false,不能用来获取任何数据
explain select * from t1 where col1='zs' and col1='ls';

 

  • select tables optimized away :
  • 在没有group by 子句的情况下, 基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作,不必 等到执行阶段再进行计算,查询执行计划生成阶段即完成优化
  • distinct : 优化distinct操作,在找到第一个匹配的数据后即停止查找同样的值的动作

 

 



 

 


 



 

 

 

 

 

 

posted @ 2019-07-24 17:54  Cynical丶Gary  阅读(410)  评论(0)  编辑  收藏  举报