优化|所有记录为空值(NULL)的字段也适合建索引 & where字段IS NOT NULL也会用到索引

skylines 2024-12-07

今天介绍一个表某个字段上所有记录都是空值(NULL),并在该字段上创建索引进行优化的业务场景例子。

有两个业务场景，都比较相似，发生在同一个表上。业务的SQL语句上，where字句的字段是有非空的传值，但是在该表中的该字段上，该字段上所有记录的值都为空值(NULL)，这种业务场景下，你觉得适合在该字段上创建一个索引嘛？

这个跟我们常说的where字句字段过滤条件为IS NULL 和IS NOT NULL这两种，还是不一样的。多数的业务中，后两者的业务场景，特别是IS NOT NULL的情况，就是该字段上有较优的索引，也会用不上索引。其实这种说法也是不太准确的，这完全是凭据经验才这样。where字句字段为IS NOT NULL也会用到索引，这个用不用到这个较优的索引，不是看SQL语句上where 用了IS NULL还是IS NOT NULL，主要还是看字段上适合过滤条件数据记录占全表记录的比重，在最后我可以证明这点。

再回来今天主要介绍的两个业务场景，都是一个统一认证的业务，涉及的表是记录用户登录一些关联系统的情况。用户登陆一个关联的业务系统，该系统对应的登陆认证表只有用户名，没有记录用户密码，登陆密码是通过调用统一认证系统中用户和密码，即是使用统一认证系统的用户与密码信息。所以这个统一认证系统中，这个SQL次数非常多，具体的业务SQL如下所示。

业务场景一

select  * from RESOUCES t where (t.loginid='yyyxxx' or t.account='yyyxxx') and t.status<4;


SQL> select distinct account,count(*) from RESOUCES group by account;
ACCOUNT                                                        COUNT(*)
------------------------------------------------------------ ----------
                                                                  34493

以上是业务SQL和表的数据情况，loginid和account都是登陆用户名，业务具体的含义就不一样，其中account字段在表中是全为空值（NULL）。

优化前执行计划（进行全表扫描）

SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 3664880602
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |   404 |   650   (1)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| RESOUCES    |     1 |   404 |   650   (1)| 00:00:08 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("STATUS"<4 AND ("LOGINID"='yyyxxx' OR
              "ACCOUNT"='yyyxxx'))
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2392  consistent gets
          0  physical reads
          0  redo size
       9744  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

尝试优化一（字段 status上建索引，强制使用该索引）

SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 33080047
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |   404 |  1463   (1)| 00:00:18 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| RESOUCES         |     1 |   404 |  1463   (1)| 00:00:18 |
|*  2 |   INDEX RANGE SCAN          | IDX_RES_STATUS   |  9250 |       |    20   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T"."LOGINID"='yyyxxx' OR "T"."ACCOUNT"='yyyxxx')
   2 - access("T"."STATUS"<4)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7604  consistent gets
          0  physical reads
          0  redo size
       9744  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

强制使用status字段的索引，性能比全表扫描更差。

尝试优化二（字段 account上建索引，优化器自动使用该索引）

SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 3933866588
------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |     1 |   404 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID     | RESOUCES          |     1 |   404 |     3   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                   |       |       |            |          |
|   3 |    BITMAP OR                     |                   |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                   |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | IDX_HMRES_LOGID   |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                   |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | IDX_HMRES_ACCOUNT |       |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T"."STATUS"<4)
   5 - access("T"."LOGINID"='yyyxxx')
   7 - access("T"."ACCOUNT"='yyyxxx')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       9744  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

用上account字段上的索引后，性能明显大幅提升。

业务场景二

select  count(id) from RESOUCES t where t.passwordlock=1 and t.status in (0, 1, 2, 3);
SQL> select distinct PASSWORDLOCK,count(*) from RESOUCES group by PASSWORDLOCK;
PASSWORDLOCK   COUNT(*)
------------ ----------
                  34493

字段passwordlock也是全为空值（NULL）.

优化前执行计划（进行全表扫描）

SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 3664880602
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |   650 (100)|          |
|   1 |  TABLE ACCESS FULL| RESOURCES   |     1 |    21 |   650   (1)| 00:00:08 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T"."STATUS"=0 OR "T"."STATUS"=1 OR "T"."STATUS"=2 OR "T"."STATUS"=3 AND "T"."PASSWORDLOCK"=1)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2390  consistent gets
          0  physical reads
          0  redo size
       9744  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

尝试优化一（字段 status上建索引，强制使用该索引）

SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 1465991502
--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     1 |    16 |  1460   (1)| 00:00:18 |
|   1 |  SORT AGGREGATE               |                  |     1 |    16 |            |          |
|   2 |   INLIST ITERATOR             |                  |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| RESOUCES         |     1 |    16 |  1460   (1)| 00:00:18 |
|*  4 |     INDEX RANGE SCAN          | IDX_RES_STATUS   |  9253 |       |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T"."PASSWORDLOCK"=1)
   4 - access("T"."STATUS"=0 OR "T"."STATUS"=1 OR "T"."STATUS"=2 OR "T"."STATUS"=3)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7607  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

尝试优化二（字段 passwordlock上建索引，优化器自动使用该索引）

SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 2756549272
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |    16 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                 |     1 |    16 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| RESOUCES        |     1 |    16 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_RES_PASSL   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T"."STATUS"=0 OR "T"."STATUS"=1 OR "T"."STATUS"=2 OR "T"."STATUS"=3)
   3 - access("T"."PASSWORDLOCK"=1)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

用上passwordlock字段上的索引后，性能明显大幅提升。

从以上两个业务场景看，尽管RESOUCES表中的account字段和passwordlock字段上所有记录的值都为空值，但是都建上索引后，业务都用到了这些字段上的索引，大大提升了业务SQL的性能。

补充业务场景三

select  count(id) from resources t where t.passwordlock is not null;

现在该表的字段passwordlock上有一个索引，我们尝试使用where passwordlock is not null模拟，看看这种情况下，是否用到passwordlock字段上的索引。

SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 942951535
------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |     1 |    13 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |                 |     1 |    13 |            |          |
|*  2 |   INDEX FULL SCAN| IDX_RES_PASSL |     1 |    13 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T"."PASSWORDLOCK" IS NOT NULL)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到，尽管字段过滤条件为 IS NOT NULL，还是用上了该字段的索引，所以这跟用不用is not null没有关系，而是跟适合过滤条件的数据占全表数据的比重是多少有关系，简单说就是数据倾斜情况。

总结

这里就简单总结一下，如果某业务表某个字段上有索引，并在业务SQL中使用到该字段作为过滤数据的条件字段，业务SQL的性能，或者有没有用到该字段上的索引，跟SQL的写法is null与is not null没有直接关系，也和表字段记录是否为空也没有直接关系，总结为就是以下几点：

1、用不用字段索引和字段过滤条件的写法is null与is not null没有直接关系；

2、一个表某字段记录全为空也适合建索引，需要看该字段的传值情况以及适合传值条件的数据分布情况；

3、用不用字段上的索引，要看过滤字段符合过滤条件数据占全表数据的比重。

文章转载自skylines，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

优化|所有记录为空值(NULL)的字段也适合建索引 & where字段IS NOT NULL也会用到索引

评论