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。
![ac44613c04471453de6e2418fb0d76a4.png](https://i-blog.csdnimg.cn/blog_migrate/7ace187da0d91c5e3b1914a1ca0b20c7.jpeg)
查询1执行计划
3.2 查询2
--把查询1中条件的第2个值由11000改成20000,也就是扩大了查询范围
SELECT * FROM T_TEST_INDEX WHERE ID BETWEEN 10000 AND 20000;
查看执行计划,走索引1,COST为46。
![b4a19eb1690578e91775995a0b1b5995.png](https://i-blog.csdnimg.cn/blog_migrate/a2785477bee46596700dd0d294165e29.jpeg)
查询2执行计划
结论:查询范围越大,COST越大。
3.3 查询3
--把查询1中条件的第2个值由11000改成40000
SELECT * FROM T_TEST_INDEX WHERE ID BETWEEN 10000 AND 40000;
查看执行计划,全表扫描,COST为103。
![369a93f23c699d3d03c3fd2b6654b7fb.png](https://i-blog.csdnimg.cn/blog_migrate/8cc1ad633b35f295b5ccc823ca04476a.jpeg)
查询3执行计划
结论:查询范围大到一定程度,数据库会认为走索引没有意义,就会变成全表扫描。以这个例子而言,全表扫描的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。
![5c77708a3005c8617d084415685eec40.png](https://i-blog.csdnimg.cn/blog_migrate/c2a4599921f6f13a84ae9c3cbf6a54d1.jpeg)
查询4执行计划
结论:换了查询条件字段,索引就会改变。
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。
![3b966b997869d20abc3e0458d3ba69d3.png](https://i-blog.csdnimg.cn/blog_migrate/3c7a26069f96f4e0d1e0fc123518ec93.jpeg)
查询5执行计划
结论:同查询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。
![bedae6d8a2e042c40400a272cdd48895.png](https://i-blog.csdnimg.cn/blog_migrate/06f683623d90f37dcab84996c5ec3cce.jpeg)
查询6执行计划
结论:同查询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。
![05b450dcc094fd96f96eb7f328ad5870.png](https://i-blog.csdnimg.cn/blog_migrate/3d8995052feb74adaaf90500652f580c.jpeg)
查询7执行计划
结论:说明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。
![a7f122b92f8fa94a22fba0bd6f539311.png](https://i-blog.csdnimg.cn/blog_migrate/fd77a1401387f03834ce01c8d479b6f8.jpeg)
查询8执行计划
结论:同查询7结论。
以上8个查询的统计汇总如下:
![21bdb073daf1b7b629f9245a2de2c968.png](https://i-blog.csdnimg.cn/blog_migrate/07a192c6714f43939df3f4b5e0e5b75b.jpeg)
8个查询统计汇总
由此我们可以得出结论:
1. 索引字段查询条件范围越大,执行的COST就越大,执行就会越慢;
2. 使用索引的COST超过全表扫描的COST,数据库就会全表扫描不走索引;
3. 多个索引都可以使用的情况下,数据库会选择COST最小的索引。