中国IT动力,最新最全的IT技术教程
最新100篇 | 推荐100篇 | 专题100篇 | 排行榜 | 搜索 | 在线API文档
首 页 | 程序开发 | 操作系统 | 软件应用 | 图形图象 | 网络应用 | 精文荟萃 | 教育认证 | 硬件维护 | 未整理篇 | 站长教程
ASP JS PHP工程 ASP.NET 网站建设 UML J2EESUN .NET VC VB VFP 网络维护 数据库 DB2 SQL2000 Oracle Mysql
服务器 Win2000 Office C DreamWeaver FireWorks Flash PhotoShop 上网宝典 CorelDraw 协议大全 网络安全 微软认证
硬件维护  CPU  主板  硬盘  内存  显卡  显示器  键盘鼠标  声卡音箱  打印机  机箱电源  BIOS  网卡  C#  Java  Delphi  vs.net2005
  当前位置：> IBM专区 > DB2 > 性能
DB2 Spatial Extender 性能调优
作者:佚名 时间:2005-11-14 21:11 出处:互连网 责编:66
              摘要：配置空间数据库以提高性能
在几乎所有的数据库系统中，使运行在系统上的工作负载取得良好的性能是一项十分重要的任务。依靠 IBM DB2 Spatial Extender 的帮助，你可以在数据库中将空间数据和你希望存储的其他数据无缝的集成在一起。该扩展器提供了一组数据类型来表示空间信息。但是，DB2 Universal Database™（DB2 UDB）数据库引擎还不能识别空间数据的确切属性，并且也没有提供内建的专用数据类型。所以，在为获得最佳性能而调优空间数据库时，需要比通常调优其他 DB2 UDB 数据库时考虑更多的东西。本文介绍了在使用 DB2 Spatial Extender 时应该考虑的基本优化步骤，并对此作了详细解释。

简介

DB2 UDB for Linux®、UNIX® 和 Windows® 从 7.1 版开始就提供了 DB2 Spatial Extender，用于支持空间数据的存储、管理和修改（请参阅 参考资料 一节，下载 DB2 Spatial Extender）。可以用专用于空间信息及其属性的结构来扩展已有的数据类型。

对于每个数据库系统，无论它支持的功能有多好，如果不能为构建在系统上的应用程序提供所要求的性能，就毫无价值。因此，性能调优是一项非常关键的任务。这不仅是数据库管理员的任务，也是应用程序开发人员的任务。DB2 UDB 已经提供了各种各样的工具来帮助调优和监视系统，例如“Configuration Advisor”和“Design Advisor”。它们都可以通过 Control Center 访问。

DB2 Spatial Extender 附带了一个 Index Advisor，可以用它来优化包含空间数据的列上定义的索引。然而，索引调优只是性能调优的一部分，还可以通过调整其他一些参数来提高空间操作的性能，例如 inline length、聚集或表空间类型。在下面几节中，我们将解释其中每个参数，并展示它们相对于未经修改的系统的效果。我们在一台配有 1.2 GHz CPU 和 1 GB 物理内存的 IBM ThinkPad T30 上进行了评测。操作系统是 SUSE Linux 9.3，所有评测都基于带 FixPak 9 的 DB2 UDB Version 8.2。请注意，我们没有执行实际的基准测试，而是集中在单个更改上，这样做只是为了展示特定调优选项的效果。您不能简单地将结果中显示的数据与其他系统进行比较。

基本性能调优

在任何性能调优过程中，第一步是为系统取得一个良好的基线。运行 DB2 Configuration Advisor 对该任务有所帮助（请参阅 参考资料 一节，获得对 DB2 Configuration Advisor 的介绍和关于调优数据库性能的信息）。可以从 Control Center 中通过右键单击一个数据库来启动 Configuration Advisor，如图 1 所示。


图 1. 启动 Configuration Advisor
启动 Configuration Advisor

您只需回答关于整个系统的一些简单问题，在任何情况下您应该都知道这些问题。下面是这些问题的一个简化列表，并提供了我们作出的选择，后者以 斜体 显示。从我们的选择中可以看出，我们从一开始就关注性能。您具有的场景可能要求不同的选择，例如对于锁和恢复方面的问题就是如此。

  1. 您想将多少内存用于 DB2 实例？ 809 MB（80%）。
  2. 您将执行更多的数据仓库操作，还是执行更多的事务性操作？ 都有。
  3. 您的事务是长还是短？一分钟预期有多少事务？ 较短的事务；一分钟 60 个。
  4. 对您来说，更快的恢复与更快的事务哪个优先？ 更快的事务。
  5. 数据库已经包含了数据吗？ 还没有包含数据。
  6. 本地和远程连接的平均数量是多少？ 平均 5 个本地连接，2 个远程连接。
  7. 您想使用哪种隔离级别？ Cursor stability （更少的锁）。

在回答这组问题之后，Configuration Advisor 将提供对建议修改项的总结。现在您可以立即应用建议的修改，或将一个任务保存在任务中心。您应该检查每一项修改，确定它们对系统是否有意义。如果想根据自己的需要进行调整，那么可以在任务中心创建一个包含配置修改的任务，然后按照自己的需要修改任务脚本。在系统上运行 Configuration Advisor 之后可以得到如图 2 所示的结果。最值得注意的是对缺省缓冲池大小的修改。增加缓冲池大小通常是提高系统整体性能的最重要的决定，因为它指定可以将多少数据缓存在内存中，从而减少对（较慢的）磁盘 I/O 的需要。


图 2. Configuration Advisor 的建议
Configuration Advisor 的建议

其他更改主要针对将可用内存划分出一些必要的缓存，例如包缓存和编目缓存，或者调整短事务的设置（日志记录和排序）。除了减少辅助日志文件的决定之外，所有更改都将被接受，不需任何人为干涉。我们将辅助日志文件的数量设为 10，以避免在空间导入操作期间可能发生的问题。取决于您的系统以及在那组问题中给出的答案，您得到的建议可能不同。

性能比较

首先在一个新安装的系统上，在应用建议的配置更改之前，我们运行一组空间操作。这些操作的执行时间通过操作系统命令 time 或 DB2 的 db2batch 工具来测量。最后，我们清除数据库，应用 DB2 Configuration Advisor 建议的配置，并重复相同的操作。为得到比较可靠的结果，我们多次执行导入操作和查询，对测量到的时间取平均值。这些空间操作如下所示：

  • 使用 Spatial Extender 命令行工具 db2se 从 DB2 Spatial Extender 附带的第一张 Data & Maps CD 中将 shapefile 文件 europe/roads.shp 导入一个名为 ROADS 的表中。
  • 执行一个空间操作，该操作使用一个简单的空间查询将这个表中所有的几何图形与一个固定的 linestring 进行比较。
  • 在空间列上创建一个网格索引。

清单 1 展示了确切的步骤和它们产生的性能结果。可以在 下载 一节中找到 SQL 脚本 test_config_advisor.sql。


清单 1. 未调优的数据库上的空间操作

            $ time db2se import_shape testdb -fileName /home/stolze/europe/roads             -srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1             -spatialColumn shape -typeName ST_LineString -idColumn id             -commitScope 1500 -messagesFile /home/stolze/import.msg
            GSE0000I  The operation was completed successfully.
            real    2m19.086s
            user    0m0.050s
            sys     0m0.021s
            $ db2batch -d testdb -f test_config_advisor.sql -i complete -s on
            ---------------------------------------------
            Statement number: 1
            SELECT id
            FROM   roads
            WHERE  db2gse.ST_Intersects(shape, db2gse.ST_LineString(
            'linestring(10 50, 20 40)', 1003)) = 1
            Prepare Time is:           0.000      seconds
            Execute Time is:           1.248      seconds
            Fetch Time is:             0.000      seconds
            Elapsed Time is:           1.248      seconds
            ---------------------------------------------
            Statement number: 2
            CREATE INDEX roads_grid_index ON roads(shape)
            EXTEND USING db2gse.spatial_index(0.27, 0.54, 1.6)
            Elapsed Time is:           25.503     seconds
            ---------------------------------------------

注意，测试系统使用的配置是次优的，因为导入的数据是从与数据库和数据库日志在同一个硬盘驱动器上的文件中读取的。因而，读操作与日志写和缓冲池中的数据页的写操作之间存在竞争。可能需要将各种特定于磁盘的任务分配到不同的文件系统上。当使用 DB2 Spatial Extender 导入工具导入 shapefile 文件时，建议不要尝试直接从 CD 装载数据，而是先将它复制到一个硬盘上。CD-ROM 驱动器不是很适合读 shapefile 文件的访问模式，因此整个操作的速度会急剧降慢。

在应用了 Configuration Advisor 的建议之后，重复前面列出的步骤就产生了清单 2 中的结果。可以看到，仅仅是导入操作的性能就提高了 11%，查询的速度快了 28%，甚至创建索引所花的时间也只有之前的 90%。所以不应当忽视最基本的性能调优。


清单 2. 调优后的数据库上的空间操作

            $ time db2se import_shape testdb -fileName /home/stolze/europe/roads             -srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1             -spatialColumn shape -typeName ST_LineString -idColumn id             -commitScope 1500 -messagesFile /home/stolze/import.msg
            GSE0000I  The operation was completed successfully.
            real    2m2.848s
            user    0m0.051s
            sys     0m0.027s
            $ db2batch -d testdb -f test_config_advisor.sql -i complete -s on
            ---------------------------------------------
            Statement number: 1
            SELECT id
            FROM   roads
            WHERE  db2gse.ST_Intersects(shape, db2gse.ST_LineString(
            'linestring(10 50, 20 40)', 1003)) = 1
            Prepare Time is:           0.000      seconds
            Execute Time is:           0.895      seconds
            Fetch Time is:             0.000      seconds
            Elapsed Time is:           0.895      seconds
            ---------------------------------------------
            Statement number: 2
            CREATE INDEX roads_grid_index ON roads(shape)
            EXTEND USING db2gse.spatial_index(0.27, 0.54, 1.6)
            Elapsed Time is:           22.980     seconds
            ---------------------------------------------



回页首


DB2 内部对空间数据的处理

空间数据可能变得非常复杂，需要很多空间来存储一个几何图形中各个点的信息。例如，表示整个美国的区域的几何图形由 60 个多边形组成，总共有 198569 个点来定义那些多边形。按照 Spatial Extender 内部格式，这个几何图形的完整定义要使用 0.9 MB 的磁盘空间（使用了压缩）。如果几何图形按照 ESRI 几何图形格式编码，那么它实际上需要 3.1 MB 的磁盘空间（请参阅 参考资料 一节，了解关于 ESRI 几何图形格式的更多信息）。所有信息封装在一个 ST_Geometry 值中，这意味着这个值在数据库中也需要大约 1 MB 的磁盘空间。另一个例子是只表示一个点的空间值。对于 X 和 Y 维，我们只有用于两个浮点值的 8 个字节。将一个点表示成 ST_Point 值会增加一些开销，但是我们谈论时仍然当作是几个字节。

DB2 表中一个行中存储的所有值的总大小不能超过表空间的页宽。一个例外是大型对象（LOB），它最大可达 2 GB。DB2 支持的最大页宽是 32K。所以存储需要 1 MB 空间的几何图形需要类似于 LOB 的存储机制。但总是为点数据使用那种机制就过分了。为了解决千差万别的需求，DB2 实现了一种用于存储空间数据（或通常的结构数据）的混合方法。如果一个空间值超过了某个大小（即所谓的 inline length），那么这个值就被存储为 BLOB。否则，这个值就存储为 VARCHAR FOR BIT DATA 值。下一节 将详细讨论如何为空间列设置 inline length，以及通过更改设置可以获得的好处。之后，我们讨论 空间数据聚集，为 编写空间 SQL 查询、调优 空间网格索引 提供指南，最后我们解释如果经常要修改数据，则建议使用哪种 表空间类型。

回页首


设置空间列的 inline length

在 上一节 中，我们解释了 DB2 存储需求多变的空间数据的内部机制。确定几何图形是存储为 VARCHAR FOR BIT DATA 还是 BLOB 的决定因素就是所谓的 inline length，这个参数适用于任何表中的空间列。如果空间值的内部表示需要的字节数少于 inline length 设置中指定的值，那么它将以 内联（inline） 的方式存储为 VARCHAR FOR BIT DATA。否则，这个值将被 大对象化（lobify），并在该表的 LONG 表空间中存储为 LOB。

inline length

当在数据库中创建一个新的结构类型时，DB2 将根据类型定义中指定的属性计算那个数据类型的缺省 inline length。可以在系统编目视图 SYSCAT.DATATYPES 的 INLINE_LENGTH 列上找到一个结构类型的缺省 inline length。如果在 CREATE TABLE 或 ALTER TABLE ... ADD COLUMN ... 语句中定义表的列时没有显式地指定 inline length，那么将沿用缺省值。

可以使用 ALTER TABLE ... ALTER COLUMN ... SET INLINE LENGTH ... 语句修改（增加）已有空间列的 inline length。除非通过 REORG TABLE 语句 加 LONGLOBDATA 选项重组存储在表中的数据，否则这种修改只影响 DB2 编目和随后的数据修改。如果值的大小小于新的 inline length，那么这个重组过程将把大对象化的空间值转换成内联值。

应该记住，以内联方式存储数据比以大对象化方式存储数据要可取得多。原因是，内联的数据当作 VARCHAR FOR BIT DATA 对待。这个值与同一行中所有其他属性一起存储在一个数据页中。一旦数据存储在那样一个页上，那一页将通过缓冲池来访问，这样可以利用先进的缓存技术，从而尽量避免文件 I/O。而对于 LOB 则截然不同，它总是直接从磁盘读取。

所以经验法则非常简单：将 inline length 设置得尽可能高，以便让尽可能多的空间值以内联方式存储。当然，实际情况并不像看上去的那么简单。高的 inline length 值告诉 DB2 空间值实际上可以在单独一行中占用很多字节。每一行的最大大小要受到针对表定义的页宽和属性（列）的限制。例如，如果有一个页宽为 4 KB（4096）的表空间，那么一行的最大大小不能超过 4005 字节（请参阅 参考资料 一节，了解关于 SQL 限制的更多信息）。如果这个表有一个不能为空的 INTEGER 列和一个可以为空的 VARCHAR(100) 列，再加上一个空间列，那么最多可以将 inline length 设置为 4005 - 6 - 4 - (1+2+100) - 1 = 3891，其中 6 个字节用于行的前缀，4 个字节是 INTEGER 列需要的空间，(1+2+100) 个字节是为 VARCHAR(100) 预留的，最后 1 个字节用于空间列的 NULL 指示符（请参阅 参考资料 一节，找到关于数据库对象和 CREATE TABLE 语句的一本书）。可以看到，其他列的长度和 inline length 实际上是相互竞争的。为了进一步增加 inline length，可以将表放在页宽为 8K、16K 甚至 32K 的表空间上。这样，对于之前的例子，就可以分别将 inline length 设为 7987、16179 或 32563 字节。

选择适当的 inline length

在将所有空间数据存储到 32K 的表空间上并且将 inline length 设置成尽可能大的值之前，应该首先分析您的数据实际上有多大以及其他参数可能对页宽产生的影响。如果只有 ST_Point 值，那么每个点将需要最多 245 字节的物理存储，如清单 3 所示。在这种情况下，甚至 减少 inline length 更有帮助，因为可以使用更小的页宽和/或在表中使用更多的列。但是要注意，ALTER TABLE 语句只允许增加 inline length。如果想使用更小的值，那么必须在创建表的时候指定。Spatial Extender 导入过程允许显式地为空间列指定 inline length。

在结构类型中嵌套 LOB

虽然 points 属性被定义为 BLOB，但是 DB2 并不会单独地存储它。相反，整个几何图形信息（包括 BLOB 数据）都存储在一起 —— 至于存储为内联值还是大对象化值，则取决于列的 inline length。结构类型的实现使所有属性值并置到一个二进制流中，任何添加的必要的元信息和产生的二进制流在物化（也就是存储到一个表中）的时候，或者存储为内联值，或者存储为大对象化值。

这种方法使任何处理 LOB 的应用程序可以以内联方式存储短的 LOB 值，并利用 DB2 的缓冲池。

清单 3 展示了如何计算每个几何图形在以内联方式存储时需要多少磁盘空间。我们假设所有几何图形都存储在一个名为 SPATIAL_DATA 的表的 GEOMETRY 列中。第一个查询使用 LENGTH 函数。该函数显示以内联方式存储的值的宽度。如果是大对象化的值，那么它显示引用实际值的定位符的宽度。所以只有知道所有空间值都是内联值时，才可以放心地使用该函数。因此，下面的查询根据空间数据类型的属性来计算数据的宽度。关于属性的信息可以从 DB2 编目视图 SYSCAT.DATATYPES 和 SYSCAT.ATTRIBUTES 获得。如果空间数据是使用结构类型实现的，那么在 SQL Reference 中关于 CREATE TYPE 语句的解释中提到的判断结构类型值宽度的法则同样适用（请参阅 参考资料 一节，了解关于 CREATE TYPE 语句的信息）。具体地说，ST_Geometry 类型定义 16 个属性，它的子类型都没有添加自己的属性。除了三个属性外，所有属性都是所谓的 短属性。其中两个非短属性 anno_text 和 ext 没有被使用，第三个非短属性 points 包含内部编码为 BLOB 的几何图形信息。除了实际的数据外，DB2 需要维护强制的 null 指示符（1 个字节）和长度信息（4 个字节）。因此，几何图形的大小可以通过公式 “32 + 16*10 + 5 + LENGTH(points) = 197 + LENGTH(points)” 得出。


清单 3. 几何图形的空间需求

            -- maximum space requirement for spatial point data
            CREATE TABLE test ( p db2gse.ST_Point INLINE LENGTH 3800 )@
            INSERT INTO test VALUES ( db2gse.ST_Point(
            1234567890123456, 1234567890123456,
            1234567890123456, 1234567890123456, 0) )@
            SELECT LENGTH(p) FROM test@
            1
            -----------
            245
            1 record(s) selected.
            -- determining the size of all geometries in a table
            SELECT 197 + LENGTH(geometry..points)
            FROM   spatial_data@
            -- calculating #geometries that would be stored inline/lobified
            -- for a given inline length
            SELECT SUM(inline) AS inline, COUNT(*) - SUM(inline) AS lobified
            FROM   ( SELECT CASE
            WHEN 197 + LENGTH(geometry..points) <= <inline_length>
            THEN 1
            ELSE 0
            END
            FROM   spatial_data ) AS t(inline)@

性能比较

为了演示小的 inline length 与大的 inline length 的效果，我们首先将 shapefile 文件 europe/roads.shp 导入到一个 inline length 为 292 的表中。这是 DB2 允许的最小值。接着运行一个 SQL 脚本，该脚本确定有多少几何图形以内联方式存储，有多少几何图形必须以大对象化的方式存储。然后测量执行一个简单空间查询的时间，并显示在执行期间产生的语句快照的一个摘录，以揭示影响性能的最突出的因素。整个过程在 inline length 为 2000 的情况下再重复一遍，2000 这个值足以导致那个 shapefile 文件中的所有几何图形都以内联方式存储。下载 一节中包含了我们运行 db2batch 时使用的脚本 test_inline_length.sql。


清单 4. 不同 inline length 设置的效果

            $ time db2se import_shape testdb -fileName /home/stolze/europe/roads             -srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1             -spatialColumn shape -typeName ST_LineString -inlineLength 292             -idColumn id -commitScope 1500
            GSE0000I  The operation was completed successfully.
            real    3m15.604s
            user    0m0.050s
            sys     0m0.026s
            $ db2batch -d testdb -f test_inline_length.sql -i complete -s on
            ---------------------------------------------
            Statement number: 1
            SELECT SUM(inline) AS inline_storage,
            COUNT(*) - SUM(inline) AS lobified_storage
            FROM   ( SELECT CASE
            WHEN 197 + LENGTH(shape..points) <=
            ( SELECT inline_length
            FROM   syscat.columns
            WHERE  colname = 'SHAPE' AND
            tabname = 'ROADS' )
            THEN 1
            ELSE 0
            END
            FROM   roads ) AS t(inline)
            INLINE_STORAGE LOBIFIED_STORAGE
            -------------- ----------------
            89595            21384
            ---------------------------------------------
            Statement number: 2
            SELECT id
            FROM   roads
            WHERE  db2gse.ST_Intersects(shape, db2gse.ST_LineString(
            'linestring(10 50, 20 40)', 1003)) = 1
            Prepare Time is:           0.000      seconds
            Execute Time is:           0.854      seconds
            Fetch Time is:             0.000      seconds
            Elapsed Time is:           0.855      seconds
            Buffer pool data logical reads               = 16818
            Buffer pool index logical reads              = 19731
            Direct reads                                 = 3088
            Direct read requests                         = 1544
            Direct read elapsed time (ms)                = 18
            ---------------------------------------------
            $ db2 "DROP TABLE roads"
            $ time db2se import_shape testdb -fileName /home/stolze/europe/roads             -srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1             -spatialColumn shape -typeName ST_LineString -inlineLength 2000             -idColumn id -commitScope 1500
            GSE0000I  The operation was completed successfully.
            real    1m57.212s
            user    0m0.049s
            sys     0m0.027s
            $ db2batch -d testdb -f test_inline_length.sql -i complete -s on
            ---------------------------------------------
            Statement number: 1
            SELECT SUM(inline) AS inline_storage,
            COUNT(*) - SUM(inline) AS lobified_storage
            FROM   ( SELECT CASE
            WHEN 197 + LENGTH(shape..points) <=
            ( SELECT inline_length
            FROM   syscat.columns
            WHERE  colname = 'SHAPE' AND
            tabname = 'ROADS' )
            THEN 1
            ELSE 0
            END
            FROM   roads ) AS t(inline)
            INLINE_STORAGE LOBIFIED_STORAGE
            -------------- ----------------
            110979                0
            ---------------------------------------------
            Statement number: 2
            SELECT id
            FROM   roads
            WHERE  db2gse.ST_Intersects(shape, db2gse.ST_LineString(
            'linestring(10 50, 20 40)', 1003)) = 1
            Prepare Time is:           0.000      seconds
            Execute Time is:           0.792      seconds
            Fetch Time is:             0.000      seconds
            Elapsed Time is:           0.792      seconds
            Buffer pool data logical reads               = 17337
            Buffer pool index logical reads              = 19731
            Buffer pool index physical reads             = 0
            Direct reads                                 = 2
            Direct read requests                         = 1
            Direct read elapsed time (ms)                = 0
            ---------------------------------------------

从结果中可以看出，对于重要操作，较大的 inline length 可以增加 40% 的速度，对样本数据的查询也快了 7%。这两种差异的底层原因是，当使用较小的 inline length 时，有超过 20000 个几何图形（大约 20% 的数据）以大对象化的方式存储。DB2 直接从磁盘读（写） LOB 数据。而在第二种场景中，大部分的直接读是不需要的，因为可以用存储在缓冲池中的内联数据来满足查询。注意，大对象化的数据所占的 20% 的比例实际上不算很坏。如果由于稍微复杂一点儿的几何图形导致更多的数据不能以内联方式存储，那么这里演示的差异还要大大增加。

回页首


聚集空间数据

根据某个属性聚集数据是一种常见的、也是非常有用的技术，这种技术可以物理地组织一个表的数据。通过对底层的观察可以发现，具有相似值的数据常常一起被访问。所以，可以将类似的数据存储在接近的位置，使得对那些数据的访问不必分散到表空间中很多不同的页上，而是分布在临近的几个页上。根据空间数据的空间属性或几何图形间的距离聚集空间数据是很自然的。空间查询是展示局部数据访问（换句话说，现实中临近的几何图形常常被一起访问）的最好例子之一。例如，如果您看一个城市的街道地图，那么很可能对那个城市的所有街道感兴趣，而对地区另一边某个其他城市的街道不感兴趣。所以在物理上将那个城市的一些行存储在相邻的位置的确很有意义。

在 DB2 中建立数据聚集属性的方法是根据一个索引对表进行重组。然而，如果由于空间索引的复杂性质导致 DB2 REORG TABLE 命令不理解空间索引，事情就不会那么容易了。关于这个问题有一个容易的方法，即使用一个列，这个列的值是根据涉及的图形计算的。这个列上声明的数据类型必须保证 DB2 能在这个列上创建一个本地 B- 树索引。这里使用空间填充曲线来保存空间和拓扑属性（请参阅 参考资料 一节，找到 H. Sagan 撰写的书籍）。我们在几何图形上取一个点，即形心点， 计算那个点在空间填充曲线上的值，并将结果存储在一个附加的列中。最后，在附加列上创建一个索引，并根据那个索引对表进行重组。

定义聚集列

在实际扩展这个包含空间数据的表并添加聚集列之前，必须定义一个函数，这个函数计算几何图形的形心点在空间填充曲线上的值。幸运的是，DB2 Spatial Extender 已经提供了大部分的逻辑，我们只需根据自己的目的将它们组合到一起。首先，有一个为多边形定义的方法 ST_Centroid。将这个方法应用到 ST_Envelope（对于任何非空的几何图形，它总是返回一个多边形）的结果上将得到我们需要的那个点。接下来就是基于 Z 顺序的索引机制的键生成器函数，该函数是 DB2 Spatial Extender 自带的（请参阅 参考资料 一节，了解关于 Index Extensions 的信息）。Z 顺序实际上是一种空间填充曲线 —— 这与我们想象的一样。关于键生成器函数的信息可以通过 db2look 工具或直接从 DB2 编目视图获得。键生成器如清单 5 中定义的那样。


清单 5. Z 顺序索引的键生成器函数

            CREATE FUNCTION db2gse.GseZordIdxKeyGen ( srsId INTEGER,
            xMin DOUBLE, xMax DOUBLE, yMin DOUBLE, yMax DOUBLE,
            xOffset DOUBLE, xScale DOUBLE, yOffset DOUBLE, yScale DOUBLE )
            RETURNS TABLE ( coordsysId INTEGER, zValue BIGINT )
            SPECIFIC ST_ZixKeyGen
            EXTERNAL NAME 'db2gsefn!gseZOrderIndexKeyGenerator'
            ...@

现在我们可以创建一个附加的函数，该函数将组合之前提到的函数，并为任何给定的几何图形产生空间填充 Z 曲线上的标量值。最终得到的 computeZValue1 函数定义如清单 6 所示，在 下载 一节中的 zvalue_fct.sql 文件中也可以找到该函数。该函数以一个几何图形作为输入，另外还带有 4 个参数，这 4 个参数定义如何将点几何图形的 X 和 Y 维上的浮点坐标转换成用于计算 Z 值的整数值。当然，如果您不需要更多的灵活性，那么可以在函数主体中直接使用常量，而不是参数。注意，按照 DB2 Spatial Extender 范例，在乘上比例系数之前，浮点值要减去偏移量。清单 6 中的第二个函数 computeZValue 是第一个函数的变种。它使用一种不同的方法为 db2gse.GseZordIdxKeyGen 函数提供输入参数。外壳的计算和外壳形心的提取需要在 DB2 引擎与 Spatial Extender 之间进行通信。通过添加 ST_MinX、ST_MaxX、ST_MinY 和 ST_MaxY 方法，提取几何图形的最小和最大 X 和 Y 坐标，并直接计算形心，可以避免这种通信。


清单 6. 为几何图形生成 Z 值的函数

            CREATE FUNCTION computeZValue1 ( g db2gse.ST_Geometry,
            xOffset DOUBLE, xScale DOUBLE, yOffset DOUBLE, yScale DOUBLE )
            RETURNS BIGINT
            LANGUAGE SQL
            DETERMINISTIC
            NO EXTERNAL ACTION
            RETURN SELECT t2.zValue
            FROM   TABLE ( VALUES (g..ST_Envelope()..ST_Centroid()) ) AS t1(p),
            TABLE ( db2gse.GseZordIdxKeyGen ( p..ST_SrsId(),
            p..ST_X(), p..ST_X(),  p..ST_Y(), p..ST_Y(),
            xOffset, xScale, yOffset, yScale ) ) AS t2@
            CREATE FUNCTION computeZValue ( g db2gse.ST_Geometry,
            xOffset DOUBLE, xScale DOUBLE, yOffset DOUBLE, yScale DOUBLE )
            RETURNS BIGINT
            LANGUAGE SQL
            DETERMINISTIC
            NO EXTERNAL ACTION
            RETURN SELECT zValue
            FROM   TABLE ( db2gse.GseZordIdxKeyGen ( g..ST_SrsId(),
            (g..ST_MinX() + g..ST_MaxX() / 2),
            (g..ST_MinX() + g..ST_MaxX() / 2),
            (g..ST_MinY() + g..ST_MaxY() / 2),
            (g..ST_MinY() + g..ST_MaxY() / 2),
            xOffset, xScale, yOffset, yScale ) ) AS t@

以后我们将使用该函数的第二个版本，因为从清单 7 可以看出，它运行起来要快得多。db2batch 运行的总结表明，很多时间花在 db2agent 过程中，这个过程执行空间函数 ST_envelope 和 ST_Centroid。虽然看上去 ST_MinX 和相关的方法将导致前面提到的通信开销，但在这里有所不同，因为那 4 个方法只是访问几何图形的结构类型的属性，这完全是在 DB2 引擎内部处理的。


清单 7. 比较生成 Z 值的函数

            $ db2se import_shape testdb -fileName /home/stolze/europe/roads             -srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1             -spatialColumn shape -typeName ST_LineString -idColumn id             -commitScope 1500
            GSE0000I  The operation was completed successfully.
            $ db2batch -d testdb -f test_zvalue_fct.sql -i complete -s on
            ---------------------------------------------
            Statement number: 1
            SELECT computezValue1(shape, -180, 1000, -90, 1000)
            FROM   roads
            Prepare Time is:           0.000      seconds
            Execute Time is:           5.737      seconds
            Fetch Time is:             106.706    seconds
            Elapsed Time is:           112.443    seconds
            ---------------------------------------------
            Statement number: 2
            SELECT computezValue(shape, -180, 1000, -90, 1000)
            FROM   roads
            Prepare Time is:           0.000      seconds
            Execute Time is:           0.130      seconds
            Fetch Time is:             2.702      seconds
            Elapsed Time is:           2.832      seconds
            ---------------------------------------------
            Summary of Results
            ==================
            Elapsed             Agent CPU         Rows      Rows
            Statement #     Time (s)            Time (s)          Fetched   Printed
            1                   112.443             101.000     110979         0
            2                     2.832               2.560     110979         0

最后一步是扩展表，使表根据几何图形的拓扑信息组织数据。我们假设有一个名为 ROADS 的表，这个表是在 shapefile 文件 europe/roads.shp 的基础上创建的。第一个想法可能是添加一个新的列，并使用 GENERATED ALWAYS AS ( ... ) 语法来填充和维护它。但是，该函数的两个变种都使用子选择（第一个版本中的空间函数使用一个便笺式存储器来存放前后两个调用之间传递的信息），并违反了生成的列的某些约束。所以我们必须像清单 8 那样借助于触发器。为了确保所有已有的行都得到适当的处理，运行 UPDATE 语句。按照空间属性聚集这些行的效果取决于在查询时对一个数据子集的访问。如果使用表扫描，那么聚集就不会真正有益，因为 DB2 仍然必须读取所有的行，那么物理排序不很重要了。所以我们创建一个空间网格索引，这个索引将在查询时用到。所有步骤同样可以在附带的 zvalue_clustering_setup.sql 文件中找到。


清单 8. 通过 Z 值列扩展表

            ALTER TABLE roads ADD COLUMN z_value BIGINT@
            CREATE INDEX z_value_idx ON roads(z_value)@
            CREATE TRIGGER roads_zval_insert NO CASCADE BEFORE INSERT ON roads
            REFERENCING NEW AS n
            FOR EACH ROW MODE DB2SQL
            SET n.z_value = computeZValue(n.shape, -180, 1000, -90, 1000)@
            CREATE TRIGGER roads_zval_update NO CASCADE BEFORE UPDATE OF shape ON roads
            REFERENCING NEW AS n
            FOR EACH ROW MODE DB2SQL
            SET n.z_value = computeZValue(n.shape, -180, 1000, -90, 1000)@
            UPDATE roads
            SET    z_value = computeZValue(shape, -180, 1000, -90, 1000)@
            REORG TABLE roads INDEX z_value_idx@

性能比较

我们首先在未聚集的数据上运行一个查询，然后在聚集的数据上运行查询。两个场景之间的 REORG TABLE 步骤将建立聚集顺序。为了确保能得到有意义的结果，我们还像 后面一个小节 中解释的那样，检验网格索引是否真正被用于这个查询。由于数据量比较小，前后两个查询在执行时间上测量不到明显的差别。但是这里收集了一个快照，并提取了关于表空间 USERSPACE1 的有趣信息，以突出两者之间的差别。像平常一样，从 shapefile 文件 europe/roads.shp 导入表中的数据，然后将 inline length 设为 2000。测量的确切的开关列在 SQL 脚本 test_clustering.sql 中，该脚本可以在 下载 一节中找到。


清单 9. 测试聚集空间数据的效果

            $ db2se import_shape testdb -fileName /home/stolze/europe/roads             -srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1             -spatialColumn shape -typeName ST_LineString -inlineLength 2000             -idColumn id -commitScope 1500
            GSE0000I  The operation was completed successfully.
            $ db2 connect to testdb
            $ db2 "CREATE INDEX roads_grid_index ON roads(shape)             EXTEND USING db2gse.spatial_index(0.27, 0.54, 1.6)"
            $ db2stop force && db2start
            $ db2batch -d testdb -f test_clustering.sql -i complete -s on
            ---------------------------------------------
            Statement number: 1
            SELECT id
            FROM   roads
            WHERE  db2gse.ST_Intersects(shape, db2gse.ST_LineString(
            'linestring(10 50, 20 40)', 1003)) = 1 SELECTIVITY 0.00000001
            *** Tablespace Snapshot ***
            Tablespace Name                               = USERSPACE1
            Buffer pool data logical reads                = 13161
            Buffer pool data physical reads               = 1949
            Total buffer pool read time (ms)              = 39
            ---------------------------------------------
            $ db2 connect to testdb
            $ db2 -td@ -f zvalue_fct.sql
            $ db2 -td@ -f zvalue_clustering_setup.sql
            $ db2stop force && db2start
            $ db2batch -d testdb -f test_clustering.sql -i complete -s on
            ---------------------------------------------
            Statement number: 1
            SELECT id
            FROM   roads
            WHERE  db2gse.ST_Intersects(shape, db2gse.ST_LineString(
            'linestring(10 50, 20 40)', 1003)) = 1 SELECTIVITY 0.00000001
            *** Tablespace Snapshot ***
            Tablespace Name                               = USERSPACE1
            Buffer pool data logical reads                = 13161
            Buffer pool data physical reads               = 749
            Total buffer pool read time (ms)              = 16
            ---------------------------------------------

您会注意到，在聚集场景中，与未聚集的表相比，数据物理读操作的次数有了明显的降低 —— 只有之前的 38%。这个差别完全归功于聚集的影响，换句话说，在物理层面上，相关数据存储在临近的位置，使得在执行查询期间需要访问的数据页更少。

使用 Hilbert 空间填充曲线

基于空间属性的聚集在一些客户站点上已经被证明是非常有效的。当然，您可以设想用不同的方法来为生成的聚集列提供值。例如，可以按照网格索引的思想，使用与几何图形重叠的一个网格单元的标识符。而且，采用的方法很大程度上取决于拥有的空间数据。对于大地数据，由于地区表面具有封闭的特性，因此空间填充 Z 顺序曲线可能不是最好的选择。

为了方便，我们在 下载 一节中提供了另一种空间填充曲线，即 Hilbert 曲线的实现（请参阅 参考资料 一节以了解关于 Hilbert 曲线的信息）。研究表明，Hilbert 曲线可以很好地保存拓扑信息，甚至优于 Z 顺序曲线。然而，使用 Hilbert 曲线时要求编译用 C 编写的用户定义函数，并将它注册到数据库。这个编译可以在 bldrtn 脚本的帮助下进行，该脚本可以在 sqllib/samples/cpp 目录中找到。在 hilbert_fct.sql 文件中可以找到用于注册函数的 SQL 语句。

添加一个有索引的聚集列的步骤非常类似于清单 8 中展示的步骤。但是，这一次我们可以使用一个生成的列，因为在函数 computeHilbertVal 的定义中没有出现子选择，并且没有违反生成的列上的其他约束。清单 10 展示了这些特定的步骤，在将 computeHilbertVal 函数注册到数据库之后便可以执行这些步骤。您会注意到，我们直接访问空间数据类型 ST_Geometry 的 4 个属性，以避免陷入生成的列的麻烦当中。只有当被授予必要的权限，并且不使用大地坐标系时才能访问那些属性，因为在使用大地坐标系时，由于对大地情况中的属性的不同使用，将导致不准确的结果。但是，如前所述，在任何情况下，应该为大地数据选用一个不同的聚集标准。只有当表已经包含一些数据，并且想强制地为已有的行生成 hilbert 值的时候，两个 SET INTEGRITY 语句才是必要的。相同的步骤同样也收集在 hilbert_clustering_setup.SQL 脚本中。必须确保有足够用于生成过程的日志空间。


清单 10. 用 Hilbert 值列扩展表

            SET INTEGRITY FOR roads OFF@
            ALTER TABLE roads ADD COLUMN hilbert_value BIGINT
            GENERATED ALWAYS AS ( computeHilbertVal(
            (shape..xMin + shape..xMax) / 2,
            (shape..yMin + shape..yMax) / 2,
            -180, 1000, -90, 1000) )@
            SET INTEGRITY FOR roads IMMEDIATE CHECKED FORCE GENERATED@
            CREATE INDEX hilbert_value_idx ON roads(hilbert_value)@
            REORG TABLE roads INDEX hilbert_value_idx@

现在我们重复清单 9 中的操作。这里只显示聚集情况下的结果，因为未聚集的表产生的结果与之前是相同的。结果表明，Hilbert 曲线的确能够更好地根据拓扑聚集数据，与 Z 排序相比，它可以减少 1.5% 的数据物理读。如前所述，如果您不想自己将源代码编译到一个共享库中，那么使用 Hilbert 曲线可能不是好的选择。


清单 11. 聚集的 Hilbert 值列对性能的影响

            $ db2se import_shape testdb -fileName /home/stolze/europe/roads             -srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1             -spatialColumn shape -typeName ST_LineString -inlineLength 2000             -idColumn id -commitScope 1500
            GSE0000I  The operation was completed successfully.
            $ db2 connect to testdb
            $ db2 "CREATE INDEX roads_grid_index ON roads(shape)             EXTEND USING db2gse.spatial_index(0.27, 0.54, 1.6)"
            $ db2 -td@ -f hilbert_fct.sql
            $ db2 -td@ -f hilbert_clustering_setup.sql
            $ db2stop force && db2start
            $ db2batch -d testdb -f test_clustering.sql -i complete -s on
            ---------------------------------------------
            Statement number: 1
            SELECT id
            FROM   roads
            WHERE  db2gse.ST_Intersects(shape, db2gse.ST_LineString(
            'linestring(10 50, 20 40)', 1003)) = 1 SELECTIVITY 0.00000001
            *** Tablespace Snapshot ***
            Tablespace Name                               = USERSPACE1
            Buffer pool data logical reads                = 13161
            Buffer pool data physical reads               = 723
            Total buffer pool read time (ms)              = 16
            ---------------------------------------------

理论上，需要读的最优页数可以通过公式 “#rows-returned-by-index * ( #pages-of-table / #rows-in-table )” 来计算。可以在收集了关于表 ROADS 的统计信息之后，通过查询 SYSSTAT.TABLES 编目视图的 CARD 和 NPAGES 这两列，来获得表中的总行数和为表分配的总页数。这里必须使用由索引返回的行数（而不是查询本身返回的行数），因为索引只识别出一组可能的候选行，需要为这些行调用空间函数 ST_Intersects，以确定空间谓词最终的结果。可以使用函数 ST_MBRIntersects 来确定候选集。在我们的例子中，通过索引扫描得到 6574 个候选行。该表将 110979 个行存储在 11018 个页上。

现在我们有了所有必需的信息，并计算 “6574 * ( 11018 / 110979 ) = 652.14”。所以，如果这些行以理想的方式分布，要使得只有被查询访问的行可以在装载的页上找到，那么至少需要从磁盘读 653 个页。在基线上，我们看到必须从物理设备多读 11% 的页。注意，未聚集的情况会导致多读 200% 的页。

回页首


关于编写空间 SQL 语句的提示和技巧

SQL 的范例是，用户（或应用程序）只需告诉数据库系统要 做什么，系统自己就会得出 如何做 这件事的最佳方法。因此，SQL 是一种纯描述性语言。DB2 优化器尽力实现这个理想，通常做得也很好。但是，还存在一些特殊情况，在这些情况下，用户的干预可以改善性能。因此，我们描述一些您应该记住的用于空间查询和空间 DML 语句的概念。

处理多个行

一开始，SQL （现在也仍然）是一种面向集合的语言，而不是过程语言。这意味着同时处理一个集合中相关的行，而不是一个接一个地处理每一行。一个很好的例子就是 INSERT 语句：如果需要将多个行插入到相同的表中，那么是在一条 SQL 语句中处理所有那些行，而不是触发多条语句。这一事实对于空间数据尤其有意义，因为几乎所有空间函数的实现都使得在同一条 SQL 语句中多次执行一个函数可以得到性能好处。在第一次调用函数时，函数的内部处理执行一些必要的初始化，例如设置计算所需的内存区域，随后的调用则利用这个已建立好的基础设施，直到在这条语句的作用域内最后一次调用函数时销毁这个基础设施。

所以，如果为每个要插入的行单独执行一条 INSERT 语句，那么前面提到的基础设施的初始化和销毁将每执行一次就重复一次，即对于每一行都重复一次。而将这些语句组合成一条单独的语句可以避免重复的初始化和销毁步骤，从而提升总体性能。此外，同时处理多个行还可以提高性能，因为减少了应用程序（DB2 客户机）与数据库引擎之间的交互次数。

让我们通过一个简单的例子来展示上述效果，在这个例子中，我们插入 50 个不同的行。首先，像清单 12 显示的第 1 个语句那样，使用 SQL 分别插入所有的行。清单 12 中的第 2 个语句将所有 50 行组合到一个单独的插入操作中。其思想是，在一个描述性级别上，在 FROM 子句中建立一个临时表，这个表由在调用任何空间函数之前导入的数据组成。接着，扫描那个临时表中的所有行，并应用空间函数，从而构建一个新的表，最终这个表中的数据被插入 tab 表中。


清单 12. 逐行操作与多行操作的比较

            -- insert just a single row
            INSERT INTO tab
            VALUES ( 1, db2gse.ST_LineString(
            'linestring (11.9963970 54.9979739, 11.9947259 55.0000000)',
            1003) )@
            -- insert multiple rows at once
            INSERT INTO tab
            SELECT id, db2gse.ST_LineString(wkt, 1003)
            FROM   TABLE ( VALUES
            ( 1, 'linestring (11.9963970 54.9979739, 11.9947259 55.0000000)' ),
            ( 2, 'linestring (11.9872250 55.0000000, 11.9963970 54.9979739)' ),
            ... ) AS t(id, wkt)@

下载 一节中提供的脚本 test_multi_row.sql 用同样的一组数据运行两种不同的方法。被插入的数据源自我们之前用过的 shapefile 文件 europe/roads.shp。第一种方法总共花费 0.050 秒，而组合语句可以在 0.012 秒内完成。

应该注意的是，大部分时间花在解析和编译 SQL 语句上。如果使用了预置语句，那么两种方法之间的差距可能拉大，也可能缩小，这取决于您的应用程序、系统配置和数据。但是，不大可能出现第一种场景好于多行语句的情况。而且应该记住，构造函数 ST_LineString 不执行任何复杂的空间计算；它只是在文本表示上执行一次 single-sweep 扫描，并将坐标转换成内部编码。

该函数占用的内存也很少。还应记住，预置语句可用于多行插入（例如，在静态嵌入式 SQL 应用程序中）并且多行插入可以执行多次，以利用上述优点。同样的技术还用于空间导入过程中，这就是为什么在消息文件的一开始出现像 “Using 342 rows per single INSERT statement” 这样的信息性消息的原因。在导入期间，总行数受 SQL 语句的最大可能大小、提交范围（commit scope）、被导入的总行数或所有这些因素的限制。

对 INSERT 语句的这些考虑同样适用于 SELECT 或 UPDATE 语句。例如，DB2 Spatial Extender 存储过程 ST_run_gc 对一组行进行地理编码（geocode）。如果为提交范围指定一个值，那么该过程可能不会立即处理所有受影响的行，但是其间它必须为这些行计数，并执行一个 COMMIT。初级的方法是使用一个游标对表进行扫描，并为每一行执行一个定位的更新语句。然而，定位更新会碰到我们刚才讨论的一个问题，那就是必须一次又一次地初始化地理编码器。

如果地理编码不仅仅是构造一个 linestring，而是执行更复杂的功能，那么对性能的影响将会更加显著。所以，如果在表中找到一个标识列，例如主键，则可以找到一种不同的解决方案。我们使用 DB2 OLAP 函数 row_number() 将一个惟一的数值赋给每个受影响的列，然后运行一个 block-wise 搜索更新，之后执行一次 COMMIT。清单 13 阐释了这一点。最里边的子查询找出所有需要更新的行；用户可能已经给出了一些条件来限制这些行。此外，每个行被赋予一个行号。下一步则根据行号过滤出那些属于将被地理编码的当前块的行。最后，最外面的 UPDATE 在相同的 SQL 作用域内将那些行逐个提供给地理编码器函数。这样，地理编码器只需为一个块进行初始化，而不必为每一行进行初始化。


清单 13. 为地理编码使用搜索更新

            UPDATE <user_table>
            SET    location = <geocoder_function> ( <parameters> )
            WHERE  id IN ( SELECT t.id
            FROM   ( SELECT id, ROW_NUMBER OVER ( ORDER BY id ) AS rn
            FROM   <user_table>
            WHERE  <geocoding_selection> ) AS t
            WHERE  t.rn BETWEEN <first_row_num> AND <last_row_num> )@

减少对空间函数的调用

当看到我们在本文前面使用的空间查询时，您会注意到，为了重叠测试，一个新的几何图形被构造成参数。现在，ST_LineString 构造函数是一个没有任何副作用的确定性的函数。DB2 优化器知道那些条件，它可以断定多次调用那个函数不会有害。取决于您的系统和查询，这可能是一个聪明的选择，但是也可能不是最佳的选择。例如，在使用 DPF 特性的分区环境中，在每个分区上构造 linestring，与在单独一个分区上构造几何图形，然后通过表队列将它分布到其他需要这个值的分区上相比，可能要好得多。在另一种场景中，为将进行空间重叠测试的每一行生成 linestring 又可能产生完全不同的结果。因此，可能有必要使用公共表表达式重新构造空间查询，以确保只调用一次构造函数。在清单 14 中，首先可以看到原有的查询，后面有一个重新构造的查询。这里执行的 SQL 脚本可以在 下载 一节中找到。这两个查询都表达了相同的语义，但是在我们的系统和数据库配置中，第二个查询运行起来要快 9%。


清单 14. 重构空间查询

            $ db2batch -d testdb -f test_cte.sql -i complete -s on
            ---------------------------------------------
            Statement number: 1
            SELECT id
            FROM   roads2
            WHERE  db2gse.ST_Intersects(shape, db2gse.ST_LineString(
            'linestring(10 50, 20 40)', 1003)) = 1
            Prepare Time is:           0.000      seconds
            Execute Time is:           0.819      seconds
            Fetch Time is:             0.000      seconds
            Elapsed Time is:           0.819      seconds
            ---------------------------------------------
            Statement number: 2
            WITH t(g) AS
            ( VALUES ( db2gse.ST_LineString('linestring(10 50, 20 40)', 1003) ) )
            SELECT r.id
            FROM   roads2 AS r, t
            WHERE  db2gse.ST_Intersects(r.shape, t.g) = 1
            Prepare Time is:           0.000      seconds
            Execute Time is:           0.744      seconds
            Fetch Time is:             0.000      seconds
            Elapsed Time is:           0.745      seconds
            ---------------------------------------------

使用空间网格索引

至此，我们讨论了很多提高空间操作性能的不同方面。现在我们将讨论最显著的一个方面，也就是对空间索引的使用。这里我们解释您应该做些什么，以便让 DB2 优化器选择使用一个空间索引。索引本身的调优在 下一节 中解释。

空间索引是建立在 DB2 可扩展索引框架（请参阅 参考资料 一节，找到关于 Index Extensions 的那篇文章）之上的一种扩展的索引。由于空间数据的多维特性，DB2 通常使用的 B* 树并不是很合适，因此 DB2 Spatial Extender 提供了专门的索引机制。DB2 索引扩展由三个部分组成：

  • 用于在 INSERT 和 UPDATE 操作中构造索引键的键生成器函数。
  • 用于定义在查询执行期间空间索引的搜索范围的范围生成器函数。
  • 告诉 DB2 优化器在哪些条件下可以使用空间索引的空间谓词。

DB2 Spatial Extender 已经定义了所有这些部分。除了真正创建一个空间索引外，您还必须熟悉最后一个部分。空间谓词是与比较两个几何图形的函数（即 ST_Contains、ST_Within、ST_Intersects、ST_Crosses、ST_Overlaps、ST_Touches、ST_EnvIntersects、ST_MBRIntersects、ST_Equals 和 ST_Distance）相关的谓词。其他任何空间函数都不能使用网格索引。而且，只有当上述函数中的一个函数出现在查询的 WHERE 子句中，并且该函数至少有一个参数标识出定义了网格索引的列的时候，才能使用空间网格索引。这听起来好像有一大堆的条件，但还是比较简单的：通过使用列可以找到索引，通过使用函数可以知道空间谓词。

此外，DB2 要求遵从基本的语法规则，以检测潜在的对空间谓词的使用。函数调用必须发生在针对值 1 进行的相等比较式的左边。一个例外是 ST_Distance 函数，它必须出现在针对一个任意距离的小于比较式中。清单 15 给出了这两种正确的规范形式。


清单 15. 使用空间索引的语法规则

            SELECT ...
            FROM   <user_table>
            WHERE  ST_Intersects(<indexed_shape_column>, ...) = 1@
            SELECT ...
            FROM   <user_table>
            WHERE  ST_Distance(..., <indexed_shape_column>) < <some_distance>@

如果所有条件都符合，同时也符合简单的语法规则，那么还不能保证可以使用空间网格索引来满足查询。DB2 优化器计算不同的访问计划，并试图找出总执行成本最低的计划。为检查在查询时是否真正使用了一个空间网格索引，应该查看一下访问计划。可以通过 db2expln 命令行工具，或者在 DB2 Control Center 中右键单击数据库并选择 Explain SQL 选项来收集访问计划。不管使用哪种方法，都可以生成和显示访问计划。图 3 描绘了我们在本文前面多次使用的查询的访问计划。如果计划包括对扩展索引的一个扫描，您将发现 EISCAN（在图 3 中高亮显示）。此外，您将在 EISCAN 下面发现网格索引的名称。


图 3. 带有网格索引扫描的空间查询访问计划
带有网格索引扫描的空间查询访问计划

前面我们解释过，DB2 优化器不完全知道空间网格索引的细节。而且，优化器很难判断那样的索引扫描的成本和选择性。DB2 开发小组目前选择的解决方案使用户可以提供对空间谓词选择性的估计。为此，可以在 WHERE 子句中将关键字 SELECTIVITY 放在谓词的后面，后面带一个 0 到 1 之间的对选择性的估计值。这个值越低，优化器就越有可能选择扫描网格索引。清单 16 给出了一个查询例子，在这个查询中，向优化器提示空间谓词只有很少的符合条件的行。


清单 16. 为空间谓词指定选择性

            SELECT ...
            FROM   <user_table>
            WHERE  ST_Intersects(<indexed_shape_column>, ...) = 1 SELECTIVITY 0.000001@



回页首


调优空间网格索引

spatial extender 提供了一个 index advisor，以帮助您调优空间索引。index advisor 可以通过命令行工具 gseidx 访问，它的语法比较罗嗦，这一点跟 SQL 本身一样。该工具不仅可用于获得关于各种不同网格大小的建议，还可以用于收集一个已有的或计划中的（虚）索引的统计信息。所以，可以提取关于在选择某种网格大小时在哪个网格层次上将生成多少索引项的信息，而不必真正创建和物化索引。您应该注意到，Index Advisor 提供的建议可以作为索引优化的出发点。


清单 17. Spatial Extender index advisor 的示例输出

            $ gseidx "CONNECT TO testdb GET GEOMETRY STATISTICS FOR COLUMN roads(shape)             USING GRID SIZES (0.5) SHOW HISTOGRAM WITH 10 BUCKETS"
            Number of Rows: 110979
            Number of non-empty Geometries: 110979
            Number of empty Geometries: 0
            Number of null values: 0
            Extent covered by data:
            Minimum X: -31.257690
            Maximum X: 66.074104
            Minimum Y: 34.824085
            Maximum Y: 72.000000
            Grid Level 1
            ------------
            Grid Size                     : 0.5
            Number of Geometries          : 110973
            Number of Index Entries       : 147461
            Number of occupied Grid Cells : 6596
            Index Entry/Geometry ratio    : 1.328801
            Geometry/Grid Cell ratio      : 16.824287
            Maximum number of Geometries per Grid Cell: 257
            Minimum number of Geometries per Grid Cell: 1
            Index Entries :  1      2      3      4      10
            ---------------  ------ ------ ------ ------ ------
            Absolute      :  82240  24962  236    3361   174
            Percentage (%):  74.11  22.49  0.21   3.03   0.16
            Grid Level X
            ------------
            Number of Geometries          : 6
            Number of Index Entries       : 6
            Histogram:
            ----------
            MBR Size             Geometry Count
            -------------------- --------------------
            0.340000               105777
            0.680000                 4750
            1.020000                  334
            1.360000                   80
            1.700000                   22
            2.040000                    4
            2.380000                    5
            2.720000                    5
            3.400000                    2
            $ gseidx "CONNECT TO testdb GET GEOMETRY STATISTICS FOR COLUMN roads(shape)             ADVISE GRID SIZES"
            Query Window Size:     Suggested Grid Sizes:           Index Entry Cost:
            --------------------   -----------------------------   ----------------------
            0.01:             0.27,      0.54,       1.6             8.3
            0.02:             0.27,      0.54,       1.6             8.7
            0.05:             0.27,      0.54,       1.6             9.9
            0.1:             0.27,      0.54,       1.6              12
            0.2:             0.17,      0.51,       1.8              17
            0.5:             0.17,      0.51,       1.8              40
            1:             0.27,      0.68,       1.7             100
            2:             0.43,       1.1,       2.2             290
            5:             0.68,       2.4,       4.8            1300
            10:              1.1,         5,         0            4500
            20:              1.7,        10,         0           15000

如果您熟悉空间数据和网格索引，那么结果就无需解释了。关于空间网格索引机制和 Index Advisor 的更多细节可以在 DB2 Spatial Extender User's Guide and Reference（请参阅 参考资料 一节）中找到。值得一提的是，过去的经验表明，与精细调优的网格索引相比，根据空间属性聚集数据和使用一个适当定义的缓冲池更能对空间性能产生显著的影响 —— 只要索引的参数大致在适当的范围内。

回页首


选择表空间类型

如果有很多数据修改操作，而查询较少，那么应该将注意力放在好的写性能上。DB2 将数据库中的所有数据都放在表空间中。管理员可以选择表空间的类型和组成表空间的容器的类型。表空间的类型可以是 database managed（DMS）或 system managed（SMS），表空间类型的选择对空间数据的写性能有一定的影响。

建议为 LONG 数据选择 DMS 类型的表空间，换句话说，选择存放 LOB 的表空间。这样做的效果是，大对象化的空间数据将被放入到那个表空间。做出这一决定的原因在于 DB2 的内部工作原理。这可以以一种更异步的方式将 LOB 写到 DMS 表空间中，而将 LOB 写到 SMS 表空间则要求同步的文件 I/O。

一旦将数据放在 DMS 表空间上，就可以根据表空间的容器进一步选择是使用原始设备还是文件系统。对于大对象化的空间数据，通常使用文件系统更好一些。理由是：对基于容器的文件系统的访问要经过操作系统内核，而操作系统带有一个文件系统缓存，可以加快对文件的重复访问。而对原始设备的访问则没有缓存，导致物理设备上的直接读写操作。现在，对于一般的数据库操作，不必考虑文件系统缓存，因为 DB2 实现了缓冲池，这些缓冲池已经做了必要的缓存。但是对于 LOB 情况又不同了。由于不同的内部存储模型和潜在的巨大对象（大到 2GB），这里没有缓冲池。所以文件系统缓存可以很大程度上帮助避免对磁盘的读写操作。

性能比较

清单 18 展示了不同表空间类型的影响。首先在一个 SMS 表空间上执行导入操作，一次使用一个较小的 inline length，一次使用都以内联形式存储的空间值，然后在一个 DMS 表空间上再次使用不同的 inline length 设置重复上述过程。可以使用 tableCreationParameters 选项指定目标表空间。最后，在两个表（使用较小的 inline length）上运行空间查询，以显示查询性能不受表空间的影响。


清单 18. SMS 与 DMS 表空间的性能比较

            $ time db2se import_shape testdb -fileName /home/stolze/europe/roads             -srsName WGS84_SRS_1003 -tableName roads_sms             -tableCreationParameters "IN userspace1" -createTableFlag 1             -spatialColumn shape -typeName ST_LineString -inlineLength 292             -idColumn id -commitScope 1500
            GSE0000I  The operation was completed successfully.
            real    3m5.618s
            user    0m0.056s
            sys     0m0.026s
            $ time db2se import_shape testdb -fileName /home/stolze/europe/roads             -srsName WGS84_SRS_1003 -tableName roads_sms             -tableCreationParameters "IN userspace1" -createTableFlag 1             -spatialColumn shape -typeName ST_LineString -inlineLength 2000             -idColumn id -commitScope 1500
            GSE0000I  The operation was completed successfully.
            real    1m56.643s
            user    0m0.049s
            sys     0m0.026s
            $ time db2se import_shape testdb -fileName /home/stolze/europe/roads             -srsName WGS84_SRS_1003 -tableName roads_dms             -tableCreationParameters "IN dms" -createTableFlag 1             -spatialColumn shape -typeName ST_LineString -inlineLength 292             -idColumn id -commitScope 1500
            GSE0000I  The operation was completed successfully.
            real    0m49.310s
            user    0m0.053s
            sys     0m0.028s
            $ time db2se import_shape testdb -fileName /home/stolze/europe/roads             -srsName WGS84_SRS_1003 -tableName roads_dms             -tableCreationParameters "IN dms" -createTableFlag 1             -spatialColumn shape -typeName ST_LineString -inlineLength 2000             -idColumn id -commitScope 1500
            GSE0000I  The operation was completed successfully.
            real    0m38.766s
            user    0m0.054s
            sys     0m0.024s
            $ db2batch -d testdb -f test_tablespace.sql -i complete -s on
            ---------------------------------------------
            Statement number: 1
            SELECT id
            FROM   roads_sms
            WHERE  db2gse.ST_Intersects(shape, db2gse.ST_LineString(
            'linestring(10 50, 20 40)', 1003)) = 1
            Prepare Time is:           0.000      seconds
            Execute Time is:           0.942      seconds
            Fetch Time is:             0.000      seconds
            Elapsed Time is:           0.943      seconds
            ---------------------------------------------
            Statement number: 2
            SELECT id
            FROM   roads_dms
            WHERE  db2gse.ST_Intersects(shape, db2gse.ST_LineString(
            'linestring(10 50, 20 40)', 1003)) = 1
            Prepare Time is:           0.000      seconds
            Execute Time is:           0.953      seconds
            Fetch Time is:             0.000      seconds
            Elapsed Time is:           0.954      seconds
            ---------------------------------------------

与 SMS 表空间相比，将数据插入 DMS 表空间上的表中花费的时间大约只有四分之一。在解释这些数值的时候，必须记住 DMS 和 SMS 表空间之间的基本不同点。DMS 表空间是在创建表空间时预先分配的。这意味着存放数据的页是已经存在的。而 SMS 表空间是在运行时动态伸缩的，导入操作会导致很多新的页被分配，同时表空间（和它的文件）也随之增长。所以，性能提升的很大一部分要归功于 DMS 上页的预先分配。但是，当比较使用不同 inline length 取得的运行时间时，我们发现，如果使用较小的 inline length（即更多的大对象化几何图形），那么将 SMS 换成 DMS 可以获得 73% 的性能提升。如果使用较大的 inline length，则性能提升只有 66%。所以附加的性能提升显然源自对大对象化数据更好的处理。

结束语

在本文中，我们展示了一些提升空间数据库性能的重要技巧。文中谈到了各种调优步骤，包括基本的系统调优，设置空间数据的 inline length，根据空间属性聚集一个表中的行，调优空间网格索引，以及选择适当的表空间类型。我们还解释了一些决策的原因，并给出了通用的指导原则。此外，我们还在一个非常简单的场景中演示了各个选项的效果。应用建议的指导原则所得到的结果取决于数据库中实际的数据，以及整个系统和数据库的配置。
关闭本页
 
首页 | 投资与合作 | 服务条款 | 隐私政策 | 收藏本站 | 设为首页 | 新用户注册 | 免责声明 | 使用帮助
Copyright ©2005-2008 chinaitpower.com All rights reserved. www.chinaitpower.com 版权所有