仓酷云

标题: 利用子查询可提拔 COUNT DISTINCT 速率 50 倍 [打印本页]

作者: 因胸联盟 时间: 2015-1-16 14:07
标题: 利用子查询可提拔 COUNT DISTINCT 速率 50 倍
我们只需要把binlog文件反向执行，每个操作都执行逆操作即可。当然也不是所有的event都反转。Table_mapevent必须还是在Rows_log_event每个操作之前。很有效，但太慢

Countdistinct是SQL剖析时的祸端，因而它是我第一篇博客的不贰选择。

起首：假如你有一个年夜的且可以容忍不准确的数据集，那像HyperLogLog如许的几率计数器应当是你最好的选择。（我们会在今后的博客中谈到HyperLogLog。）但关于必要疾速、精准谜底的查询，一些复杂的子查询能够节俭你良多工夫。

让我们以我们一向利用的一个复杂查询入手下手：哪一个图表的用户会见量最年夜？
select
dashboards.name,
count(distincttime_on_site_logs.user_id)
fromtime_on_site_logs
joindashboardsontime_on_site_logs.dashboard_id=dashboards.id
groupbyname
orderbycountdesc

起首，我们假定user_id和dashboard_id上已设置了索引，且有比图表和用户数多很多的日记条目。

一万万行数据时，查询必要48秒。要晓得缘故原由让我们看一下SQL剖析：

点击图片可检察全图

它慢是由于数据库遍历了一切日记和一切的图表，然后join它们，再将它们排序，这些都在真实的group和分组和聚合事情之前。

先聚合，然后Join

group-聚合后的任何事情价值都要低，由于数据量会更小。group-聚应时我们不需利用dashboards.name，我们也能够先在数据库上做会萃，在join之前：
select
dashboards.name,
log_counts.ct
fromdashboards
join(
select
dashboard_id,
count(distinctuser_id)asct
fromtime_on_site_logs
groupbydashboard_id)aslog_counts
onlog_counts.dashboard_id=dashboards.id
orderbylog_counts.ctdesc

如今查询运转了20秒，提拔了2.4倍。再次经由过程剖析来看一下缘故原由：

点击图片可检察全图

正如计划的，group-聚合在join之前。并且，分外的我们能够使用time_on_site_logs内外的索引。

起首，减少数据集

我们能够做的更好。经由过程在全部日记表上group-聚合，我们处置了数据库中良多不用要的数据。Countdistinct为每一个group天生一个哈希——在本次情况中为每一个dashboard_id——来跟踪哪些bucket中的哪些值已反省过。

我们能够事后盘算差别，而不是处置全体数据，如许只必要一个哈希汇合。然后我们在此基本上做一个复杂的会萃便可。
select
dashboards.name,
log_counts.ct
fromdashboards
join(
selectdistinct_logs.dashboard_id,
count(1)asct
from(
selectdistinctdashboard_id,user_id
fromtime_on_site_logs
)asdistinct_logs
groupbydistinct_logs.dashboard_id
)aslog_counts
onlog_counts.dashboard_id=dashboards.id
orderbylog_counts.ctdesc

我们接纳外部的count-distinct-group，然后将数据拆成两部分分红两块。第一块盘算distinct(dashboard_id,user_id)。第二块在它们基本上运转一个复杂group-count。跟下面一样，最初再join。

点击图片可检察全图

呵呵，年夜发明：如许只必要0.7秒！这比下面的查询快28倍，比本来的快了68倍。

一般，数据巨细和范例很主要。下面的例子受害于基数中没几换算。distinct(user_id,dashboard_id)相对数据总量来讲数目也很少。分歧的对数越多，用来group和计数的独一数据就越多——价值便会愈来愈年夜。

下一次碰到长工夫运转的countdistinct时，实验一些子查询来减负吧。
优化的SQL查询算法，有效地提高查询速度

作者: 飘飘悠悠 时间: 2015-1-18 11:56
记得在最开始使用2k的时候就要用到这个功能，可惜2k没有，现在有了作解决方案的朋友会很高兴吧。

作者: 海妖 时间: 2015-1-26 12:51
where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

作者: 分手快乐 时间: 2015-2-4 19:05
需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。

作者: 小妖女 时间: 2015-2-10 06:04
原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！

作者: 简单生活 时间: 2015-3-1 00:24
如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录.

作者: 山那边是海 时间: 2015-3-10 11:19
如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录.

作者: admin 时间: 2015-3-17 07:08
对于微软系列的东西除了一遍遍尝试还真没有太好的办法

作者: 第二个灵魂 时间: 2015-3-24 01:58
其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！

欢迎光临仓酷云 (http://ckuyun.com/)