爱站网备案查询,海口网站开发制作,网站建设软文推广,宝安品牌网站制作背景#xff1a;
业务系统中现在经常存在上亿数据的大表#xff0c;在这样的大表上新建索引#xff0c;是一个较为耗时的操作#xff0c;特别是在生产环境的系统中#xff0c;添加不当#xff0c;有可能造成业务表锁表#xff0c;业务表长时间的停服势必会影响正常业务…背景
业务系统中现在经常存在上亿数据的大表在这样的大表上新建索引是一个较为耗时的操作特别是在生产环境的系统中添加不当有可能造成业务表锁表业务表长时间的停服势必会影响正常业务的开展。根据个人的实际经验我们可以使用三种手段来帮助大家解决这个问题需要注意的是这三种方法并不是独立使用的很多时候我们会结合起来一起使用来提升建索引的效率。解决方案
第一种方法就是使用并行——parallel 开启并发执行并发执行可以最大程度的利用我们的数据库的硬件资源把大批量的数据分成小批量到不同的进程去执行从而大大减少sql的执行的时间。由于建索引属于ddl操作我们可以通过下面的语句来实现并发执行。 下面的语句中我们就配置了使用并发值为8来执行我们的sql语句CREATE INDEX idx_table1_column1 ON table1 (column1) PARALLEL 8; 注意并不是所有的系统都适用使用并行来解决比如目前有个系统使用cpu已经很高如果这时你再开启并行只会加重系统的负载。因此在执行并行操作前一定要看一下系统目前使用情况。第二种方法是不开启日志——nologging我们知道数据表新增、修改、删除记录都可能会触发redo日志和undo日志的记录特别是insert into table1 select * from table2这种语句每条insert动作都会同时生成redo日志和undo日志从而降低sql的执行速度。 对于创建索引的操作也是如此索引的创建同样也涉及到这两类日志的记录我们可以手动指定不记录非必要日志来加快sql执行的速度。注意nologging的核心在于只输入最少的redo日志注意这里不是不输出日志只是最小化需要输入的日志量而已 用法的话十分简单只需要在我们创建索引的语句上加上nologging关键字即可
CREATE INDEX idx_table1_column1 ON table1 (column1) nologging;
第三种方法是在线执行——online推荐使用前面介绍的两个命令虽然能大幅度提升效率但归根结底建索引就是会导致锁表不停服执行的话还是相当有风险的online的作用在于不阻塞DML操作使得生产环境不会因为执行DDL语句导致业务功能阻塞 尤其适合于不停机新建表索引这类场景。需要注意的是online关键字的使用相对来说耗时会长一些而且online关键字只能用于新增索引并不能用在修改表结构等SQL语句中。 online的使用也十分简单在sql语句后面加上online就行。
CREATE INDEX idx_table1_column1 ON table1 (column1) online;
有了这三个方法我们的最终的sql大概是这样的有了online可以保障不影响业务主流程的进行而nologging和parallel则可以大幅度提高我们sql的执行速度个人觉得是一种可行的解决方案。
CREATE INDEX idx_table1_column1 ON table1 (column1) parallel 8 nologging online ;
很多朋友认为到这里就结束了其实oracle数据库优化的空间永无止境如果有朋友想追求最佳想把数据库的性能发挥到最佳。那么下面还有三种方法但是这些不常用作为学习数据库的原理可以了解一下。 补充方法1由于创建索引时需要对表进行全表扫描,可以适当考虑调大db_file_multiblock_read_count的值 db_file_multiblock_read_count影响Oracle在读取数据时一次读取的最大block数量在进行一些数据量比较大的操作时可以适当 调整当前session的db_file_multiblock_read_count值会在IO上节省节省一些时间。
SQL show parameter db_file
NAME TYPE VALUE db_file_multiblock_read_count integer 128
SQL alter session set db_file_multiblock_read_count256;
Session altered.
SQL show parameter db_file
NAME TYPE VALUE db_file_multiblock_read_count integer 256
补充方法2我们知道索引都是有序的利用索引的这个特性因此我们可以想到在创建索引时要把索引列的值拿到内存中进行排序因此我们调整排序区的大小(sort_area_size)建立索引时要对大量数据进行排序操作 在oracle11g,如果workarea_size_policy的值为AUTO,sort_area_size将被忽略pga_aggregate_target将被启用,pga_aggregate_target决定了整个 的pga大小而且一个session并不能使用全部的pga大小它受到一个隐藏参数的限制大致能使用pga_agregate_target的5%因此可以 考虑将workarea_size_policy的值为manual然后设置较大的sort_area_size以满足需求。
SQL alter system set workarea_size_policy‘MANUAL’;
System altered.
SQL alter session set sort_area_size204800;
Session altered.
SQL show parameter sort_area_size;
NAME TYPE VALUE sort_area_size integer 204800
补充方法3 为了让添加索引的表能尽快加载到数据缓存区中buffer cache,我们可以使用cache和full hint对源表做fts,以使它尽可能的出现在 buffer cache中LRU的MRU一端。
SQL select /* cache(t) full(t) / count() from big_table t;
打扫战场添加完索引后把打扫一下战场把战场恢复到操作之前因此我们要把调整的参数进行恢复到原来的样子。
SQL alter system set workarea_size_policy‘AUTO’;
System altered.
SQL alter session set db_file_multiblock_read_count 128;
Session altered.
