摘要: 出处：黑洞中的奇点 的博客 http://www.cnblogs.com/kelvin19840813/ 您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。
- x220 笔记本 , cpu: i5-2520 , 磁盘intel ssd sata2接口 , 就是这么搓的环境 :(
- 源语句
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 |
+----+-------------+------------+------------+-------+----------------+-------------+---------+--------------------+---------+----------+-------------+
| 100.00 | Using where |
| PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | employees.e.emp_no | 10 | 100.00 | NULL |
| 100.00 | Using index |
+----+-------------+------------+------------+-------+----------------+-------------+---------+--------------------+---------+----------+-------------+
+--------+------------+------------+-----------+--------+------------+--------+----------+
| emp_no | birth_date | first_name | last_name | gender | hire_date | emp_no | count(*) |
+--------+------------+------------+-----------+--------+------------+--------+----------+
|
|
|
|
|
|
|
|
|
|
+--------+------------+------------+-----------+--------+------------+--------+----------+
rows in set (1.00 sec)
- 从id=2开始就先执行salaries 表的子查询 , 可以看出红色字部分是临时索引,从5.6开始就有这个功能,这个临时索引是在内存生成的,如果返回数据量大占用内存越多,表之间关联性能就下降,也有可能把内存涨满 , 最终用了1秒执行效率
- 现在想把这个salaries表的子查询跟employees表同一时间执行,并且达到性能效率提升
- 创建一个存储过程另外关联这个salaries表,如下:
create FUNCTION t1(in_emp_no int ) RETURNS INT BEGIN DECLARE a1 int ; select count(*) into a1 from salaries where emp_no = in_emp_no group by emp_no; RETURN a1; end;
mysql and t1(e.emp_no);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
row warning (0.00 sec)
- 变成了只有一个表运行,而salaries 和 employees 关联在另外一个线程执行存储过程, 至于怎样从mysql核心看它执行这个稍后揭晓 , 最终使用了0.01秒执行 , 记住这是5年前的x220哦
+--------+------------+------------+-----------+--------+------------+--------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date | t1(e.emp_no) |
+--------+------------+------------+-----------+--------+------------+--------------+
|
|
|
|
|
|
|
|
|
|
+--------+------------+------------+-----------+--------+------------+--------------+
rows in set (0.01 sec)