牛客SQL.3

223 阅读5分钟

「这是我参与11月更文挑战的第3天,活动详情查看:2021最后一次更文挑战

描述

有一个全部员工的薪水表salaries简况如下:

img

有一个各个部门的领导表dept_manager简况如下:

img

请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no,输出结果以salaries.emp_no升序排序,并且请注意输出结果里面dept_no列是最后一列,以上例子输出如下:

img

示例1

drop table if exists  `salaries` ; 
drop table if exists  `dept_manager` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_manager VALUES('d001',10002,'9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
​
输出:
10002|72527|2001-08-02|9999-01-01|d001
10004|74057|2001-11-27|9999-01-01|d004
10005|94692|2001-09-09|9999-01-01|d003
10006|43311|2001-08-02|9999-01-01|d002

方法一

对两张表进行内连接,以manager表中的emp_no为条件去salaries表中查询,再排序输出;

SELECT a.*, b.dept_no
FROM salaries a, dept_manager b
WHERE a.emp_no=b.emp_no 
ORDER BY emp_no

查看题解后,发现审题不仔细,题意为当前领导的薪水,所以最好再加上日期条件;不加也能过

SELECT a.*, b.dept_no
FROM salaries a JOIN dept_manager b
ON a.emp_no=b.emp_no 
WHERE a.to_date='9999-01-01' and b.to_date='9999-01-01'
ORDER BY emp_no

运行效果

首先我们还是打开OPTIMIZER_TRACE查看,因为涉及到了两张表的连接,所以我们直接来看considered_execution_plans部分,具体看看是如何进行选择的,(不加日期条件)结果如下:

image-20211104104225193

首先plan_prefix字段为空,且table字段为表b,说明是以b为驱动表;再看considered_access_paths字段,对于驱动表b是以什么方式进行查询的,可以看到有两个{}说明对该表的查询方式有两种:

  • 第一种访问方式是access_type=ref,且index=primary说明是以主键常数值比较的方式,usable=false说明该方式不可用,因为在查询语句中,对于条件a.emp_no=b.emp_no,如果b为驱动表,那么此时a.emp_no的值是未知的,所以无法将a.emp_no替换成一个常数值,因此无法使用该方式;
  • 第二种访问方式access_type=scan就是采用全表扫描的方式,rows_to_scan预计需要扫描的行数为4,cost花销为1.8,chosen选择了这种方式;

接着往下看:

image-20211104105416076

对于驱动表为b,剩下对于被驱动表a的访问如何选择:

  • 第一种方式,access_type=refindex=primary说明是以主键常数比较的方式进行访问,开销为4.8,并且选择了该种方式;所以查询两个表的开销为驱动表的1.8加上被驱动表的4.8,最终为6.6;此外还可以从上图的倒数第三个字段可以看出排序的开销为4,所以整个方案的最终开销为10.6;并且chosen=true,说明最后的执行方式为这种;
  • 第二种方式,全表扫描,开销为6.6,开销比第一种大,所以chosen=false

以上是以b为驱动表的分析,我们再来看看以a为驱动表是如何:

image-20211104110408801

a为驱动表也有两种方式:

  • 第一种方式,主键常量比较,上面分析过,不行;
  • 第二种方式为全表扫描,开销为2.4,选择该方式;

来看看剩下的被驱动表b的访问方式:

image-20211104110638855

对于表b也有两种访问方式:

  • 第一种常量比较,对于被驱动表来说,条件a.emp_no=b.emp_no等式的另一端可以替换为常量;开销为8.4;
  • 第二种方式,全表扫描,不选择,原因是索引覆盖的方式比全表扫描更好;因为第一种方式为主键索引,所以B+树的叶结点中包含了所有信息,可以看成是索引覆盖,直接扫描索引显然比全表扫描更好;

对于a为驱动表来说,最小的开销为10.8;所以最终方案为以b为驱动表的方式,开销为10.6;

通过上面的例子,也发现了,两表连接时的成本为驱动表的扇出 x 被驱动表的单次访问开销 + 驱动表的单词访问开销,所以当我们需要优化时,可以减小驱动表的结果集,或者在被驱动表上加上合适的索引,来降低成本;