|
?
某天，我们的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,
?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,
?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) = '',-- 页面显示记录数
@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,
????? 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,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了。
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
