百度2008笔试题（关于SQL）

rel="File-List" href="file:///C:%5CDOCUME%7E1%5C%E4%B8%B0%E5%9B%BD%E6%A0%8B%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml">

题目：一个简单的论坛系统，以数据库储存如下数据：用户名，email，主页，电话，联系地址，发帖标题，发帖内容，回复标题，回复内容。每天论坛访问量300万左右，更新帖子10万左右。请给出数据库表结构设计，并结合范式简要说明设计思路。

 

1.设计目标：注册进入论坛后，得到最近N条主题模式下的帖子（只显示title，username，posttime），点击进入后显示主贴子和回复帖子，主贴主要有：title，username，content，posttime；恢复帖子主要有replytitle，replyusername，replycontent，replyposttime。能够发帖更新数据，插入数据等

 

2.功能实现：

（1）千万量级数据库的分页查询显示

（2）主题模式下的主贴和回复贴的显示

（3）主题模式下通过搜索name，title，posttime等参数来查询

 

3.数据库设计

使用MS SQL2000作为平台

CREATE DATABASE forum

go

use forum

 

CREATE TABLE [UsernameTable]

(

       --用户名（可以是中文）

       [username] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,

       --email

       [email] [varchar](255) NULL,

       --homepage

       [homepage] [varchar](255) NULL,

       --tel

       [tel] [varchar](20),

       --设置主键

       primary key (username)

)ON [PRIMARY]

go

 

CREATE TABLE [PostTable]

(

       --本表的id号，也是主键

       [grid] [int] IDENTITY (1,1) NOT NULL,

       --发贴人

       [username] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,

       --标题

       [title] [nvarchar](32) COLLATE Chinese_PRC_CI_AS NOT NULL,

       --内容

       [content] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,

       --回帖时间

       [posttime] [datetime] NOT NULL

)ON [PRIMARY]

go

 

CREATE CLUSTERED INDEX PK_IndexPostTime on PostTable(posttime)

CREATE NONCLUSTERED INDEX PK_IndexUsername on PostTable(username)

CREATE NONCLUSTERED INDEX PK_IndexTitle on PostTable(title)

go

 

CREATE TABLE [ReplyTable]

(

       --本表的id号，也是主键

       [grid] [int] IDENTITY (1,1) NOT NULL,

       --原帖id

       [postgrid] [int] NOT NULL,

       --回贴人

       [replyusername] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,

       --标题

       [replytitle] [nvarchar](32) COLLATE Chinese_PRC_CI_AS NOT NULL,

       --内容

       [replycontent] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,

       --回帖时间

       [replytime] [datetime] NOT NULL,

)ON [PRIMARY]

go

 

CREATE CLUSTERED INDEX PK_IndexReplyTime on ReplyTable(replytime)

CREATE NONCLUSTERED INDEX PK_IndexPostgrid on ReplyTable(postgrid)

Go

 

PostTable中的grid和Replytable中的postgrid是1:N关系，但是不是foreign key 关系，删除posttable中的帖子，其回帖仍然存在，但是无入口，找不到。

 

4.分页查询

CREATE PROCEDURE dt_pagination3

@tblName   varchar(255),       -- 表名

@strGetFields varchar(1000) = '*', -- 需要返回的列

@fldName varchar(255)='',      -- 排序的字段名

@PageSize   int = 10,          -- 页尺寸

@PageIndex int = 1,           -- 页码

@doCount bit = 0,   -- 返回记录总数, 非 0 值则返回

@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序

@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL   varchar(5000)       -- 主语句

declare @strTmp   varchar(110)        -- 临时变量

declare @strOrder varchar(400)        -- 排序类型

 

set nocount on              --使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息

 

if @doCount != 0

 begin

    if @strWhere !=''

    set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere

    else

    set @strSQL = "select count(*) as Total from [" + @tblName + "]"

end

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况

else

begin

 

if @OrderType != 0

begin

    set @strTmp = "<(select min"

set @strOrder = " order by [" + @fldName +"] desc"

--如果@OrderType不是0，就执行降序，这句很重要！

end

else

begin

    set @strTmp = ">(select max"

    set @strOrder = " order by [" + @fldName +"] asc"

end

 

if @PageIndex = 1

begin

    if @strWhere != '' 

    set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder

     else

     set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder

--如果是第一页就执行以上代码，这样会加快执行速度

end

else

begin

--以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["

    + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder

 

if @strWhere != ''

    set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["

        + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["

        + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["

        + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "

        + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder

end

end 

exec (@strSQL)

GO

set nocount off

 

5.添加测试数据

（1）往PostTable添加1,000,000条数据，每天发帖10,000条新贴，PostTable有100w条数据

set nocount on

declare @i int

declare @j int

 

set @j = 1

declare @post_time datetime

set @post_time = '2008-8-1'

 

declare @titles nvarchar(32)

declare @contents nvarchar(255)

declare @usernames nvarchar(20)

 

 

while @j<=100

begin

       set @i =1      

       while @i<=10000

       begin

              set @usernames = '丰国栋'+cast(@i as varchar(10));

              set @titles = '医学图像增强 '+cast(@i as varchar(10))

              set @contents = 'DR医学图像增强 '+cast(@i as varchar(10))

              insert into PostTable(username,title,content,posttime) values(@usernames,@titles,@contents,@post_time+@j-1+0.00009*@i)

              set @i = @i + 1

       end

       set @j = @j + 1

end

go

set nocount off

（2）平均每个帖子10条回复，共有1000w条数据

set nocount on

declare @i int

declare @j int

declare @post_time datetime

declare @titles nvarchar(32)

declare @contents nvarchar(255)

declare @usernames nvarchar(20)

declare @grid int

declare @gridtemp int

declare @k int

 

set @post_time = '2008-8-2'

set @j = 1

while @j<=100

begin

       set @i =1

       set @k = 0

       while @i<=10000

       begin

              set @grid = 1        

              while @grid <= 10

              begin

                     set @usernames = '丰国栋'+cast(@i as varchar(10));

                     set @titles = '医学图像增强 '+cast(@i as varchar(10))

                     set @contents = 'DR医学图像增强 '+cast(@i as varchar(10))

                     set @gridtemp = (@j-1) * 10000 + @i

                     rel="File-List" href="file:///C:%5CDOCUME%7E1%5C%E4%B8%B0%E5%9B%BD%E6%A0%8B%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"> insert into ReplyTable(postgrid,replyusername,replytitle,replycontent,replytime) values(@gridtemp,@usernames,@titles,@contents,@post_time+@j-1+0.00001*@k)                    

                     set @grid = @grid + 1

              end

              set @k = @k + 1                 

              set @i = @i + 1

       end

       set @j = @j + 1

end

go

set nocount off

 

6.测试结果

主题模式下对PostTable查询页

declare @d datetime

set @d=getdate()

exec dt_page PostTable,'grid,username,title,content,posttime','posttime',100,10000,0,1,''

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

go

516ms（最后一页）

 

exec dt_page PostTable,'grid,username,title,content,posttime','posttime',100,1,0,1,''

0ms

 

exec dt_page PostTable,'grid,username,title,content,posttime','posttime',100,5000,0,1,''

rel="File-List" href="file:///C:%5CDOCUME%7E1%5C%E4%B8%B0%E5%9B%BD%E6%A0%8B%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml">

186ms

 

用得到的grid查询ReplyTable得到回复贴

 

7.讨论

1.分页算法中的fldName只能是唯一的，在以时间为clusterindex时，最多一天有24*60*60*1000= 86400000个数据，如果一天处理的数据达到1亿，甚至更多，如何处理？

2.并发数据在插入时，有没有可能出现两个getdate（）是一样的情况？如果有，如何处理？

3.MS SQL2005中或更新的版本有没有比表变量，top等更好的分页方法？

4.表没有属性count数，不知道TableNum/pagesize的页表数，不然最多top（tablenum/2）。即上例最多186ms

exec dt_page PostTable,'grid,username,title,content,posttime','posttime',100,5000,0,1,''

rel="File-List" href="file:///C:%5CDOCUME%7E1%5C%E4%B8%B0%E5%9B%BD%E6%A0%8B%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml">

186ms
 

8．Reference

http://blog.csdn.net/chenjinjie/archive/2007/05/28/1628355.aspx

摘要：

（1）深入浅出理解索引结构

您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。

（2）何时使用聚集索引或非聚集索引

下面的表总结了何时使用聚集索引或非聚集索引（很重要）。

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

（3）索引使用的误区

1、主键就是聚集索引

2、只要建立索引就能显著提高查询速度

3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度