67-oracle数据库，有索引，但是没有被使用的N种情况，以及应对方法（上篇）

如果说性能优化是数据库技术中的明珠，那么索引无疑是其中最耀眼的一颗，特别是OLTP业务数据库。掌握了索引技术，基本上性能就不会有太大的问题。

有时我们创建了字段上的索引，但是通过执行计划却发现索引并没有被使用，还是会使用全表扫描。随着表上数据量的增长，性能会越来越差。如果不能查明原因，就只能盲目的靠不断扩容硬件来缓解（不是解决）这类问题，投资巨大，收益甚微。有时实在没办法，只能通过定期清空表来解决。

对于大表来说，能否使用到索引，效率上可能会相差上万倍（表越大，性能差距就越大）。下面我们就列举一些有索引，但是没有被使用的10几种情况，并给出对应的解决方法：

1. 索引状态为UNUSABLE --普通索引 select * from dba_indexes where status='UNUSABLE'; （分区表global index比较容易因为删除分区导致unusable） --分区索引 select * from dba_ind_partitions where status='UNUSABLE'; --子分区索引 select * from dba_ind_subpartitions where status='UNUSABLE'; 应对方法： 处于unusable状态的索引，需要rebuild ，才可能被使用。 alter index idx_name rebuild; alter index idx_name rebuild partition part_name; alter index idx_name rebuild subpartition subpart_name;
2. 索引状态为INVISIBLE select table_owner,table_name,index_name, visibility from dba_indexes where visibility='INVISIBLE'; 应对方法： alter index idx_name visible; 如果把参数optimizer_use_invisible_indexes 改为true（默认是false），那么invisible状态的索引也可以正常使用，不建议。
3. 优化器估算使用索引COST高： 3.1 字段的NDV（唯一值个数）小，数据平均分布，不使用索引是正确的选择： 比如在正常的gender（性别）字段上创建索引，where gender='M' ，即使gender字段上有索引，也不会使用，但是可以使用hint强制使用该索引，只是效率不如不使用索引。 如果还有除了M、F外的其他性别，占的比例又比较少，比如：where gender='X' ，这种情况优化器会自动使用gender字段上的索引（字段上的直方图信息是选择使用索引的依据）。 应对方法： 正确选择，不需要干预。如果修改了optimizer_index_caching或optimizer_index_cost_adj参数，让优化器误用低效索引，性能反而会下降。 3.2 字段的NDV（唯一值个数）小，分布不均，但是没有直方图信息： 上面例子，如果gender字段上没有直方图信息，即便我们知道where gender='X' 使用索引效率高，优化器也不会使用索引。因为在没有直方图信息的情况下，优化器会按照数据平均分布来计算COST，唯一值越小，COST越高。 应对方法： 可以使用hint强制使用索引。 直方图信息默认会自动收集，不建议主动关闭直方图信息收集。有了直方图信息，索引会自动使用。 3.3 字段数据分布不均，有直方图信息。但是SQL使用了绑定变量，而且隐含参数_optim_peek_user_binds（绑定变量窥视）被关闭（默认是开启，有少量生产数据库关闭了这个参数）： 还是上面例子，where gender=:b1 ，在b1赋值 'X' 时，我们希望SQL执行计划能使用索引，但是在绑定变量窥视关闭的情况下，优化器不会选择使用索引，理由还是cost高。 应对方法： 如果绑定变量b1经常使用的变量值是'X'，这种情况可以使用hint强制使用索引。 如果绑定变量值仍有一些使用'F'、'M'的查询，这种情况不建议使用hint强制使用一种执行计划。建议开启绑定变量窥视（生产系统为了避免全局影响，可以在sql级别通过hint开启绑定变量窥视/*+ OPT_PARAM('_optim_peek_user_binds' 'true') */），并且保持ACS（自适应游标）开启，必要时还需要配合 bind_aware的hint。 3.4 like '%abcde%'， select ... from t1 where object_name like '%abcde%'这种常规写法，优化器计算使用索引COST高，不会使用。只能通过改写，再配合相关hint，才能起到优化效果： 错误应对方法： 下面常规写法SQL，object_name字段上有索引，使用hint也可以强制使用该索引，但是，效率比不使用索引还要差（index full scan）： Select /*+ index(t1 idx_t1_object_name) */ object_id,object_type,object_name from t1 where object_name like '%ABCDE%'; 正确应对方法： 如果返回的行数比较少，可以做如下改写（配合hint）： Select object_id ,object_type,object_name from t1 Where object_name in (select /*+ cardinality(t1 5) */object_name from t1 where object_name like '%ABCDE%');
4. 索引不保存全是NULL的记录 4.1 select * from t1 where object_id is null； 这种SQL的谓词条件写法，object_id字段上的索引无法被使用，因为索引不保存全是null的条目。这种情况即使使用hint也无法使用索引。 应对方法： 如果要使用索引，需要与一个非空内容做组合索引：create index idx_t1_object_id on t1(object_id,0); 原object_id字段上的索引可以删除掉。 为了避免这种情况，一般索引字段在设计表时，都设计为not null，同时就会杜绝col_name is null的写法。 4.2 select * from (select * from t1 order by object_id desc) where rownum<=10； 这种sql写法也与4.1一样，如果object_id 定义为not null，优化器会自动使用索引。如果定义为null，应对方法同4.1
5. 字段上使用了函数或是做了运算 下面列举了一些sql写法，这样的写法，无法使用字段上的普通索引，需要创建对应的函数索引，才能使用索引： 5.1 to_char(cdate,'yyyymmdd')='20191027' 应对方法： 如果不能改sql，只能创建函数索引: create index idx_name on tname (to_char(cdate,'yyyymmdd')); 如果可以改sql，建议改成 cdate >=to_date('20191027') and cdate<to_date('20191027')+1 5.2 substr(name,1,10)=:b1 应对方法： 不能改sql：只能再创建函数索引； 可改写sql：name like :b1||'%' and length(:b1)=10; 5.3 cdate+1/24>sysdate 应对方法： 不能改sql：只能再创建cdate+1/24函数索引（如果还有cdate+1/48>sysdate，还需要再创建一个cdate+1/48的函数索引）； 可改写sql：需要改成 cdate>sysdate-1/24 （ cdate >sysdate - 1/48）
6. 几种隐式类型转换 6.1 varchar2类型的字段，谓词条件变量类型是number类型： phone_no=87654321 ; 这种情况，oracle会自动转换成to_number(phone_no)=87654321; 导致无法使用phone_no字段上的正常索引。 应对方法： 能改代码： 更改变量类型定义，将number类型改成varchar2类型； 如果是关联字段类型不匹配（nested loop，驱动表关联字段类型是number类型，被驱动表关联字段类型是varchar2类型），则需要在nested loop驱动表的关联字段上使用to_char函数。 如: select ... from t1,t2 where t1.num_col=t2.varchar2_col and t1.object_name='xxx'; 改成：select ... from t1,t2 where to_char(t1.num_col)=t2.varchar2_col and t1.object_name='xxx'; 不能改代码： 创建to_number(phone_no)函数索引。 6.2 date类型的字段，谓词条件变量类型是timestamp类型： cdate>=:b1 (b1 类型是timestamp)；这种情况，oracle需要将cdate通过内部函数(internal_function）转换成timestamp类型，导致无法使用cdate字段上的正常索引。 应对方法： 只能通过修改代码，将变量类型从timestamp改成date，或者将字段类型从date改成timestamp，不能通过创建to_timestamp函数索引解决。 6.3 varchar2类型的字段，谓词条件变量类型是nvarchar2类型，这种情况orace会自动使用SYS_OP_C2C函数，将varchar2转换成nvarchar2。执行计划中会出现SYS_OP_C2C(col_name）类似的信息（plsql developer的F5看不到这些信息）。 应对方法： 这种情况一般发生在关联字段上，可以在驱动表的关联字段上使用to_char；或者在被驱动表的关联字段上创建to_nchar或SYS_OP_C2C函数索引。
7. 绑定变量窥视与自适应游标（ACS） 与3.3类似，在分布不均字段上使用绑定变量，sql硬解析时，如果窥视到的绑定变量适合全表扫描，那么接下来使用的绑定变量如果适合使用索引，就会有多种不同选择： 7.1 10g及以下版本，会一直使用全表扫描的执行计划，直到下次硬解析，再次窥视绑定变量，制定新的执行计划（可能仍是全表扫描，也可能走索引） 7.2 11g及以上版本，如果关闭了自适应游标ACS，与7.1是一样的结果。 7.3 11g及以上版本，如果没有主动关闭ACS，在绑定变量适合走索引的时候，第二次执行的时候，会变成使用索引的执行计划。 7.4 11g及以上版本，如果想让绑定变量在适合走索引的时候，第一次执行时执行计划就能及时作出调整，需要使用bind_aware的hint。 应对方法： 10g版本没办法；11g版本不建议关闭ACS，即可自动完成执行计划转变，必要时需要配合bind_aware hint。
8. 直方图的局限性 11g及以下版本， 直方图只计算字符串的前32位，如果是带长路径的文件名、URL地址、设备号等字符串标识，前面32位都相同，即便后面的值各不相同，也会被认为只有一个唯一值，导致优化器不会使用字段上的索引。（如果前31位相同，那么直方图信息只能靠最后一位的唯一值个数来计算选择性） 应对方法（2选1）： 1、使用hint强制使用字段上的索引。 2、清除该字段上的直方图信息： --清除当前直方图信息： exec DBMS_STATS.DELETE_COLUMN_STATS('&owner','&tab_name','&col_name',col_stat_type=>'HISTOGRAM'); --避免下次收集统计信息又恢复： exec dbms_stats.set_table_prefs( '&owner', '&tab_name', 'method_opt', 'for all columns size auto for columns size 1 &col_name'); 12c及以上版本： varchar2字符串，直方图信息扩展到可以识别字符串的前64位，如果字段的前面64位都是相同的，一般不会自动收集该字段直方图信息，不影响索引的正常选择使用。如果强制收集了直方图信息，也会导致索引无法被使用。应对方法与11版本相同。 （未完待续）