发帖    主题    评论    推荐    标签    作者    订阅    查搜    注册   登陆   关注
 
面向对象 设计模式 领域驱动设计 企业架构 框架 开发教程 微服务 CQRS 扩展性 并发编程 事件溯源 分布式 SOA

SQL的group聚合与嵌套循环子查询的性能分析

              
2016-05-31 11:39
赞助商链接

SQL经常使用嵌套循环的子查询,这样性能是慢的。

嵌套循环查询SQL如下:

SELECT
first_name, last_name,
(SELECT count(*)
FROM film_actor fa
WHERE fa.actor_id = a.actor_id)
FROM actor a


这会强迫数据库引擎进入一个嵌套循环查询,下面是伪代码演示:

for (Actor a : actor) {
output(
a.first_name,
a.last_name,
film_actor.where(fa -> fa.actor_id = a.actor_id)
.size()
}

对于每条actor记录,对其film_actor字段进行计数,这会产生许多film遍历。

相反,使用bulk聚合反而更好些:

SELECT
first_name, last_name, count(*)
FROM actor a
JOIN film_actor fa USING (actor_id)
GROUP BY actor_id, first_name, last_name


这种bulk聚合是搜集所有actor集合和收集所有film_actor集合,然后在内存中通过group融合merge它们,Oracle的执行方式大概如下:

-------------------------------------------------------------------
| Id | Operation | Name | A-Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 |
| 1 | HASH GROUP BY | | 200 |
|* 2 | HASH JOIN | | 5462 |
| 3 | TABLE ACCESS FULL | ACTOR | 200 |
| 4 | INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR | 5462 |
-------------------------------------------------------------------

注意,对于每个actor,有5462行filem_actor,我们join并group和汇聚它们得到最终结果,再看看之前第一个代码嵌套循环查询是如何执行的:

-----------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 |
| 1 | SORT AGGREGATE | | 200 | 200 |
|* 2 | INDEX RANGE SCAN| IDX_FK_FILM_ACTOR_ACTOR | 200 | 5462 |
| 3 | TABLE ACCESS FULL| ACTOR | 1 | 200 |
-----------------------------------------------------------------------

列Starts是说明搜集了所有200个actor之后的情况,在在行INDEX RANGE SCAN我们为每个actor开始子查询200次才能得到film_actor总数。

我们比较一下这两种方式哪个更快?使用group的聚合(Bulk aggregation)与使用嵌套循环的子查询哪个更快?

group聚合因为将所有数据加载到内存,所以耗费更多内存,但是算法复杂性低;而嵌套循环查询是直接从索引获取有信息,因此耗费内存少,但是有更高的算法复杂性(二次方程?)。

实际并不是这样(banq:数据库的复杂性和不确定性可见一斑了)

group聚合是线性的,但是按照O(M+N),其中M=actor数量而N=film_actor数量,嵌套循环不是这样二次方程,而是对数方程,是O(M log N),这里的log是对数的意思,我们其实不需要遍历整个索引来获得总数。

越高复杂性意味着不好,但是数据量比较小时并不是这样。

在对group聚合和嵌套循环对比性能如下,嵌套循环性能超过group聚合:

Nested select: +000000000 00:00:01.122000000
Group by : +000000000 00:00:03.191000000

Nested select: +000000000 00:00:01.116000000
Group by : +000000000 00:00:03.104000000

Nested select: +000000000 00:00:01.122000000
Group by : +000000000 00:00:03.228000000


如果我们使用一个衍生表来优化嵌套GROUP BY操作,代码如下:

SELECT
first_name, last_name, c
FROM actor a
JOIN (
SELECT actor_id, count(*) c
FROM film_actor
GROUP BY actor_id
) USING (actor_id)

经过测试,这个衍生表(derived table)性能最好:

Nested select : +000000000 00:00:01.371000000
Group by : +000000000 00:00:03.417000000
Group by in derived table: +000000000 00:00:01.592000000

Nested select : +000000000 00:00:01.236000000
Group by : +000000000 00:00:03.329000000
Group by in derived table: +000000000 00:00:01.595000000


Correlated Subqueries are Evil and Slow. Or are Th

上述测试基于Oracle数据库,如果你觉得每个数据库是个黑盒很难了解,同时因为引入join难以横向扩展,难以分区分表,那么可以考虑在应用层进行集合的联合比较。
见:Node.js CPU密集型的Join操作

性能调优      数据库      join     

赞助商链接

赞助商链接

返回顶部

移动版 关于本站 使用帮助 联系反馈 最佳分辨率1366x768
OpenSource JIVEJDON Powered by JdonFramework Code © 2002-20 jdon.com