本文示例源代碼或素材下載 關于編寫空間 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 rowINSERT INTO tabVALUES ( 1, db2gse.ST_LineString(　 'linestring (11.9963970 54.9979739, 11.9947259 55.0000000)',　 1003) )@-- insert multiple rows at onceINSERT INTO tabSELECT 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> )WHERE　id IN ( SELECT t.id　 FROM　 ( SELECT id, ROW_NUMBER OVER ( ORDER BY id ) AS rnFROM　 <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: 1SELECT idFROM　 roads2WHERE　db2gse.ST_Intersects(shape, db2gse.ST_LineString(　'linestring(10 50, 20 40)', 1003)) = 1Prepare Time is:　 0.000　seconds　Execute Time is:　 0.819　secondsFetch Time is: 0.000　seconds　Elapsed Time is:　 0.819　seconds　---------------------------------------------Statement number: 2WITH t(g) AS　 ( VALUES ( db2gse.ST_LineString('linestring(10 50, 20 40)', 1003) ) )SELECT r.idFROM　 roads2 AS r, tWHERE　db2gse.ST_Intersects(r.shape, t.g) = 1Prepare Time is:　 0.000　seconds　Execute Time is:　 0.744　secondsFetch 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: 110979Number of non-empty Geometries: 110979Number of empty Geometries: 0Number of null values: 0Extent covered by data:Minimum X: -31.257690Maximum X: 66.074104Minimum Y: 34.824085Maximum Y: 72.000000Grid Level 1------------Grid Size : 0.5Number of Geometries　: 110973Number of Index Entries　 : 147461Number of occupied Grid Cells : 6596Index Entry/Geometry ratio: 1.328801Geometry/Grid Cell ratio　: 16.824287Maximum number of Geometries per Grid Cell: 257Minimum number of Geometries per Grid Cell: 1Index Entries :　1　2　3　4　10---------------　------ ------ ------ ------ ------Absolute　:　82240　24962　2363361　 174　Percentage (%):　74.11　22.49　0.21　 3.03　 0.16　Grid Level X------------Number of Geometries　: 6Number of Index Entries　 : 6Histogram:----------MBR Size Geometry Count-------------------- --------------------0.340000　 1057770.680000 47501.020000　3341.360000　 801.700000　 222.04000042.38000052.72000053.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.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.8130010:　1.1, 5, 0450020:　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 1500GSE0000I　The operation was completed successfully.real3m5.618suser0m0.056ssys 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 1500GSE0000I　The operation was completed successfully.real1m56.643suser0m0.049ssys 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 1500GSE0000I　The operation was completed successfully.real0m49.310suser0m0.053ssys 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 1500GSE0000I　The operation was completed successfully.real0m38.766suser0m0.054ssys 0m0.024s$ db2batch -d testdb -f test_tablespace.sql -i complete -s on---------------------------------------------Statement number: 1SELECT idFROM　 roads_smsWHERE　db2gse.ST_Intersects(shape, db2gse.ST_LineString(　'linestring(10 50, 20 40)', 1003)) = 1Prepare Time is:　 0.000　seconds　Execute Time is:　 0.942　secondsFetch Time is: 0.000　seconds　Elapsed Time is:　 0.943　seconds---------------------------------------------Statement number: 2SELECT idFROM　 roads_dmsWHERE　db2gse.ST_Intersects(shape, db2gse.ST_LineString(　'linestring(10 50, 20 40)', 1003)) = 1Prepare Time is:　 0.000　seconds　Execute Time is:　 0.953　secondsFetch 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