本文示例源代码或素材下载
关于编写空间 SQL 语句的提示和技巧SQL 的范例是，用户（或应用程序）只需告诉数据库系统要 做什么，系统自己就会得出 如何做 这件事的最佳方法。因此，SQL 是一种纯描述性语言。DB2 优化器尽力实现这个理想，通常做得也很好。但是，还存在一些非凡情况，在这些情况下，用户的干预可以改善性能。因此，我们描述一些您应该记住的用于空间查询和空间 DML 语句的概念。处理多个行一开始，SQL （现在也仍然）是一种面向集合的语言，而不是过程语言。这意味着同时处理一个集合中相关的行，而不是一个接一个地处理每一行。一个很好的例子就是 INSERT 语句：假如需要将多个行插入到相同的表中，那么是在一条 SQL 语句中处理所有那些行，而不是触发多条语句。这一事实对于空间数据尤其有意义，因为几乎所有空间函数的实现都使得在同一条 SQL 语句中多次执行一个函数可以得到性能好处。在第一次调用函数时，函数的内部处理执行一些必要的初始化，例如设置计算所需的内存区域，随后的调用则利用这个已建立好的基础设施，直到在这条语句的作用域内最后一次调用函数时销毁这个基础设施。所以，假如为每个要插入的行单独执行一条 INSERT 语句，那么前面提到的基础设施的初始化和销毁将每执行一次就重复一次，即对于每一行都重复一次。而将这些语句组合成一条单独的语句可以避免重复的初始化和销毁步骤，从而提升总体性能。此外，同时处理多个行还可以提高性能，因为减少了应用程序（DB2 客户机）与数据库引擎之间的交互次数。让我们通过一个简单的例子来展示上述效果，在这个例子中，我们插入 50 个不同的行。首先，像清单 12 显示的第 1 个语句那样，使用 SQL 分别插入所有的行。清单 12 中的第 2 个语句将所有 50 行组合到一个单独的插入操作中。其思想是，在一个描述性级别上，在 FROM 子句中建立一个临时表，这个表由在调用任何空间函数之前导入的数据组成。接着，扫描那个临时表中的所有行，并应用空间函数，从而构建一个新的表，最终这个表中的数据被插入 tab 表中。
123456789下一页
清单 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）、被导入的总行数或所有这些因素的限制。
上一页123456789下一页
对 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>
SETlocation = <geocoder_function> ( <parameters> )
WHEREid IN ( SELECT t.id
FROM ( SELECT id, ROW_NUMBER OVER ( ORDER BY id ) AS rn
FROM <user_table>
WHERE<geocoding_selection> ) AS t
WHEREt.rn BETWEEN <first_row_num> AND <last_row_num> )@
上一页123456789下一页
减少对空间函数的调用当看到我们在本文前面使用的空间查询时，您会注重到，为了重叠测试，一个新的几何图形被构造成参数。现在，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
WHEREdb2gse.ST_Intersects(shape, db2gse.ST_LineString(
'linestring(10 50, 20 40)', 1003)) = 1
Prepare Time is: 0.000seconds
Execute Time is: 0.819seconds
Fetch Time is: 0.000seconds
Elapsed Time is: 0.819seconds
---------------------------------------------
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
WHEREdb2gse.ST_Intersects(r.shape, t.g) = 1
Prepare Time is: 0.000seconds
Execute Time is: 0.744seconds
Fetch Time is: 0.000seconds
Elapsed Time is: 0.745seconds
---------------------------------------------
上一页123456789下一页
使用空间网格索引至此，我们讨论了很多提高空间操作性能的不同方面。现在我们将讨论最显著的一个方面，也就是对空间索引的使用。这里我们解释您应该做些什么，以便让 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 给出了这两种正确的规范形式。
上一页123456789下一页
清单 15. 使用空间索引的语法规则SELECT ...
FROM <user_table>
WHEREST_Intersects(<indexed_shape_column>, ...) = 1@
SELECT ...
FROM <user_table>
WHEREST_Distance(..., <indexed_shape_column>) < <some_distance>@假如所有条件都符合，同时也符合简单的语法规则，那么还不能保证可以使用空间网格索引来满足查询。DB2 优化器计算不同的访问计划，并试图找出总执行成本最低的计划。为检查在查询时是否真正使用了一个空间网格索引，应该查看一下访问计划。可以通过 db2expln 命令行工具，或者在 DB2 Control Center 中右键单击数据库并选择 Explain SQL 选项来收集访问计划。不管使用哪种方法，都可以生成和显示访问计划。图 3 描绘了我们在本文前面多次使用的查询的访问计划。假如计划包括对扩展索引的一个扫描，您将发现 EISCAN（在图 3 中高亮显示）。此外，您将在 EISCAN 下面发现网格索引的名称。图 3. 带有网格索引扫描的空间查询访问计划
上一页123456789下一页
清单 16. 为空间谓词指定选择性SELECT ...
FROM <user_table>
WHEREST_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 :123410
--------------------- ------ ------ ------ ------
Absolute:82240249622363361 174
Percentage (%):74.1122.490.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.020000334
1.360000 80
1.700000 22
2.0400004
2.3800005
2.7200005
3.4000002
$ 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.612
0.2: 0.17,0.51, 1.817
0.5: 0.17,0.51, 1.840
1: 0.27,0.68, 1.7 100
2: 0.43, 1.1, 2.2 290
5: 0.68, 2.4, 4.81300
10:1.1, 5, 04500
20:1.7,10, 0 15000
上一页123456789下一页
假如您熟悉空间数据和网格索引，那么结果就无需解释了。关于空间网格索引机制和 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），这里没有缓冲池。所以文件系统缓存可以很大程度上帮助避免对磁盘的读写操作。
上一页123456789下一页
性能比较清单 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
GSE0000IThe operation was completed successfully.
real3m5.618s
user0m0.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
GSE0000IThe operation was completed successfully.
real1m56.643s
user0m0.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
GSE0000IThe operation was completed successfully.
real0m49.310s
user0m0.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
GSE0000IThe operation was completed successfully.
real0m38.766s
user0m0.054s
sys 0m0.024s
$ db2batch -d testdb -f test_tablespace.sql -i complete -s on
---------------------------------------------
Statement number: 1
SELECT id
FROM roads_sms
WHEREdb2gse.ST_Intersects(shape, db2gse.ST_LineString(
'linestring(10 50, 20 40)', 1003)) = 1
Prepare Time is: 0.000seconds
Execute Time is: 0.942seconds
Fetch Time is: 0.000seconds
Elapsed Time is: 0.943seconds
---------------------------------------------
Statement number: 2
SELECT id
FROM roads_dms
WHEREdb2gse.ST_Intersects(shape, db2gse.ST_LineString(
'linestring(10 50, 20 40)', 1003)) = 1
Prepare Time is: 0.000seconds
Execute Time is: 0.953seconds
Fetch Time is: 0.000seconds
Elapsed Time is: 0.954seconds
---------------------------------------------与 SMS 表空间相比，将数据插入 DMS 表空间上的表中花费的时间大约只有四分之一。在解释这些数值的时候，必须记住 DMS 和 SMS 表空间之间的基本不同点。DMS 表空间是在创建表空间时预先分配的。这意味着存放数据的页是已经存在的。而 SMS 表空间是在运行时动态伸缩的，导入操作会导致很多新的页被分配，同时表空间（和它的文件）也随之增长。所以，性能提升的很大一部分要归功于 DMS 上页的预先分配。但是，当比较使用不同 inline length 取得的运行时间时，我们发现，假如使用较小的 inline length（即更多的大对象化几何图形），那么将 SMS 换成 DMS 可以获得 73% 的性能提升。假如使用较大的 inline length，则性能提升只有 66%。所以附加的性能提升显然源自对大对象化数据更好的处理。结束语在本文中，我们展示了一些提升空间数据库性能的重要技巧。文中谈到了各种调优步骤，包括基本的系统调优，设置空间数据的 inline length，根据空间属性聚集一个表中的行，调优空间网格索引，以及选择适当的表空间类型。我们还解释了一些决策的原因，并给出了通用的指导原则。此外，我们还在一个非常简单的场景中演示了各个选项的效果。应用建议的指导原则所得到的结果取决于数据库中实际的数据，以及整个系统和数据库的配置。
上一页123456789
