explain分析sql语句性能详解
一、概念及作用
使用explain关键字,可以模拟优化器执行的SQL语句，从而知道MYSQL是如何处理sql语句的。通过Explain可以分析查询语句或表结构的性能瓶颈
具体作用：
-
查看表的读取顺序
-
数据读取操作的操作类型
-
查看哪些索引可以使用
-
查看哪些索引被实际使用
-
查看表之间的引用
-
查看每张表有多少行被优化器执行
二、使用方法
使用Explain关键字 放到sql语句前
explain select cus_id from testemployee where cus_id > 10
三、 参数详解
3.1 id（重要）
select查询的序列号，包含一组数字,表示查询中执行select子句或操作表的顺序 值分为三种情况
id值相同
执行顺序由上到下
id不同
如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行
id相同、不同,同时存在
可以认为是一组,从上往下顺序执行
在所有组中,id值越大,优先级越高,越先执行
-- id值相同
EXPLAIN SELECT * from employee e,department d,customer c where e.dep_id = d.id and e.cus_id = c.id;
-- id值不同
EXPLAIN SELECT * from department WHERE id = (SELECT id from employee WHERE id=(SELECT id from customer WHERE id = 1))
-- id值相同 不同都存在 deriverd 衍生出来的虚表
EXPLAIN select * from department d, (select * from employee group by dep_id) t where d.id = t.dep_id;
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合并的结果集在最后
-- 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
3.3 table
显示这一行的数据是关于哪张表的
3.4 partitions
如果查询是基于分区表的话, 会显示查询访问的分区
3.5 type (重要)
访问类型排列
结果值:(最好到最差) system > const > eq_ref > ref > range > index > ALL
3.5.1 system
-- system 表中有一行记录(系统表) 这是const类型的特例,平时不会出现
explain select HOST from mysql.db where HOST='localhost'
3.5.2 const 表示通过索引一次就找到了
-- const 表示通过索引一次就找到了，const用于比较primary 或者 unique索引. 直接查询主键或者唯一索引，因为只匹配一行数据,所以很快
EXPLAIN select id from employee where id=1
3.5.3 eq_ref 唯一性索引扫描
eq_ref 唯一性索引扫描 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或唯一索引扫描
EXPLAIN select * from employee e,department d where e.id=d.id
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
添加dep_id索引
CREATE index emp_dep_id on employee(dep_id)
执行结果
3.5.5 range
range 只检索给定范围的行,使用一个索引来选择行 一般就是在你的where语句中出现between<>\ in等查询,这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点.而结束语另一点,不用扫描全部索引
explain select * from employee where id>2
3.5.6 index
index与All区别为index类型只遍历索引树,通常比All要快,
因为索引文件通常比数据文件要小all和index都是读全表,
但index是从索引中读取,all是从硬盘当中读取
explain select id from employee
3.5.7 ALL
ALL 将全表进行扫描,从硬盘当中读取数据,如果出现了All 切数据量非常大, 一定要去做优化
explain select * from employee
要求:
一般来说,保证查询至少达到range级别
最好能达到ref
3.6 possible_keys
显示可能应用在这张表中的索引,一个或者多个
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
可能自己创建了4个索引,在执行的时候,可能根据内部的自动判断,只使用了3个
-- 可能不会用到索引，实际用到索引
explain select dep_id from employee
-- 可能会使用索引，实际没用到索引
EXPLAIN select * from employee e,department d where e.dep_id = d.id
3.7 key （重要）
实际使用的索引,如果为NULL,则没有使用索引,查询中若使用了覆盖索引 ,
则该索引仅出现在key列表possible_keys与key关系,理论应该用到
哪些索引实际用到了哪些索引覆盖索引 查询的字段和建立的字段刚好吻合,
这种我们称为覆盖索引
3.8 key_len
-- 表示索引中使用的字节数,可通过该列计算查询中使用的索引长度 .
explain select * from employee where dep_id=1 and name='鲁班' and age=10
3.9 ref
索引是否被引入到, 到底引用到了哪几个索引
Explain select * from employee e,department d where e.dep_id = d.id and e.cus_id = 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='鲁班'
3.10 rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,每长表有多少行被优化器查询过
3.11 filtered
满足查询的记录数量的比例，注意是百分比，不是具体记录数 . 值越大越好，filtered列的值依赖统计信息，并不十分准确
-- 满足查询的记录数量的比例，注意是百分比，不是具体记录数 . 值越大越好，filtered列的值依赖统计信息，并不十分准确
Explain select e.dep_id from employee e,department d where e.dep_id = d.id
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
/*
Using temporary (需要优化)
使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,
常见于排序orderby 和分组查询group by
*/
explain select name from employee where dep_id in (1,2,3) GROUP BY cus_id
/*
impossible where (需要优化)
where 子句的值总是false 不能用来获取任何元组
*/
explain select name from employee where name='鲁班' and name='zs'
-
use index
表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好 如果同时出现using where 表明索引被用来执行索引键值的查找 如果没有同时出现using where 表明索引 用来读取数据而非执行查找动作 示例 -
using where
表明使用了where过滤 -
using join buffer
使用了连接缓存
四 sql索引优化实战总结
五 案例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');
- 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层');
- 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');
- 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);
604
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
