第8章-7 explain

已于 2025-05-13 09:35:19 修改
阅读量790 收藏 12
点赞数 17
分类专栏： mysql高级 文章标签： 数据库 sql java
于 2025-05-13 09:17:35 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_35461948/article/details/147915314
版权

         上一篇：《第8章-6 索引失效的情况》，接着回顾下explain的内容

1，使用方法

1，explain  sql语句

部门数据：

DROP TABLE IF EXISTS department;
CREATE TABLE department (
  id int(11) NOT NULL AUTO_INCREMENT,
  dept_name varchar(30) DEFAULT NULL,
  address varchar(40) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO department VALUES
 ('1', '研发部', '1层'),
 ('2', '人事部', '2层'),
 ('3', '市场部', '3层'),
 ('4', '后勤部', '4层'),
 ('5', '财务部', '5层');

员工数据：

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=1 DEFAULT CHARSET=utf8;



INSERT INTO employee VALUES 
('1', '张三', '1', '10', '1000.00', '1'),
('2', '李四', '1', '20', '2000.00', '1'),
('3', '王五', '1', '20', '2500.00', '1'),
('4', '赵六', '4', '20', '3000.00', '1'),
('5', '孙七', '4', '40', '3500.00', '2'),
('6', '周八', '6', '20', '5000.00', '1'),
('7', '吴九', '6', '50', '5000.00', '1'),
('8', '郑十', '30', '35', '4000.00', '1');

customer数据：

DROP TABLE IF EXISTS customer;
CREATE TABLE customer (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  email varchar(255) NOT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
INSERT INTO customer (name, email) VALUES
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com'),
('王五', 'wangwu@example.com'),
('赵六', 'zhaoliu@example.com'),
('孙七', 'sunqi@example.com'),
('周八', 'zhouba@example.com'),
('吴九', 'wujiu@example.com'),
('郑十', 'zhengshi@example.com');

2，分析包含信息

id 执行顺序

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

id值的三种情况

id相同,
EXPLAIN SELECT * from employee e,department d,customer c
where e.dep_id = d.id and e.cus_id = c.id;

如果id相同，执行顺序由上到下

id不同
EXPLAIN SELECT * FROM department
   WHERE id = (SELECT id FROM employee 
	WHERE id= (SELECT id FROM customer WHERE id = 1));

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

先执行：

SELECT id FROM customer WHERE id = 1

然后执行：

 

SELECT id FROM employee 
WHERE id= (SELECT id FROM customer WHERE id = 1) 
 

 

最后执行：
 

SELECT * FROM department
   WHERE id = (SELECT id FROM employee 
	WHERE id= (SELECT id FROM customer WHERE id = 1));
 

 

 

id相同不同，同时存在
 

可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
 

EXPLAIN SELECT * FROM department d, 
(SELECT dep_id, COUNT(id) count_staff 
   FROM employee GROUP BY dep_id) t 
WHERE d.id = t.dep_id;
 

 

 

优先执行2（deriverd 衍生出来的虚表）：
 

SELECT dep_id, COUNT(id) count_staff 
FROM employee GROUP BY dep_id
 

然后id相同的，从上往下顺序执行。
 

执行：
 

SELECT * FROM department d
 

最后执行 <derived2> 临时表2
 

 

总结：
 

相同，顺序走；不同，看谁大；大的先执行
 

 

select_type 查询类型
 

作用：查询类型，主要用于区别普通查询，联合查询，子查询等复杂查询
 

 

结果值：
 

SIMPLE
 

简单select查询，单表查询，查询中不包含子查询或者UNION
 

EXPLAIN SELECT * from department;
 

 

 

 

PRIMARY
 

查询中若包含任何复杂的子查询，最外层查询则被标记为primary
 

EXPLAIN SELECT * FROM department
   WHERE id = (SELECT id FROM employee 
	WHERE id= (SELECT id FROM customer WHERE id = 1));
 

 

 

第一个，最外层的查询则被标记为primary
 

 

SUBQUERY
 

在select或where中包含了子查询
 

 

DERIVED
 

在from列表中包含的子查询被标记为derived(衍生)，把结果放在临时表当中
 

EXPLAIN SELECT * FROM department d, 
(SELECT dep_id, COUNT(id) count_staff FROM employee GROUP BY dep_id) t 
WHERE d.id = t.dep_id;
 

 

 

SELECT dep_id, COUNT(id) count_staff 
FROM employee GROUP BY dep_id
 

会衍生出一个临时表。Extra里面会标记为：Using temporary
 

 

UNION
 

若第二个select出现的union之后，则被标记为union
 

若union包含在from子句的子查询中，外层select将被标记为deriver
 

 

UNION RESULT
 

从union表获取结果select
 

两个UNION合并的结果集在最后
 

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;
 

 

 

 

table 使用的表格
 

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

 

partitions 访问的分区
 

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

 

重要指标：
 

type 扫描类型
 

表示访问类型排列。
 

 

结果值最好到最差：
 

1，system
 

2，const
 

3，eq_ref
 

4，ref
 

5，range
 

6，index
 

7，ALL
 

 

system
 

表中有一行记录(系统表)  这是const类型的特例，平时不会出现
 

 

const
 

表示通过索引一次就找到了；
 

const用于比较primary 或者 unique索引，直接查询主键或者唯一索引
 

因为只匹配一行数据，所以很快
 

EXPLAIN SELECT id FROM employee WHERE id = 1;
 

 

 

 

eq_ref
 

唯一性索引扫描
 

对于每个索引键，表中只有一条记录与之匹配，一一对应的。
 

常见于主键或唯一索引扫描
 

EXPLAIN SELECT * FROM employee, department 
WHERE employee.id = department.id;
 

 

 

 

ref
 

非唯一性索引扫描，返回匹配某个单独值的所有行，不是一一对应的。
 

本质上也是一种索引访问
 

它返回所有匹配某个单独值的行
 

可能会找到多个符合条件的行，
 

所以它应该属于查找和扫描的混合体
 

先添加索引：
 

ALTER TABLE employee
ADD INDEX idx_employ_dep_id(dep_id);

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

 

 

 

range
 

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

EXPLAIN SELECT * FROM employee WHERE employee.id BETWEEN 1 and 3;
 

 

 

 

index
 

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

添加一个索引：
 

ALTER TABLE employee
ADD INDEX idx_employ_name(NAME);

EXPLAIN SELECT name FROM employee;
 

 

 

 

ALL
 

将全表进行扫描，从硬盘当中读取数据
 

如果出现了All 切数据量非常大， 一定要去做优化
 

EXPLAIN SELECT * FROM employee;
 

 

 

type的要求：
 

一般来说，保证查询至少达到range级别，最好能达到ref
 

 

possible_keys
 

        key与keys主要作用，是查看是否使用了建立的索引， 也即判断索引失效。在建立多个索引 的情况下， mysql最终用到了哪一个索引 。
 

possible_keys
 

        显示可能应用在这张表中的索引，一个或者多个
 

        查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
 

        可能自己创建了4个索引，在执行的时候，可能根据内部的自动判断，只使用了3个
 

 

key
 

        实际使用的索引，如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中。possible_keys与key关系理论应该用到哪些索引实际用到了哪些索引
 

覆盖索引查询的字段和建立的字段刚好吻合，这种我们称为覆盖索引
 

EXPLAIN SELECT * FROM employee e ,department d 
WHERE e.dep_id = d.id;
 

 

 

直接查所有*，并没有用到索引，key都是null值。修改为：
 

EXPLAIN SELECT e.dep_id,d.id FROM employee e ,department d 
WHERE e.dep_id = d.id;
 

 

 

这样possibel_keys和key就保持一致了。
 

 

key_len 索引长度
 

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度
 

EXPLAIN SELECT * FROM employee WHERE NAME = '张三' AND dep_id=1;
 

 

 

 

EXPLAIN SELECT * FROM employee WHERE dep_id=1;
 

 

 

 

EXPLAIN SELECT * FROM employee WHERE NAME = '张三';
 

 

 

key_len显示的数值并不是最终使用的数值，是可能出现的一个最大值。
 

 

ref 索引引用的情况
 

 

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

EXPLAIN SELECT * FROM employee e, department d
WHERE e.dep_id = d.id and e.cus_id = 1;
 

 

 

 

EXPLAIN SELECT * from employee e,department d,customer c
where e.dep_id = d.id and e.cus_id = c.id and e.name='张三';
 

 

EXPLAIN SELECT c.name FROM customer c,employee e
WHERE e.name = c.name and e.dep id = 1;
 

 

rows 扫描行数
 

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

先查看索引：
 

SHOW INDEX FROM employee;
 

 

 

把idx_employ_dep_id和idx_employ_name删了。
 

DROP INDEX idx_employ_dep_id ON employee;
DROP INDEX idx_employ_name ON employee;
 

 

没有建立id索引
 

EXPLAIN SELECT * from employee e, department d WHERE e.dep_id = d.id;
 

 

 

rows是5和8，是全部；filtered是100%和12.5%。
 

 

重新加上索引：
 

ALTER TABLE employee
ADD INDEX idx_employ_dep_id(dep_id);
 

建立了索引后，再进行查询
 

EXPLAIN SELECT * from employee e, department d
wHERE e.dep id = d.id;
 

 

 

查询条数没有变化，filtered第二个变成25%。 索引都没有用上。因为查的是*
 

EXPLAIN SELECT e.dep_id, d.id FROM employee e, department d
WHERE e.dep_id = d.id;
 

都查带索引的字段：
 

 

 

这边显示用了索引，rows是5和2；filtered都是100%。
 

 

这个一般都会查人员名称，查询字段带上人名名称：
 

EXPLAIN SELECT e.dep_id, e.name FROM employee e, department d
WHERE e.dep_id = d.id;
 

 

 

第二条没有用上索引。
 

 

把idx_employ_dep_id索引上了，加上带名称的索引
 

ALTER TABLE employee
ADD INDEX idx_employ_name_dep_id(dep_id, NAME);
 

再查询：
 

EXPLAIN SELECT e.dep_id, e.name, d.id, d.deptName  
FROM employee e, department d
WHERE e.dep_id = d.id;
 

 

 

这边显示用了索引，rows是5和2；filtered都是100%。
 

 

filtered 过滤比例
 

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

 

Extra 额外信息
 

产生的值
 

 

Using filesort
 

        说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行。采用文件扫描对结果进行计算排序，效率很差。Mysql中无法利用索引完成排序操作称为"文件排序"，需要进行一个优化。
 

EXPLAIN SELECT * from employee WHERE dep_id = 1 ORDER BY cus_id;
 

 

 

-- 当第一个是常量时，可以不加
EXPLAIN SELECT NAME FROM employee WHERE NAME = '张三'
ORDER BY NAME, dep_id, cus_id;
 

对于排序,只有select 字段 与order by 字段都被索引覆盖是才允许使用Using Index
 

 

Using temporary
 

        使用了临时表保存中间结果，Mysql在对查询结果排序时，使用了临时表。出现这种情况一般是要进行优化的，首先是想到用索引来优化。常见于排序order by 和分组查询group by，需要进行优化。
 

EXPLAIN SELECT cus_id, COUNT(1) FROM employee 
WHERE dep_id IN(1,2,3) GROUP BY cus_id;
 

 

 

EXPLAIN SELECT dep_id, cus_id, COUNT(1) FROM employee  
GROUP BY  dep_id, cus_id ORDER BY dep_id;
 

 

 

 

use index也叫索引覆盖
 

表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好；
 

如果同时出现using where  表明索引被用来执行索引键值的查找；
 

如果没有同时出现using where 表明索引 用来读取数据而非执行查找动作；
 

EXPIAIN SELECT name from employee 
 WHERE dep_id = 1;

EXPLAIN SELECT name from employee WHERE name = '张三′;
 

 

using where
 

        表明使用了where过滤，mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。
 

 

using join buffer
 

使用了连接缓存
 

 

impossible where
 

where 子句的值总是false 不能用来获取任何元组，条件是不会满足的。
 

EXPLAIN SELECT name, dep_id,cus_id FRoM employee 
where name='张三' and name='zs';
 

 

 

 

小结：
 

        explain是了解索引执行情况，要熟悉里面常见字段的含义，是否索引，扫描的行数等等
 

 

        上一篇：《第8章-6 索引失效的情况》
 

        下一篇：《第8章-7 explain》

                

        

    

  



    


            

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  





	
打赏作者

	

		

		
			
		
		

		

			
天狼1222

			
你的鼓励将是我创作的最大动力

		

	

	

			

	

	

    ¥1
    ¥2
    ¥4
    ¥6
    ¥10
    ¥20
	

	

		

			

				

					扫码支付：¥1
				

				

					

					
					获取中
					

				

				

					
					
					扫码支付
				

			

		

		

			
您的余额不足，请更换扫码支付或充值

			
打赏作者

		

	



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值