非空+默认值——一种选择方案思路

最新推荐文章于 2023-03-30 12:21:02 发布

ciqu9915

最新推荐文章于 2023-03-30 12:21:02 发布

阅读量302

点赞数

文章标签：数据库

同事和我讨论一个问题：非关键业务字段（比如状态字段）如果从业务上允许为空，那么在数据库里面，是否有必要通过一个默认值将这个字段作为非空字段。笔者将个人的一点拙见在这里和大家分享。

举一个例子，有一个数据表T，包括两个字段tkttype和tktsts。其中，tkttype表示数据类型信息，非空且类型为varchar2(1)。Tktsts表示特定类型票证的状态信息，只有在tkttype为特定值“P/W”的时候，才赋予有业务含义的状态取值。环境如下：

SQL> desc t;

Name Type Nullable Default Comments

------- ----------- -------- ------- --------

ID NUMBER Y

TKTTYPE VARCHAR2(1)

TKTSTS VARCHAR2(1) Y

我们的问题是，有没有必要将tktsts字段设置为非空，并且给原来允许为空的tktsts字段设置默认值。这样做的好处是可以通过在该列上加索引来提高性能，如果列值为空的话，查询路径就不会走索引。

这样做的确是有一定道理。如果在包括空值null的列上建立索引（非复合索引），由于空值比较排序的原因，空值对应行是不会进入索引树结构的。进而在执行计划中，涉及到空列取值的检索是不可能出现索引路径的。这也就是为什么我们经常说“is null/is not null”条件是不走索引。

在笔者看来，这个问题本身是一个很复杂的决策问题，涉及到很多需要考量的因素。盲目规定说这种情况字段应该加或者不应该加默认值，是比较武断的。简单的整理，我们可以发现这个问题实际上是由两个问题决策组成的。首先，是该列是否加索引来提升检索效率。其次就是添加索引时，是否需要考虑进列空值行的数据。针对不同的场景，两个问题的回答不同，进行决策判断的结果也必然是不同的。

具体可以考虑下面几个方面

1、是否有必要进行索引优化

“加不加索引，是一个问题”。在笔者过去的文章中，一直在强调索引并非一份免费的午餐，一旦添加入索引，之后数据库都会为维护索引消耗CPU和存储资源。我们添加一个索引项目，都要尽可能实现“高回报”的性能收益。首先，我们需要确定的问题是，是否有需要在该列上进行加索引来提升性能。进一步说就是，我们加入的索引，究竟能不能被应用使用到。我们付出了成本，就一定要有最大化的收益。

确定索引列是一个全局考虑的问题。由于应用的复杂，可能出现所有列均出现在不同功能用例搜索条件中的情况。这种时候，我们必然不能将所有列都做成索引列，就需要我们从用例重要程度和系统价值层面对用例进行排序。用户最关注、最常用，系统核心模块的优化级别排序要高于其他模块。对于一些不常用、非核心模块的用例，优化顺序可以适当后移或者甚至不进行优化。

举个例子，目前系统中数据表A两个数据列A1和A2，分别对应两个功能模块检索的要求。如果从优化角度看，两个列都需要建立索引来满足两个模块的要求。但是，由于系统资源等条件的限制，只能建立一个索引。这种时候，就需要评估功能模块的优先级，有限构建高优先级别用户的索引。一个一年只运行一次的查询操作，慢几秒钟给用户体验带来的影响是有限甚至是可以忽略的。但是一个每天运行的大作业慢几个量级却是绝不可以忍受的。

回到本文中的问题。我们首选要确认该状态字段在系统功能模块中是否会作为检索条件，出现在where条件后。where条件是我们借助索引的一个重要场景（注意：但不是唯一场景）。如果对高优先级核心用例来说，该字段根本就不会作为检索条件出现，通常我们就不会考虑为该列建立专门的索引。进而也就不存在通过default值来优化索引的问题了。

那么，是不是该状态列出现在where条件，而且出现的用例是核心用例，就一定需要建立索引呢？我们说也是未必，要根据具体情况来说。一个数据表在一个查询检索（或者一个检索子句）中，无论有多少索引方案，最终执行路径下只有一个索引会进入路径。

比如：select * from t where id=3 and tkttype=’P’; 此时如果在id和stktype两个字段上均有索引。对这个查询，Oracle最终的执行计划中最多只有一个索引进入。当然，在CBO时代，这个选择是依据两个方案的Cost成本来定。

基于这样的情况，回到我们的问题。如果where条件中出现我们关注的状态列tktsts和其他的字段，而且这个用例场景也是需要进行优化的核心用例，这时我们就需要关注各个条件列的查询方式和列选择性。所谓的列选择性就是各个where条件列中，那个列数据的选择性更好。这样的数据列建立索引后，路径执行效率相对较高，出现全表扫描的情况较少。这个时候，才考虑建立确定索引列。

综合上述的部分，我们可以归纳出第一阶段解决的原则：我们对关键用例的关键列才考虑加索引优化的问题。如果分析后结论是状态列不需要索引优化，那么默认值、非空问题也就不用考虑了。

2、选择率、取值规律确定索引构成

如果通过了第一步的决策，确定我们需要对状态列进行加索引操作。接下来就要确定索引类型和结构，包括索引中是否要包括现在列值为空的数据行。

在这个环节，我们要关注的方向还要包括数据取值的选择特性和访问方式。数据列取值通常是有规律的。正常情况下，数据列最大、最小取值，各个取值范围分布是有规律可循的。这种规律性，在Oracle下被归纳为统计量信息。Oracle通过定期或者不定期的统计量收集，提供给CBO优化器进行执行路径生成的依据。

也就是说，同样一个SQL结构，如select * from t where tkttype=’XX’；如果数据列tkttype数据分布偏移严重，对于不同的取值，Oracle会制定出不同的执行计划。可能是走索引，也可能是全表扫描。

此外，对有空值列建索引，还有一个额外的特性，就是空值行是不会进入索引的。那么，索引叶节点所涉及到的行都是索引列非空的数据行。那么，我们对允许为空列构建的索引树，就是一个相对较小的索引树，对应的空间消耗和检索成本就相对较小。

如果索引列值中，null值占到了相当比例，此时我们就不建议对索引列加默认值并且设置为非空。首先，如果设置默认值，null值对应默认值在索引列中比例高的现实没有改变。那么在生成执行计划的时候，生成全表扫描的几率较高。这和使用is null的情况没有区别。同时，使用默认值之后，所有的数据行都要进入索引构建过程，成为叶子节点。这样，索引树的体积也就较大，大部分叶节点对应那些不会被查询到的默认值数据。即使要进行索引扫描，真正需要的值消耗成本也就增加。所以，如果null值比例较高，而且又要在该列加索引的话，不要加默认值为好。