工作笔记：sqlserver性能之缓存

某天，我们的DBA对我说，我们的读书频道搜索太慢了，IO比较大，优化一下吧。
打开profiler看一下，果然reads达到了2000多，有时还会冲到4000，是该优化一下了！
我们的读书频道，主要的数据都集中在t_book, 图书表，t_chapter 章节表，t_content 内容表,而搜索主要就是针对t_book表进行的，大概的

表结构如下：
Create table t_book (
bookid int identity(1,1) primary key,
bookname varchar(50), -- 书名
author varchar(50), -- 作者
classid int,-- 分类id
... -- 其他字段省略
...
...
)
-- 索引
create index ix_bookname on t_book(bookname)

搜索采用的是like，这是性能差的原因之一，搜索的存储过程如下：

/*
搜索图书
南国刀客
2007-09-27
*/
create procedure p_book_search
@bookname varchar(50) = '', -- 要搜索的书名
@page int = 1,-- 当前要显示的页面
@pagesize int = 10, -- 页面显示记录数
@total int = 0 output-- 返回总记录数
as

set nocount on

if( @bookname = '' )
return

set @bookname = '%' + @bookname + '%'

declare @tmptb table
(
rownum int identity(1,1),
bookid int
)

insert into @tmptb(bookid)
select bookid from t_book where bookname like @bookname

-- 符合条件的总记录数
set @total = @@rowcount

-- 查询当前要显示的记录
select a.* from t_book a inner join @tmptb b on a.bookid = b.bookid
where b.rownum > @pagesize * (@page - 1 ) and b.rownum <= @pagesize * @page

go

DBA的要求是不用全文索引，搜索又要准确，那就是说还的用like，咋办？
仔细分析一下我们这个读书频道的特点：
1.每天新增的书不多，大概10本，其他都是更新连载中的书；
2.t_book数据量大约10万。
3.读者每天搜索的书都比较集中，大部分是网络上流行的网络文学。

根据分析出来的这些结果，我决定缓存，大概的思路如下：

1.新建一个表t_search_keyword，存放搜索的关键词,结构如下:
 create table t_search_keyword
 (
 keyid int identity(1,1) primary key,
 keyword varchar(50), -- 要搜索的书
 rowcounts int -- 总记录数
 )

建立索引
create index ix_keyword on t_search_keyword(keyword)

2.再新建一个表t_search_index,存放搜索结果对应的bookid，并对其进行排序;
 create table t_search_index

 (
 rid int identity(1,1) primary key,
 keyid int , -- 对应 t_search_keyword 的keyid
 bookid int , -- 对应t_book的bookid
 rownum int -- 排序

 )

建立索引
create index ix_keyid on t_search_index(keyid)
create index ix_bookid on t_search_index(bookid)
create index ix_rownum on t_search_index(rownum)

3.当第二次再搜索这本书的时候，从表 t_search_index 里面取得第一次查询的数据。那么就可以把存储过程修改如下：
/*
搜索图书
南国刀客
2007-09-27
*/
create procedure p_book_search
@bookname varchar(50) = '', -- 要搜索的书名
@page int = 1,-- 当前要显示的页面
@pagesize int = 10, -- 页面显示记录数
@total int = 0 output-- 返回总记录数
as

set nocount on

if( @bookname = '' )
return

declare @keyid int

--查询关键词的keyid
select @keyid = keyid, @total = rowcounts from t_search_keyword where keyword = @bookname

-- 如果没有这个关键词，说明是第一次查询，插入一个关键词
if( @@rowcount = 0 )
begin
insert into t_search_keyword(keyword) values(@bookname)
set @keyid = @@identity -- 得到keyid
end

-- 通过keyid查询t_search_index 存放的结果集

-- t_search_index 有结果集，说明已经不是第一次查询
if exists(select top 1 * from t_search_index where keyid = @keyid )
begin

select a.* from t_book a
inner join t_search_index b on a.bookid = b.bookid
where b.keyid = @keyid
and b.rownum > @pagesize * ( @page - 1 )
and b.rownum <= @pagesize * @page

end
else
begin

-- t_search_index 没有结果集，说明是第一次查询

set @bookname = '%' + @bookname + '%'
declare @tmptb table
(
rownum int identity(1,1),
bookid int
)
insert into @tmptb(bookid)
 select bookid from t_book where bookname like @bookname

 -- 把数据保存到t_search_index 供下次查询
 insert into t_search_index(keyid,bookid,rownum) select @keyid,bookid,rownum from @tmptb

 -- 符合条件的总记录数
 set @total = @@rowcount

 -- 保存总记录数
 update t_search_keyword set rowcounts = @total where keyid = @keyid

 -- 查询当前要显示的记录
 select a.* from t_book a inner join @tmptb b on a.bookid = b.bookid
 where b.rownum > @pagesize * (@page - 1 ) and b.rownum <= @pagesize * @page

end

go

到此，优化已经差不多结束，说差不多，其实还剩下一个缓存期限的问题，这个缓存的数据要保留多久，我保留的是一天的数据，可以做一个

作业，在每天00：00 的时候清空t_search_index的数据。这样就ok了。