优化与索引

@对表的访问1、全表扫描1、对表所有的块，进行访问，采用多块读的方式2、设置多块读的参数SQL> show parameter db_file_multiblockNAMETYPE VALUE----------------------------------------------- ------------------------------db_file_multiblock_read_count integ er 16上面设置的多块读是16，Oracle读的时候尽量每次读取是16块，Oracle不害怕多块读，害怕的是产生多次物理io的读取成本计算：cost：标的块数/db_file_multiblock_read_count db_file_multiblock_read_count这个参数设置的大小会影响Oracle在计算的时候的一个成本。

如果说这个参数设置的足够大，那么就会导致好多表不走索引，会去走全表扫描3、filter：过滤读取了大量的数据，然后使用条件过滤了大量的数据，剩余了少量的数据行4、filter是否合适，判断标准1、读取了多少数据2、取出了多少数据，过滤了多少数据假设过滤掉99%的数据，那么过滤是失败的90%以上的数据过滤掉，我们就应该考虑这个过滤的价值，也就是cost2、走索引不使用多块读1、成本计算：访问索引的成本+索引访问表的成本访问索引的成本：索引树的高度+叶子节点的块数索引访问表的成本：行数*集群因子/总行数2、集群因子：最小值就是表的块数最大值就是表的行数集群因子高带来的问题：1、计算走索引的时候的成本高2、额外的占用过多的buffer3、额外的增加物理io3、取得数据量一般<5%~20%的话我们建议走索引4、Oracle在计算成本的时候是假设所有的数据都在磁盘中，这样的话计算的成本都有物理读，而实际的生产中我们经常读取的数据，大部分都缓存在内存中5、全表扫描在oltp中肯定会产生额外的一些物理读，因此一般不要全表扫描表整理：表不存在碎片的问题，表时间长了会有一个集群因子很高的问题，有些索引是一个表的主索引，时间长了可以按照主索引对这个表重新生成一下，按照这个主索引排下序，这样的话表的集群因子又会降下来了select d.clustering_factor from dba_indexes d where d.table_name='T1'and owner='SYS '; //查看表的集群因子大小select t.num_rows,t.blocks from dba_tables t where t.table_name='T1'and owner='SYS' ; //查看表的行数和块数SQL> select * from t1 where object_id=10;Execution Plan----------------------------------------------------------Plan hash value: 3617692013--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 8 | 1416 | 155 (2)| 00:00:02 ||* 1 | TABLE ACCESS FULL| T1 | 8 | 1416 | 155 (2)| 00:00:02 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("OBJECT_ID"=10)Note------ dynamic sampling used for this statementStatistics----------------------------------------------------------5 recursive calls //递归sql 产生系统级别的select0 db block gets762 consistent gets //内存读0 physical reads //物理读0 redo size1401 bytes sent via SQL*Net to client // 服务器给客户端发的字节492 bytes received via SQL*Net from client //接收的字节2 SQL*Net roundtrips to/from client //客户端和服务器的响应0 sorts (memory) //内存排序0 sorts (disk)1 rows processed //实际返回一行1、select*from dba_indexes d where d.index_name like'I_T1%'and owner='SYS';blevel：是索引的树的高度，一般在5以下distinct_keys：唯一值得数量avg_leaf_blocks_per_key:平均块中的叶子块avg_data_blocks_per_key:平均每个块中的数据块在这里面有一列是distinct_keys和num_rows 我们希望distinct_keys很高num_rows/distinct_keys若是很高那么就会取出很多行，所以这样是不好的，我们希望的是索引的值很高接近这个num的值索引：1、选择性希望distinct_keys/num_rows的值大一些选择性高说明where条件取出的数值少2、集群因子cluster_factor 取决于索引访问表的值看索引好不好主要是上面的两个值2、关于索引的几个参数SQL> show parameter indNAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_index_caching integer 0 //假设访问索引的时候，索引所有的块都不在内存中，也就是将访问索引的部分也计算其内存读，但是如果说设置成100的时候他会忽略访问索引发生的物理IO，不管是不是在内存中。

optimizer_index_cost_adj integer 100 //生产中一般改成50 ，设置成50表示，在计算成本的时候，走索引的成本*50%这个时候如果是小于全表扫描的成本那么这个时候就会选择走索引skip_unusable_indexes boolean TRUEuse_indirect_data_buffers boolean FALSE3、为什么使用索引看一个SQL语句的执行计划：SQL> set autotrace traceSQL> select * from t1 where object_id=10;Execution Plan----------------------------------------------------------Plan hash value: 852682354----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------| 0 | SELECTSTATEMENT | | 1| 93 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 93 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------//通过上面的可以看出先访问索引，然后通过索引去访问这个表Predicate Information (identified by operation id):---------------------------------------------------2 - access("OBJECT_ID"=10)//access就是直接去找OBJECT_ID=10所对应的值，而不是全部扫描完了去找，而是有目的的去找Statistics----------------------------------------------------------0 recursive calls0 db block gets4 consistent gets0 physical reads0 redo size1401 bytes sent via SQL*Net to client492 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedaccess：直接本主题去找，而不是全部访问一遍，也就是说根据object_id=10走索引直接找到数据往往意味着走索引，直接读取有用的数据，没有读取过多的数据很多时候会将filter改变成access，减少内存读的时候，自然就减少了物理读如何看一个sql的执行优劣1、内存度的数量主要是看的内存高不高2、物理读的话，第一次执行，物理读高，自然时间就长3、不要看执行时间最好去除解析，也就是去除递归sql以后的一些内存读清理buffer cache走索引好处：1、通过走索引，可以避免全表扫描，减少内存读和物理读where条件中2、减少排序SQL> select * from t1 order by object_id;50829 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2148421099-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 50829| 4616K| | 1249 (1) | 00:00:15 || 1 | SORT ORDER BY | | 50829| 4616K| 6264K | 1249 (1) | 00:00:15 || 2 | TABLE ACCESS FULL | T1 | 50829| 4616K| | 156 (2) | 00:00:02 |-----------------------------------------------------------------------------------Statistics----------------------------------------------------------1 recursive calls0 db block gets702 consistent gets0 physical reads0 redo size2547630 bytes sent via SQL*Net to client37760 bytes received via SQL*Net from client3390 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)50829 rows processed通过走索引减少排序select/*+index(t t.i_t1_object_id)*/*from t1 t where object_id=object_id order by object_id;访问表的时候，如果出现了临时段的排序，也就是磁盘排序，严重的影响了性能，那么可以采取上面的措施，可以走索引，走哪个索引？走排序列上的索引3、索引可以引导我们走嵌套循环4、索引减少对表的访问要访问的列都在索引上那么就直接访问索引，不走表select /*+index(t t.i_t1_object_id*/ object_id from t1 where object_id=object_id;5、走索引的情况：1、where子句中使用到了索引列2、没有where子句，但是也可能使用到索引查询索引列的MIN或者是MAXSQL> select min(object_id) from t1;对索引列执行countSQL> select count(object_id) from t1;SQL> select count(*) from t1; //不走索引如果是要走索引那么可以在列上加上非空区别：index fast full scan：多块读取出来的数据是无序的index full scan：通过索引定位到一个表的第一行，一个块一个块的读，加入说是100个块这个时候就会发生100次物理IO也就是单块顺序读，取出来的顺序是有序的5、访问索引经常访问的视图select * from dba_indexes where table_name='T1';select * from dba_ind_columns d where d.table_name ='T1'select * from dba_ind_statistics e where e.table_name='T1;复合索引 (很重要)create index i_t1_id_name on t1(object_id,object_name); //给表t1创建一个索引select * from dba_ind_columns d where d.table_name='T1'; //查看t1表上的列的索引exec dbms_stats.gather_table_stats('SYS','T1'); //收集统计信息select * from dba_ind_statistics e where e.table_name='T1'; //查看一下上面收集的统计信息强制走现在的新索引select /*+index(t t.I_T1_ID_NAME) */ * from t1 t where object_id=100 and object_name='T1';必须有前导列出现在where条件中，才有可能走索引，比如说上面的复合索引的前导列是object_id,这个时候若是执行select /*+index(t t.I_T1_ID_NAME)*/ * from t1 t where object_name='I_IND1';是不走索引的，这是一般情况，也就是在一个表中有两列上存在索引这个时候，如果说上面的and改成or这个时候也不会走索引#不走索引的一些情况1、<>2、orselect*from t3 where object_id=100and object_name like'TAB$'or object_name like'COL$';//这样是不走索引的select*from t3 where object_id=100and(object_name like 'TAB$'or object_name like'COL$');//这样是可以的3、is not null、is null 也不走索引select*from t3 where object_id is not null4、集群因子很高、取的数据量相对大，也可能不走索引，这个可以加hints强制走索引5、列上加了函数，也不走索引，建立基于函数的索引，函数只能是系统自带函数，生产自己建立的函数不能建立基于函数的索引create index i_t3_id_fun ont3(to_char(object_id)); //基于函数建立索引select*from t3 where to_char(object_id)='100' //这个时候就能够走索引6、||也不走索引，解决的方式如下select*from t3 where to_char(object_id)='100'and to_char (object_id)||'A'='100A'#不要随意的建立复合索引1、索引大，会带来额外的物理io2、对dml会有影响#跳跃式索引扫描在两个列上建立索引，在一个列上的值很少，另一个列上的值很多，这个时候就会跳跃式扫描假如是object_id只有3个值，执行select * from t1 where object_name='T1'的时候Oracle会自动转换成select * from t1 where object_id=1 and object_name='T1';union allselect * from t1 where object_id=2 and object_name='T1';union allselect * from t1 where object_id=3 and object_name='T1';#建立复合索引的目的和判断标准复合索引建立完成以后，相对只在前导列上建立索引1、distinct keys有明显提高2、索引的块数不要增加太多,也就是索引不要太大通过这种方式，可以大幅的对系统的复合索引进行修改但是有一种情况，需要注意select object_name from t1 where object_name='T1' // name 这一个列上有复合索引，这个时候就会走复合索引，但是如果说在name列上没有复合索引，那么就不会走索引，如果说这个表很大，那么这个时候就会进行一个大表的全表扫描，造成效果比较差看执行计划：实际上没有执行SQL> explain plan for select * from t1 where object_id=101;Explained.SQL> select * from table(dbms_xplan.display);可以通过rowid去访问一个表，但是实际的生产中是不会这样的SQL> select object_id,rowid from t1 where rownum=1;OBJECT_ID ROWID---------- ------------------20 AAANjrAABAAAOAiAAASQL> set autotrace traceSQL> select * from t1 where rowid='AAANjrAABAAAOAiAAA';Execution Plan----------------------------------------------------------Plan hash value: 487051824-----------------------------------------------------------------------------------| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |//rows是一个估算值，不准//cost是一个累积值，在0的时候花费的是0是估算值主要是包括cpu+IO 在这cpu消耗是0 //time也是估算值，也是累计值-----------------------------------------------------------------------------------| 0 | SELECTSTATEMENT | | 1 |93 | 1 (0) | 00:00:01 || 1 | TABLE ACCESS BY USER ROWID|T1 | 1 | 93 | 1 (0) | 00:00:01 |-----------------------------------------------------------------------------------Statistics----------------------------------------------------------0 recursive calls0 db block gets1 consistent gets0 physical reads0 redo size1397 bytes sent via SQL*Net to client492 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed##总结为什么走索引1、从表中取少量的数据，尽量走索引<5%~20%2、表中存放的数据有新旧之说，生产上不会访问旧的数据，一直在访问新的数据新的数据一般都在buffer中，走索引，一般不会发生大量的物理io，即使数据量大到20%，而且集群因子很高，走索引效果也很好，不要走全表扫描hash join往往会让我们走全表扫描##索引的特点1、索引很小往往被缓存到内存中去，所以索引常驻内存，这个时候访问索引的成本，物理io很低常用的视图：select*from v$segment_statistics where object_name='T1';statistic_name这一列中值得含义statistic_name这个是一个累计信息--logical reads 若是很高，可能说明索引建立的有问题--若是一个对象buffer busy waits很高说明这个对象是一个热对象--gc buffer busy是指在rac中有一个块在节点1上这个时候有进程在访问这个块，这个时候另一个节点也想访问这个块，这个时候就会造成gc buffer busy当这个值很高的时候说明这个对象也是热对象--db block changes 数据块被改变过就加1，如果说这个值很高，那么就说明这个块别改变很频繁--physical reads如果说这个这个索引的物理读很高，那么说明这个索引建立的非常大，可能是复合索引，dbwrite完成的--physical reads direct是一个server process直接从磁盘中将数据读到PGA中去，不经过buffer，server process完成的--gc cr blocks received表示在远端传过来的量，如果说这个值比较大，说明对这个段进行大量的读取，需要在远端进行数据的读取，将数据块构造出来--gc current blocks received--ITL waits产生这个说明存在了事物槽的争用，可以增加这个段的pct free--segment scans代表对一个表的全表扫描的次数，若是高则说明是全表扫描很高一个对象改变量很大的时候我们希望这个块常驻一个节点，不要在两个节点之间传来传去2、排序、树，可以快速定位数据#索引的选择性和集群因子1、索引的选择性越高索引越好，最好是接近表的行数2、集群因子，越低索引越好，最好是接近表的块数#柱状图1、什么时候需要柱状图一个列上有where条件这个列上有严重的数据倾斜2、柱状图是什么东西可以描述这个列上的一个数据倾斜的情况，柱状图来存储这个列上的数据的情况1、统计信息2、需要额外收集3、反映这个列上的数据倾斜情况3、柱状图的收集方法添加柱状图后，发生数据倾斜那部分是不走索引的，没有发生数据倾斜的部分还是要走索引的--收集柱状图begindbms_stats.gather_table_stats(ownname =>'SYS',tabname =>'T3',method_opt =>'for col umns size auto object_id');end;--可以查看到这个表的一些情况，尤其是列值，柱状图select*from dba_tab_col_statistics d where d.table_name='T3'and d.owner='SYS'--能够看到列上面的数据倾斜的情况select*from dba_tab_cols e where e.table_name='T3'and e.owner='SYS';--查看柱状图的信息select*from dba_histograms f where f.owner='SYS'and f.table_name='T3';--endpoint_number是指占的桶的数量4、关注一个参数cursor_sharing有3个值：EXACT:对于一个select语句:select …… from t1 where object_id=100；oracle对这个原本的select的语句做hash，然后去library cache中去寻找执行计划cursor有，soft parse无，hard parseFORCE:直接将上面的select语句替换成select …… from t1 where object_id=:1;然后进行hash，然后去library cache中去寻找执行计划cursor有，soft parse无，hard parseSIMILAR:如果where列上存在柱状图，说明有数据倾斜，那么不使用绑定变量，同exact 如果说where列上不存在数据倾斜，使用替换绑定变量，同force#索引类型1、B树索引2、位图索引 //用在数据仓库里面的3、索引组织表IOT4、基于函数的索引5、分区索引（本地索引、全局索引）OLTP数据库中用的最多的是B树索引&B树索引（普通索引，要求列的选择性很高）单列索引组合索引&位图索引（在OLTP中基本上不会采用）可以提取大量的数据，25%1、列的选择性低（列上唯一值得数量）必须很低2、create bitmap index3、DML并发性非常差特别是对于insert和delete4、表如果是只读表，而且存在大批量取数据的情况】可以考虑位图索引&IOT索引组织表可以考虑使用1、在一个表中取相对大量的数据2、范围取数据，between and没有cluster factor的概念，省去了通过索引从表中取数据的这个步骤3、如果不是按照主键取数据，就比较麻烦需要建立二级索引性能相对较差4、对于DML成本较高对主键的update较少数据的插入删除插入新数据，删除旧数据新旧按照主键5、对于旧数据抽走的情况，不太适合配合分区，分区一旦被抽空，直接drop分区即可&基于函数的索引尽量修改SQL，让where后面的列上面不要有函数，尽量将函数放在列值上面，不要放在列上&分区索引解决的问题是索引大的问题如果能够实现索引分区消除，效果最好对于分区索引的理解对于表是否分区，以及怎么分区，可以认为没有关系索引分区可以实现访问一个小的索引，也可能是访问多个小的索引，有一些索引分区被消除索引没有分区访问一个大的索引大索引和小索引的区别：树的高度不一样，索引的高度一般<=6树枝和树根都在内存里面索引分区基本上没有意义1、全局分区索引（效果很差）表分区和索引分区没有任何关系表的分区的改变，会导致全局分区索引失效alter table …… update global in dex2、局部分区索引（索引的分区和表的分区完全一致），也可以叫local前缀：表的分区列出现在索引中，而且是第一列非前缀：表的分区列没有出现在索引中，或者出现在索引的非第一个列中3、我们可以考虑索引分区情况1、这个索引的物理io很高可能是内存空间有限，系统数据量很大2、索引访问很频繁3、对索引进行分区，主要是分散物理io4、局部分区索引可以降低分区表的索引维护成本#解决物理io的思路1、尽量内存读，不要物理读2、将物理读分散#降低io有效的办法：1、索引2、cluster factor3、分区表、索引4、IOT（局限性大）5、位图索引、聚簇表（局限性大）6、物化视图上面的集中办法组合使用##补充知识1、有意识建立索引2、有意识建立合适的索引索引的建立1、开发人员10W行以上的表需要建立索引一个表上尽量不要超过6个索引表的索引的建立：访问这个表的时候，总是通过哪些列作为过滤条件，而且这个过滤条件可以过滤掉大部分数据OLTP1、过滤条件访问表2、过滤掉绝大多数数据2、针对sql看执行计划，filter条件，如果filter过滤大量的数据，但是没有出现在access里面，可以针对这个SQL增加索引这个索引解决了这个SQL问题，可能会导致其他SQL性能差尽量避免在列上建立重复索引如果建立这个索引以后，这个SQL的性能得到大幅提升，本质提升，这个SQL目前已经影响系统性能了，这个时候会考虑针对SQL建立索引在一个SQL上建立索引后，这个SQL的性能比以前有提高，但是不是很明显，而且这个SQL目前运行的还行，那么在这个SQL上建立索引会导致其他SQL的问题3、建立索引要统筹规划，统一建立索引，统一对系统进行调优最好做好预案col_usage$dba_tab_cols4、在表的外键上建立索引索引的三大功能：1、减少全表扫描，也就是减少物理io，通过索引在表中取数据2、可以省略sort，通过索引访问表的时候本身就是一行一行有序的取得，因为排序可能会导致性能很差，导致磁盘排序3、要访问的列如果说在索引中并且索引效率高是最好的索引的坏处：1、索引多了对增删改，特别是update影响比较严重，一个表上的索引最好不要超过6个2、建索引的时候，要对这个表进行锁表，对这个表进行全表扫描，这样的话可能需要10分钟、20分钟等，那么生产上这个表就会被锁住，极端情况下会导致数据库hang住，还有可能造成磁盘排序，产生磁盘io，极端情况下会导致数据库hang住3、在数据库上建了一个索引，这个索引可能将这个sql的性能问题解决了，但是可能导致其他的sql 也走这个索引，导致数据库的性能突然降下来所以在生产上建索引的时候要谨慎，通盘考虑，平时还要注意，在建索引的时候，也就是rebuild 的时候加上online，也就是在原来的基础上建立索引，这样的话避免了排序。