超大数量表优化(转)

http://topic.csdn.net/u/20110304/19/20F1737D-74CE-4546-9273-5861A022C301.html

 

先笔录下，又时间总结下该类问题！

 

数据库版本是db2 9.7。
表出入口T_CRK，数据量为4亿，并且每月都增加几百万。 结构大致如下：
ID 主键
CRRQ 日期
PLACE 地点
XNAME 姓名
....

还有其他的字段，但主要是这几个字段，查询的时候也一般是按照这几个条件查询，其中，日期是必选的查询条件，用户可以选择某一天也可以是某个时间段。姓名用户希望是模糊查询，地点总共有200个。

我是这样做的优化：
1、把原来的表改成分区表。按照日期的年份分区，既 一年 的数据是一个分区。
2、在日期上建立索引。后来想在日期上建立一个分区索引，一查才知道 db2 9.7 默认建立的就是分区索引。
3、在地点、姓名上建立了索引。

现在，查询条件为 等于某个日期的时候效率很高（<2s）。但是如果是一个时间段（一个月，一个月大约为几百万数据），就很慢，大约得1-2分钟。
如果在加上地点、姓名那就更慢了。最要命的是用户要求姓名模糊查询。

--------------------------------------------------------------------

有人帮我提出以一下解决方案:
a.每天建立一个分区.那么每天,也就是每个分区的数量为几十万.如果这样的话,我的表日期是从2006年到2016年,一共要3000多个分区!分区可以动态创建,即 每月的最后一天利用job驱动存储过程执行 alter table add pa....来增加下一个月的分区. 我考虑一个表几千个分区行不行,即时可以的话,效率会不会有提高.

b.按月分区,每月的数据量为几百万.如果是10年的数据大约是:12*10=120个分区.

c.把一个大表拆分成多个表.也就是说每个月的数据我都放到一个单独的表中,这个表的数据量大约为几百万.如果这样的话,10年的数据,共需要创建10*12=120张表.


不知道上面的3种解决方案哪种更好.如果没有更好的,那我应该怎么办呢?请大家帮帮我.

 

 

1 考虑是否值得按地点建子分区（如果按姓名查询时，地点为必要条件的话，这种方法可以尝试）
2 创建组合索引 时间-地点-姓名
3 考虑将中文名字用汉字拆开存储，每个汉字一个字段，这样前台的模糊条件，数据库都是确定条件了。

 

 

1、按月还是按天的话考虑你的数据量和硬件条件来决定
我当时是按月分区，大概数据量在300万，机器是IBM P570 16C 32G
如果你的机器性能低一些，那么可以考虑按天分区，通常来讲一个分区几百万的数据是没有问题的

2、还有db2的表分区功能下，各个表分区在物理上独立，所以分表是多此一举的

3、自动运行job来添加表分区是不可取的，会有执行失败的时候，到时候你就难办了，通常由维护人员来提前添加

4、在按月分区的同时，按地区组织（organized）成MDC表将显著提高按地区查询的性能

 

 

数据库分区+表分区+索引
+如果是OLAP的表可以再按月分区的同时，按地区组织（organized）成MDC表将显著提高按地区查询的性能

 

大数据表优化思路：
 1 数据库分区 不知道楼主的数据库服务器是几个CPU的，如果有多个CPU的话，建议对表创建数据库分区，一个分区一个CPU，这样可以提高数据库性能
 2 表分区 这点楼主已经建立就无需多讲
 3 建立MDC MDC的核心思想是把多个维相等的数据放在同一个块里 ，可以大大提高查询效率 例如 楼主讲的时间-地点-姓名 相等的数据放入同一个块里，数据库对块进行索引，所以查找 时间=XX and 地点=XX and
姓名 =XX的记录是直接通过索引找到记录
 4 建立 MQT 其思想是DB2自动建立聚集表

 

 4亿说大不大 说小不小 实际上就是怎么进行物理设计的问题

 

 

用cache 服务器

 

 

如果 查询条件 是 and 的组合
则 日期范围（分区）+地点（可以索引）+姓名（无法索引，或者按字拆到4个字段） 应该还是会遍历范围比较小的

关键是要让数据库引擎知道：先根据 日期范围+地点 确定 几十万条以下的记录，再在其中按姓名like筛选
而不是先按姓名like对4亿条进行筛选

 

 

我经历过的，sql 2k支持单表30亿+记录,游戏公司。数据量，单表500G+，木问题。不过你说的模糊查询，SQL可能没办法。

 

没有用过db2，对数据库优化也没有多少经验，提几个可以考虑的点吧，请各位来评判指导一下：
1 日期字段如果是只有日期而不带时间，推荐改成数字型，使用程序把日期转换成（相对某一天的）数字[类似unix时间戳的设计]
2 姓名等字段，应该是大量重复的，建议改成数字型的，而把真正的姓名单独建表，使用主键id号与本表的地点与姓名字段对应。
这样对姓名字段like查询时，先查出相符的姓名的id号，然后再到这个大表里查询。
3 地点字段同理。

58楼的优化思路也是一个很好的idea 相当于用了数据仓库的基本思想
把文本类的、描述类的、含有大量重复的字段单独建立维表 ；
事实表（大表）联合主键值尽量使用int类型，并对联合主键建立位图索引从而提高查询效率

 

Oracle的经验是：
1。FULL INDEX SCAN
2。加并发
3。这种查询最好做成BATCH，如果实时的话要求比较高级的数据库系统