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

(), 软件工程师，Informix Dynamic Server/Optimizer 团队, IBM　

2008 年 1 月 03 日

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

在选择数据库管理系统（DBMS）时，性能是一个关键的考虑因素。在执行 SELECT、INSERT、UPDATE 和 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 VARCHER(100) ) RETURNS VARCHAR(100)
        WITH (NOT VARIANT);

        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、UPDATE 和 DELETE 操作对索引进行更新，DBMS 能够确保索引始终与表数据一致。

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

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

可以将 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 选项进行修复。该选项可以同时对函数索引和非功能性索进行修复。

从用户的角度来看，函数索引和非功能性索之间的一个差异就是它们执行 CREATE 和 DROP 操作的方式不同。在创建或删除一个函数索引时，不能指定 ONLINE 关键字，如下所示：

CREATE INDEX ... ONLINE;  <== Not valid
DROP INDEX ... ONLINE;    <== Not valid
            

这说明，在创建或删除函数索引时，执行索引的表中始终持有一个排他锁。在这段时间内，其他所有用户都无法访问这个表。

任何索引都存在开销。包括资源的使用和执行时间。所有索引都需要进行保存，并且，所有索引都需要制定执行时间并保存其键值。函数索引还会产生额外的函数执行开销。在创建函数索引时，必须针对表中的每一行执行相关的函数。并且，必须在 INSERT 和 UPDATE 操作期间执行函数。

在创建函数索引之前，总是需要对数据库进行详细的成本收益分析。分析的内容应该包括表中存储的数据量、执行数据查询的类型和频率。如果表非常小，或者不经常执行使用函数索引的查询，那么创建函数索引可能收效甚微。

查询计划被写入到 SQL EXPLAIN 文件中。下表展示了该文件的位置和名称。

平台	IDS Server 位置	SQL EXPLAIN 位置	SQL EXPLAIN 名称
UNIX	本地	当前目录	sqexplain.out
UNIX	远程	远程计算机的主目录	sqexplain.out
Windows	本地和远程	/sqexpln	.out

现在，您已了解了函数索引的定义以及其使用方式和使用时机，接下来，将提供一些使用函数索引的例子。我们将为您提供分步指导，使您能够亲自实现函数索引。

您已经了解了函数索引的定义以及其使用方式和使用时机。下面的例子将展示一些具体应用。每个例子都附带了分步指导，根据这些指导，您将能够亲自实现函数索引。

本示例展示如何对圆的面积创建函数索引。如果数据集较大，或者经常发出请求圆面积的查询，那么创建函数索引将有助于提高性能。

1. 首先，创建一个表。

   CREATE TABLE circles ( radius FLOAT );

   
   

2. 接着，创建一个 SPL 函数，它将返回给定半径的圆的面积。

   CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
       WITH (NOT VARIANT);
   
       RETURN 3.14159 * radius * radius;
   END FUNCTION;

   
   

3. 对这个圆形区域创建一个函数索引。

   CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

   
   

4. 最后，执行查询，该查询将使用函数索引。DBMS 使用索引判断哪些元组（tuple）满足查询，并且仅将这些元组（tuple）作为查询结果返回。

   SELECT radius FROM circle WHERE circleArea( radius ) < 500;

   
   

在这个例子中，我们将针对一副数字图像的像素平均色值创建函数索引。UDR 使用 C 编写，可以处理简单的彩色 TIFF 图像。提供了以下函数：

• avgRGB：图像中所有像素的平均值
• avgRed：图像中所有红色像素的平均值
• avgGreen：图像中所有绿色像素的平均值
• avgBlue：图像中所有蓝色像素的平均值

这个例子演示了如何使用函数索引选择具有某些特征的图像。在创建函数索引时，将执行与其相关的 UDR 来对图像进行预处理，并将结果作为索引键保存。SELECT 查询在查询过滤器中指定图像处理函数，现在，这些查询的运行速度得到了提高，因为它们使用索引判断每个元组（tuple）是否满足查询。

我们针对运行在 64 位 Linux x86 上的 IDS version 11.10 对该示例进行了测试。可针对不同平台修改相应的指令。要想了解您的平台适合哪些编译器和连接器，请参考 $INFORMIXDIR/incl/dbdk 中的 makeinc.* 文件。

本示例的源代码以及相关文件可下载获得。可通过 一节下载代码。

1. 设置以下环境变量：

   INFORMIXDIR       the location of your IDS installation
   LD_LIBRARY_PATH   add $INFORMIXDIR/lib
                     add $INFORMIXDIR/lib/esql

   
   

2. 为智能二进制大对象（smart blob）创建 sbspace。注意：sbspace 名称必须与 ONCONFIG 文件中 SBSPACENAME 值匹配。在本例中，名为 sbsp2。

   在与根 dbspace 相同的目录中，创建一个名为 sbsp2 的空文件（ONCONFIG 文件中 ROOTPATH 的值指定根 dbspace 的名称和位置）。

   将文件所有权改为 informix:informix。
   将文件权限改为 mode 660。

   以用户 informix 的身份，创建 sbspace：

   onspaces -c -S sbsp2 -p /sbsp2 -o 0 -s 50000

   
   

3. 创建一个数据库，并将图像作为智能二进制大对象保存。loadImages.sql 脚本创建一个名为 imagedb 的数据库并加载一些图像。

   cd 
   dbaccess - loadImages.sql

   
   

4. 编译 C UDR 并创建一个共享库。

   cc -c -fPIC -I$INFORMIXDIR/incl/public imageUDR.c
   ld -shared -melf_x86_64 -Bsymbolic -o imageUDR.so imageUDR.o

   
   

5. 注册 C UDR。

   根据您的 IDS 服务器的配置方式，您可能需要使用 EXTEND 角色创建 C UDR。如果您的服务器配置中的 IFX_EXTEND_ROLE 设置为 OFF，那么即使不具备 EXTEND 角色也可以创建 C UDR。如果服务器配置中的 IFX_EXTEND_ROLE 设置为 ON，则需要使用 EXTEND 角色才能创建 UDR。

   您的数据库管理员可以使用下面的 SQL 命令向您授予 EXTEND 角色： GRANT EXTEND TO ''。

   您可以使用下面的命令检查服务器配置：onstat -c。

   将包含 C UDR 的共享库复制到 INFORMIXDIR：

   Copy imageUDR.so to $INFORMIXDIR/extend
   Change the file permissions to mode 755

   
   

   将 C UDR 注册到 Informix。注意，我们指定了 HANDLESNULLS，因此，当列值为 NULL 时，IDS 将允许函数返回一个值。

   dbaccess imagedb -
   
   CREATE FUNCTION avgRGB( blob ) RETURNS INTEGER
   WITH ( NOT VARIANT, HANDLESNULLS )
   EXTERNAL NAME '$INFORMIXDIR/extend/imageUDR.so( avgRGB )'
   LANGUAGE C;
   
   CREATE FUNCTION avgRed( blob ) RETURNS INTEGER
   WITH ( NOT VARIANT, HANDLESNULLS )
   EXTERNAL NAME '$INFORMIXDIR /extend/imageUDR.so( avgRed )'
   LANGUAGE C;
   
   CREATE FUNCTION avgGreen( blob ) RETURNS INTEGER
   WITH ( NOT VARIANT, HANDLESNULLS )
   EXTERNAL NAME '$INFORMIXDIR /extend/imageUDR.so( avgGreen )'
   LANGUAGE C;
   
   CREATE FUNCTION avgBlue( blob ) RETURNS INTEGER
   WITH ( NOT VARIANT, HANDLESNULLS )
   EXTERNAL NAME '$INFORMIXDIR /extend/imageUDR.so( avgBlue )'
   LANGUAGE C;

   
   

6. 验证是否已加载数据以及是否能够访问 C UDR：

       dbaccess imagedb -
   
       Database selected.
   
       > select name,
       > avgRed(image) as avgRed,
       > avgGreen(image) as avgGreen,
       > avgBlue(image) as avgBlue,
       > avgRGB(image) as avgRGB
       > from images;
    
      ...
   
       name      yellowbluestainedglass
       avgred    190
       avggreen  190
       avgblue   66
       avgrgb    148
   
       18 row(s) retrieved.
   
       >

   
   

1. 发出使用 UDR 的查询。此时，不存在任何索引。set explain on 指示 IDS 生成显示查询计划的文件。

   dbaccess imagedb -
   
   > set explain on;
   
   >  select name,
   >  avgRed(image) as avgRed,
   >  avgGreen(image) as avgGreen,
   >  avgBlue(image) as avgBlue,
   >  avgRGB(image) as avgRGB
   >  from images
   >  where avgRGB(image) > 150 and avgBlue(image) > 160;
   
   
   
   name      white
   avgred    255
   avggreen  255
   avgblue   255
   avgrgb    255
   
   name      redblue
   avgred    255
   avggreen  0
   avgblue   255
   avgrgb    170
   
   name      graygreentexture
   avgred    173
   avggreen  173
   avgblue   171
   avgrgb    172
   
   3 row(s) retrieved.
   
   >

   
   

2. 检查 SQL EXPLAIN 输出。该查询计划显示了一个全表扫描。

   QUERY:
   ------
   select name, avgRed(image) as avgRed, avgGreen(image) as avgGreen,
       avgBlue(image) as avgBlue, avgRGB(image) as avgRGB
   from images where avgRGB(image) > 150 and avgBlue(image) > 160
   
   Estimated Cost: 2
   Estimated # of Rows Returned: 1
   
     1) .images: SEQUENTIAL SCAN    <== Full Table Scan
   
           Filters: (.avgblue(.images.image )> 160
                        AND .avgrgb(.images.image )> 150 ) 
   
   UDRs in query:
   --------------
       UDR id  :       350
       UDR name:       avgblue
       UDR id  :       347
       UDR name:       avgrgb
       UDR id  :       347
       UDR name:       avgrgb
       UDR id  :       350
       UDR name:       avgblue
       UDR id  :       349
       UDR name:       avggreen
       UDR id  :       348
       UDR name:       avgred

   
   

3. 对 C UDR 提供的函数创建函数索引并再次发出查询：

   dbaccess imagedb -
   
   >   create index avgRGBIndex on images( avgRGB( image ) );
   >   create index avgRedIndex on images( avgRed( image ) );
   >   create index avgGreenIndex on images( avgGreen( image ) );
   >   create index avgBlueIndex on images( avgBlue( image ) );
   
   >   set explain on;
   
   >   select name,
   >   avgRed(image) as avgRed,
   >   avgGreen(image) as avgGreen,
   >   avgBlue(image) as avgBlue,
   >   avgRGB(image) as avgRGB
   >   from images
   >   where avgRGB(image) > 150 and avgBlue(image) > 160;

   
   

4. 检查 SQL EXPLAIN 输出。该查询计划展示了函数索引的使用。

   QUERY:
   ------
   select name,
   avgRed(image) as avgRed,
   avgGreen(image) as avgGreen,
   avgBlue(image) as avgBlue,
   avgRGB(image) as avgRGB
   from images
   where avgRGB(image) > 150 and avgBlue(image) > 160
   
   Estimated Cost: 1
   Estimated # of Rows Returned: 2
   
     1) .images: INDEX PATH    <== Index Scan
   
           Filters: .avgblue(.images.image )> 160
   
       (1) Index Keys: .avgrgb(image)   (Serial, fragments: ALL)
           Lower Index Filter: .avgrgb(.images.image )> 150
   
   UDRs in query:
   --------------
       UDR id  :   350
       UDR name:   avgblue
       UDR id  :   347
       UDR name:   avgrgb
       UDR id  :   347
       UDR name:   avgrgb
       UDR id  :   350
       UDR name:   avgblue
       UDR id  :   349
       UDR name:   avggreen
       UDR id  :   348
       UDR name:   avgred
       UDR id  :   347
       UDR name:   avgrgb

   
   

5. 使用 SQL 指令实现查询计划。要详细了解与优化器相关的 SQL 指令，请参考 IDS 文档。

   此处使用了 AVOID_INDEX 指令，指示查询优化器不要使用函数索引。

   dbaccess imagedb -
   
   > set explain on;
   > select {+avoid_index(images avgrgbindex)}
   > * from images where avgrgb(image) > 100;

   
   

6. 检查 SQL EXPLAIN 输出。该查询计划显示使用了 AVOID_INDEX 指令，并展示了全表扫描。

   QUERY:
   ------
   select {+avoid_index(images avgrgbindex)}
   * from images where avgrgb(image) > 100
   
   DIRECTIVES FOLLOWED:
   AVOID_INDEX ( images avgrgbindex )
   DIRECTIVES NOT FOLLOWED:
   
   Estimated Cost: 2
   Estimated # of Rows Returned: 6
   
     1) .images: SEQUENTIAL SCAN    <== Full Table Scan
   
           Filters: .avgrgb(.images.image )> 100
   
   UDRs in query:
   --------------
       UDR id  :   347
       UDR name:   avgrgb

   
   

在本例中，我们将向您展示如何创建使用 R-Tree 访问方法的函数索引。R-Tree 数据结构可以保存有关多维数据的信息，并用于有关空间或地理数据的索引。这类索引有助于改善针对空间数据的查询的性能。

要理解本示例，需要熟悉 IDS DataBlade 技术。具体来说，您应该能够安装和配置所使用的 Spatial DataBlade。有关 DataBlade 的详细信息，请参考 IBM Informix Spatial DataBlade 用户指南。

本例使用的数据包括一些城市的地理数据和 Oregon 州的海啸影响范围。本文下载部分没有提供这些地理数据，但是可以通过 Oregon Geospatial Enterprise Office 自由获取。可从该站点下载 City Limits 2007 和 Tsunami Inundation Line 数据集。

假设您希望查找即将受海啸影响的城市。为此，发出一个空间数据查询，搜索其中心包含在海啸线内的所有城市。要满足这个查询，必须计算地理城市中心，因此，需要创建一个函数索引来计算这个值。由于涉及到空间数据，您需要为这个索引指定 R-Tree 访问方法。

下面列出了 R-Tree 函数索引的一些有趣特性：

• 它们不仅是针对相交区域的索引；叶页（leaf page）面保存数据对象本身。
• R-Tree 具有高度上的平稳性：
  • 从根页面到任何叶页面的路径均穿过相同数量的层。
  • 所有 leave 节点都位于同一级别。

本例中使用的空间函数来自 Spatial DataBlade。因此，创建和使用索引不需要编写额外的代码。

该示例针对运行在 64 位 Linux x86 上的 IDS version 11.10 进行了测试。可针对不同平台对指令进行相应修改。

1. 设置以下环境变量：

   INFORMIXDIR     the location of your IDS installation
   PATH            add $INFORMIXDIR/bin
                   add $INFORMIXDIR/extend//bin (see below)

   
   

2. 安装 Spatial DataBlade。安装指导可从 IDS 11.10 信息中心获得（参考本文末尾的链接）。

   下面的指令假设您已经在默认位置安装好了 Spatial DataBlade version 8.21： $INFORMIXDIR/extend/spatial.8.21.xC1，其中 ‘x’ 表示特定于平台的字母。对于 64 位 Linux x86，默认位置为 $INFORMIXDIR/extend/spatial.8.21.FC1。可根据您的环境对指令作出相应修改。

3. 为空间数据创建 sbspace。注意：sbspace 名必须与 ONCONFIG 文件中的 SYSSBSPACENAME 值匹配。本例假设 sbspace 名为 syssbspace。

   在与根 dbspace 相同的目录中创建名为 syssbspace 的空文件（ONCONFIG 文件中 ROOTPATH 的值指定根 dbspace 的名称和位置）。

   将文件所有权改为 informix:informix。
   将文件权限改为 mode 660。

   以用户 informix 的身份，创建 sbspace：

   onspaces -c -S syssbspace -p /syssbspace -o 0 -s 100000

   
   

4. 创建您的数据库。

   dbaccess - -
   
   create database spatialdb;

   
   

5. 注册 Spatial DataBlade。

   使用 blademgr 实用工具注册 Spatial DataBlade。Spatial DataBlade 依赖于 R-Tree DataBlade，后者由 IDS 自动附带。因此，必须同时向您的数据库注册这两个 DataBlade。

   blademgr
   register ifxrltree.2.00 spatialdb
   register spatial.8.21.FC1 spatialdb
   list spatialdb
   
       DataBlade modules registered in database spatialdb:
   
           ifxrltree.2.00        spatial.8.21.FC1

   
   

6. 加载空间数据。

   从 City Limits 2007 和 Tsunami Inundation Line 下载中提取文件，并分别保存在两个目录中，即 citylim_2007 和 tsunami：

                           ls citylim_2007
   citylim_2007.dbf
   citylim_2007.prj
   citylim_2007.sbn
   citylim_2007.sbx
   citylim_2007.shp
   citylim_2007.shp.xml
   citylim_2007.shx
   
   ls tsunami
   PRJdevelopment.pdf
   tsunami.dbf 
   tsunami.html
   tsunami.pdf
   tsunami.prj
   tsunami.shp
   tsunami.shp.xml
   tsunami.shx

   
   

   注意：在 UNIX 上，数据文件名称区分大小写，而且必须为小写。这是 Shapefile 数据的要求。如果文件名不正确，那么 loadshp 实用工具将报告错误，指出它无法找到数据文件。

   关于如何正确配置投影数据超出本文讨论的范围。如果没有正确配置，loadshp 实用工具在尝试处理投影信息时将返回错误，因此，隐藏或删除扩展名为 'prj' 的文件：

   rm citylim_2007/citylim_2007.prj
   rm tsunami/tsunami.prj

   
   

   使用 Spatial DataBlade 附带的 loadshp 实用工具将数据加载到 IDS 中。该实用工具位于 $INFORMIXDIR/extend/spatial.8.21.FC1/bin 中。 loadshp 创建了两个表：cities 和 tsunami，并将空间数据分别加载到 citylim 和 inundation 列中。

   $INFORMIXDIDR/extend/spatial.8.21.FC1/bin/loadshp -o create -D spatialdb2 \
       -l cities,citylim -f /citylim_2007
   
   $INFORMIXDIDR/extend/spatial.8.21.FC1/bin/loadshp -o create -D spatialdb2 \
       -l tsunami,inundation -f /tsunami

   
   

1. 发出查询。在这个查询中，

   • st_intersects、st_centroid 和 st_envelope 由 Spatial DataBlade 提供
   • st_envelope 计算每条海啸线的边界，返回类型 st_multilinestring
   • st_centroid 计算每个城市的中心，返回类型 st_multipolygon
   • st_intersects 可确定两种结构是否相互交叉

   这次没有定义函数索引。

       dbaccess spatialdb -
   
       set explain on;
   
       select city_name from cities, tsunami
       where st_intersects( st_centroid( citylim ), st_envelope( inundation ) );
   
   
       city_name                      
   
       Astoria                       
       Bandon                        
       Bay City                      
       Brookings                     
       Cannon Beach                  
       Cave Junction                 
       Coos Bay                      
       Coquille                      
       Depoe Bay                     
       Dunes City                    
       Elkton                        
       Falls City                    
       Florence                      
       Garibaldi                     
       Gearhart                      
       Glendale                      
       Gold Beach                    
       Grants Pass                   
       Lakeside                      
       Lincoln City                  
       Manzanita                     
       Myrtle Point                  
       Nehalem                       
       Newport                       
       North Bend                    
       Port Orford                   
       Powers                        
       Reedsport                     
       Riddle                        
       Rockaway Beach                
       Seaside                       
       Siletz                        
       Tillamook                     
       Toledo                        
       Waldport                      
       Warrenton                     
       Wheeler                       
       Willamina                     
       Winston                       
       Yachats                       
   
       40 row(s) retrieved.

   
   

2. 检查 SQL EXPLAIN 输出。该查询计划显示了全表扫描。针对每一个元组（tuple）执行 st_centroid() 计算。

   QUERY:
   ------
   select city_name from cities, tsunami
   where st_intersects( st_centroid( citylim ), st_envelope( inundation ) )
   
   Estimated Cost: 25671
   Estimated # of Rows Returned: 7018
   
     1) .tsunami: SEQUENTIAL SCAN    <== Full Table Scan
   
     2) .cities: SEQUENTIAL SCAN     <== Full Table Scan
   
           Filters: informix.st_intersects(informix.st_centroid(.cities.citylim ),
                    informix.st_envelope(.tsunami.inundation ))
   NESTED LOOP JOIN
   
   UDRs in query:
   --------------
       UDR id  :   662
       UDR name:   st_intersects
       UDR id  :   626
       UDR name:   st_envelope
       UDR id  :   567
       UDR name:   st_centroid

   
   

3. 创建函数索引计算城市中心。

      dbaccess spatialdb -
   
      create index citycenterindex
      on cities( st_centroid( citylim ) st_geometry_ops )
      using rtree;

   
   

   有关这个索引，需注意以下几点：

   • 指定 R-Tree 访问方法。
   • st_centroid() 计算城市中心。
   • st_geometry_ops 指定操作符类。

   将操作符类与访问方法结合使用，可以指定创建索引和优化查询时使用的操作符。操作符类通常为一组函数。对于 Spatial DataBlade，st_geometry_ops 类由以下函数组成：

   ST_Contains()
   ST_Crosses()
   ST_Equals()
   SE_EnvelopesIntersect()
   ST_Intersects()
   SE_Nearest()
   SE_NearestBbox()
   ST_Overlaps()
   ST_Touches()
   ST_Within()

   
   

4. 执行相同的查询。

   dbaccess spatialdb -
   
   select city_name from cities, tsunami
   where st_intersects( st_centroid( citylim ), st_envelope( inundation ) );

   
   

   注意：当索引表达式被用于关系表达式，或作为某个操作符类函数的参数时，查询优化器将考虑使用函数索引。由于 st_centroid() 是索引 st_geometry_ops 中指定的操作符类的成员，优化器在生成查询计划时将考虑使用索引。

   注意：优化器不会对下面这个查询使用函数索引。这是因为 st_distance() 不属于 st_geometry_ops() 操作符类。

   select city_name from cities, tsunami
   where st_distance( st_centroid( citylim ), inundation ) < 5280;

   
   

5. 检查 SQL EXPLAIN 输出。该查询计划显示索引扫描使用了函数索引。

   注意：查询优化器只有在表统计信息保持最新的情况下使用 R-Tree 索引。这意味着您应该在创建索引之前加载数据，或者在加载数据之后更新表的统计信息。

   QUERY:
   ------
   select city_name from cities, tsunami
   where st_intersects( st_centroid( citylim ), st_envelope( inundation ) )
   
   Estimated Cost: 12920
   Estimated # of Rows Returned: 7018
   
     1) .tsunami: SEQUENTIAL SCAN
   
     2) .cities: INDEX PATH    <== Index Scan
   
       (1) VII Index Keys: informix.st_centroid(citylim)   (Serial, fragments: ALL)
           VII Index Filter:
               informix.st_intersects(informix.st_centroid(.cities.citylim),
               informix.st_envelope(.tsunami.inundation ))
   NESTED LOOP JOIN
   
   UDRs in query:
   --------------
       UDR id  :   662
       UDR name:   st_intersects
       UDR id  :   626
       UDR name:   st_envelope
       UDR id  :   567
       UDR name:   st_centroid
       UDR id  :   567
       UDR name:   st_centroid

   
   

函数索引是一种强大的性能调优工具。通过减少执行查询时需要检索的元组（tuple）的数量，以及减少查询所需的计算，函数索引能够显著改善性能。

除大型对象外，函数索引可以返回任何标准 SQL 类型。同样，函数索引还可以返回用户定义的类型。函数索引可以指定各种不同的访问方法，包括 B-Tree、R-Tree 和用户定义的访问方法。

函数索引的存储和维护成本较高。您应该对系统进行成本收益分析，并只在能保证获益的情况下创建函数索引。如果数据大小较小，或者索引对于数据库查询的性能改善帮助不大，则不适合使用函数索引。

本文提供了一些有关函数索引的示例。希望我们介绍的这些创建和使用函数索引的方法能够帮助您改善查询搜索的性能。