explain分析sql语句性能详解

ゝ一世哀愁。 2023-09-29 15:15 22阅读 0赞

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

*   *  一、概念及作用
     *  二、使用方法
     *  三、 参数详解
     *   *  3.1 id（重要）
         *  3.2 select\_type
         *  3.3 table
         *  3.4 partitions
         *  3.5 type (重要)
         *   *  3.5.1 system
             *  3.5.2 const 表示通过索引一次就找到了
             *  3.5.3 eq\_ref 唯一性索引扫描
             *  3.5.4 ref 非唯一性索引扫描
             *  3.5.5 range
             *  3.5.6 index
             *  3.5.7 ALL
         *  3.6 possible\_keys
         *  3.7 key （重要）
         *  3.8 key\_len
         *  3.9 ref
         *  3.10 rows
         *  3.11 filtered
         *  3.12 Extra （重要）
 *  四 sql索引优化实战总结
 *  五 案例sql脚本

## 一、概念及作用 ##

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

*  查看表的读取顺序
 *  数据读取操作的操作类型
 *  查看哪些索引可以使用
 *  查看哪些索引被实际使用
 *  查看表之间的引用
 *  查看每张表有多少行被优化器执行

## 二、使用方法 ##

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

explain select cus_id from testemployee where cus_id > 10

![在这里插入图片描述][ce01f26288444865aff289006121e1d6.png]

## 三、 参数详解 ##

### 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;

![在这里插入图片描述][52f5b27fa5174c97b8a39e807aa7ea16.png]

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

![在这里插入图片描述][d278d00b98d94c4b8135f1e528e2cf3c.png]

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

![在这里插入图片描述][06d819831f50403fa3492b09edd5c2ee.png]

### 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

![在这里插入图片描述][f882281d5910463fbfe9d5b84dece1f3.png]

### 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'

![在这里插入图片描述][2523a9fd0108458aac156af1c7fb851d.png]

#### 3.5.2 const 表示通过索引一次就找到了 ####

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

![在这里插入图片描述][2506824a087e436db7db33fd5f7c5047.png]

#### 3.5.3 eq\_ref 唯一性索引扫描 ####

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

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

![在这里插入图片描述][829c215a9c94462e935567c3ed8c7d7d.png]

#### 3.5.4 ref 非唯一性索引扫描 ####

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

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

![在这里插入图片描述][e1c515a3194b41cdb51784da225ccdba.png]  
添加dep\_id索引

CREATE index emp_dep_id on  employee(dep_id)

执行结果  
![在这里插入图片描述][4f60844db87c4c5cb4ddc86877a80176.png]

#### 3.5.5 range ####

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

explain select * from employee where id>2

![在这里插入图片描述][05ea064681b445f9aecdc64e647d80ef.png]

#### 3.5.6 index ####

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

explain select id from employee

![在这里插入图片描述][c68500bc3f3b4e5f8fa2f22ae3a09fd4.png]

#### 3.5.7 ALL ####

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

explain select * from employee

![在这里插入图片描述][5b684076d6a44a748ff73e9461e1055e.png]

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

### 3.6 possible\_keys ###

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

--  可能不会用到索引，实际用到索引
    explain select dep_id from employee

![在这里插入图片描述][c95ca7187ee54b87bc94e47b024ade75.png]

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

![在这里插入图片描述][9639761829194c4f96992aa8debd57f4.png]

### 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

![在这里插入图片描述][a1b9d89a23a6430fa2453810646c0653.png]

### 3.9 ref ###

索引是否被引入到, 到底引用到了哪几个索引

Explain select * from employee e,department d where e.dep_id = d.id and e.cus_id = 1

![在这里插入图片描述][437840e132744b5a93d1a180e14be355.png]

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='鲁班'

![在这里插入图片描述][5553c5a47165402f9369eaa1fc3a98cb.png]

### 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

![在这里插入图片描述][174ebb0624ef4f919a042807be88ba0f.png]

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

![在这里插入图片描述][bd0ffa87e90b46729f6bb49dca9b45b3.png]

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

![在这里插入图片描述][207137666a9c4cfd853f77c3f13360b5.png]

*  use index
    
        表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好
         如果同时出现using where  表明索引被用来执行索引键值的查找
         如果没有同时出现using where 表明索引 用来读取数据而非执行查找动作
         示例
 *  using where
    
        表明使用了where过滤
 *  using join buffer
    
        使用了连接缓存

# 四 sql索引优化实战总结 #

[mysql索引及性能优化][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');

*  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);

[ce01f26288444865aff289006121e1d6.png]: https://img-blog.csdnimg.cn/ce01f26288444865aff289006121e1d6.png
[52f5b27fa5174c97b8a39e807aa7ea16.png]: https://img-blog.csdnimg.cn/52f5b27fa5174c97b8a39e807aa7ea16.png
[d278d00b98d94c4b8135f1e528e2cf3c.png]: https://img-blog.csdnimg.cn/d278d00b98d94c4b8135f1e528e2cf3c.png
[06d819831f50403fa3492b09edd5c2ee.png]: https://img-blog.csdnimg.cn/06d819831f50403fa3492b09edd5c2ee.png
[f882281d5910463fbfe9d5b84dece1f3.png]: https://img-blog.csdnimg.cn/f882281d5910463fbfe9d5b84dece1f3.png
[2523a9fd0108458aac156af1c7fb851d.png]: https://img-blog.csdnimg.cn/2523a9fd0108458aac156af1c7fb851d.png
[2506824a087e436db7db33fd5f7c5047.png]: https://img-blog.csdnimg.cn/2506824a087e436db7db33fd5f7c5047.png
[829c215a9c94462e935567c3ed8c7d7d.png]: https://img-blog.csdnimg.cn/829c215a9c94462e935567c3ed8c7d7d.png
[841f5823d7734dc1bf5fcccdae2f97bd.png]: https://img-blog.csdnimg.cn/841f5823d7734dc1bf5fcccdae2f97bd.png
[e1c515a3194b41cdb51784da225ccdba.png]: https://img-blog.csdnimg.cn/e1c515a3194b41cdb51784da225ccdba.png
[4f60844db87c4c5cb4ddc86877a80176.png]: https://img-blog.csdnimg.cn/4f60844db87c4c5cb4ddc86877a80176.png
[05ea064681b445f9aecdc64e647d80ef.png]: https://img-blog.csdnimg.cn/05ea064681b445f9aecdc64e647d80ef.png
[c68500bc3f3b4e5f8fa2f22ae3a09fd4.png]: https://img-blog.csdnimg.cn/c68500bc3f3b4e5f8fa2f22ae3a09fd4.png
[5b684076d6a44a748ff73e9461e1055e.png]: https://img-blog.csdnimg.cn/5b684076d6a44a748ff73e9461e1055e.png
[c95ca7187ee54b87bc94e47b024ade75.png]: https://img-blog.csdnimg.cn/c95ca7187ee54b87bc94e47b024ade75.png
[9639761829194c4f96992aa8debd57f4.png]: https://img-blog.csdnimg.cn/9639761829194c4f96992aa8debd57f4.png
[a1b9d89a23a6430fa2453810646c0653.png]: https://img-blog.csdnimg.cn/a1b9d89a23a6430fa2453810646c0653.png
[437840e132744b5a93d1a180e14be355.png]: https://img-blog.csdnimg.cn/437840e132744b5a93d1a180e14be355.png
[5553c5a47165402f9369eaa1fc3a98cb.png]: https://img-blog.csdnimg.cn/5553c5a47165402f9369eaa1fc3a98cb.png
[174ebb0624ef4f919a042807be88ba0f.png]: https://img-blog.csdnimg.cn/174ebb0624ef4f919a042807be88ba0f.png
[bd0ffa87e90b46729f6bb49dca9b45b3.png]: https://img-blog.csdnimg.cn/bd0ffa87e90b46729f6bb49dca9b45b3.png
[207137666a9c4cfd853f77c3f13360b5.png]: https://img-blog.csdnimg.cn/207137666a9c4cfd853f77c3f13360b5.png
[mysql]: https://blog.csdn.net/weixin_43811057/article/details/124984631