为什么你加的索引不管用？

最近在办公室里，听见这么一段对话：

Bob: Alice，我看了下你昨天告诉我的那个慢查询，我已经把你想要的那个索引给加上去。现在肯定OK了。

Alice：谢谢你，Bob。我马上确认一下…不对啊，还是很慢，看起来没起作用啊

Bob：还真是。看起来Oracle没有用上这个索引，你那个查询我加了/*+INDEX(...)*/索引提示也不行。真是不知道怎么回事了。

然后，问题仍然没有解决。Alice很头疼，因为她要加的特性没有按时发现，Bob也很发愁，因为他觉得Oracle居然没有正常工作。

这是个真事。

[b]Bob忘了Oralce和NULL值的问题了[/b]

可怜的Bob忘了，Oralce是不会把NULL放到普通索引里的。你想一下这种情况：

CREATE TABLE person (
  id            NUMBER(38)   NOT NULL PRIMARY KEY,
  first_name    VARCHAR2(50) NOT NULL,
  last_name     VARCHAR2(50) NOT NULL,
  date_of_birth DATE             NULL
);

CREATE INDEX i_person_dob ON person(date_of_birth);

现在Bob认为有了这个索引什么事都解决了，因为他用这个查询验证了下，这个索引确实是好使的：

SELECT *
FROM   person
WHERE  date_of_birth > DATE '1980-01-01’;

([url=http://en.deepinmind.com/blog/2014/05/17/its-not-about-the-star-stupid.html]当然了，你不应该使用select *[/url])

这个查询的执行计划看起来很正常：

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| PERSON       |
|*  2 |   INDEX RANGE SCAN          | I_PERSON_DOB |
----------------------------------------------------

这是因为Bob的查询并不需要NULL作为I_PERSON_DOB索引的一部分。不幸的是，Alice的查询看起来大概是这样的：

SELECT 1 
FROM   dual
WHERE  DATE '1980-01-01' NOT IN (
  SELECT date_of_birth FROM person
);

实际上，Alice的查询是判断是不是有人是这天生日的。她的执行计划看起来会是这样的：

-------------------------------------
| Id  | Operation          | Name   |
-------------------------------------
|   0 | SELECT STATEMENT   |        |
|*  1 |  FILTER            |        |
|   2 |   FAST DUAL        |        |
|*  3 |   TABLE ACCESS FULL| PERSON |
-------------------------------------

可以看到，她的查询进行了一个TABLE ACCESS FULL操作，索引被忽略了。为什么呢？很简单：


- Oracle不会把NULL值放到索引里。
- NOT IN（a, b, NULL, c, d）的结果是NULL。

不管你的这个日期’1980-01-01’在没在索引里，我们都得查看整个表来确认date_of_birth列中是否饮食一个NULL值。因为如果存在NULL值的话，[url=http://en.deepinmind.com/blog/2014/05/17/sql-incompatibilities-not-in-and-null-values.html]Alice查询中这个NOT IN谓词的结果不是TRUE或FALSE，而是NULL[/url]。


[b]Alice可以用NOT EXISTS来解决这个问题[/b]

这个问题其实Alice自己就可以很容易搞定，她只需要把NOT IN换成NOT EXISTS就好了，这个谓词能够绕过SQL的特殊的[url=http://en.deepinmind.com/blog/2014/05/17/null-in-sql-explaining-its-behavior.html]三值逻辑[/url]。

SELECT 1
FROM   dual
WHERE  NOT EXISTS (
  SELECT 1
  FROM   person
  WHERE  date_of_birth = DATE '1980-01-01'
);

现在新的查询语句的确能够得到一个最优的执行计划：

------------------------------------------
| Id  | Operation         | Name         |
------------------------------------------
|   0 | SELECT STATEMENT  |              |
|*  1 |  FILTER           |              |
|   2 |   FAST DUAL       |              |
|*  3 |   INDEX RANGE SCAN| I_PERSON_DOB |
------------------------------------------

但问题仍然存在，因为该来的迟早还是会来的。Alice必须在写每条查询说一句的时候都时刻谨记这次教训。

[b]对于Bob只需把这列设置成NOT NULL就好了[/b]

最佳的解决方案，其实就是把这列设置成NOT NULL就好了：

ALTER TABLE person 
MODIFY date_of_birth DATE NOT NULL;

有了这个约束后，NOT IN查询就和NOT EXISTS查询是一样的了，Bob和Alice又可以一起快乐地玩耍了。

[b]如何找出这些捣乱的列?[/b]

很简单。下面这个查询可以列出所有存在一个NULL值的索引列。

SELECT
  i.table_name,
  i.index_name,
  LISTAGG(
    LPAD(i.column_position,  2) || ': ' || 
    RPAD(i.column_name    , 30) || ' '  ||
    DECODE(t.nullable, 'Y', '(NULL)', '(NOT NULL)'), 
    ', '
  ) WITHIN GROUP (ORDER BY i.column_position) 
    AS "NULLABLE columns in indexes"
FROM user_ind_columns i
JOIN user_tab_cols t
ON (t.table_name, t.column_name) = 
  ((i.table_name, i.column_name))
WHERE EXISTS (
  SELECT 1
  FROM user_tab_cols t
  WHERE (t.table_name, t.column_name, t.nullable) = 
       ((i.table_name, i.column_name, 'Y'       ))
)
GROUP BY i.table_name, i.index_name
ORDER BY i.index_name ASC;

你现在再用Bob和Alice的schema来执行下，上述查询的结果是：

TABLE_NAME | INDEX_NAME   | NULLABLE columns in indexes
-----------+--------------+----------------------------
PERSON     | I_PERSON_DOB | 1: DATE_OF_BIRTH (NULL)

现在你可以在你自己的schema上运行下这条查询语句，仔细地看一下结果中的那些列有没有必要允许NULL值的出现。应该有半数的情况下是不该出现NULL值的。加上一个NOT NULL约束后，你的程序的性能可能会得到质的提升!


原创文章转载请注明出处：[url=http://it.deepinmind.com]http://it.deepinmind.com[/url]
[url=http://blog.jooq.org/2014/05/12/the-index-youve-added-is-useless-why/]英文原文链接[/url]