查询产生临时表的调优
来源:互联网 发布:ug车削编程检查几何体 编辑:程序博客网 时间:2024/05/29 11:02
我们都知道,SQL查询时若产生了临时表,一般要消耗更多的内存,降低查询的效率。因此,当查询时产生了临时表,要进行优化,使引擎在查询时不用创建临时表就能完成查询。
示例使用的是MySQL示例数据库sakila。actor表中有一个主键actor_id,演员的first_name与last_name。film_actor表中外键列actor_id,与actor表进行连接,其中actor_id有很多重复的值,同时actor_id也是主键列(不自增)。
执行语句:SELECT first_name,last_name,COUNT(*) FROM film_actor INNER JOIN actor USING(actor_id) GROUP BY film_actor.actor_id;
结果(返回了很多演员的名字与演过的电影数量):
执行计划:
由于在检索actor表时产生了临时表(文件排序一般也伴随出现)。该SQL效率与性能有待提高。
产生临时表的原因有很多,一般有五种情况:
- 如果GROUP BY 的列没有索引,产生临时表.
- 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.
- 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.
- 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.
- 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.
下面就来优化SQL语句使临时表不再产生就能完成查询,准则就是尽量把大查询分开为小查询:
调优后的执行语句:SELECT first_name,last_name,c.cnt FROM actor INNER JOIN (SELECT actor_id,COUNT(*) AS cnt FROM film_actor GROUP BY actor_id) AS c USING(actor_id);
执行结果(同上):
执行计划:
可见不再有临时表的产生以及文件排序。
两次慢查询日志的比较:
# Time: 160718 15:28:05# User@Host: root[root] @ localhost [127.0.0.1] Id: 37# Query_time: 0.013001 Lock_time: 0.000000 Rows_sent: 200 Rows_examined: 6062SET timestamp=1468826885;SELECT first_name,last_name,COUNT(*) FROM film_actor INNER JOIN actor USING(actor_id) GROUP BY film_actor.actor_id;# Time: 160718 15:37:19# User@Host: root[root] @ localhost [127.0.0.1] Id: 37# Query_time: 0.006000 Lock_time: 0.000000 Rows_sent: 200 Rows_examined: 5862SET timestamp=1468827439;SELECT first_name,last_name,c.cnt FROM actor INNER JOIN(SELECT actor_id,COUNT(*) AS cnt FROM film_actor GROUP BY actor_id) AS c USING(actor_id);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
优化后的查询没有临时表的产生,时间效率更高,性能更好。
- 查询产生临时表的调优
- 查询产生临时表的调优
- 编译器产生的临时对象
- C++临时变量的产生
- 临时表和普通表日志量产生的对比
- Oracle中临时表产生过量Redo的说明
- Oracle中临时表产生过量Redo的说明
- Oracle中临时表产生过量Redo的说明
- Oracle中临时表产生过量Redo的说明
- mysql临时表产生的执行效率问题改进
- SQL临时表的创建+查询
- 临时表查询
- with as 将查询出来的临时表联合查询
- SqlServer_临时表查询和内联子查询的使用;
- C++讨厌的临时变量什么时候产生
- 关于c++临时对象产生的规则
- C++产生的临时对象引用
- vim产生的临时.swp文件
- iOS中打包.a静态库
- Java 导出Excel 和 打包导出
- 善用 PHP-FPM 的 slow log 分析问题
- css 教程
- 怎么判断一个构造函数有多少个实例
- 查询产生临时表的调优
- Android Animation 动画介绍和实现
- 在构造函数中需要初始化列表初始化的有如下三种情况
- AtCoder2341
- 迷宫问题—简单深度搜索
- Vue学习之路---No.4(分享心得,欢迎批评指正)
- 魔方阵
- LIMIT优化
- 《Mac-网络端口监听访问查询》---相关命令记录