Help me!海量数据多表联合查询的问题!!

guxincao11 2006-07-14 09:24:14
现有一个文章数据库,要实现查询指定记录数的数据(如select top 200),又要查询与其相关的表,最后实现输出指定记录的功能。(问题有点长,但是描述很详细,希望高手耐心看完并给与解答,谢谢!)
与之相关的数据库结构如下:

作品表:books(bookid<作品ID>,btitle<作品名称>,classid<作品类别ID>,hits<作品点击数>)记录数 万级
分卷表:bookj(jid<分卷ID>,jtitle<分卷名称>,bookid<分卷所属作品ID>) 记录数 十万级
章节表:bookz(zid<章节ID>,ztitle<章节名称>,bookid<章节所属作品ID>,jid<章节所属分卷ID>,updates<本章更新时间>) 记录数 百万级
分类表:class(classid<分类ID>,classname<分类名称>)

(主键、索引都是默认id,递增<如bookid,jid,zid,classid 都是主键和索引?>,其他字段均未做任何索引或优化等)

具体描述:要查询按点击数(hits)降序的前200部作品,并显示

作品名称(btitle),所属分类(classname,class.classid=books.classid),该作品最新章节(ztitle,bookz.bookid=books.bookid,按updates降序),最新章节所属分卷(jtitle,bookj.jid=bookz.jid)

要求输出格式如下(类似):

分类名称 作品名称 分卷名称 章节名称 点击数
TestClass1 TestBook1 Testjuan1 TestChap1 100
TestClass2 TestBook2 Testjuan2 TestChap2 90
TestClass3 TestBook3 <NULL> <NULL> 80
TestClass4 TestBook4 Testjuan3 TestChap3 70

其中Books表里面的内容,对应的Boosj和Bookz里面可能找不到内容(因为该作品还没有章节),但是又要求输出(即使是空值),这样的话,我原先的sql语句(在底下)就不行了(因为过滤了空记录),我不知道left join 或者right join怎么用……

<----
原先用的是,嵌套循环,即,查询作品->查询所属分类->查询最新章节->查询其所属分卷,
用while循环,嵌套查询,输出(我用asp编写的)。
发现当要显示的记录数较大(200)的时候,速度很慢,效率太低(mssql占用cpu近100%,几乎死掉,最后用时近30秒),不得不放弃了。还有,随着要查询的记录增大,耗时几乎平方级增加!

然后改写后,大致如下
select top 200 a.bookid,a.btitle,a.classid,a.hits,b.jtitle,b.jid,c.ztitle,d.classname from books as a join bookj as b on a.bookid=b.bookid join bookz as c on a.bookid=c.bookid join class as d on a.classid=d.classid where zid = (select top 1 zid from bookz as c where c.bookid=a.bookid order by c.updates desc,c.zid desc) and b.jid = c.jid order by a.hits desc

---->

当查询较大记录时(同样200),效率提高很多(用时3-4秒),而且不管要查询的记录数多少,时间都差不多。

已经优化不少了,但是个人觉得远远不够!!(因为结果是秒级,而不是毫秒级的~~)我对sql语句研究不深,以上代码仅仅是经过多番网上搜索相关资料总结得来的,现学现用的,应该存在不少问题或者弯路。我发现没有经过系统的学习,自行泛泛搜索、研究是一件极度不明智的行为。有时候高手的一句有针对性的指点可以事半功 N 倍!!!

还有,因为要做个排行榜,所以不只是按照hits排序,也有其它的如favs(收藏数)排行,只是我没有列举而已。查询后生成Html(总共14个文件,就是14个排行榜),判断后,用For Next循环14次,总耗时 75 秒 左右!!我觉得效率太低了,我知道关键就是那个很长的sql语句select那里很费时(那个For Next具体实现方法不在讨论范围),但是又不知如何优化???

因此,在此恳请高手指点一下,如何设计代码,或者改动数据库,可以符合我的要求,以及效率可以更高一些??用mssql存储过程最好。而且最好能给出asp的调用代码(我用asp写的程序)
...全文
812 28 打赏 收藏 转发到动态 举报
写回复
用AI写文章
28 条回复
切换为时间正序
请发表友善的回复…
发表回复
tigerjacky 2006-08-06
  • 打赏
  • 举报
回复
mark
guxincao11 2006-07-28
  • 打赏
  • 举报
回复
要分你就拿去吧。。。。
原以为我是初学者,来CSDN学习的,没想到,很多人眼睛就盯着那些分,那些很虚的东西。。。
我故意不结帖?我有必要吗?不结帖貌似会被强制结帖并扣什么信誉分的(我刚刚知道)。。虽然我都不在意这些。。。
算了,自己没技术,怪不得别人。。。
再次感谢你们这些高手的指点!!
jianqiliu999 2006-07-28
  • 打赏
  • 举报
回复
呵呵,都是道上混的,谅解谅解!
guxincao11 2006-07-28
  • 打赏
  • 举报
回复
To:zhangshenghua(张胜华)
====================
把你的“优化”贴出来啊~~~~~~牛皮佬!
你能写出更快的,我就跟你混了,NND。
====================
晕了,今天进来转转,竟然发现你又不满了。。。不知我哪里得罪你了,你说话如此“损”……
这个代码已经没有用了,因为我重新设计了数据库,把最新章节存入books表了,免得每次都查询那个海量表,毕竟增加章节不是特别频繁,一天有个几十上百的就不错了。。。
至于我以前的思路,我也不知道是什么思路,具体代码也不贴了,免得怡笑大方。
我也没说你的方法就不好了,其实是我还不是很理解你的思路。。。当时只好按照自己的思路随便写了——(都是菜鸟的理解,你是高手看了一笑而过吧。。)
我吹牛皮?算了,算我吹牛皮了。。。讨论这个已经没有意义了,麻烦你“大人有大量”,不要再计较了,OK?
我是菜鸟,希望能跟各位前辈高手多学学。。。

========================
最后告诉大家一声,其实 zhangshenghua(张胜华) 是高手,而且很热心,虽然他对我有意见。。。只要你的脸皮可以像我一样厚,不对某些人的某些话在意,你是可以学到很多的。。。。
guxincao11 2006-07-28
  • 打赏
  • 举报
回复
靠,今天在百度搜了老半天,终于知道怎么结帖了
zhangshenghua 2006-07-28
  • 打赏
  • 举报
回复
借口不结帖,在CSDN可能不受欢迎。这是别人说的,我个人不在意(除了最近一个月,我很少在CSDN混,尽管已经注册好几年了)。
zhangshenghua 2006-07-19
  • 打赏
  • 举报
回复
把你的“优化”贴出来啊~~~~~~牛皮佬!
你能写出更快的,我就跟你混了,NND。
guxincao11 2006-07-19
  • 打赏
  • 举报
回复
To:zhangshenghua(张胜华)
==================
1)从SQL语句的执行流程分析,你的不可能更快,尽管你那个忽略了没有章节的书(我的符合要求,没有忽略,这会增加一些开销),因为你是最后才排序取前200的;
2)查询策略与索引紧密相关,我的查询要求你加两个索引,你加了没有?加与不加差别可大了;
3)我也不知道怎么给分(从没问过问题,只回答过问题);
==================
我现在的不是上面我给出的那个了,又经过一些自认为的优化,反正最后查询都会稍快一些的。。。我也不知道哪里的问题。。。索引我应该加了的(就是不知加得对不对:( )
感谢你的帮助,现在已经没空在这个问题上面纠缠太久。。。再次感谢!!!
zhangshenghua 2006-07-18
  • 打赏
  • 举报
回复
1)从SQL语句的执行流程分析,你的不可能更快,尽管你那个忽略了没有章节的书(我的符合要求,没有忽略,这会增加一些开销),因为你是最后才排序取前200的;
2)查询策略与索引紧密相关,我的查询要求你加两个索引,你加了没有?加与不加差别可大了;
3)我也不知道怎么给分(从没问过问题,只回答过问题);
guxincao11 2006-07-18
  • 打赏
  • 举报
回复
靠,不知怎么给分,哪位教教我???
guxincao11 2006-07-18
  • 打赏
  • 举报
回复
老大,怎么结贴或者给分啊???再试一次,晕了。。。
guxincao11 2006-07-18
  • 打赏
  • 举报
回复
To:zhangshenghua(张胜华)
=================================
靠,帖子也不来结了?
帮你解决问题了,完了还要说我的比你的那个还慢,I服了YOU。

拉你进黑名单。
=================================
这话有些严重了。。。
问题还是没有很好地解决啊。。。你的的确比我的要慢的,我也不知道怎么回事。。。
没时间仔细测试了,还有别的东西要做。。。
很感谢你了!!!不知道我有多少分,怎么给:( 我在CSDN没混过多久,大多只是看贴,自己很少发文和回帖的。。。不好意思了。。。随便给你一些分吧。。。
帖子就先不结了(其实我也不怎么懂得结贴……),看看还有没有人有更好的思路的。。。
=================
看了一下,我只有100分:( 给你们70分了,手头留30分以备不时之需 :)
zhangshenghua 2006-07-18
  • 打赏
  • 举报
回复
靠,帖子也不来结了?
帮你解决问题了,完了还要说我的比你的那个还慢,I服了YOU。

拉你进黑名单。
wfliu 2006-07-16
  • 打赏
  • 举报
回复
好长
zhangshenghua 2006-07-16
  • 打赏
  • 举报
回复
另外,别忘了我要你加的两个索引。
如果还不能满足要求,只能重新设计表结构和关系了,这个我就不帮你啦(收费,嘿)。
zhangshenghua 2006-07-16
  • 打赏
  • 举报
回复
若更新时间相同,则取zid最大的(经复杂运算后,必须在最后重新排序,因此末尾加了order by子句):

select c.classname, t.btitle, j.jtitle, z.ztitle, t.hits
from (select t.bookid, t.btitle, t.classid, t.hits, max(convert(varchar, z.updates) + z.zid) updates_zid
from (select top 200 bookid, btitle, classid, hits from books order by hits desc) t
left join bookz z on z.bookid = t.bookid
group by t.bookid, t.btitle, t.classid, t.hits) t
left join bookz z on t.bookid = z.bookid and t.updates_zid = (convert(varchar, z.updates) + z.zid)
left join bookj j on z.bookid = j.bookid and z.jid = j.jid, class c
where t.classid = c.classid
order by t.hits desc;


我能写出的最快、最简单、最紧凑的,且完全符合要求的代码就是这样,没有优化空间了。
不要同时两个大规模查询,这样后执行的一定吃亏,因为系统未来得及释放被第一个查询消耗的资源。


自动计算一个查询的时间消耗(毫秒级):

declare @d as datetime;
declare @c as int;
select @d = getdate();

//执行查询SQL;

select @c = datediff(ms, @d, getdate());
select @c milliseconds;
guxincao11 2006-07-16
  • 打赏
  • 举报
回复
To:Athoncj(阿龙)
=====================
你的方法还未测试,明天再看,很晚了,睡觉先……^_^
guxincao11 2006-07-16
  • 打赏
  • 举报
回复
To:zhangshenghua(张胜华)
====================
初步测试了你的方法,发现会有作品重复(所影响的行数为 217 行),可能是因为有章节的updates时间是相同的……
还有,我今天研究了一天的代码,跟你的比较了一下(一起执行),发觉貌似我的效率更高一些?(20多万章节,占用数据库空间1.7G!总时间2秒<查询分析器右下角得到的,我不知道在哪里看,汗!>)

我的:查询1:查询成本(相对于批处理):14.43%
你的:查询2:查询成本(相对于批处理):85.57%

只是在我的代码中,假如最新章节为空(新书,未有任何章节),则该作品不显示(与我想要的结果稍有出入)
LouisXIV 2006-07-15
  • 打赏
  • 举报
回复
你把现存索引都改为降序试试,并把books表的索引的hits改为前导列,其后再排bookid等等
guxincao11 2006-07-15
  • 打赏
  • 举报
回复
To:zhangshenghua(张胜华)

很晚了,明天再测试你的方法,先谢过……

睡觉去了^_^
加载更多回复(8)
本课程目前总计105课时,并且不定期的进行新知识点的补充,目的是打造一部围绕MySQL的全体系课程。课程涵盖11大章节,分别是:第1章基础&技巧:这部分的重点是会讲解一些容易被开发人员忽略的技巧,例如utf8mb4字符集问题、如何使用外部临时表提高查询效率、快速创建同结构表及快速复制数据、截断表和删除数据使用和差异、以及怎样使用help语句查看帮助文档。第2章六大数据类型:这部分的重点是对MySQL的8种数字类型、5种日期和时间类型、10种字符串类型、枚举类型、集合类型和时间戳类型的区别和使用进行深入讲解。第3章数据库函数大全:MySQL中有上百种函数之多,使用函数可以快速的解决我们很多开发问题,但是由于我们掌握的函数不够多,往往没有办法实际应用,本章节重点是让你掌握更多好用而你不知道的函数使用。第4章数据库引擎精讲:本章节带您深入到MySQL的体系架构,深入理解innoDB、MyISAM、MEMORY、ARCHIVE引擎的区别和使用原则。第5章数据库索引精讲:索引是保障我们查询效率的重点,本章节从逻辑存储和物理存储的底层入手,深入剖析索引的存储结构和查找方法,掌握聚簇索引、非聚簇索引、前缀索引等的存取原理和使用技巧。第6章调优工具:工欲善其事必先利其器,本章节带你掌握读写比例查询、缓存设置、执行计划和Profile调优工具。第7章参数调优和索引调优:怎么样让SQL执行的更快、数据库的性能更强,怎样充分利用索引进行不断的优化。本章节会为您讲解16种MySQL的优化策略。第8章SQL调优:SQL语句是我们日常使用的重点,怎么样写出一手高性能的SQL语句,其实是具有一定技巧的,本章节讲解8种优化策略,让数据SQL执行性能更强。第9章分库分表:在面对海量数据的时候单表和单个数据库的性能始终会存在瓶颈,本章节为您讲解分库分表的原理和技巧,怎么样使用Merge引擎分表、深入掌握MySQL数据库分区表的能力。第10章高可用架构和安全管理:本部分涵盖MySQL的高可用架构,主备架构、主从架构、主从从架构、互为主从架构。数据的同步复制、半同步复制、异步复制。主从复制原理和主从延迟的问题,以及在管理和开发层面怎样保证数据库安全。第11章MySQL日志:对MySQL的7种日志进行讲解,包括errorlog错误日志、general log查询日志、slow log慢日志、binlog 二进制日志、redlog重做日志。课程会附带配套文档和SQL脚本。有问题可以直接联系作者,24小时线上答疑。

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧