本文示例源代碼或素材下載 關于編寫空間 SQL 語句的提示和技巧SQL 的范例是，用戶（或應用程序）只需告訴數(shù)據(jù)庫系統(tǒng)要 做什么，系統(tǒng)自己就會得出 如何做 這件事的最佳方法。因此，SQL 是一種純描述性語言。DB2 優(yōu)化器盡力實現(xiàn)這個理想，通常做得也很好。但是，還存在一些非凡情況，在這些情況下，用戶的干預可以改善性能。因此，我們描述一些您應該記住的用于空間查詢和空間 DML 語句的概念。處理多個行一開始，SQL （現(xiàn)在也仍然）是一種面向集合的語言，而不是過程語言。這意味著同時處理一個集合中相關的行，而不是一個接一個地處理每一行。一個很好的例子就是 INSERT 語句：假如需要將多個行插入到相同的表中，那么是在一條 SQL 語句中處理所有那些行，而不是觸發(fā)多條語句。這一事實對于空間數(shù)據(jù)尤其有意義，因為幾乎所有空間函數(shù)的實現(xiàn)都使得在同一條 SQL 語句中多次執(zhí)行一個函數(shù)可以得到性能好處。在第一次調(diào)用函數(shù)時，函數(shù)的內(nèi)部處理執(zhí)行一些必要的初始化，例如設置計算所需的內(nèi)存區(qū)域，隨后的調(diào)用則利用這個已建立好的基礎設施，直到在這條語句的作用域內(nèi)最后一次調(diào)用函數(shù)時銷毀這個基礎設施。所以，假如為每個要插入的行單獨執(zhí)行一條 INSERT 語句，那么前面提到的基礎設施的初始化和銷毀將每執(zhí)行一次就重復一次，即對于每一行都重復一次。而將這些語句組合成一條單獨的語句可以避免重復的初始化和銷毀步驟，從而提升總體性能。此外，同時處理多個行還可以提高性能，因為減少了應用程序（DB2 客戶機）與數(shù)據(jù)庫引擎之間的交互次數(shù)。讓我們通過一個簡單的例子來展示上述效果，在這個例子中，我們插入 50 個不同的行。首先，像清單 12 顯示的第 1 個語句那樣，使用 SQL 分別插入所有的行。清單 12 中的第 2 個語句將所有 50 行組合到一個單獨的插入操作中。其思想是，在一個描述性級別上，在 FROM 子句中建立一個臨時表，這個表由在調(diào)用任何空間函數(shù)之前導入的數(shù)據(jù)組成。接著，掃描那個臨時表中的所有行，并應用空間函數(shù)，從而構(gòu)建一個新的表，最終這個表中的數(shù)據(jù)被插入 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)@下載 一節(jié)中提供的腳本 test_multi_row.sql 用同樣的一組數(shù)據(jù)運行兩種不同的方法。被插入的數(shù)據(jù)源自我們之前用過的 shapefile 文件 europe/roads.shp。第一種方法總共花費 0.050 秒，而組合語句可以在 0.012 秒內(nèi)完成。應該注重的是，大部分時間花在解析和編譯 SQL 語句上。假如使用了預置語句，那么兩種方法之間的差距可能拉大，也可能縮小，這取決于您的應用程序、系統(tǒng)配置和數(shù)據(jù)。但是，不大可能出現(xiàn)第一種場景好于多行語句的情況。而且應該記住，構(gòu)造函數(shù) ST_LineString 不執(zhí)行任何復雜的空間計算；它只是在文本表示上執(zhí)行一次 single-sweep 掃描，并將坐標轉(zhuǎn)換成內(nèi)部編碼。該函數(shù)占用的內(nèi)存也很少。還應記住，預置語句可用于多行插入（例如，在靜態(tài)嵌入式 SQL 應用程序中）并且多行插入可以執(zhí)行多次，以利用上述優(yōu)點。同樣的技術還用于空間導入過程中，這就是為什么在消息文件的一開始出現(xiàn)像 “Using 342 rows per single INSERT statement 這樣的信息性消息的原因。在導入期間，總行數(shù)受 SQL 語句的最大可能大小、提交范圍（commit scope）、被導入的總行數(shù)或所有這些因素的限制。 上一頁123456789下一頁 對 INSERT 語句的這些考慮同樣適用于 SELECT 或 UPDATE 語句。例如，DB2 Spatial Extender 存儲過程 ST_run_gc 對一組行進行地理編碼（geocode）。假如為提交范圍指定一個值，那么該過程可能不會立即處理所有受影響的行，但是其間它必須為這些行計數(shù)，并執(zhí)行一個 COMMIT。初級的方法是使用一個游標對表進行掃描，并為每一行執(zhí)行一個定位的更新語句。然而，定位更新會碰到我們剛才討論的一個問題，那就是必須一次又一次地初始化地理編碼器。假如地理編碼不僅僅是構(gòu)造一個 linestring，而是執(zhí)行更復雜的功能，那么對性能的影響將會更加顯著。所以，假如在表中找到一個標識列，例如主鍵，則可以找到一種不同的解決方案。我們使用 DB2 OLAP 函數(shù) row_number() 將一個惟一的數(shù)值賦給每個受影響的列，然后運行一個 block-wise 搜索更新，之后執(zhí)行一次 COMMIT。清單 13 闡釋了這一點。最里邊的子查詢找出所有需要更新的行；用戶可能已經(jīng)給出了一些條件來限制這些行。此外，每個行被賦予一個行號。下一步則根據(jù)行號過濾出那些屬于將被地理編碼的當前塊的行。最后，最外面的 UPDATE 在相同的 SQL 作用域內(nèi)將那些行逐個提供給地理編碼器函數(shù)。這樣，地理編碼器只需為一個塊進行初始化，而不必為每一行進行初始化。清單 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下一頁 減少對空間函數(shù)的調(diào)用當看到我們在本文前面使用的空間查詢時，您會注重到，為了重疊測試，一個新的幾何圖形被構(gòu)造成參數(shù)。現(xiàn)在，ST_LineString 構(gòu)造函數(shù)是一個沒有任何副作用的確定性的函數(shù)。DB2 優(yōu)化器知道那些條件，它可以斷定多次調(diào)用那個函數(shù)不會有害。取決于您的系統(tǒng)和查詢，這可能是一個聰明的選擇，但是也可能不是最佳的選擇。例如，在使用 DPF 特性的分區(qū)環(huán)境中，在每個分區(qū)上構(gòu)造 linestring，與在單獨一個分區(qū)上構(gòu)造幾何圖形，然后通過表隊列將它分布到其他需要這個值的分區(qū)上相比，可能要好得多。在另一種場景中，為將進行空間重疊測試的每一行生成 linestring 又可能產(chǎn)生完全不同的結(jié)果。因此，可能有必要使用公共表表達式重新構(gòu)造空間查詢，以確保只調(diào)用一次構(gòu)造函數(shù)。在清單 14 中，首先可以看到原有的查詢，后面有一個重新構(gòu)造的查詢。這里執(zhí)行的 SQL 腳本可以在 下載 一節(jié)中找到。這兩個查詢都表達了相同的語義，但是在我們的系統(tǒng)和數(shù)據(jù)庫配置中，第二個查詢運行起來要快 9%。清單 14. 重構(gòu)空間查詢$ 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下一頁 使用空間網(wǎng)格索引至此，我們討論了很多提高空間操作性能的不同方面。現(xiàn)在我們將討論最顯著的一個方面，也就是對空間索引的使用。這里我們解釋您應該做些什么，以便讓 DB2 優(yōu)化器選擇使用一個空間索引。索引本身的調(diào)優(yōu)在 下一節(jié) 中解釋。空間索引是建立在 DB2 可擴展索引框架（請參閱 參考資料 一節(jié)，找到關于 Index Extensions 的那篇文章）之上的一種擴展的索引。由于空間數(shù)據(jù)的多維特性，DB2 通常使用的 B* 樹并不是很合適，因此 DB2 Spatial Extender 提供了專門的索引機制。DB2 索引擴展由三個部分組成：用于在 INSERT 和 UPDATE 操作中構(gòu)造索引鍵的鍵生成器函數(shù)。用于定義在查詢執(zhí)行期間空間索引的搜索范圍的范圍生成器函數(shù)。告訴 DB2 優(yōu)化器在哪些條件下可以使用空間索引的空間謂詞。DB2 Spatial Extender 已經(jīng)定義了所有這些部分。除了真正創(chuàng)建一個空間索引外，您還必須熟悉最后一個部分。空間謂詞是與比較兩個幾何圖形的函數(shù)（即 ST_Contains、ST_Within、ST_Intersects、ST_Crosses、ST_Overlaps、ST_Touches、ST_EnvIntersects、ST_MBRIntersects、ST_Equals 和 ST_Distance）相關的謂詞。其他任何空間函數(shù)都不能使用網(wǎng)格索引。而且，只有當上述函數(shù)中的一個函數(shù)出現(xiàn)在查詢的 WHERE 子句中，并且該函數(shù)至少有一個參數(shù)標識出定義了網(wǎng)格索引的列的時候，才能使用空間網(wǎng)格索引。這聽起來似乎有一大堆的條件，但還是比較簡單的：通過使用列可以找到索引，通過使用函數(shù)可以知道空間謂詞。此外，DB2 要求遵從基本的語法規(guī)則，以檢測潛在的對空間謂詞的使用。函數(shù)調(diào)用必須發(fā)生在針對值 1 進行的相等比較式的左邊。一個例外是 ST_Distance 函數(shù)，它必須出現(xiàn)在針對一個任意距離的小于比較式中。清單 15 給出了這兩種正確的規(guī)范形式。 上一頁123456789下一頁 清單 15. 使用空間索引的語法規(guī)則SELECT ...FROM　 <user_table>WHERE　ST_Intersects(<indexed_shape_column>, ...) = 1@SELECT ...FROM　 <user_table>WHERE　ST_Distance(..., <indexed_shape_column>) < <some_distance>@假如所有條件都符合，同時也符合簡單的語法規(guī)則，那么還不能保證可以使用空間網(wǎng)格索引來滿足查詢。DB2 優(yōu)化器計算不同的訪問計劃，并試圖找出總執(zhí)行成本最低的計劃。為檢查在查詢時是否真正使用了一個空間網(wǎng)格索引，應該查看一下訪問計劃。可以通過 db2expln 命令行工具，或者在 DB2 Control Center 中右鍵單擊數(shù)據(jù)庫并選擇 Explain SQL 選項來收集訪問計劃。不管使用哪種方法，都可以生成和顯示訪問計劃。圖 3 描繪了我們在本文前面多次使用的查詢的訪問計劃。假如計劃包括對擴展索引的一個掃描，您將發(fā)現(xiàn) EISCAN（在圖 3 中高亮顯示）。此外，您將在 EISCAN 下面發(fā)現(xiàn)網(wǎng)格索引的名稱。圖 3. 帶有網(wǎng)格索引掃描的空間查詢訪問計劃前面我們解釋過，DB2 優(yōu)化器不完全知道空間網(wǎng)格索引的細節(jié)。而且，優(yōu)化器很難判定那樣的索引掃描的成本和選擇性。DB2 開發(fā)小組目前選擇的解決方案使用戶可以提供對空間謂詞選擇性的估計。為此，可以在 WHERE 子句中將要害字 SELECTIVITY 放在謂詞的后面，后面帶一個 0 到 1 之間的對選擇性的估計值。這個值越低，優(yōu)化器就越有可能選擇掃描網(wǎng)格索引。清單 16 給出了一個查詢例子，在這個查詢中，向優(yōu)化器提示空間謂詞只有很少的符合條件的行。 上一頁123456789下一頁 清單 16. 為空間謂詞指定選擇性SELECT ...FROM　 <user_table>WHERE　ST_Intersects(<indexed_shape_column>, ...) = 1 SELECTIVITY 0.000001@調(diào)優(yōu)空間網(wǎng)格索引spatial extender 提供了一個 index advisor，以幫助您調(diào)優(yōu)空間索引。index advisor 可以通過命令行工具 gseidx 訪問，它的語法比較羅嗦，這一點跟 SQL 本身一樣。該工具不僅可用于獲得關于各種不同網(wǎng)格大小的建議，還可以用于收集一個已有的或計劃中的（虛）索引的統(tǒng)計信息。所以，可以提取關于在選擇某種網(wǎng)格大小時在哪個網(wǎng)格層次上將生成多少索引項的信息，而不必真正創(chuàng)建和物化索引。您應該注重到，Index Advisor 提供的建議可以作為索引優(yōu)化的出發(fā)點。清單 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下一頁 假如您熟悉空間數(shù)據(jù)和網(wǎng)格索引，那么結(jié)果就無需解釋了。關于空間網(wǎng)格索引機制和 Index Advisor 的更多細節(jié)可以在 DB2 Spatial Extender User's Guide and Reference（請參閱 參考資料 一節(jié)）中找到。值得一提的是，過去的經(jīng)驗表明，與精細調(diào)優(yōu)的網(wǎng)格索引相比，根據(jù)空間屬性聚集數(shù)據(jù)和使用一個適當定義的緩沖池更能對空間性能產(chǎn)生顯著的影響 —— 只要索引的參數(shù)大致在適當?shù)姆秶鷥?nèi)。選擇表空間類型假如有很多數(shù)據(jù)修改操作，而查詢較少，那么應該將注重力放在好的寫性能上。DB2 將數(shù)據(jù)庫中的所有數(shù)據(jù)都放在表空間中。治理員可以選擇表空間的類型和組成表空間的容器的類型。表空間的類型可以是 database managed（DMS）或 system managed（SMS），表空間類型的選擇對空間數(shù)據(jù)的寫性能有一定的影響。建議為 LONG 數(shù)據(jù)選擇 DMS 類型的表空間，換句話說，選擇存放 LOB 的表空間。這樣做的效果是，大對象化的空間數(shù)據(jù)將被放入到那個表空間。做出這一決定的原因在于 DB2 的內(nèi)部工作原理。這可以以一種更異步的方式將 LOB 寫到 DMS 表空間中，而將 LOB 寫到 SMS 表空間則要求同步的文件 I/O。一旦將數(shù)據(jù)放在 DMS 表空間上，就可以根據(jù)表空間的容器進一步選擇是使用原始設備還是文件系統(tǒng)。對于大對象化的空間數(shù)據(jù)，通常使用文件系統(tǒng)更好一些。理由是：對基于容器的文件系統(tǒng)的訪問要經(jīng)過操作系統(tǒng)內(nèi)核，而操作系統(tǒng)帶有一個文件系統(tǒng)緩存，可以加快對文件的重復訪問。而對原始設備的訪問則沒有緩存，導致物理設備上的直接讀寫操作。現(xiàn)在，對于一般的數(shù)據(jù)庫操作，不必考慮文件系統(tǒng)緩存，因為 DB2 實現(xiàn)了緩沖池，這些緩沖池已經(jīng)做了必要的緩存。但是對于 LOB 情況又不同了。由于不同的內(nèi)部存儲模型和潛在的巨大對象（大到 2GB），這里沒有緩沖池。所以文件系統(tǒng)緩存可以很大程度上幫助避免對磁盤的讀寫操作。 上一頁123456789下一頁 性能比較清單 18 展示了不同表空間類型的影響。首先在一個 SMS 表空間上執(zhí)行導入操作，一次使用一個較小的 inline length，一次使用都以內(nèi)聯(lián)形式存儲的空間值，然后在一個 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 表空間相比，將數(shù)據(jù)插入 DMS 表空間上的表中花費的時間大約只有四分之一。在解釋這些數(shù)值的時候，必須記住 DMS 和 SMS 表空間之間的基本不同點。DMS 表空間是在創(chuàng)建表空間時預先分配的。這意味著存放數(shù)據(jù)的頁是已經(jīng)存在的。而 SMS 表空間是在運行時動態(tài)伸縮的，導入操作會導致很多新的頁被分配，同時表空間（和它的文件）也隨之增長。所以，性能提升的很大一部分要歸功于 DMS 上頁的預先分配。但是，當比較使用不同 inline length 取得的運行時間時，我們發(fā)現(xiàn)，假如使用較小的 inline length（即更多的大對象化幾何圖形），那么將 SMS 換成 DMS 可以獲得 73% 的性能提升。假如使用較大的 inline length，則性能提升只有 66%。所以附加的性能提升顯然源自對大對象化數(shù)據(jù)更好的處理。結(jié)束語在本文中，我們展示了一些提升空間數(shù)據(jù)庫性能的重要技巧。文中談到了各種調(diào)優(yōu)步驟，包括基本的系統(tǒng)調(diào)優(yōu)，設置空間數(shù)據(jù)的 inline length，根據(jù)空間屬性聚集一個表中的行，調(diào)優(yōu)空間網(wǎng)格索引，以及選擇適當?shù)谋砜臻g類型。我們還解釋了一些決策的原因，并給出了通用的指導原則。此外，我們還在一個非常簡單的場景中演示了各個選項的效果。應用建議的指導原則所得到的結(jié)果取決于數(shù)據(jù)庫中實際的數(shù)據(jù)，以及整個系統(tǒng)和數(shù)據(jù)庫的配置。 上一頁123456789