mysql之explain 分析sql语句

EXPLAIN SELECT * FROM  表名

rows (估算) =>这个是MYSQL在改优化器的执行方案中扫描的大约数据量与实际的数据有多少
在实际项目中,需要做一个某个统计操作的时候需要的数据不一定正确,这个时候就可以使用explain来获取 

select_type:查询类型不是核心
                  SIMPLE:除了子查询或者union之外的查询显示
partitions:参数的使用，SQL优化本质=》减少=》减少数据扫描的范围
type:类型->当前SQL扫描的类型
       all: 全表扫描效率是最低的
       index:全索引扫描->就是根据索引去获取结果
       const 读常量->主键和唯一字段(主键告诉数据库数据存储的位置)
       rang: 索引的范围查询
       eq_ref:最多只会有一条结果主要是也是唯一索引
possible key:代表这条SQL语句可能使用到的索引是哪些
key:来决定最终会使用的索引
key len: 索引的大小
extra:这里描述的细节信息
        null: 如果key中索引标识那么这里是一个问题"回表"->使用了索引,但是效率不一定很高回表问题在索引去解释
        using index覆盖索引->就是直接通过索引获取到了数据(效率是最高)
        using where根据条件过,虽然可能使用了索引,但是还是会根据where条件对数据进行过滤
        using temporary:意味着产生了一个临时表
        using filesort:会对于数据在索引外进行了排序操作

回表：

customers 表 id是主键,a，b字段

SELECT * FROM customers WHERE id=1

这样不会产生回表，因为所有的数据在id的索引树中均能找到

SELECT * FROM customers WHERE a=1;

这样就会产生回表，因为where条件是a字段，那么会去a的索引树里查找数据，但a的索引里面只有a,id两个字段的值，没有b，那么这个查询为了取到b字段，就要取出主键id的值，然后去id的索引树去找b字段的数据。查了两个索引树，这就叫回表。

避免回表，可以建立a和b的联合索引，不用*号，只查询用上的字段

覆盖索引:

覆盖索引就是查这个索引能查到你所需要的所有数据，不需要去另外的数据结构去查。

 

建立索引 

alter table 表名 add index 索引名称 (字段名);

alter table customers add index city_monthsalary(city);

查看表的索引

show indexes from 表名;
show indexes from customers;

删除索引

alter table 表名 drop index 索引名称
alter table salary drop index city_monthsalary

查询女性客户数量和平均月薪

未建索引前所需的时间

select count(*),avg(monthsalary) from customers1 where gender = 0;

 

[SQL]select count(*),avg(monthsalary) from customers1 where gender = 0;
受影响的行: 0
时间: 12.022s

建立联合索引后，再用explain来看看是否用到联合索引

alter table customers1 add index idex_gender_monthsalary(gender,monthsalary);

explain select count(*),avg(monthsalary) from customers1 where gender = 0;

 

 用上了，再查询一次看看几秒，哇哦，差不多快了12s

建立索引要记住最左匹配原则，就是索引会先匹配最左边的字段，上面的语句gender是最左的，索引建立索引时，gender放在monthsalary前面

___________________________________________________________________

profiling分析

mysql的profiler是一个使用非常方便的查询诊断分析工具，通过该工具可以获取一条查询在 整个执行过程中多种资源的消耗情况，例如内存消耗、I/O消耗和CPU消耗等。

profile的语法规则如下：

show profile [type [, type] ...]
[for query n]
[limit row_count [offset offset]]



其中type参数的可选项含义如下：
all：显示所有的信息
block io：限时输入输出操作阻塞的数量
context switches：显示自动或非自动context switches的数量
cpu：显示系统和用户cpu使用的时间
ipc：显示信息发送和接受的数量
memory：内存的信息
page faults：显示主要的page faults数量
source：显示函数的名称，并且系那是函数所在文件的名字和行数
swaps：显示swap数量

 

开启profiling 

set profiling =1;
show profiling;

在开启了query profiler功能之后，mysql就会自动记录所有执行的query的profile信息。然后执行sql

SELECT * FROM customers1;

通过执行 show profiles 命令获取当前系统中保存的多个query的profile的概要信息。

show PROFILES;

找到刚刚执行的sql，记录下query_id

针对单个query获取详细的profile的信息 在获取概要信息之后就可以根据概要信息中的query_id来获取某一个query在执行过程中的profile信息了

show PROFILE cpu,block io for query 387;