其他
SQL案例分析之部分查询和全部查询
Editor's Note
SQL开发是门大学问，跟着松华老师每次都能多学到一点知识。
The following article is from SQL开发与优化 Author 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: ALLpossible_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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 331143 filtered: 100.00 Extra: Using filesort2 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: indexpossible_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: refpossible_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: refpossible_keys: ix_dept_emp key: ix_dept_emp key_len: 16 ref: employees.d1.dept_no rows: 41376 filtered: 100.00 Extra: NULL3 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)