當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

【原创】一个亿级数据库优化过程

發布時間：2023/12/15 数据库 28 豆豆

生活随笔收集整理的這篇文章主要介紹了【原创】一个亿级数据库优化过程小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

第一部分棉花數據庫問題和分析

1.問題sql

數據庫的版本是9i,問題sql有兩個：

Sql1：

SELECT

?c_lotno

???????? FROM b_ctn_normal

?WHERE d_prodatetime BETWEEN to_date('2011-07-01', 'yyyy-mm-dd HH24:MI:SS') AND

??????????????????????????? ?to_date('2012-07-03', 'yyyy-mm-dd HH24:MI:SS')

??????????????????????????? ?AND n_madein = 65

??????????????????????????? ?AND rownum < 31

Sql2:

SELECT count(c_bale)

???????? FROM b_ctn_normal

?WHERE d_prodatetime BETWEEN to_date('2011-07-01', 'yyyy-mm-dd HH24:MI:SS') AND

??????????????????????????? ?to_date('2012-07-03', 'yyyy-mm-dd HH24:MI:SS')

這倆sql其實非常簡單，就是一個按照時間的分頁查詢，一個查詢時間范圍內的總數據量。

但是這個表的數據量很大，41803656條數據，單表容量超過21G。因此查詢非常慢，僅僅查詢30條數據就需要耗費十幾分鐘。甚至查不出結果。

2 表概況

表b_ctn_normal是一個分區表,按照D_VERIFYDATETIME進行了range分區，分區的策略為2010年前每年一個分區,2010年后每月一個分區.該表的數據量為41803656條。

partition by range (D_VERIFYDATETIME)

partition PART_20080101 values less than (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

? partition PART_20090101 values less than (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))—后續的省略

另外該表上建了大量的索引，見表1：

3.表存在的問題

以下是索引的概況統計信息。

NDEX_NAME	DISTINCT_KEYS	NUM_ROWS	SAMPLE_SIZE
I_CTN_NORMAL_99	4	41805079.0630631	8459008
I_NORMAL_MADEIN1	17	41804897.3546108	9544621
I_CTN_NORMAL_66	80	41767143.7096454	9580744
I_CTN_NORMAL_77	86	41473125.0963043	9479665
I_CTN_NORMAL_22	366	41875654.4438373	9937607
I_CTN_NORMAL_11	889	41867424.9314059	11007070
I_CTN_NORMAL_55	1957	40169648.695544	9058949
I_NORMAL_UPLOADTIME1	17253	41866396.7193889	10087608
I_CTN_NORMAL_33	384472	41842227.8696896	10621842
I_NORMAL_D_COLORGRADETIME	1485863	41727490.2734861	9119757
GLOBAL_INDEX_D_VERIFYDATETIME	21162573	41804256	9592128
PRIMARY1_ID	41804473	41804473	9540784
UNI_NORMAL_C_BALE1	41841809	41841809.1781587	7023237

【表1】索引概況

l? 表不是很寬，但是竟然建了13個索引，而且8個索引可選性很差，每個索引都占據不少段空間，極大的浪費了存儲空間。

l? 索引沒有分區，千萬級別的數據量，本身查找索引就很耗時，因此應當對索引分區其高索引檢索性能。

l? 表的索引和表應該建立在不同的表空間分開存放,同時表的分區在不同的表空間存放。

l? 分區的記錄不均勻，分區不合理

分區的統計信息顯示，大量的數據集中在了PART_20100101和PART_20090101分區，分區很不合理，大大削弱了分區表的作用。應該對分區進行細粒度的劃分，均勻分布數據。

TABLE_NAME	PARTITION_NAME	NUM_ROWS	SAMPLE_SIZE
B_CTN_NORMAL	PART_20100101	15580400	2883811
B_CTN_NORMAL	PART_20090101	13007483	2420607
B_CTN_NORMAL	PART_20101201	3809673	709735
B_CTN_NORMAL	PART_20110101	2656138	494675
B_CTN_NORMAL	PART_20101101	2641196	492471
B_CTN_NORMAL	PART_20110201	1169697	217919
B_CTN_NORMAL	PART_20100201	1106187	205854
B_CTN_NORMAL	PART_20110401	662618	123426
B_CTN_NORMAL	PART_20110301	271190	50600
B_CTN_NORMAL	PART_20100501	205173	37933
B_CTN_NORMAL	PART_20100401	194223	35804
B_CTN_NORMAL	PART_20100601	154195	28641
B_CTN_NORMAL	PART_20110501	137085	25587
B_CTN_NORMAL	PART_20100301	105747	19575
B_CTN_NORMAL	PART_20101001	64424	11960
B_CTN_NORMAL	PART_20100701	33743	6206
B_CTN_NORMAL	PART_20100801	4044	725
B_CTN_NORMAL	PART_20100901	283	283
B_CTN_NORMAL	PART_20111001	80	80
B_CTN_NORMAL	PART_20080101	53	53
B_CTN_NORMAL	PART_20111201	21	21
B_CTN_NORMAL	PART_20120601	2	2
B_CTN_NORMAL	PART_20120301	1	1
B_CTN_NORMAL	PART_20110601	0
B_CTN_NORMAL	PART_20110701	0
B_CTN_NORMAL	PART_20120401	0
B_CTN_NORMAL	PART_20120801	0
B_CTN_NORMAL	PART_20120701	0
B_CTN_NORMAL	PART_20120501	0
B_CTN_NORMAL	PART_20120201	0
B_CTN_NORMAL	PART_20120101	0
B_CTN_NORMAL	PART_20111101	0
B_CTN_NORMAL	PART_20110801	0
B_CTN_NORMAL	PART_20110901	0

注:以上的統計信息都是最新收集的.

4.分析制定策略

結合問題sql發現, 兩個查詢共同依賴于d_prodatetime字段的過濾，但是該字段重復值很多，根據統計信息，該列的NUM_DISTINCT為456，因此只依靠索引沒有意義，CBO不會選擇索引而是全表掃描。執行這兩個查詢的時候對數據沒有區分度，選擇了4K萬數據進行全表掃描，效率可寫而至。

而該查詢較為簡單，經過仔細的分析并研究目前的分區策略，我認為最佳的策略是增加范圍分區字段，將表重新分區，分區條件納入d_prodatetime字段。這樣查詢時可以以d_prodatetime進行分區裁減從而減少掃描的數據量。需要分析字段的值的分布區間，平均分配到各分區。經過分析表的數據分布，從節省時間上考慮，就以每兩個月為一個范圍進行分區。

partition by range (D_VERIFYDATETIME, d_prodatetime)

partition PART_20080101 values less than (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'), TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

? partition PART_20090101 values less than (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'), TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

考慮到sql1中有對n_madein的過濾，分析該字段，字段值總共為17種，為number類型，而且該字段值在字段D_VERIFYDATETIME, d_prodatetime表示的區間中分布很均勻，因此，在上述分區基礎上增加list分區，使分區策略變為Rang-list復合分區：

partition by range (D_VERIFYDATETIME, d_prodatetime)

subpartition by list(N_MADEIN)

partition PART_20080101 values less than (range1,range2)

(

subpartition p31 values(64),

?subpartition p32 values(37),

?subpartition p33 values(48),

?subpartition p34 values(55),

……………

? partition PART_20090101 values less than (range3,range4)

(

subpartition p31 values(64),

?subpartition p32 values(37),

?subpartition p33 values(48),

?subpartition p34 values(55),

…………..

)

這樣，sql1在執行時，會首先根據d_prodatetime裁減掉部分數據，然后再根據N_MADEIN再次裁減掉一部分，這樣sql1的性能應該會得到很大提升。而對于僅僅含有N_MAADIN的過濾條件的查詢，都會進行分區裁減，減少數據量。具體性能提高多少，需要測試。

同時，之前的分區字段D_VERIFYDATETIME的粒度應該適當的減小。因為d_prodatetime的重復值較多，以之前的分區粒度，2010年前的是每年一個分區，這樣會導致d_prodatetime分區后數據會很不均勻，若查詢2010年之前的數據，則d_prodatetime裁減的效果會不好，因此需要考慮d_prodatetime的字段值，重新規劃分區粒度。分區粒度的大小需要考慮到d_prodatetime的范圍分布情況。通過分析決定和d_prodatetime字段使用同樣的分區范圍。

由于需要對表重新分區，因此需要重建表。如果在已有的分區策略下增加分區，則直接alter表即可，oracle提供了豐富的方法為不同的分區增加新的分區；但是修改分區策略，必須重建表。而表數據量巨大，單表超過20G，因此數據的加載成為頭疼的問題，如果在生產環境，產生的日志也很巨大。

第二部分試驗過程結果及分析

為了能試驗本文預測的效果,于是我在我本機騰出了30G的空間,將庫置于非歸檔模式，然后用database link以insert append的方式直接加載數據。通過仔細的權衡，我創建的分區表如下（限于時間關系，將所有表分區和索引分區建在一個表空間內）：

--ddl過長，省略(見附件)

（注：復合分區可以為二級分區創建一個template，從而減少建表DDL的篇幅）

考慮只有本文開頭的兩個查詢問題突出，因此我只建立了倆索引，選擇了全局范圍分區索引。

--創建分區索引GLOBAL_INDEX_D_VERIFYDATETIME

CREATE INDEX GLOBAL_INDEX_D_VERIFYDATETIME ON b_ctn_normal(D_VERIFYDATETIME)

?? GLOBAL PARTITION BY RANGE(D_VERIFYDATETIME)(

partition part_index_0 values less than(to_date('2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_1 values less than(to_date('2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_2 values less than(to_date('2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_3 values less than(to_date('2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_4 values less than(to_date('2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_5 values less than(to_date('2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_6 values less than(to_date('2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_7 values less than(to_date('2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_8 values less than(to_date('2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_9 values less than(to_date('2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_10 values less than(to_date('2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_11 values less than(to_date('2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_12 values less than(to_date('2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_13 values less than(to_date('2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_14 values less than(to_date('2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_15 values less than(to_date('2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_16 values less than(to_date('2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_17 values less than(to_date('2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_18 values less than(to_date('2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_19 values less than(to_date('2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_20 values less than(to_date('2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_21 values less than(to_date('2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_22 values less than(to_date('2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_23 values less than(to_date('2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_24 values less than(to_date('2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_25 values less than(to_date('2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_26 values less than(to_date('2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_27 values less than(to_date('2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_28 values less than(to_date('2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index_29 values less than(maxvalue)

)

注：分區索引也可以使用本地前綴索引，可以減少DDL篇幅

考慮到D_PRODATETIME的值較多，當查詢來臨時，oracle首先以查詢where條件中的D_PRODATETIME進行裁減，到目標分區之后，如果有索引的話，應該可以進行INDEX RANGE SCAN進行掃描，因此先建立分區索引試試。同樣選擇了全局范圍分區索引。

--創建分區索引GLOBAL_INDEX_D_PRODATETIME

CREATE INDEX GLOBAL_INDEX_D_PRODATETIME ON b_ctn_normal(D_PRODATETIME)

?? GLOBAL PARTITION BY RANGE(D_PRODATETIME)(

partition part_index1_0 values less than(to_date('2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_1 values less than(to_date('2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_2 values less than(to_date('2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_3 values less than(to_date('2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_4 values less than(to_date('2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_5 values less than(to_date('2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_6 values less than(to_date('2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_7 values less than(to_date('2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_8 values less than(to_date('2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_9 values less than(to_date('2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_10 values less than(to_date('2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_11 values less than(to_date('2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_12 values less than(to_date('2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_13 values less than(to_date('2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_14 values less than(to_date('2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_15 values less than(to_date('2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_16 values less than(to_date('2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_17 values less than(to_date('2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_18 values less than(to_date('2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_19 values less than(to_date('2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_20 values less than(to_date('2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_21 values less than(to_date('2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_22 values less than(to_date('2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_23 values less than(to_date('2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_24 values less than(to_date('2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_25 values less than(to_date('2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_26 values less than(to_date('2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_27 values less than(to_date('2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_28 values less than(to_date('2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),

partition part_index1_29 values less than(maxvalue)

))

下面是加載數據以及實施步驟：

l? 表數據加載

創建db link “ctn”.

Insert /*+append*/ into b_ctn_normal select * from b_ctn_normal@ctn;

加載速度還可以，大概30分鐘加載完,加載數據量4400W。

l? 分別按照上述的策略創建分區索引

GLOBAL_INDEX_D_VERIFYDATETIME和GLOBAL_INDEX_D_PRODATETIME

索引創建約為30分鐘

l? 執行sql1和sql2和原始庫進行對比

Sql1的測試結果對比：

?	返回行數	Pl/sql查詢	Sqlplus跟蹤
優化前	30	>15分鐘	00: 04: 31.62
優化后	30	<0.4秒	00: 00: 00.03

Sql2的測試結果對比：

?	返回行數	Pl/sql查詢	Sqlplus跟蹤
優化前	5529	？	00: 05: 42.67
優化后	5529	<0.02秒	00: 00: 00.01

通過以上對比結果，顯示新的分區策略帶來了巨大的性能提升，顯示了oracle分區技術的強大威力。原來十幾分鐘甚至返回不了結果的查詢現在毫秒就返回數據。

下面分析優化后的執行計劃：

Sql1執行計劃:

通過執行計劃可以看出，查詢正確的使用了d_prodatetime字段進行了分區裁減，然后使用到了該列的分區索引，但是并沒有使用n_madein進行裁減。于是改變了下查詢條件，將查詢的數據量增大：

SELECT

?c_lotno

???????? FROM b_ctn_normal

?WHERE d_prodatetime BETWEEN to_date('2011-07-01', 'yyyy-mm-dd HH24:MI:SS') AND

??????????????????????????? ?to_date('2012-07-03', 'yyyy-mm-dd HH24:MI:SS')

??????????????????????????? ?AND n_madein = 65

??????????????????????????? ?AND rownum < 5000

這次，查詢使用了二級分區裁減。先是對一級分區進行裁減，然后又對二級分區進行裁減，最后對二級分區使用N_MADEIN進行全表掃描。執行計劃顯示，查詢5000條數據時耗時增加了很多，因為掃描的數據量實在太大了，查詢需要掃描很多分區。這樣只能通過減少一次查詢的數據量來保證性能。通過和開發人員確認，一次查詢一般不用返回這么多數據。

Sql2執行計劃:

執行計劃已經顯示的很明確，一級分區按照新分區的字段進行裁減，然后使用建立的分區索引，性能很高。

?????????? 雖然新的分區策略顯示了巨大的性能提升，有效的解決了性能問題，但是仔細分析一下，仍然存在一些問題：

u? 分區較多，在4K萬級別的表上，分區多達493個，這有些過分了。需要減少分區數量。目前的分區是每倆月一個分區，目前的數據分布比重新分區前均勻了很多，但是仍然存在不均勻現象，而且每倆月一個分區仍然較多。因此需要維持現在的范圍分區字段不變，將現在的倆月一個分區的條件變化一下，分析數據的分布區間，制定一個不均勻的分區條件。如2010年8月的數據很多，那可以分別以2010-08-01~2010-08-15~2010-08-30為區間劃分。如果2010年9-12月數據很少，那么可以將9-12月合并為一個分區。盡可能的均勻劃分分區記錄數，也減少分區數量。

u? 評估二級分區的必要性。總的分區數是1級分區和二級分區的乘積，為M*N的關系。二級分區的增加，大大增加了分區數。分析發現，有接近一半的二級分區是空閑的，并沒有記錄裝入，浪費了大量的空間。而且目前的sql并沒有使用到二級分區裁減，因此需要評估二級分區帶來的性能提高。然后考慮是否將二級分區去掉只采用范圍分區。去掉二級分區，目前對性能是沒有壞處的，而且未來如果用到對N_MADEIN字段的裁剪，直接alter表即可增加二級分區，不用重建。因此建議去掉。

l? 總結

分區是處理大表的首要應對策略，但是分區字段的選取和分區的方法需要仔細權衡，一般第一想到的分區字段都是合理的，但是一些隱含的字段沒有考慮到，未來數據量上去了，這些隱含的條件造成的性能問題就暴露出來了，因此還是需要全面的分析。

對表進行了分區，相應的也要對索引進行分區，這樣可以裁減掉部分索引,然后裁減掉記錄,雖然是海量數據,但是卻擁有極高的查詢速度。記得在一本書上看過，作者說，正是因為有了分區技術，oracle才敢號稱是海量數據庫。

Reference:

【http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm#i1009216】

轉載于:https://www.cnblogs.com/zhangxsh/p/3494420.html

總結

以上是生活随笔為你收集整理的【原创】一个亿级数据库优化过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：车载大屏沾满指纹怎么办，通用汽车申请自清
下一篇： linux 的基本命令

数据库

【原创】一个亿级数据库优化过程

第一部分 棉花數據庫問題和分析

1.問題sql

2 表概況

3.表存在的問題

4.分析制定策略

第二部分 試驗過程結果及分析

總結

第一部分棉花數據庫問題和分析

第二部分試驗過程結果及分析