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

今天有位同学出来一个趣味小题。 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

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

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。

小二: 恩，不错…..

。。。。。。

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