表结构设计不佳，索引无法使用，如何去弥补？

客户TOP1 Reads sql消耗了大量的物理读：

sql 代码：

SELECT t.serialno,......

FROM MSGCONSUMER_LOG t ,code_library cl

WHERE t.transcode = cl.itemno AND

cl.codeno = 'OsgiMsgTransCode' AND

t.transresult IS NULL AND

rownum <= 500 ORDER BY t.starttime;

（这个sql的写法还有一点小瑕疵，就是rownum与order by写在了同一层select中。正规写法应该是先在第一层select 中做order by，在第二层select再使用rownum<=500。否则在两表关联后返回结果集超过500的情况，可能会出现没有按照starttime先后处理的情况 ）

执行计划：

从执行计划可以看出，MSGCONSUMER_LOG表是个大表，transresult IS NULL谓词条件返回少量的记录，适合走索引，但是却使用了全表扫描，是这个sql的性能问题所在。

两表的索引情况：

分析：

MSGCONSUMER_LOG表transresult字段的唯一值是1（表因为消耗IO资源多，被truncate 过，收集信息时只有不到2万条记录，上面执行计划是表在1300多万条记录的时候的历史执行计划），根据SQL写法以及统计信息来判断，这个表应该是一个接口表：transresult is null是需要处理的记录，处理完后会将transresult update为一个常量（比如'Y'）。

虽然transresult字段上存在单字段索引：MSGCONSUMER_LOG_TRANSRESULT，因为索引是不保存全是null的条目，所以transresult is null这种写法还是无法使用这个索引。

这时我们就需要使用一个小技巧，让这个sql可以使用索引：

create index idx_msg_log_test on MSGCONSUMER_LOG(transresult,0);

即创建transresult字段与一个数字常量（字符常量也一样）组成联合索引，创建了这个索引后（原索引MSGCONSUMER_LOG_TRANSRESULT可以删掉了），执行计划就变成了下面这样：

注：因为这个操作是在测试库完成，测试库只有数据，没有业务在执行，transresult is null的记录数较多（生产系统实时处理，一般transresult is null的记录数只有几十条左右），上面这个计划实际上是使用了hint，模拟的是生产系统在创建索引后执行计划。如果不使用hint，根据测试库的两表数据分布，生成的执行计划是下面的样子：

不管哪一种计划，都要比原来MSGCONSUMER_LOG表全表扫描的执行计划效率高很多。

这个SQL还有一种优化方法：

创建MSGCONSUMER_LOG表(TRANSCODE,transresult)两字段组合索引， 这时MSGCONSUMER_LOG可以做被驱动表，性能也会有很大提高，但没有前一种方法好（尤其是transresult is null的记录数经常<8的情况）。

这个sql的问题其实是设计不合理导致的，transresult 这种索引字段，一般不定义为null，is null的写法更是断绝了单字段使用索引的可能。

如果不创建新索引来对SQL进行优化，需要：

1、将现有transresult 为 null的值都改成非空值，如'N'，

2、设置transresult字段default值为'N'，插入新记录时，将NULL自动替换成'N'；

3、修改SQL，将transresult is null 改成 transresult = 'N'。

这样，就可以不需要做索引上的改动，让优化器放心使用transresult 单字段上的索引。

总结：

索引字段不建议定义为null；

索引不保存null条目，如果要检索null，需要使用一定的技巧，如和常量组成联合索引。