B树索引设计

函数索引

场景5-系统函数索引

  准确的来说，函数索引其实也是二级索引的B树索引中的一员，之所以在这里单独拎出来介绍，是因为其存在这一些特殊性以及限制性，需要我们在日常使用中注意的。
  为了认识函数索引的细微差异，我们先来构造一个使用了系统函数的场景5，来验证一下普通索引和系统函数索引的差异：

select count(*)
 from usert
 where 
sex='男'
and to_char(create_time,'yyyymmdd') > '20250515';

  比较简单的查询语句，照例我们根据索引第一星原则来设计索引，首先计算一下两个谓词的过滤因子：

  从过滤因子看到，包含create_time列的谓词过滤性非常好，那理所当然，创建一个单列create_time的索引似乎就可以了，我们先来创建普通B树索引：

CREATE INDEX IDX_USERT_CREATE_TIME ON USERT(CREATE_TIME);

  索引建立后，我们测试验证一下：

  从执行计划可以看到，优化器没有利用我们创建的create_time单列索引，这个其实从执行计划中可以看出来端倪，在SLCT2选择操作符中，除了SEX谓词外，另一个谓词是以exp11 > ‘20250515’表示的，exp11是表达式的意思，在数据库层面，经过to_char函数处理后的这个谓词和原来的create_time列数据是存在差异的，不能相提并论，所以是无法直接利用以create_time列构建的索引的，这种情况下应该如何处理呢？

•   方式一，直接修改sql语句，去掉to_char函数后，即可利用索引，如下图，无论是改为谓词create_time > '2025-05-16'还是谓词create_time > '20250516'均能得到正确结果集，并且利用了索引：

•   方式二，建立to_char(create_time,'yyyymmdd')的函数索引：

CREATE INDEX IDX_USERT_TOCHAR_CREATE_TIME ON USERT(to_char(create_time,'yyyymmdd'));

  再看原始场景5的语句执行结果：

  可以看到建立了函数索引下，原包含系统函数的谓词to_char(create_time,'yyyymmdd')>’20250515’也可以利用索引快速检索了。这种通过预先计算函数或者表达式的返回值来构造的索引称之为函数索引。

场景6-静态函数表达式

  那么是否是所有包含系统函数的谓词都可以建立索引来利用呢？我们再来看一下个场景6，通过timestampdiff函数，比对create_time列，来检索出最近60分钟内创建的数据：

select count(*)
 from usert
 where 
sex='男'
and timestampdiff(mi,create_time,sysdate) < 60;

  执行结果如下：

  我们根据原始谓词表达式的格式，创建函数索引：

CREATE INDEX IDX_USERT_DIFFMI_CREATE_TIME ON USERT(timestampdiff(mi,create_time,sysdate));

  但是在执行函数索引创建语句时，会提示如下：

  数据库阻止了这个函数索引的创建，这是因为timestampdiff(mi,create_time,sysdate)表达式中存在着会变化的数据，即sysdate，这将导致以该函数表达式创建的索引键值不是固定不变的。
  而根据我们B+树构建原理，是需要根据索引键值排序存储的，这个矛盾就导致了该函数索引无法建立，也就无从谈起是否能利用了。

场景7-自定义函数索引

  在数据库中，除了自带的一些常见系统函数以外，根据业务场景需要，我们通常需要自己定义一些特殊用途的函数，这类自行创建的函数一般都归为自定义函数，那么自定义函数是否可以创建函数索引呢？
  我们继续来构造场景7来验证，首先我们定义个GET_ZODIAC函数，当传入1个日期数据时，会返回这个日期的所属12生肖中文字符：

CREATE OR REPLACE FUNCTION GET_ZODIAC(P_DATE IN DATE)

 RETURN VARCHAR(10) DETERMINISTIC IS
    v_year NUMBER;
    v_zodiac VARCHAR(10);
BEGIN
    -- 获取年份
    v_year := EXTRACT(YEAR FROM P_DATE);
    -- 计算生肖，1900 年是子鼠年，所以用 (v_year - 1900) MOD 12 来计算生肖索引
    CASE (v_year - 1900) MOD 12
        WHEN 0 THEN v_zodiac := '鼠';
        WHEN 1 THEN v_zodiac := '牛';
        WHEN 2 THEN v_zodiac := '虎';
        WHEN 3 THEN v_zodiac := '兔';
        WHEN 4 THEN v_zodiac := '龙';
        WHEN 5 THEN v_zodiac := '蛇';
        WHEN 6 THEN v_zodiac := '马';
        WHEN 7 THEN v_zodiac := '羊';
        WHEN 8 THEN v_zodiac := '猴';
        WHEN 9 THEN v_zodiac := '鸡';
        WHEN 10 THEN v_zodiac := '狗';
        WHEN 11 THEN v_zodiac := '猪';
    END CASE;
    RETURN v_zodiac;
END GET_ZODIAC;

  然后是业务语句，假设为检索用户表中创建时间为鼠年的数据：

select count(*)
  from usert
 where sex = '男'
   and GET_ZODIAC(create_time) = '鼠';

  原始语句执行结果如下，在没有索引优化的前提下耗时15秒左右：

  我们尝试建立谓词GET_ZODIAC(create_time) = '鼠'的函数索引：

CREATE INDEX IDX_USERT_GET_ZODIAC_CREATE_TIME ON USERT(GET_ZODIAC(CREATE_TIME));

  结果如下图，依然会提示不是静态函数而无法创建，这是为什么呢，我们的自定义函数针对于输入的日期都是计算到固定的生肖字符来输出的啊：

  这是因为在达梦的函数声明中，需要使用DETERMINISTIC属性来显式声明函数，才能定义该函数为确定性函数，否则不能创建函数索引。我们重新创建GET_ZODIAC函数如下：

  然后再执行创建索引语句，即可正常创建该函数索引了：

  最后再次验证我们的场景7原始语句如下图，是能利用上这个函数索引了：

  测试验证可知，自定义函数是可以创建函数索引，并为之利用的，只不过需要保证该自定义函数为确定性函数，并在定义函数的过程使用DETERMINISTIC属性显式声明。

函数索引设计总结

  从上面的测试场景中，我们可以看到在达梦中函数索引也是可以创建来使用优化查询的，但是总是存在这一些限制，比如不能是非确定性函数。
  至于非确定性函数的定义简单理解则是当我们固定传入一个相同参数时，函数处理后每次可能输出不同的结果值，则表示是非确定性，而确定性则是相反，只要我们的传入参数值相同，我们每次函数处理后输出的结果值也是相同的。
  在这里我们引用达梦数据库《SQL语言使用手册》中的标准定义来做小结：
函数索引：基于函数或表达式建立的索引称为函数索引。即<索引列表达式>是以表中列为变量的函数或表达式。函数索引创建方式与普通索引一样，并且支持UNIQUE和STORAGE设置项，对于以函数或表达式为过滤的查询，创建合适的函数索引会提升查询效率。它存在这以下约束项：
1)表达式不允许为时间间隔类型；
2)表达式中不允许出现半透明加密列；
3)函数索引表达式的长度理论值不能超过 816 个字符（包括函数索引表达式本身的长度加上系统生成的指令长度）；
4)函数索引不能为 CLUSTER 或 PRIMARY KEY 类型；
5)表达式不支持集函数和不确定函数，不确定函数为每次执行得到的结果不确定，系统中不确定函数包括：RAND、CURDATE、CURTIME、CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、GETDATE、NOW、SYSDATE、CUR_DATABASE、DBID、EXTENT、PAGE、SESSID、UID、USER、VSIZE、SET_TABLE_OPTION 、 SET_INDEX_OPTION 、 UNLOCK_LOGIN 、CHECK_LOGIN、GET_AUDIT、CFALGORITHMSENCRYPT、
SF_MAC_LABEL_TO_CHAR、CFALGORITHMSDECRYPT、
BFALGORITHMSENCRYPT、SF_MAC_LABEL_FROM_CHAR、BFALGORITHMSDECRYPT、SF_MAC_LABEL_CMP；
6)快速装载不支持含有函数索引的表；
7)若函数索引中要使用用户自定义的函数，则函数必须是指定了 DETERMINISTIC属性的确定性函数；
8)若函数索引中使用的确定性函数发生了变更或删除，用户需手动重建函数索引；
9)若函数索引中使用的确定性函数内有不确定因素，会导致前后计算结果不同的情况。在查询使用函数索引时，使用数据插入函数索引时的计算结果为 KEY值；修改时可能会导致在使用函数索引过程中出现根据聚集索引无法在函数索引中找到相应记录的情况，对此进行报错处理；
临时表不支持函数索引。