如何提高海量数据表关联查询的效率

[funboy88]

现在的情况，是有一个表很大，有一亿的记录，而搜索查询量又很大，而且更新量也很大，基本每天更新三分之一到三分之二左右，而且需要和其他相关的表进行关联查询，不知道有啥好的解决方法

一般情况下，都是要水平拆分表的，这样会导致，拆分后的表，不好与其他的相关表进行关联查询,没用过分区视图的功能,听同事说,性能不好,如果拆分的表在不同DB上,每次访问的时候,需要用连接服务器验证访问,性能很差.没测试过,有哪位有分区视图的心得不?


如果不拆表，一亿数据全放在一个表里，做读写分离，通过索引优化来提高性能，或采用一些全文检索的方法，就需要做复制分发，但是如果这个表更新频繁，做复制分发，性能又是一个问题,没测试过,凭直觉认为复制分发对于更新频繁的表不适合做
这种策略

[--小F--]

2005以上的数据库建议使用分区表来实现

[-狙击手-]

每 天更新的数据是都最近的吗？有没有规律

[soft_wsx]

用分区表，以下是说明和方法！

高性能网站建设之 MS Sql Server数据库分区 
什么是数据库分区？
数据库分区是一种对表的横向分割，Sql server 2005企业版和之后的Sql server版本才提供这种技术，这种对表的横向分割不同于2000中的表分割，它对访问用户是透明的，用户并不会感觉的表被横向分割了。（2000中的表横向分割是建n个表例如按时间建表每月一个表，表名不同，最后需要做一个大视图）

关于具体的如何做分区，请参考数据库分区演练http://www.cnblogs.com/yukaizhao/archive/2008/05/07/sql_partition_test.html 

为什么要分区？
显而易见分区是为了提高数据库的读写性能，提高数据库的效率；

分区是否总是可以提高效率？
分区是一把双刃剑，并不总能提高效率，这和具体情况有关系。
之所以有分区技术，分区技术用的好的话可以提高性能，是因为一方面分区把一大块数据分成了n小块，这样查询的时候很快定位到某一小块上，在小块中寻址要快很多；另一方面CPU比磁盘IO快很多倍，而硬件上又有多个磁盘，或者是RAID（廉价磁盘冗余阵列），可以让数据库驱动CPU同时去读写不同的磁盘，这样才有可能可以提高效率。
分区在有些时候并不能提高读写效率，比如说我们经常看到的按照日期字段去分区MSDN例子，这个实例中是按照记录的生成时间来分区的，把一年的数据分割成12个分区，每月一个。这样的分区导致分区并不能实现CPU同步写并提高写入性能，因为在同一个时段CPU总是要写入到最新的那一个分区对应的磁盘中。另一个问题是：这样分区是否可以提高读取性能呢？答案是不一定，要看根据什么字段来查询，如果是根据时间来查询，根据时间生成报表那么这种分区肯定会提高查询的效率，但是如果是按照某个客户查询客户最近1年内的账单数据，这样数据分布到不同的分区上，这样的话效率就不一定能提高了，这要看数据在同一个分区上连续分布的读性能高，还是CPU从几个磁盘上同步读取，然后在合并数据的性能更高一些，这和读取数据的记录数也有关系。

如何分区？用什么字段做分区依据？
具体如何分区和涉及的业务有关系，要看业务上最经常的写入和读取操作是什么，然后再考虑分区的策略。

既然与具体业务相关，我们就假定一个业务环境，假如我们要做一个论坛，对论坛的帖子和回复表进行分区。
论坛中最常见的写操作是1）发帖 2）回复帖子，
最常见的读操作是
1） 根据帖子id显示帖子详情和分页的帖子回复 
2） 根据帖子版面帖子列表页根据版面id分页读取帖子列表数据
怎么分区更合适呢？现在还没有准确答案，我有两种可能的方案，写下来，大家讨论看看。
方案1. 根据帖子ID区域段分区（1-300w一个分区、300w-600w一个分区…），这样理论上可以提高帖子详细页的读取速度，而对于写操作性能没有益处，对于根据版面id读取帖子列表页有可能有益
方案2. 根据版面id进行分区，这样对于写性能应该有提高，不同的分区对应不同的版面，当有两个版面同时有发帖回帖操作时，有可能可以并发写。对于根据版面id获得帖子列表页数据也可以提高性能，而对于帖子详细信息页没有性能影响。

多大的数据量才需要分区？
这个问题我只能说一个内部标准，如果一张表的记录超过在超过1000w，并以每月百万的数据量增长，那就需要分区。大家有不同的看法请回复讨论

关于具体的如何做分区，请参考数据库分区演练http://www.cnblogs.com/yukaizhao/archive/2008/05/07/sql_partition_test.html

[code=SQL]代码加注释，希望对初学者有用。

USE [master]
GO
if exists (select * from sys.databases where name = 'Test_1')
drop database Test_1
GO
--创建新库，要演练分区所以我们会多创建两个文件组Test_A,Test_B，以便在后面的分区方案中使用。
CREATE DATABASE [Test_1] ON  PRIMARY 
( NAME = N'test_1', FILENAME = N'D:\sqldata\test_1.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [test_A] 
( NAME = N'Test_A', FILENAME = N'D:\sqldata\test_A.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [test_B] 
( NAME = N'Test_B', FILENAME = N'D:\sqldata\test_B.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Test_log', FILENAME = N'D:\sqldata\Test_log.ldf' , SIZE = 7616KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS
GO
USE [Test_1]
GO
--若分区函数存在则先drop掉
IF  EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'test_partition')
DROP PARTITION FUNCTION [test_partition]
GO
/**//*创建分区函数给后面的分区方案使用，分区函数很简单就是指定一个范围确定在某个值为什么的时候放在那个分区上*/
--新建一个简单的分区函数，该函数以1000为界分两个区
create partition function test_partition(int)
AS
RANGE LEFT FOR VALUES (1000) 
go
/**//*看分区方案是否存在，若存在先drop掉*/
IF  EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'test_scheme')
DROP PARTITION SCHEME test_scheme
GO
--创建分区方案，分区方案需要指定一个分区函数，并指定在分区函数中分的区需要放在哪一个文件组上
create partition scheme test_scheme 
AS 
PARTITION [test_partition] TO (test_A,test_B)
GO
--创建分区表
if object_id('student','U') is not null
drop table student;
go
create table student
(
    id int identity(1,1) not null,
    name varchar(10) not null,
    class int not null,
    grade int
) on test_scheme(class) --在此处指定该表要使用的分区方案，并将指定分区依据列
go
--随便插入几条数据
insert into student values ('AQU',10,100); -- 这条数据在A分区上
insert into student values ('AQU_边界',1000,89); -- 这边数据也在A分区上是个边界，因为我们上面在函数中指定的是RANGE LEFT，所以1000在A分区上
insert into student values ('BQU',1001,90); -- 这一条肯定是在B分区上了。

go
--最后看看结果。$partition.分区函数(分区列)可以返回某一行所在的分区序号
select *,分区序号 = $partition.test_partition(class) from student
GO

[/code]

[pbsh]

[Quote=引用楼主 funboy88 的回复:]
现在的情况，是有一个表很大，有一亿的记录，而搜索查询量又很大，而且更新量也很大，基本每天更新三分之一到三分之二左右，而且需要和其他相关的表进行关联查询，不知道有啥好的解决方法

一般情况下，都是要水平拆分表的，这样会导致，拆分后的表，不好与其他的相关表进行关联查询,没用过分区视图的功能,听同事说,性能不好,如果拆分的表在不同DB上,每次访问的时候,需要用连接服务器验证访问,性能很差.没测试过,有哪位有分区视图的心得不?


如果不拆表，一亿数据全放在一个表里，做读写分离，通过索引优化来提高性能，或采用一些全文检索的方法，就需要做复制分发，但是如果这个表更新频繁，做复制分发，性能又是一个问题,没测试过,凭直觉认为复制分发对于更新频繁的表不适合做
这种策略



[/Quote]

分区表需要企业版，如果你们用的是企业版，这么大的表一定要分区，性能会提高很多，就是有那么点贵。

[funboy88]

分区表知道

主要是我们的数据记录不是按时间增长成线性增长,增长还是比较慢,总的数据量是比较稳定
也就是客户需要每天把自己的不要的数据删除掉,添加新的数据,还有把一些旧的数据内容进行修改操作.

我所说的每天更新量有三分之一到三分二左右,就是这些更新量.


相对来说这个更新量还是很频繁的,

用分区表的话,插入的时候,把数据插到不同的子表中,查询的时候,用汇总表,但是与其他相关表做关联查询的时候,性能会不会不好?

而且网站的流量大的话,肯定分区子表不能放在一台DB服务器上,肯定是放在不同的DB上,这样汇总表的时候,需要用linkserver,好像这个每次连接的时候,需要身份验证,性能很不好.

不知道myspace网站怎么做的,

[lihan6415151528]

[Quote=引用 5 楼 soft_wsx 的回复:]
用分区表，以下是说明和方法！SQL code高性能网站建设之 MS Sql Server数据库分区
什么是数据库分区？
数据库分区是一种对表的横向分割，Sql server 2005企业版和之后的Sql server版本才提供这种技术，这种对表的横向分割不同于2000中的表分割，它对访问用户是透明的，用户并不会感觉的表被横向分割了。（2000中的表横向分割是建n个表例如按时间建表每月一个表，表名¡­
[/Quote]

...怎么还是贴这个出来呀？

[pbsh]

[Quote=引用 7 楼 funboy88 的回复:]
分区表知道

主要是我们的数据记录不是按时间增长成线性增长,增长还是比较慢,总的数据量是比较稳定
也就是客户需要每天把自己的不要的数据删除掉,添加新的数据,还有把一些旧的数据内容进行修改操作.

我所说的每天更新量有三分之一到三分二左右,就是这些更新量.


相对来说这个更新量还是很频繁的,

用分区表的话,插入的时候,把数据插到不同的子表中,查询的时候,用汇总表,但是与其他相关表做关联查询的时候,性能会不会不好?

而且网站的流量大的话,肯定分区子表不能放在一台DB服务器上,肯定是放在不同的DB上,这样汇总表的时候,需要用linkserver,好像这个每次连接的时候,需要身份验证,性能很不好.

不知道myspace网站怎么做的,
[/Quote]

分区表只是数据分布到不同的文件组，你看到的其实只有一张表，不需要Linkserver。
另外，分区规则你自己定，不是一定要用时间来划分的。

[funboy88]

不好意思,看成2000上的分区视图了,
2005的文件组分区表,确实如你说

[Quote=引用 9 楼 pbsh 的回复:]
引用 7 楼 funboy88 的回复:
分区表知道

主要是我们的数据记录不是按时间增长成线性增长,增长还是比较慢,总的数据量是比较稳定
也就是客户需要每天把自己的不要的数据删除掉,添加新的数据,还有把一些旧的数据内容进行修改操作.

我所说的每天更新量有三分之一到三分二左右,就是这些更新量.


相对来说这个更新量还是很频繁的,

用分区表的话,插入的时候,把数据插到不同的子表中,查询的时候,用汇总表,但是与其他相关表做关联查询的时候,性能会不会不好?

而且网站的流量大的话,肯定分区子表不能放在一台DB服务器上,肯定是放在不同的DB上,这样汇总表的时候,需要用linkserver,好像这个每次连接的时候,需要身份验证,性能很不好.

不知道myspace网站怎么做的,


分区表只是数据分布到不同的文件组，你看到的其实只有一张表，不需要Linkserver。
另外，分区规则你自己定，不是一定要用时间来划分的。
[/Quote]

[you_tube]

看看,

[funboy88]

2005分区表,不知道查询的时候,能不能并行查询

[pbsh]

[Quote=引用 12 楼 funboy88 的回复:]
2005分区表,不知道查询的时候,能不能并行查询
[/Quote]

并行跟硬件资源关系很大，CPU个数，磁盘数量等等。

[哈哈V大侠]

学习

[Q315054403]

不要笼统地用分区或分割
实事求是，充分考虑数据特点才有最好的解决方案

[oceantang]

大数据量，硬件要求比较高，最好把硬件先升级好。

[灰太狼]

1亿数据量不算大。根本上建好索引应该就可以了。如果频繁更新，用分区效果也不会太好。

[pbsh]

[Quote=引用 17 楼 tjvictor 的回复:]
1亿数据量不算大。根本上建好索引应该就可以了。如果频繁更新，用分区效果也不会太好。
[/Quote]

频繁更新，跟分区表有什么关系呢？多磁头读写总比你单磁头快吧。

1亿数据量大不大，跟你的应用有关，很多问题不单单是建索引就可以解决的。

[funboy88]

非常感谢各位,

[dawugui]

这么大的量,能查询出来就已经不错了.

[xiaoliaoyun]

分区,把这个表分散到不同的物理磁盘.
建立索引,且索引最好与数据在不同的物理磁盘.

[taojhlwkl501]

没有统一的架构 只有适应不同应用的优秀架构。按车次来建表和部署数据库，不同的车次建不同的表，若干个车次部署在一个数据库上。就能解决并发问题。