SQL案例分析之部分查询和全部查询

大家好，我是知数堂SQL 优化班老师 网名：骑龟的兔子

今天给大家看一个案例来讨论，一个重要的SQL优化思路，部分查询和全部查询。

下面是要使用的两个表 

root@mysql3306.sock>[employees]>select * from departments ;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+


root@mysql3306.sock>[employees]>desc dept_emp2 ;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int     | NO   | PRI | NULL    |       |
| dept_no   | char(4) | NO   | PRI | NULL    |       |
| from_date | date    | NO   |     | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+

现在需求是求出 每个部门的员工号从小到大的五个员工。

我们按照思路写下如下SQL

desc with w1 as (
select d.* 
,row_number() over(PARTITION by d.dept_no order by emp_no asc ) rn 
from dept_emp d 
)
select dept_no ,group_concat(emp_no  order by emp_no asc SEPARATOR '|' ) c 
 from w1 
 where rn <=5 
 group by dept_no\G


*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 331143
     filtered: 33.33
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: d
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 331143
     filtered: 100.00
        Extra: Using filesort
2 rows in set, 2 warnings (0.00 sec)




oot@mysql3306.sock>[employees]>with w1 as (
    -> select d.*
    -> ,row_number() over(PARTITION by d.dept_no order by emp_no asc ) rn
    -> from dept_emp2 d
    -> )
    -> select dept_no ,group_concat(emp_no  order by emp_no asc SEPARATOR '|' ) c
    ->  from w1
    ->  where rn <=5
    ->  group by dept_no;
+---------+-------------------------------+
| dept_no | c                             |
+---------+-------------------------------+
| d001    | 10017|10055|10058|10108|10140 |
| d002    | 10042|10050|10059|10080|10132 |
| d003    | 10005|10013|10036|10039|10054 |
| d004    | 10003|10004|10010|10018|10020 |
| d005    | 10001|10006|10008|10012|10014 |
| d006    | 10009|10010|10029|10033|10067 |
| d007    | 10002|10016|10034|10041|10050 |
| d008    | 10007|10015|10019|10040|10046 |
| d009    | 10011|10038|10049|10060|10088 |
+---------+-------------------------------+
9 rows in set (0.60 sec)

从上面的执行计划中，可以看出rows 331143 这就是说，对这个表的这么多

数据进行了排序和操作，这就是全部查询

那看下,下面的SQL

root@mysql3306.sock>[employees]>desc with w1 as (
    -> select
    -> d.* from departments d1 join lateral
    -> (
    -> select d.*
    -> from dept_emp2 d
    -> where d.dept_no = d1.dept_no
    ->  order by  d.dept_no ,d.emp_no
    -> limit 5
    -> ) d on  d.dept_no = d1.dept_no
    -> )
    -> select dept_no ,group_concat(emp_no  order by emp_no asc SEPARATOR '|' ) c
    ->  from w1
    ->  group by dept_no;
 *************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: d1
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: dept_name
      key_len: 162
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index; Using temporary; Using filesort; Rematerialize (<derived3>)
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 16
          ref: employees.d1.dept_no
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 3
  select_type: DEPENDENT DERIVED
        table: d
   partitions: NULL
         type: ref
possible_keys: ix_dept_emp
          key: ix_dept_emp
      key_len: 16
          ref: employees.d1.dept_no
         rows: 41376
     filtered: 100.00
        Extra: NULL
3 rows in set, 2 warnings (0.00 sec)


root@mysql3306.sock>[employees]>with w1 as (
    -> select
    -> d.* from departments d1 join lateral
    -> (
    -> select d.*
    -> from dept_emp2 d
    -> where d.dept_no = d1.dept_no
    ->  order by  d.dept_no ,d.emp_no
    -> limit 5
    -> ) d on  d.dept_no = d1.dept_no
    -> )
    -> select dept_no ,group_concat(emp_no  order by emp_no asc SEPARATOR '|' ) c
    ->  from w1
    ->  group by dept_no;
+---------+-------------------------------+
| dept_no | c                             |
+---------+-------------------------------+
| d001    | 10017|10055|10058|10108|10140 |
| d002    | 10042|10050|10059|10080|10132 |
| d003    | 10005|10013|10036|10039|10054 |
| d004    | 10003|10004|10010|10018|10020 |
| d005    | 10001|10006|10008|10012|10014 |
| d006    | 10009|10010|10029|10033|10067 |
| d007    | 10002|10016|10034|10041|10050 |
| d008    | 10007|10015|10019|10040|10046 |
| d009    | 10011|10038|10049|10060|10088 |
+---------+-------------------------------+
9 rows in set (0.23 sec)

上面的SQL是每个dept_no 只要选出5个之后就不进行查询了，从执行计划中能够可以看出，良好的索引和排序策略，也直接把排序也消除了。这就是部分查询，即只查询满足要求的数量进而达到优化的目的。

 从上面同一种需求的，两种不同SQL写法，可以看出不同的思路，就有可能导致不同的效率。

我是知数堂SQL 优化班老师~ ^^

最新一期SQL优化课，在12月份开始。

如有关于SQL优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化

高性能MySQL,SQL优化群 有叶金荣，吴炳锡 两位大神坐镇 ：579036588

欢迎加入 知数堂大家庭。

我的微信公众号：SQL开发与优化(sqlturning)