SQL使用复合索引实现数据库查询的优化

一 问题

程序再在一次查询时出现查询时间过长，每次查询要1-2分钟业务反馈用户操作体验很差，sql如下:

select *
FROM edi_booking edibooking0_
WHERE 1 = 1
        AND edibooking0_.load_port_code IN ('CNCWN', 'CNDCB', 'AA', 'CNMWN'
        , 'CWHSD', 'CNSHK', 'CNYTN', 'CNSKU')
        AND edibooking0_.carrier_code = 'WHL'
        AND upper(edibooking0_.so_no) LIKE upper('025%')
        AND edibooking0_.load_port_code = 'CNSHK'
        AND edibooking0_.status <= 1
        AND edibooking0_.tfc_code = 'E19957'
ORDER BY edibooking0_.so_no ASC;

需要对查询进行优化。

二 分析

还是老样子，先看看执行计划，看看走没走索引，不查不知道一查吓一跳，执行计划上居然显示走了索引，索引是函数索引upper(so_no) ，走了索引为什么还慢呢，根据以往的经验，走了索引不是应该很快才对吗？奇奇怪怪，于是注意到了查询条件中还有一个条件tfc_code，又发现这个字段其实也有建立索引，是不是数据库的执行计划有问题，没有走tfc_code索引呢？或者说tfc_code索引本身有问题，于是进行重建tfc_code索引。

alter index EDI_BOOKING_IDX_TFC_CODE rebuild online;

执行后，哇塞，查询速度果然上来了，2s钟返回查询结果。查看执行计划，走了tfc_code索引，nice！ 但是故事还没有结束！ 过了一天后，业务又反馈查询慢了，查看执行计划，走的索引又变成了upper(so_no)。让人头秃。

那还个方向思考，既然走了索引，为什么还会慢！！！ 原来走了索引并不一定就会快，这是一个大大的误区。

upper(edibooking0_.so_no) LIKE upper('025%')这个过滤条件走了索引，但是索引的类型是range_scan,这种类型查询返回的数据量会比较大，这就是这次走索引还慢的问题所在，因为走了索引之后返回了150w条数据，而150w条数据被后去的条件过滤，这样导致了查询速度慢的问题。

而引发这个问题，一个是upper(so_no)索引返回数据量大，另外一个就是oracle的执行计划没有选择最优的索引，如果选择tfc_code索引，那么查询也会很快。

三 解决方案

指定数据库选择索引：

由于执行计划是数据库自动生成的，我们无法改变执行计划，但是我们可以通过指定索引的方式，让数据库去执行我们指定的索引，如：

select /*+index(edibooking0_ IDX_EDI_BOOKING_SO_TFC_CT)*/*
FROM edi_booking edibooking0_  

但是这种有一个弊端，要对每一个执行的语句都要进行指定索引，修改量比较大。

建立复合索引：

CREATE INDEX IDX_EDI_BOOKING_SO_TFC_CT
ON edi_booking (UPPER("SO_NO"), "TFC_CODE","CONTRACT_NO");

复合索引很容易给人一种鸡肋的感觉，因为他对应的查询条件一定是他最左边的索引字段被查询才能生效，但是其实他是非常有用的，比如我们现在的场景，进行复核索引过滤时就会产生非常大的性能提升,最终通过建立组合索引解决问题