2 lollipop6105 Lollipop6105 于 2016.02.10 23:40 提问

sql数据库设计,一对多的问题。数据量在200w+,求设计思路 15C

拿新闻系统来说。一条新闻对应多个分类,正常来讲表设计为一对多的关系,3张→新闻表,分类表,新闻分类关联表。随着时间的推移,新闻越来越多,表中的数据越来越大。查询起来会越来越慢。程序要求能够按照多个分类(or的关系)、标题、地区等去搜索新闻、分页。如何设计这个系统的表结构才能使得搜索高效快速?(分类均为五位数的ID),新闻内容需要格式,html代码,需要什么类型?与新闻表分离还是在一起呢?目前数据量200w+。
旧的程序设计是只有一张新闻表,分类单独一个字段,分类字段数据用逗号隔开‘10001,10002,10012’这种,新改版后需要多个条件搜索,按目前200w左右的数据like查询,三个分类or的关系查询都得个4s甚至更多,实在接受不了。该如何设计?如果现有的表结构不改变该如何优化?求解~~~
SQL Server

4个回答

caozhy
caozhy   Ds   Rxr 2016.02.11 06:33

可以分库,比如分为历史库和当前库。对于历史库的内容,直接缓存静态页面。不必要再查询。
另外不知道你like是什么?全文搜索么?全文搜索需要建立全文索引(对于中文,还有一个分词的步骤)。而不是这么like

caozhy
caozhy   Ds   Rxr 2016.02.11 06:34
yokingboy
yokingboy   2016.02.12 01:24

数据库查询不要用Like 或者模糊匹配,何况你都200W了,每次都得全表扫。
搜索内容的话可以考虑用Lucene,我看你是SQL Service八成是.Net。如果是的话,不知道Lucene.net 怎么样了。
新闻内容估计是单独显示的,可以把新闻表中的新闻内容移出来,单独存放,查询的话从Lucene索引里面查。
主表里面就放需要查询的字段。 或者把需要查询的分类都放到Lucene Field里。
显示的时候根据ID去内容表里面只查内容,或者直接把改页面静态化或者缓存。
主要保证根据条件能快速查到内容ID。

Lollipop6105
Lollipop6105 使用Lucene后CPU会飚上去该怎么优化?求教!用的是.net
接近 2 年之前 回复
qq_16414307
qq_16414307   2016.02.13 12:09

你这个关键是分页的问题

分类肯定不会很多,那么无论你怎么检索,多个分类返回的记录数都非常多,那么速度慢主要就是分页慢,
而分页慢主要是因为要计算一共多少记录,也就是要全表扫描。
select top 10 * from t where fenlei like '' order by id desc
单纯这个语句无论多少记录,只要是按主键id排序,而且fenlei比较均匀 ,都不会慢,因为最多扫描几百几千记录肯定就会找到符合条件的记录 。
第二页你可以这样
select top 10 * from t where fenlei like '' and id<minid order by id desc
其中minid是上条语句返回的 最小 id,这样速度也不会慢。

但是如果你附加其他条件,导致根本没有符合的记录,那么就只能做全表扫描,速度就肯定快不了了 。

最终解决方案就是
0.如果按顺序扫描很少记录就可结束查询时,通过改进分页即可,无需另外加索引,原先like方式也可以
1.必须扫描很多时(比如附加,作者=),就要增加索引覆盖可能的查询,来避免一次查询全表扫描
2.一定要全表扫描,无法建立合适索引时,通过其他方式cache查询结果,简单说就是数据库前面加cache减少cache压力
前提是大家查询条件差不多,可以复用结果
3.查询无法复用,就必须设法通过其他索引方式,比如自己的关键词,或者全文检索,或者复合索引
比如经常查询 分类1,分类2,那么就增加一个新分类12代表,分类1+分类2,这样检索fenlei=12时效率就高多了
当然这时肯定要一对多表,逗号分隔的是无法直接建立索引的
新闻表 id,subject
分类表 fenlei,id
select top 10 * from 新闻表 x,分类表 f where f.fenlei=1 and f.id=x.id order by f.id desc
这个SQL是高效的,但是
select top 10 * from 新闻表 x,分类表 f where f.fenlei in(1,2) and f.id=x.id order by f.id desc
这个SQL效率不高,而如果保证同时fenlei为1,2的文章再设置成fenlei=12,按 12检索效率就高了

这种方式实际就是用数据冗余来提高查询了。对于SQLServer2008你可以通过建立索引视图,来自动实现
比如建立一个视图
select * from 分类表 where fenlei in (1,2)
然后在这个视图上建立索引id,就会导致这个数据被”cache“,你查询fenlei in(1,2)时,就可以直接利用这个索引了

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!