MySQL使用存储过程代替子查询
摘要: 出处：黑洞中的奇点 的博客 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 | +----+-------------+------------+------------+-------+----------------+-------------+---------+--------------------+---------+----------+-------------+ | 1 | PRIMARY | e | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where | | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | employees.e.emp_no | 10 | 100.00 | NULL | | 2 | DERIVED | salaries | NULL | index | PRIMARY,emp_no | emp_no | 4 | NULL | 2694129 | 100.00 | Using index | +----+-------------+------------+------------+-------+----------------+-------------+---------+--------------------+---------+----------+-------------+
+--------+------------+------------+-----------+--------+------------+--------+----------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | emp_no | count(*) | +--------+------------+------------+-----------+--------+------------+--------+----------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 10001 | 17 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | 10002 | 6 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | 10003 | 7 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | 10004 | 16 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | 10005 | 13 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | 10006 | 12 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | 10007 | 14 | | 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 (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> explain select e.* , t1(e.emp_no) from employees e where e.emp_no BETWEEN 10001 and 10010 and t1(e.emp_no); +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | e | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 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) | +--------+------------+------------+-----------+--------+------------+--------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 17 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | 6 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | 7 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | 16 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | 13 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | 12 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | 14 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | 3 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | 18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | 6 | +--------+------------+------------+-----------+--------+------------+--------------+ 10 rows in set (0.01 sec)
版权声明：本文为kelvin19840813原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。