在Informix+Dynamic+Server+中创建并使用函数索引

在Informix Dynamic Server 中创建并使用函数索引

随着数据量以惊人速度不断增长，数据库管理系统将继续关注性能问题。本文主要介绍一种名为函数索引（functional index）的性能调优技术。根据数据库使用情况的统计信息创建并使用函数索引，可以显著提升SELECT 查询的性能。通过本文了解如何在IBM ® Informix ® Dynamic Server 中创建和使用函数索引并最大限度提升查询性能。

简介

在选择数据库管理系统（DBMS）时，性能是一个关键的考虑因素。在执行SELECT、INSERT、UPDA TE 和DELETE 操作时，很多因素都会对性能产生影响。这些因素包括：

持久性数据存储的速度和大小

数据存储结构

数据访问方法

随着数据集不断变大，查询性能愈发变得重要。

通常，使用索引可以改善查询性能。索引将数据库中的行位置与一组有序数据子集和/或数据派生物关联在一起。索引可以减少DBMS 在执行查询时检查的行（或元组）数量，从而获得性能增益。有时，仅通过搜索索引即可完成查询，而不需要从表中取回任何元组（tuple）。例如，如果您在列c1 中建有索引，并且发出查询select c1 from t1 where c1 < 10 ，那么索引中包含了可以满足查询的所有信息。

有趣的是，ANSI SQL 标准并没有说明如何创建、实现或维护索引。因此，数据库供应商可以按照自己的方式自由地实现索引。

本文讨论了Informix Dynamic Server 的函数索引特性。要理解本文涉及的概念，您需要熟悉基本的数据库术语和概念，例如模式、表、行、列、索引和可扩展性。还需了解Informix Dynamic Server (IDS) 的基本配置以及如何启动和停止服务器、如何使用ONCONFIG 文件进行配置。此外，还需熟悉基本的SQL 命令以及如何使用dbaccess 对服务器执行SQL 命令。

本文的目的是帮助您理解函数索引的定义以及使用。此外，您还将了解如何创建和使用函数索引，以及在创建函数索引之前需要考虑的一些问题。

函数索引的优势

索引按照某种顺序保存列值。函数索引对列中的数据进行转换并按照顺序保存转换后的值。

假设某个表中保存了一个企业的员工名称，并且需要保留名称的大小写形式。那么，如果查询需要执行大小写不敏感的搜索（如下所示），则必须转换数据：

SELECT * FROM t1 WHERE toUpper(name) LIKE 'ANTHONY % HOPKINS';

如果没有为名称建立索引，那么DBMS 将执行全表扫描并对每个元组（tuple）的name 列应用toUpper 函数。要确定元组（tuple）是否满足查询，必须调用toUpper 函数。当表非常大或者大量会话发出这种类型的查询时，性能将会有所下降。

避免调用toUpper 函数的一种方法是在表中同时保存大小写混合的名称和大写名称。应用程序查询大小写不敏感的列：

SELECT * FROM t1 WHERE ucname like 'ANTHONY % HOPKINS';

如果没有为ucname 创建索引，DBMS 仍然执行全表扫描，但是不会对数据进行进一步处理来判断其是否满足查询。尽管这样做改善了性能，但并不是理想的解决方案，因为表非常大，而且所有需要操纵或访问数据的应用程序必须包括处理ucname 的逻辑。

改善查询性能的一种更好的方法是对name 创建函数索引：

CREATE FUNCTION toUpper( name V ARCHER(100) ) RETURNS V ARCHAR(100)

WITH (NOT V ARIANT);

RETURN upper( name );

END FUNCTION;

CREATE INDEX ucnameIndex ON t1 ( toUpper(name) );

当执行这种查询时，DBMS 可以使用函数索引判断哪些元组元组（tuple）满足查询。DBMS 只获取并返回这些满足查询的元组（tuple），如下面的清单所示：

SELECT * FROM t1 WHERE toUpper(name) LIKE 'ANTHONY % HOPKINS';

DBMS 将自动管理函数索引以及不需要包含逻辑来管理大写形式数据的应用程序。通过使用INSERT、UPDA TE 和DELETE 操作对索引进行更新，DBMS 能够确保索引始终与表数据一致。

接下来将深入讨论如何创建和使用函数索引，以及如何生成和检验查询计划，并提供具体的示例。

各种类型的函数索引

函数索引是根据用户定义例程（User Defined Routine，UDR）返回的值创建的。这里的UDR 一词通常用来指代返回值的函数。UDR 必须是不可变的。也就是说，对于给定的参数，UDR 始终返回相同的值，而且UDR 不能修改数据库或变量状态。和随机数生成程序、当前日期/时间函数一样，UDR 通常是变化的，因此不能用于函数索引。当定义UDR 并将之用于函数索引时，您必须显式将其指定为NOT V ARIANT。

可以将UDR 编写为一个存储过程语言（Stored Procedure Language，SPL）函数，或者使用外部语言（SQL、

C/C++ 或Java）编写为一个外部函数。

函数索引可以根据用户定义的类型创建。

函数索引可以使用以下任意一种访问方法：

B-树（默认）

R-树

用户定义的二级方法

可针对单个列、单个列的派生值、多个列和多个列的派生值创建索引。针对多个列创建的索引称为复合索引（composite index）。例如，下面的查询针对一个列的列值和第二个列的派生值创建了一个复合索引：

CREATE INDEX idx1 ON myTable( c1, f(c3) );

函数索引的局限性

函数索引不能是内置的代数、指数、对数或十六进制函数。如果需要使用内置函数定义函数索引，那么必须从SQL 或外部语言函数中调用该函数。

不能针对返回大对象的UDR 创建函数索引。不允许使用大对象作为索引键，因为一般情况下不能够对大对象进行比较和排序。然而，需要注意，可以将大对象作为参数传递给UDR。

如果将某个UDR 用于函数索引，则该UDR 不能使用集合数据类型作为参数类型。集合数据类型包括SET、MULTISET 和LIST。

对于用于函数索引的UDR，传递给它的参数在数量上有所限制。根据所使用的Informix Data Server (IDS) 版本以及实现UDR 的具体语言的不同，这些限制也不尽相同。例如，对于IDS 9.4，最多可以将102 列作为参数传递给 C UDR，并且最多可以将341 列作为参数传递给Java 或SPL UDR。有关此方面的详细内容，请参考您的文档。

比较函数索引和非函数索引

在创建和使用方面，函数索引和非函数索引之间存在着大量差异。

诸如UNIQUE 和CLUSTER 等索引选项，以及B-Tree、R-Tree 等访问方法和用户定义的访问方法，可以同时应用于函数索引和非函数索引。同样，这两种类型的索引都可以指定一个FILLFACTOR，并可以指定存储选项来控制创建索引的位置以及是否对索引进行分段。这两种索引都可以指定索引操作符类并按照降序或升序排列。

oncheck 实用工具可以对两种类型的索引进行验证和修复。例如，下面的oncheck 命令将对db 数据库的tbl 表中名为i1 的索引进行验证：oncheck -ci db:tbl#i1。如果oncheck 报告索引中存在一个问题，那么可以使用oncheck 的-y 选项进行修复。该选项可以同时对函数索引和非功能性索进行修复。