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

2012-10-24

某个大公司的sql面试题，自己不太会做有没有童鞋指点一下自己对sql的理解。用一句俗话说：人有七窍，已经通了

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

自己对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分割分布式存储，文章类似，还可以用读写分离等策略水平扩展数据库.

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

帅多，你的这个 1是什么意思呀?没见过，请给分析一下...
问：关系模式：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分割分布式存储，文章类似，还可以用读写分离等策略水平扩展数据库.
</div>
?

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

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是最保险的做法了，基本能保证速度最快。select userid from(select a.userid,count(1) articleqty, avg(c.score) scoreavgfrom user a,artical b,vote cwhere a.userid=b.useridand b.articleid=c.articleidgroup by a.userid) aa where articleqty>5 and scoreavg>100
三表关联会消除没有发表文章的userid，但是为了减少子查询的条数，还可以进一步改进：

select userid,scoreavg from(select aa.userid,avg(bb.score) scoreavgfrom(select a.userid,b.articleid,count(1) articleqtyfrom user a,artical bwhere a.userid=b.useridgroup by a.userid) aa,vote bbwhere aa.articleid=bb.articleidand aa.articleqty>5group by aa.userid) aaawhere aaa.scoreavg>100order by scoreavg desc

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

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

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。
46 楼 wps352 2010-08-21 mark 学习了。~~

热点排行