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

《《數(shù)據(jù)倉庫SQL優(yōu)化》PPT課件.ppt》由會員分享，可在線閱讀，更多相關《《數(shù)據(jù)倉庫SQL優(yōu)化》PPT課件.ppt（22頁珍藏版）》請在裝配圖網(wǎng)上搜索。

數(shù)據(jù)倉庫系統(tǒng)調優(yōu)課題,DB2數(shù)據(jù)庫分區(qū),數(shù)據(jù)庫分區(qū)(database partitioning)設計,基本思路：字典表壓縮 Oracle：塊級壓縮 塊：大小介于4KB~32KB之間的存儲單元 當確定某個表要被壓縮后，Oracle在每個數(shù)據(jù)塊中保留空間，以便儲存在該數(shù)據(jù)塊中的多個位置上出現(xiàn)的數(shù)據(jù)的單一拷貝 不能夠跨塊的邊界尋找更大的重復模式 只支持在批量加載操作期間進行數(shù)據(jù)壓縮 DB2：表級壓縮 需要事先提供樣本數(shù)據(jù) 可在INSERT 操作過程中同時進行壓縮,DB2行壓縮技術（一）,基于壓縮字典的壓縮方式 通過使用較少的數(shù)據(jù)庫頁來表示相同數(shù)據(jù),從而達到節(jié)省磁盤存儲空間的目的,DB2行壓縮技術（二）,行壓縮的不會明顯降低UPDATE的效率 由于數(shù)據(jù)占用的存儲空間少了，所以訪問數(shù)據(jù)所需要的IO也少了，而IO正是數(shù)據(jù)倉庫系統(tǒng)常見的瓶頸 與行壓縮關聯(lián)的成本取決于壓縮和解壓縮數(shù)據(jù)所需的額外 CPU 周期 可使用 REORG TABLE 命令來創(chuàng)建壓縮字典?在處理 REORG 命令時,現(xiàn)有的所有表行都要被壓縮? Reorg完成之后，后續(xù)INSERT的數(shù)據(jù)會自動按照現(xiàn)有的壓縮字典來壓縮數(shù)據(jù)，如果數(shù)據(jù)業(yè)務邏輯發(fā)生了大的變更，壓縮效果不理想，可以重新執(zhí)行reorg操作 分區(qū)表是一個分區(qū)一個壓縮字典，所以分區(qū)表新分區(qū)的數(shù)據(jù)在沒有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 數(shù)據(jù)集: 中國移動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,壓縮后的數(shù)據(jù)僅占原來的38.76%（Oracle）或24.55%（DB2） VARCHAR的結構信息在未壓縮的DB2上占用較多空間 壓縮后數(shù)據(jù)增、刪、改、查的效率更高 移動數(shù)據(jù)上非常適合壓縮,索引優(yōu)化(一),索引的優(yōu)點,1. 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。 2. 可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。 3. 可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。 4. 在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。 5. 通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。,索引的缺點,1. 創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。 2. 索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間。 3.當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。,索引優(yōu)化(二),DB2在用戶指定數(shù)據(jù)表主鍵時， 自動生成以主鍵為關鍵字的聚簇索引。建立其他索引時有以下策略： ( 1 )避免在小表上建立索引 因為索引的維護需要一定的代價，在表上進行增刪改操作時 ，索引需要重組，這就增加了數(shù)據(jù)庫的負擔， 如果對索引的插入或者更新?lián)p失的時間大于在查詢中節(jié)省下來的時間，那么使用索引就是一個不明智的決定。 ( 2 )在經常進行鏈接的列上建立索引， 并且字段類型保持一致多表鏈接查詢是數(shù)據(jù)庫中最復雜、 最耗時的操作之一。改進多表鏈接查詢性能對系統(tǒng)性能的改進起很大的作用。在鏈接屬性上存在索引時， 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 )避免在選擇性太低的字段上建立索引，基數(shù)較大的列很適合用來做索引。選擇性太低指的是索引中同一索引值的對應記錄太多， 在這種列上建立索引意義不大。DB 2最優(yōu)化處理器不會使用該列作為執(zhí)行計劃的一部分 。 ( 7 )建立組合索引需要注意索引列順序如果在 A， B兩列上順序建立組合索引以后， 那么在w h e r e 子句中帶有下列搜 索條件會使用此索引 ： 條件為 A； 條件為 A。 B 。如果條件僅為 B則不會使用此索引。對于多列索引，將查詢中引用最多的列放在定義的前面。 ( 8 )由少數(shù)窄列組成，列寬度較大的列不適合作索引?？紤]到管理上的開銷，應避免在索引中使用多于 5 個的列。 ( 9 )避免添加與已有的索引相似的索引。因為這樣會給優(yōu)化器帶來更多的工作，并且會降慢更新操作的速度。相反，我們應該修改已有的索引，使其包含附加的列。,多維群集（ MDC ）索引(一),基于塊的索引，比常規(guī)索引小得多，掃描的時候更快 MDC 對性能的貢獻在于提高檢索數(shù)據(jù)的效率 MDC 塊索引意味著需要的 RID 索引更少 由于新行是插在表中具有近似值的行附近的位置，因此數(shù)據(jù)仍然是聚合的，而不需要運行 REORG 實用程序 由于新行是插在表中具有近似值的行附近的位置，所以MDC表無法創(chuàng)建群集索引(cluster index)，索引的群集率也比較低，類似serv_id = 1100000000000000 and serv_id 1200000000000000 的索引訪問效率比較低。按照目前倉庫的應用現(xiàn)狀，清單類的表不適合建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; 系統(tǒng)會自動創(chuàng)建MDC索引,分區(qū)表（一）,與 MDC 類似，它也可以將具有近似值的行存儲在一起 分區(qū)表支持按照一個維將一個表分區(qū)成多個數(shù)據(jù)分區(qū) 通過分區(qū)表特性，用戶可以手動地定義每個數(shù)據(jù)分區(qū)，包括將被包括到那個分區(qū)的值的范圍 每個分區(qū)表分區(qū)是一個單獨的數(shù)據(jù)庫對象 因此，分區(qū)表支持為分區(qū)表附加和卸除數(shù)據(jù)分區(qū)。卸除的分區(qū)成為一個常規(guī)表。而且，必要時可以將每個數(shù)據(jù)分區(qū)放在它自己的表空間中。 在DROP分區(qū)表的時候是一個分區(qū)一個分區(qū)地卸載，分區(qū)多的情況下比常規(guī)表慢很多 分區(qū)表通過分區(qū)排除提高數(shù)據(jù)檢索性能 分區(qū)字段同樣不允許UPDATE操作,分區(qū)表（二）,分區(qū)表的每個表分區(qū)進行reorg操作，但是要把該分區(qū)的數(shù)據(jù)卸載(detach)到小表，然后再安裝(attach)上去 RANGE分區(qū)，未指定的分區(qū)值不能INSERT到數(shù)據(jù)庫中 添加分區(qū)操作ALTER TABLE xjdss.linguo_md_cu_user_day_03 ADD PARTITION STAT_DATE20080601 STARTING FROM 20080601 INCLUSIVE ENDING AT 20080602; 添加帶數(shù)據(jù)分區(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ū)(可以用戶清除數(shù)據(jù)) ALTER TABLE XJDSS.LINGUO_MD_CU_USER_DAY_03 DETACH PARTITION STAT_DATE20080601 INTO XJDSS.MD_CU_USER_DAY20080601 ;,分區(qū)表（三）,卸載分區(qū)(可以用戶清除數(shù)據(jù)) 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中看不出來，可以從系統(tǒng)字典表中看出來 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個文件夾，每個文件夾中包含1~6個Perl程序，SQL語句嵌入到Perl程序中對數(shù)據(jù)庫進行操作,現(xiàn)狀1：插入語句嵌入到循環(huán)中，每次循環(huán)插入一條語句，然后斷開與數(shù)據(jù)庫的聯(lián)系，進行非數(shù)據(jù)庫操作,改進建議1：在每次與數(shù)據(jù)庫的連接中，盡可能多地執(zhí)行插入操作，減少與數(shù)據(jù)庫連接的次數(shù),,現(xiàn)狀2：不少插入語句和文件操作，或者插入語句和字符串操作交替運行 INSERT INTO $PDATADB.$table_target($TRG_COL_LIST) SELECT $TRG_COL_LIST FROM $table_today ; 交替著：$UNIT_DATTM = substr($DATA_FNAME,8,8);,改進建議2：在插入數(shù)據(jù)的過程中，盡可能在下一次插入語句之前不要進行其它文件I/O 或字符處理，全部集中最后一同處理,,SQL優(yōu)化(二),19,現(xiàn)狀3：DELETE FROM $PDATADB.$table_target WHERE $FILTER INSERT INTO $PDATADB.$table_target($TRG_COL_LIST),改進建議3：使用UPDATE代替DELETE和INSERT組合 ，減少數(shù)據(jù)庫I/O,,現(xiàn)狀4：WHERE子句中，直接在列名上使用函數(shù)，無法利用索引 where function(colName) operator Value ；,改進建議4：函數(shù)轉移到值上 where colName operator function(Value),,現(xiàn)狀5：查詢語句的WHERE子句中，直接在沒有索引的列名上使用函數(shù) char(trim(tablename))=char(trim(tb_10400_04002_s_20041007…)),改進建議5：進行語句的等價轉換，提高效率： tablename like ’% tb_10400_04002_s_20041007…% ’,,現(xiàn)狀6：統(tǒng)計表中記錄的個數(shù)，大部分情況使用了 select count(*),改進建議6：進行語句的等價轉換，盡量利用索引進行統(tǒng)計：SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table_name) AND indid 2,,SQL語句索引的利用,采用函數(shù)處理的字段不能利用索引，如： substr(hbs_bh,1,4)=’5400’，優(yōu)化處理：hbs_bh like ‘5400%’ 進行了顯式或隱式的運算的字段不能進行索引，如： ss_df+2050，優(yōu)化處理：ss_df30 ‘X’||hbs_bh’X5400021452’，優(yōu)化處理：hbs_bh’5400021542’ 條件內包括了多個本表的字段運算時不能進行索引，如： ys_dfcx_df，無法進行優(yōu)化 qc_bh||kh_bh=’5400250000’，優(yōu)化處理：qc_bh=’5400’ and kh_bh=’250000’,表的三種Join方法(NLJOIN HSJOIN MSJOIN ）,對于被連接的數(shù)據(jù)子集較小的情況，nested loop連接是個較好的選擇。nested loop就是掃描一個表，每讀到一條記錄，就根據(jù)索引去另一個表里面查找，沒有索引一般就不會是 nested loops。 Hash join是大數(shù)據(jù)集連接時的常用方式。優(yōu)化器掃描小表（或數(shù)據(jù)源），利用連接鍵（也就是根據(jù)連接字段計算hash 值）在內存中建立hash表，然后掃描大表，每讀到一條記錄就來探測hash表一次，找出與hash表匹配的行。 對連接的每個表做table access full；對table access full的結果按照連接鍵進行排序；進行msjoin對排序結果進行合并。 因為其排序成本高，大多為hash join替代。,SQL優(yōu)化效果,實驗環(huán)境：處理器AMD865，主頻1.8GHz，內存31.8GB 實驗數(shù)據(jù)：每表包含10萬條記錄,