出处：黑洞中的奇点 的博客 http://www.cnblogs.com/kelvin19840813/ 您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。
原SQL执行了 4秒:
mysql> select * from employees e inner join (select emp_no,count(*) from salaries group by emp_no) s on s.emp_no=e.emp
o where e.emp_no BETWEEN 10001 and 10010; | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | 10008 | 3 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | 10009 | 18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | 10010 | 6 |
+--------+------------+------------+-----------+--------+------------+--------+----------+
10 rows in set (4.11 sec)
执行计划, 中间有个auto_key 是mysql 临时在内存创建索引 , salaries表id 是 2 优先执行 , 如果这个表统计数据量很多,那么先等待它执行完之后才能再跟employee表关联增加执行时间 , 子查询有group by 就不能合并到视图里面去 , 这就是 “filter push-down” , “filter push-down”的目的是推动视图内的限制或不能合并的内联视图。 , 并没有重复使用mysql 优化器优势 :
mysql> explain select * from employees e inner join (select emp_no,count(*) from salaries group by emp_no) s on s.emp_no=e.emp_no where e.emp_no BETWEEN 10001 and 10010;
+----+-------------+------------+------------+-------+----------------------+-------------+---------+--------------------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+----------------------+-------------+---------+--------------------+---------+----------+-------------+
| 1 | PRIMARY | e | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100 | Using where |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | employees.e.emp_no | 10 | 100 | NULL |
| 2 | DERIVED | salaries | NULL | index | PRIMARY,emp_no,idx_s | emp_no | 4 | NULL | 2694129 | 100 | Using index |
+----+-------------+------------+------------+-------+----------------------+-------------+---------+--------------------+---------+----------+-------------+
那么现在就是要想把emp 表 和 sal 表 同时统计同进进行关联提高效率 , 做法就是把两个表放到一个子查询里面 , 并且摆脱了mysql 临时在内存中创建索引 , 虽然还有using filesort , 但并不是重点 , 效率提升到 0.01 秒 :
select * from employees e inner join
(
select s.emp_no,count(*) from employees e inner join salaries s on s.emp_no = e.emp_no where e.emp_no BETWEEN 10001 and 10010 group by s.emp_no
) s where e.emp_no BETWEEN 10001 and 10010;
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | 10010 | 6 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | 10010 | 6 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | 10010 | 6 |
+--------+------------+------------+-----------+--------+------------+--------+----------+
100 rows in set (0.01 sec)
mysql> explain select * from employees e inner join
(
select s.emp_no,count(*) from employees e inner join salaries s on s.emp_no = e.emp_no where e.emp_no BETWEEN 10001 and 10010 group by s.emp_no
) s where e.emp_no BETWEEN 10001 and 10010;
+----+-------------+------------+------------+-------+----------------------+---------+---------+--------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+----------------------+---------+---------+--------------------+------+----------+-----------------------------------------------------------+
| 1 | PRIMARY | e | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100 | Using where |
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 93 | 100 | Using join buffer (Block Nested Loop) |
| 2 | DERIVED | e | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100 | Using where; Using index; Using temporary; Using filesort |
| 2 | DERIVED | s | NULL | ref | PRIMARY,emp_no,idx_s | emp_no | 4 | employees.e.emp_no | 9 | 100 | Using index |
+----+-------------+------------+------------+-------+----------------------+---------+---------+--------------------+------+----------+-----------------------------------------------------------+