关于子查询和连接的趣味小题目

十一 6th, 2013

今天有位同学出来一个趣味小题。

A:
select title
from simplified
where id in (select id
                  from analysis
                  where word = ‘something’);
B:
select b.title
from  analysis a
join simplified b
on (a.id=b.id)
where a.word=’something’;
C:
select simplified.title
from  analysis
join simplified
on (analysis.id=simplified.id)
where analysi.word=’something’;

上面的3段sql,在MYSQL中执行,哪一个效率最优,请讲出原因

看到这个问题,我想起了之前作面试官的时候,也问过这里的问题。从另一个角度,我收集了大家面对这题目的时候一些思考,总结成下面的一个面试小故事。

这类题目真得蛮适合面试的时候考察对sql的掌握程度。

模拟一个场景,面试者:小白,面试官:小二,下面开始这段面试过程…..

小二:…. 我这里有个小问题,你看看(给出上面3段sql),balabala。。。。

小白:这2个表数据量如何了,join ,子查询的优化根据数据量可能还有所不一样吧。子查询适合外结果集大,子查询结果集小的情况,最好是能保证子查询所返回的结果集尽量的小。

小二:假设2表,数据量差不多,都是万条记录左右。

小白:如果是这种的话,A应该会慢一点吧。 如果是在mysql上执行的话,A中子查询语句会认为与外面的simolified表进行关联比较有效率。这样的话A其实就回被翻译成:

select title from simplified where exists (select simplified.title from analysis where word= ‘something’ and id =simplified .id);

这种in子查询的形式,在外部表(比如上面的simplified )数据量较大的时候效率是很差的.

小二: 恩,那么B和C之间有什么区别呢?

小白:B和C在我看来,应该是差不多的,其主要区别就是B采用了别名。但是我认为应该是没多大影响的。 

因为数据库本身执行时,都会再把表名给换成自己的别名。但我影响中好像记得有一条数据库的优化建议是尽量多表连接,最好是使用表的别名来引用列。

对了,有对id建索引吧。

小二: 2表id都是主键,这算是建了索引吗?

小白: 是的,主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引,所以主键也是一个特殊的索引。

小二:  恩,一张表只能有一个主键,是不是就可以说只能有一个唯一的索引呢?

小白:  不对,主键一定是唯一性索引,唯一性索引并不一定就是主键,主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引。

小二: 主键列和唯一索引列还有区别吗?

小白:应该有,我知道的主键列不允许空值,而唯一性索引列允许空值。一个表中可以有多个唯一性索引,但只能有一个主键。

小二: 恩,回到上面的问题,你记得多表连接,最好使用表的别名来引用列,哪你有想过会是什么原因导致的呢?

小白:我估计应该是Mysql内部处理逻辑有关吧,有可能是使用了别名,就会再建一个临时表放入内存,这样后面的命中会更高。

小二:  差不多吧,mysql引擎自动生成temp表,造成缓存的效果。从这个sql,你大致应该能明白它的意图了,你想想有没有更优的写法,可以试试看…

小白: 好的,我想想…..

小白:  

select b.title from
(select id from  analysis where word=’something’)a,
 simplified b
where a.id=b.id;

如果这么去写,应该是最快的。放弃子查询和join。

小二: 恩,不错…..

。。。。。。

如果我在面试中碰到这样的小白,绝对会留下深刻的影响。





除非注明,本站文章均为原创。本文基于 BY-NC-SA 协议进行授权,欢迎转载,演绎或用于商业目的,但是必须保留本文的署名 metaboy(包含链接).

本文链接地址: http://blog.wangyuxiong.com/archives/52069

订阅本站:http://www.wangyuxiong.com/feed

分类: 工作笔记 思考讨论 海量数据         标签: ,
  1. Seven
    十二 13th, 201318:20

    SELECT * FROM t1 LEFT JOIN t2 ON (t1.id = t2.id) WHERE t2.column2=”aaa”; 改为如下语句即可

    SELECT * FROM t1 LEFT JOIN t2 ON (t1.id = t2.id) and t2.column2=”aaa”;

    注意join 的时候 on 和where的区别,

    where是从两张表笛卡尔积以后筛选,

    on是根据条件做笛卡尔积。

    • metaboy
      十二 14th, 201300:35

      对,你解释的没错。
      SELECT * FROM t1 LEFT JOIN t2 ON (t1.id = t2.id) WHERE t2.column2=”aaa”这个语句拿出来,其实是为说明on子句,where子句的关系或者说是执行先后顺序。

  2. Seven
    十二 13th, 201318:18

    SELECT * FROM t1 LEFT JOIN t2 ON (t1.id = t2.id) WHERE t2.column2=”aaa” 改为如下SQL即可:
    SELECT * FROM t1 LEFT JOIN t2 ON (t1.id = t2.id) and t2.column2=”aaa”

    第一个是按照id join然后再where筛选,第二个是选择先筛选aaa然后id join。

    显然第一种写法中间表数据很大,第二个数据会很少。可以看执行计划即可。

  3. cfanbo
    十二 13th, 201314:52

    select title from simplified where exists (select simplified.title from analysis where word= ‘something’ and id =simplified .id);

    这个如何理解的?他们是id,不是title的,条件查询是主键的in来查询的.

    • metaboy
      十二 13th, 201316:33

      这应该是mysql内部的一个优化过程,当看到A这种子查询语句,会默认优化与外表关联比较的。exists 条件后面并不是返回simplified.title,返回值应该是True和False,他所带的子查询一般直接用 ‘SELECT *’ 因为给出列名也没多少意义,我上面是随便写的列名。

      EXISTS指定一个子查询,检测行的存在。这样基本上要扫描simplified全表一遍。

  4. 新浪微博rss
    十一 10th, 201300:28

    sql我只会基本了,join 这些还不会,最近要学学了。

    • sky
      十二 13th, 201311:13

      最后一个问题没明白,请教一下:
      select b.title from
      (select id from analysis where word=’something’)a,
      simplified b
      where a.id=b.id;

      select b.title from
      (select id from analysis where word=’something’)a
      join simplified b
      on a.id=b.id;
      有什么区别?效率上来说

      • 阿驹
        十二 13th, 201314:01

        我花了近半小时对这两条语句进行了验证。采用相同的两张表,一张表几万条数据,一张表几十万条数据,连接之后26,478,758条数据。

        使用逗号的查询方式,三次耗时(毫秒)分别为170880,165450,170886,平均169072毫秒。
        使用join on的方式,三次耗时(毫秒)分别为 189620,203670,167266,平均186852毫秒。

        也就是说逗号连接的方式比join的连接方式快17780ms,接近18秒。

        系统是windows8.1 ,sqlserver2008R2,同一台电脑本地数据库,执行期间均未做其他任何操作。

      • metaboy
        十二 13th, 201316:15

        其实这些sql并不能很好来解释这类问题,这个其实真实的意图是下面这样子的,对比外连接和等值连接的:比如说如果是下面的join方式
        SELECT * FROM t1 LEFT JOIN t2 ON (t1.id = t2.id) WHERE t2.column2=”aaa”;
        执行顺序:
        1:表t2依赖于表t1
        2:on条件决定了怎样来读取表t2,where条件对t2是没有用的
        3:如果在t1中的一行满足where条件,而t2没有,则会被填充null
        这样产生的结果则在过程中产生了很多null行的临时结果

        但是如果采用等值连接(也就是上面说的逗号连接)则会避免产生null的行,(t2.column2=”aaa”限制了t2),如下:
        SELECT * FROM t1, t2 WHERE t2.column2=”aaa” AND t1.column1=t2.column1;

        相当于把外连接转换为等值连接,少了很多行的扫描和判断。

无觅相关文章插件,快速提升流量