当前位置:   article > 正文

explain分析sql语句性能详解_explain sql

explain sql

一、概念及作用

使用explain关键字,可以模拟优化器执行的SQL语句,从而知道MYSQL是如何处理sql语句的。通过Explain可以分析查询语句或表结构的性能瓶颈
具体作用:

  • 查看表的读取顺序

  • 数据读取操作的操作类型

  • 查看哪些索引可以使用

  • 查看哪些索引被实际使用

  • 查看表之间的引用

  • 查看每张表有多少行被优化器执行

二、使用方法

使用Explain关键字 放到sql语句前

explain select cus_id from testemployee where cus_id > 10
  • 1

在这里插入图片描述

三、 参数详解

3.1 id(重要)

select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序 值分为三种情况
id值相同

执行顺序由上到下
  • 1

id不同
如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行

id相同、不同,同时存在

可以认为是一组,从上往下顺序执行
在所有组中,id值越大,优先级越高,越先执行	
  • 1
  • 2
-- id值相同
EXPLAIN SELECT * from employee e,department d,customer c where e.dep_id = d.id and e.cus_id = c.id;
  • 1
  • 2

在这里插入图片描述

-- id值不同	
EXPLAIN SELECT * from department WHERE id = (SELECT id from employee WHERE id=(SELECT id from customer WHERE id = 1))
  • 1
  • 2

在这里插入图片描述

-- id值相同 不同都存在 deriverd 衍生出来的虚表
EXPLAIN select * from department d, (select * from employee group by dep_id) t where d.id = t.dep_id;
  • 1
  • 2

在这里插入图片描述

3.2 select_type

查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询结果值
	SIMPLE
			简单select查询,查询中不包含子查询或者UNION
	PRIMARY
			查询中若包含任何复杂的子查询,最外层查询则被标记为primary
	SUBQUERY
			在select或where中包含了子查询
	DERIVED
			在from列表中包含的子查询被标记为derived(衍生)把结果放在临时表当		
	UNION
			若第二个select出现的union之后,则被标记为union
			若union包含在from子句的子查询中,外层select将被标记为deriver
	UNION RESULT
			从union表获取结果select,两个UNION合并的结果集在最后
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
-- union 和 union result 示例
EXPLAIN select * from employee e LEFT JOIN department d on e.dep_id = d.id
UNION 
select * from employee e RIGHT JOIN department D ON e.dep_id = D.id
  • 1
  • 2
  • 3
  • 4

在这里插入图片描述

3.3 table

显示这一行的数据是关于哪张表的
  • 1

3.4 partitions

如果查询是基于分区表的话, 会显示查询访问的分区
  • 1

3.5 type (重要)

 访问类型排列	
结果值:(最好到最差) system > const > eq_ref > ref > range > index > ALL
  • 1
  • 2
3.5.1 system
-- system 表中有一行记录(系统表)  这是const类型的特例,平时不会出现
explain select HOST from mysql.db where HOST='localhost'
  • 1
  • 2

在这里插入图片描述

3.5.2 const 表示通过索引一次就找到了
-- const  表示通过索引一次就找到了,const用于比较primary 或者 unique索引.   直接查询主键或者唯一索引,因为只匹配一行数据,所以很快
EXPLAIN select id from employee where id=1
  • 1
  • 2

在这里插入图片描述

3.5.3 eq_ref 唯一性索引扫描

eq_ref 唯一性索引扫描 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或唯一索引扫描

EXPLAIN select * from employee e,department d where e.id=d.id
  • 1

在这里插入图片描述

3.5.4 ref 非唯一性索引扫描

出现在一对多关系中,例如部门和员工
在这里插入图片描述
ref 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体

EXPLAIN select e.id,e.dep_id,d.id from employee e,department d where e.dep_id = d.id
  • 1

在这里插入图片描述
添加dep_id索引

CREATE index emp_dep_id on  employee(dep_id)
  • 1

执行结果
在这里插入图片描述

3.5.5 range

range 只检索给定范围的行,使用一个索引来选择行 一般就是在你的where语句中出现between<>\ in等查询,这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点.而结束语另一点,不用扫描全部索引

explain select * from employee where id>2
  • 1

在这里插入图片描述

3.5.6 index

indexAll区别为index类型只遍历索引树,通常比All要快,
因为索引文件通常比数据文件要小all和index都是读全表,
但index是从索引中读取,all是从硬盘当中读取

explain select id from employee
  • 1

在这里插入图片描述

3.5.7 ALL

ALL 将全表进行扫描,从硬盘当中读取数据,如果出现了All 切数据量非常大, 一定要去做优化

explain select * from employee
  • 1

在这里插入图片描述

要求:
	一般来说,保证查询至少达到range级别
	最好能达到ref
  • 1
  • 2
  • 3

3.6 possible_keys

显示可能应用在这张表中的索引,一个或者多个
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
可能自己创建了4个索引,在执行的时候,可能根据内部的自动判断,只使用了3个
  • 1
  • 2
  • 3
--  可能不会用到索引,实际用到索引
explain select dep_id from employee
  • 1
  • 2

在这里插入图片描述

-- 可能会使用索引,实际没用到索引
EXPLAIN select * from employee e,department d where e.dep_id = d.id
  • 1
  • 2

在这里插入图片描述

3.7 key (重要)

实际使用的索引,如果为NULL,则没有使用索引,查询中若使用了覆盖索引 ,
则该索引仅出现在key列表possible_keys与key关系,理论应该用到
哪些索引实际用到了哪些索引覆盖索引 查询的字段和建立的字段刚好吻合,
这种我们称为覆盖索引
  • 1
  • 2
  • 3
  • 4

3.8 key_len

-- 表示索引中使用的字节数,可通过该列计算查询中使用的索引长度 .
explain select * from employee where dep_id=1 and name='鲁班' and age=10
  • 1
  • 2

在这里插入图片描述

3.9 ref

索引是否被引入到, 到底引用到了哪几个索引
  • 1
Explain select * from employee e,department d where e.dep_id = d.id and e.cus_id = 1
  • 1

在这里插入图片描述

Explain select e.dep_id from employee e,department d,customer c where e.dep_id = d.id and e.cus_id = c.id and e.name='鲁班'
  • 1

在这里插入图片描述

3.10 rows

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,每长表有多少行被优化器查询过

3.11 filtered

满足查询的记录数量的比例,注意是百分比,不是具体记录数 . 值越大越好,filtered列的值依赖统计信息,并不十分准确

-- 满足查询的记录数量的比例,注意是百分比,不是具体记录数 . 值越大越好,filtered列的值依赖统计信息,并不十分准确
Explain select e.dep_id from employee e,department d where e.dep_id = d.id
  • 1
  • 2

3.12 Extra (重要)

注意:
语句中出现了Using Filesort 和 Using Temporary说明没有使用到索引
出现 impossible where说明条件永远不成立

产生的值:

	/* 
	Using filesort (需要优化)
		说明mysql会对数据使用一个外部的索引排序,
		而不是按照表内的索引顺序进行
		Mysql中无法利用索引完成排序操作称为"文件排序"
	*/	
	explain select * from employee where dep_id =1 ORDER BY cus_id
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

在这里插入图片描述

	/* 
	Using temporary (需要优化)
		使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,
		常见于排序orderby 和分组查询group by
	*/
	explain select name from employee where dep_id in (1,2,3) GROUP BY cus_id
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

在这里插入图片描述

	/*
	impossible where (需要优化)
		where 子句的值总是false 不能用来获取任何元组
	*/	
	explain select name from employee where name='鲁班' and name='zs'
  • 1
  • 2
  • 3
  • 4
  • 5

在这里插入图片描述

  • use index

     表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好
     如果同时出现using where  表明索引被用来执行索引键值的查找
     如果没有同时出现using where 表明索引 用来读取数据而非执行查找动作
     示例
    
    • 1
    • 2
    • 3
    • 4
  • using where

     表明使用了where过滤
    
    • 1
  • using join buffer

     使用了连接缓存
    
    • 1

四 sql索引优化实战总结

mysql索引及性能优化

五 案例sql脚本

  • customer.sql
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES ('1', 'zs');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • department.sql
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`address` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('1', '研发部(RD)', '2层');
INSERT INTO `department` VALUES ('2', '人事部(HR)', '3层');
INSERT INTO `department` VALUES ('3', '市场部(MK)', '4层');
INSERT INTO `department` VALUES ('4', '后勤部(MIS)', '5层');
INSERT INTO `department` VALUES ('5', '财务部(FD)', '6层');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • employee.sql
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 `dep_id` int(11) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 `salary` decimal(10,2) DEFAULT NULL,
 `cus_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', '鲁班', '1', '10', '1000.00', '1');
INSERT INTO `employee` VALUES ('2', '后裔', '1', '20', '2000.00', '1');
INSERT INTO `employee` VALUES ('3', '孙尚香', '1', '20', '2500.00', '1');
INSERT INTO `employee` VALUES ('4', '凯', '4', '20', '3000.00', '1');
INSERT INTO `employee` VALUES ('5', '典韦', '4', '40', '3500.00', '2');
INSERT INTO `employee` VALUES ('6', '貂蝉', '6', '20', '5000.00', '1');
INSERT INTO `employee` VALUES ('7', '孙膑', '6', '50', '5000.00', '1');
INSERT INTO `employee` VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • testemployee.sql
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for testemployee
-- ----------------------------
DROP TABLE IF EXISTS `testemployee`;
CREATE TABLE `testemployee` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 `dep_id` int(11) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 `salary` decimal(10,2) DEFAULT NULL,
 `cus_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1000109 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of testemployee
-- ----------------------------
-- 使用存储过程导入数据
-- 查看是否开启函数功能
show variables like 'log_bin_trust_function_creators';
-- 设置开启函数功能
set global log_bin_trust_function_creators=1;
-- 创建函数用于生成随机字符串
delimiter $$
create function rand_string(n int) returns varchar(255)
begin
  declare chars_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnm';
  declare return_str varchar(255) default '';
  declare i int default 0;
  while i<n do
  set return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));
  set i=i+1;
  end while;
  return return_str;
end $$
-- 创建存储过程用于插入数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
/*把autocommit设置成0*/
set autocommit= 0;
repeat
set i=i+1;
insert into testemployee(name,dep_id,age,salary,cus_id)
values(rand_string(6),'2',24,3000,6);
until i=max_num end repeat;
commit;
end $$

-- 调用存储过程插入数据
call insert_emp(1,1000000);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/一键难忘520/article/detail/754961
推荐阅读
相关标签
  

闽ICP备14008679号