SQL查询条件值对于执行计划的影响

SQL查询是否走索引，以及走哪个索引，对于SQL执行的效率会有很大的影响。SQL查询走哪个索引，主要是由查询条件中使用了哪些字段决定的，但是在多个索引都满足条件的情况下，具体走哪个索引，则是由各索引的COST决定的，数据库就会选择COST最小的索引。而每个索引COST的大小，又跟数据的分布，以及查询条件的值有关。

另外有时候SQL明明使用了索引字段，但是查询执行时却不走索引，这经常也跟查询条件值有关。

下面我们通过具体的例子，来分析ORACLE中查询条件值是如何影响执行计划，进而影响索引的选择。

1. 创建测试表T_TEST_INDEX，在ID字段上建索引1，在CREATE_DATE上建索引2。

CREATE TABLE T_TEST_INDEX

(

ID NUMBER(10) NOT NULL,

CREATE_DATE DATE

);

CREATE INDEX IDX_TEST_INDEX_01 ON T_TEST_INDEX (ID);

CREATE INDEX IDX_TEST_INDEX_02 ON T_TEST_INDEX (CREATE_DATE);

2. 插入10万数据

declare

i integer;

V_SQL VARCHAR2(2000);

begin

V_SQL := 'INSERT INTO T_TEST_INDEX(ID,CREATE_DATE) VALUES(:1,:2)';

for i in 1 .. 100000 loop

EXECUTE IMMEDIATE V_SQL USING i,date '2000-01-01'+i;

end loop;

end;

3.查看SQL的执行计划。

3.1 查询1

SELECT * FROM T_TEST_INDEX WHERE ID BETWEEN 10000 AND 11000;

查看执行计划如下，走索引1，COST为3。

3.2 查询2

--把查询1中条件的第2个值由11000改成20000，也就是扩大了查询范围

SELECT * FROM T_TEST_INDEX WHERE ID BETWEEN 10000 AND 20000;

查看执行计划，走索引1，COST为46。

结论：查询范围越大，COST越大。

3.3 查询3

--把查询1中条件的第2个值由11000改成40000

SELECT * FROM T_TEST_INDEX WHERE ID BETWEEN 10000 AND 40000;

查看执行计划，全表扫描，COST为103。

结论：查询范围大到一定程度，数据库会认为走索引没有意义，就会变成全表扫描。以这个例子而言，全表扫描的COST为103，当走索引的COST超过全表扫描的COST时，数据库会选择不走索引。

3.4 查询4

SELECT * FROM T_TEST_INDEX WHERE CREATE_DATE BETWEEN date '2010-01-01' AND date '2020-01-01'

查看执行计划如下，走索引2，COST为6。

结论：换了查询条件字段，索引就会改变。

3.5 查询5

--把查询4中条件的第2个值由2020年改成2030年，也就是扩大了查询范围

SELECT * FROM T_TEST_INDEX WHERE CREATE_DATE BETWEEN date '2010-01-01' AND date '2030-01-01'

查看执行计划，走索引2，COST为24。

结论：同查询2结论。

3.6 查询6

--把查询4中条件的第2个值由2020年改成2090年

SELECT * FROM T_TEST_INDEX WHERE CREATE_DATE BETWEEN date '2010-01-01' AND date '2090-01-01'

查看执行计划，全表扫描，COST为103。

结论：同查询3结论。

3.7 查询7

--查询1和4的条件组合

SELECT * FROM T_TEST_INDEX

WHERE ID BETWEEN 10000 AND 11000　

AND CREATE_DATE BETWEEN date '2010-01-01' AND date '2020-01-01'

查看执行计划，走索引1，COST为6。

结论：说明2个索引都可以走的情况下，系统会选择COST小的索引。

3.8 查询8

--查询2和5的条件组合

SELECT * FROM T_TEST_INDEX

WHERE ID BETWEEN 10000 AND 20000　

AND CREATE_DATE BETWEEN date '2010-01-01' AND date '2030-01-01'

查看执行计划，走索引2，COST为24。

结论：同查询7结论。

以上8个查询的统计汇总如下：

由此我们可以得出结论：

1. 索引字段查询条件范围越大，执行的COST就越大，执行就会越慢；

2. 使用索引的COST超过全表扫描的COST，数据库就会全表扫描不走索引；

3. 多个索引都可以使用的情况下，数据库会选择COST最小的索引。