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币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
数据库设计一对多、多对多关系
关联映射:一对多关系 简单来说就像球队和球员的关系; 一对多:一个球队拥有多个球员,而一个球员只对应一个球队,则球队和球员的关系就是一对多的关系。 关联映射:多对多关系 例如学生和选修课之间的关系; 多对多:一门课程有若干个学生选修,而一个学生可以同时选修多门课程,则课程和学生之间具有多对多的关系。 数据表间多对多的关系如图: 注:图片转载至http://
数据库设计(一对一,一对多,多对多)关联查询
※表与表之间的关系 1、一对一 需要两个表。当然做项目时为了省空间,通常只建一个表,如果要实现一对一的查询,可以建两个视图。示例如下: 1)建物理表,初始化数据 CREATE TABLE person( id INT, NAME VARCHAR(10), sex CHAR(1), wife INT, husband INT ); INSERT INTO person
数据库一对一、一对多、多对多设计
做一个项目,必然是少不了数据库设计的!在学习阶段,基本都是单表。然而在实际开发过程中,一对多,多对多的表处处都是!简单整理一下,一对多,多对多表如何设计整理一下思路:        数据库实体间有三种对应关系:一对一,一对多,多对多。        一对一关系示例: 一个学生对应一个学生档案材料,或者每个人都有唯一的身份证编号。        一对多关系示例:
数据库表设计(一对多,多对多)
         做一个项目,必然是少不了数据库设计的!在学习阶段,基本都是单表。然而在实际开发过程中,一对多,多对多的表处处都是!简单整理一下,一对多,多对多表如何设计整理一下思路:        数据库实体间有三种对应关系:一对一,一对多,多对多。        一对一关系示例: 一个学生对应一个学生档案材料,或者每个人都有唯一的身份证编号。
多表设计之间的关系;一对一,一对多,多对多
5 多表设计之间的关系 一对多的关系:例如 一个部门对多个员工,一个员工只能属于一个部门 多对多的关系:例如 学生选课,一个学生可以选多门课程,一门可能可以被多个学生选择 一对一的关系:例如 一个人只能有一个身份证,一个身份证只能有一个人 6 表与表一对多的关系: 在一对多时 多的表中添加一个外键,用来和一的表的主键。产生联系。 7 多对多关系介绍: 多对对建表原则:创建第三张表
2014.5.5数据库设计:一对多关系的表的设计和查询技巧
数据库设计:一对多关系的设计
数据库中多对多的关系设计
数据库设计多对多关系的几种形态   前言:多对多关系至少需要3个表,我们把一个表叫做主表,一个叫做关系表,另外一个叫做字典表或者副表(字典表是纪录比较少,而且基本稳定的,例如:版块名称;副表是内容比较多,内容变化的,例如)。   按照数据库的增删查改操作,多对多关系的查找都可以用inner join或者select * from 主表 where id in (select 主表id from
sql数据库一对多和多对多的关系在c#中怎么做?? ̄ ̄ ̄ ̄急。。。
sql数据库一对多和多对多的关系在c#中怎么做?? ̄ ̄ ̄ ̄急。。。
SQL 一对多关系检索多行中的一条记录
今天工作中,需要从多表抽取数据。抽取逻辑就是在一对多关系表中,需要检索符合条件的多行中的一条记录。 DEMO如下: A表: B表: 具体的抽取的逻辑: 所有的A_name对应的 B_name中的第一个B_name记录。比如抽取 所有课程的第一位授课教师的名字 SQL: select A.A_name, B.B_name from a in
数据库设计(一对一、一对多、多对多)
关联映射:一对多/多对一 存在最普遍的映射关系,简单来讲就如球员与球队的关系; 一对多:从球队角度来说一个球队拥有多个球员 即为一对多 多对一:从球员角度来说多个球员属于一个球队 即为多对一数据表间一对多关系如下图:   关联映射:一对一 一对一关系就如球队与球队所在地址之间的关系,一支球队仅有一个地址,而一个地址区也仅有一支球队。 数据表间一对一关系的表现有两种,一种是外键关联