oracle 亿级数据存储方案

转载自：https://www.cnblogs.com/ShaYeBlog/p/10383644.html

大数据量的查询，不仅查询速度非常慢，而且还会导致数据库经常宕机（刚接到这个项目时候，数据库经常宕机o(╯□╰)o）。 那么，如何处理上亿级的数据量呢？如何从数据库经常宕机到上亿数据秒查？仅以此篇文章作为处理的总结。

数据背景：
下面是存放历史数据表的数据量，数据量确实很大，3亿多条。但这也仅仅是测试数据而已，因为客户端服务器上的数据可能远不止于此。

为什么说远不止于此呢？实际情况是这样的：

有一个实时数据表，THTF_TABLE_AI，以及历史数据表，THTF_TABLE_AI_HIS

实时数据表固定3万条数据（客户推送过来的数据），每2小时刷新一次，每刷新一次就往历史表中插入一次数据。

可以算一下，历史表中数据量的数据量：

3 x 12 x 30 = 1080万,也就是每个月存储1080条数据，1年就1亿多的数据量。这样大的数据量，导致查询速度慢，估计用户会气炸的…

解决方案：
第一步：分表

如果历史表中存储了很多年的数据，会造成严重的数据冗余。那如果将历史表分表存储，比如每年创建一个表，数据存储到对应的年表中，必定会减少很多数据量。（如果分成年表数据量还是过大，可以细分到月表，天表…）。

我们这里以创建年表为例，写一个创建年表的存储过程，利用PLSQL定时任务定时执行此存储过程（定时每年12月31号创建下一年的年表）。存储过程如下，定时任务查看此篇文章：PLSQL执行Oracle定时任务

CREATE OR REPLACE
PROCEDURE CREATE_YEAR_TABLE IS
/变量/
grantSql VARCHAR2(50);
yearStr VARCHAR2(4);
tableCount int(2);
createSql VARCHAR2(1000);
BEGIN
/权限/
grantSql := ‘grant create any table to thtf_taiyuan’;
EXECUTE IMMEDIATE grantSql;
/创建年表 注意create table 后边的空格/
SELECT TO_CHAR(SYSDATE, ‘yyyy’)+1 INTO yearStr FROM dual;
createSql := 'CREATE TABLE ’ || ‘THTF_TABLE_YEAR_’ || yearStr ||
‘( SHE_SHI_CODE VARCHAR2(100),
SHE_SHI_TYPE NUMBER DEFAULT 1,
FEN_GONG_SI VARCHAR2(100),
SHUI_HAO NUMBER(20,4) DEFAULT 0,
PRE_SHUI_HAO NUMBER(20,4) DEFAULT 0,
DIAN_HAO NUMBER(20,4) DEFAULT 0,
PRE_DIAN_HAO NUMBER(20,4) DEFAULT 0,
RE_HAO NUMBER(20,4) DEFAULT 0,
PRE_RE_HAO NUMBER(20,4) DEFAULT 0,
SHI_JIAN DATE DEFAULT SYSDATE,
STATE NUMBER DEFAULT 1 )’;
SELECT COUNT(1) INTO tableCount FROM user_tables WHERE table_name = CONCAT(‘THTF_TABLE_YEAR_’, yearStr);
IF tableCount = 0 THEN
EXECUTE IMMEDIATE createSql;
COMMIT;
END IF;
END CREATE_YEAR_TABLE;
第二步：分区

年表创建过后，查询就是查询年表中的数据，可是虽然分表了，但是年表中的数据量仍然很大，查询速度虽然有提升，但并不能满足用户的要求。便考虑到分表再分区，即将历史数据以不同的年表来存储，在年表中按月分区。

说道分区，要恶补一下了~

数据库分区：就是减少SQL操作的数据量，从而提升查询效率。表分区后，逻辑上仍然是一张表，只不过将表中的数据在物理上存放到多个表空间上。这样在查询数据时，会查询相应分区的数据，避免了全表扫描。

分区又分为水平分区、垂直分区。

水平分区：就是对行进行分区，举个例子来说，就是一个表中有1000万条数据，每100万条数据划一个分区，这样就将表中数据分到10个分区中去。水平分区要通过某个特定的属性列进行分区，比如我用的列就是Date时间。

垂直分区：通过对标垂直划分来减少表的宽度，从而提升查询效率。比如一个学生表中，有他相关的信息列，还有论文列以CLOB存储。这些以CLOB存储的论文并不会经常被访问到，这时候就要把这些不经常使用的CLOB划分到另一个分区，需要访问时再调用它。

总的来说，分区的主要目的还是避免了全表扫描，从而提升查询速度。

接下来在上面的存储过程的基础上，我们创建按月分区。

CREATE OR REPLACE
PROCEDURE CREATE_YEAR_TABLE IS
grantSql VARCHAR2(50);
yearStr VARCHAR2(4);
tableCount int(2);
createSql VARCHAR2(1000);
BEGIN
/权限/
grantSql := ‘grant create any table to thtf_taiyuan’;
EXECUTE IMMEDIATE grantSql;
/创建年表 注意create table 后边的空格/
SELECT TO_CHAR(SYSDATE, ‘yyyy’)+1 INTO yearStr FROM dual;
createSql := ‘CREATE TABLE ’ || ‘THTF_TABLE_YEAR_’ || yearStr ||
‘( SHE_SHI_CODE VARCHAR2(100),
SHE_SHI_TYPE NUMBER DEFAULT 1,
FEN_GONG_SI VARCHAR2(100),
SHUI_HAO NUMBER(20,4) DEFAULT 0,
PRE_SHUI_HAO NUMBER(20,4) DEFAULT 0,
DIAN_HAO NUMBER(20,4) DEFAULT 0,
PRE_DIAN_HAO NUMBER(20,4) DEFAULT 0,
RE_HAO NUMBER(20,4) DEFAULT 0,
PRE_RE_HAO NUMBER(20,4) DEFAULT 0,
SHI_JIAN DATE DEFAULT SYSDATE,
STATE NUMBER DEFAULT 1 )
/按月分区/
PARTITION BY RANGE(SHI_JIAN)
INTERVAL(NUMTOYMINTERVAL(1,’’’ || ‘MONTH’ || ‘’’))
( PARTITION PART1 VALUES LESS THAN(TO_DATE(’’’|| CONCAT(yearStr, ‘-11-01’) ||’’’,’’’|| ‘YYYY-MM-DD’ ||’’’)) )’;
SELECT COUNT(1) INTO tableCount FROM user_tables WHERE table_name = CONCAT(‘THTF_TABLE_YEAR_’, yearStr);
IF tableCount = 0 THEN
EXECUTE IMMEDIATE createSql;
–添加注释
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ’ || ‘THTF_TABLE_YEAR_’ || yearStr || ‘.SHE_SHI_CODE IS ‘‘设施编号’’’;
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ’ || ‘THTF_TABLE_YEAR_’ || yearStr || ‘.SHE_SHI_TYPE IS ‘‘设施类型’’’;
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ’ || ‘THTF_TABLE_YEAR_’ || yearStr || ‘.FEN_GONG_SI IS ‘‘分公司’’’;
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ’ || ‘THTF_TABLE_YEAR_’ || yearStr || ‘.SHUI_HAO IS ‘‘水耗’’’;
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ’ || ‘THTF_TABLE_YEAR_’ || yearStr || ‘.PRE_SHUI_HAO IS ‘‘上一小时水耗’’’;
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ’ || ‘THTF_TABLE_YEAR_’ || yearStr || ‘.DIAN_HAO IS ‘‘电耗’’’;
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ’ || ‘THTF_TABLE_YEAR_’ || yearStr || ‘.PRE_DIAN_HAO IS ‘‘上一小时电耗’’’;
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ’ || ‘THTF_TABLE_YEAR_’ || yearStr || ‘.RE_HAO IS ‘‘热耗’’’;
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ’ || ‘THTF_TABLE_YEAR_’ || yearStr || ‘.PRE_RE_HAO IS ‘‘上一小时热耗’’’;
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ’ || ‘THTF_TABLE_YEAR_’ || yearStr || ‘.SHI_JIAN IS ‘‘时间’’’;
EXECUTE IMMEDIATE 'COMMENT ON COLUMN ’ || ‘THTF_TABLE_YEAR_’ || yearStr || ‘.STATE IS ‘‘状态值’’’;
COMMIT;
END IF;
END CREATE_YEAR_TABLE;
如果分区要细化到天，将分区语句改为如下：

PARTITION BY RANGE(SHI_JIAN)
INTERVAL(NUMTOYMINTERVAL(1,’’’ || ‘DAY’ || ‘’’))
( PARTITION PART1 VALUES LESS THAN(TO_DATE(’’’|| CONCAT(yearStr, ‘-01-01’) ||’’’,’’’|| ‘YYYY-MM-DD’ ||’’’)) )’;
创建完分区后，如何查询表中有哪些分区呢？

–查分区数
SELECT table_name,partition_name from user_tab_partitions where table_name = ‘THTF_TABLE_YEAR_2017’
如何查询分区中的数据呢？

–查分区数据
SELECT * FROM THTF_TABLE_YEAR_2017 PARTITION(PART1)