MySQL中distinct语句去查询重复记录及相关的性能讨论

更新时间:2023-07-11 19:50:29 阅读: 评论:0

MySQL中distinct语句去查询重复记录及相关的性能讨论在 MySQL 查询中,可能会包含重复值。这并不成问题,不过,有时您也许希望仅仅列出不同(distinct)的值。
关键词 DISTINCT ⽤于返回唯⼀不同的值,就是去重啦。⽤法也很简单:
SELECT DISTINCT * FROM tableName
DISTINCT 这个关键字来过滤掉多余的重复记录只保留⼀条。
另外,如果要对某个字段去重,可以试下:
SELECT *, COUNT(DISTINCT nowamagic) FROM table GROUP BY nowamagic
这个⽤法,MySQL的版本不能太低。
在编写查询之前,我们甚⾄应该对过滤条件进⾏排序,真正⾼效的条件(可能有多个,涉到同的表)是查询的主要驱动⼒,低效条件只起辅助作⽤。那么定义⾼效过滤条件的准则是什呢?⾸先,要看过滤条件能否尽快减少必须处理的数据量。所以,我们必须倍加关注条件的写⽅式。
假设有四个表: customers 、 orders 、 orderdetail 、 articles ,现在假设 SQL 要处理的问题是:找出
最近六个⽉内居住在Gotham 市、订购了蝙蝠车的所有客户。当然,编写这个查询有多种⽅法, ANSI SQL 的推崇者可能写出下列语句:
lect distinct c.custname
from customers c
join orders o
on o.custid = c.custid
join orderdetail od
did = o.ordid
join articles a
on a.artid = od.artid读《红楼梦》有感
where c.city = 'GOTHAM'
and a.artname = 'BATMOBILE'
dered >= somefunc
其中, somefunc 是个函数,返回距今六个⽉前的具体⽇期。注意上⾯⽤了 distinct ,因为考虑到某个客户可以是⼤买家,最近订购了好⼏台蝙蝠车。
暂不考虑优化器将如何改写此查询,我们先看⼀下这段代码的含义。⾸先,来⾃ customers 表的数据应只保留城市名为Gotham 的记录。接着,搜索 orders 表,这意味着 custid 字段最好有索引,否则只有通过排序、合并或扫描 orders 表建⽴⼀个哈希表才能保证查询速度。对 orders 表,还要针对订单⽇期进⾏过滤:如果优化器⽐较聪明,它会在连接( join )前先过滤掉⼀些数据,从⽽减少后⾯要处理的数据量;不太聪明的优化器则可能会先做连接,再作过滤,这时在连接中指定过滤条件利于提⾼性能,例如:
join orders o
on o.custid = c.custid
dered >= somefunc
注意,如果是:
铅笔英语怎么说left outer join orders o on
o.custid = c.custid
dered >= somefunc
此处关于left表的筛选条件将失效,因为是左外连接,左表的所有列都将出现在这次连接结果集中)。
即使过滤条件与连接( join )⽆关,优化器也会受到过滤条件的影响。例如,若 orderdetail 的主键为( ordid, artid ),即ordid 为索引的第⼀个属性,那么我们可以利⽤索引找到与订单相关的记录。但如果主键是( artid, ordid )就太不幸了(注意,就关系理论⽽⾔,⽆论哪个版本都是完全⼀样),此时的访问效率⽐( ordid, artid )作为索引时要差,甚⾄⼀些数据库产品⽆法使⽤该索引(注 3 ),唯⼀的希望就是在ordid 上加独⽴索引了。
连接了表 orderdetail 和 orders 之后,来看 articles 表,这不会有问题,因为表 order 包括 artid 字段。最后,检查 articles 中的值是否为 Batmobile 。查询就这样结束了,因为⽤了 distinct ,通过层层筛选的客户名还必须要排序,以剔除重复项⽬。
链球菌感染的原因避免在最⾼层使⽤ distinct 应该是⼀条基本规则。原因在于,即使我们遗漏了连接的某个条件, distinct 也会使查询 " 看似正确 " 地执⾏ —— ⽆可否认,发现重复数据容易,发现数据不准确很难,所以避免在最⾼层使⽤ distinct 应该是⼀条基本规则。发现结果不正确更难,例如,如果恰巧有多位
客户都叫 " Wayne " , distinct 不但会剔除由同个客户的多张订单产⽣的重复项⽬,也会剔除由名字相同的不同客户产⽣的重复项⽬。事实上,应该同时返回具唯⼀性的客户 ID 和客户名,以保证得到蝙蝠车买家的完整清单。
要摆脱 distinct ,可考虑以下思路:客户在 Gohtam 市,⽽且满⾜存在性测试,即在最近六个⽉订购过蝙蝠车。注意,多数
(但⾮全部) SQL ⽅⾔⽀持以下语法:
lect c.custname
from customers c
where c.city = 'GOTHAM'
and exists (lect null
from orders o,
orderdetail od,
articles a
where a.artname = 'BATMOBILE'
and a.artid = od.artid
did = o.ordid
and o.custid = c.custid
dered >= somefunc )
上例的存在性测试,同⼀个名字可能出现多次,但每个客户只出现⼀次,不管他有多少订单。有⼈认为我对 ANSI SQL 语法的挑剔有点苛刻(指 " 蝙蝠车买主 " 的例⼦),因为上⾯代码中customers 表的地位并没有降低。其实,关键区别在于,新查询中 customers 表是查询结果的唯⼀来源(嵌套的⼦查询会负责找出客户⼦集),⽽先前的查询却⽤了 join 。
这个嵌套的⼦查询与外层的 lect 关系⼗分密切。如代码第 11 ⾏所⽰(粗体部分),⼦查询参照了外层查询的当前记录,因此,内层⼦查询就是所谓的关联⼦查询( correlated subquery )。
此类⼦查询有个弱点,它⽆法在确定当前客户之前执⾏。如果优化器不改写此查询,就必须先找出每个客户,然后逐⼀检查是否满⾜存在性测试,当来⾃ Gotham 市的客户⾮常少时执⾏效率倒是很⾼,否则情况会很糟(此时,优秀的优化器应尝试其他执⾏查询的⽅式)。
lect custname
from customers
where city = 'GOTHAM'
and custid in
(lect o.custid
from orders o,
orderdetail od,
articles a
where a.artname = 'BATMOBILE'
and a.artid = od.artid
did = o.ordid
dered >= somefunc)
在这个例⼦中,内层查询不再依赖外层查询,它已变成了⾮关联⼦查询( uncorrelated subquery ),只须执⾏⼀次。很显然,这段代码采⽤了原有的执⾏流程。在本节的前⼀个例⼦中,必须先搜寻符合地点条件的客户(如均来⾃ GOTHAM ),接着依次检查各个订单。⽽现在,订购了蝙蝠车的客户,可以通过内层查询获得。
不过,如果更仔细地分析⼀下,前后两个版本的代码还有些更微妙的差异。含关联⼦查询的代码中,⾄关重要的是 orders 表中的 custid 字段要有索引,⽽这对另⼀段代码并不重要,因为这时要⽤到的索引(如果有的话)是表 customers 的主键索引。
你或许注意到,新版的查询中执⾏了隐式的 distinct 。的确,由于连接操作,⼦查询可能会返回有关⼀个客户的多条记录。但重复项⽬不会有影响,因为 in 条件只检查该项⽬是否出现在⼦查询返回的列表中,且 in 不在乎某值在列表中出现了⼀次还是⼀百次。但为了⼀致性,作为整体,应该对⼦查询和主查询应⽤相同的规则,也就是在⼦查询中也加⼊存在性测试:
lect custname
from customers
where city = 'GOTHAM'
猴子头饰and custid in
100字的日记
(lect o.custid
from orders o
dered >= somefunc
and exists (lect null
from orderdetail od,
articles a
where a.artname = 'BATMOBILE'
严遵and a.artid = od.artid
did = o.ordid))
或者
lect custname
读碑from customers
where city = 'GOTHAM'
三峡注释
and custid in
(lect custid
from orders
where ordered >= somefunc
and ordid in (did
from orderdetail od,
articles a
where a.artname = 'BATMOBILE'
and a.artid = od.artid)
尽管嵌套变得更深、也更难懂了,但⼦查询内应选择 exists 还是 in 的选择规则相同:此选择取决于⽇期与商品条件的有效性。除⾮过去六个⽉的⽣意⾮常清淡,否则商品名称应为最有效的过滤条件,因此⼦查询中⽤ in ⽐ exists 好,这是因为,先找出所有蝙蝠车的订单、再检查销售是否发⽣在最近六个⽉,⽐反过来操作要快。如果表 orderdetail 的 artid 字段有索引,这个⽅法会更快,否则,这个聪明巧妙的举措就会黯然失⾊。
每当对⼤量记录做存在性检查时,选择 in 还是 exists 须斟酌。
利于多数 SQL ⽅⾔,⾮关联⼦查询可以被改写成 from ⼦句中的内嵌视图。然⽽,⼀定要记住的是, in 会隐式地剔除重复项⽬,当⼦查询改写为 from ⼦句中的内嵌视图时,必须要显式地消除重复项⽬。例如:
lect custname
from customers
where city = 'GOTHAM'
and custid in
(lect o.custid
from orders o,
(lect did
from orderdetail od,
articles a
where a.artname = 'BATMOBILE'
and a.artid = od.artid) x
dered >= somefunc
did = o.ordid)
总结:保证 SQL 语句返回正确结果,只是建⽴最佳 SQL 语句的第⼀步。

本文发布于:2023-07-11 19:50:29,感谢您对本站的认可!

本文链接:https://www.wtabcd.cn/fanwen/fan/82/1091288.html

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。

标签:查询   条件   客户   过滤
相关文章
留言与评论(共有 0 条评论)
   
验证码:
推荐文章
排行榜
Copyright ©2019-2022 Comsenz Inc.Powered by © 专利检索| 网站地图