`
NiceKevin
  • 浏览: 11185 次
  • 性别: Icon_minigender_1
  • 来自: 厦门
社区版块
存档分类
最新评论

使用子查询可提升 COUNT DISTINCT 速度 50 倍

    博客分类:
  • SQL
 
阅读更多

英文原文:Use Subqueries to Count Distinct 50X Faster [url]https://periscope.io/blog/use-subqueries-to-count-distinct-50x-faster.html[/url]

出处:http://www.oschina.net/translate/use-subqueries-to-count-distinct-50x-faster

 

Count distinct是SQL分析时的祸根,因此它是我第一篇博客的不二选择。

首先:如果你有一个大的且能够容忍不精确的数据集,那像HyperLogLog这样的概率计数器应该是你最好

的选择。(我们会在以后的博客中谈到HyperLogLog。)但对于需要快速、精准答案的查询,一些简单的

子查询可以节省你很多时间。

Garfielt
Garfielt
翻译于 2年前

3人顶

 

 翻译的不错哦!

让我们以我们一直使用的一个简单查询开始:哪个图表的用户访问量最大?

1
2
3
4
5
6
7
select 
  dashboards.name
  count(distinct time_on_site_logs.user_id)
from time_on_site_logs 
join dashboards on time_on_site_logs.dashboard_id = dashboards.id
group by name 
order by count desc

 

首先,我们假设user_id和dashboard_id上已经设置了索引,且有比图表和用户数多得多的日志条目。

一千万行数据时,查询需要48秒。要知道原因让我们看一下SQL解析:

Explain Slow

它慢是因为数据库遍历了所有日志以及所有的图表,然后join它们,再将它们排序,这些都在真正的

group和分组和聚合工作之前。

Garfielt
Garfielt
翻译于 2年前

3人顶

 

 翻译的不错哦!

先聚合,然后Join

group-聚合后的任何工作代价都要低,因为数据量会更小。group-聚合时我们不需使用dashboards.name

,我们也可以先在数据库上做聚集,在join之前:

1
2
3
4
5
6
7
8
9
10
11
12
13
select
  dashboards.name,
  log_counts.ct
from dashboards
join (
  select
    dashboard_id,
    count(distinct user_id) as ct
  from time_on_site_logs 
  group by dashboard_id
as log_counts 
on log_counts.dashboard_id = dashboards.id
order by log_counts.ct desc

 

现在查询运行了20秒,提升了2.4倍。再次通过解析来看一下原因:

Explain Faster

正如设计的,group-聚合在join之前。而且,额外的我们可以利用time_on_site_logs表里的索引。

Garfielt
Garfielt
翻译于 2年前

2人顶

 

 翻译的不错哦!

首先,缩小数据集

我们可以做的更好。通过在整个日志表上group-聚合,我们处理了数据库中很多不必要的数据。

Count distinct为每个group生成一个哈希——在本次环境中为每个dashboard_id——来跟踪哪

些bucket中的哪些值已经检查过。

我们可以预先计算差异,而不是处理全部数据,这样只需要一个哈希集合。然后我们在此基础上做一个简

单的聚集即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
  dashboards.name,
  log_counts.ct
from dashboards 
join (
  select distinct_logs.dashboard_id, 
  count(1) as ct
  from (
    select distinct dashboard_id, user_id
    from time_on_site_logs
  as distinct_logs
  group by distinct_logs.dashboard_id
as log_counts 
on log_counts.dashboard_id = dashboards.id
order by log_counts.ct desc

 

我们采取内部的count-distinct-group,然后将数据拆成两部分分成两块。第一块计算

distinct (dashboard_id, user_id) 。第二块在它们基础上运行一个简单group-count。跟上面一样,

最后再join。

Explain Fastest

Garfielt
Garfielt
翻译于 2年前

2人顶

 

 翻译的不错哦!

呵呵,大发现:这样只需要0.7秒!这比上面的查询快28倍,比原来的快了68倍

通常,数据大小和类型很重要。上面的例子受益于基数中没多少换算。distinct (user_id, dashboard_id)

相对于数据总量来说数量也很少。不同的对数越多,用来group和计数的唯一数据就越多——代价便会越

来越大。

下一遇到长时间运行的count distinct时,尝试一些子查询来减负吧。

 

分享到:
评论

相关推荐

    使用Distinct查询.rar

    有时,可以使用子查询来代替`DISTINCT`,特别是在处理复杂查询时。例如,找出有多个员工的部门: ```sql SELECT department FROM employees WHERE department IN (SELECT department FROM employees GROUP BY ...

    分析MySQL中优化distinct的技巧

    这里的子查询`SELECT DISTINCT(nick) FROM user_access`起到了关键作用。MySQL的优化器将`DISTINCT`操作转换为`GROUP BY`,使得查询在利用索引分组后,仅扫描一次所需的`nick`值。在新的执行计划中,`Using index ...

    用Distinct在MySQL中查询多条不重复记录值,绝对的物有所值

    但这显然不是最优解,因为内部的子查询会被多次执行,降低查询效率。更合理的做法是先使用`GROUP BY`结合聚合函数来达到目的。 ### `GROUP BY`与`DISTINCT`的协同工作 在某些情况下,我们可能需要统计每个不同`...

    Oracle初级(子查询).doc

    这里,子查询`(SELECT deptno, COUNT(empno) ct FROM emp GROUP BY deptno)`统计每个部门的员工数,然后与dept表连接,显示部门名称和人数。 子查询有多种类型,包括单列子查询、单行子查询、多行子查询: - 单...

    【DISTINCT】优化之MySQL官方文档翻译

    5. **考虑替代方案**:在某些情况下,使用子查询或联接可能比直接使用`DISTINCT`更高效。 #### 六、结论 通过对MySQL处理`DISTINCT`优化的理解,我们可以更好地设计和调整查询语句,以提高查询性能。在实际应用中...

    数据库设计(包括select语句、子查询、语句嵌套)

    在本话题中,我们将探讨数据库设计的基础,特别关注SELECT语句、子查询以及语句嵌套这三个核心概念。 首先,数据库设计不仅仅是创建表格,更重要的是理解和分析业务需求,以构建一个能够支持高效数据存储、检索和...

    小度写范文【SQL中distinct的用法】mysql中distinct用法模板.pdf

    这时,你可以考虑使用子查询或者窗口函数(如`ROW_NUMBER()`或`RANK()`)来达到目的。在SQL Server中,有一种常见的做法是使用`FOR XML PATH`来将一列的多行内容拼接成一行。 总之,`DISTINCT`在SQL中是一个强大的...

    数据库 sql 习题

    我们需要从“产品”表中查询出价格高于产品名称为“一次性纸杯”的产品的记录,因此我们需要使用子查询来查询出产品名称为“一次性纸杯”的产品的价格,然后再查询出价格高于该价格的产品记录。 2. 现有顾客表 ...

    小度写范文【SQL中distinct的用法】mysql中distinct用法模板.docx

    这时,我们可以利用子查询或者窗口函数(如SQL Server中的`ROW_NUMBER()`)来实现这个目标。 总的来说,`DISTINCT`在SQL查询中是过滤重复数据的关键工具,无论是在数据分析、报表生成还是数据清洗过程中都十分常用...

    数据库管理与开发项目教程-教案-08项目四:使用T-SQL查询表数据-任务4子查询-实训XS.docx

    数据库管理与开发项目教程-教案-08项目四:使用T-SQL查询表数据-任务4子查询 知识点1:SELECT语句的书写格式 * SELECT [DISTINCT] 选择列表 [Into 新的表] From 表或视图 [Where 检索的条件] [Group by 字段名1 ...

    SQL数据的查询和更新实验报告报告.pdf

    - **子查询限制条件**:在主查询中嵌入子查询,满足特定条件 - **相关子查询**:子查询的结果取决于外部查询的每一行 - **不相关子查询**:子查询独立于外部查询,只执行一次 - **谓词子查询**: - **IN, NOT IN**...

    Mysql子查询IN中使用LIMIT应用示例

    这种重构使用了JOIN来连接原始的子查询结果,这可能在某些情况下提供更好的性能,因为它避免了在`IN`子句中使用`LIMIT`的限制。 此外,对于复杂的查询,考虑使用索引以提高查询速度也是至关重要的。确保`mapply`表...

    mysql数据查询操作-实验训练2.docx

    MySQL 数据查询操作实验训练 2 本实验训练涵盖了 MySQL ...* 了解如何使用子查询进行数据过滤 * 了解如何使用子查询进行数据聚合 通过本实验训练,可以掌握 MySQL 数据查询操作的基本技能,提高数据分析和处理能力。

    SQL select distinct的使用方法

    对于多个列的去重计数,可能需要采用其他策略,如创建临时表或子查询。 在使用 `DISTINCT` 时,需要注意的一点是,它必须放在 `SELECT` 语句的开头,不能与其他字段混合使用。例如,以下查询是错误的: ```sql ...

    oracle中使用group by优化distinct

    4. **使用`DISTINCT ON`(Oracle不支持,但其他数据库如PostgreSQL支持)**:虽然Oracle不直接支持`DISTINCT ON`,但可以尝试通过子查询或窗口函数模拟此功能。例如,你可以先用`ROW_NUMBER()`分配唯一的行号,然后...

    数据库查询汇总,多表联查,条件查询,子查询等

    数据库查询是数据库管理的核心操作,本篇将详细阐述在MySQL中进行各种查询的方式,包括基本查询、条件查询、范围查询、聚合与分组、排序、分页、链接、自关联以及子查询。 首先,基本查询涉及到`SELECT`语句的不同...

    SQLServer经典面试题.doc

    SQL Server经典面试题汇总 以下是对SQL Server经典面试...这些问题涵盖了SQL Server中的一些重要知识点,如嵌套语句、子查询、关联查询、NOT IN 操作符、COUNT 函数、DISTINCT 操作符、GROUP BY 语句、HAVING 子句等。

    数据库链接查询、子查询等数据查询的方法 课件

    本课件主要关注链接查询和子查询,这两种方法是数据库查询的基石,它们可以帮助我们处理复杂的查询需求。以下是对这些查询方法的详细解释。 首先,查询的基本结构包括`SELECT`、`FROM`、`WHERE`、`ORDER BY`和`...

    SQL50个查询命令

    - **子查询**: 子查询用于构建临时表,这里分别构造了两组子查询,一组为选修了课程“001”的学生及其成绩,另一组为选修了课程“002”的学生及其成绩。 - **连接查询**: 使用`AND`条件将两个子查询结果集进行连接,...

    50个常用sql语句

    - **子查询**: 使用子查询获取所有选过“叶平”老师课程的学生学号。 - **子查询结果作为条件**: 通过 `not in` 排除选过该老师的课程的学生。 ### 6. 查询选修了课程号为 '001' 同时也选修了课程号为 '002' 的学生...

Global site tag (gtag.js) - Google Analytics