With As多表查询

目录

语法

举例

表关系

 分析

mysql进程状态

         使用左连接

---

语法

with 
alias_name_01 as (查询SQL  select * from table_name_01)
alias_name_02 as (查询SQL  select * from alias_name_01 an01,table_name_02 tn02 where an01.foreign_id = tn02.id)
select * from alias_name_02 ;

举例

表关系

CREATE TABLE ims.`ims_app_dept_info` (
  `app_dept_id` int NOT NULL AUTO_INCREMENT,
  `p_app_dept_id` int DEFAULT NULL,
  `dept_name` varchar(50)  DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`app_dept_id`)
)  COMMENT='组织机构表';



CREATE TABLE ims.`ims_app_employee_info` (
  `app_emp_id` int NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(50) DEFAULT NULL COMMENT '员工姓名',
  `emp_code` varchar(50) DEFAULT NULL COMMENT '员工编码',
  `app_dept_id` int DEFAULT NULL,
  PRIMARY KEY (`app_emp_id`)
) COMMENT='员工信息表';

        如上所示，有一个员工表，一个组织表，员工表关联到组织表，通过app_dept_id关联。

with 
emp_info as (
	select * from ims.ims_app_employee_info t limit 100
),
emp_all_info as (
	select 
         ei.emp_name,ei.emp_code,dept.dept_name 
    from emp_info ei,
         ims.ims_app_dept_info dept
	where 
         dept.app_dept_id = ei.app_dept_id
)
select * from emp_all_info limit 3;

        使用上面的SQL，就可以查询两个表之间的数据，而不需要使用left join 关联。

 

 分析

 用explain可以看到这个SQL执行的大概过程。

首先是从表t，也就是ims_app_employee_info中过滤出100条数据，这个过程是很快的。

然后就是从ims_app_dept_info表，根据主键关联，速度也是很快的。

最后是table=derived3这个表，这是一个假表，应该是临时表，没有用到任何key，所以应该是从100条数据中过滤出来3条。

再看这张执行进程状态过程图，这里面查询时间总共是0.333s，

        从上图大概能猜到，创建了临时表用于存放这些查询数据，后面就是执行with这个SQL语句。

         这里有三个query end状态，并不是说执行了三次，也不是说移除了两张临时表。

        此处应该是进程执行是有时间的，query end表示查询结束，可以开始处理数据。

        而查询的数据是放在临时表中，数据处理完毕后会处于 removing tmp table。

        其实可以做个测试，比如explain select * from ims.ims_app_dept_info t，有时候也是会有三个query end状态。

        

mysql进程状态

query end	这种状态发生后处理查询。
removing tmp table	线程移除临时表。 如果没有创建临时表，这种状态是不使用。
closing tables	线程改变表中的数据刷新到磁盘和关闭使用的表。
end	这发生在最后的清理工作，有ALTER TABLE , CREATE VIEW , DELETE , INSERT , SELECT或UPDATE语句。
freeing items	线程已执行的命令。 释放一些在这种状态下完成的项目，涉及的查询缓存。 这种状态通常后跟cleaning up 。
init	在发生之前的初始化ALTER TABLE , DELETE , INSERT , SELECT或UPDATE语句。 服务器在这种状态下采取的行动包括冲洗二进制日志中InnoDB日志,和一些查询缓存清理操作
optimizing	服务器执行查询的初始优化
preparing	这种状态出现在查询优化过程中。

  使用左连接

explain with 
    emp_info as (
	    select * from ims.ims_app_employee_info t limit 3
    ),
    emp_all_info as (
	    select ei.emp_name,ei.emp_code,dept.dept_name 
        from emp_info ei 
			left join ims.ims_app_dept_info dept 
            on dept.app_dept_id = ei.app_dept_id
    ) 
	select * from emp_all_info;	


explain 
select e.emp_name,e.emp_code,t.dept_name 
from ims.ims_app_employee_info e 
    left join ims.ims_app_dept_info t  
    on t.app_dept_id = e.app_dept_id 
limit 3;

图一

 

图二

         上述两个查询结果是一样的，执行时间也是基本相似，都是0.183s，也就是说基本对性能相差无几。

        但是从上述id的内容来看，猜测图一应该是执行了两次查询处理，图二可能只有一次。