某个大公司的sql面试题，自己不太会做有没有童鞋指点一下

自己对sql的理解。用一句俗话说：人有七窍，已经通了六窍，有没有童鞋指点一下

问：关系模式：User（userId, userName）， Article（articleId, userId, title,   content），Vote（articleId, score），User为用户关系，Article为用户发表的文章关系，Vote为文章得票关系，title为文章标题、score为得票数。
（1）用SQL语言查询所有没发表过文章的用户名；
（2）用SQL语言查询得票数大于100的所有文章标题，按得票数倒序排列；
（3）用SQL语言查询出发表文章数大于5，文章平均得票数大于100的用户名，按平均得票数倒序排列；
（4）设计这些表的主键、外键和索引，并指出上面三个查询所使用的索引。
（5）当用户数超过1000万，文章数超过1亿时，如何考虑存储及性能的改进和优化？

 

答： 

 

1 select * from User where useid  not in(select userid from Article);
2 select title from article   inner  join vote  on article.articleid=vote.aritcleid and  vote.score>100 order by vote.score asc;
3有点不太会，下面胡乱乱写了一通

3 select * from user where userid in(select userid from Article inner join vote on article.articleid = vote.articleid group by userId  having avg(score)>100) group by userid having count(*) >5;

4主键外键应该很简单，索引第一个应该是userid,第二个是articleid 和score，第三个应该是articleid和 userid

5用户数按照id分割分布式存储，文章类似，还可以用读写分离等策略水平扩展数据库.

评论

46 楼 wps352 2010-08-21  

mark 学习了。~~

45 楼 alvin969 2010-08-19  

第四题：
主键的话是毫无疑问的，user表里的userid,article表里的articleid，vote表里的articleid。
一般来说，在设计主键时，最好采用字符型的．不采用自动递增，在新增记录时，系统生成主键值。而且，主键最好不具有任何实际意义，因为带有实际意义的字段，还是存在被修改的可能性．而对于主键最大的忌讳就是修改主键，这可能会导致非常严重的不可估计的后果。
外键的话就是article表里的userid，vote表里的ariticleid。

建立索引的时候要注意，复合索引对多条件查询的速度提速是很明显的，但是用不好的话，不但对sql查询的速度没有提升，还会拖慢数据插入的速度。当数据量达到100万的时候，复合索引甚至会成倍的拖慢插入速度。比如article表中，建立(articleid,userid)索引，必须同时使用两列查询条件，才能使用复合索引，用userid关联user表和article表时，就不会走索引。
同理，SQL Server里面的聚类索引也要慎用。索引递增插入还好，否则就是悲剧了。

唯一性索引是效率最高的。

个人认为，user下userid列建立一个索引，article表建立两个索引，一个是articleid，一个是userid，vote建立一个索引，是articleid。

44 楼 alvin969 2010-08-19  

然后是第三题：

平常习惯就不太常用having，跟前面不用in是一样的道理，having的效率总不会比where条件更快。语句如下：

select userid from(
select a.userid,count(1) articleqty, avg(c.score) scoreavg
from user a,artical b,vote c
where a.userid=b.userid
and b.articleid=c.articleid
group by a.userid
) aa where articleqty>5 and scoreavg>100

三表关联会消除没有发表文章的userid，但是为了减少子查询的条数，还可以进一步改进：

select userid,scoreavg from(
select aa.userid,avg(bb.score) scoreavg
from(
select a.userid,b.articleid,count(1) articleqty
from user a,artical b
where a.userid=b.userid
group by a.userid
) aa,vote bb
where aa.articleid=bb.articleid
and aa.articleqty>5
group by aa.userid
) aaa
where aaa.scoreavg>100
order by scoreavg desc

这样会根据发表文章数大于5做一个初步过滤，减小驱动表的数据量。如果大量存在非活跃用户，这种筛选还是能提速不少的。当然，最外面的一层查询可以改成having。

还有一种情况，就是如果没有人评分过的文章就在vote表中添加记录，而且大量存在未评分文章，那么vote表的数量就会比article小很多，可以使用第一个SQL，三表关联，以vote作为驱动表，也应该能提高不少效率。

43 楼 alvin969 2010-08-19  

忍不住来叨叨两句，有不对的大家指点哈！

第一题：
用not in还是not exists快，这要取决于不同数据库不同sql了。就此题来说，在SQL Server中两者是一样快的，正好有现成数据，刚试验了一下，user表跟article表各五万条数据，not in和not exists的写法运行时间均是五秒。分析执行计划也是一样的。主要的时间代价花费到了三个地方：两个表的索引扫描约是55%，多线程的并行分拆及合并11%，哈希匹配14%。看了一下执行计划，SQL Server对这两者皆做了优化，主要工作还是在索引和建立hash关系上，于是就有了第三种写法：

select count(userida) from(
select a.userid userida,b.userid useridb from user a left join article b on a.userid=b.userid
) aa where useridb is null

试了试，跟not in, not exists执行计划基本一致，运行时间也是一样的。

在oracle下就复杂多了，RBO还是CBO、表的大小都有可能改变执行计划。在基于规则的RBO优化器下，exists和in的执行计划是一致的，跟 not exists, in ,not in执行计划都不一样，其中exists, not exists使用了不同的hash计算，not in是效率最低的，用的是filter，要做笛卡尔积再用条件过滤，巨慢。不过通过加HINT，可以选择合适的执行计划，这点也是我喜欢oracle不喜欢sql server的一个重要原因，在上百行的复杂sql的优化中很是有用。

综上，写not exists是最保险的做法了，基本能保证速度最快。

42 楼 cxr1217 2010-08-19  

kakaluyi 写道

vision2000 写道

我是来关注第五题的，前面的题目没兴趣

1、我把3个表合成一个表：Table（userId, userName，articleId,  title,   content，score）
   理由：一个userName不占用很多存储空间，空间换取速度，如果有其他属性，可以分成用户表和文章表，甚至可以做一扩展属性表，把不常用的属性放入扩展表，减少查询数据的表连接，userName字段的变动不会很大，即使变Table不一定要跟着变，这样可以知道在发表该文章的时候userName是什么，如果一定要变建立userId的索引，update也是很高效率的。
2、根据用户的点击率和、登录频、文章点击率等高使用频率分级存储数据
3、建立相关查询的表索引，使用服务器缓存高使用频率的数据

   恩面试完了，没有sql的问题。
谢谢大家的答复，这个朋友的建议
1首先3个表应该不能完全合并的，毕竟用户和文章是一对多的关系，
2索引，缓存数据库，分布式确实是第五题的通用解决方法，
3还有就是数据库的一些性能调优比如mysql的 table_cache key_buffer_cache，合理利用服务器超强的性能

昨天问到一个数据库连接池的设计问题和一些优化设计问题，过段时间再和大家探讨一下

我也感觉3个表合成一个是不合理的。
楼主，正确答案没有是么？或者你可以整理一下你的问题，发表一个正确答案来结贴啊。第五个问题，用分区的方式来解决应该可以吧。
mysql里面倒是可以的，使用用户的id或者名字来进行分区
article 也用分区，也可以使用articleid和userid来分区

41 楼 kakaluyi 2010-08-19  

vision2000 写道

我是来关注第五题的，前面的题目没兴趣

1、我把3个表合成一个表：Table（userId, userName，articleId,  title,   content，score）
   理由：一个userName不占用很多存储空间，空间换取速度，如果有其他属性，可以分成用户表和文章表，甚至可以做一扩展属性表，把不常用的属性放入扩展表，减少查询数据的表连接，userName字段的变动不会很大，即使变Table不一定要跟着变，这样可以知道在发表该文章的时候userName是什么，如果一定要变建立userId的索引，update也是很高效率的。
2、根据用户的点击率和、登录频、文章点击率等高使用频率分级存储数据
3、建立相关查询的表索引，使用服务器缓存高使用频率的数据

   恩面试完了，没有sql的问题。
谢谢大家的答复，这个朋友的建议
1首先3个表应该不能完全合并的，毕竟用户和文章是一对多的关系，
2索引，缓存数据库，分布式确实是第五题的通用解决方法，
3还有就是数据库的一些性能调优比如mysql的 table_cache key_buffer_cache，合理利用服务器超强的性能

昨天问到一个数据库连接池的设计问题和一些优化设计问题，过段时间再和大家探讨一下

40 楼 vision2000 2010-08-19  

我是来关注第五题的，前面的题目没兴趣

1、我把3个表合成一个表：Table（userId, userName，articleId,  title,   content，score）
   理由：一个userName不占用很多存储空间，空间换取速度，如果有其他属性，可以分成用户表和文章表，甚至可以做一扩展属性表，把不常用的属性放入扩展表，减少查询数据的表连接，userName字段的变动不会很大，即使变Table不一定要跟着变，这样可以知道在发表该文章的时候userName是什么，如果一定要变建立userId的索引，update也是很高效率的。
2、根据用户的点击率和、登录频、文章点击率等高使用频率分级存储数据
3、建立相关查询的表索引，使用服务器缓存高使用频率的数据

39 楼 finux 2010-08-18  

Mrpublic 写道

finux 写道

呵呵。。。发下我的想法，不一定正确哦~
1.
select username from user usr
left join article art on art.userid = user.userid
where art.userid is null;

可以把On 与 where 在一起用哦？？ 求解

我只见过... left join ... on ...没有见过 后面还有一个where,
本人菜鸟，给解...

你试试就知道了，嘻嘻。。。
我天天写这样的SQL呀~

38 楼 tianhewulei 2010-08-18  

not in还是少用，个人感觉很慢... 左连接之后isnull应该比not in会好很多。

37 楼 pollyduan 2010-08-18  

reilost说的难道是BD?

36 楼 mogui258 2010-08-18  

都没说第五题啊，求解。

35 楼 dhj1986 2010-08-18  

讨论一下最后一个问题吧

34 楼 longhua828 2010-08-18  

哥们第一个就错了，人要的是  用户名 

33 楼 nashuoliqu 2010-08-18  

<div class="quote_title">kakaluyi 写道</div>
<div class="quote_div">
<p><span>自己对sql的理解。用一句俗话说：人有七窍，已经通了六窍，有没有童鞋指点一下<img src="/images/smiles/icon_smile.gif" alt=""></span></p>
<p><span>问：关系模式：User（userId, userName）， Article（articleId, userId, title,   content），Vote（articleId, score），User为用户关系，Article为用户发表的文章关系，Vote为文章得票关系，title为文章标题、score为得票数。<br>（1）用SQL语言查询所有没发表过文章的用户名；<br>（2）用SQL语言查询得票数大于100的所有文章标题，按得票数倒序排列；<br>（3）用SQL语言查询出发表文章数大于5，文章平均得票数大于100的用户名，按平均得票数倒序排列；<br>（4）设计这些表的主键、外键和索引，并指出上面三个查询所使用的索引。<br>（5）当用户数超过1000万，文章数超过1亿时，如何考虑存储及性能的改进和优化？</span> </p>
<p> </p>
<p>答： </p>
<p> </p>
<p><span>1 select * from User where useid  not in(select userid from Article);<br>2 select title from article   inner  join vote  on article.articleid=vote.aritcleid and  vote.score&gt;100 order by vote.score asc; <br>3有点不太会，下面胡乱乱写了一通</span></p>
<p><span>3 select * from user where userid in(select userid from Article inner join vote on article.articleid = vote.articleid group by userId  having avg(score)&gt;100) group by userid having count(*) &gt;5;</span></p>
<p><span>4主键外键应该很简单，索引第一个应该是userid,第二个是articleid 和score，第三个应该是articleid和 userid</span></p>
<p><span>5用户数按照id分割分布式存储，文章类似，还可以用读写分离等策略水平扩展数据库.</span></p>
</div>
<p> </p>

32 楼 spell 2010-08-18  

msi110 写道

貌似大家都关心些SQL语句去了？
还是应该多讨论一下后面两个问题吧...............

恩，围观中，我只知道根据ID mod 下，然后找到相应的表去取数据，如userid %10 ，如果=1，就放在 user_1表，=2就在user_2中。不过这样做统计之类的话，就比较折腾了，大家有什么好的方案不？

31 楼 msi110 2010-08-18  

貌似大家都关心些SQL语句去了？
还是应该多讨论一下后面两个问题吧...............

30 楼 lirong1978 2010-08-18  

只要查出东西出来就行了吧，考虑用这个用那个累不累啊

29 楼 icefishc 2010-08-18  

Mrpublic 写道

lerous 写道

kakaluyi 写道

谢谢，第3个题目看来只要自己好好再想想就出来了，
exists在写hql和jdbc的时候从来没有用过，据说少量结果集用exists,大量结果集用in，再研究研究

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in；反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists

请问： 你说的这个: 主查询中表较大又有索引时应该用In;反之外层主查询记录较少，子查询表大又有索引时用exists是有实践证明 ，还是某本书某个老师说的

这种东西基本都是误传。

28 楼 runshine 2010-08-18  

shaobaitou 写道

建议count(*)少用,用count(userid)之类的、

这种说法似乎是误传...

27 楼 Mrpublic 2010-08-18  

facade 写道

查询:

(1)
SELECT U.USERNAME FROM USER U WHERE NOT EXISTS
(SELECT 1 FROM ARTICLE A
 WHERE U.USERID = A.USERID) 
 

帅多，你的这个 1是什么意思呀?没见过，请给分析一下...