关于数据库大表创建索引建议

关于数据库大表创建索引建议

1、估算索引所需空间

Oracle提供了存储过程DBMS_SPACE.CREATE_INDEX_COST来估算索引的大小，测试脚本：

declare
  used_bytes  number(10);
  alloc_bytes number(10);
begin
  dbms_stats.gather_table_stats('SCOTT', 'ORDER_PRODUCT');
  dbms_space.create_index_cost('create index ind_prouduct_id  on order_product (product_id)',
                               used_bytes      => used_bytes,
                               alloc_bytes     => alloc_bytes);
  dbms_output.put_line('used_bytes : ' || used_bytes/1024 || ' KB');
  dbms_output.put_line('alloc_bytes : ' || alloc_bytes/1024 || ' KB');
end;
/

used_bytes : 396.09375 KB
alloc_bytes : 2048 KB

PL/SQL procedure successfully completed

2、调整会话参数

（1）设置手动管理pga

SQL> show parameter workarea_size_policy;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO

SQL> alter session set workarea_size_policy=manual; 

Session altered

（2）调整PGA

SQL> show parameter sort_area_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     65536

--设置PGA 1g
SQL> alter session set sort_area_size=1073741824;

Session altered


Executed in 0.003 seconds


SQL> show parameter sort_area_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     1073741824

--一般默认是false
alter session set db_block_checking=false;

（3）多块读参数

SQL> show parameter db_file_multiblock_read_count;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     32

SQL> alter session set db_file_multiblock_read_count=128; 

Session altered


Executed in 0.006 seconds

--设置多块读为128,也就是一次io希望他尽可能的读到多的块
SQL> show parameter db_file_multiblock_read_count;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128

（4）适度使用并行参数

 --parallel - 依据服务器CPU个数而定，对单CPU最好不使用parallel
SQL> create index ind_prouduct_id  on order_product (product_id) nologging parallel 2 tablespace tb_scott; 

Index created


Executed in 1.981 seconds

（5）恢复索引非并行和日志模式

去掉并行，将索引改为写日志模式
alter index ind_prouduct_id  noparallel;
alter index ind_prouduct_id   logging;

SQL> alter index ind_prouduct_id  noparallel;

Index altered


Executed in 0.02 seconds


SQL> alter index ind_prouduct_id   logging;

Index altered


Executed in 0.087 seconds

（6）恢复PGA

--设置自动管理PGA
alter session set workarea_size_policy=AUTO;
SQL> alter session set workarea_size_policy=AUTO;

Session altered


Executed in 0.003 seconds

3、大数据量表测试验证

（1）数据量

6400万的数据量

SQL> select t.TABLE_NAME,t.NUM_ROWS from user_tables t where t.TABLE_NAME='ORDER_CONTRACT';

TABLE_NAME                                                                         NUM_ROWS
-------------------------------------------------------------------------------- ----------
ORDER_CONTRACT                                                                     64050000

Executed in 0.111 seconds

（2）预估索引空间

alloc_bytes : 1677721600
估算索引需要1600MB的空间，因为做了数据搜集，耗时1个小时多一点。

SQL> declare
  2    used_bytes  number(10);
  3    alloc_bytes number(10);
  4  begin
  5    dbms_stats.gather_table_stats(user, 'ORDER_CONTRACT');
  6    dbms_space.create_index_cost('create index ind_contract_date_empno on order_contract(contract_date,empno)',
  7                                 used_bytes      => used_bytes,
  8                                 alloc_bytes     => alloc_bytes);
  9    dbms_output.put_line('used_bytes : ' || used_bytes);
 10    dbms_output.put_line('alloc_bytes : ' || alloc_bytes);
 11  end;
 12  /
 
used_bytes : 832650000
alloc_bytes : 1677721600

PL/SQL procedure successfully completed


Executed in 3889.222 seconds

（3）对比

A . 会话做参数优化

因为前后做了几次，为了更准确，清理了内存：
alter system flush buffer_cache;
alter system flush shared_pool;

SQL> alter system flush buffer_cache;

System altered


Executed in 0.102 seconds


SQL> alter system flush shared_pool;

System altered


Executed in 0.063 seconds


SQL> drop index ind_contract_date_empno ;

Index dropped


Executed in 0.819 seconds


SQL> create index ind_contract_date_empno on order_contract(contract_date,empno) nologging parallel 2 tablespace tb_index;

Index created


Executed in 257.922 seconds

优化用时：257.922 seconds

B . 会话用默认参数

同样清理内存
alter system flush buffer_cache;
alter system flush shared_pool;

SQL> alter system flush shared_pool;

System altered


Executed in 4.703 seconds


SQL> alter system flush buffer_cache;

System altered


Executed in 2.228 seconds


SQL>  alter system flush shared_pool;
SQL> 
SQL> 
SQL> drop index ind_contract_date_empno ;

Index dropped


Executed in 0.524 seconds


SQL> 
SQL> 
SQL> create index ind_contract_date_empno on order_contract(contract_date,empno) nologging parallel 2 tablespace tb_index;

Index created


Executed in 266.428 seconds

默认用时： 266.428 seconds

257.922/266.428=0.96807392616391670545137898419085
性能提高3.2%
与期望值差距比较大，原来以为有10-20%的性能提升，也许数据量不够大吧。

4、索引实际使用空间

SQL> select t.segment_name,t.BYTES/1024/1024 mb
  2   from user_segments t where t.segment_name=upper('ind_contract_date_empno');

SEGMENT_NAME                                                                             MB
-------------------------------------------------------------------------------- ----------
IND_CONTRACT_DATE_EMPNO                                                            1686.125

Executed in 0.078 seconds

实际索引使用1686.125MB，与估算1600MB误差不大，准确度比较高！