千萬級數據,如何做性能優化?分庫分表、Oracle分區表?

語言: CN / TW / HK

大家好,我是哪吒,最近項目有一個新的需求,按月建表,按天分區

不都是分庫分表嗎?怎麼又來個分區?

讓我們一起,一探究竟,深入理解一下Oracle分區表技術,實現快速入門,豐富個人簡歷,提高面試level,給自己增加一點談資,秒變面試小達人,BAT不是夢。

三分鐘你將學會:

  1. Oracle是如何存儲數據的?
  2. Oracle分區是什麼?
  3. 何時分區?
  4. 分區表的分類都有哪些?
  5. Oracle分區技術實戰演練

一、Oracle是如何存儲數據的?

1、邏輯存儲與物理存儲

在國企或者一線大廠,一般都會選擇使用Oracle數據庫,程序通過mybatis等持久層框架訪問Oracle數據庫,指定表空間,表空間內包含若干張表,表中存有行數據,行數據以行片段的形式存儲在數據庫塊中,① 當插入的行太大,無法裝入單個塊時;② 或因為更新的緣故,導致現有行超出了當前空間時 -> 就會發生整個行不存儲在一個位置的情況。

Oracle在邏輯上將數據存儲在表空間中,在物理上將數據存儲在數據文件中。

表空間包括若干個數據文件,這些表空間使用與運行Oracle軟件的操作系統一致的物理結構。數據庫的數據存儲在構成數據庫表空間的數據文件中。

臨時文件是一個臨時表空間的文件;它是通過TEMPFILE選項創建的。臨時表空間不包含表,通常用於排序。

2、進一步分析它們之間的關係

  1. 數據庫包含若干個表空間(邏輯存儲單元);
  2. 每一個表空間包含很多的Oracle 邏輯數據塊,邏輯數據塊的大小一般在2 KB 至32 KB,默認8 KB;
  3. Oracle 數據塊是邏輯I/O的最小單位;
  4. 特定數目的相鄰邏輯塊構成了“區”;
  5. 特定邏輯結構分配的一組區構成了一個段;

3、Oracle邏輯數據塊

數據庫塊包含塊頭、行數據、可用空間。

(1)塊頭

塊頭包含段類型(如表或索引)、數據塊地址、表目錄、行目錄和事務處理插槽。

每個插槽的大小為24 字節,修改塊中的行時會使用這些插槽。

(2)行數據

塊中行的實際數據。

(3)可用空間

可用空間位於塊的中部,允許頭和行數據空間在必要時進行增長。當插入新行或用更大的值更新現有行的列時,行數據會佔用可用空間。

(4)致塊頭增長的原因有:
  1. 行目錄需要更多的行條目;
  2. 需要的事務處理插槽數多於最初配置的數目;

塊中的可用空間最初是相鄰的。但是,刪除和更新操作可能會使塊中的可用空間變成碎片,需要時Oracle 服務器會接合塊中的空閒空間。

二、Oracle分區表技術

分區是指表和索引可以被分成若干個部分,它們擁有相同的邏輯屬性和數據結構。所有分區的字段和索引都是一樣的。

分區表是將表數據分為若干個可以被單獨管理的片,每個片就是一個分區,分一個分區都可以擁有自己的物理屬性,比如表空間、事務槽、存儲參數、最小區段數等,通過建分區語句指定,提升可用性和存儲效率。

每個分區可以被單獨管理,降低管理成本和備份成本,提高容錯率,避免“一榮既榮,一損俱損”的問題。

1、分區表的優缺點

(1)優點

  1. 可以通過指定分區提高查詢性能;
  2. 提高容錯率,避免“一榮既榮,一損俱損”的問題;
  3. 降低管理成本;
  4. 降低備份成本;

(2)缺點

普通表和分區表不能直接轉換,可以通過數據遷移,再重命名的方式實現,需要重建約束、索引,在創建表時可以添加關鍵字“parallel compress”並行執行,提高效率,下面會通過SQL實例介紹。

2、何時分區?

單表的數據量如果過大,會影響SQL的讀寫性能,我們可以通過分庫分表的方式解決表性能的問題,Oracle的分區表是將一張大表在物理上分成幾個較小的表,從邏輯上看仍然是一張完整的表。這樣,每次DML操作只考慮其中一張分區表即可。

那麼,臨界點是多少呢?

  1. 數量量超過500萬且空間佔用超過2GB的時候必須分區
  2. 數量量高於100萬,低於500萬時建議分區;

注意:單個分區的數據可以超過500萬,但存儲空間不建議超過2GB。

三、分區相關的數據字典

根據數據字典表的前綴不同,可查詢的內容及權限有所差異:

  1. DBA_開頭:需要DBA權限,查詢全庫內容;
  2. ALL_開頭:查詢當前用户權限下的內容;
  3. USER_開頭:查詢當前用户下的內容;

以下是分區表的一些相關字典表,前綴是“DBA_”、“ALL_”、“USER_”;

  1. 分區表信息字典表:*_PART_TABLES;
  2. 分區信息字典表:*_TAB_PARTITIONS;
  3. 子分區信息字典表:*_TAB_SUBPARTITIONS;
  4. 分區表的分區字段信息字典表:*_PART_KEY_COLUMNS;

四、分區表的分類

1、範圍分區

將數據基於範圍映射到每一個分區,這個範圍是由創建分區表時指定的分區鍵決定。

一般選取id或者時間作為範圍分區的分區鍵。

(1)按月建表,按天分區

sql create table WORKER_202301 ( id VARCHAR2(100) not null, name VARCHAR2(200), technology VARCHAR2(100), save_date DATE ) partition by range (SAVE_DATE) ( partition WORKER20230129 values less than (TO_DATE('2023-01-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace MYSPACE pctfree 10 initrans 1 maxtrans 255 storage ( initial 80K minextents 1 maxextents unlimited ), partition WORKER20230130 values less than (TO_DATE('2023-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace MYSPACE pctfree 10 initrans 1 maxtrans 255 storage ( initial 80K minextents 1 maxextents unlimited ) ); create index IDX_WORKER_ID202301 on WORKER_202301 (ID) local; create index IDX_WORKER_ID_NAME202301 on WORKER_202301 (ID, NAME) local;

(2)建表語句分析
  1. NLS_CALENDAR=GREGORIAN:用於指定Oracle所使用的日曆體系,其取值為Arabic Hijrah、English Hijrah、Gregorian、Japanese Imperial、Persian、ROC Official、Thai Buddha。
  2. tablespace:指定表空間;
  3. pctfree:塊保留10%的空間留給更新該塊數據使用
  4. initrans:初始化事務槽的個數;
  5. maxtrans:最大事務槽的個數;
  6. storage:存儲參數
  7. initial:區段(extent)一次擴展64k
  8. minextents:最小區段數
  9. maxextents unlimited:最大區段無限制

每個塊都有一個塊首部。這個塊首部中有一個事務表。事務表中會建立一些條目來描述哪些事務將塊上的哪些行/元素鎖定。這個事務表的初始大小由對象的INITRANS 設置指定。對於表,這個值默認為2(索引的INITRANS 也默認為2)。事務表會根據需要動態擴展,最大達到MAXTRANS 個條目(假設塊上有足夠的自由空間)。所分配的每個事務條目需要佔用塊首部中的23~24 字節的存儲空間。注意,對於Oracle 10g,MAXTRANS 則會忽略,所有段的MAXTRANS 都是255。

由於oracle塊裏有一個PCT_free的概念,即oracle會預留塊大小的10%作為緩衝,當修改oracle的事務增加時,事務槽向下增長,當更新oracle塊的數據時,數據向上增長,PCT_free的空間被壓縮。

local索引是針對單個分區表的索引;

在對分區表進行維護操作時需檢查索引是否失效,索引失效除了會導致查詢慢,還會導致數據寫入失敗,在ALTER TBALE語法中也可以添加關鍵字“UPDATE INDEXES”避免維護表時索引失效。

(3)插入三條數據

sql insert into worker_202301 (id,name,technology,save_date) values ('1','哪吒','java',to_date('2023/1/29 22:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202301 (id,name,technology,save_date) values ('2','雲韻','java',to_date('2023/1/29 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202301 (id,name,technology,save_date) values ('3','美杜莎','Python',to_date('2023/1/30 00:45:19','yyyy-MM-dd hh24:mi:ss'));

(4)查詢指定分區

sql select * from worker_202301 partition (WORKER20230129);

跨分區查詢時,查詢每個分區的數據後使用UNION ALL關鍵字做集合查詢,提高查詢效率。

(5)添加分區

sql ALTER TABLE worker_202301 ADD PARTITION WORKER20230131 VALUES LESS THAN (TO_DATE(' 2023-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));

(6)刪除分區

sql ALTER TABLE worker_202301 DROP PARTITION WORKER20230131;

2、列表分區

(1)列表分區適用於一個字段只有固定的幾個值,比如類型、月份、課程等。

```sql create table WORKER_202302 ( id VARCHAR2(100) not null, name VARCHAR2(200), technology VARCHAR2(100), save_date DATE ) partition by list (technology) ( partition technology_java values ('java'), partition technology_python values ('python'), partition technology_c values ('c') );

create index IDX_WORKER_ID202301 on WORKER_202301 (ID) local; create index IDX_WORKER_ID_NAME202301 on WORKER_202301 (ID, NAME) local; ```

(2)插入三條數據

sql insert into worker_202302 (id,name,technology,save_date) values ('1','哪吒','java',to_date('2023/2/1 22:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202302 (id,name,technology,save_date) values ('2','雲韻','java',to_date('2023/2/1 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202302 (id,name,technology,save_date) values ('3','美杜莎','python',to_date('2023/2/2 00:45:19','yyyy-MM-dd hh24:mi:ss'));

(3)查詢列表分區數據

(4)如果一個分區的數據量不大,可以合併分區

sql create table WORKER_202303 ( id VARCHAR2(100) not null, name VARCHAR2(200), technology VARCHAR2(100), save_date DATE ) partition by list (technology) ( partition technology_java values ('java','python'), partition technology_c values ('c','c++') );

3、哈希分區

範圍分區和列表分區都是使用某一個字段進行分區,此字段的分區度大才行,但也會產生諸多問題,比如上述的按技術列表分區,現階段,Java開發人員明顯高於C,此時就會導致分區不均勻的問題。

此時,hash分區閃亮登場,hash分區的好處是讓分區更均勻一些。

(1)上面的諸多參數都可以省略。

sql create table WORKER_202304 ( id VARCHAR2(100) not null, name VARCHAR2(200), technology VARCHAR2(100), save_date DATE ) partition by hash (id) ( partition worker_id_1, partition worker_id_2, partition worker_id_3, partition worker_id_4, );

此時,插入200條數據,id從1到200,驗證一下是否均勻。數據條數分別是51、55、61、33。

(2)何時使用hash分區?
  1. 分區鍵的值最好是連續的;
  2. 分區數量最好是2的n次方,對hash運算更加友好;
(3)添加hash分區:

sql ALTER TABLE worker_202304 ADD PARTITION worker_id_5; 剛創建好分區,worker_id_5就有數據了,why?匪夷所思。

添加分區時,所有數據會重新計算hash值,重新分配到不同的分區表中。

(4)不可以刪除hash分區

4、範圍列表組合分區

(1)建表語句

sql create table WORKER_202305 ( id VARCHAR2(100) not null, name VARCHAR2(200), technology VARCHAR2(100), save_date DATE ) partition by range (SAVE_DATE) SUBPARTITION BY LIST (technology) ( partition WORKER20230529 values less than (TO_DATE(' 2023-05-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( SUBPARTITION technology_java_29 values('java'), SUBPARTITION technology_python_29 values('python'), SUBPARTITION technology_c_29 values('c') ), partition WORKER20230530 values less than (TO_DATE(' 2023-05-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( SUBPARTITION technology_java_30 values('java'), SUBPARTITION technology_python_30 values('python'), SUBPARTITION technology_c_30 values('c') ) );

(2)插入8條數據

sql insert into worker_202305 (id,name,technology,save_date) values ('101','哪吒','java',to_date('2023/5/29 22:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202305 (id,name,technology,save_date) values ('102','雲韻','java',to_date('2023/5/29 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202305 (id,name,technology,save_date) values ('103','美杜莎','java',to_date('2023/5/29 00:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202305 (id,name,technology,save_date) values ('104','哪吒','java',to_date('2023/5/29 22:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202305 (id,name,technology,save_date) values ('105','雲韻1','python',to_date('2023/5/30 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202305 (id,name,technology,save_date) values ('106','美杜莎1','python',to_date('2023/5/30 00:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202305 (id,name,technology,save_date) values ('107','哪吒1','python',to_date('2023/5/30 22:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202305 (id,name,technology,save_date) values ('108','雲韻1','python',to_date('2023/5/30 22:46:19','yyyy-MM-dd hh24:mi:ss'));

(3)查詢分區數據

sql select count(1) from worker_202305 PARTITION (WORKER20230529);//4條 ok select count(1) from worker_202305 PARTITION (WORKER20230530);//4條 ok select count(1) from worker_202305 SUBPARTITION (TECHNOLOGY_JAVA_29);//4條 ok select count(1) from worker_202305 SUBPARTITION (TECHNOLOGY_JAVA_30);//0條 ok

(4)添加主分區

sql ALTER TABLE worker_202305 ADD PARTITION WORKER20230531 values less than (TO_DATE(' 2023-06-1 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( SUBPARTITION technology_java_31 values('java'), SUBPARTITION technology_python_31 values('python'), SUBPARTITION technology_c_31 values('c') )

為WORKER20230529添加子分區technology_go_29:

sql ALTER TABLE worker_202305 MODIFY PARTITION WORKER20230529 ADD SUBPARTITION technology_go_29 values('go');

(5)刪除子分區

sql ALTER TABLE worker_202305 DROP SUBPARTITION technology_go_29;

5、範圍哈希組合分區

(1)建表語句

sql create table WORKER_202306 ( id VARCHAR2(100) not null, name VARCHAR2(200), technology VARCHAR2(100), save_date DATE ) partition by range (SAVE_DATE) SUBPARTITION BY HASH (id) ( partition WORKER20230628 values less than (TO_DATE(' 2023-06-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( SUBPARTITION worker_id_1, SUBPARTITION worker_id_2, SUBPARTITION worker_id_3, SUBPARTITION worker_id_4 ), partition WORKER20230629 values less than (TO_DATE(' 2023-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ( SUBPARTITION worker_id_5, SUBPARTITION worker_id_6, SUBPARTITION worker_id_7, SUBPARTITION worker_id_8 ) );

(2)插入10條數據

sql insert into worker_202306 (id,name,technology,save_date) values ('101','哪吒','java',to_date('2023/6/28 22:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202306 (id,name,technology,save_date) values ('102','雲韻','java',to_date('2023/6/28 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202306 (id,name,technology,save_date) values ('103','美杜莎','java',to_date('2023/6/28 00:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202306 (id,name,technology,save_date) values ('104','哪吒','java',to_date('2023/6/28 22:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202306 (id,name,technology,save_date) values ('105','雲韻1','python',to_date('2023/6/29 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202306 (id,name,technology,save_date) values ('106','美杜莎1','python',to_date('2023/6/29 00:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202306 (id,name,technology,save_date) values ('107','哪吒1','python',to_date('2023/6/29 22:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202306 (id,name,technology,save_date) values ('108','雲韻1','python',to_date('2023/6/29 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202306 (id,name,technology,save_date) values ('109','雲韻1','python',to_date('2023/6/29 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202306 (id,name,technology,save_date) values ('110','雲韻1','python',to_date('2023/6/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));

(3)查詢分區數據

sql select count(1) from worker_202306 PARTITION (WORKER20230628); select count(1) from worker_202306 PARTITION (WORKER20230629); select count(1) from worker_202306 SUBPARTITION (worker_id_1); select count(1) from worker_202306 SUBPARTITION (worker_id_2); select count(1) from worker_202306 SUBPARTITION (worker_id_5); select count(1) from worker_202306 SUBPARTITION (worker_id_6);

由於hash分區的緣故,數據分佈不均勻。

6、列表哈希組合分區

(1)建表語句

sql create table WORKER_202307 ( id VARCHAR2(100) not null, name VARCHAR2(200), technology VARCHAR2(100), save_date DATE ) partition by list (technology) SUBPARTITION BY HASH (id) ( partition technology_java values ('java') ( SUBPARTITION worker_id_1, SUBPARTITION worker_id_2, SUBPARTITION worker_id_3, SUBPARTITION worker_id_4 ), partition technology_python values ('python') ( SUBPARTITION worker_id_5, SUBPARTITION worker_id_6, SUBPARTITION worker_id_7, SUBPARTITION worker_id_8 ) );

(2)插入10條數據

sql insert into worker_202307 (id,name,technology,save_date) values ('101','哪吒','java',to_date('2023/7/28 22:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202307 (id,name,technology,save_date) values ('102','雲韻','java',to_date('2023/7/28 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202307 (id,name,technology,save_date) values ('103','美杜莎','java',to_date('2023/7/28 00:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202307 (id,name,technology,save_date) values ('104','哪吒','java',to_date('2023/7/28 22:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202307 (id,name,technology,save_date) values ('105','雲韻1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202307 (id,name,technology,save_date) values ('106','美杜莎1','python',to_date('2023/7/29 00:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202307 (id,name,technology,save_date) values ('107','哪吒1','python',to_date('2023/7/29 22:45:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202307 (id,name,technology,save_date) values ('108','雲韻1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202307 (id,name,technology,save_date) values ('109','雲韻1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss')); insert into worker_202307 (id,name,technology,save_date) values ('110','雲韻1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));

(3)查詢分區數據

sql select count(1) from worker_202307 PARTITION (technology_java); select count(1) from worker_202307 PARTITION (technology_python); select count(1) from worker_202307 SUBPARTITION (worker_id_1); select count(1) from worker_202307 SUBPARTITION (worker_id_2); select count(1) from worker_202307 SUBPARTITION (worker_id_5); select count(1) from worker_202307 SUBPARTITION (worker_id_6);

五、對已有表進行分區

1、先創建一張表,再插入200條數據。

sql create table WORKER_202308 ( id number not null, name VARCHAR2(200), technology VARCHAR2(100), save_date DATE )

2、創建一張新表,建一個範圍分區

sql create table WORKER_202308_tab ( id number not null, name VARCHAR2(200), technology VARCHAR2(100), save_date DATE ) partition by range (id) ( partition WORKER1 values less than (201) );

3、把原表數據插入到新表

```sql select * from WORKER_202308; select * from WORKER_202308_tab;

ALTER TABLE WORKER_202308_tab EXCHANGE PARTITION WORKER1 WITH TABLE WORKER_202308 WITHOUT VALIDATION; ```

成功轉移。

4、刪除原表、新表改名

sql DROP TABLE WORKER_202308; RENAME WORKER_202308_tab TO WORKER_202308;

5、將一個分區拆分成多個分區,分區界限元素必須是一個:字符串,日期時間或間隔文字。

AT括號內不能是字段名稱,改為數字即可。

sql ALTER TABLE WORKER_202308 SPLIT PARTITION WORKER1 AT (180) INTO (PARTITION WORKER2,PARTITION WORKER3);

注意:不能修改分區列的數據類型

通過sql查詢驗證分區是否成功。

sql SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='WORKER_202309';

通過sql查詢分區數據:

```sql select count(1) from WORKER_202309 PARTITION (WORKER1);//分區不存在 ok

select count(1) from WORKER_202309 PARTITION (WORKER2);//179條數據 ok

select count(1) from WORKER_202309 PARTITION (WORKER3);//21條數據 ok ```

6、截斷分區

截斷分區是指刪除某個分區中的數據,並不會刪除分區,也不會刪除其它分區中的數據。

sql ALTER TABLE WORKER_202309 TRUNCATE PARTITION WORKER3;

7、合併分區

sql ALTER TABLE WORKER_202309 MERGE PARTITIONS WORKER2,WORKER3 INTO PARTITION WORKER3 ;

六、小結

使用Oracle這麼久,第一次系統的瞭解Oracle的存儲結構,Oracle -> 表空間 -> 段 -> 區 -> 邏輯數據塊

瞭解了Oracle分區表技術適用於哪些場景、何時分區、分區表的分類,並通過SQL實例進行了實戰演練。