千万值表数据量优化

一、试验方式：
1、利用工具进行数据插入、查询试验，目标是单表数据超过1000W条记录。
2、针对单个表创建单独的数据存储空间和索引存储空间。
3、对表进行anlyze分析。
4、创建表分区。
二、试验结论：
1 数据插入与数据量大小无关，与数据表是否在大量并行操作有关。
2 数据查询与表的数据存储空间有很大关系，数据量大的表建议单独创建数据存储空间和索引存储空间。
3 数据查询结果集的大小与查询性能有很大关系，如在普通索引下，查询结果大与小耗时差别接近1000倍以上。
4 查询性能与索引类型有很大关系，主要是根据范围查询，如时间范围
select count(id)  from list　where time between '2011-04-30 00:00:00' and '2011-05-30 00:01:00';　
time字段由于离散度很高，只能建为b树索引，在按时间范围进行查询时耗时在10m左右，经过数据分析。
如改成
select count(id)  from list
where itime >= '20100430'　and itime <= '20100530';
itime字段为整数，精确到年月日，由于离散度低，可以建为bitmap类型索引，一年只有356个，即便有1亿条记录，其索引记录最多只

有356条。
查询统计就可以过到ms级（0.078s）比前一种情况提高几百倍。同样字段重复值与记录数之比为1:100以上都可以建为bitmap类型索引,一般大数据表要结合各种索引的特性进行创建合适的索引。

5 SQL语法与性能有很大关系，一是索引顺序，二是嵌套查询
直接从查询统计，索引的解析是从后向前解析。
select count(id)  from list
where itime >= '20100330'  and itime <= '20100830'
   and icode = '3123232331';　 3.219s

select count(id)   from (select id from list
where itime >= '20100330'  and itime <= '20100830'
   and icode = '3123232331');　 0.015s

6 表分析与查询性能有关系。通过anlyze对表或是索引进行分析前后对查询性能有明显的变化。
分析表中所有索引：analyze table list compute statistics for all indexes
分析表中指定索引：analyze index LIST_2 compute statistics
分析表所有索引耗时长，分析指定索引耗时短。

7、如果按时间存放可以针对大数据表创建表分区，提高插入和查询速度。
创建表并指定分区。
create table LIST
(
  ID                   INTEGER not null,
  ICODE                CHAR(14),
  NAME     　　　　　　 CHAR(64),
  SEX              　　CHAR(2),
  CARD_NUMBER      　　CHAR(20),
  TIME      　　　　　　CHAR(21),
  ITIME       　　　　　INTEGER,
  MONTH            　　INTEGER
)
partition by list (MONTH)
(
  partition MONTH1 values (1)
    tablespace TEST1,
  partition MONTH2 values (2)
    tablespace TEST2,
  partition MONTH3 values (3)
    tablespace TEST3,
  partition MONTH4 values (4)
    tablespace TEST4,
  partition MONTH5 values (5)
    tablespace TEST5,
  partition MONTH6 values (6)
    tablespace TEST6,
  partition MONTH7 values (7)
    tablespace TEST7,
  partition MONTH8 values (8)
    tablespace TEST8,
  partition MONTH9 values (9)
    tablespace TEST9,
  partition MONTH10 values (10)
    tablespace TEST10,
  partition MONTH11 values (11)
    tablespace TEST11,
  partition MONTH12 values (12)
    tablespace TEST12
);
创建索引其它属性与普通表一样。
create index LIST_1 on LIST (ID);
create index LIST_2 on LIST (ICODE);
create bitmap index LIST_3 on LIST (ITIME);
create bitmap index LIST_4 on LIST (MONTH);

查询数据
select count(id)  from LIST partition(MONTH1)
where itime between '20100101' and '20100131'
and icode = '1000182';

删除分区数据
删除分区：alter table [tbname] drop partition [ptname];
alter table LIST drop partition MONTH1;
删除子分区：alter table [tbname] drop subpartition [ptname];



最后总结:表分区和sql语句优化，建立索引，数据缓存、数据分流，读写分离，和一些第三方插件

已有 0 人发表留言，猛击->> 这里<<-参与讨论


ITeye推荐

• —软件人才免语言低担保 赴美带薪读研！—