DB2 Spatial Extender 性能調優（1）

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。

應該記住，以內聯方式存儲數據比以大對象化方式存儲數據要可取得多。原因是，內聯的數據當作 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

當在數據庫中創建一個新的結構類型時，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，那麼這個重組過程將把大對象化的空間值轉換成內聯值。

選擇適當的 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
---------------------------------------------