本文示例源代码或素材下载 　　关于编写空间 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>
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> )@ 上一页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
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　
--------------------------------------------- 上一页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>
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 给出了一个查询例子，在这个查询中，向优化器提示空间谓词只有很少的符合条件的行。 上一页123456789下一页 　　清单 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 上一页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
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，根据空间属性聚集一个表中的行，调优空间网格索引，以及选择适当的表空间类型。我们还解释了一些决策的原因，并给出了通用的指导原则。此外，我们还在一个非常简单的场景中演示了各个选项的效果。应用建议的指导原则所得到的结果取决于数据库中实际的数据，以及整个系统和数据库的配置。 上一页123456789