阅读本文大约需要 4 分钟。

从： ////

虽然你已经在该列上建立了索引，并且查询条件也是索引列，但是最终的执行计划并没有使用它的索引。 以下是导致此问题的几个关键点。

列与列的比较

在某个表中，两列（id 和 c_id）具有单独的索引。 以下查询条件不会被索引。

select * from test where id=c_id;

这种情况下认为最好进行全表扫描。

存在 NULL 值条件

在设计数据库表时，我们应该尽量避免NULL值。 如果NULL值不可避免，我们也应该给出一个值。 数字类型可以是0、-1等。 字符串有时会被赋予空字符串。 如果有问题，就给个空格什么的。 如果索引列可为空，则不会为其构建索引。 索引值小于表的count(*)值，所以在这种情况下，执行计划自然会扫描整个表。

select * from test where id is not null;

非条件

我们知道，在创建索引时，会为每个索引列创建一个条目。 如果查询条件是等值或范围查询，则索引可以根据查询条件找到对应的条目。 另一方面，当查询条件为NOT时，索引定位困难，此时执行计划可能更倾向于全表扫描。 此类查询条件包括：、NOT、in、not

select * from test where id<>500;
select * from test where id in (1,2,3,4,5);
select * from test where not in (6,7,8,9,0);
select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

LIKE 通配符

使用模糊搜索时，尽量使用尾随通配符，如：name||'%'，因为建立索引时，会从前面开始匹配索引列，此时就可以找到。 如果使用前置匹配，那么查找索引就会很麻烦。 例如，要查询所有姓张的人，可以搜索“张%”。 相反，如果查询所有名为“Ming”的人，那么只能是%Ming。 这个时候索引怎么定位呢？ 在前面匹配的情况下，执行计划会更倾向于选择全表扫描。 匹配完成后，就可以使用INDEX RANGE SCAN。 因此，在业务设计时，尽量考虑模糊搜索问题，多使用后通配符。

select * from test where name like 张||'%';

有条件包含函数

查询条件中尽量不要对索引列使用函数，比如下面的SQL

select * from test where upper(name)='SUNYANG';

这样就不会使用索引了，因为索引创建时和计算后可能不同，无法定位到索引。 但如果查询条件没有计算索引列，那么索引仍然可以使用。例如

select * from test where name=upper('sunyang');
--INDEX RANGE SCAN

此类函数还包括：、、、trunc等。

复合索引领先列区别大

当复合索引的前导列差异较小时，我们有INDEX SKIP SCAN。 目前的引导列分化较大，在检查后方引导列时，引导列的分裂会消耗大量的资源。 从执行计划来看，它不如全表扫描。 很快，然后索引就失效了。

select * from test where owner='sunyang';

数据类型转换

当查询条件存在隐式转换时，索引就会失效。 比如数据库中存储的是ID的类型，但是查询的时候却采用下面的形式：

select * from sunyang where id='123';

按级别

按级别使用时，不会使用索引。

谓词运算

上面我们说过，不能对索引列进行函数操作，其中还包括加减乘除的谓词操作，这也会使索引失效。 创建一个表，索引为id。 看看这个SQL：

select * from sunyang where id/2=:type_id;

这里很明显是对索引列id进行了'/2'除法运算。 这时，索引就会失效。 这种情况应该改写为：

select * from sunyang where id=:type_id*2;

您可以使用索引。

指数

首先说明一下，创建虚拟索引是否有用，取决于具体的执行计划。 如果可行，您可以构建一个。 如果它不起作用，那就忘记它吧。 一个普通的索引是这样构建的：

create index idx_test_id on test(id);

虚拟索引Index是这样构建的：

create index idx_test_id on test(id) nosegment;

我做了一个实验，首先创建了一个表：

CREATE TABLE test_1116( 
id number, 
a number 
); 
CREATE INDEX idx_test_1116_id on test_1116(id); 
CREATE INDEX idx_test_1116_a on test_1116(a)nosegment; 

其中id为普通索引，a为虚拟索引。向表中插入10万条数据

begin 
for i in 1 .. 100000 loop 
        insert into test_1116 values (i,i); 
end loop; 
commit; 
end; 

然后执行以下SQL来查看时间。 由于在内网机器上实验，无法贴出图片，保证数据的真实性。

select count(id) from test_1116;
--第一次耗时：0.061秒
--第二次耗时：0.016秒
select count(a) from test_1116; 
--第一次耗时：0.031秒
--第二次耗时：0.016秒

因为执行一次后结果集就会被缓存，所以无论索引或内存使用情况如何，第二次执行时间都是相同的。 可以看到，在这种情况下，虚拟索引的速度是普通索引的两倍。 虚拟索引的具体使用细节这里不再讨论。

指数

Index是11g提供的新特性。 它对于优化器是不可见的（你还收到上一篇博客中提到的CBO吗）。 MySQL也有。 MySQL 8.0 中的索引可以隐藏。 我感觉这个功能主要是为了测试。 如果一张表的索引太多，逐一调试执行计划会非常慢。 这时候最好建一个对表和查询没有影响的索引，方便调试。 看起来很不错。使用下面的语句来操作索引

alter index idx_test_id invisible;
alter index idx_test_id visible;

如果想让CBO看到Index，需要加上这句话：

alter session set optimizer_use_invisible_indexes = true;