某大公司的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分割分布式存储，文章类似，还可以用读写分离等策略水平扩展数据库.
其他答案：
第四题：
主键的话是毫无疑问的，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。
第三题：
平常习惯就不太常用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作为驱动表，也应该能提高不少效率。
第一题：
用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是最保险的做法了，基本能保证速度最快。
第五题：
1、我把3个表合成一个表：Table（userId, userName，articleId, title, content，score）
理由：一个userName不占用很多存储空间，空间换取速度，如果有其他属性，可以分成用户表和文章表，甚至可以做一扩展属性表，把不常用的属性放入扩展表，减少查询数据的表连接，userName字段的变动不会很大，即使变Table不一定要跟着变，这样可以知道在发表该文章的时候userName是什么，如果一定要变建立userId的索引，update也是很高效率的。
2、根据用户的点击率和、登录频、文章点击率等高使用频率分级存储数据
3、建立相关查询的表索引，使用服务器缓存高使用频率的数据
上述答案的建议：
1首先3个表应该不能完全合并的，毕竟用户和文章是一对多的关系，
2索引，缓存数据库，分布式确实是第五题的通用解决方法，
3还有就是数据库的一些性能调优比如mysql的 table_cache key_buffer_cache，合理利用服务器超强的性能
第一题：
select username from user usr
left join article art on art.userid = user.userid
where art.userid is null;
