《數據倉庫SQL優(yōu)化》PPT課件.ppt

數據倉庫系統調優(yōu)課題,DB2數據庫分區(qū),數據庫分區(qū)(database partitioning)設計,基本思路：字典表壓縮 Oracle：塊級壓縮 塊：大小介于4KB32KB之間的存儲單元 當確定某個表要被壓縮后，Oracle在每個數據塊中保留空間，以便儲存在該數據塊中的多個位置上出現的數據的單一拷貝 不能夠跨塊的邊界尋找更大的重復模式 只支持在批量加載操作期間進行數據壓縮 DB2：表級壓縮 需要事先提供樣本數據 可在INSERT 操作過程中同時進行壓縮,DB2行壓縮技術（一）,基于壓縮字典的壓縮方式 通過使用較少的數據庫頁來表示相同數據,從而達到節(jié)省磁盤存儲空間的目的,DB2行壓縮技術（二）,行壓縮的不會明顯降低UPDATE的效率 由于數據占用的存儲空間少了，所以訪問數據所需要的IO也少了，而IO正是數據倉庫系統常見的瓶頸 與行壓縮關聯的成本取決于壓縮和解壓縮數據所需的額外 CPU 周期 可使用 REORG TABLE 命令來創(chuàng)建壓縮字典在處理 REORG 命令時,現有的所有表行都要被壓縮 Reorg完成之后，后續(xù)INSERT的數據會自動按照現有的壓縮字典來壓縮數據，如果數據業(yè)務邏輯發(fā)生了大的變更，壓縮效果不理想，可以重新執(zhí)行reorg操作 分區(qū)表是一個分區(qū)一個壓縮字典，所以分區(qū)表新分區(qū)的數據在沒有reorg過之前是不會自動壓縮的,DB2 VS Oracle,實驗環(huán)境 OS: Windows Server 2003 64-bit Memory: 2G CPU: AMD opteron 865 processor 1.80G（8核） Oracle企業(yè)版 10g VS DB2版本：DB2 企業(yè)版 9.7 數據集: 中國移動GSM語音通話記錄（gsm_voic_cdr.dat），替換分隔符后總大小為4.247G,實驗效果,7,7,查詢語句 select count(*) from GSM where EXCHANGECODE = 8613900121 更新語句 UPDATE GSM SET LACCODE = GSM WHERE DURATION = 100 刪除語句 DELETE FROM GSM WHERE DURATION = 100,壓縮后的數據僅占原來的38.76%（Oracle）或24.55%（DB2） VARCHAR的結構信息在未壓縮的DB2上占用較多空間 壓縮后數據增、刪、改、查的效率更高 移動數據上非常適合壓縮,索引優(yōu)化(一),索引的優(yōu)點,1. 通過創(chuàng)建唯一性索引，可以保證數據庫表中每一行數據的唯一性。 2. 可以大大加快數據的檢索速度，這也是創(chuàng)建索引的最主要的原因。 3. 可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。 4. 在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。 5. 通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統的性能。,索引的缺點,1. 創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。 2. 索引需要占物理空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間。 3.當對表中的數據進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數據的維護速度。,索引優(yōu)化(二),DB2在用戶指定數據表主鍵時， 自動生成以主鍵為關鍵字的聚簇索引。建立其他索引時有以下策略： ( 1 )避免在小表上建立索引 因為索引的維護需要一定的代價，在表上進行增刪改操作時 ，索引需要重組，這就增加了數據庫的負擔， 如果對索引的插入或者更新損失的時間大于在查詢中節(jié)省下來的時間，那么使用索引就是一個不明智的決定。 ( 2 )在經常進行鏈接的列上建立索引， 并且字段類型保持一致多表鏈接查詢是數據庫中最復雜、 最耗時的操作之一。改進多表鏈接查詢性能對系統性能的改進起很大的作用。在鏈接屬性上存在索引時， D B 2中采用索引嵌套循環(huán)鏈接 ，否則 D B 2使用哈希鏈接。 ( 3 )在頻繁進行 g r o u p b y o r d e r b y的列上建立索引 ( 4 )建立索引字段的列的長度盡量小，避免在 B l o b C l o b類型上建立索引。,索引優(yōu)化(三),( 5 )在 S QL語句中頻繁進行比較運算的列上建立索引。 ( 6 )避免在選擇性太低的字段上建立索引，基數較大的列很適合用來做索引。選擇性太低指的是索引中同一索引值的對應記錄太多， 在這種列上建立索引意義不大。DB 2最優(yōu)化處理器不會使用該列作為執(zhí)行計劃的一部分 。 ( 7 )建立組合索引需要注意索引列順序如果在 A， B兩列上順序建立組合索引以后， 那么在w h e r e 子句中帶有下列搜 索條件會使用此索引 ： 條件為 A； 條件為 A。 B 。如果條件僅為 B則不會使用此索引。對于多列索引，將查詢中引用最多的列放在定義的前面。 ( 8 )由少數窄列組成，列寬度較大的列不適合作索引?？紤]到管理上的開銷，應避免在索引中使用多于 5 個的列。 ( 9 )避免添加與已有的索引相似的索引。因為這樣會給優(yōu)化器帶來更多的工作，并且會降慢更新操作的速度。相反，我們應該修改已有的索引，使其包含附加的列。,多維群集（ MDC ）索引(一),基于塊的索引，比常規(guī)索引小得多，掃描的時候更快 MDC 對性能的貢獻在于提高檢索數據的效率 MDC 塊索引意味著需要的 RID 索引更少 由于新行是插在表中具有近似值的行附近的位置，因此數據仍然是聚合的，而不需要運行 REORG 實用程序 由于新行是插在表中具有近似值的行附近的位置，所以MDC表無法創(chuàng)建群集索引(cluster index)，索引的群集率也比較低，類似serv_id = 1100000000000000 and serv_id 1200000000000000 的索引訪問效率比較低。按照目前倉庫的應用現狀，清單類的表不適合建MDC 要避免mdc字段被頻繁的更新，因為引起被更新的行從原來的塊遷移到新的塊,多維群集（ MDC ）索引(二),MDC維的定義原則 用于范圍、等于或 IN 列表謂詞 用于轉入、轉出或其他大規(guī)模的行刪除 粗粒度，也就是說不同的值很少的列 MDC維最多可以使用16列的組合 典型的應用設計是用一個表示日期的列作為一個 MDC 維，再加上 0 到 3 個其他列作為其他維，例如 地域(area) 和 產品類型(product_type),多維群集（ MDC ）索引(三),CREATE TABLE “XJDSS“.“LINGUO_MD_CU_COLLIGATE_ATTRI_DAY_01“ (“STAT_DATE“ DECIMAL(8, 0), “SERV_ID“ DECIMAL(18, 0), ) ORGANIZE BY DIMENSIONS (STAT_DATE,USER_STATUS) DATA CAPTURE NONE IN “BAS_WH_MINE“ INDEX IN “BAS_WH_MINE“ PARTITIONING KEY (SERV_ID ) USING HASHING COMPRESS YES VALUE COMPRESSION; 系統會自動創(chuàng)建MDC索引,分區(qū)表（一）,與 MDC 類似，它也可以將具有近似值的行存儲在一起 分區(qū)表支持按照一個維將一個表分區(qū)成多個數據分區(qū) 通過分區(qū)表特性，用戶可以手動地定義每個數據分區(qū)，包括將被包括到那個分區(qū)的值的范圍 每個分區(qū)表分區(qū)是一個單獨的數據庫對象 因此，分區(qū)表支持為分區(qū)表附加和卸除數據分區(qū)。卸除的分區(qū)成為一個常規(guī)表。而且，必要時可以將每個數據分區(qū)放在它自己的表空間中。 在DROP分區(qū)表的時候是一個分區(qū)一個分區(qū)地卸載，分區(qū)多的情況下比常規(guī)表慢很多 分區(qū)表通過分區(qū)排除提高數據檢索性能 分區(qū)字段同樣不允許UPDATE操作,分區(qū)表（二）,分區(qū)表的每個表分區(qū)進行reorg操作，但是要把該分區(qū)的數據卸載(detach)到小表，然后再安裝(attach)上去 RANGE分區(qū)，未指定的分區(qū)值不能INSERT到數據庫中 添加分區(qū)操作ALTER TABLE xjdss.linguo_md_cu_user_day_03 ADD PARTITION STAT_DATE20080601 STARTING FROM 20080601 INCLUSIVE ENDING AT 20080602; 添加帶數據分區(qū)的操作 ALTER TABLE xjdss.linguo_md_cu_user_day_03 ATTACH PARTITION STAT_DATE20080601 STARTING FROM 20080601 INCLUSIVE ENDING AT 20080602 EXCLUSIVE from XJDSS.MD_CU_USER_DAY20080601; 卸載分區(qū)(可以用戶清除數據) ALTER TABLE XJDSS.LINGUO_MD_CU_USER_DAY_03 DETACH PARTITION STAT_DATE20080601 INTO XJDSS.MD_CU_USER_DAY20080601 ;,分區(qū)表（三）,卸載分區(qū)(可以用戶清除數據) ALTER TABLE XJDSS.LINGUO_MD_CU_USER_DAY_03 DETACH PARTITION STAT_DATE20080601 INTO XJDSS.MD_CU_USER_DAY20080601 ; 分區(qū)轉入后該分區(qū)的狀態(tài)不正常 可以同構完整性檢查來回復 SET INTEGRITY FOR xjdss.linguo_md_cu_user_day_03 ALLOW WRITE ACCESS IMMEDIATE CHECKED FOR EXCEPTION IN xjdss.linguo_md_cu_user_day_03 USE XJDSS.MD_CU_USER_DAY_tmp SET INTEGRITY FOR xjdss.linguo_md_cu_user_day_03 ALLOW WRITE ACCESS IMMEDIATE CHECKED; 分區(qū)表結構在DDL中看不出來，可以從系統字典表中看出來 select * from syscat.datapartitions with ur,分區(qū)表（四）,建表語句(推薦) CREATE TABLE XJDSS.LINGUO_MD_CU_COLLIGATE_ATTRI_DAY_01 (“STAT_DATE“ DECIMAL(8, 0), “SERV_ID“ DECIMAL(18, 0), “MSISDN“ VARCHAR(15) ) DATA CAPTURE NONE IN “BAS_WH_MID01“ INDEX IN “BAS_WH_MID01_IDX“ PARTITIONING KEY (SERV_ID ) USING HASHING partition by range (stat_date) ( PARTITION STAT_DATE20081001 STARTING FROM 20081001 INCLUSIVE ENDING AT 20081002 EXCLUSIVE ,PARTITION STAT_DATE20081002 STARTING FROM 20081002 INCLUSIVE ENDING AT 20081003 EXCLUSIVE ) COMPRESS YES VALUE COMPRESSION;,SQL優(yōu)化(一),18,針對5月6日運行時間最長的100個腳本：共198個文件夾，每個文件夾中包含16個Perl程序，SQL語句嵌入到Perl程序中對數據庫進行操作,現狀1：插入語句嵌入到循環(huán)中，每次循環(huán)插入一條語句，然后斷開與數據庫的聯系，進行非數據庫操作,改進建議1：在每次與數據庫的連接中，盡可能多地執(zhí)行插入操作，減少與數據庫連接的次數,現狀2：不少插入語句和文件操作，或者插入語句和字符串操作交替運行 INSERT INTO $PDATADB.$table_target($TRG_COL_LIST) SELECT $TRG_COL_LIST FROM $table_today ; 交替著：$UNIT_DATTM = substr($DATA_FNAME,8,8);,改進建議2：在插入數據的過程中，盡可能在下一次插入語句之前不要進行其它文件I/O 或字符處理，全部集中最后一同處理,SQL優(yōu)化(二),19,現狀3：DELETE FROM $PDATADB.$table_target WHERE $FILTER INSERT INTO $PDATADB.$table_target($TRG_COL_LIST),改進建議3：使用UPDATE代替DELETE和INSERT組合 ，減少數據庫I/O,現狀4：WHERE子句中，直接在列名上使用函數，無法利用索引 where function(colName) operator Value ；,改進建議4：函數轉移到值上 where colName operator function(Value),現狀5：查詢語句的WHERE子句中，直接在沒有索引的列名上使用函數 char(trim(tablename)=char(trim(tb_10400_04002_s_20041007),改進建議5：進行語句的等價轉換，提高效率： tablename like % tb_10400_04002_s_20041007% ,現狀6：統計表中記錄的個數，大部分情況使用了 select count(*),改進建議6：進行語句的等價轉換，盡量利用索引進行統計：SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table_name) AND indid 2,SQL語句索引的利用,采用函數處理的字段不能利用索引，如： substr(hbs_bh,1,4)=5400，優(yōu)化處理：hbs_bh like 5400% 進行了顯式或隱式的運算的字段不能進行索引，如： ss_df+2050，優(yōu)化處理：ss_df30 X|hbs_bhX5400021452，優(yōu)化處理：hbs_bh5400021542 條件內包括了多個本表的字段運算時不能進行索引，如： ys_dfcx_df，無法進行優(yōu)化 qc_bh|kh_bh=5400250000，優(yōu)化處理：qc_bh=5400 and kh_bh=250000,表的三種Join方法(NLJOIN HSJOIN MSJOIN ）,對于被連接的數據子集較小的情況，nested loop連接是個較好的選擇。nested loop就是掃描一個表，每讀到一條記錄，就根據索引去另一個表里面查找，沒有索引一般就不會是 nested loops。 Hash join是大數據集連接時的常用方式。優(yōu)化器掃描小表（或數據源），利用連接鍵（也就是根據連接字段計算hash 值）在內存中建立hash表，然后掃描大表，每讀到一條記錄就來探測hash表一次，找出與hash表匹配的行。 對連接的每個表做table access full；對table access full的結果按照連接鍵進行排序；進行msjoin對排序結果進行合并。 因為其排序成本高，大多為hash join替代。,SQL優(yōu)化效果,實驗環(huán)境：處理器AMD865，主頻1.8GHz，內存31.8GB 實驗數據：每表包含10萬條記錄,