背景

業(yè)務(wù)系統(tǒng)中現(xiàn)在經(jīng)常存在上億數(shù)據(jù)的大表，在這樣的大表上新建索引，是一個較為耗時的操作，特別是在生產(chǎn)環(huán)境的系統(tǒng)中，添加不當(dāng)，有可能造成業(yè)務(wù)表鎖表，業(yè)務(wù)表長時間的停服勢必會影響正常業(yè)務(wù)的開展。

根據(jù)個人的實際經(jīng)驗，我們可以使用三種手段來幫助大家解決這個問題，需要注意的是這三種方法并不是獨立使用的，很多時候我們會結(jié)合起來一起使用來提升建索引的效率。

解決方案

第一種方法就是使用并行——parallel 開啟并發(fā)執(zhí)行

并發(fā)執(zhí)行可以最大程度的利用我們的數(shù)據(jù)庫的硬件資源，把大批量的數(shù)據(jù)分成小批量到不同的進程去執(zhí)行，從而大大減少sql的執(zhí)行的時間。

由于建索引屬于ddl操作，我們可以通過下面的語句來實現(xiàn)并發(fā)執(zhí)行。

下面的語句中，我們就配置了使用并發(fā)值為8來執(zhí)行我們的sql語句

CREATE INDEX idx_table1_column1 ON table1 (column1) PARALLEL 8;

注意：

并不是所有的系統(tǒng)都適用使用并行來解決，比如：目前有個系統(tǒng)使用cpu已經(jīng)很高，如果這時你再開啟并行，只會加重系統(tǒng)的負載。因此，在執(zhí)行并行操作前一定要看一下系統(tǒng)目前使用情況。

第二種方法是不開啟日志——nologging

我們知道數(shù)據(jù)表新增、修改、刪除記錄都可能會觸發(fā)redo日志和undo日志的記錄，特別是insert into table1 select * from table2這種語句，每條insert動作都會同時生成redo日志和undo日志，從而降低sql的執(zhí)行速度。

對于創(chuàng)建索引的操作也是如此，索引的創(chuàng)建同樣也涉及到這兩類日志的記錄，我們可以手動指定不記錄非必要日志來加快sql執(zhí)行的速度。

注意：

nologging的核心在于只輸入最少的redo日志（注意，這里不是不輸出日志，只是最小化需要輸入的日志量而已） 用法的話十分簡單，只需要在我們創(chuàng)建索引的語句上加上nologging關(guān)鍵字即可

CREATE INDEX idx_table1_column1 ON table1 (column1) nologging;

第三種方法是在線執(zhí)行——online（推薦使用）

前面介紹的兩個命令雖然能大幅度提升效率，但歸根結(jié)底建索引就是會導(dǎo)致鎖表，不停服執(zhí)行的話還是相當(dāng)有風(fēng)險的，online的作用在于不阻塞DML操作，使得生產(chǎn)環(huán)境不會因為執(zhí)行DDL語句導(dǎo)致業(yè)務(wù)功能阻塞， 尤其適合于不停機新建表索引這類場景。

需要注意的是：

online關(guān)鍵字的使用相對來說耗時會長一些，而且online關(guān)鍵字只能用于新增索引，并不能用在修改表結(jié)構(gòu)等SQL語句中。

online的使用也十分簡單，在sql語句后面加上online就行。

CREATE INDEX idx_table1_column1 ON table1 (column1) online;

有了這三個方法，我們的最終的sql大概是這樣的，有了online可以保障不影響業(yè)務(wù)主流程的進行，而nologging和parallel則可以大幅度提高我們sql的執(zhí)行速度，個人覺得是一種可行的解決方案。

CREATE INDEX idx_table1_column1 ON table1 (column1) parallel 8 nologging online ;

很多朋友認(rèn)為到這里就結(jié)束了，其實oracle數(shù)據(jù)庫優(yōu)化的空間永無止境，如果有朋友想追求最佳，想把數(shù)據(jù)庫的性能發(fā)揮到最佳。

那么下面還有三種方法，但是這些不常用，作為學(xué)習(xí)數(shù)據(jù)庫的原理，可以了解一下。

補充方法1：

由于創(chuàng)建索引時需要對表進行全表掃描,可以適當(dāng)考慮調(diào)大db_file_multiblock_read_count的值， db_file_multiblock_read_count影響Oracle在讀取數(shù)據(jù)時一次讀取的最大block數(shù)量，在進行一些數(shù)據(jù)量比較大的操作時，可以適當(dāng) 調(diào)整當(dāng)前session的db_file_multiblock_read_count值，會在IO上節(jié)省節(jié)省一些時間。

SQL> show parameter db_file
NAME TYPE VALUE
db_file_multiblock_read_count integer 128
SQL> alter session set db_file_multiblock_read_count=256;
Session altered.
SQL> show parameter db_file
NAME TYPE VALUE
db_file_multiblock_read_count integer 256

補充方法2：

我們知道索引都是有序的，利用索引的這個特性，因此我們可以想到，在創(chuàng)建索引時，要把索引列的值拿到內(nèi)存中進行排序，因此我們調(diào)整排序區(qū)的大小(sort_area_size)，建立索引時要對大量數(shù)據(jù)進行排序操作 在oracle11g,如果workarea_size_policy的值為AUTO,sort_area_size將被忽略，pga_aggregate_target將被啟用,pga_aggregate_target決定了整個 的pga大小，而且一個session并不能使用全部的pga大小，它受到一個隱藏參數(shù)的限制，大致能使用pga_agregate_target的5%，因此可以 考慮將workarea_size_policy的值為manual，然后設(shè)置較大的sort_area_size以滿足需求。

SQL> alter system set workarea_size_policy=‘MANUAL';
System altered.
SQL> alter session set sort_area_size=204800;
Session altered.
SQL> show parameter sort_area_size;
NAME TYPE VALUE
sort_area_size integer 204800

補充方法3 ：

為了讓添加索引的表能盡快加載到數(shù)據(jù)緩存區(qū)中buffer cache,我們可以使用cache和full hint對源表做fts,以使它盡可能的出現(xiàn)在 buffer cache中LRU的MRU一端。

SQL> select /*+ cache(t) full(t) / count() from big_table t;

打掃戰(zhàn)場：添加完索引后，把打掃一下戰(zhàn)場，把戰(zhàn)場恢復(fù)到操作之前，因此我們要把調(diào)整的參數(shù)進行恢復(fù)到原來的樣子。

SQL> alter system set workarea_size_policy=‘AUTO';
System altered.
SQL> alter session set db_file_multiblock_read_count = 128;
Session altered.

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論